Why One Blocked ALTER TABLE Stalls Every Query Behind It

The alert says the API’s p99 latency went from 40 ms to 30 seconds, the connection pool is exhausted, and the only thing that changed was a migration that “just adds a column”. The migration log shows the ALTER TABLE did eventually succeed, and in pg_stat_activity it ran for well under a second once it started. What happened in between is the PostgreSQL lock queue: the ALTER could not start because another transaction held a lock on the table, and while it waited, it made every subsequent query on that table wait behind it. This page reproduces the behaviour so you can see it for yourself, explains the queueing rule that causes it, and shows the timeout that stops it — the foundation for everything in DDL Lock Management & Timeouts.

The key insight is counter-intuitive: a table can be completely frozen by a lock that nobody holds yet. The ALTER is only waiting for ACCESS EXCLUSIVE, but its presence in the queue is enough to block every newcomer.

How the Queue Forms, Session by Session Sequence across three sessions and PostgreSQL. Session A begins a transaction and reads the table, holding ACCESS SHARE. Session B runs ALTER TABLE and waits for ACCESS EXCLUSIVE. Session C runs a plain SELECT and waits behind B. When A commits, B gets its lock, runs, commits, and only then does C proceed. How the Queue Forms, Session by Session Session A (report) Session B (migration) Session C (app) PostgreSQL BEGIN; SELECT ... FROM orders A holds ACCESS SHARE on orders ALTER TABLE orders ADD COLUMN ... B queued: wants ACCESS EXCLUSIVE SELECT * FROM orders WHERE id = 7 C queued behind B COMMIT (after 8 s) lock granted, ALTER runs rows returned (after 9 s)
Session C's SELECT is compatible with session A's lock, but it has to wait behind session B's pending exclusive request — PostgreSQL grants conflicting requests in arrival order.

Symptom / Error Signatures

You are looking at a lock-queue stall when these signals appear together during a migration window:

  • pg_stat_activity shows many sessions with wait_event_type = 'Lock' and wait_event = 'relation', all against the same table.
  • One of those waiting sessions is running DDL (ALTER TABLE, CREATE INDEX without CONCURRENTLY, DROP ...), and it is itself waiting.
  • Somewhere ahead of it is a session in state active with a long-running query, or in state idle in transaction with an old xact_start.
  • The application reports pool exhaustion: P2024 from Prisma, QueuePool limit ... overflow ... reached from SQLAlchemy, Connection is not available, request timed out from HikariCP.
  • If log_lock_waits is on, the server log contains lines such as:
LOG:  process 41877 still waiting for AccessExclusiveLock on relation 16402 of database 16384 after 1000.112 ms
DETAIL:  Process holding the lock: 41102. Wait queue: 41877, 41880, 41881, 41885.

That Wait queue list is the smoking gun: the first pid is the DDL, and every pid after it is an application query that arrived later and is now stuck.

Root Cause Analysis

PostgreSQL’s lock manager keeps, for each locked object, the set of granted locks and a queue of waiters. When a new request arrives, it is granted immediately only if it conflicts neither with any granted lock nor with any lock already waiting in the queue. That second condition exists to prevent starvation: without it, a steady stream of readers could keep an exclusive request waiting forever. The cost of that fairness is that a single waiting exclusive request converts the table from “shared by everybody” to “nobody new gets in”.

ACCESS EXCLUSIVE conflicts with all eight lock modes, so the moment an ALTER that needs it joins the queue, even ACCESS SHARE requests from plain SELECT statements must line up behind it. The stall lasts for the lifetime of the oldest conflicting transaction ahead of the DDL, plus the DDL’s own runtime. The DDL’s runtime is often negligible; the blocker’s lifetime is the whole problem.

Without and With a Waiting Exclusive Request Two panels. Left: with only readers on the table, a new SELECT is granted immediately because ACCESS SHARE is compatible with ACCESS SHARE. Right: once an ALTER is waiting for ACCESS EXCLUSIVE, a new SELECT is compatible with the granted lock but conflicts with the queued request, so it waits. Without and With a Waiting Exclusive Request Readers only queue is empty granted: report holds ACCESS SHARE new SELECT asks for ACCESS SHARE compatible with granted locks and queue granted at once no waiting ALTER waiting in the queue queue holds ACCESS EXCLUSIVE granted: report holds ACCESS SHARE queued: ALTER wants ACCESS EXCLUSIVE new SELECT conflicts with the queued ALTER SELECT waits until report and ALTER finish table frozen for new work
Compatibility is checked against the wait queue as well as the granted locks — that one rule is why the waiting ALTER, not the running report, freezes the table.

The typical blockers ahead of a migration are predictable. Analytics or reporting queries run against the primary and take minutes. ORMs or connection wrappers open a transaction at the start of a request and hold it while calling out to other services, leaving a session idle in transaction with its locks intact. Long batch jobs — a nightly export, a backfill without batching — hold ROW EXCLUSIVE for their duration. And pg_dump holds ACCESS SHARE on every table it is dumping for the whole run, which makes a daytime logical backup a classic migration blocker.

