Zero-downtime PostgreSQL migrations: expand, migrate, contract

Most migrations that take a site down are not wrong statements. They are right statements run at the wrong moment: the schema changed while old code was still running, or one ALTER held a lock and every request queued behind it. This article shows how to change a PostgreSQL schema while the system keeps serving traffic: expand → migrate → contract, the locks that matter, and backfilling data in batches.

Checked against the PostgreSQL 18 documentation (ALTER TABLE, CREATE INDEX, explicit locking, and the 11, 12 and 18 release notes). Everything here works on PostgreSQL 12 and later unless noted.

Why migrations break sites

During a deploy, two versions of your code run at once: a rolling deploy, several workers, or just the seconds between running the migration and restarting the app. If the migration makes the schema incompatible with either version, that version errors immediately.

The second problem is locks. Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which conflicts with everything, including a plain SELECT. If a long query holds the table, the ALTER waits. While it waits, every request that arrives later queues behind the ALTER, even though the ALTER has not started. One slow query plus one waiting ALTER freezes the whole table.

Rule one: every migration must work with both the previous and the next version of the code.

Expand → migrate → contract

Split an incompatible change into three steps. Each one is deployable and each one can be rolled back.

Step	Schema	Code
expand	add the new thing (column, table, index); leave the old alone	write both old and new, read old
migrate	backfill old data into the new place, then add constraints	read new, still write both
contract	remove the old thing	use only the new

Expand cannot break old code, because old code does not know the new column exists. Contract cannot break new code, because new code stopped using the old column a deploy ago. If any deploy goes wrong you can roll back to the previous version at once, because the schema still supports it. This fits neatly with atomic deploys and rollback.

Worked example: renaming a column in three deploys

Table users has a column name; we want full_name, with no downtime.

Deploy 1 (expand). Add the new column, nullable, no default. Metadata only.

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN full_name text;

This code version writes both name and full_name on every insert and update, and still reads name. Then run the backfill (next section) so old rows get full_name too.

Deploy 2 (migrate). Once the backfill is done, add NOT NULL without scanning the table under a heavy lock:

SET lock_timeout = '2s';
ALTER TABLE users ADD CONSTRAINT users_full_name_nn
  CHECK (full_name IS NOT NULL) NOT VALID;               -- brief lock, no scan
ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn;  -- scans, light lock
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;     -- scan skipped: a valid CHECK proves no NULLs
ALTER TABLE users DROP CONSTRAINT users_full_name_nn;

This code version reads full_name and still writes both, so you can roll back to version 1.

Deploy 3 (contract). The code stops touching name entirely. Once that deploy is finished, drop it:

SET lock_timeout = '2s';
ALTER TABLE users DROP COLUMN name;

DROP COLUMN does not physically remove the data; it makes the column invisible, which is why it is quick. The space comes back gradually as existing rows are updated, or at once if something rewrites the whole table.

PostgreSQL 18 adds a shortcut: a NOT NULL constraint can itself be added NOT VALID, and SET NOT NULL validates it. The CHECK route above works from version 12 onward, so it stays the safe default.

The locks to know

Statement	Lock	Blocks	Rewrites table?
ADD COLUMN nullable or constant default (11+)	ACCESS EXCLUSIVE, briefly	everything, briefly	no
ADD COLUMN volatile default, e.g. clock_timestamp()	ACCESS EXCLUSIVE	everything, for the whole rewrite	yes, table and indexes
CREATE INDEX	SHARE	writes (reads continue)	no
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	neither reads nor writes	no
ADD CHECK ... NOT VALID	ACCESS EXCLUSIVE, briefly	everything, briefly	no, and no scan
ADD FOREIGN KEY ... NOT VALID	SHARE ROW EXCLUSIVE on both tables, briefly	writes, briefly	no
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	neither reads nor writes	no (scan only)
SET NOT NULL	ACCESS EXCLUSIVE	everything	no, but scans the table unless a valid CHECK exists
DROP COLUMN	ACCESS EXCLUSIVE, briefly	everything, briefly	no

The pattern: work that scans or rewrites the table goes into statements with light locks (CONCURRENTLY, VALIDATE). Statements that need a heavy lock must be metadata changes that finish in a fraction of a second.

CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY users_full_name_idx ON users (full_name);

