Database queries suddenly queue: locate the shared bottleneck

The symptoms

• •Application latency rises sharply while CPU on the database host appears unchanged or only modestly elevated.
• •Connection pools (PgBouncer, application pools) report waiters or growing checkout times.
• •Multiple distinct statements are slow at once, indicating the queue, not a single bad query.
• •pg_stat_activity shows many backends in state 'active' with non-null wait_event_type values rather than running on CPU.
• •Logs show 'lock waits', 'canceling statement due to lock timeout', or checkpoint warnings appearing together.

Likely causes

• •Lock contention: row, relation, or transaction-id locks held by a long-running or idle-in-transaction session.
• •Connection pool saturation: total backend count near max_connections with many sessions waiting in pool queues rather than running queries.
• •I/O backpressure: storage layer cannot keep up, shown by waits on WaitIO/IO/DataFileRead, sustained bufmgr backends, or extended checkpoint stalls.
• •Plan regression: statistics drift, schema change, or parameter change (e.g., work_mem, jit) flips an index scan into a sequential scan or hash join that spills to disk.
• •Background-process interference: autovacuum, replication catch-up, or index creation competing with foreground traffic for I/O or lock space.

First ten minutes

1. 01Capture a snapshot of pg_stat_activity filtered to non-idle states and note the dominant wait_event_type.
2. 02Check whether total active backends approach max_connections and whether connections are queued outside PostgreSQL.
3. 03List ungranted locks and identify the blocker PIDs and the statements they are running.
4. 04Sample pg_stat_statements to find which normalized statements have just begun contributing disproportionately to total execution time.
5. 05Confirm there is no in-progress maintenance (VACUUM, REINDEX, ALTER TABLE) by checking pg_stat_progress_* views.

Evidence to collect

• •Per-backend wait_event_type and wait_event counts from pg_stat_activity, grouped by class (Lock, IO, LWLock, Activity, Client, Extension).
• •Ungranted lock rows from pg_locks joined to pg_stat_activity to map waiters to blockers and to the exact statement text.
• •Database-wide counters from pg_stat_database: tup_returned, tup_fetched, blks_read, blks_hit, xact_commit, xact_rollback, conflicts.
• •Top statements from pg_stat_statements by total_exec_time and mean_exec_time, with calls and rows columns to detect regressions.
• •Background writer and checkpoint behavior: pg_stat_bgwriter buffers_written and recent checkpoint_warning log entries.

Where to look

• •pg_stat_activity for live session states, wait events, query_start, xact_start, and application_name.
• •pg_locks joined to pg_stat_activity for granted versus not granted lock rows and lock type breakdown.
• •pg_stat_statements (if enabled) for normalized statement totals and recent drift.
• •pg_stat_database and pg_stat_bgwriter for cluster-level throughput and write pressure.
• •PostgreSQL log (log_lock_waits, log_checkpoints, log_temp_files, auto_explain) for corroborating timeline events.

Diagnostic steps

1. 01Group pg_stat_activity by wait_event_type; the dominant class names the bottleneck category (Lock, IO, LWLock, Activity).
2. 02If 'Lock' dominates, run the pg_locks join to find blockers. Distinguish row/relation locks from transaction-id locks; idle-in-transaction backends typically cause the latter.
3. 03If 'IO' or 'LWLock' dominates, check pg_stat_database blks_read growth, recent log_temp_files entries, and pg_stat_bgwriter buffers_written to separate user reads from background flush pressure.
4. 04If no wait event dominates and many sessions are simply 'active', check total connections versus max_connections and the pool layer upstream for queueing.
5. 05For a suspect statement, retrieve its plan from pg_stat_statements or recent auto_explain output and compare to the prior plan; note plan_node_id, total_cost, and any disk-spill indicators.
6. 06Re-run EXPLAIN (without ANALYZE) on a copy or against a read replica to inspect the new plan without further stressing the primary.

Common mistakes

• •Assuming CPU is the cause because the database host looks busy; many queued sessions appear as load while actually waiting.
• •Killing long-running backends without first identifying what they hold; cancelling a blocker can cascade failures into dependent sessions.
• •Raising max_connections when the real ceiling is application-side pooling or I/O throughput, which deepens the queue.
• •Running EXPLAIN (ANALYZE) on a suspect query directly on the saturated primary, adding more load while diagnosing.
• •Trusting a single snapshot; wait events shift quickly during incident triage, so capture multiple samples before concluding.

Safe fixes

• •Notify the application owner of the suspect blocker PID and statement; require explicit approval before terminating a session.
• •Tighten the application transaction boundary by routing only the offending feature behind a feature flag, reducing new waiters.
• •Enable log_lock_waits = on and auto_explain (with log_min_duration set just above the new p95) to capture the regressed plans during the incident window.
• •Increase statement_timeout or lock_timeout selectively on the problem workload via SET LOCAL inside a scoped session, never cluster-wide without review.
• •Schedule any required maintenance (VACUUM, ANALYZE, REINDEX) outside peak windows and verify impact with pg_stat_progress_* views before letting it run to completion.

Prove the fix

1. 01Median and p95 latency of the previously slow normalized statement returns to within the documented pre-incident baseline for at least two consecutive sampling windows.
2. 02wait_event_type counts in pg_stat_activity for the dominant class drop to background noise levels for the same observation window.
3. 03Connection pool checkout time and queue depth return to pre-incident values, with no new timeouts.
4. 04Lock wait log entries cease appearing at the prior rate, and pg_locks shows no sustained not-granted rows tied to the original blocker statement.
5. 05No new plan regressions appear in pg_stat_statements or auto_explain for the affected workload after the change.

Prevention and next steps

• •Keep pg_stat_statements enabled with a sufficient pg_stat_statements.max to detect drift in mean_exec_time and calls per normalized query.
• •Set application-side transaction timeouts and pool sizing relative to max_connections and observed concurrency, not headroom alone.
• •Alert on idle-in-transaction age, lock_wait rate, and blks_read growth so a regression is visible before latency breaches SLO.
• •Review plans for hot statements after schema, statistics, or parameter changes; capture a baseline plan set per release.
• •Run heavy maintenance (autovacuum tuning, REINDEX, statistics refresh) on a schedule validated against pg_stat_progress_* impact, not ad hoc.

Safe commands and checks

SELECT pid, state, wait_event_type, wait_event, xact_start, query_start, LEFT(query, 200) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start NULLS LAST;
SELECT wait_event_type, wait_event, COUNT(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY 1,2 ORDER BY 3 DESC;
SELECT blocked.pid AS blocked_pid, blocked.wait_event AS blocked_wait, blocking.pid AS blocking_pid, blocking.state AS blocking_state, LEFT(blocking.query, 200) AS blocking_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid AND kl.pid != bl.pid AND kl.granted JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;
SELECT pid, state, xact_start, NOW() - xact_start AS idle_txn_age, LEFT(query, 200) FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)','disabled') ORDER BY xact_start;
SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts FROM pg_stat_database WHERE datname = current_database();
SELECT buffers_clean, buffers_backend, buffers_alloc, buffers_checkpoint, buffers_backend_fsync FROM pg_stat_bgwriter;
SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
SHOW max_connections; SELECT count(*) AS active_backends FROM pg_stat_activity;