The mechanism
A schema change breaks production in one of two ways. Either some running code still expects the old shape when the change lands, or the database blocks traffic while it changes the table. Expand and contract, which Martin Fowler's site also calls parallel change, deals with the first problem by splitting one breaking change into steps that are each backward compatible:
- Expand. Add the new shape next to the old one: a new column, table or index. Nothing reads it yet, and every version of the code that is still running keeps working.
- Migrate. Deploy code that writes both shapes, backfill the old rows in small batches, then move reads to the new shape. Every step can be shipped and rolled back on its own.
- Contract. Remove the old shape only after no running code, background worker, report or ORM model refers to it.
Stripe ran the same pattern as four separate deploys to move hundreds of millions of subscription objects between tables: write to both, move the reads, write only to the new table, then delete the old data. You can roll back freely while both tables are being written. Once writes go only to the new table, the old one goes stale, and rolling back needs a reverse backfill.
The second problem, the database blocking traffic, is about locks. In PostgreSQL, ALTER TABLE takes an ACCESS EXCLUSIVE lock unless the documentation for that form says otherwise. That lock conflicts with every other lock mode, including the one a plain SELECT takes. The expensive part is usually not holding the lock but waiting for it. While the ALTER waits behind a long-running query, every new query on that table queues behind the ALTER. A statement that runs in milliseconds can still stop a table for as long as the slowest open transaction on it.
So a zero-downtime migration has two parts:
- Compatible steps, so old and new code can both run.
- Lock discipline, so each step holds its lock only briefly and gives up rather than wait: set
lock_timeout, and use the forms that skip a rewrite or a full-table scan under the strong lock.
Worked numbers
The arithmetic below is illustrative. Put in your own traffic and table sizes.
The lock queue. Take a table that serves 2,000 queries a second, and an analytics query on it that has run for 30 seconds. A migration's ALTER TABLE arrives and waits for that query. Every query that arrives after the ALTER waits too. If the analytics query has 25 seconds left, about 50,000 requests pile up (2,000 × 25). An application with a pool of 200 database connections runs out of connections within a tenth of a second (200 ÷ 2,000), and the outage spreads past this table to everything that shares the pool. With lock_timeout = '2s', the queue stops at about 4,000 queries (2,000 × 2). The pool still drains, but for two seconds instead of 25. Then the migration fails, and you retry it with backoff when the long query has finished. Keep the timeout short: the pool stays saturated for almost all of it.
The backfill. Take 200 million rows, updated 5,000 at a time. If each batch takes 50 ms and you pause 50 ms between batches to let replicas catch up (lagging replicas have their own failure mode: see You Saved It. Why Did It Disappear?), the backfill needs 40,000 batches at 0.1 s each: 4,000 seconds, about 67 minutes. A single UPDATE over all 200 million rows would instead hold row locks on all of them in one transaction, generate one huge burst of WAL for the replicas to replay, leave 200 million dead row versions for vacuum, and lose all of its work if it fails. Stripe puts the sequential cost plainly: at one second per object, migrating one hundred million objects would take over three years, which is why its migrations ran in parallel batches.
How it fails
- Contract before every reader has moved. On 5 August 2025, GitHub dropped a column that pull request functionality no longer used directly, but its ORM still listed it in some queries. Errors peaked at about 4% of all web and REST API traffic; the incident lasted 32 minutes, and most services recovered by 16:13 UTC. The fix, a deploy telling the ORM to ignore the column, reached only the largest environment, so a second, smaller incident hit custom and canary environments until 19:45 UTC.
- A fast statement stuck in the lock queue. In a post published in 2019, GoCardless described about 15 seconds of API downtime from a migration that took a few hundred milliseconds against a backup. On the older PostgreSQL they ran then, adding a foreign key took an ACCESS EXCLUSIVE lock on the referenced table as well, one of their busiest. A long read on that table held the migration in the queue, and API queries piled up behind it. Their advice: remove long transactions, set
lock_timeout, and split schema changes into smaller steps. Since PostgreSQL 9.5, adding a foreign key takes a SHARE ROW EXCLUSIVE lock on both tables. Reads get through, but writes still queue. - The final swap. Tools that copy a table and swap it in at the end move the risk to that last step. In November 2021, the final rename step of a migration on a large MySQL table sent many of GitHub's read replicas into a semaphore deadlock and a crash-recovery loop. GitHub notes that its schema migrations often take weeks. Core services were degraded for 2 hours and 50 minutes, writes stayed healthy, and GitHub paused schema migrations while it investigated.
- Every statement in one transaction. Many migration frameworks wrap each migration in a single transaction, so every lock a statement takes is held until the whole migration commits, and locks stack up across statements.
lock_timeoutapplies separately to each lock attempt, not to the migration as a whole.CREATE INDEX CONCURRENTLYcannot run inside a transaction block at all, so it needs its own non-transactional migration.
Traps without a published incident
- A rewrite or scan hidden in a simple-looking statement. Adding a column with a volatile default such as
clock_timestamp()rewrites the whole table and its indexes. Changing a column's type normally does too; binary-coercible changes, such asvarchartotext, skip the rewrite but may still rebuild indexes.SET NOT NULLscans the whole table under the strong lock unless a validCHECKconstraint already proves there are no nulls (PostgreSQL 12 and later). - A concurrent index build that fails halfway.
CREATE INDEX CONCURRENTLYlets writes continue, but it scans the table twice and waits for transactions that started before each scan, so a long transaction elsewhere can stall it. If it fails, it leaves an invalid index that still slows writes. Drop it, or rebuild it withREINDEX INDEX CONCURRENTLY, before you try again.
Decision table
| Change | What blocks or breaks | Safer sequence (PostgreSQL) |
|---|---|---|
| Add a nullable column, or one with a non-volatile default (PostgreSQL 11+) | Only the brief ACCESS EXCLUSIVE lock; no rewrite | One ALTER with lock_timeout set |
| Add a column with a volatile default | Rewrites the table and its indexes | Add it nullable, then ALTER COLUMN … SET DEFAULT (new rows only, no rewrite), then backfill existing rows in batches |
| Make a column NOT NULL | Scans the whole table under the strong lock | Add CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, SET NOT NULL (12+), then drop the CHECK |
| Add an index | A plain CREATE INDEX blocks writes until it finishes | CREATE INDEX CONCURRENTLY, outside a transaction; drop or reindex the invalid index if it fails |
| Add a foreign key | Scans to validate while writes queue | Add it NOT VALID with lock_timeout set (it still briefly locks both tables), then VALIDATE CONSTRAINT |
| Rename a column or change its type | Old code fails at once, or the table is rewritten | Full expand and contract: new column, dual write, backfill, switch reads, then drop the old one |
| Drop a column | Any code, ORM or report that still lists it fails; the drop itself needs ACCESS EXCLUSIVE | Tell the ORM to ignore it, deploy everywhere, wait a release, then drop it with lock_timeout set |
A check you can run this week
Pick the next migration in your queue and write down which of the three steps it is: expand, migrate or contract. If it is a contract step, list everything that can still read the old shape: services, workers, ORM models, reports and replicas, in every environment. Put SET lock_timeout = '2s'; at the top of the migration itself and run it against a staging copy. Before you schedule it in production, look at the oldest transactions holding locks on the table it changes (here orders):
SELECT a.pid, now() - a.xact_start AS open_for, a.state, left(a.query, 60) AS query
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY a.xact_start
LIMIT 5;If the oldest transaction that touches that table has been open for longer than your users would wait, that transaction, not your migration, decides how long the table will be blocked.
To make this a team default rather than a one-off, set lock_timeout on the role your migrations run as, and have CI reject a contract step that ships in the same release as the code change that stops using the old shape.
Our episode Before You Migrate: Can You Really Roll Back? works through the rollback side of the same decision.
Sources
The primary sources this guide was checked against.
- Martin Fowler: Parallel Change (expand and contract)
- Stripe: Online migrations at scale
- GitHub Availability Report: August 2025
- GitHub Availability Report: November 2021
- GoCardless: Zero-downtime Postgres migrations, the hard parts
- PostgreSQL documentation: ALTER TABLE
- PostgreSQL documentation: explicit locking
- PostgreSQL documentation: lock_timeout
- PostgreSQL documentation: building indexes concurrently
- GitHub: gh-ost, triggerless online schema migration for MySQL
Hear it discussed
Episodes of the show that work through this decision.