Postgres Migration Safety: Locks, Online ALTERs, and Expand-Contract

A migration is "safe" when it cannot take your write path down while old and new code versions run side by side during a deploy. Postgres makes that achievable — and makes it easy to fail catastrophically with one innocent ALTER TABLE before you know which locks it takes. Paste any migration into the formatter before it goes in a PR; unreadable DDL is unreviewable DDL.

Honest note: no Postgres on this box — every claim is doc-verified against the PostgreSQL 16 manual and release notes, not executed here. Syntax is PG 11+ unless stated. Test on a staging copy of prod first.

The lock ladder, top to bottom

Postgres always waits its turn to acquire the lock below — the hidden failure mode is queue, not error. A long ACCESS EXCLUSIVE holder lines every later request up behind it, even the dashboard that only needed ACCESS SHARE.

Headline: most ALTER TABLE forms take ACCESS EXCLUSIVE — it waits for every existing query to finish and queues every new one behind it. One long-running read can stall an entire instance behind a 3 a.m. migration, without the migration doing anything itself.

The famous fast defaults

Since PG 11, ADD COLUMN ... NOT NULL DEFAULT <constant> writes no rows: the default lives in the catalog and existing rows are virtual.

ALTER TABLE orders
  ADD COLUMN status text NOT NULL DEFAULT 'new';

That is ACCESS EXCLUSIVE — for milliseconds, because no rewrite happens. Pre-11, the statement rewrote the table; the folklore ("always add nullable, backfill, set default in app") is now a version mismatch, not a safety rule. Two caveats:

• The default must be constant-ish. DEFAULT now() is fine (evaluated once, applied to all rows). Volatile defaults like gen_random_uuid() evaluate per row, forcing the rewrite and holding the lock for its full duration.
• The brief ACCESS EXCLUSIVE still queues behind any long-running read. With lock_timeout set, it fails fast instead of stalling traffic; without it, it waits.

RENAME, SET DEFAULT, DROP DEFAULT, and NOT VALID constraints also avoid rewrites; ALTER COLUMN TYPE does not. When a rewrite is genuinely needed on a hot table, skip ALTER and use pg_repack (below).

The two-part constraint life

ALTER TABLE ... ADD CONSTRAINT ... NOT VALID takes the big lock for microseconds, enforces the rule for all new writes, and leaves existing rows unexamined. Then, offline:

ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;  -- fast, blocks reads+writes for an instant

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;   -- slow, blocks writes only

VALIDATE takes SHARE UPDATE EXCLUSIVE: reads continue, writes pause. On a table with real traffic the correct sequence is always add-not-valid → validate — even for the CHECK you're sure passes, because "sure" is not a lock lifetime. FKs also briefly lock the referenced table; plan the blast radius accordingly.

Indexes: the concurrent lifecycle

Plain CREATE INDEX takes SHARE — no writes on the table for the build's duration, which on a large table is an outage-shaped event. The online form:

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

CONCURRENTLY runs in its own transaction: it cannot be executed inside a transaction block, which means most migration tools refuse it by default (Flyway: separate non-transactional migration; Rails: disable_ddl_transaction). It takes two brief locks around two table scans, blocks nothing but ACCESS EXCLUSIVE waiters, and waits for all pre-existing transactions before starting — one old snapshot-holding connection can stall the build for hours. Guardrails come first:

SET lock_timeout = '5s';
SET statement_timeout = '2h';
CREATE INDEX CONCURRENTLY ...;

lock_timeout = "abort if my brief locks take over 5s" (protects the world from the migration); statement_timeout = "abort past 2h" (protects the migration from a stalled wait). Both are session-level — set them in the same session as the DDL. A failed build leaves an INVALID index behind — invisible to queries, a permanent drag on every write until you DROP INDEX CONCURRENTLY it. Script that cleanup; a "just works" retry loop can leave five invalid copies.

REINDEX CONCURRENTLY (PG 12+) and DROP INDEX CONCURRENTLY behave with the same courtesy — and the same no-transaction rule.

Expand-contract: migrations that survive a rolling deploy

Zero downtime is mostly ordering. During a deploy, old and new code share the schema, so every intermediate state must be valid for both. An email → contact_email rename, expanded:

-- 1. EXPAND: additive only, old code never sees a change
ALTER TABLE users ADD COLUMN contact_email text;
-- 2. backfill in batches (see below), then dual-write from the app
-- 3. flip reads to the new column, deploy, soak
-- 4. CONTRACT (separate deploy, weeks later):
ALTER TABLE users DROP COLUMN email;  -- after old code is fully gone

Rules: never rename — add, migrate, drop, in separate releases; never reuse a column name for a new meaning inside one cycle. A backfill is part of expand: an unbatched UPDATE ... WHERE col IS NULL on 100M rows is one long ROW EXCLUSIVE-holding transaction — batch it:

UPDATE users SET contact_email = email
WHERE ctid IN (SELECT ctid FROM users
               WHERE contact_email IS NULL LIMIT 10000
               FOR UPDATE SKIP LOCKED);

SKIP LOCKED (the same trick as the job-queue pattern) means the backfill never fights live writes; small batches keep row locks short and the WAL digestible.

pg_repack: online rewrites without the folklore

pg_repack journals concurrent writes into a shadow copy, then swaps it in under a brief lock:

pg_repack -d appdb -t orders -j 4

Use it for rewrites ALTER still forces (type changes) and for bloat you'd otherwise VACUUM FULL — a lock held for the entire rewrite, the most common self-inflicted outage here. The journal consumes disk; on a table with a real no-write window, plain ALTER stays simpler.

The guardrail block

Prepend to every migration, non-negotiable, in this order:

SET lock_timeout = '5s';
SET statement_timeout = '30min';
ALTER TABLE ...;

With lock_timeout set, "migration wants a lock somebody holds" becomes a clean, retriable error — minutes of retry, not a page. If your runner can't emit per-migration SET, initialize it on the migration role; a pooler that resets session state can silently drop it.

FAQ

Is ADD COLUMN NOT NULL DEFAULT finally safe? On PG 11+, yes for constant defaults: no rewrite, catalog-only, sub-second exclusive lock. Volatile defaults (gen_random_uuid()) still rewrite row-by-row.

Why did CREATE INDEX CONCURRENTLY error "cannot run inside a transaction block"? Correct behavior. It must run outside a transaction; configure your migration tool to mark that step non-transactional. Do not "fix" it by dropping CONCURRENTLY on a hot table.

The index build has been "running" for hours with no progress. Alive? Probably waiting for an old transaction. Check pg_stat_activity for long-running idle-in-transaction sessions, then lock_timeout and reschedule.

We deploy with zero downtime. Why did last Tuesday hurt? Someone shipped contract before expand — dropped or renamed something the old pods were still reading. Rule: nothing is removed in the same release window that stops referencing it.