Postgres ALTER TABLE Hangs: Find the Blocker, Then Decide Who to Cancel

Your ALTER TABLE is not slow. It is sitting in a lock queue, it has not touched a single row yet, and every query that arrived after it on the same table is queued behind it. Open a second session and run this:

-- What is running, what is waiting, and how old each transaction is
SELECT pid,
       application_name,
       backend_type,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start AS tx_age,
       left(query, 120)   AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state IS DISTINCT FROM 'idle'
ORDER BY xact_start;

Find your migration’s row. If the migration set application_name, add AND application_name LIKE 'migrate:%' to the WHERE clause and it is the only row left. If the row shows wait_event_type = Lock and wait_event = relation, the statement has not begun its work. Per the PostgreSQL monitoring documentation, a Lock wait event type means “The server process is waiting for a heavyweight lock”, and the relation wait event means “Waiting to acquire a lock on a relation.” The row with the oldest xact_start is almost always the reason.

That is the diagnosis. The rest of this page is how to confirm who is in front of you, and why the first process you cancel should usually be your own migration rather than the session blocking it.

Step two: name the blocker

Do not read pg_locks by hand. Postgres ships a function that answers this question directly:

-- Replace 12345 with your migration's pid from the query above
SELECT a.pid,
       a.application_name,
       a.backend_type,
       a.state,
       now() - a.xact_start    AS tx_age,
       now() - a.state_change  AS in_this_state_for,
       left(a.query, 80)       AS query
FROM pg_stat_activity a
WHERE a.pid = ANY (pg_blocking_pids(12345));

The documentation for pg_blocking_pids defines exactly what it counts, and the second half of that definition is the whole story of this incident: “One server process blocks another if it either holds a lock that conflicts with the blocked process’s lock request (hard block), or is waiting for a lock that would conflict with the blocked process’s lock request and is ahead of it in the wait queue (soft block).”

Hard block is your migration waiting on somebody else. Soft block is everybody else waiting on your migration. If what you need is how long a wait has been running rather than who is causing it, pg_locks.waitstart has it from PostgreSQL 14 on: “Time when the server process started waiting for this lock, or null if the lock is held.”

Quick reference: what is in front of your ALTER TABLE

The blocker’s row shows	Lock it holds on your table	Why your ALTER TABLE waits	What to do about the blocker
state = active, a long SELECT, a report, a pg_dump	ACCESS SHARE	ACCESS EXCLUSIVE conflicts with every lock mode, including the weakest one	Usually let it finish. Cancel your migration first
state = idle in transaction	Whatever it touched, often ROW EXCLUSIVE, held until COMMIT	The lock is held to the end of the transaction, and the transaction is not ending	Terminate the session, then fix the client that leaked it
backend_type = autovacuum worker, query like autovacuum: VACUUM public.orders	SHARE UPDATE EXCLUSIVE	Conflicts, but Postgres interrupts plain autovacuum for you	Nothing. Your lock request cancels it automatically
Same, but query ends (to prevent wraparound)	SHARE UPDATE EXCLUSIVE	Anti-wraparound autovacuum is not interrupted	Leave it alone. Cancel your migration and come back later
state = active, CREATE INDEX ... ON orders without CONCURRENTLY	SHARE	Conflicts, and an index build on a large table runs for minutes	Cancel your migration, let the build finish
state = active, another ALTER TABLE orders	ACCESS EXCLUSIVE	Conflicts with itself	Cancel your migration. Two DDL statements are racing for the same table

Column 2 is not a guess. The explicit locking page lists, for all eight modes, exactly which commands acquire them.

Why every query on the table is stuck behind a statement that has not started

ALTER TABLE takes the strongest lock Postgres has. The documentation states the default plainly: “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” The exceptions are few, ADD FOREIGN KEY at SHARE ROW EXCLUSIVE and VALIDATE CONSTRAINT at SHARE UPDATE EXCLUSIVE being the ones worth knowing, and the full per-statement map is on the lock mode reference.

ACCESS EXCLUSIVE “conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE).” Every single mode, including the one a plain SELECT takes. That is why any activity at all on the table, not just writes, is enough to make your migration wait.

