Find Blocking Sessions in PostgreSQL

A query that appears stuck may be waiting for a lock held by another transaction. Finding the blocker is safer than cancelling sessions at random, because the blocking transaction may be doing important work or holding uncommitted changes.

Identify waiting and blocking PIDs

On supported PostgreSQL versions, pg_blocking_pids() can show which backend processes block a waiting session. Join that information to pg_stat_activity to inspect state, wait event, transaction age and application name. Restrict output to the relevant database and keep query text in an access-controlled diagnostic session.

PostgreSQL documents these views in its monitoring statistics reference. Required visibility depends on the role and privileges. Do not expose query text publicly; statements can contain sensitive values.

Understand the transaction behind the wait

Look for an old transaction that is idle while still holding locks, a migration that is changing a busy table or a batch job that touches rows in an unexpected order. Confirm the application owner and recent deployment before intervening. Check whether the blocked statement is waiting on a row, relation or another resource.

Choose cancellation with care

Canceling a statement is different from terminating a backend and rolling back its transaction. Either action can create application errors and a large rollback workload. Coordinate with the service owner and prefer a targeted cancellation when approved.

After resolving the wait, verify that the application request completes and review transaction duration or missing indexes that contributed. Prevent recurring lock incidents by keeping transactions short and using a consistent update order where possible.