Design a Zero-Downtime Database Migration System
Scenario prompt
A platform team needs to migrate a large, actively-written production table off an aging database (schema change, engine change, or both) without a maintenance window. Requirements:
- Reads and writes against the table must keep working throughout the migration, with no dropped or lost writes
- The cutover must be reversible at any point before it's declared final — a bad migration can't be a one-way door
- The old and new stores must stay consistent with each other for the full duration of the migration, which could run for days or weeks on a large table
- Application code deployed by multiple teams reads and writes this table, and can't all be coordinated to deploy at the exact same instant
- The team needs a way to prove correctness (the new store actually matches the old one) before cutting over, not just hope
Mihir's attempt
[!todo] Write your own attempt here before reading the model solution below — how you'd sequence the phases, and what you'd do if you discover a data mismatch between old and new stores midway through.
Model solution
Expand/contract, not a single atomic cutover. The migration proceeds in phases: first expand (add the new store/schema alongside the old one, without removing anything the old code depends on), then migrate (backfill and dual-write), then contract (remove the old store once the new one is proven correct and every consumer has moved over). This is the same discipline as the Strangler Fig Pattern applied to data instead of services — replace incrementally behind a stable interface, never a single flag-day cutover that has to work perfectly or not at all.
Dual writes plus backfill, reconciled continuously. New writes go to both the old and new store (synchronously, or via Change Data Capture (CDC) streaming changes from the old store into the new one) while a backfill job copies historical rows in the background. Because dual writes and backfill can race — a backfill row overwriting a newer dual-write, or a dual write failing on one side — the system needs an explicit reconciliation pass that diffs the two stores and reports (or repairs) mismatches, rather than assuming the copy is correct because the job completed without error. CDC is generally the more robust of the two dual-write mechanisms: it decouples the migration's correctness from every application's write path being updated correctly and simultaneously, since it reads off the database's own change log rather than trusting each caller to remember to write twice.
Idempotent, safely-replayable writes on both paths. Because CDC replay, backfill retries, and reconciliation repairs can all reapply the same logical write more than once, every write into the new store needs to be safe to apply twice — the same requirement underpinning Idempotency Keys elsewhere in this vault. Without that property, a retried backfill chunk or a replayed CDC event silently corrupts the very store the migration is trying to validate.
Reads cut over gradually and independently of writes, behind a flag. Once the new store is verified consistent, reads shift over service-by-service (or percentage-by-percentage) behind a runtime flag — not a global switch every team has to coordinate a deploy around. This decouples the migration's timeline from any single team's deploy schedule, and because dual writes are still happening, a read-path regression can flip straight back to the old store with no data loss, keeping the whole migration reversible up until the old store is actually decommissioned in the contract phase.
Correctness has to be measured, not assumed. Before contract, an automated comparison job samples (or fully scans, for tables small enough) both stores and reports drift — row counts, checksums, or field-level diffs — on an ongoing basis during the migration window, not just once at the end. A migration that "looks done" because the backfill job exited zero is not the same as a migration that's been proven correct against live, concurrently-written data.
Gaps to revisit
- How long does dual-write overhead (extra write latency, doubled infrastructure cost) stay acceptable on a multi-week migration of a very large table, and when does that budget run out?
- What's the reconciliation strategy when the old and new stores have genuinely different consistency models (e.g., migrating from a strongly consistent primary to an eventually consistent distributed store) — some drift is expected, not a bug, and the system needs to tell those apart
- How do you handle schema changes that aren't purely additive (a column type change, a normalization/denormalization) where the "same row" doesn't have an obvious 1:1 mapping between old and new?
- Rollback after contract: once the old store is decommissioned, is there still a recovery path if a correctness issue surfaces weeks later, or is that the point of no return?
Principal Engineer Lens
This is one of the purest tests of "no big-bang cutovers" thinking at Principal scope, because the stakes of getting it wrong (silent data loss or corruption in a production system of record) are much higher than a typical stateless-service deploy. The core judgment call — dual write vs. CDC, and how aggressively to gate the read cutover — is exactly the kind of tradeoff that needs to be defensible in an architecture review: CDC costs more setup complexity but decouples correctness from every application team's discipline, which is usually the right trade once more than one or two services touch the table. It's also a good test of whether someone treats "the migration completed" and "the migration is correct" as the same claim — a Principal Engineer should instinctively ask for the reconciliation evidence, not just the job's exit code.
Reel Script
Setup: A team needs to move a table that's under constant read and write load, off its current database, without a maintenance window and without losing a single write.
Concept walkthrough: Walk through the expand/contract phases — add the new store, dual-write and backfill while reconciling continuously, then gradually cut reads over behind a flag, and only then remove the old store. Emphasize that dual writes and backfill can race each other, so every write has to be idempotent and safe to replay.
Real example tie-in: Contrast application-level dual writes (simple, but every team's write path has to be updated correctly) against CDC-based replication off the database's own change log (more setup, but correctness no longer depends on every caller remembering to write twice) — this is the same tradeoff shape as choosing between service-level and infrastructure-level enforcement anywhere else in the stack.
Tradeoffs & alternatives: A flag-day cutover is simpler to reason about on paper but is a one-way door the moment something's wrong at 2am with a live table. Expand/contract costs weeks of dual-running infrastructure and reconciliation tooling, but keeps the migration reversible at every step — which is the whole point when the thing being migrated is a system of record, not a stateless service.
Principal Engineer takeaway: "The job finished" and "the data is correct" are different claims, and conflating them is how migrations quietly corrupt production — always ask for the reconciliation evidence, not the exit code.