Quick answer
Troubleshoot a production slow query as an end-to-end incident, not an isolated SQL puzzle. First declare the user journey, SLI, start time, affected deployment, route, operation, and database role. Split elapsed time into application queue, connection-pool acquisition, database execution, lock wait, rollback, retry, replica delay, and response work. Identify normalized query families without exporting sensitive values, then connect active waits and cumulative workload evidence to a safely captured plan.
Inside the database, compare estimated rows, actual rows, loops, scan and join work, buffers, temporary I/O, WAL, locks, transaction age, dead tuples, vacuum progress, partition pruning, and index state. Every source has a boundary: plans describe one statement under specific data and settings; sampled traces omit traffic; cumulative statistics can reset and lag; active views are a momentary snapshot.
Mitigate the proven constraint with the least risky reversible control. Pause optional reports/backfills/DDL, bound admission and retries, cancel only reviewed work, restore transaction hygiene, or route an explicitly stale-safe read. Stage statistics, query, index, partition, or schema repairs through separate validated changes. Close only after user SLI, correctness, pool and lock waits, retries, database resources, maintenance, and replicas remain healthy.
Shared order-history incident stage
The incident opens when the order-history API’s p99 and timeout rate rise after a release. Checkout writes are also affected because database connections remain occupied. Traffic volume increased, but the admitted work grew faster due to retries. The affected query filters tenant, status, and time, orders newest first, limits results, and joins order items.
A query plan from a representative safe environment shows a cardinality estimate error for the dominant tenant/status combination. Actual rows and loops exceed the planned work; heap visits and temporary I/O rise. Separate single-column indexes do not match the combined filter and ordering. A transformed partition-key predicate fails to prune expected partitions.
At the same time, a reporting process is idle in a long transaction, delaying MVCC cleanup. Updates accumulate obsolete versions, visibility coverage falls, and index-only execution performs heap fetches. Some order updates wait on locks; pool acquisition rises. An attempted index/schema repair competes for locks and I/O. No single graph explains all of this.
Responders pause the report, backfill, and migration, reduce optional admission, and stop unsafe retries. They construct the blocker/waiter graph and cancel only the owned report after side-effect review. Permanent repairs update the statistical model, introduce a workload-shaped index through a concurrent procedure, restore a direct half-open partition predicate, and move the schema through expand-contract. Recovery is verified across every clock and business invariant.
Core mechanism and evidence boundary
Use a hypothesis table with four columns: claim, required evidence, disconfirming evidence, and safe next action. “The database is slow” is not a hypothesis. “Pool wait rose after deployment because normalized order-history statements occupy sessions while waiting on locks and processing underestimated rows” is testable across application, activity, lock, statement, and plan evidence.
Start with bounded low-cardinality dimensions: service.name, deployment.environment.name, normalized route, named operation, and finite database_role. Keep trace_id, span_id, and request_id in restricted correlation stores. Group SQL by an approved normalized fingerprint or PostgreSQL queryid only in restricted diagnostics; identifiers can change with configuration or version and must not become metric labels.
The shared clocks are pool_wait_seconds, query_duration_seconds, lock_wait_seconds, transaction_age_seconds, and replication_lag_seconds. The plan fields are plan_rows, actual_rows, plan_loops, shared_blks_hit, shared_blks_read, and temp_blks_written. Maintenance adds dead_tuple_ratio, vacuum activity, relation/index bytes, and freeze age. User evidence includes successes, errors, timeouts, rejections, retries, latency, and correctness.
Align collection windows and denominators. pg_stat_statements totals since reset do not directly match five-minute application traffic. Active sessions are not completed requests. A sampled trace population is not complete capacity. Actual plan observations may use sanitized staging data. Record time, reset, version, role, deployment, data class, settings, cache, and concurrency.
Order the investigation to avoid self-inflicted load. First confirm users and freeze unrelated change. Second inspect existing application and database telemetry. Third identify waits and normalized workload families. Fourth capture non-executing plans and metadata. Fifth reproduce execution on safe representative data. This sequence delays expensive new queries until the team knows which evidence is missing.
Separate mitigation from diagnosis. A bounded reduction in optional report concurrency can protect users even before the exact estimate error is understood, provided the control has an owner and correctness contract. Conversely, a permanent index proposal should wait for query, data, and write evidence. Record each action with time, hypothesis, expected signal, observed result, and reversal condition so simultaneous changes do not destroy causal learning.
Recovery has three levels. Service recovery means the user journey is within its objective and overload is no longer compounding. Resource recovery means queues, locks, pools, CPU, I/O, WAL, replicas, and maintenance are stable. Correctness recovery means no missing, duplicate, stale-beyond-contract, unauthorized, or partially migrated order state remains. All three are required before closure.
PostgreSQL 18 EXPLAIN, pg_stat_activity, pg_locks, pg_blocking_pids(), pg_stat_statements, table statistics, and progress views are PostgreSQL-specific, not a SQL standard or universal database interface. The teaching fields above are not a stable OpenTelemetry semantic convention. Query text and parameters can contain PII and must remain out of dashboards and metric labels.
Minimal reproducible PostgreSQL 18 example
This runbook reuses the synthetic order-history schema from Database Query Performance for Backend Systems, so its SQL can be rehearsed without production identifiers. Begin with application evidence and a restricted activity snapshot. Avoid selecting raw query text into a shared incident channel:
SELECT pid,
application_name,
state,
now() - xact_start AS transaction_age,
wait_event_type,
wait_event,
pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE datname = current_database()
AND (state <> 'idle' OR xact_start IS NOT NULL)
ORDER BY xact_start NULLS LAST;Use pg_stat_statements through a privileged, controlled diagnostic process and aggregate without literals:
SELECT queryid, calls, total_exec_time, mean_exec_time,
rows, shared_blks_hit, shared_blks_read,
temp_blks_read, temp_blks_written, wal_bytes
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 20;Do not turn queryid into a Prometheus label or public incident identifier. Record statistics reset context. Confirm the application’s query shape from source/deployment evidence, then capture a non-executing plan. Run execution analysis only on a known-safe read in an approved environment:
BEGIN READ ONLY;
SET LOCAL lock_timeout = '250ms';
SET LOCAL statement_timeout = '3s';
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_bucket = 7
AND status = 'PAID'
AND created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY created_at DESC, id DESC
LIMIT 100;
ROLLBACK;Compare dominant, typical, rare, and broad classes using synthetic or sanitized fixtures. Preserve estimated rows, actual rows, loops, buffers, spill, returned rows, and correctness. The read-only transaction still consumes resources; never point an unknown heavy test at production merely because it cannot write.
Failure modes and dangerous misconceptions
Starting with an index. Without user, workload, plan, write, and maintenance evidence, the index may treat a symptom or add write saturation.
Sorting by mean duration only. A frequent moderate query can consume more capacity; a rare tail can dominate user harm. Compare total time, calls, tails, and journey importance.
Treating wait events as root causes. A lock wait identifies current waiting, not why the blocker holds work or whether user impact comes from the same population.
Running production EXPLAIN ANALYZE on unknown SQL. It executes the statement and can consume or mutate resources. Start non-executing and reproduce safely.
Increasing pool, memory, or timeouts. These can admit more work and deepen queues. Change them only from a capacity model with concurrency tests.
Killing sessions by age. Build ownership and blocker evidence, then assess rollback, committed effects, retries, and business recovery.
Declaring recovery when the plan changes. A plan is one database artifact. Verify users, pools, locks, writes, maintenance, replicas, and correctness.
Publishing SQL evidence. Literals, predicates, object names, and statistics can disclose sensitive workload structure.
Changing multiple planner settings at once. A session-level experiment can isolate a hypothesis, but global toggles affect unrelated queries. Avoid permanent configuration changes until workload-wide tests show why the default cost model is wrong.
Failing over to a replica without a read contract. A replica can be delayed by WAL or conflict handling, and order history may have read-after-write requirements. Measure lag and route only journeys that permit the observed staleness.
Closing after averages improve. Tail latency, a dominant tenant, background writes, or maintenance backlog can remain impaired. Compare the same bounded populations and watch long enough for queued work and retries to drain.
Conflating rejection with database failure. Admission controls may deliberately reject optional work to preserve capacity. Count rejection separately, then let the user-journey SLI decide whether it is a visible failure.
Security, privacy, capacity, and cost implications
Query text, parameters, plans, statistics arrays, application names, and activity views can expose PII or personal data, tenant boundaries, payment references, authorization predicates, or internal topology. Use least-privilege diagnostic roles, redact exports, audit access and cancellation, and define retention. Incident urgency does not remove privacy obligations.
Never use raw SQL, query identifier, PID, transaction ID, tenant, user, order, request ID, trace ID, span ID, or exception text as metric labels. Aggregate by bounded route, operation, environment, role, wait class, outcome, and migration phase. Keep drill-down identity in protected logs.
Diagnostics and repairs consume capacity. Execution analysis, statistics collection, index builds, vacuum, reindex, backfills, partition maintenance, and rollback compete for CPU, memory, I/O, WAL, disk, pools, and replicas. Every action needs guardrails and a stop condition.
Mitigation can change product behavior. Shedding optional reports, using stale replicas, or cancelling work requires an approved user and correctness contract. Orders, inventory, payment, and authorization must fail closed unless an alternative preserves the same invariant.
Testing and production validation
Run incident drills with synthetic skew, a plan estimate error, a bounded blocker, pool saturation, an old transaction, a pruning miss, and a failed concurrent index fixture. Require responders to establish evidence boundaries before acting. Score correctness and safe mitigation, not speed alone.
Test telemetry resets, missing query text permissions, sampled traces, stale cumulative statistics, and an unavailable replica. The runbook must still produce a bounded hypothesis without inventing data. Verify dashboards keep labels finite and incident exports redact values.
For each permanent repair, run its article’s test matrix: statistics classes, plan rows/loops/buffers, index read/write cost, MVCC churn, lock timeouts, partition boundaries, mixed application versions, backfill restart, invalid-index cleanup, and rollback/roll-forward.
Production recovery needs a comparable demand window and sustained observation. Confirm p50/p95/p99 journey latency, success/error/timeout/rejection, retry volume, pool wait, statement duration, lock wait, transaction age, buffers, temporary I/O, CPU, I/O, WAL, replicas, maintenance, and business correctness. Preserve the incident timeline and distinguish mitigations from permanent changes.
Validate rollback and roll-forward drills after the incident. Restore the prior application against the expanded schema, pause and resume the backfill, detect an invalid index, and prove a lock-timeout exit leaves no hidden partial state. A runbook that only covers the successful path will fail during the next constrained change.
Review alert quality. A page should represent urgent user impact or fast SLO burn, not a single plan node or instantaneous database threshold. Pool wait, old transactions, lock wait, temporary I/O, or dead tuples can support diagnosis and tickets. Multi-signal alerting reduces pressure to execute dangerous diagnostics before impact is confirmed.
The postmortem should name detection gaps, unsafe retry paths, transaction ownership, missing capacity tests, statistics or partition lifecycle failures, and change-control weaknesses. Assign durable controls with verification dates. Do not turn one observed plan into a permanent universal rule; preserve the workload and evidence conditions that made it problematic.
Operations checklist
- Declare incident owner, affected journey, SLI, deployment, route, operation, role, and time window.
- Split request time into queue, pool, database execution, lock, rollback, retry, replica, and response clocks.
- Group normalized query families in restricted diagnostics without exporting literals.
- Check demand, admitted concurrency, pool occupancy, waits, blockers, transaction age, retries, and replicas.
- Record statistics reset and evidence collection boundaries.
- Capture a non-executing plan first; execute only a known-safe read under bounded controls.
- Compare estimates, actuals, loops, filters, joins, sorts, buffers, spill, WAL, and returned rows.
- Check statistics, indexes, visibility, dead tuples, vacuum, partition pruning, and migration/index state.
- Pause optional reports, backfills, DDL, and unsafe retries before increasing capacity pressure.
- Cancel only with ownership, side-effect, rollback, idempotency, and retry analysis.
- Stage permanent fixes independently with correctness, capacity, failure, and rollback tests.
- Close after sustained user, database, maintenance, replica, security, and business verification.
Official sources
- PostgreSQL 18: Monitoring Database Activity — activity, statistics, locks, and progress views; accessed 2026-08-04.
- PostgreSQL 18: pg_stat_statements — normalized workload statistics and access boundaries; accessed 2026-08-04.
- PostgreSQL 18: Using EXPLAIN — plan and execution evidence; accessed 2026-08-04.
- PostgreSQL 18: Viewing Locks — blocker and lock evidence; accessed 2026-08-04.
- Prometheus Instrumentation Practices — metric and label design; accessed 2026-08-04.
- Google SRE: Addressing Cascading Failures — overload-safe diagnosis and mitigation; accessed 2026-08-04.
Continue the learning path
When the affected application is Spring Boot, Production Spring Boot Incident Troubleshooting carries this database evidence through the framework’s configuration, transaction, JPA, executor, security, Actuator, idempotency, and durable-order boundaries before declaring recovery.
Use Database Query Performance for Backend Systems for the evidence map, PostgreSQL EXPLAIN and Query Plans for node-level work, and Database Lock Contention and Long Transactions for the blocker graph. Compare the broader telemetry process in Production Incident Troubleshooting with Logs, Metrics, and Traces. Continue through System Design or Topics.