PostgreSQL Primary with Read Replicas→Horizontally Sharded Database
Duration Estimate
3–6 months or more
Migration Stages
6
Blocking Prerequisites
4
Confidence
StrongReadiness Checklist(4 blocking, 1 advisory)
Team at staff+plus maturity level
This migration requires staff+plus 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.
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.
Rollback plan documented and tested
Rollback feasibility for this migration is 'never'. 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.
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.
Migration trigger condition confirmed
Confirm that the actual trigger for this migration is present. Common triggers: Write throughput sustained above 80k–100k TPS: single primary B-tree write amplification is the verified bottleneck after vertical scaling is exhausted; Single-node storage approaching physical or operational limits (>10TB) with growth rate that will breach limits within 12 months; Vertical scaling has reached the largest available instance class and write latency SLA is still being violated.
Guidance: Validate the trigger with production metrics or benchmarks before committing to the migration timeline.
Migration Stages
Identify the shard key. Audit every query in the application to determine whether it includes the shard key in its WHERE clause. Queries that do not include the shard key must scatter-gather across all shards: enumerate and accept these as permanent scatter queries, or redesign them. Document every cross-shard join that must be eliminated. This step is research and design: no production changes.
Key tasks
- ·Identify the shard key. Audit every query in the application to determine whether it includes the shard key in its WHERE clause. Queries that do not include the shard key must scatter-gather across all shards: enumerate and accept these as permanent scatter queries, or redesign them. Document every cross-shard join that must be eliminated. This step is research and design: no production changes.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Shard Key Selection and Query Audit' validated in production with no regression in key metrics
Rollback: possible at this stage
Implement shard routing in the application: all database calls go through a routing layer that accepts a shard key and returns the correct database connection. Initially, all routing maps to shard 0 (the existing single database). This allows the routing logic to be deployed and validated in production before any data is moved.
Key tasks
- ·Implement shard routing in the application: all database calls go through a routing layer that accepts a shard key and returns the correct database connection. Initially, all routing maps to shard 0 (the existing single database). This allows the routing logic to be deployed and validated in production before any data is moved.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Application Shard Routing Layer' validated in production with no regression in key metrics
Rollback: possible at this stage
Provision N shard instances (primary + replica each). Validate replication, connection pooling, monitoring, and failover procedures on each shard. All shards are empty at this stage. Production traffic still routes entirely to the single database (shard 0).
Key tasks
- ·Provision N shard instances (primary + replica each). Validate replication, connection pooling, monitoring, and failover procedures on each shard. All shards are empty at this stage. Production traffic still routes entirely to the single database (shard 0).
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Provision Shard Cluster' validated in production with no regression in key metrics
Rollback: possible at this stage
Application writes to both the original single database and the target shard simultaneously. This validates shard routing correctness: the sharded dataset should grow to match the single database. Run consistency checks. Monitor for routing errors (rows on wrong shard).
Key tasks
- ·Application writes to both the original single database and the target shard simultaneously. This validates shard routing correctness: the sharded dataset should grow to match the single database. Run consistency checks. Monitor for routing errors (rows on wrong shard).
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Dual-Write to Single DB and Sharded Cluster' validated in production with no regression in key metrics
Rollback: possible at this stage
Migrate all existing rows from the single database to their correct shard via batch backfill jobs. Run checksums and row count validation per shard. This is the most time-consuming stage: large datasets require weeks of controlled backfill with rate limiting to avoid impacting production write performance.
Key tasks
- ·Migrate all existing rows from the single database to their correct shard via batch backfill jobs. Run checksums and row count validation per shard. This is the most time-consuming stage: large datasets require weeks of controlled backfill with rate limiting to avoid impacting production write performance.
- ·Validate rollback capability at this stage boundary before proceeding.
Exit criteria
- ✓'Historical Data Backfill' validated in production with no regression in key metrics
[CRITICAL] Cross-shard joins: queries that previously fetched rows from multiple tables via JOIN : must be replaced entirely. These queries are not possible in the sharded model without scatter-gather. Analytics queries that span all shards will have O(N) cost growth.
Mitigation: Eliminate all cross-shard joins before cutover: replace with async denormalization, application-level assembly, or move to a separate analytics store (ClickHouse, Snowflake) for cross-entity queries. Accept that some query patterns are permanently incompatible with sharding.
[CRITICAL] A shard key with non-uniform distribution creates hotspot shards: one shard receives a disproportionate share of writes while others are idle. The hotspot shard becomes the new bottleneck, defeating the purpose of sharding. Resharding requires another full data migration.
Mitigation: Measure shard key value distribution before choosing. UUIDs and high-cardinality integers distribute evenly with consistent hashing. Avoid shard keys based on geographic region, account type, or any attribute with uneven business distribution.
Rollback: possible at this stage
Gradually shift read traffic from the single database to the sharded cluster: 10%, 25%, 50%, 100% over multiple weeks. Monitor query latency and error rates at each step. After reads are fully on the sharded cluster, cut writes. Decommission single database after 30+ days of stable sharded operation.
Key tasks
- ·Gradually shift read traffic from the single database to the sharded cluster: 10%, 25%, 50%, 100% over multiple weeks. Monitor query latency and error rates at each step. After reads are fully on the sharded cluster, cut writes. Decommission single database after 30+ days of stable sharded operation.
- ·No rollback available after this stage, confirm all exit criteria before proceeding.
Exit criteria
- ✓'Read Cutover and Single DB Decommission' validated in production with no regression in key metrics
[CRITICAL] Cross-shard joins: queries that previously fetched rows from multiple tables via JOIN : must be replaced entirely. These queries are not possible in the sharded model without scatter-gather. Analytics queries that span all shards will have O(N) cost growth.
Mitigation: Eliminate all cross-shard joins before cutover: replace with async denormalization, application-level assembly, or move to a separate analytics store (ClickHouse, Snowflake) for cross-entity queries. Accept that some query patterns are permanently incompatible with sharding.
[CRITICAL] A shard key with non-uniform distribution creates hotspot shards: one shard receives a disproportionate share of writes while others are idle. The hotspot shard becomes the new bottleneck, defeating the purpose of sharding. Resharding requires another full data migration.
Mitigation: Measure shard key value distribution before choosing. UUIDs and high-cardinality integers distribute evenly with consistent hashing. Avoid shard keys based on geographic region, account type, or any attribute with uneven business distribution.
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 never, follow the documented rollback procedure for 'read-replicas-to-sharding'. Engage senior engineering support.
Recovery: Hours to days, rollback is difficult for this migration
After stage 5
Trigger: Any of the following in the 30 minutes after completing stage 5: p99 latency increases >50% from baseline, error rate exceeds 1%, or data consistency checks fail.
Procedure: Revert stage 5 changes. Rollback is never, follow the documented rollback procedure for 'read-replicas-to-sharding'. 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 'Horizontally Sharded Database' 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.
- ✓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.