Problem Statement: Zero-Downtime Database Migration at Scale
Frames blue-green database migration as a data-layer extension of the blue-green deployment pattern, addressing schema changes, data replication, and traffic cutover without downtime.
Problem statement
Design a blue-green database migration system that enables a production application to migrate from an existing database cluster (Blue) to a new cluster with a different schema, engine version, or provider (Green) with zero downtime and full rollback capability.
The core challenge is not merely provisioning a second database. It is maintaining bidirectional data consistency between two divergent schemas during a transition window that may span hours to days, then executing an atomic cutover that redirects all read and write traffic in a single coordinated step, while preserving the ability to instantly revert if the Green environment exhibits anomalies.
Why this is harder than application-level blue-green
Application blue-green is well understood: deploy a new version behind a load balancer, shift traffic, roll back by flipping a pointer. Database blue-green adds three orders of complexity:
- State divergence: The application writes continuously during migration. Every write to Blue must be replicated to Green (and potentially vice versa during the overlap window), across potentially incompatible schemas.
- Schema incompatibility: Green may have new columns, removed columns, renamed tables, different indexes, or an entirely different storage engine. The replication layer must transform data in flight.
- Referential integrity across versions: Foreign keys, triggers, stored procedures, and application-level invariants must hold in both environments simultaneously.
The four phases of a blue-green database migration
- Provision & Sync: Stand up Green with the target schema. Establish a Change Data Capture (CDC) pipeline from Blue to Green. Backfill historical data.
- Validate: Run shadow queries against Green. Compare checksums, row counts, and query results. Verify application compatibility through canary reads.
- Cutover: Freeze writes to Blue (or drain in-flight transactions), confirm replication lag is zero, flip the application connection string to Green, and resume writes.
- Cleanup: After a bake period, decommission Blue. Archive binlogs. Remove CDC infrastructure.
What makes this interview question distinctive
Most blue-green references cover the application tier only. This question probes whether you can extend the pattern to the data layer, where mistakes cause data loss rather than a 502 error. A strong answer must address replication topology, schema transformation, cutover atomicity, rollback fencing, and post-migration cleanup.
Design assumptions for this answer
- Source database: PostgreSQL 13, 800 GB logical size, 4,200 tables
- Target database: PostgreSQL 16 with a restructured schema (30% of tables modified)
- Write throughput: 12,000 writes/sec sustained, 45,000 writes/sec at peak
- Read throughput: 85,000 reads/sec sustained
- Acceptable cutover window: under 30 seconds of write unavailability
- Rollback window: 72 hours post-cutover
- Replication lag budget: under 2 seconds steady-state, under 500ms at cutover
These are explicit design assumptions for capacity planning, not claims about any specific company's production metrics.
Key Highlights
- •Blue-green DB migration adds schema transformation, CDC pipeline management, and atomic cutover on top of standard blue-green deployment.
- •The four phases are Provision & Sync, Validate, Cutover, and Cleanup.
- •Write throughput assumption: 12K writes/sec sustained, 45K at peak.
- •Cutover must complete in under 30 seconds of write unavailability.
- •Rollback capability must persist for 72 hours post-cutover.
Section Rescue Kit
Buzzwords to use:
Safe statements:
- "Let me separate the application-level blue-green pattern from the data-layer extension before diving into implementation."
- "The key insight is that we need continuous replication between divergent schemas, not just a point-in-time copy."