Zero-downtime database migrations: expand and contract, backfills and locks

Key points

• Set lock_timeout in every migration session and retry on failure: a DDL statement waiting for its lock blocks every query that arrives after it.
• Split breaking changes into expand, migrate and contract, with a deploy between each, so the old and the new code both work at every step.
• Know which statements rewrite the table: a constant default is instant since PostgreSQL 11, while a volatile default or most type changes rewrite everything.
• Build indexes with CREATE INDEX CONCURRENTLY, and add foreign keys and CHECK constraints as NOT VALID, then validate them under a weaker lock.
• Backfill in short batches keyed on the primary key, with a pause between them, never in one giant UPDATE.
• Roll back code, not schema: expand steps are harmless to leave in place, and the contract step waits until the new path has proven itself.

Why migrations cause outages: locks and rewrites

Schema changes take an app down in two ways. The first is a lock. The PostgreSQL documentation says ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a subcommand says otherwise, and that this is the only lock mode that blocks a plain SELECT. While it is held, nothing reads or writes the table.

A statement that would finish in milliseconds still causes an outage if it has to wait for that lock. Lock requests queue: if a long report or a session left idle in a transaction holds even a read lock, the ALTER TABLE waits for it, and every query that arrives afterwards waits behind the ALTER TABLE, even queries that do not conflict with the report. A test on a local PostgreSQL 14 shows it: with one session holding an open read transaction and an ALTER TABLE waiting with a 4-second lock_timeout, a plain SELECT count(*) started a second later stayed blocked for about 3 seconds, until the ALTER TABLE gave up. Without the timeout it would have waited as long as the first transaction stayed open, and so would every request to that table.

The second is a table rewrite. Some changes make PostgreSQL write a new copy of the whole table and its indexes while holding the exclusive lock: most column type changes, and a new column with a volatile default. On a large table that is a long outage.

The expand and contract pattern

The way to make a breaking change safely is to never make it in one step. Danilo Sato’s ParallelChange article on Martin Fowler’s site describes three phases for changing an interface in a backward-incompatible way: expand (add the new alongside the old), migrate (move the clients over) and contract (remove the old). It names database refactoring as one of its main uses.

Applied to a schema, the rule is that the running code and the schema must be compatible at every moment, including during a deploy, when old and new versions of the app run side by side. Renaming fullname to display_name becomes six steps, each its own deploy:

1. Expand: add display_name with no default and no constraint. It is a catalog change and finishes at once.
2. Dual write: deploy code that writes both columns on every insert and update. Old rows still have only fullname.
3. Backfill: copy fullname into display_name for the old rows, in batches.
4. Switch reads: deploy code that reads display_name. A feature flag lets you move reads over gradually and move them back.
5. Stop the old writes: deploy code that writes only display_name.
6. Contract: once nothing reads or writes fullname, drop it in a later migration.

Stripe’s engineering post on online migrations describes the same moves at large scale, for moving data between tables: dual writing, changing the read paths, changing the write paths, then removing the old data, with a gradual ramp-up and automated comparisons of old and new reads before trusting the new path. On MySQL, GitHub’s gh-ost takes a different route for changes the database cannot make in place: it builds a copy of the table with the new schema, keeps it in sync from the binary log, can throttle or pause, and swaps the tables at a moment the operator controls.

Recipes for common changes in PostgreSQL

Adding a column. The ALTER TABLE documentation explains that with a non-volatile default the value is computed once and stored in the table’s metadata, which makes the change very fast even on large tables, while a volatile default, a stored generated column or an identity column makes PostgreSQL rewrite the table and its indexes. The PostgreSQL 11 release notes introduced this for constant defaults. On a local PostgreSQL 14 table of 200,000 rows, the table’s relfilenode stayed the same after adding a column with a constant default and changed after adding one that defaults to clock_timestamp(): a new file on disk, which is the rewrite.

-- Run each DDL step in its own session, with tight limits.
SET lock_timeout = '3s';       -- give up if the lock is not granted quickly
SET statement_timeout = '30s'; -- and if the statement itself runs long

