Lock Waits That Look Like Slow Plans #

A query that normally returns in 3 ms occasionally takes 40 seconds. Its plan is unchanged, it reads six buffers, and EXPLAIN ANALYZE — when you finally reproduce it — shows an execution time of 40 seconds with essentially no work done. The query was not slow; it was waiting for a lock held by a migration running in another session.

Finding the expensive node in a plan is covered in identifying plan bottlenecks. This page covers the case where no node is expensive at all.

The Condition #

A statement waits when it needs a lock another transaction holds. The common cases:

In EXPLAIN ANALYZE, waiting shows up as a large actual time with no corresponding work: few buffers, few rows, no I/O timings. The wait typically happens before the first row is produced, so the node’s startup time carries it.

The definitive evidence is outside the plan: pg_stat_activity.wait_event_type = 'Lock' while the query runs, and pg_locks showing which session holds the conflicting lock.

Waiting, or working? Three cases. Large execution time with high buffers or I/O timings is genuine work. Large execution time with almost no buffers and no I/O is a wait. A wait event type of Lock in pg_stat_activity during execution confirms it, and pg_locks identifies the blocking session. query slow — which kind of slow? high buffers or I/O time genuine work tune the plan no buffers, no I/O, long time waiting find the blocker wait_event_type = 'Lock' confirmed lock wait pg_locks shows who a plain SELECT never waits for row locks; DDL blocks everything

Annotated Evidence #

The plan, captured during a wait:

EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET balance = balance - 10 WHERE id = 4821;

Update on accounts  (actual time=41204.6..41204.6 rows=0 loops=1)
  Buffers: shared hit=8
  ->  Index Scan using accounts_pkey on accounts  (actual time=41204.2..41204.3 rows=1 loops=1)
        Index Cond: (id = 4821)
        Buffers: shared hit=4
Execution Time: 41204.9 ms
-- 41 seconds, 8 buffers, no I/O timings: nothing was done

Live evidence while it waits:

SELECT pid, state, wait_event_type, wait_event, now() - query_start AS waited, left(query, 50)
FROM pg_stat_activity WHERE wait_event_type = 'Lock';
  pid  | state  | wait_event_type | wait_event |   waited    |                query
-------+--------+-----------------+------------+-------------+----------------------------------
 41204 | active | Lock            | transactionid | 00:00:38.2 | UPDATE accounts SET balance = …

Who is blocking:

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       now() - blocking.xact_start AS blocker_age,
       left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE blocked.wait_event_type = 'Lock';
 blocked_pid | blocking_pid | blocker_age |                  blocking_query
-------------+--------------+-------------+--------------------------------------------------
       41204 |        38102 | 00:12:41    | ALTER TABLE accounts ADD COLUMN notes text

And from the log, with log_lock_waits = on:

LOG:  process 41204 still waiting for ShareLock on transaction 918204 after 1000.124 ms
DETAIL:  Process holding the lock: 38102. Wait queue: 41204, 41207, 41211.
Four signals of a lock wait Four items are annotated. A long execution time with a handful of buffers. A wait event type of Lock in pg_stat_activity. pg_blocking_pids naming the holder. A log line listing the wait queue behind one blocking process. 41,204 ms with Buffers: shared hit=8 time without work wait_event_type = 'Lock' confirmed while running pg_blocking_pids → 38102 (ALTER TABLE) the holder Wait queue: 41204, 41207, 41211 everything queued behind it capture this while the wait happens; afterwards it is invisible

Step-by-Step Resolution #

  1. Check for time without work in the plan: large actual time, few buffers, no I/O timings.

  2. Catch it live. Lock waits leave little trace after the fact, so sample pg_stat_activity during the slow window, or enable log_lock_waits = on with deadlock_timeout (default 1 s) as the threshold so every wait longer than that is logged with the blocking process.

  3. Identify the blocker with pg_blocking_pids(), and look at its age — a blocker that has been in a transaction for minutes is usually the real problem.

  4. Fix the blocker’s pattern, not the victim: DDL run without lock_timeout, a transaction left open by an application, an interactive session with an uncommitted statement, or a long analytical query holding a share lock.

  5. Protect DDL with timeouts so a migration fails fast rather than queueing and blocking everything behind it:

    SET lock_timeout = '3s';
ALTER TABLE accounts ADD COLUMN notes text;

  6. Bound application transactions. Set idle_in_transaction_session_timeout so forgotten transactions cannot hold locks indefinitely.

  7. For hot-row contention, reduce the time each transaction holds the row: shorter transactions, batching, or a design that avoids a single counter row — the pattern also discussed in partial indexes for job queue polling.

How one migration blocks everything A timeline. A reporting transaction starts and holds a share lock on the table. A migration requests an access exclusive lock and queues behind it. Every subsequent query on the table queues behind the migration, including plain selects. When the report commits, the migration runs and the queue drains. report begins holds ShareLock ALTER TABLE queues needs AccessExclusive reads queue too behind the ALTER report commits ALTER runs queue drains latency spike ends the migration blocks reads only because it is itself blocked

Before and After #

-- BEFORE: migration without a lock timeout, behind a 12-minute report
UPDATE … Execution Time: 41204.9 ms   Buffers: shared hit=8      (waiting)

-- AFTER: SET lock_timeout = '3s' before DDL; idle transaction timeout set
ALTER TABLE … ERROR: canceling statement due to lock timeout   (retried later)
UPDATE … Execution Time: 2.9 ms

Why a queued DDL statement blocks readers #

ALTER TABLE needs an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode. While it waits for that lock, it sits in the lock queue — and PostgreSQL’s queue is ordered: a new request that conflicts with a queued request also waits, rather than jumping ahead. So a migration stuck behind a long-running report blocks every subsequent query on that table, including simple selects that would otherwise be unaffected by either the report or the migration.

That is why a five-second DDL statement can cause a five-minute outage on a busy table: the statement itself is fast, but it waited behind something slow, and everything else waited behind it. Two habits prevent it: always set lock_timeout before DDL, so the migration gives up instead of queueing, and keep long-running transactions away from tables that receive schema changes. Retrying the DDL in a loop with a short timeout is a common and effective pattern, since it only succeeds when it can take the lock immediately.

Common Pitfalls #

Tuning the victim’s query. Its plan is fine. Diagnostic signal: time without buffers. Fix: find the blocker.

Investigating after the fact. Lock waits leave no trace in the plan. Diagnostic signal: irreproducible slowness. Fix: log_lock_waits and periodic pg_stat_activity sampling.

DDL without lock_timeout. One migration blocks a whole table. Diagnostic signal: a latency spike on every query touching one table. Fix: timeouts and retries.

Forgotten transactions. An idle session in a transaction holds its locks. Diagnostic signal: state = 'idle in transaction' with an old xact_start. Fix: idle_in_transaction_session_timeout.

Frequently Asked Questions #

How can I tell whether a query is slow or waiting? #

Look for a long execution time with almost no buffers and no I/O timings — that combination means the time was spent waiting. Confirm with pg_stat_activity: wait_event_type of Lock during execution.

Why did a simple SELECT get blocked? #

Plain selects do not wait for row locks, but they do wait for ACCESS EXCLUSIVE table locks — and for anything queued behind one. A migration waiting for that lock puts every later query on the table behind it in the queue.

How do I find which session is blocking mine? #

Use pg_blocking_pids() on the waiting backend’s pid and join to pg_stat_activity to see the blocking query and how long its transaction has been open. Enabling log_lock_waits records the same information automatically.

Up: Identifying Plan Bottlenecks