The part that turns a wait into an outage is what happens to requests that arrive afterwards. A new lock request does not get to step over a process already queued for a conflicting mode. In LockAcquireExtended() in src/backend/storage/lmgr/lock.c, the first thing checked is the wait queue, not the granted locks, under the comment “If lock requested conflicts with locks requested by waiters, must join wait queue”:

if (lockMethodTable->conflictTab[lockmode] & lock->waitMask)
    found_conflict = true;
else
    found_conflict = LockCheckConflicts(lockMethodTable, lockmode,
                                        lock, proclock);

src/backend/storage/lmgr/README spells out the consequence: “Each waiter is awoken if (a) its request does not conflict with already-granted locks, and (b) its request does not conflict with the requests of prior un-wakable waiters. Rule (b) ensures that conflicting requests are granted in order of arrival.”

So once your ALTER TABLE joins the queue for ACCESS EXCLUSIVE:

• Sessions that already hold a lock keep running to completion. Their locks were granted before your request existed. The long analytics SELECT is not affected at all.
• Every new statement touching that table joins the queue behind you. A plain SELECT that would never have conflicted with the analytics query now waits, because it conflicts with the ACCESS EXCLUSIVE you are requesting.
• Statements on other tables are untouched. The blast radius is exactly the tables named in the transaction, which is why a migration holding locks on two tables at once is twice the problem.

And nothing is released early. Per the locking documentation: “Once acquired, a lock is normally held until the end of the transaction.”

Reproduce it in three psql sessions

On a scratch database, this takes about a minute:

-- Session A, setup. lock_timeout is deliberately left at its default of 0
-- (disabled), because the point of this exercise is to watch the wait happen.
CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  status      text,
  total_cents integer NOT NULL
);
INSERT INTO orders (status, total_cents)
SELECT 'shipped', i FROM generate_series(1, 200000) AS i;

BEGIN;
SELECT count(*) FROM orders;  -- takes ACCESS SHARE, held until COMMIT
-- Now stop typing. This session is 'idle in transaction'.

-- Session B, the migration
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;  -- hangs here

-- Session C, arrives after the migration and is not even a write
SELECT count(*) FROM orders;  -- hangs too, behind Session B

Session C is the demonstration. It does not conflict with Session A in any way: two ACCESS SHARE locks coexist happily. It waits because Session B is ahead of it in the queue asking for something that conflicts with both of them. Commit or roll back Session A and the queue drains in order of arrival: Session B is granted ACCESS EXCLUSIVE and finishes, then Session C runs.

The migration that did this, and what on-call actually saw

orders holds 40 million rows, shipments holds 12 million, and this shipped on a Tuesday afternoon:

-- migrations/0142_tighten_nullable_columns.sql
BEGIN;

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE shipments ALTER COLUMN carrier SET NOT NULL;

COMMIT;

The migration log reported eleven minutes. Not one of those minutes was spent scanning a table. The first ALTER TABLE queued behind a connection-pool session that had run a SELECT, returned control to the application, and then sat in idle in transaction because an exception path skipped its commit.

What the dashboards showed: read error rate climbing before write error rate, because reads are the higher-volume path and ACCESS EXCLUSIVE blocks them equally. p99 latency flat, then vertical. Connection count at the pool ceiling inside forty seconds. Then healthcheck failures and pod restarts, each restart opening fresh connections that immediately joined the same queue. pg_stat_activity showed roughly 600 rows with wait_event_type = Lock, wait_event = relation, and one row with state = idle in transaction, a tx_age of eleven minutes, and no wait event at all. That last row is the blocker: it is the only session in the list waiting for nothing.

Should I cancel the ALTER TABLE or the query blocking it?

Cancel the ALTER TABLE first. Your migration is a single stalled statement. The queue behind it is the outage.

-- 12345 is the migration's pid. Ends the statement, keeps the session.
SELECT pg_cancel_backend(12345);

