How to approach…
Start the work without skipping the evidence. Use these task maps before a change or during an investigation. Each keeps the same operating loop: observe, interpret, act safely, and escalate with a clear threshold.
Investigate slow queries Diagnose lock contention Plan index changes Respond to connection saturation Manage vacuum and autovacuum Investigate disk growth Plan backup and restore tests Investigate replication lag Prepare safe production changes Observe first Define the affected endpoints, query fingerprints, databases, and time window. Capture current waits, workload contribution, plan shape, and host I/O at the same timestamp. Interpret the evidence A long-running query can be a victim of a lock or I/O queue rather than the cause. Compare calls, total time, mean time, rows, and plan estimates instead of ranking by one number. Choose a safe action Cancel clearly runaway work before terminating a session when cancellation is sufficient. Test plan or index changes with representative parameters and a rollback path. Escalate when Latency and timeout queues continue to grow after demand is reduced. The change requires production DDL, memory changes, or workload ownership decisions. Open the slow-query runbook → Observe first Map waiting PIDs to blocking PIDs with transaction age, application, user, and query context. Confirm user impact and whether DDL, an idle transaction, or normal write contention is at the front of the queue. Interpret the evidence Terminating waiters does not remove the lock request that caused the queue. An old root transaction may also retain row versions and amplify vacuum debt. Choose a safe action Contact the owning team and prefer cancellation or rollback of the root operation. Pause conflicting deploy work before it repeatedly rebuilds the queue. Escalate when The blocker owns a high-value or ambiguous transaction. The queue threatens availability and the application owner cannot be reached. Open the blocked-writes runbook → Observe first Collect representative predicates, ordering, row counts, plans, and buffer evidence. Check for equivalent or overlapping indexes and estimate table change rate. Interpret the evidence An unused index snapshot does not prove the index is unnecessary across the workload cycle. A selective predicate can benefit while low-cardinality or write-heavy paths may not. Choose a safe action Test with realistic data and use a production-safe build method where locking matters. Define success, cancellation, rollback, disk, and WAL thresholds before the build. Escalate when The build could exceed disk, WAL, replica-lag, or maintenance windows. Dropping an index has uncertain constraint or rare-workload dependencies. Study workload-led indexing → Observe first Preserve administrative access and group sessions by database, role, application, client, and state. Compare demand with pool limits, transaction age, memory pressure, and request concurrency. Interpret the evidence Idle sessions may be normal pool inventory but still consume backend capacity. Raising max_connections can move failure from admission to memory, CPU, or I/O queues. Choose a safe action Reduce admission or concurrency at the application boundary. End only confirmed abandoned sessions and repair connection lifecycle or pool limits. Escalate when Reserved administration access is lost or memory pressure is rising. Idle-in-transaction sessions own locks or old snapshots. Open the connection runbook → 05 Manage vacuum and autovacuum Distinguish scheduling, worker capacity, table churn, and pinned cleanup horizons.
Observe first Rank tables by transaction ID age, dead rows, last vacuum, and current progress. Inspect old transactions, prepared transactions, and slots that may pin the cleanup horizon. Interpret the evidence A running vacuum can still be unable to remove rows needed by an old snapshot. Dead-row estimates and bloat are related signals, not interchangeable measurements. Choose a safe action Resolve the oldest safe-to-end horizon owner before increasing worker capacity blindly. Use per-table settings for persistently high-churn relations and verify later cycles. Escalate when Wraparound warnings appear or transaction ID age approaches refusal thresholds. The response requires VACUUM FULL or another locking rewrite. Open the autovacuum runbook → 06 Investigate disk growth Attribute growth to relations, indexes, WAL, temporary work, logs, or backup staging first.
Observe first Record free bytes, growth rate, and the response window at the current rate. Separate data, WAL, temp, logs, archive, and backup destinations; compare relation snapshots. Interpret the evidence Vacuumed relation space can be reusable without shrinking the operating-system file. Retained WAL often points to slots, archiving, replicas, or backup behaviour rather than table growth. Choose a safe action Add headroom when the exhaustion window is shorter than safe diagnosis or reclamation. Stop the specific growth source before scheduling any rewrite or deletion. Escalate when Available space is below the worst-case response window. pg_wal growth is uncontrolled or a rewrite lacks enough temporary headroom. Open the disk-growth runbook → 07 Plan backup and restore tests Test the recovery path against explicit data, time, dependency, and validation objectives.
Observe first State recovery point and recovery time objectives, restore target, data size, and dependencies. Inventory base backups, WAL/archive continuity, encryption keys, extensions, roles, and external secrets. Interpret the evidence A successful backup command proves creation, not restorability or objective compliance. A restore that starts is incomplete until applications and data invariants are validated. Choose a safe action Restore into an isolated environment, time each stage, and record objective evidence. Validate row counts, checksums or invariants, roles, extensions, and application smoke tests. Escalate when Required WAL, keys, extensions, or role definitions are missing. The measured restore time or recovery point misses the agreed objective. Open the backup and restore runbook → 08 Investigate replication lag Locate accumulated WAL across send, receive, write, flush, and replay boundaries.
Observe first Define freshness and failover impact, then compare WAL positions twice over a known interval. Separate primary generation, network transport, standby I/O, replay conflicts, and intentional delay. Interpret the evidence A lag interval alone does not locate the constrained stage. A shrinking byte gap can coexist with a large time lag while the standby catches up. Choose a safe action Reduce the proven competing workload or resolve the specific replay conflict reversibly. Keep measuring until the gap converges and objectives are restored. Escalate when Retained WAL threatens primary disk or failover objectives are lost. The standby is diverged, repeatedly disconnecting, or unable to replay. Open the replication-lag runbook → 09 Prepare safe production changes Make scope, locking, resource cost, verification, cancellation, and rollback explicit.
Observe first Identify affected objects, workload owners, dependencies, table size, change rate, and peak windows. Test lock acquisition, runtime, WAL, disk, replica, and application behaviour with representative data. Interpret the evidence A fast staging migration may still queue behind production transactions or generate disproportionate WAL. Rollback feasibility depends on data and application compatibility, not only a reverse SQL file. Choose a safe action Set statement and lock timeouts, stage compatible application changes, and define abort thresholds. Observe the same success and safety signals before, during, and after the change. Escalate when The change is irreversible, lacks a compatibility window, or cannot be rehearsed. Lock, disk, WAL, replica, or recovery limits cannot be bounded. Open the deployment-change runbook →