Zero-downtime Postgres migrations

A migration causes an outage in one of two ways. Either a statement takes a lock that conflicts with the application's queries and holds it while it does work proportional to the table size (a scan, a rewrite, an index build), or a statement waits for a lock and every query behind it in the queue waits too. Everything below is about avoiding those two things. Facts are for PostgreSQL 18 (18.6 is current as of November 2026), with notes where 12-17 differ.

Ask two questions of every statement

1. Which lock does it take, on which tables, and until when? Table locks are held until the transaction ends, not until the statement ends. The transaction is the unit you reason about.
2. Does it do O(rows) work while that lock is held? Scanning to validate, rewriting the heap, or building an index.

The app's writes take ROW EXCLUSIVE. Reads take ACCESS SHARE. What conflicts:

Lock	Typical sources	Blocks reads	Blocks writes
SHARE UPDATE EXCLUSIVE	CREATE INDEX CONCURRENTLY, VALIDATE CONSTRAINT, VACUUM, ANALYZE	no	no
SHARE	CREATE INDEX (not concurrent)	no	yes
SHARE ROW EXCLUSIVE	ADD FOREIGN KEY (both tables), CREATE TRIGGER	no	yes
ACCESS EXCLUSIVE	most ALTER TABLE, DROP, TRUNCATE, REINDEX (on the index), VACUUM FULL	yes	yes

Rule: a statement on a live table is safe when it either takes SHARE UPDATE EXCLUSIVE or weaker (long work is fine), or takes something stronger only for a catalog-only change (milliseconds), with lock_timeout set. Anything that scans, rewrites or builds under SHARE or stronger blocks writes for the whole duration. references/lock-matrix.md has the per-statement table, measured on 18.6.

The lock queue: why "instant" DDL still takes production down

ALTER TABLE shipments ADD COLUMN signed_by text is a catalog change, but it needs ACCESS EXCLUSIVE. If a long query or an idle-in-transaction session holds any lock on shipments, the ALTER waits. While it waits, every new query on shipments, plain SELECTs included, queues behind it. Reproduced on 18.6: a reader holding a 6-second transaction plus a waiting ALTER made an unrelated SELECT count(*) wait the full 6 seconds.

Fix: cap the wait and retry.

-- Inside a transactional migration: SET LOCAL ends with the transaction.
SET LOCAL lock_timeout = '5s';
ALTER TABLE shipments ADD COLUMN signed_by text;

• With lock_timeout the ALTER fails with canceling statement due to lock timeout and the queue drains. Retry the migration (with backoff) from the deploy tool, or let it fail loudly.
• In a file that runs outside a transaction, SET LOCAL does nothing (Postgres only warns), and plain SET is session-wide, so it leaks into the next files. Use SET, then RESET lock_timeout afterwards.
• Set timeouts per migration session, not in postgresql.conf; the docs advise against that because it affects every session.
• Do not give CREATE INDEX CONCURRENTLY a short lock_timeout. It waits for older transactions that touched the table, and that wait counts. On timeout it leaves an INVALID index behind (reproduced on 18.6). Let it wait, and watch for long transactions instead.
• statement_timeout caps the whole statement, lock wait included; transaction_timeout (PG 17+) caps a whole transaction. Neither replaces lock_timeout.

Change → safe recipe

