PostgreSQL Locks: Find and Handle Blocking Queries

Show blocked sessions and their direct blockers

select
  waiting.pid as waiting_pid,
  waiting.usename as waiting_user,
  waiting.query as waiting_query,
  waiting.wait_event_type,
  waiting.wait_event,
  blocker.pid as blocker_pid,
  blocker.usename as blocker_user,
  blocker.state as blocker_state,
  blocker.xact_start as blocker_xact_start,
  blocker.query as blocker_query
from pg_stat_activity waiting
cross join lateral unnest(pg_blocking_pids(waiting.pid)) as b(pid)
join pg_stat_activity blocker on blocker.pid = b.pid
order by waiting.query_start;

Run this with a role allowed to view the relevant activity. Query text for other users can be restricted.

Confirm that the wait is a lock

pg_stat_activity shows one row per backend. A lock wait normally reports wait_event_type as Lock. Other wait types, such as Client, IO, or IPC, need a different investigation.

pg_locks exposes outstanding lock records, but joining it correctly for every lock type is easy to get wrong. pg_blocking_pids is the direct starting point for finding the sessions that block a specific backend.

Inspect the blocker before touching it

Record the blocker PID, user, application name, client address, state, transaction start, query start, and query text. An idle in transaction session is especially important: its last statement ended, but its open transaction can still hold locks.

Check whether the blocker belongs to a migration, maintenance task, checkout flow, background worker, or another administrator. Killing a legitimate migration midway can leave an operational mess even though PostgreSQL rolls back its open transaction.

Inspect one blocker in context

select
  pid, usename, application_name, client_addr,
  state, xact_start, query_start, state_change,
  wait_event_type, wait_event, query
from pg_stat_activity
where pid = 12345;

Replace 12345 with the blocker PID returned by the first query.

Prefer a clean transaction end

The best resolution is for the owning application to commit or roll back. That releases locks through the normal transaction path and lets the application report its own outcome.

If you control the waiting operation, canceling that operation may be safer than disrupting the blocker. The right choice depends on which transaction is valuable and whether either client can retry safely.

Cancel before terminating

pg_cancel_backend asks PostgreSQL to cancel the current query while preserving the session. pg_terminate_backend ends the session. Termination rolls back an open transaction, which can itself take time before every lock is released.

These signaling functions are permission-controlled. Only authorized operators should use them, and a normal role cannot terminate a superuser backend.

Escalate deliberately

-- First, ask the current statement to stop
select pg_cancel_backend(12345);

-- If the session itself must end
select pg_terminate_backend(12345, 5000);

Replace 12345 only after verifying the session. The optional timeout waits for termination confirmation on supported PostgreSQL versions.

Prevent the next lock pileup

Set application_name so sessions can be traced to a service. Keep transactions short, avoid user interaction inside an open transaction, and set idle_in_transaction_session_timeout where an abandoned transaction is worse than a disconnected client.

Slow statements can hold locks longer even when the lock choice is correct. Pair this workflow with pg_stat_statements and the EXPLAIN ANALYZE guide after the immediate incident is stable.