Zero-Downtime Schema Migrations: The Expand/Contract Pattern

The migration ran in eleven seconds in staging. In production it took the orders table offline for four minutes, because production had ninety million rows and staging had eleven thousand. The change itself was trivial — a single new column, not null, with a sensible default. The incident report was not trivial. It ran to two pages, included a timeline, and used the phrase "lessons learned", which is the corporate equivalent of a chalk outline.

This is the failure mode the expand/contract pattern exists to prevent. Not because the people writing the migration are careless — they usually aren't — but because the thing that makes a migration safe is almost never visible in the diff. The diff says add column. What it does is acquire an ACCESS EXCLUSIVE lock on a table the entire order pipeline reads from, and hold it for as long as it takes to rewrite every row on disk. The size of that "as long as it takes" is a property of production, not of the code, which is exactly why it doesn't show up until production.

Backwards compatibility is the whole game

The core idea is almost embarrassingly simple once you've seen it. A migration is dangerous when the schema and the application code have to change at the same instant — when there is a single moment where the database is in the new shape and the running code expects the old one, or vice versa. That moment is the outage. Everything else is detail.

So you remove the moment. You decompose one risky change into a sequence of individually safe ones, arranged so that at every step the database is compatible with both the code that's currently running and the code that's about to deploy. There's always an overlap window where both versions of the application work against the same database.

That's the entire pattern. Expand the schema to support the new shape without removing the old. Migrate the data and the reads and writes across, gradually. Contract the schema to drop the old shape, only once nothing depends on it. Three phases, and the discipline is in never collapsing them back into one because the change "looks small".

Here is the phase-to-tolerance mapping, which is the bit worth pinning to the wall:

Every row in that table is a deploy boundary. You don't move to the next until the previous has fully rolled out and you've confirmed it.

Expand, without the lock

The expand phase is where most people get burned, because the most natural way to add a column is also the one that rewrites the table. On older PostgreSQL — pre-11 — ADD COLUMN ... NOT NULL DEFAULT 'something' had to write the default into every existing row, under a lock, before it would let go. On a small table you never notice. On ninety million rows you write that incident report.

Modern Postgres made the cheap path wider than people remember: since 11 it stores the default in the catalogue and materialises it lazily, so adding a column with a non-volatile default no longer rewrites the table. That includes DEFAULT now() — now() is STABLE, fixed once per transaction, so it takes the fast path too. The trap is reserved for genuinely volatile defaults that must be computed per row — DEFAULT clock_timestamp(), DEFAULT random(), DEFAULT gen_random_uuid() — which still force a full rewrite, as does adding NOT NULL to a column that already exists. The safe shape is the boring one: add the column nullable, with no default or a non-volatile one, in its own migration. No lock worth worrying about, no rewrite.

In Alembic that's a one-liner, and it should be its own revision rather than bundled with anything else:

def upgrade() -> None:
    op.add_column(
        "orders",
        sa.Column("currency", sa.String(length=3), nullable=True),
    )

The constraint comes later, separately, and not as a table-rewriting ALTER. Postgres lets you add a CHECK constraint NOT VALID, which applies to new rows immediately but skips the full-table scan, then VALIDATE it afterwards under a far weaker lock:

def upgrade() -> None:
    op.execute("ALTER TABLE orders ADD CONSTRAINT currency_not_null "
               "CHECK (currency IS NOT NULL) NOT VALID")
    # ... later, once backfilled, in its own migration:
    op.execute("ALTER TABLE orders VALIDATE CONSTRAINT currency_not_null")

Indexes follow the same logic. CREATE INDEX takes a lock that blocks writes for the duration; CREATE INDEX CONCURRENTLY doesn't, at the cost of running outside a transaction and occasionally failing in a way that leaves an invalid index behind to clean up. In Alembic you reach for it with op.create_index(..., postgresql_concurrently=True) and you set the migration to not run in a transaction. Slower, fussier, and the only sane choice on a live table.

Backfill in batches, never in one statement

Once the new column exists, it's empty, and you need to fill it. The instinct is a single UPDATE orders SET currency = 'GBP' WHERE currency IS NULL. The instinct is wrong. That statement takes row locks on every matching row and holds them in one transaction until it commits — which on a large table means a long-running transaction, bloated dead tuples, and a lock footprint that fights with live traffic the whole way.

Batch it. Loop in chunks of a few thousand rows, commit between batches, and let the database breathe:

UPDATE orders SET currency = 'GBP'
WHERE id IN (
    SELECT id FROM orders WHERE currency IS NULL LIMIT 5000
);

Run that until it affects zero rows. Each batch is a short transaction, locks are released promptly, and autovacuum gets a chance to keep up. Slower in wall-clock terms, dramatically cheaper in blast radius — the trade you want every time. Keep the batch loop out of a single Alembic revision if it'll run for hours; a migration that takes four hours is a migration that can't be cancelled cleanly. A separate, resumable script is usually the better home for a large backfill, with Alembic owning only the schema shape.

Dual writes and the switch-over

The interesting case is renaming a column, because there's no such thing as renaming a column without downtime — only adding a new one and retiring the old. The sequence is the pattern in miniature. Add email_address alongside email. Deploy code that writes to both on every insert and update, so the two stay in lockstep for all new data. Backfill email_address from email in batches for the historical rows. Switch reads over to email_address and deploy that. Confirm nothing reads email. Only then drop it.

At no point in that sequence does a running version of the application see a schema it doesn't understand. The old code reads and writes email; the dual-writing code keeps both honest; the new code reads email_address. The overlap window is where the safety lives, and it can be open for hours or weeks depending on how cautious you want to be. There's no prize for closing it early.

Rollback is a phase, not an afterthought

The quiet virtue of doing it this way is that every step is independently reversible. If the expand migration misbehaves, you drop a nullable column nobody reads yet — a non-event. If the backfill is wrong, you fix the data and run it again; the schema hasn't moved. If the switch-over surfaces a bug, you redeploy the previous version, which still reads the old column, because you haven't dropped it. The only irreversible step is the contract, and you've deliberately put it last, after everything else has proven itself in production.

That's the actual payoff. Not that the migration is faster — it's slower, with more moving parts and more deploys. It's that there is no single step which, done wrong at three in the morning, takes the table offline. Boring migrations are the goal. The expand/contract pattern is mostly a method for making them boring on purpose, which is the only kind of boring worth engineering.