Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Zero-Downtime Postgres Migrations: lock_timeout and NOT VALID

One ALTER TABLE waiting behind a slow SELECT blocks every query that arrives after it. The timeout-retry loop, NOT VALID constraints and CONCURRENTLY index rules.

~4 min readSQL
#PostgreSQL#Migrations#Locks#Operations
3D illustration of a migration gate holding an exclusive lock while a queue of query blocks piles up behind it, a clock node ticking

Introduction & Quick Summary

Symptom. A migration that ran in milliseconds on staging freezes production for ninety seconds. Queries unrelated to the migrated table time out, connection pools exhaust, and the deploy is rolled back with ALTER TABLE ... ACCESS EXCLUSIVE still waiting. In the log: canceling statement due to lock timeout or, worse, nothing at all until the whole API stops responding.

Cause. ALTER TABLE requests an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode including plain SELECT. PostgreSQL queues lock requests in order, so the moment an ALTER starts waiting behind one long-running query, every later query on that table queues behind the ALTER — a single slow SELECT becomes a full-table outage. Separately, operations that look cheap are not: SET NOT NULL scans the table under that exclusive lock, CREATE INDEX without CONCURRENTLY blocks writes, and a column type change may rewrite every row.

Fix. Wrap every DDL statement in a short lock_timeout with a retry loop so it fails fast instead of blocking the queue; use NOT VALID plus VALIDATE CONSTRAINT to validate without an exclusive lock; use CREATE INDEX CONCURRENTLY outside a transaction; and split destructive changes into an expand/contract pair across two deploys.

Core Mechanics, Signatures & Reproducible Setup

The safe migration is a retry loop around a statement-level timeout, because the alternative to waiting is failing.

sql
-- every migration file opens with this SET lock_timeout = '3s'; SET statement_timeout = '30s'; ALTER TABLE orders ADD COLUMN IF NOT EXISTS channel text; -- metadata only SET lock_timeout = '3s'; ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web'; -- metadata only on PG 11+

A migration runner that does not retry turns a lock timeout into a failed deploy, so the retry belongs above the SQL:

python
import time, psycopg def run_with_retry(conn, statements, attempts=10): for i in range(attempts): try: with conn.transaction(): conn.execute("SET LOCAL lock_timeout = '3s'") for sql in statements: conn.execute(sql) return except psycopg.errors.LockNotAvailable: time.sleep(min(2 ** i, 30)) # 1s, 2s, 4s … capped at 30s raise RuntimeError("could not acquire lock; migrate later")

Reproduce the queue effect in one psql session and one app query: hold a SELECT * FROM orders inside BEGIN (repeatable read) for ten seconds, then run the ALTER. The ALTER waits, and any new SELECT waits behind the ALTER, not behind the original transaction's duration.

Key Comparison Table

OperationLock takenBlocks readsBlocks writesSafe form
ADD COLUMN (no default)ACCESS EXCLUSIVE, instantbrieflybrieflyadd, then backfill
ADD COLUMN with constant defaultACCESS EXCLUSIVE, instant (PG 11+)brieflybrieflyfine as written
ADD COLUMN with volatile defaultACCESS EXCLUSIVE + rewritelonglongadd nullable, backfill, set default
SET NOT NULLACCESS EXCLUSIVE + full scanlonglongCHECK … NOT VALID, validate, then set
ADD CONSTRAINT … NOT VALIDACCESS EXCLUSIVE, instantbrieflybrieflypreferred
VALIDATE CONSTRAINTSHARE UPDATE EXCLUSIVEnonoalways this
CREATE INDEXSHARE, blocks writesnoyesCONCURRENTLY
ALTER TYPEACCESS EXCLUSIVE + rewritelonglongnew column, dual write, backfill
TimeoutScopeUse in a migration
lock_timeoutlock acquisition only2–5s, retried
statement_timeouttotal statement run time30s for DDL, longer for backfill
idle_in_transaction_session_timeoutidle transaction60s, protects against leaked locks
deadlock_timeoutdeadlock detectionleave default

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. NOT VALID then VALIDATE

The two-step adds a constraint that new rows must satisfy immediately, while old rows are checked later under a lock that permits reads and writes.

sql
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total >= 0) NOT VALID; -- separate statement, separate transaction, can take minutes safely ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

