Zero-Downtime Database Migration Architecture (2026)

Q: What is the expand-contract (parallel change) pattern for database migrations?

Expand-contract, also called parallel change, is a way to evolve a database schema in small, backward-compatible steps so the application never goes down and every step is reversible. Instead of one risky "ALTER TABLE then deploy new code" operation, you expand the schema additively (add the new column or table, nullable and unconstrained), deploy code that writes to both the old and new shapes while still reading the old, backfill historical rows into the new shape in background batches, verify the new shape matches the old, switch reads to the new shape, stop writing the old, and finally contract by dropping the old column or table and adding any constraints. The governing invariant is that at every moment the schema is compatible with both the currently running code and the next version, which is essential because rolling deploys run old and new code simultaneously. A rename becomes seven boring, independently deployable steps instead of one outage-prone change, and you can pause or roll back at any point before the final contract.

Q: Why does an in-place ALTER plus a code deploy cause downtime?

Because it couples two dangerous things — a potentially locking schema change and a code cutover that depends on it — into a single moment with no safe intermediate state. On a large table, a plain ALTER TABLE can take a lock or rewrite the whole table for minutes, during which queries block and the application effectively stalls. Worse, you are deploying new code that expects the new schema at the same time, so during a rolling deploy the old still-running instances see a schema they do not understand, or the new instances see a schema that is not finished migrating, and either way requests error. There is also no clean rollback: if the migration fails halfway you may have a partially altered table and a half-deployed application, which is exactly the torn state that turns a routine change into an incident. Expand-contract removes the coupling by making the schema change additive and separate from the code cutover, so the lock risk, the deploy, and the read-switch are independent reversible steps rather than one all-or-nothing event.

Q: How should I backfill historical data without impacting production?

Run the backfill as a throttled, resumable background job, never as a single large UPDATE. A single statement over hundreds of millions of rows takes a long lock and saturates I/O, stalling live traffic, so instead you process in batches — commonly 5,000 to 10,000 rows per batch — with a short pause between batches to leave headroom for production queries, and you tune the batch size and delay against the database load you observe. The job must be idempotent so it can crash and resume without double-applying, typically by tracking the last processed key and using upserts rather than blind inserts. Because the application started dual-writing when the new shape was added, only rows that predate the dual-write need backfilling, and new rows are already correct in both shapes. A backfill of a very large table legitimately runs for hours or days, and that is fine: the application is serving the entire time, the migration makes no user-visible change until you switch reads, and you verify correctness before that switch. The key disciplines are batch, throttle, make it resumable, and monitor database load while it runs.

Q: How do I migrate a huge table without locking it?

Use lock-light DDL where the database supports it, and shadow-table tooling where it does not. On PostgreSQL, modern versions let you add a column without a volatile default instantly, create indexes with CREATE INDEX CONCURRENTLY so writes are not blocked, and add constraints as NOT VALID and then VALIDATE them in a separate non-blocking scan, so the expensive parts never hold a long lock. On MySQL and at very large scale generally, use an online schema-change tool such as GitHub gh-ost or Percona pt-online-schema-change: these build a hidden copy of the table with the new schema, continuously stream changes into it via the binlog or triggers while the original stays live, and then swap the copy in atomically with a sub-second cutover. The same principle applies to the cutover itself — keep it to an instant, atomic rename or swap, never a long write-locking operation — and ensure the rollback is equally instant. The combination of lock-light DDL for the additive expand and contract steps, plus a streamed shadow table for full rewrites, is what lets you change multi-hundred-million-row tables with no perceptible impact.

Q: Why is verification before switching reads so important?

Because a backfill bug is silent: the new column or table looks populated, but some rows are wrong, missing, or stale, and if you switch reads to it without checking, customers see corrupted data and you find out from support tickets rather than from your own monitoring. Verification closes that gap with a shadow-read phase: before flipping reads to the new shape, the application reads both the old and the new shape for the same records and compares them, logging any divergence, so you accumulate evidence that the new path is correct across real production traffic and edge cases the backfill might have missed. You treat any mismatch as a release blocker and fix the backfill or dual-write logic before proceeding, exactly as you would treat a failing test. This step is cheap relative to the cost of a data-corruption incident, and it is the reason the destructive contract step comes last and only after the new path has soaked in production: you do not drop the old shape until verification and live reads have proven the new one trustworthy.

Q: How do rollbacks work in a zero-downtime migration?

Rollback works because every step is designed to be independently reversible up until the final contract, and the old shape keeps existing and being written for the whole transition. If the read-switch step misbehaves, you redeploy the previous version that reads the old shape and you are immediately safe, because dual-write means the old shape is still current. If the dual-write code has a bug, you revert it and the system falls back to the old shape alone. The expand step (adding a nullable column or a new table) is harmless to leave in place even if you abandon the migration, and the backfill can simply be stopped. The one irreversible step is contract — dropping the old column or table — which is precisely why it comes last and only after the new path has run in production long enough to trust. To make the read-switch reversible without even a redeploy, gate it behind a feature flag so you can flip back instantly. The overall design treats the migration as a series of small reversible doors rather than one one-way door, which is what makes running it on live production data safe.

Q: How do I coordinate schema changes with rolling application deploys?

The coordination rule is that the schema change always lands before the code that depends on it, and the destructive change always lands after the code that depended on the old shape is gone. Concretely: the expand (add new shape) deploys before the code that writes to it; the dual-write code deploys after the new shape exists; the read-switch deploys only after backfill and verification; and the contract (drop old) deploys only after no running code reads or writes the old shape. This ordering respects the reality of rolling deploys, where old and new application instances run simultaneously for minutes, so the schema must satisfy both at once. Migrations should run as separate, observable jobs rather than inside the application boot path, because a long migration in startup stalls the rollout and couples deploy health to migration health. Feature-flagging the read switch lets you decouple the behavioural cutover from the deploy entirely, flipping reads to the new shape (and back) without shipping new code. Done this way, schema evolution becomes a sequence of ordinary deploys, each safe on its own, rather than a coordinated big-bang event the team has to choreograph and fear.

Q: What is a sensible maturity path for database migrations?

Stage 0 is running schema changes by hand in a scheduled maintenance window, which accepts downtime and is fragile and unrepeatable. Stage 1 puts migrations in version control and CI but still applies them in place and couples them to deploys, which works fine until a table grows large or a deploy fails mid-cutover and causes an incident. Stage 2 adopts expand-contract discipline as the default: additive steps, dual-write, batched resumable backfill, and dropping the old shape last, so routine schema changes ship with no downtime and full reversibility. Stage 3 adds online-DDL tooling for large tables — Postgres lock-light patterns or gh-ost/pt-online-schema-change — together with shadow-read verification and feature-flagged read switches for instant rollback, so even multi-hundred-million-row changes are safe. Stage 4 treats migrations as first-class, observable jobs with automated verification gates, a soak period before the contract step, and audit evidence for compliance regimes, so schema evolution is a routine, reversible, zero-downtime operation rather than a dreaded event. Most teams sit at Stage 1 and discover its ceiling during an incident on their biggest table; teams that never schedule downtime operate at Stage 2 or above as a matter of course.