System Design HLD-07.08
Zero-downtime schema and data migrations
Changing schemas and moving data while serving traffic: expand and contract, dual writes, backfills, shadow reads, cutover and rollback.
The big idea
Sooner or later you must change data that is in constant use: rename a column, split a table, change a field's format, move a table to a new database or reshard it. The tempting approach, a maintenance window where the site goes down while a script runs, stops being acceptable once you have real users, and a script that runs for days cannot finish in any window. Large systems instead migrate online, while every read and write keeps working.
The core technique is expand and contractMaking a breaking change in safe steps: first add the new structure alongside the old (expand), migrate traffic and data to it, then remove the old structure (contract).: never change anything in place. Add the new structure next to the old one, make the system work with both, move data and traffic across in small reversible steps while checking the results, and only remove the old structure at the very end. At every step you can stop or roll back.
Think of replacing a bridge. You do not close the road and rebuild in place. You build a new bridge alongside, open it to a few cars, then all traffic, and demolish the old one only once nobody needs it.
How it works
Step 1: The phases
- Dual write Deploy code that writes every change to both the old and the new structure. Reads still come from the old one, so nothing user-visible changes yet.
- Backfill and verify Copy historical data into the new structure in throttled batches. Then run shadow reads: read both, compare, log differences, and fix bugs until they match.
- Switch reads Move reads to the new structure gradually, behind a feature flag, starting with a small percentage. Keep dual writing so you can switch back instantly.
- Contract Once the new structure has served all reads for a while, stop writing the old one, then drop it. Only this last step is hard to undo, so it comes last.
Step 2: Doing each phase safely
- Dual writes: the old write stays authoritative; failures writing the new structure are logged and repaired rather than failing the request. For moving data between databases, it is often safer to feed the new store from the old one's change log (change data capture) than to write both from the application ( Transactional outbox and change data capture ).
- Backfill: process history in small batches with throttling, so the migration never competes with real traffic. Make it idempotent and resumable: a backfill that crashes at 60 percent should continue, not restart.
- Verify: compare old and new for every read for a while (shadow reads), and run reconciliation jobs over the whole dataset. Disagreements are bugs to fix before cutover.
- Cutover: use feature flags to switch reads per percentage of traffic or per tenant, and watch error rates and latency.
- Rollback: until the contract step, rolling back is just flipping the flag back.
Step 3: Schema changes in one database
The same pattern handles a column rename, which done naively breaks every running instance of the old code: add the new column (expand), write both, backfill, switch reads, stop writing the old column, drop it (contract). Each step is its own deploy. On large tables, use online schema change tools, because a plain ALTER TABLE can lock the table for a long time.
Going deeper
Deploys and migrations must overlap safely
During a rolling deploy, old and new versions of your code run at the same time. Every schema state must therefore work with at least two consecutive code versions. That is the real reason for small steps: each deploy only has to be compatible with the schema before and after it.
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Maintenance window | Small datasets and internal tools where downtime is acceptable | Large data, global users, anything that must stay up |
| Expand and contract online | Production systems of any size: reversible, observable, no downtime | Time and engineering effort: weeks of careful steps |
| Change data capture into the new store | Moving between databases without application dual writes | Extra infrastructure and a short replication delay |
Common mistakes
- Renaming or dropping a column in one step while old code is still running.
- Backfilling at full speed and overloading the production database.
- Switching reads without first verifying that old and new agree.
- Dropping the old structure immediately after cutover, removing the only rollback path.
In the wild
Stripe
Online migrations at scale (opens in a new tab)Jacqueline Xu's 2017 post describes how Stripe moved hundreds of millions of Subscription objects out of Customer records into their own table without downtime, using a four-step dual-writing pattern: dual write to the existing and new tables, change all read paths to the new table, change all write paths to only the new table, then remove the old data. The post notes that migrating one object per second sequentially would take over three years for a hundred million objects, so they used Hadoop jobs (via Scalding) to process data offline, and GitHub's Scientist library to run old and new code paths side by side in production and alert whenever results differed.
Check yourself
-
What is the first step of expand and contract?
Show answer
B. Add the new structure alongside the old and start writing both Expand first: nothing is removed until the new structure is proven.
-
Why run shadow reads before switching reads?
Show answer
B. To compare old and new results and catch migration bugs safely Comparing both paths in production reveals mismatches before users see them.
-
Why must a backfill be throttled and resumable?
Show answer
B. So it does not overload production and can continue after a crash Backfills run for hours or days alongside real traffic.
-
Which step is hardest to undo, and so comes last?
Show answer
D. Dropping the old structure Once the old data is gone, there is no instant rollback.
Recap
- Large systems migrate online, never with long maintenance windows.
- Expand and contract: add the new, dual write, backfill, verify, switch reads, then remove the old.
- Throttle and make backfills resumable; verify with shadow reads and reconciliation.
- Cut over gradually with feature flags; keep rollback cheap until the end.
- Each schema state must work with two consecutive code versions.
Next up
- Crack the system design round learning path