-- Fast since PostgreSQL 11: a constant default is stored in the catalog.
ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';

-- Slow: a volatile default is computed per row, so the table is rewritten.
-- ALTER TABLE orders ADD COLUMN seen_at timestamptz DEFAULT clock_timestamp();

Making a column required. Marking an existing column required makes PostgreSQL scan the whole table to prove that no row lacks a value, while it holds ACCESS EXCLUSIVE. The ALTER TABLE documentation describes the way around it: if a valid CHECK constraint already proves that every row has a value, the scan is skipped. So add a CHECK constraint that rejects rows missing the value, marked NOT VALID; validate it under the weaker lock; mark the column required, which is now a quick catalog change; then drop the redundant CHECK. The shortcut arrived in PostgreSQL 12, according to its release notes, and PostgreSQL 18 also accepts NOT VALID on the required-column constraint itself.

Changing a type. The documentation says a type change normally rewrites the table and its indexes. The exception is a change where the old type is binary coercible to the new one and the USING clause does not change the contents; the docs give text to varchar and back as a change that needs no index rebuild either. For anything else, treat it like a rename: a new column, dual writes, a backfill and a switch.

Indexes, foreign keys and CHECK constraints without blocking writes

The CREATE INDEX documentation says a normal index build locks the table against writes for the whole build, while reads continue. With CONCURRENTLY, PostgreSQL scans the table twice and waits for existing transactions that could use the index, so the build does more work and takes significantly longer, but inserts, updates and deletes keep running. It cannot run inside a transaction block, and many migration tools wrap each migration in one, so this step needs the tool’s opt-out. Which index to build in the first place is covered in database indexing explained.

-- Not allowed inside a transaction block. A long build needs no statement limit.
SET lock_timeout = '3s';
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

-- Foreign key: add it unchecked, then check old rows under a weaker lock.
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

-- The same two steps for a CHECK constraint.
ALTER TABLE orders ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'packed', 'shipped')) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_check;

• A concurrent build that fails, for example on a deadlock or a uniqueness violation, leaves an INVALID index behind. The docs say it is ignored by queries but still costs on every write; drop it and run the build again, or use REINDEX INDEX CONCURRENTLY.
• NOT VALID skips the check of existing rows, but new and updated rows are checked at once. In the same local test, an insert that broke a NOT VALID CHECK constraint was rejected, and the later VALIDATE CONSTRAINT failed on the old row that broke it.
• VALIDATE CONSTRAINT takes only SHARE UPDATE EXCLUSIVE, plus ROW SHARE on the referenced table for a foreign key, so reads and writes continue while it scans.
• Adding the foreign key still takes SHARE ROW EXCLUSIVE on both tables, briefly. That brief lock can still queue behind a long transaction, so it needs lock_timeout like any other DDL.

Batched backfills with throttling

One UPDATE over a whole table is one huge transaction. It locks every row it touches until it commits, leaves a dead copy of each row for vacuum to clean up, floods the write-ahead log and the replicas reading it, and if it fails near the end it rolls everything back. Backfill in batches instead. The loop below ran against SQLite as a simulation; the same loop works against PostgreSQL through any driver.

import sqlite3
import time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)")
db.executemany("INSERT INTO users (email) VALUES (?)",
               ((f"User{i}@Example.com",) for i in range(1, 10_001)))
db.execute("ALTER TABLE users ADD COLUMN email_lower TEXT")  # expand: new column

BATCH, PAUSE = 1_000, 0.05  # rows per transaction, seconds between batches
last_id, batches = 0, 0
max_id = db.execute("SELECT MAX(id) FROM users").fetchone()[0]
while last_id < max_id:
    with db:  # one short transaction per batch keeps row locks brief
        db.execute("UPDATE users SET email_lower = lower(email) "
                   "WHERE id > ? AND id <= ?", (last_id, last_id + BATCH))
    last_id += BATCH  # keyset by primary key: no OFFSET, no full scans
    batches += 1
    time.sleep(PAUSE)  # throttle: leave room for live traffic and replicas

