The deploy is mid-flight, the migration runner sits at ALTER TABLE orders ADD COLUMN ..., and nothing moves. CPU is flat, no errors in the log. Here is why: most DDL in Postgres takes an ACCESS EXCLUSIVE lock, the strongest lock there is, and it has to wait behind any other transaction that already touched the table — even a sleepy SELECT from an analyst or an open pg_dump. The brutal part is the lock queue: while your ALTER TABLE waits, every new query against that table queues behind it too, including plain reads. So one stuck migration can take a whole table offline.
Fastest fix: find the session blocking you with pg_blocking_pids() (Step 1), cancel or terminate it (Step 2), then re-run the migration with SET lock_timeout = '5s' (Step 3) so the next attempt fails fast and retries instead of freezing the table again. All examples below are verified against Postgres 18 (current as of June 2026); the lock behavior is unchanged back to Postgres 11.
Which bucket are you in?
Run the diagnostic query in Step 1 first, then match the symptom:
Symptom in pg_stat_activity | Likely cause | Action |
|---|---|---|
Another session with a long xact_start, same table | Long-running txn / forgotten psql | Cancel it (Step 2) |
application_name = 'pg_dump' | Concurrent backup holding ACCESS SHARE | Wait or reschedule — do not kill blindly |
query starts autovacuum: VACUUM | Autovacuum / VACUUM FULL on the table | Let it finish, or set a maintenance window |
state = 'idle in transaction' for minutes | App opened BEGIN and never committed | Terminate it (Step 2) |
Your ALTER is active, wait_event_type is null | Not blocked — the DDL is doing real work | Wait; see Step 4 to avoid a rewrite next time |
Common causes
Ordered by hit rate.
1. Long-running transaction holds a competing lock
A reporting query, a forgotten psql session, or an ORM transaction left open is holding a ROW EXCLUSIVE or ACCESS SHARE lock on the target table. ACCESS EXCLUSIVE conflicts with every other lock mode, so your ALTER TABLE waits.
How to spot it: pg_stat_activity shows a long-running statement against the same table with an old xact_start.
2. pg_dump running concurrently
pg_dump holds ACCESS SHARE on every table it reads for the entire dump duration. ALTER TABLE will wait until the dump completes.
How to spot it: an application_name = 'pg_dump' session in pg_stat_activity.
3. Autovacuum on the same table
A normal autovacuum holds a SHARE UPDATE EXCLUSIVE lock — weak enough for reads and writes, but it conflicts with DDL. Worse, VACUUM FULL or CLUSTER takes an ACCESS EXCLUSIVE itself.
How to spot it: autovacuum: VACUUM table in pg_stat_activity.query.
4. Idle in transaction from the app
Someone ran BEGIN and never committed (often a debugger paused on a breakpoint, or a dropped connection that never signaled close). The transaction still holds whatever locks it grabbed.
How to spot it: state = 'idle in transaction' for many minutes.
5. The DDL itself is doing work, not blocked
ADD COLUMN ... DEFAULT <volatile> (e.g. clock_timestamp()), a stored generated column, an identity column, or ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT rewrites or scans the whole table. It is not stuck — it is working.
How to spot it: the session is active, not waiting on a lock, and wait_event_type IS NULL.
Shortest path to fix
Step 1: Identify the blocker
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.application_name,
blocking.state,
age(now(), blocking.xact_start) AS xact_age,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.query ILIKE 'ALTER TABLE%';This returns exactly who is blocking the migration. pg_blocking_pids() (Postgres 9.6+) is precise and much lighter than the older recipes that self-join pg_locks. Note the blocking_pid, the application_name, and state — they tell you which row of the table above you are in.
Step 2: Terminate the blocker (with care)
For ordinary app or analyst sessions, cancel first, then terminate if it does not let go:
-- Try cancel first (cancels the current query, keeps the connection)
SELECT pg_cancel_backend(12345);
-- If it does not release the lock (e.g. idle in transaction), terminate the whole backend
SELECT pg_terminate_backend(12345);pg_cancel_backend only stops a running statement; it cannot clear an idle in transaction session, so those need pg_terminate_backend. Do not blindly kill pg_dump if it is part of your backup pipeline — pause the migration and let the dump finish, or schedule the migration after the dump.
Step 3: Re-run with a lock_timeout
Make the migration fail fast and retry instead of joining the lock queue and freezing the table.
SET lock_timeout = '5s';
SET statement_timeout = '0'; -- the DDL itself can legitimately run long
ALTER TABLE orders ADD COLUMN shipping_method text;Set it per migration session in your runner:
# alembic.ini — apply before each migration
sqlalchemy.connect_args = { "options": "-c lock_timeout=5s" }-- Flyway / raw SQL: set at the top of the migration script
SET lock_timeout TO '5s';If the lock cannot be acquired in time, the statement aborts with:
ERROR: canceling statement due to lock timeoutPostgres does not retry on its own — that is on you or your runner. Wrap the migration in a retry loop with exponential backoff (most tools, e.g. pgroll and gh-ost-style runners, do this for you). A train of short retries beats one long stall, because between attempts the lock queue drains and your reads keep flowing. Keep lock_timeout short; values under 2 seconds are common for busy systems.
Step 4: Use a safer DDL pattern
ADD COLUMN is fast or slow depending on the default, not on nullability:
-- Postgres 11+: a constant / non-volatile DEFAULT is metadata-only and instant,
-- even on a huge table. now() counts as non-volatile here.
ALTER TABLE orders ADD COLUMN created_at timestamptz DEFAULT now();
-- DANGER: a volatile DEFAULT rewrites the ENTIRE table + indexes under ACCESS EXCLUSIVE.
-- (clock_timestamp(), random(), gen_random_uuid() per-row, stored generated columns,
-- identity columns, and domain types with constraints all trigger a rewrite.)
ALTER TABLE orders ADD COLUMN token uuid DEFAULT clock_timestamp(); -- avoid on big tablesFor NOT NULL on a large table, split it into three cheap steps so no single statement rewrites the table:
ALTER TABLE orders ADD COLUMN shipping_method text; -- 1. add nullable
UPDATE orders SET shipping_method = 'standard' WHERE shipping_method IS NULL; -- 2. backfill in batches
ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL; -- 3. add the constraintFor new indexes, use CREATE INDEX CONCURRENTLY so the build never takes ACCESS EXCLUSIVE:
CREATE INDEX CONCURRENTLY orders_shipping_method_idx
ON orders (shipping_method);Two caveats that bite people:
CREATE INDEX CONCURRENTLYcannot run inside a transaction block. Some runners need an opt-out (Alembic:op.create_index(..., postgresql_concurrently=True), autocommit migration).- If it fails partway (including a
lock_timeout), it leaves behind an invalid index. The next retry then errors withrelation "..._idx" already exists. Drop the leftover before retrying:
-- find invalid indexes
SELECT indexrelid::regclass AS idx FROM pg_index WHERE indisvalid = false;
DROP INDEX CONCURRENTLY orders_shipping_method_idx;Step 5: Confirm it’s fixed
\d+ orders -- column / index is present
SELECT relname, n_live_tup FROM pg_stat_user_tables
WHERE relname = 'orders';
SELECT * FROM pg_locks WHERE relation = 'orders'::regclass; -- no leftover ACCESS EXCLUSIVE
SELECT pid, state, age(now(), xact_start)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start; -- no new stragglersIf the column shows in \d+, there is no lingering lock on orders::regclass, and your application’s queries against the table return normally, the migration is done and the queue has drained.
Prevention
- Every migration sets
lock_timeout(commonly under 2 s, up to ~30 s for off-peak DDL) and is retry-safe. - Set
idle_in_transaction_session_timeout(e.g.'60s') at the database or role level so abandoned transactions get killed automatically — these are the most common blockers. - Schedule migrations outside backup and heavy-reporting windows.
- Use
CREATE INDEX CONCURRENTLY, and use constant defaults; neverADD COLUMN ... DEFAULT <volatile>on a large table. - For risky migrations, dry-run on a recent prod-sized clone (
pg_dump+pg_restoreto staging) and time the lock window.
FAQ
Will killing the blocking session corrupt my data?
No. pg_cancel_backend aborts the in-flight query and pg_terminate_backend rolls back the whole transaction cleanly — Postgres is transactional, so nothing half-committed survives. The risk is application-level: you cancelled someone’s work, so they may see an error and retry.
Can I just lower statement_timeout instead of using lock_timeout?
No. statement_timeout caps total run time, so it would also kill a legitimately long DDL (a large rewrite). lock_timeout caps only the time spent waiting to acquire a lock, which is exactly the stall you want to bound. Keep statement_timeout = 0 (or generous) for the DDL itself.
My ALTER TABLE shows active, not waiting. Why is it slow?
It is not blocked — it is rewriting or scanning the table (a volatile default, a stored generated column, VALIDATE CONSTRAINT, etc.). See Step 4. Cancelling it just wastes the work; instead, restructure the migration to avoid the rewrite.
Why did my migration retry fail with relation "..._idx" already exists?
A previous CREATE INDEX CONCURRENTLY failed and left an invalid index. Drop it (DROP INDEX CONCURRENTLY ...) and re-run. Query pg_index WHERE indisvalid = false to find these.
Is adding a nullable column always instant?
Adding a column is instant when its default is constant/non-volatile (including now()) — that has been metadata-only since Postgres 11. Nullability is not the deciding factor; the default’s volatility is.