Find what is blocking a query in PostgreSQL

Before you start

A query that is not slow, only stuck, is usually waiting for a lock another session holds. The query text looks fine and its plan is fine; the time goes to waiting. You need a login that can see other sessions' activity (the pg_read_all_stats role is enough). All names and values below are made up.

List who is waiting, and for whom

SELECT pid,
       application_name,
       now() - query_start       AS waiting_for,
       pg_blocking_pids(pid)     AS blocked_by,
       left(query, 60)           AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids(pid) returns the process ids of the sessions that hold, or are queued ahead for, the lock this session needs. An empty result means nothing was waiting at that moment, not that nothing ever waited: the view is a snapshot.

Look at the session they wait for

SELECT pid, application_name, state,
       now() - xact_start    AS transaction_open_for,
       now() - state_change  AS in_this_state_for,
       left(query, 60)       AS last_query
FROM pg_stat_activity
WHERE pid = 4398;  -- a pid from blocked_by

state is active

The blocker is running a statement now. Wait for it, or look at that statement as a slow query.

state is idle in transaction

The blocker opened a transaction and is doing nothing in it, while still holding its locks. last_query is only the last statement it ran, not what is holding the lock. This is the common case; see the idle-in-transaction guide.

A chain of pids

The blocker is itself waiting. Follow blocked_by from it until you reach a session that is not waiting.

Decide what to do

Waiting is often right: a short transaction will commit in a moment. If it will not, you have two tools, both of which lose work, so check what the session is for first.

-- Cancels the session's current statement; the session stays connected
SELECT pg_cancel_backend(4398);

-- Ends the session and rolls back its open transaction
SELECT pg_terminate_backend(4398);

Cancelling does nothing to a session that is idle in a transaction, because it has no statement running. Ending it rolls back whatever it wrote.

Make the next one visible and shorter

log_lock_waits = on writes a log line when a session waits longer than deadlock_timeout (1 second by default).
lock_timeout makes a statement give up after a set time instead of waiting forever. Set it per session or per transaction, not blindly for everything.
Keep transactions short, and do not call another service while one is open.

Next step

The queries above show one moment. dbtempo records lock waits (paid plan; see Pricing). Short waits can be missed. See Turn on lock and transaction recording. dbtempo only shows you the statements above; it never runs them.

Sources