PostgreSQL - DBA Toolkit - Blocking and Lock Waits

Someone Is Waiting. You Need to Know Who Started It.

Lock waits in PostgreSQL do not make noise.

An application hangs. Queries pile up behind it. Users report timeouts. And somewhere in the middle of it all, one session is sitting on a lock that nobody released.

This script joins pg_stat_activity with pg_locks to build the full blocking chain — the session doing the blocking, the session waiting, how long it has been waiting, and what each one is actually trying to do.

    Loading…

Reading the Results

Column	What It Tells You
`blocking_pid`	The process ID of the session holding the lock
`blocking_user`	The database user running the blocking session
`blocking_query`	The SQL the blocking session is executing (or last executed)
`blocked_pid`	The process ID of the session waiting for the lock
`blocked_user`	The database user stuck waiting
`blocked_query`	The SQL that cannot proceed
`wait_duration`	How long the blocked session has been waiting
`lock_type`	The type of lock being contested — e.g. `relation`, `tuple`, `transactionid`

What to Watch For

Signal	What It Means
`wait_duration` growing over minutes	A transaction is open and idle — likely a forgotten `BEGIN` with no `COMMIT`
Many blocked sessions, one blocking PID	One query or transaction is the root cause — kill or investigate that one
`blocking_query` shows a `SELECT`	Row-level `FOR UPDATE` or `FOR SHARE` locking — not always obvious from the query text
`lock_type = relation`	A table-level lock — DDL, `TRUNCATE`, or `LOCK TABLE` is involved
Same blocking PID across multiple restarts	A long-running transaction that survived a retry — check client-side retry logic
`blocking_query` is `idle in transaction`	The session committed or rolled back but held locks — connection pool issue

Killing the Blocker

Once you identify the blocking PID, you have two options:

    Loading…

pg_cancel_backend cancels only the current query. pg_terminate_backend ends the entire connection. Use terminate when the session is idle in transaction and will not respond to cancel.

Gareth Winterman