The trade-offs: it scans the table twice and waits for existing transactions to finish, so it is much slower than a normal build. It cannot run inside a transaction block. If it fails part-way (a deadlock, a duplicate in a unique index) it leaves an INVALID index that queries ignore but every write still maintains. Find it with \d users in psql, then DROP INDEX and build again, or REINDEX INDEX CONCURRENTLY.

lock_timeout: fail fast instead of freezing the table

lock_timeout defaults to 0, meaning wait forever. Set it before every DDL statement: if the lock is not granted in time, the statement errors instead of blocking the queue. The limit applies to each lock acquisition attempt separately.

Worked example: a report runs a 40-second SELECT on users, and you issue an ALTER.

• No lock_timeout: the ALTER waits 40 seconds, and every new request touching users queues behind it. At 50 requests a second that is about 50 × 40 = 2,000 stuck requests; your connection pool ran out long before
• lock_timeout = '2s': the ALTER gives up after 2 seconds. At most about 50 × 2 = 100 requests wait, then traffic flows again. The migration fails and you retry later

Two seconds is a common choice; tune it to your app’s own timeouts. Your migration tooling must retry, because PostgreSQL will not. If you also set statement_timeout, keep lock_timeout below it, otherwise it never fires.

Backfilling in batches

A single UPDATE users SET full_name = name on 20 million rows is one long transaction: it locks every row it touches, creates 20 million dead tuples in one go, and if it fails at 90% everything rolls back. Split by primary key into batches of a few thousand rows, each committing on its own.

#!/usr/bin/env bash
# run from a machine that can reach the DB; DB="postgres://app@db.example.com/app"
set -euo pipefail
BATCH=5000
MAX=$(psql "$DB" -Atc "SELECT coalesce(max(id),0) FROM users")
for ((start=1; start<=MAX; start+=BATCH)); do
  psql "$DB" -qc "UPDATE users SET full_name = name
                  WHERE id >= $start AND id < $start + $BATCH
                    AND full_name IS NULL"
  sleep 0.1   # give replicas and autovacuum room to keep up
done

The full_name IS NULL condition makes it safe to re-run: if it stops half-way, start again without overwriting values the app already wrote.

Work out the time before you start. The per-batch time is an assumption here; measure yours on staging with realistic data.

	value
Rows	20,000,000
Batch size	5,000
Batches	20,000,000 ÷ 5,000 = 4,000
Time per batch (measured, assumed here)	150 ms + 100 ms pause = 250 ms
Total	4,000 × 0.25 s = 1,000 s ≈ 17 minutes

If 150 ms per batch is too long, shrink the batch. The goal is that each transaction is short enough that nobody notices. The total time hardly matters, because the site keeps serving normally the whole way through.

Common mistakes

• Forgetting the tool wraps a transaction. CREATE INDEX CONCURRENTLY fails at once, and with several DDL statements in one file, the first one’s lock is held until the file ends
• Dropping a column in the same deploy that stops using it. Old code still running errors. Finish the deploy that stops using it first
• ORMs that cache the column list. Some ORMs load column names at process start and SELECT or INSERT all of them. Drop a column and it breaks even though your code never names it. Tell the ORM to ignore the column before you drop it
• Changing a column type in place. ALTER COLUMN ... TYPE to a type that is not binary-compatible rewrites the whole table under ACCESS EXCLUSIVE. Use the same expand/contract steps as a rename
• Testing on an empty table. A migration that takes 50 ms on a dev box can lock for 10 minutes on real data. Always test on a full-size copy (the database edition of works on my machine)
• A backfill that is not idempotent. Without the IS NULL guard, a re-run overwrites what the new code wrote

A statement that takes a heavy lock must finish in a fraction of a second; push the heavy work into light-lock statements, and always set lock_timeout.

Sources

• ALTER TABLE — PostgreSQL 18 Documentation
• CREATE INDEX (Building Indexes Concurrently) — PostgreSQL 18 Documentation
• Explicit Locking — PostgreSQL 18 Documentation
• Client Connection Defaults (lock_timeout) — PostgreSQL 18 Documentation
• PostgreSQL 11 Release Notes — postgresql.org and PostgreSQL 18 Release Notes
• Schema changes and the Postgres lock queue — Xata