How do you find which query is blocking another in PostgreSQL?
Look at pg_stat_activity for sessions with wait_event_type Lock, and use pg_blocking_pids(pid) to get the PIDs holding the lock. Then inspect those sessions and, if needed, cancel or terminate the blocker.
Explain it simply
When an update hangs, someone else is holding the thing you need. PostgreSQL records who is waiting and who they are waiting for, so finding the blocker is a lookup, not guesswork.
The usual culprit is a session that opened a transaction, touched the row, and never committed.
Example
SELECT w.pid AS waiting_pid,
w.query AS waiting_query,
b.pid AS blocking_pid,
b.state AS blocker_state,
b.query AS blocker_last_query,
now() - b.xact_start AS blocker_open_for
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock';
-- polite: cancel the running query, keep the connection
SELECT pg_cancel_backend(4321);
-- forceful: close the whole connection and roll back its work
SELECT pg_terminate_backend(4321);The blocker's state is often idle in transaction, which means its last query already finished, but the transaction still holds locks.
What to say in the interview
"I join pg_stat_activity to itself through pg_blocking_pids to see waiter and blocker side by side. If the blocker is idle in transaction I cancel or terminate it, then fix the application so transactions end promptly."
Common mistakes
- Terminating the wrong PID without checking what it was doing.