VALIDATE CONSTRAINT waits for concurrent writers to finish touching the rows it scans, so it can sit behind a long transaction — but it never blocks inserts or selects. Combine it with NOT NULL in PostgreSQL 12 and later and the scan is skipped entirely:

sql
ALTER TABLE orders ADD CONSTRAINT orders_channel_not_null CHECK (channel IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_not_null; ALTER TABLE orders ALTER COLUMN channel SET NOT NULL; -- reuses the proof

2. CREATE INDEX CONCURRENTLY cannot run in a transaction

Most migration tools wrap each migration in a transaction, and CONCURRENTLY fails there with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Move it to a standalone step and accept that it is not atomic: an interrupted build leaves an INVALID index that still costs writes and is used by nothing.

sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at ON orders (created_at) WHERE deleted_at IS NULL;
sql
-- detect a half-built index after a failed deploy SELECT indexrelid::regclass AS index, indrelid::regclass AS table FROM pg_index WHERE NOT indisvalid; -- then, outside a transaction DROP INDEX CONCURRENTLY idx_orders_created_at;

3. Backfilling in batches

An UPDATE over ten million rows takes one long transaction, holds row versions, and blocks vacuum. Batch it, commit between batches, and let autovacuum keep up:

sql
-- a procedure, not a DO block: COMMIT is only legal here CREATE PROCEDURE backfill_orders() LANGUAGE plpgsql AS $$ DECLARE rows_updated int; BEGIN LOOP UPDATE orders SET channel = 'web' WHERE id IN ( SELECT id FROM orders WHERE channel IS NULL LIMIT 5000 FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS rows_updated = ROW_COUNT; COMMIT; EXIT WHEN rows_updated = 0; PERFORM pg_sleep(0.1); END LOOP; END $$; CALL backfill_orders();

FOR UPDATE SKIP LOCKED keeps two backfill workers from fighting over the same rows.

4. Dropping a column in two deploys

DROP COLUMN is metadata-only and fast, and it instantly breaks any application instance still selecting that column. Deploy order matters more than the lock:

  1. Stop writing the column; deploy.
  2. Stop reading it; deploy.
  3. ALTER TABLE … DROP COLUMN … in a later migration.

Renaming is worse: ALTER TABLE … RENAME is instant and breaks old code just as fast. Add a new column, dual-write, backfill, then drop.

5. Finding who is blocking you

The migration's own log line names the timeout; pg_blocking_pids names the culprit:

sql
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, now() - query_start AS running, left(query, 80) AS query FROM pg_stat_activity WHERE datname = current_database() AND state <> 'idle' ORDER BY running DESC;

A blocked_by array that grows during the outage is the queue pile-up, and it is the evidence to show the reviewer who wants to raise lock_timeout instead.

6. Managed providers and migration runners

Prisma Migrate wraps migrations in a transaction and offers no per-statement lock_timeout; set it through the connection string (?options=-c%20lock_timeout%3D3000) and split CONCURRENTLY statements into a script. Alembic needs transaction_per_migration=False for the same reason, and op.execute("SET lock_timeout = '3s'") inside a transactional migration has no effect on the DDL that follows it in the same transaction.

Common Compilation/Runtime Mistakes & Version Gotchas

  • No lock_timeout at all. The default is zero, meaning wait forever, and every second of waiting blocks the queue behind it.
  • lock_timeout set inside the same transaction as the DDL. Acquisition happens at the statement, so this usually works — but a runner that starts a fresh connection per statement loses it.
  • CONCURRENTLY inside a transaction. Fails outright; move it out of the tool's transaction wrapper.
  • SET NOT NULL on a large table without the CHECK proof. Full scan under an exclusive lock.
  • Volatile defaults on ADD COLUMN. now(), gen_random_uuid(), and random() force a table rewrite even on PostgreSQL 11+.
  • Raising statement_timeout globally to let a migration finish. It also removes the guard on user queries.
  • Retrying the whole migration file. A partially applied file plus a naive retry produces duplicate objects; make every statement idempotent with IF NOT EXISTS.

Related tools. Read the failing statement with the SQL formatter to confirm which lock mode it requests, diff the previous and new migration files with the text diff to catch a rewritten ALTER, and translate the query_start timestamps next to each pg_stat_activity row with the timestamp converter.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.