Per the documentation, pg_cancel_backend “Cancels the current query of the session whose backend process has the specified process ID.” A backend asleep on a heavyweight lock responds to it: ProcSleep() in src/backend/storage/lmgr/proc.c waits on a latch specifically so that “cancel/die interrupts are processed quickly”, and LockErrorCleanup() removes the process from the wait queue on the way out. The migration session gets the error emitted by ProcessInterrupts() in src/backend/tcop/postgres.c:

ERROR:  canceling statement due to user request

The queue on the table it was waiting for drains in the same instant, in order of arrival, usually before you have finished reading the blocker’s row. Locks the transaction already took on earlier tables are released by the ROLLBACK that follows, not by the cancel, which is one more reason to keep one table per transaction.

Killing the blocker instead does the opposite of what it feels like it does. The blocker releases its lock, your ALTER TABLE is granted ACCESS EXCLUSIVE, and now it starts the work it had not started yet. On a 40-million-row table, SET NOT NULL means a full scan under that lock. Per the ALTER TABLE documentation: “Ordinarily this is checked during the ALTER TABLE by scanning the entire table.” The 600 queued sessions stay queued, and now they are queued behind a statement that is genuinely busy rather than one you could have ended instantly.

Cancelling also costs nothing. The statement never acquired its lock, so it never modified anything: the cancel raises an error, the transaction goes to aborted state, and your migration tool issues ROLLBACK. Nothing is left half applied.

If pg_cancel_backend does not take effect within a few seconds, escalate to pg_terminate_backend(12345), which “Terminates the session whose backend process has the specified process ID” and reports terminating connection due to administrator command. Reach for cancel first: it leaves the connection alive and the migration tool’s error handling intact.

Then, and only then, deal with the blocker

With the queue drained you can look at the blocker without a clock running. The table above carries the verdicts. Two of them have documentation behind them that is worth having in front of you before you act.

idle in transaction: a session in this state holds every lock it acquired with no statement running and no end in sight. idle_in_transaction_session_timeout exists for exactly this, and the documentation is explicit about the purpose: “This option can be used to ensure that idle sessions do not hold locks for an unreasonable amount of time.” Thirty seconds is a reasonable value on an application connection pool.

An autovacuum worker: “If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum.” The exception is the one you must recognize on sight: “However, if the autovacuum is running to prevent transaction ID wraparound (i.e., the autovacuum query name in the pg_stat_activity view ends with (to prevent wraparound)), the autovacuum is not automatically interrupted.” That worker is protecting the database from a shutdown, and it is the one case where you reschedule the deploy instead.

The rewrite that keeps this out of the queue

Three changes, in order of how much they buy you.

First, bound the wait. The lock_timeout parameter is documented as: “Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object.” A value of zero, the default, disables it. Two seconds of failed migration beats forty seconds of saturated pool, and the full argument for why is in the lock_timeout death spiral. One detail matters when a migration has several statements: “The time limit applies separately to each lock acquisition attempt”, so the budget is per lock acquisition, not per file, and a statement that locks two tables can spend it twice.

-- Migration header. Every one of these lines does a separate job.
SET lock_timeout = '2s';
SET statement_timeout = '5min';
SET idle_in_transaction_session_timeout = '30s';
SET application_name = 'migrate:0142_tighten_nullable_columns';

application_name is the one people skip. It is also the column that turns the very first query on this page from a guessing game into a lookup.

Second, split the transaction. Both statements hold ACCESS EXCLUSIVE until COMMIT, so orders stays locked for the whole time shipments is being scanned: one table per migration.

Third, stop taking the lock for a full scan at all. SET NOT NULL normally scans the whole table under ACCESS EXCLUSIVE, but PostgreSQL 12 added an escape hatch, listed in its release notes as “Allow ALTER TABLE … SET NOT NULL to avoid unnecessary table scans”, and documented on the PostgreSQL 14 ALTER TABLE page as: “Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint exists (and is not dropped in the same command) which proves no NULL can exist, then the table scan is skipped.” That is the recipe pgfence emits, and on PostgreSQL 11 and earlier it does not apply, the scan happens regardless.

-- Migration 1: brief ACCESS EXCLUSIVE, no scan
SET lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT chk_status_nn CHECK (status IS NOT NULL) NOT VALID;

