pgdba.dev

Before you begin

Prerequisites

Confirm the target cluster, database, PostgreSQL major version, and your read-only access to the required statistics views.
Record the current user impact, incident owner, UTC timestamp, and a baseline before running diagnostics.

Control the blast radius

Safety boundary

Start with read-only observations. Do not restart, terminate, delete, fail over, or change configuration until ownership, blast radius, and an approved recovery path are explicit.

Triage

Establish impact

Identify whether the blocked work is user traffic, maintenance, deployment DDL, or a background job.
Measure the number and age of waiters and whether the queue is still growing.
Locate the root blocker before cancelling any waiter.

Observe

Gather evidence

Show each blocked backend, its blockers, and the ages needed to establish ownership and impact.

SELECT
  waiting.pid AS waiting_pid,
  pg_blocking_pids(waiting.pid) AS blocking_pids,
  waiting.application_name,
  now() - waiting.query_start AS waiting_for,
  now() - waiting.xact_start AS transaction_age,
  left(waiting.query, 160) AS waiting_query
FROM pg_stat_activity AS waiting
WHERE cardinality(pg_blocking_pids(waiting.pid)) > 0
ORDER BY waiting_for DESC;

low observation cost

Interpret

Interpret the evidence

A blocker doing useful work may only need time; an abandoned idle transaction needs application ownership and a controlled rollback decision.
DDL near the front of a lock queue can make later compatible traffic wait behind it.

Act

Take the safest useful action

Contact the owner of the root transaction and determine rollback consequences.
Use `pg_cancel_backend` when cancelling the current statement is sufficient; reserve termination for sessions whose transaction must be rolled back.
Remove or postpone lock-heavy deployment work and add explicit lock timeouts before retrying.

Prove the outcome

Verification

Repeat the baseline observation over a known interval and confirm that the measured queue or risk is moving in the intended direction.
Verify the user-facing objective and every affected replica or dependency before closing or handing over the incident.

Keep recovery close

Rollback

Record the original state and rollback owner before acting. If verification worsens or the objective is missed, reverse only the bounded change, confirm the baseline is restored, and escalate with the before-and-after evidence.

Escalate

Escalate when

Escalate before terminating a transaction that owns business-critical writes or when the blocking chain involves prepared transactions.

Prevent recurrence

Study the underlying system

Certification competency

Refresh the assessed operating model

Reference assurance

Version and review

Compatible versions: PostgreSQL 16–18
Content version: 2026.08
Reviewed: 2026-08-04

Catalog columns, wait events, and operational controls can vary by PostgreSQL major version, extensions, and orchestration layer. Verify commands against the deployed version.

Verify independently

Sources

PostgreSQL 18: Explicit Locking

Writes are blocked