PostgreSQL in Practice

PostgreSQL Locks, Monitoring, and Production Diagnostics

PostgreSQL incident diagnosis correlates sessions, waits, locks, query fingerprints, WAL/replication, checkpoint and vacuum signals with configuration provenance. The runbook preserves evidence, contains harm with the least destructive action, and verifies recovery rather than stopping at symptom disappearance.

A wait event names the current wait, not the root cause.
pg_locks shows held and awaited lockable objects.
Statistics views are cumulative and version-sensitive.
pg_stat_statements is an optional, normalized workload view.
Settings need provenance.
Escalation should preserve correctness and evidence.

Snapshot activity and blocking edges

SELECT clock_timestamp() AS captured_at, pid, usename, application_name,
       state, wait_event_type, wait_event, xact_start, query_start,
       backend_xmin, pg_blocking_pids(pid) AS blockers, query_id, left(query, 500) AS query
FROM pg_stat_activity WHERE pid <> pg_backend_pid();

SELECT pid, locktype, relation::regclass, transactionid, mode, granted, waitstart
FROM pg_locks WHERE NOT granted ORDER BY waitstart;

SELECT name, setting, unit, source, sourcefile, pending_restart
FROM pg_settings WHERE name IN ('max_connections','shared_buffers','work_mem','synchronous_commit');

Evidence-preserving production runbook

Signal bundle
Question	PostgreSQL evidence	Interpret with
Who is blocked?	pg_stat_activity, pg_locks, pg_blocking_pids	transaction age, application owner, DDL/job timeline
Which workload changed?	pg_stat_statements if installed; logs/query_id	stats reset, deploy, binds unavailable
Is storage/checkpoint saturated?	pg_stat_io, pg_stat_bgwriter, pg_stat_checkpointer, pg_stat_wal	OS/device latency and sample deltas
Is maintenance pinned?	backend_xmin, progress views, pg_stat_user_tables	slots, prepared xacts, standby feedback
Is recovery path healthy?	pg_stat_replication, pg_replication_slots, pg_stat_archiver	archive continuity and business freshness