-- Migration 2: separate deploy. SHARE UPDATE EXCLUSIVE, reads and writes keep running.
SET lock_timeout = '2s';
ALTER TABLE orders VALIDATE CONSTRAINT chk_status_nn;

-- Migration 3: the validated CHECK proves the column, so this skips the scan on PG12+
SET lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
-- Separate command on purpose: the docs only skip the scan if the CHECK
-- is not dropped in the same command.
ALTER TABLE orders DROP CONSTRAINT chk_status_nn;

Between migrations 1 and 3 the constraint is enforced for every new row while pg_constraint.convalidated stays false until migration 2 finishes, so the intermediate state is safe to leave in production for days.

What pgfence does with this

pgfence reads the transaction shape, not just the statements. On the original file it reports the two ACCESS EXCLUSIVE locks compounding inside one transaction, the lock window spanning two tables, and the missing session settings that would have bounded the wait. Statement table and safe-rewrite recipes trimmed here for width:

  migrations/0142_tighten_nullable_columns.sql  [MEDIUM]
  Lock: ACCESS EXCLUSIVE | Blocks: reads+writes+DDL | Risk: MEDIUM | Rule: alter-column-set-not-null

  Policy Violations:
  WARNING Multiple statements holding ACCESS EXCLUSIVE lock in same transaction: "ALTER TABLE shipments ALTER COLUMN carrier SET NOT NULL" runs while ACCESS EXCLUSIVE is already held from "ALTER TABLE orders ALTER COLUMN status SET NOT NULL". This compounds the lock duration, blocking all reads and writes for the entire transaction.
    → Split into separate transactions so each ACCESS EXCLUSIVE lock is held for the minimum time
  WARNING Wide lock window: ACCESS EXCLUSIVE locks held on multiple tables ("orders" and "shipments") in the same transaction. This multiplies the blast radius of lock contention.
    → Split operations on different tables into separate transactions to minimize lock overlap
  ERROR Missing SET lock_timeout: without this, an ACCESS EXCLUSIVE lock will queue behind running queries and every new query queues behind it, causing a lock queue death spiral
    → Add SET lock_timeout = '2s'; at the start of the migration
  WARNING Missing SET statement_timeout: long-running operations can block other queries indefinitely
    → Add SET statement_timeout = '5min'; at the start of the migration
  WARNING Missing SET application_name: makes it harder to identify migration locks in pg_stat_activity
    → Add SET application_name = 'migrate:<migration_name>';
  WARNING Missing SET idle_in_transaction_session_timeout: orphaned connections with open transactions can hold locks indefinitely
    → Add SET idle_in_transaction_session_timeout = '30s';

=== Coverage ===
Postgres ruleset: PG14+ (configurable)
Analyzed 4 SQL statements. 0 dynamic statements not analyzable. Coverage: 100%

npx @flvmnt/pgfence analyze migrations/*.sql

What it does not do is watch a running database. pgfence is a static analyzer: it tells you, before merge, that this file will queue on two tables at once with no timeout. It cannot tell you that a pool session is idle in transaction right now. That is what the first query on this page is for.

Review checklist

If it is happening right now:

1. Read wait_event_type and wait_event for the stalled statement before touching anything. They decide whether you are looking at a queue or at a slow statement, and the two have different fixes.
2. Cancel your own migration first with pg_cancel_backend. It drains the queue instantly and rolls back cleanly, because a statement that never got its lock never changed anything.
3. Check backend_type and the query text before killing a blocker. An autovacuum worker cancels itself, and one running (to prevent wraparound) must be left alone.

Before the next one merges:

4. Reject any migration whose first line is not SET lock_timeout. Reject any that holds ACCESS EXCLUSIVE on two tables in one transaction.
5. Set idle_in_transaction_session_timeout on the application role, not just in the migration. The blocker in this incident was an application connection, not a migration.

Related

• The lock_timeout death spiral
• Postgres lock mode cheat sheet
• ENABLE ROW LEVEL SECURITY returns zero rows, not an error
• ALTER COLUMN SET NOT NULL: lock mode, risk, and safe rewrite
• Transaction and policy checks