System designDeep dive11 min
Zero-downtime migrations
A schema change on a busy table can stop every request for minutes, even when the change itself takes milliseconds. Changing a live database safely means knowing which statements lock what, breaking incompatible changes into compatible steps, copying data in small batches, and keeping every step reversible until the last one.
250 million reservations in one busy table
A car-rental company keeps 250 million reservations in one PostgreSQL table, read and written all day by 40 app servers.
1 · The problem
A one-millisecond schema change can stall a table for minutes
A car-rental company keeps 250 million reservations in one PostgreSQL table. Forty app servers read and write it all day. An engineer adds a column for loyalty tiers, a change that only updates the table's metadata and finishes in milliseconds. The site stops taking bookings for six minutes anyway.
Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which conflicts with every other lock, including the light one a plain SELECT holds. At that moment an analyst's report was reading reservations, with six minutes still to run. The ALTER waited for it, and PostgreSQL queues lock requests, so every query that arrived after the ALTER waited behind it:
The booking queries don't conflict with the report, only with the waiting ALTER. Until the report ends and the ALTER runs, the table is effectively closed to reads and writes.
The fix is to refuse to wait. Set lock_timeout before any schema change, and retry when it fires. A migration that gives up after two seconds blocks traffic for at most two seconds; retrying a few seconds later usually finds a gap between long queries.
Fail fast instead of queueing
SET lock_timeout = '2s';
ALTER TABLE reservations
ADD COLUMN loyalty_tier text NOT NULL DEFAULT 'basic';
-- If the lock times out: wait a few seconds and run it again.2 · The locks
Know what each change locks, and for how long
Lock type is half the story; duration is the other half. A change that holds an exclusive lock for a millisecond is safe; one that holds it while rewriting 250 million rows is an outage. In PostgreSQL:
Change | What happens | Safe on a busy table? |
|---|---|---|
Add a nullable column, or one with a constant default | Metadata only; the default is stored, not written to each row | Yes, with lock_timeout |
Add a column with a volatile default, such as clock_timestamp() | Rewrites the table and its indexes | No |
Change a column's type | Usually rewrites the table and indexes | No; use a new column |
CREATE INDEX | Blocks writes until the index is built | No |
CREATE INDEX CONCURRENTLY | Two table scans; writes continue | Yes, slower |
Add a CHECK or foreign key | Scans every row while blocking writes (and reads too, for a CHECK) | Use NOT VALID, then VALIDATE |
Two techniques do most of the work:
- Build indexes concurrently. It takes more total work and can't run inside a transaction. If it fails, say on a duplicate in a unique index, it leaves an invalid index that queries ignore but every write still maintains, so drop it and try again.
- Split constraints into add and validate.
ADD CONSTRAINT … NOT VALIDskips the scan and enforces the rule on new writes only.VALIDATE CONSTRAINTthen checks the old rows under a weaker lock that lets normal updates continue.
The same split makes a column NOT NULL cheaply. Add CHECK (pickup_at IS NOT NULL) NOT VALID, validate it, and then SET NOT NULL: PostgreSQL skips its full-table scan when a valid check constraint already proves there are no nulls.
MySQL's rules differ. In MySQL 8.4, adding, dropping or renaming a column can be done instantly, as a metadata change. Adding a secondary index is done in place while reads and writes continue. Changing a column's type copies the whole table and blocks writes, which is where the copy-and-swap tools later on this page come in.
3 · Expand and contract
An incompatible change becomes expand, migrate and contract
The fleet deploys in rolling waves, so for a while old and new code run side by side against the same table (Schema evolution & encoding). Code also deploys separately from the schema. So no single step may break the code on either side of it.
The next change is a real one. pickup_time holds local time with no zone, and pickups across time zones keep going wrong. It should become pickup_at, a timestamptz. Renaming and retyping in place would break every server still running old code. Expand and contract does it in compatible steps:
- Expand the schema
Add pickup_at as a nullable column. Instant, and invisible to the running code.
- Write both
Deploy code that writes pickup_time and pickup_at on every insert and update. Reads still use pickup_time.
- Backfill
Fill pickup_at for existing rows, in batches (next section).
- Switch reads
Deploy code that reads pickup_at. Verify, then add the NOT NULL rule with the check-constraint split.
- Stop writing the old column
Deploy code that ignores pickup_time completely.
- Contract
Drop pickup_time, only once no running code refers to it.
After step | Schema | Code that may be running | Roll back by |
|---|---|---|---|
Expand | Both columns | Old | Dropping the empty column |
Write both | Both columns | Old and dual-writing | Redeploying old code |
Switch reads | Both, filled | Dual-writing, reading new | Redeploying dual-write code |
Contract | New column only | New | Restore from backup |
Every step before the contract can be undone by a deploy, which is the point. The ordering rule behind it: changes that add to the schema run before the code that needs them; changes that remove run after the last code that uses them is gone. Some frameworks cache the column list at startup, so GitLab, for example, first deploys code that ignores a column and only drops it in the following release.
4 · The backfill
Backfill in small batches, not one giant update
Filling pickup_at takes one statement: UPDATE reservations SET pickup_at = … over every row. On a live table that statement is a problem:
- It locks every row it touches until it commits. A customer changing a booking waits until the whole update finishes.
- It writes 250 million rows in one burst, and replicas fall behind replaying it.
- If it fails at row 240 million, it rolls back everything.
Instead, a background job walks the table by primary key, a few thousand rows per transaction:
One batch of the backfill
-- The job repeats this with the next id range until it passes the max id
UPDATE reservations r
SET pickup_at = r.pickup_time AT TIME ZONE b.time_zone
FROM branches b
WHERE b.id = r.branch_id
AND r.id >= :start AND r.id < :start + 5000
AND r.pickup_at IS NULL;Each batch locks 5,000 rows for a fraction of a second, and the pickup_at IS NULL condition makes reruns harmless: a batch that already ran changes nothing (Idempotency). The job records its position, so a crash resumes where it stopped.
Quantity | Value |
|---|---|
Rows | 250,000,000 |
Rows per batch | 5,000 |
Batches | 250,000,000 ÷ 5,000 = 50,000 |
Time per batch, with a 100 ms pause | About 250 ms (assumed) |
Total | 50,000 × 0.25 s = 12,500 s ≈ 3.5 hours |
The pause between batches is the throttle. Watch replica lag and database load and lengthen the pause, or stop, when either climbs; gh-ost and pt-online-schema-change both pause on replica lag. A backfill that takes a calm afternoon beats one that takes ten minutes and pages the on-call engineer. Meanwhile, dual writes keep new and changed rows up to date, so the backfill only has to catch up the past.
5 · Copy and swap
When a change rewrites the table, build a copy and swap it in
Some changes have no cheap path, such as changing a column type in MySQL, which copies the table and blocks writes. Online schema change tools such as gh-ost and pt-online-schema-change do the copy themselves, without blocking:
The ghost table is created empty, altered while empty, then filled with copied rows plus every change made meanwhile. At cut-over it takes the original's name in one atomic rename.
The tools differ in how they follow ongoing changes. pt-online-schema-change installs triggers on the original table, so every application write also writes the copy, inside the same transaction. gh-ost reads the binary log instead, which lets it pause completely: when it throttles, the extra write load really stops, which triggers can't do.
The cut-over is the delicate moment. Renaming the old table away and the new one into place as two statements leaves a gap in which the table doesn't exist and queries fail. Both tools swap with a single atomic rename instead, and gh-ost can postpone the cut-over until someone is around to watch it.
6 · Moving data
Moving data to a new table or store is the same dance, verified by comparison
The rental company also wants to move add-ons, child seats and GPS units, out of a JSON column on reservations into their own reservation_extras table. The same four-step pattern applies at any scale, from a new table to a new database:
- Dual write
Every write goes to the old place and the new one, and a backfill copies the history.
- Move the reads
Switch read paths to the new table, one at a time, after checking they agree.
- Move the writes
Stop writing to the old place.
- Remove the old data
Drop the JSON column once nothing reads or writes it.
Before trusting the new table, compare it with the old one under real traffic. A shadow read runs both reads, returns the old answer to the user, and reports any difference:
The customer sees the old, trusted answer. The mismatch points at a bug, here a missed GPS, while switching reads is still a configuration change away.
Stripe moved millions of subscriptions between tables this way, using GitHub's Scientist library to run both code paths and alert on differences. To find every object needing migration without loading the production database, it ran the search as an offline job over database snapshots. Moving a feature into a separate service follows the same shape (Monolithic vs microservices), and copying a table can also be done by following its change log (Change data capture).
7 · In practice
Additive first, destructive last, reversible in between
Most of the risk sits at two ends: a change that takes a long lock, and a change that destroys something. Everything in between can be rolled back by a deploy.
Goal | Unsafe way | Safe way |
|---|---|---|
Add a column | With a volatile default | Nullable or constant default, with lock_timeout |
Add an index | CREATE INDEX | CREATE INDEX CONCURRENTLY |
Add a constraint | ADD CONSTRAINT | NOT VALID, then VALIDATE |
Rename or retype | In place | Expand, dual write, backfill, switch, contract |
Fill a column | One UPDATE | Batches by key, throttled on replica lag |
Drop a column | In the same deploy as the code change | After the last code using it is gone |
For the rental company, the checklist:
- Set lock_timeout on every migration, and retry instead of waiting.
- Know each statement's lock and duration before running it on a large table.
- Run additive changes before the code, destructive ones after, in separate deploys.
- Backfill in small, idempotent, resumable batches, throttled on replica lag.
- Verify with comparisons before switching reads.
- Rehearse on a production-sized copy, since a rewrite that takes a second on a test table can take an hour on 250 million rows.
- Keep a backup ready before the contract step; it is the only step a deploy can't undo (Disaster recovery & backups).
Check yourself
5 questions
Takeaways
Remember this
- Most DDL needs an exclusive lock; a waiting lock blocks everything behind it, so always set lock_timeout and retry.
- Prefer changes that touch only metadata, build indexes concurrently, and add constraints as NOT VALID then VALIDATE.
- Make incompatible changes in compatible steps: expand, dual write, backfill, switch reads, then contract.
- Additive schema changes go before the code that needs them, destructive ones after the last code that used them.
- Backfill in small idempotent batches throttled on replica lag, and verify a move with shadow reads before switching.