Zero-Downtime Database Migrations — Expand/Contract, Dual Write & Online DDL
Zero-downtime migrations change schema and data paths without taking the app offline. Expand first and contract only after every reader and writer is gone. Online DDL bounds lock time. Dual-write, an idempotent backfill, and a feature-flag rollback cover moves to a new table or store.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Most product databases
Prefer
Expand / contract
Add the new shape, move traffic while both shapes exist, drop the old shape only after every reader and writer is gone.
- Each step is reversible at the application layer.
- Rolling deploys already run two binaries. The schema has to tolerate that overlap.
- Online DDL bounds how long locks are held while you add indexes or rebuild.
Alternative
Big-bang maintenance window
Stop writes, migrate, restart. Fine when the database is tiny and downtime is an approved plan.
- One irreversible shot. If it runs long, you are already in the outage.
- Wrong for a hot path, multi-region reads, or an SLO that forbids a freeze.
Overview
Zero-downtime migrations change schema, or the path data takes, without taking the app offline. The discipline is expand/contract (also called parallel change): additive schema first, remove the old shape only after every reader and writer is gone. Pair that with online DDL so indexes and table rewrites do not hold an exclusive lock for the whole operation. When the shape of the data moves — a new table, a new store, a split column — add dual-write / dual-read, an idempotent backfill, and a reconcile step before you cut traffic. Every phase needs an explicit rollback. That rollback is usually a feature flag that flips reads back, not a restore from backup.
This cluster is about shipping schema change safely under load. It does not re-teach how an engine stores rows or which versions a snapshot can see. Use Database storage engines when the question is pages, the write-ahead log, or compaction. Use MVCC, snapshot isolation, and write skew when the question is visibility, anomalies, or vacuum. Use this cluster when the question is how you rename, split, or move data without a maintenance window.
By the end of this hub you should be able to:
- Pick expand/contract, dual-write, or a blue/green database from the shape of the change
- Refuse a rename or a drop in the same deploy as code that still needs the old shape
- Name what online DDL does not save you from: lag, disk, long transactions, and half-migrated reads
- State the rollback as a flag flip while the old path is still being written
Flow
- 1
1. Name schema or data-path change
- next2. Expand additive shape first
- 2
2. Expand additive shape first
- next3. Deploy dual-compatible code
- 3
3. Deploy dual-compatible code
- next4. Backfill, checksum, shadow read
- 4
4. Backfill, checksum, shadow read
- next5. Flag cutover, rollback is the flag
- 5
5. Flag cutover, rollback is the flag
- next6. Contract only after soak
- 6
6. Contract only after soak
Lesson map
Zero-Downtime Database Migrations — Expand/Contract, Dual Write & Online DDL
Zero-downtime migrations change schema and data paths without taking the app offline. Expand first and contract only after every reader and writer is gone. Online DDL bounds lock time. Dual-write, an idempotent backfill, and a feature-flag rollback cover moves to a new table or store.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1. Name schema or data-path change"] b["2. Expand additive shape first"] c["3. Deploy dual-compatible code"] d["4. Backfill, checksum, shadow read"] a -->|1. Name schema or data-path change| b b -->|2. Expand additive shape first| c c -->|3. Deploy dual-compatible code to 4.| d
Default path under load
Branches differ. The order does not: expand, migrate, switch, then contract.
- 1
Expand the schema
ADD COLUMN nullable, a new table, or an online index. Old code ignores what it does not know. - 2
Deploy dual-compatible code
Writers and readers understand both shapes before you depend on the new one. - 3
Migrate and prove it
Backfill history. Dual-write the live head. Checksums and shadow reads beat a row-count that happens to match. - 4
Switch reads with a flag
Canary, then 100 percent. Rollback sets the flag back. Do not restore a backup to undo a read switch. - 5
Contract in a later release
Drop the old column, table, or path only after metrics show nothing still reads or writes it.
Which question this cluster answers
| Question shape | Go to |
|---|---|
| How does InnoDB or Postgres lay out pages, the log, or compaction? | Storage engines |
| What can concurrent transactions see? Isolation anomalies? Vacuum? | MVCC and snapshot isolation |
| How do I rename a column, split a table, or add NOT NULL without downtime? | This cluster, starting with expand/contract |
| How do I move rows to a new table or store while serving traffic? | Dual write and backfills |
| What if cutover fails? | Rollback and feature flags |
Log-based capture and the transactional outbox pattern are a different cluster. Dual-write may record durable intent in an outbox. The connector, slot, and relay lessons stay on CDC and the transactional outbox page.
Which migration path
Rule of thumb: never rename or drop in the same deploy as the code that still needs the old shape. Never big-bang a type change under an exclusive lock if the table is hot.
Flow
- 1
1. Nullable column: expand then contract
- next2. Rename: new name, dual-write, drop
- 2
2. Rename: new name, dual-write, drop
- next3. Bad type: new column, cast, shadow
- 3
3. Bad type: new column, cast, shadow
- next4. Split table: dual-write and checksum
- 4
4. Split table: dual-write and checksum
- next5. Engine move: logical replica cutover
- 5
5. Engine move: logical replica cutover
| Change | Path | Then |
|---|---|---|
| Additive column, nullable or a safe default | Expand: add the column, deploy dual-compatible code, contract later | Online DDL if an index or rewrite is required |
| Rename a column or table | Add the new name, dual-write both, backfill, switch reads, drop the old name in contract | Same online-DDL gate |
| Incompatible type change | New column or table, dual-write, cast during backfill, shadow read, then cutover | Dual-write and backfill, not a blocking ALTER |
| Split or merge a table | Create the target, dual-write, chunked backfill, reconcile checksums, flip reads | Dual-write and backfill |
| Storage engine or major version | Logical replica or a blue/green database | In-place online DDL alone is the wrong tool |
Online DDL, when you need it, still ends at the same place: monitor locks, replica lag, and disk, then cut over behind a flag. Rollback is the flag, not a restore.
Strategies
| Strategy | How it works | Wins when | Loses when |
|---|---|---|---|
| Big-bang maintenance window | Stop writes, migrate, restart | Tiny database, scheduled downtime is acceptable, the change is a short one-shot | Hot path, multi-region, the SLO forbids a freeze |
| Expand / contract | Additive schema, migrate traffic, remove the old shape | Most OLTP schema evolution; rollback is code | Needs multi-PR discipline; impatient teams skip contract |
| Dual-write and dual-read | App (or an outbox) writes old and new; flip reads | Splitting stores, new tables, denormalized paths | Partial write failure, ordering bugs, double write cost |
| Blue / green database | New instance or cluster, sync, cut DNS or the proxy | Engine upgrades, major versions, a storage move | Sync lag, dual cost, a cutover blast if you skip checksums |
Expand/contract beats big-bang for most product databases because you keep serving traffic, each step reverses at the application layer, and online DDL tools bound lock time: CREATE INDEX CONCURRENTLY, gh-ost, and pt-online-schema-change. Big-bang still wins for a tiny internal tool, or when the change is inherently irreversible and short and downtime is approved.
What each sibling owns
- Hub — this page. Pick the path.
- Expand/contract — additive schema, dual-compatible code, contract after soak.
- Online DDL — locks,
CONCURRENTLY, gh-ost, and pt-osc. - Dual write / dual read — moving a data path, partial failure, outbox at the policy level.
- Backfills and reconciliation — keyset jobs, checksums, shadow reads.
- Rollback, flags, and cutover — instant reverse without data loss, and the irreversible zone.
Interview Q&A
How do you rename a column with zero downtime?
Answer
Do not RENAME COLUMN as a single step while old code still runs. Expand: add the new column, deploy writers that set both (or derive the new value from the old), backfill nulls, deploy readers that prefer the new column with a fallback, stop writing the old column, then contract. Drop the old column in a later release after metrics show zero reads of that shape.
When do you use expand/contract, and when a blue/green database?
Answer
Expand/contract is in-place schema evolution on the same primary. Blue/green is for an engine change, a major version, or a clean instance: logical replication or dump-and-restore, then cutover. Mixing them without a sync plan doubles the risk. In-place online DDL does not replace a version upgrade.
What does online DDL not save you from?
Answer
Replication lag, disk amplification from a temp copy, long-running transactions that block CREATE INDEX CONCURRENTLY, application bugs from reading half-migrated rows, and an irreversible data transform that has no dual-read safety net. "Online" means the lock is short. It does not mean the operation is free.
Dual-write failed on the new store. What now?
Answer
Decide the policy before you code it. Fail the request (stronger consistency, higher error rate) or succeed on the old store and enqueue a repair (eventual, needs a lag SLO). Never silently succeed on one side with no reconciliation path. Prefer an outbox so the second write is durable intent, not a best-effort second RPC. The relay itself is the outbox lesson, not this hub.
How do you know a backfill is done?
Answer
The resume token is at the end of the keyspace, row counts are inside tolerance, and checksums or hash samples (chunk hashes, or a Merkle-style tree for a huge table) agree. Shadow reads stay under the mismatch SLO for a soak window. A green row count with a red checksum is not done.
Why is DROP COLUMN dangerous in the same deploy that removes the code?
Answer
Rolling deploys mean old pods still SELECT the column. Dropping it mid-rollout returns errors. Contract only after every instance and job has stopped referencing the old shape. That is often one release later than you think: ORMs, reports, and replicas.
Postgres CREATE INDEX CONCURRENTLY failed halfway. Are you safe?
Answer
You can be left with an INVALID index. Query pg_index.indisvalid, drop the invalid index, fix the cause (a deadlock, a unique violation, an old snapshot), and retry. Do not assume a failed CONCURRENTLY build left nothing behind. Details are on the online DDL page.
Why does expand/contract beat a freeze for most product databases?
Answer
You keep serving traffic. Each step reverses in application code. Online DDL bounds lock time. A freeze wins only when the database is tiny, one maintainer window is approved, or the transform is short and inherently irreversible.
What fails if you choose the wrong strategy?
Answer
An exclusive ALTER on a hot, large table stalls writers, piles up lock waits, lags replicas, and can thrash failover. Dual-write without idempotent upserts and a reconcile job diverges quietly. A blue/green cutover that skips checksums moves the outage to the moment you flip the proxy.
Where do storage engines and MVCC fit?
Answer
They answer different questions. Storage engines: how the bytes are laid out and recovered. MVCC: what a concurrent transaction is allowed to see. This cluster: how you change the schema or the data path while those engines keep serving. Name the sibling cluster and come back to phasing, locks, and the flag.
Pitfalls
- Renaming or dropping in the same deploy as code that still reads the old name.
- Adding
NOT NULLwith no default on a populated hot table as one statement. - Treating online DDL as a substitute for dual-compatible application code.
- Cutting reads to the new store before checksums and shadow reads are inside the SLO.
- Rolling back by restoring a backup while the old path was already stale.
- Skipping contract forever, so orphaned columns and dual-write branches rot.
A product table has 200 million users. You must rename verified_at to a boolean, and separately you must move from MySQL 5.7 to a new major version. Say which change is expand/contract on the same primary, which is blue/green, and which lock you refuse to hold for the whole job.
Go deeper
Public references only. The cards on this page link the Postgres concurrent-index docs, the MySQL online DDL matrix, gh-ost, pt-online-schema-change, Martin Fowler's parallel change, Stripe's online migrations write-up, and Shopify's notes on schema migrations while scaling Shop onto Vitess.
Interviewers care that you phase the change, measure lag and mismatches, and roll back with a flag. They do not care that you memorized one ALTER syntax. Next: Expand/Contract — additive schema without downtime.