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

Download on the App Store
Scan to open it on your iPhone

Free · Pro $19.99 one-time · PostgreSQL 13–19 · iOS 17+ · iPhone and iPad

Locks screen: a root blocker idle in transaction for 06:48 above four waiting sessions, with the Terminate root (unblocks 2) button and why a cancel would be ignored

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:48 and 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 idle or idle 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 an idle in transaction root. 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.