You want to	Unsafe form (why)	Safe form
Add an index	CREATE INDEX (SHARE for the whole build: no writes)	CREATE INDEX CONCURRENTLY, outside a transaction, one statement per call
Drop an index	DROP INDEX (ACCESS EXCLUSIVE)	DROP INDEX CONCURRENTLY (not for indexes backing a constraint)
Rebuild an index	REINDEX INDEX (blocks writes; ACCESS EXCLUSIVE on the index)	REINDEX INDEX CONCURRENTLY
Add a foreign key	ADD FOREIGN KEY (validates under SHARE ROW EXCLUSIVE on both tables)	ADD ... NOT VALID, then VALIDATE CONSTRAINT in a later transaction
Add a CHECK	ADD CHECK (scan under ACCESS EXCLUSIVE)	ADD ... NOT VALID, then VALIDATE CONSTRAINT later
Make a column NOT NULL	SET NOT NULL (scan under ACCESS EXCLUSIVE)	PG 18: ADD CONSTRAINT x NOT NULL col NOT VALID, then VALIDATE. PG 12-17: validated CHECK (col IS NOT NULL) first
Add UNIQUE or PRIMARY KEY	ADD UNIQUE (col) (builds the index under ACCESS EXCLUSIVE)	CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... UNIQUE USING INDEX
Add a column with generated values	ADD COLUMN tracking_code uuid DEFAULT gen_random_uuid() (volatile default: full rewrite)	add with no default, SET DEFAULT separately, batched backfill
Add a column with a constant default	(nothing wrong)	ADD COLUMN c text NOT NULL DEFAULT 'x' is catalog-only since PG 11; now() counts as constant
Change a column type	ALTER TYPE that rewrites (int→bigint, numeric scale, shrinking varchar)	expand/contract: new column, dual write, backfill, swap
Rename a column or table	RENAME (instant, but running code breaks)	expand/contract, or a compatibility view
Backfill existing rows	one UPDATE over the table (row locks on every row until commit)	keyset batches, COMMIT after each

Full SQL for each row is in references/recipes.md.

Transactions and the migration runner

Most bugs in "safe" migrations come from the runner, not the SQL.

• NOT VALID and VALIDATE must be in different transactions. In one transaction, the ADD keeps its strong lock while VALIDATE scans, which is exactly the validated ADD you were avoiding. Separate files are only separate transactions if the runner commits between files. drizzle-orm's migrator (0.45.x) runs every pending migration in one transaction. node-pg-migrate's CLI defaults to --single-transaction.
• CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY and VACUUM cannot run in a transaction block. That includes the implicit block of a multi-statement simple query. SET ...; CREATE INDEX CONCURRENTLY ... sent as one string fails, so send one statement per call. graphile-migrate's no-transaction files must contain exactly one statement for this reason.
• Opting out of the transaction, per tool:
  • node-pg-migrate: pgm.noTransaction().
  • dbmate: -- migrate:up transaction:false.
  • graphile-migrate: --! no-transaction at the top, one statement.
  • Django: atomic = False with AddIndexConcurrently, and AddConstraintNotValid plus ValidateConstraint in two separate migrations.
  • Rails: disable_ddl_transaction! with algorithm: :concurrently, and validate: false on add_foreign_key or add_check_constraint.
  • A custom runner: read it. Look for a per-file directive, and check whether it wraps one file or the whole batch. See references/runners.md.
• A no-transaction file is not atomic. If statement 3 of 4 fails, statements 1-2 stay applied and the file is not recorded, so the rerun repeats them. Make every statement idempotent: IF NOT EXISTS, IF EXISTS, and DROP INDEX CONCURRENTLY IF EXISTS before a concurrent build.
• A failed concurrent build leaves an INVALID index that costs write overhead and is never used. CREATE INDEX CONCURRENTLY IF NOT EXISTS then skips it and leaves it invalid (reproduced on 18.6). Drop and rebuild instead.
• Never edit an applied migration. Production will not rerun it. Add a new file.

Core recipes

Index on a hot table (non-transactional step):

DROP INDEX CONCURRENTLY IF EXISTS shipments_carrier_dispatched_idx;
CREATE INDEX CONCURRENTLY shipments_carrier_dispatched_idx
  ON shipments (carrier_id, dispatched_at DESC);
-- afterwards: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;  -- expect no rows

Column order matters: equality columns first, then the ORDER BY column, so WHERE carrier_id = $1 ORDER BY dispatched_at DESC LIMIT 50 reads 50 index entries and stops. With (dispatched_at, carrier_id) there's no sort either, but the scan walks most of the index.

Foreign key or CHECK on existing rows:

-- transaction 1: catalog-only, brief strong locks
SET LOCAL lock_timeout = '5s';
ALTER TABLE shipments ADD CONSTRAINT shipments_carrier_fk
  FOREIGN KEY (carrier_id) REFERENCES carriers (carrier_id) NOT VALID;
ALTER TABLE shipments ADD CONSTRAINT shipments_fee_minor_check CHECK (fee_minor >= 0) NOT VALID;

