Find blocking queries in PostgreSQL from your iPhone
When requests pile up behind a lock, one session sits at the root. HeapDeck draws the whole chain from pg_blocking_pids(), puts that session on top and tells you what cancelling it would release.
Updated · Vladimir Chemeris, the developer of HeapDeck
A lock pile-up, step by step
A lock pile-up starts with something ordinary. A transaction updates a row and then waits on the application, or a migration asks for ACCESS EXCLUSIVE on a busy table. Sessions that need a conflicting lock wait, and the sessions behind them wait too. To your users, the database stops answering.
pg_blocking_pids(pid) tells you who blocks whom: it returns the sessions that block a given one, including sessions ahead of it in the lock queue:
SELECT pid,
pg_blocking_pids(pid) AS blocked_by,
state,
now() - query_start AS waiting_for,
left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
The query gives you the edges. On a phone you don’t want to turn them into a tree, find the root and pick what to stop by hand.
The Locks screen
Locks builds the tree in one query over pg_locks, pg_blocking_pids() and pg_stat_activity, and reads it again every 5 seconds:
- a summary like
4 sessions waiting · 1 chain · longest wait 04:12; - the root blocker card: pid,
user@app, state,holding for 06:48and the statement it is running; - each waiting session under it, with how long it has waited and what for, such as
RowExclusiveLock on public.orders.
The Blocked tile on Health reads the same tree, so a red tile and the Locks screen show the same chain.
Stopping the root, with a preview
The button on the root card follows what the root is doing:
- the root runs a statement: Cancel root (unblocks N), with Terminate root in the
…menu; - the root sits
idleoridle in transaction: only Terminate root (unblocks N), with a line under it on why a cancel would be ignored; - your role can’t see the root’s state: Terminate root, with a cancel in the menu that promises no count.
unblocks N counts the sessions waiting on the root itself. In the demo, four sessions wait and the button reads unblocks 2: the other two wait behind one of those two, and the line under the button names them. The confirmation lists the pids it counts.
- Cancel calls
pg_cancel_backend(): the running statement stops, the session and its transaction stay open. - Terminate calls
pg_terminate_backend(): the session ends and its transaction rolls back, which releases the locks of anidle in transactionroot. Terminating a root asks you to type its pid first.
HeapDeck reads the chain again before it sends anything. If the root is gone, HeapDeck tells you the chain cleared on its own and sends nothing. If the count changed, the sheet stays open with The chain changed while you were deciding and the new numbers. After the signal, HeapDeck reads the tree once more and reports what it measured, such as Terminated · 2 sessions unblocked · 2 still waiting.
Both actions are part of HeapDeck Pro. On Free the button shows its count with a PRO badge, and in the built-in demo you can run the whole flow without buying.
Deadlocks
If sessions wait on each other in a circle, Locks shows These sessions are waiting on each other in place of a tree. That is a deadlock: PostgreSQL resolves it by cancelling one of the sessions once deadlock_timeout passes, one second by default.
Without superuser
HeapDeck builds the tree for any role. Without pg_monitor, PostgreSQL hides the SQL of other users’ sessions; HeapDeck shows the tree anyway, labels the hidden statements and offers the GRANT pg_monitor that fixes it. To cancel sessions of other roles you need the usual rights, such as membership in pg_signal_backend. If your role can’t signal the root, the card reads Your role can't stop this session and gives you the command to hand to a DBA, plus the GRANT pg_signal_backend for an ordinary role.
A single ALTER TABLE can stop the queries on a whole table: the lock queue, step by step.
Questions
How do I find which query holds a lock in Postgres?
Select from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0. The blocked_by pids point to the sessions in front. The one nobody blocks is the root.
Should I cancel or terminate the blocking session?
Cancel if the root is running a statement. Terminate if it sits idle or idle in transaction, since a cancel has nothing to stop there. Terminate rolls back its transaction, and the application sees a dropped connection.