Zero-Downtime Schema Migrations: The Expand-and-Contract Pattern

Posted on

There is a particular kind of outage that never appears in any incident report: the migration that ran at 2 a.m. because it had to. If your schema change requires a table rewrite, the database holds a lock that blocks every read and write until the rewrite finishes, and the larger the table, the longer everyone waits. This is not a rare edge case — it is the default behavior for many ordinary DDL statements on both PostgreSQL and MySQL. The fix is not better timing or braver rollouts; it is changing the shape of migrations themselves so that no single statement ever requires the outage. The expand-and-contract pattern, applied consistently, makes schema changes as boring and reversible as any other deploy.

Why a Single ALTER TABLE Is Dangerous

PostgreSQL takes an ACCESS EXCLUSIVE lock for nearly every ALTER TABLE — even for changes that finish in milliseconds. The lock itself is brief, but it queues: while your DDL waits for a long-running transaction to release the table, every subsequent query on that table stacks up behind it. A thirty-second analytics query plus a two-second DDL equals thirty-two seconds of a completely frozen table. This is why even “fast” DDL belongs inside a SET lock_timeout wrapper: if the lock cannot be acquired quickly, abort the DDL and retry later rather than letting it queue behind everything (the ALTER TABLE documentation lists the lock level for every operation).

Some operations are worse than a lock. In older PostgreSQL versions, adding a column with a default rewrote and re-wrote every row of the table; on MySQL, many in-place ALTER TABLE variants still rebuild the table under the hood. The general rule: anything that must touch every existing row — adding a column with a volatile default, changing a column type, adding a unique constraint on an existing column — is a potential outage at scale.

The Pattern: Expand, Backfill, Switch, Contract

Expand-and-contract splits one dangerous change into four safe phases:

  • Expand the schema with only additive changes: new columns, new indexes, new nullable fields. Old code keeps working untouched.
  • Backfill existing rows in small batches so no transaction holds a lock long enough to matter.
  • Switch the application over to reading and writing the new shape — ideally behind a flag, so a rollback is a config change rather than a migration — and then tighten constraints on the new column.
  • Contract by removing the old column and indexes once no code path references them anymore.

The phases are separated by deployments, not minutes. The old and new schema shapes coexist in production for as long as the rollback window requires, and the contract phase happens only after you are certain nothing would miss what you are removing.

Phase One: Expand Safely

Suppose orders currently store the customer’s email as text, and you want to replace it with a foreign key. The expanding migration adds the new column without disturbing anything:

SET lock_timeout = '2s';

ALTER TABLE orders
    ADD COLUMN customer_id BIGINT;

The column is nullable and has no default, so existing rows are untouched and the statement is metadata-only. A subtlety worth knowing: since PostgreSQL 11, ADD COLUMN ... NOT NULL DEFAULT with a constant default is also metadata-only — the database records the default in the catalog and never rewrites the rows. The rewrite only returns when the default expression is volatile, such as clock_timestamp(), because every row must then materialize its own value.

Indexes that back the new column come next, and they get their own rule: build them concurrently.

CREATE INDEX CONCURRENTLY idx_orders_customer_id
    ON orders (customer_id);

A concurrent build does not block writes, but it has two traps. It cannot run inside a transaction, which breaks migration tools that wrap every statement in one — the statement usually needs to be marked as non-transactional. And if the build fails midway, it leaves an invalid index behind that must be dropped before retrying; a plain retry fails with “already exists.”

Phase Two: Backfill in Batches

The instinctive backfill — one giant UPDATE — is the outage you were trying to avoid: it rewrites every row in a single transaction, bloats the table, blocks vacuum, and replays as one massive write set on any replica. The working version processes a bounded batch per statement, committing as it goes:

SET lock_timeout = '2s';

WITH batch AS (
    SELECT id, email
    FROM orders
    WHERE customer_id IS NULL
    ORDER BY id
    LIMIT 1000
)
UPDATE orders o
SET customer_id = c.id
FROM batch b
JOIN customers c ON c.email = b.email
WHERE o.id = b.id;