-- transaction 2, if existing rows need fixing: plain DML, ROW EXCLUSIVE only
UPDATE shipments SET carrier_id = $unknown_carrier
WHERE NOT EXISTS (SELECT 1 FROM carriers k WHERE k.carrier_id = shipments.carrier_id);

-- transaction 3: scans under SHARE UPDATE EXCLUSIVE (plus ROW SHARE on carriers): no blocking
ALTER TABLE shipments VALIDATE CONSTRAINT shipments_carrier_fk;
ALTER TABLE shipments VALIDATE CONSTRAINT shipments_fee_minor_check;

A NOT VALID constraint is enforced for new and updated rows immediately. Only existing rows are unchecked until VALIDATE. Don't stop at NOT VALID: until it is validated, Postgres "will not assume that the constraint holds for all rows", and neither should you.

NOT NULL on an existing column:

-- PostgreSQL 18+
-- tx 1
ALTER TABLE shipments ADD CONSTRAINT shipments_tracking_code_nn NOT NULL tracking_code NOT VALID;
-- tx 2
ALTER TABLE shipments VALIDATE CONSTRAINT shipments_tracking_code_nn;
-- attnotnull and information_schema already say NOT NULL while the constraint is NOT VALID (18.6);
-- only pg_constraint.convalidated tells you old rows were checked. Finish with VALIDATE, not with
-- SET NOT NULL: on a column with an invalid not-null constraint, SET NOT NULL validates it by
-- scanning under ACCESS EXCLUSIVE.

-- PostgreSQL 12-17
-- tx 1
ALTER TABLE shipments ADD CONSTRAINT shipments_tracking_code_present
  CHECK (tracking_code IS NOT NULL) NOT VALID;
-- tx 2
ALTER TABLE shipments VALIDATE CONSTRAINT shipments_tracking_code_present;
-- tx 3: SET NOT NULL skips its scan because the validated CHECK proves there are no NULLs
ALTER TABLE shipments ALTER COLUMN tracking_code SET NOT NULL;
ALTER TABLE shipments DROP CONSTRAINT shipments_tracking_code_present;

Unique constraint:

-- non-transactional step
CREATE UNIQUE INDEX CONCURRENTLY shipments_tracking_code_uniq ON shipments (tracking_code);
-- new transaction
SET LOCAL lock_timeout = '5s';
ALTER TABLE shipments ADD CONSTRAINT shipments_tracking_code_uniq
  UNIQUE USING INDEX shipments_tracking_code_uniq;

USING INDEX needs a plain btree, non-partial, non-expression unique index. For a partial or expression rule (lower(username)), the unique index itself is the enforcement; skip the constraint. A concurrent unique build fails, and leaves an invalid index, if duplicates exist. Find them first.

New column filled for every row (for example an external tracking code):

-- tx 1: catalog-only
SET LOCAL lock_timeout = '5s';
ALTER TABLE shipments ADD COLUMN tracking_code uuid;  -- no default here
-- new rows only, no rewrite
ALTER TABLE shipments ALTER COLUMN tracking_code SET DEFAULT gen_random_uuid();
-- then: batched backfill (below), CREATE UNIQUE INDEX CONCURRENTLY, NOT NULL via NOT VALID + VALIDATE

gen_random_uuid(), uuidv7() (PG 18), clock_timestamp(), random(), serial/bigserial, identity columns and STORED generated columns all rewrite the table when added with existing rows (measured). now(), literals and virtual generated columns (the PG 18 default) do not.

Batched backfill that runs from a migration (outside a transaction block; COMMIT inside DO or a procedure is allowed only there):

DO $$
DECLARE
  chunk constant bigint := 5000;
  lower_key bigint := 0;
  upper_bound bigint;
BEGIN
  SELECT coalesce(max(shipment_id), 0) INTO upper_bound FROM shipments;
  WHILE lower_key <= upper_bound LOOP
    UPDATE shipments SET tracking_code = gen_random_uuid()
    WHERE shipment_id >= lower_key AND shipment_id < lower_key + chunk AND tracking_code IS NULL;
    lower_key := lower_key + chunk;
    COMMIT;            -- releases this chunk's row locks; each chunk is its own transaction
  END LOOP;
END
$$;

