All field guides

FIELD GUIDE Published

Zero-downtime schema migrations: expand and contract

Ship a breaking schema change as three backward-compatible steps, expand, migrate and contract, and give every DDL statement a lock_timeout so it fails instead of queueing. Contracting too early cost GitHub errors on about 4% of web and API traffic in August 2025, when it dropped a column its ORM still referenced; a queued lock cost GoCardless about 15 seconds of API downtime from a migration that ran in a few hundred milliseconds against a backup.

Written with AI and checked against the cited sources before publication.

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:

  1. 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.
  2. 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.
  3. 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_timeout applies separately to each lock attempt, not to the migration as a whole. CREATE INDEX CONCURRENTLY cannot 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 as varchar to text, skip the rewrite but may still rebuild indexes. SET NOT NULL scans the whole table under the strong lock unless a valid CHECK constraint already proves there are no nulls (PostgreSQL 12 and later).
  • A concurrent index build that fails halfway. CREATE INDEX CONCURRENTLY lets 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 with REINDEX INDEX CONCURRENTLY, before you try again.

Decision table

ChangeWhat blocks or breaksSafer sequence (PostgreSQL)
Add a nullable column, or one with a non-volatile default (PostgreSQL 11+)Only the brief ACCESS EXCLUSIVE lock; no rewriteOne ALTER with lock_timeout set
Add a column with a volatile defaultRewrites the table and its indexesAdd it nullable, then ALTER COLUMN … SET DEFAULT (new rows only, no rewrite), then backfill existing rows in batches
Make a column NOT NULLScans the whole table under the strong lockAdd CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, SET NOT NULL (12+), then drop the CHECK
Add an indexA plain CREATE INDEX blocks writes until it finishesCREATE INDEX CONCURRENTLY, outside a transaction; drop or reindex the invalid index if it fails
Add a foreign keyScans to validate while writes queueAdd it NOT VALID with lock_timeout set (it still briefly locks both tables), then VALIDATE CONSTRAINT
Rename a column or change its typeOld code fails at once, or the table is rewrittenFull expand and contract: new column, dual write, backfill, switch reads, then drop the old one
Drop a columnAny code, ORM or report that still lists it fails; the drop itself needs ACCESS EXCLUSIVETell 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.

Hear it discussed

Episodes of the show that work through this decision.