Run that statement in a loop until it reports zero rows updated, and sleep briefly between batches. Each batch is short, so long-running transactions never overlap with it, replication lag stays bounded, and an interrupted backfill simply resumes where it stopped. The backfill can run while old code still writes the old column — but it must only start after the expanding migration is committed, and new code must be able to tolerate rows where customer_id is still NULL, because writes from not-yet-deployed old instances will keep landing that way for a while.

Phase Three: Tighten Without the Scan

Once the backfill is complete and all writes go through the new column, you can usually make it NOT NULL. Doing so naively triggers a full-table scan under an exclusive lock — exactly what the pattern exists to avoid. PostgreSQL offers a three-step alternative that keeps every step cheap:

-- 1. Declare intent; NOT VALID skips the scan on existing rows.
ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_nn
    CHECK (customer_id IS NOT NULL)
    NOT VALID;

-- 2. Validate with only a SHARE UPDATE EXCLUSIVE lock; writes continue.
ALTER TABLE orders
    VALIDATE CONSTRAINT orders_customer_id_nn;

-- 3. Since PostgreSQL 12, SET NOT NULL trusts the validated check
--    and skips the table scan entirely.
ALTER TABLE orders
    ALTER COLUMN customer_id SET NOT NULL;

-- The check constraint is now redundant.
ALTER TABLE orders
    DROP CONSTRAINT orders_customer_id_nn;

Each statement takes a brief lock measured in milliseconds. New inserts and updates are enforced from the moment the check constraint exists, existing rows are proven clean by the validation pass, and the final SET NOT NULL is effectively free. The same dance applies to most tightening operations: find the version of the constraint that can be added permissively, validate in the background, then promote it once the data is known to comply.

Phase Four: Contract

The final phase removes what the earlier phases made obsolete. After the code that reads and writes the old column has been gone for at least one full release-and-rollback window, the old column can go:

SET lock_timeout = '2s';

ALTER TABLE orders
    DROP COLUMN email;

Dropping a column is metadata-only — the data is not physically rewritten — but the momentary exclusive lock still warrants the lock_timeout guard. Resisting the urge to contract early is the hard part: carrying an unused column for a month costs nothing, while rolling back a week too soon costs another outage. Let the calendar decide — one clean rollback window, no references left in any deploy branch.

When the Database Needs Help

Some changes cannot be made incremental with plain DDL. Changing a primary key type, restructuring a huge table’s storage engine, or anything requiring a full rebuild on MySQL historically meant table copy. Three tools have made this tractable:

  • gh-ost — GitHub’s online migration tool for MySQL. It copies the table to a shadow table while streaming changes from the binary log, then swaps them atomically, with throttling and a built-in pause for cutover.
  • pt-online-schema-change — the Percona Toolkit equivalent, using triggers to capture concurrent changes during the copy.
  • pgroll — brings the same discipline to PostgreSQL. It implements expand-and-contract natively: each migration gets an expand phase and a contract phase, and old and new client versions can work against the database simultaneously through a versioned schema view.

The tool matters less than the invariant they all share: no migration step should block production traffic for more than a moment, and every migration should stay reversible while the previous code version is still deployable.

Rules Worth Adopting

  • Wrap every DDL statement in a short lock_timeout so a queued lock aborts instead of freezing the table.
  • Never ship a migration that rewrites every row in one transaction; batch the backfill instead.
  • Build indexes concurrently — outside the migration tool’s transaction wrapper — and clean up invalid indexes after failures.
  • Use the check-constraint route for NOT NULL on large tables instead of the direct scan-based one.
  • Separate expand, switch, and contract with real deployments, and give each a rollback window.
  • Prefer dropping things last. A migration that only adds and a migration that only removes are each independently revertible; a combined one is not.

The expand-and-contract pattern asks for patience in exchange for a guarantee: schema changes stop being scheduled maintenance events and become ordinary commits. Once a team has run a few migrations where nothing went down and nothing needed a rollback, it becomes difficult to remember why anyone ever accepted a migration window in the first place.

Leave a Reply

Your email address will not be published. Required fields are marked *