PostgreSQL Blocking: Trace the Session Holding the Work

Context

A blocked PostgreSQL query is waiting for another transaction or lock relationship to permit progress. The waiting query may be the visible symptom while the root blocker is an apparently idle connection. Trace ownership before intervening: canceling a random waiting request may reduce the queue without releasing the lock that caused it.

Prerequisites

Obtain authorized visibility into session activity, know the PostgreSQL major version, and identify application owners. Collect the incident time, affected database, and maintenance schedule. Query text can contain sensitive values, so limit what is retained in tickets. The read-only investigation below is separate from permission to cancel statements or terminate backends, which can roll back work and affect users.

Follow the blocking chain

1. Inspect pg_stat_activity for waiting sessions, their wait types, transaction start times, application names, and client addresses. A read-only starting query can select pid, application_name, state, xact_start, wait_event_type, wait_event, pg_blocking_pids(pid) for the affected database. Include timestamps and interpret the snapshot as a moment in a changing system.
2. Follow each reported blocking PID until you find the earliest relevant owner. A queue can contain sessions that are themselves waiting, so stopping at the first relationship may miss the root. Use pg_locks to understand the object and mode when needed. PostgreSQL does not expose every row lock as a simple row in this view, so avoid assuming that an absent tuple entry proves no row-level conflict.
3. Correlate the root session with application logs and transaction boundaries. Determine whether it is performing legitimate work, waiting on an external service, or idle inside an unfinished transaction. Ask the owning service to commit or roll back through its normal workflow where feasible. A completed statement does not necessarily mean its surrounding transaction has ended.
4. If intervention is necessary, choose it deliberately with the incident owner. Canceling a statement and terminating a session have different consequences. For an idle transaction, there may be no active statement whose cancellation releases its locks. Review rollback impact and application retry behavior before using the approved administrative function. Do not paste a bulk termination query into production.

Diagnostic table

Illustrative scenario

A support tool updates a customer record and leaves its transaction open while an operator reviews another screen. Checkout requests needing the same record queue behind it. The team identifies the support session, asks its owner to roll back, and verifies the queue clears. It then changes the tool's interaction pattern so a human review does not hold a database transaction open.

Verify and correct

After resolution, confirm that the original root transaction ended and that affected business requests complete. Check for retry bursts and duplicate side effects in the application workflow. If blocking returns, preserve the transaction timeline and fix the owning code path or migration design rather than repeatedly terminating symptoms. Review whether cancellation left a connection in a failed transaction before it returned to its pool.

Operating procedure

Monitor lock-wait duration, root-blocker transaction age, and idle-in-transaction sessions with application identity attached. Define who may intervene and how the business consequence is recorded. Rehearse a bounded blocking example in staging. Keep the resulting chain, owner, correction, and acceptance evidence in the runbook so future incidents start with the holding transaction.

Explore Clouvenix database administration for implementation and operational handover.

Technical reference

• www.postgresql.org documentation
• www.postgresql.org documentation