# name: troubleshooting-databases description: Diagnoses live database incidents with a fixed triage runbook — connection exhaustion, lock contention and blocking queries, newly slow queries, disk and bloat, and replication lag — with the exact diagnostic query for each stage and safe kill procedures. Use when the user reports a database outage or incident, "too many connections", queries hanging or timing out, "database is slow" right now, deadlocks, or replica lag. Do not use for proactive query tuning or server configuration work.

# Troubleshooting Databases

# When to use / when NOT to use

• Use for: live incidents — the database is slow, hanging, erroring, or lagging RIGHT NOW (examples are PostgreSQL; the triage order applies to any engine).
• Do NOT use for: proactive query tuning (optimizing-sql-performance) or configuration/maintenance work (administering-postgresql).

# Prime directive

Capture evidence before changing anything. Restarting clears the very state (pg_stat_activity, locks, stats) that explains the incident. Snapshot first (Workflow step 1), then act.

# Triage runbook — run IN ORDER, stop at the first stage that explains the symptom

1. Connections / pool exhaustion — symptom: "too many connections", app timeouts on connect. SELECT state, count(*) FROM pg_stat_activity GROUP BY state; Many idle → app leaks connections / missing pooler. Many idle in transaction → code holds transactions open; find and fix the caller, kill the worst offenders.
2. Locks / blocking queries — symptom: queries hang but CPU is quiet. Run the blocker query (reference file) joining pg_locks to pg_stat_activity; it prints who blocks whom. Kill the ROOT blocker only.
3. Slow queries just deployed — symptom: gradual or post-deploy slowdown. SELECT round(total_exec_time) ms, calls, left(query,100) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; — compare against the last deploy; a new top entry is your suspect.
4. Disk / IO / bloat — symptom: everything slow, writes failing. df -h on the data volume; dead-tuple and size queries from the reference file. Out of disk → free WAL/logs first, never delete files inside the data directory.
5. Replication lag — symptom: stale reads on replicas. SELECT client_addr, state, replay_lag FROM pg_stat_replication; (primary) / SELECT now() - pg_last_xact_replay_timestamp(); (replica). Long-running replica queries or primary write bursts are the usual causes.

# Kill safely

• ✅ First SELECT pg_cancel_backend(pid); (cancels the query, keeps the connection). Only if it doesn't die: pg_terminate_backend(pid).
• ❌ Never kill -9 a Postgres backend — it forces a full crash-recovery restart of the whole server.
• Record every pid you kill, with its query text, in the incident notes.

# Workflow

1. Snapshot evidence: dump pg_stat_activity, the blocker query output, and top pg_stat_statements into a timestamped file BEFORE any intervention.
2. Run the triage runbook in order; stop at the first stage whose check explains the symptom.
3. Apply the smallest intervention for that stage (cancel one pid, fix one caller, free disk).
4. Validate recovery: symptom gone, connection states normal, no waiting locks, lag shrinking.
5. Write the post-incident note: stage, root cause, evidence file, intervention, and the follow-up fix that prevents recurrence (pooler, index, code fix) — routed to the appropriate skill.

# Edge cases & failure modes

• Deadlock errors → Postgres already resolved it (one victim). Read the two queries in the error detail; the fix is consistent lock ordering in application code, not a server change.
• Can't even connect to diagnose → connect to a different database on the instance, or use the reserved superuser slot; on managed services use the provider's performance dashboard.
• idle in transaction recidivism → set idle_in_transaction_session_timeout (e.g. 60s) as a guardrail after the incident.
• Everything checks out but app is slow → the bottleneck is app-side (pool config, N+1, network) — say so explicitly rather than tuning the database blindly.
• Managed replicas lagging with no visible cause → check for vacuum conflicts (max_standby_streaming_delay) and instance-class IO limits.

# References

Copy-paste diagnostic SQL for every stage, blocker tree query, evidence-snapshot script: see references/patterns.md.