• Walk the primary key in ranges (keyset). WHERE col IS NULL LIMIT n without an index rescans from the start every batch.
• Set the column default before the backfill, so rows inserted during it are already filled.
• Keep batches small enough that each commits in well under a second on production hardware. On very large tables, run the backfill as a throttled background job, outside the deploy, and watch replication lag.
• Run inside a transaction block, COMMIT fails with invalid transaction termination. That's how you find out the runner wrapped your file.

Check a migration empirically before shipping

Run each statement of the migration against a copy with realistic data, inside BEGIN ... ROLLBACK, and look at what it actually did:

BEGIN;
SELECT relfilenode, pg_stat_get_xact_tuples_returned(oid) + pg_stat_get_xact_tuples_fetched(oid)
FROM pg_class WHERE oid = 'shipments'::regclass;                         -- note both values
-- <the DDL under test>
SELECT relfilenode, pg_stat_get_xact_tuples_returned(oid) + pg_stat_get_xact_tuples_fetched(oid)
FROM pg_class WHERE oid = 'shipments'::regclass;   -- new relfilenode => rewritten; difference => rows read
SELECT mode FROM pg_locks
WHERE relation = 'shipments'::regclass AND pid = pg_backend_pid() AND granted;  -- locks this tx now holds
ROLLBACK;

Use the difference: the xact counters can still include unflushed counts from earlier transactions in the same session. A large difference together with ShareLock, ShareRowExclusiveLock or AccessExclusiveLock means the statement blocks writes for as long as that scan takes in production. Statements that refuse to run in a transaction (CONCURRENTLY) can't be checked this way. Know their locks from the table above.

Review checklist

• For each statement: lock mode, tables, and whether it scans, rewrites or builds.
• No scan, rewrite or index build under SHARE or stronger on a table with live traffic.
• Indexes are built and dropped CONCURRENTLY, one statement per call, outside any transaction, with invalid leftovers dropped before a rebuild.
• Foreign keys and CHECKs are added NOT VALID and validated in a later transaction. Verify the runner really commits in between.
• NOT NULL goes through a NOT VALID constraint (PG 18) or a validated CHECK (PG 12-17), never a bare SET NOT NULL.
• No volatile default in ADD COLUMN, and no type change that rewrites a big table.
• Backfills are batched and each batch commits. The default is in place before the backfill.
• lock_timeout is set for strong-lock DDL (SET LOCAL, or RESET after a session-level SET), and the deploy can retry.
• Code and schema order: expand, deploy the code that uses both shapes, backfill, contract.
• Applied migrations are untouched. The whole history still applies to an empty database.

Anti-patterns

• ALTER TABLE t ADD COLUMN c uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE → rewrites t and builds the index under ACCESS EXCLUSIVE. Split it: add the column, set the default, backfill, build the index concurrently, add NOT NULL as NOT VALID and validate.
• ADD CONSTRAINT ... NOT VALID; VALIDATE CONSTRAINT ...; in one file of a transactional runner → same as a validated ADD. Move VALIDATE to a later, separately committed step.
• CREATE INDEX CONCURRENTLY in a normal migration → it errors inside the transaction. A plain CREATE INDEX "fixes" the error by blocking writes. Use the runner's no-transaction mode instead.
• UPDATE big_table SET ... in the same transaction as the ALTER TABLE that added the column → ACCESS EXCLUSIVE held for the whole backfill. Separate transactions, batched.
• ALTER COLUMN event_id TYPE bigint on a large table → full rewrite. Add a bigint column, keep it in sync with a trigger, backfill, build indexes concurrently, then swap in a short transaction.
• RENAME COLUMN while old code still runs → old pods error out. Expand/contract over two deploys.
• SET lock_timeout in a no-transaction file without RESET → silently applies to later migrations in the same session.
• A short lock_timeout on CREATE INDEX CONCURRENTLY → an INVALID index on timeout, and an IF NOT EXISTS retry keeps it.

More

• references/lock-matrix.md: lock, scan and rewrite behaviour per statement, measured on 18.6.
• references/recipes.md: full recipes, including type changes, renames, partitioned-table indexes and dropping columns.
• references/runners.md: how common migration tools handle transactions.
• references/sources.md: the documentation and experiments behind every claim.