<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Database Troubleshooting — Diagnostic Patterns

## Contents
- Stage 1: connections
- Stage 2: locks and blockers
- Stage 3: slow queries
- Stage 4: disk, IO, bloat
- Stage 5: replication lag
- Safe kill procedure
- Evidence snapshot script
- Gotchas

## Stage 1: connections

```sql
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY 2 DESC;

-- Worst 'idle in transaction' offenders (these hold locks and block vacuum):
SELECT pid, usename, application_name,
       now() - xact_start AS xact_age, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC LIMIT 10;
```

## Stage 2: locks and blockers

```sql
-- Who blocks whom (root blockers have blocked_by = {}):
SELECT a.pid,
       a.pid AS blocked_pid,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.wait_event_type,
       now() - a.query_start AS waiting_for,
       left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

-- Detail on the root blocker:
SELECT pid, state, now() - xact_start AS xact_age, left(query, 120) AS query
FROM pg_stat_activity WHERE pid = <root_pid>;
```

Kill the ROOT of the tree, not the waiters — they resolve on their own.

## Stage 3: slow queries

```sql
-- Top by cumulative time:
SELECT round(total_exec_time) AS total_ms, calls,
       round(mean_exec_time, 1) AS mean_ms, rows,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Reset AFTER capturing, to watch fresh accumulation during the incident:
SELECT pg_stat_statements_reset();

-- Currently running long queries:
SELECT pid, now() - query_start AS runtime, state, left(query, 100)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds'
ORDER BY runtime DESC;
```

## Stage 4: disk, IO, bloat

```bash
df -h /var/lib/postgresql        # data volume
du -sh /var/lib/postgresql/16/main/pg_wal   # runaway WAL?
```

```sql
-- Dead tuples (vacuum debt):
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Biggest relations:
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
```

Out of disk: free space OUTSIDE the data dir first (old logs, temp dumps).
Runaway `pg_wal` usually means a dead replication slot:

```sql
SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
-- Inactive slot retaining GBs → confirm the consumer is truly gone, then:
SELECT pg_drop_replication_slot('dead_slot');
```

## Stage 5: replication lag

```sql
-- On the primary:
SELECT client_addr, state, sent_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- On the replica:
SELECT now() - pg_last_xact_replay_timestamp() AS lag;
```

## Safe kill procedure

```sql
SELECT pg_cancel_backend(<pid>);      -- 1) cancel the query
-- wait 5-10s; if still there:
SELECT pg_terminate_backend(<pid>);   -- 2) drop the connection
```

Never `kill -9` a backend: Postgres treats it as a crash and restarts with
full recovery, turning one bad query into a full outage.

## Evidence snapshot script

```bash
#!/bin/sh
# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai
# snapshot.sh — capture incident evidence before intervening.
TS=$(date +%Y%m%dT%H%M%S)
OUT="incident-$TS.txt"
for Q in \
  "SELECT now()" \
  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state" \
  "SELECT pid, state, now()-query_start rt, wait_event_type, left(query,120) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY rt DESC" \
  "SELECT a.pid, pg_blocking_pids(a.pid), left(a.query,100) FROM pg_stat_activity a WHERE cardinality(pg_blocking_pids(a.pid)) > 0" \
  "SELECT round(total_exec_time) ms, calls, left(query,100) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15"
do
  echo "== $Q" >> "$OUT"; psql -X -c "$Q" >> "$OUT" 2>&1
done
echo "evidence saved to $OUT"
```

## Gotchas

- `pg_stat_activity.query` shows the LAST query for idle sessions — an
  `idle in transaction` session's displayed query already finished; the
  transaction is what's still open.
- `pg_blocking_pids()` is Postgres ≥ 9.6; on older versions use the classic
  pg_locks self-join.
- `pg_stat_statements` normalizes literals (`WHERE id = $1`) — you cannot
  recover the exact parameter values from it; check application logs for those.
- Lock waits don't consume CPU — "server looks idle but everything hangs" is
  the lock-stage signature, not a reason to skip to stage 4.
- On replicas, long SELECTs conflict with WAL replay; lag with an idle-looking
  replica often traces to one analytics query.
- After ANY intervention, re-run the stage check — a killed blocker often
  reveals a second blocker behind it.