You can reproduce the whole thing in two minutes with three psql sessions against a scratch database:

-- PostgreSQL · scratch database only · three separate psql sessions
-- WARNING: never run the session-A step against production; it deliberately holds a lock.
-- session A
BEGIN; SELECT count(*) FROM orders;          -- leave this transaction open
-- session B
ALTER TABLE orders ADD COLUMN demo int;      -- hangs: waiting for ACCESS EXCLUSIVE
-- session C
SELECT * FROM orders WHERE id = 1;           -- also hangs: queued behind session B
-- session A
COMMIT;                                      -- B completes, then C returns immediately
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS demo;

Immediate Mitigation

When the stall is happening now, the goal is to empty the queue as fast as possible. Cancelling the DDL is almost always the right first move, because it is the one waiter blocking all the others.

1. Confirm the DDL is the head of the queue. Look for the waiting DDL and the sessions blocked behind it before you touch anything.

-- PostgreSQL 9.6+ · read-only · run as a role with pg_read_all_stats
SELECT pid, state, wait_event_type, now() - query_start AS waiting_for,
       pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

2. Cancel the waiting DDL. pg_cancel_backend cancels the current statement, and because the ALTER has not yet acquired its lock, nothing is half-applied. Every query queued behind it proceeds within milliseconds.

-- PostgreSQL · requires pg_signal_backend or superuser · safe: the DDL has not started work
SELECT pg_cancel_backend(41877);   -- the DDL's pid from step 1

3. Identify and deal with the blocker separately. With the application recovered, look at the pid in blocked_by. If it is an idle in transaction session, the application leaked a transaction; terminating it is usually safe. If it is an active report, reschedule it or move it to a replica.

4. Re-run the migration with a lock timeout. Only after the blocker is gone, and only with a short lock_timeout in place so that the next blocker produces a fast failure instead of a new stall.

Permanent Fix / Long-Term Pattern

The permanent fix is to make sure DDL can never wait long enough to matter. Put SET lock_timeout in every migration that takes a strong lock, and let the migration runner retry lock-timeout failures with backoff, as described in setting lock_timeout and retrying DDL safely. With a two-second timeout, the worst case for application queries becomes two seconds of queueing per attempt, which is inside most pool checkout budgets.

-- PostgreSQL · top of every migration file that takes ACCESS EXCLUSIVE
-- WARNING: SET LOCAL requires the tool to run the file inside a transaction; use SET otherwise.
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN fulfilment_region text;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS fulfilment_region;

Then shrink the population of blockers. Set idle_in_transaction_session_timeout for application roles so leaked transactions are cleaned up automatically, move reporting to a read replica as described in Read/Write Splitting Tactics, and schedule logical backups away from deploy windows. Finally, make the queue visible: log_lock_waits = on gives you a log line for every wait over deadlock_timeout, which is exactly the history you need when the next stall happens.

Worst-Case Stall Per Attempt, by Timeout Setting Stacked bars showing how long application queries can be stalled per migration attempt. With no lock_timeout the stall equals the blocker's remaining runtime, 180 seconds in this example, plus DDL time. With lock_timeout 2 seconds the stall is capped at 2 seconds per attempt; three retries total 6 seconds spread over a minute. Worst-Case Stall Per Attempt, by Timeout Setting no timeout 180 s lock_timeout 5 s lock_timeout 2 s queued behind blocker DDL work The blocker in this example is a 3-minute report; each timed-out attempt releases the queue immediately.
A lock_timeout does not make the blocker go away — it caps how long the queue can exist, so users see seconds of latency instead of a three-minute outage.

Verification Checklist

Frequently Asked Questions

Why doesn’t PostgreSQL let the SELECT jump ahead of the waiting ALTER? Because it would starve exclusive requests. If compatible requests could always jump the queue, a busy table would never have a moment with zero readers, and the ALTER would wait forever. PostgreSQL chooses fairness, which means one waiting exclusive request blocks newcomers until it is served or cancelled.

Is it safe to cancel a DDL statement that is waiting for its lock? Yes. A DDL statement waiting in the lock queue has not changed anything yet, and PostgreSQL runs DDL transactionally, so cancelling it rolls back cleanly. The application recovers the moment the waiter disappears from the queue.

Does CREATE INDEX CONCURRENTLY have the same problem? Less severely. It takes SHARE UPDATE EXCLUSIVE, which does not conflict with reads or ordinary writes, so it does not freeze the table while queued. It does, however, wait for older transactions at several points during the build, so a long transaction still slows it down — see building indexes with CREATE INDEX CONCURRENTLY.

Does the same queueing happen in MySQL? Yes, with metadata locks instead of table locks. A pending exclusive MDL request from DDL blocks new shared MDL requests, and the processlist fills with Waiting for table metadata lock. The MySQL-specific diagnosis is covered in diagnosing “Waiting for table metadata lock”.

Related Articles