done = db.execute("SELECT COUNT(*) FROM users WHERE email_lower = lower(email)").fetchone()[0]
print(f"{batches} batches, {done} of {max_id} rows backfilled")
# 10 batches, 10000 of 10000 rows backfilled

• Walk the primary key in ranges rather than paging with OFFSET: each batch is an index range scan, and after a crash you resume from the last id. Store that id somewhere durable for long runs.
• Make each batch idempotent. Running a range twice writes the same values, so retries are harmless; dual writes from the app keep new rows correct while the backfill runs.
• Size batches by time rather than rows: aim for transactions that commit in well under a second, and adjust as you watch.
• Throttle on signals, not only a fixed sleep: replica lag, lock waits and query latency. gh-ost’s documentation says that when it throttles it stops copying rows altogether, which is the right instinct.
• Run the backfill as a job, separate from the migration, so a deploy does not wait on it and an operator can pause it.

lock_timeout and statement_timeout for DDL

PostgreSQL’s lock_timeout aborts any statement that waits longer than the limit to acquire a lock on a table, index, row or other object, and the limit applies to each lock attempt separately. statement_timeout aborts a statement that runs longer than its limit. Both default to zero, which means no limit. The documentation advises against setting either in postgresql.conf, because that affects every session, so set them with SET in the migration session itself.

• Set lock_timeout to the longest stall your traffic can absorb, a few seconds at most. The docs note that a lock_timeout equal to or above statement_timeout is pointless, since the statement timeout fires first.
• Retry with backoff. A lock timeout fails with SQLSTATE 55P03 (lock_not_available); catch that error and try again rather than failing the whole deploy on the first attempt.
• Find the blocker. pg_stat_activity shows long transactions and sessions idle in a transaction, and pg_blocking_pids() shows which sessions block a given one. Setting idle_in_transaction_session_timeout for the app’s role stops forgotten transactions from holding locks indefinitely.
• Keep one DDL statement per transaction where you can. Locks are held until commit, so a migration that runs three ALTER TABLE statements and a data change in one transaction keeps the first lock for the whole run.
• Lift statement_timeout for CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT. They are long, but they do not block traffic.

Deploying code and schema in the right order

Expand migrations run before the code that needs them. Contract migrations run after the code that stopped needing them has fully rolled out. In a pipeline, as described in what CI/CD is, that makes migrations a step of their own, separate from the app deploy, and every migration must work with both the version currently running and the next one.

• Adding something: migrate first, then deploy the code that uses it.
• Removing something: deploy code that no longer uses it, wait until no old instances remain, then migrate.
• Put behavior switches behind a flag, so reads can move to the new column for part of the traffic and move back without a deploy; see feature flags system design.
• Lint migrations in CI for the risky forms in the recipes table: a plain CREATE INDEX on an existing table, a constraint without NOT VALID, a volatile default, a rename or a drop in the same release as the code change.
• Rehearse on a copy with production-sized data. A migration that is instant on a development table of a thousand rows can rewrite a table of hundreds of millions.

Rollback strategy

Down migrations are less useful than they look. Once new code has written data into a new column, dropping that column loses the data; once an old column is dropped, no down migration brings its contents back. Expand and contract gives a better answer: at every step before the contract, rolling back means redeploying the previous version of the code, and the schema stays as it is because both versions work with it.

• Leave expand steps in place when you roll back. An unused column or index is harmless, and you will apply the change again.
• Delay the contract step until the new path has run in production through at least a full release cycle, and the old column is read nowhere, including reports, exports and analytics jobs.
• Before a contract step that drops data, take a backup or note a point-in-time recovery position, or copy the column into an archive table.
• Keep the source column until the contract step, so a bad backfill can always be run again from it.

BYD Tech builds web apps and the databases behind them, schema changes included: the code lives in the client’s GitHub from day one, and AI-assisted work is reviewed by an engineer.