DBRaven
Start Review →
Migration Playbook

Single-Node PostgreSQL→Partitioned PostgreSQL

High complexityHigh riskRarely Possible

Duration Estimate

1–3 months

Migration Stages

6

Blocking Prerequisites

4

Confidence

Strong

Readiness Checklist(4 blocking, 2 advisory)

blocking
Team

Team at senior maturity level

This migration requires senior engineering maturity. The team should have hands-on experience with the relevant technologies and proven ability to execute complex system changes safely.

Guidance: Build team skills through learning modules before attempting this migration.

blocking
Monitoring

Production observability baseline established

Comprehensive metrics, logs, and traces must be in place before the migration starts. You need a clear baseline to detect regressions during and after each migration stage.

Guidance: Instrument key metrics (latency p99, error rate, throughput, resource utilization) on all affected components.

blocking
Process

Rollback plan documented and tested

Rollback feasibility for this migration is 'rarely'. Rollback is difficult or impossible, validate the migration in a staging environment thoroughly before proceeding to production.

Guidance: Run a full dry-run in a staging environment that mirrors production load characteristics.

blocking
Data

Data backup and validation strategy in place

This migration involves technology changes that affect data storage or access patterns. Ensure full backups are current and a data validation strategy is defined to confirm data integrity at each stage boundary.

Guidance: Perform a full backup before starting. Run data validation checksums after each stage.

Infrastructure

'postgresql' provisioned and validated in target environment

'postgresql' must be available, configured, and load-tested in the target environment before the migration stage that introduces it.

Guidance: Deploy 'postgresql' with production-equivalent configuration. Run a smoke test under representative load.

Process

Migration trigger condition confirmed

Confirm that the actual trigger for this migration is present. Common triggers: Write throughput sustains >30k rows/second: single-node B-tree write amplification becomes the bottleneck; Table size exceeds 500GB: VACUUM runtime extends beyond maintenance windows; Partition pruning would eliminate >80% of scanned rows for most queries.

Guidance: Validate the trigger with production metrics or benchmarks before committing to the migration timeline.

Migration Stages

1Partition Strategy DesignLow1-2 weeks

Identify partition key (most commonly created_at or tenant_id). Decide range vs hash partitioning. Determine partition granularity (monthly, weekly, quarterly). Document all queries that will and will not benefit from partition pruning.

Key tasks

  • ·Identify partition key (most commonly created_at or tenant_id). Decide range vs hash partitioning. Determine partition granularity (monthly, weekly, quarterly). Document all queries that will and will not benefit from partition pruning.
  • ·Validate rollback capability at this stage boundary before proceeding.

Exit criteria

  • ✓'Partition Strategy Design' validated in production with no regression in key metrics

Rollback: possible at this stage

2Shadow Table CreationLow1 week

Create new partitioned table structure alongside existing table. Do not migrate data yet. Validate that all indexes, constraints, and foreign keys can be replicated per partition.

Key tasks

  • ·Create new partitioned table structure alongside existing table. Do not migrate data yet. Validate that all indexes, constraints, and foreign keys can be replicated per partition.
  • ·Validate rollback capability at this stage boundary before proceeding.

Exit criteria

  • ✓'Shadow Table Creation' validated in production with no regression in key metrics

Rollback: possible at this stage

3Dual WriteMedium1-2 weeks

Application writes to both old table and new partitioned table simultaneously. Validate partition routing is correct for recent data. Monitor insert latency on both paths.

Key tasks

  • ·Application writes to both old table and new partitioned table simultaneously. Validate partition routing is correct for recent data. Monitor insert latency on both paths.
  • ·Validate rollback capability at this stage boundary before proceeding.

Exit criteria

  • ✓'Dual Write' validated in production with no regression in key metrics

Rollback: possible at this stage

4Historical Data MigrationHigh1-4 weeks

Backfill historical data from old table into partitioned table in batches. Use pg_partman or custom migration scripts. Validate row counts per partition. This is the longest and riskiest step: target 10k-50k rows per batch with sleep between batches.

Key tasks

  • ·Backfill historical data from old table into partitioned table in batches. Use pg_partman or custom migration scripts. Validate row counts per partition. This is the longest and riskiest step: target 10k-50k rows per batch with sleep between batches.
  • ·Validate rollback capability at this stage boundary before proceeding.

Exit criteria

  • ✓'Historical Data Migration' validated in production with no regression in key metrics

Rollback: possible at this stage

5Read Traffic CutoverMedium1-2 weeks

Switch application reads to partitioned table. Monitor partition pruning effectiveness via EXPLAIN ANALYZE. Confirm query plans use partition exclusion.

Key tasks

  • ·Switch application reads to partitioned table. Monitor partition pruning effectiveness via EXPLAIN ANALYZE. Confirm query plans use partition exclusion.
  • ·Validate rollback capability at this stage boundary before proceeding.

Exit criteria

  • ✓'Read Traffic Cutover' validated in production with no regression in key metrics

Rollback: possible at this stage

6Old Table ArchivalLow1 week

Rename old table to _archived. Keep for 30 days before dropping. Remove dual-write path from application code.

Key tasks

  • ·Rename old table to _archived. Keep for 30 days before dropping. Remove dual-write path from application code.
  • ·No rollback available after this stage, confirm all exit criteria before proceeding.

Exit criteria

  • ✓'Old Table Archival' validated in production with no regression in key metrics

Rollback: not available after this stage

Rollback Decision Points

After stage 4

Trigger: Any of the following in the 30 minutes after completing stage 4: p99 latency increases >50% from baseline, error rate exceeds 1%, or data consistency checks fail.

Procedure: Revert stage 4 changes. Rollback is rarely, follow the documented rollback procedure for 'postgresql-to-partitioned'. Engage senior engineering support.

Recovery: Hours to days, rollback is difficult for this migration

After stage 6

Trigger: Final validation: confirm all success criteria are met within 24 hours of migration completion. If any success criterion fails, trigger rollback or remediation.

Procedure: If success criteria are not met within 24 hours, escalate to the architecture team. Decide between full rollback, partial rollback, or targeted remediation based on scope.

Recovery: 24–72 hours for full assessment and remediation

Success Criteria

  • ✓Target state 'Partitioned PostgreSQL' is stable in production for 72+ hours with no regressions.
  • ✓p99 latency on all affected services is within acceptable range (≤110% of pre-migration baseline).
  • ✓Error rate is at or below pre-migration baseline.
  • ✓New components (postgresql) are fully operational and monitored.
  • ✓Data integrity verified: checksums or consistency checks confirm no data loss or corruption.

This migration playbook is derived from structured architecture knowledge. Duration estimates and risk levels are structural approximations based on documented migration complexity and operational risk, not measured execution data. Validate all stages against your specific system constraints before executing.