# Postgres Diagnostics

> Diagnose PostgreSQL health and performance using the read-only pgbot tool. Use for slow databases, expensive queries, unused indexes, disk usage, bloat, autovacuum, vacuum health, and configuration questions.

- Skill: `pgrundev/postgres-diagnostics` (Agent Skill)
- Install (CLI): `npx skillmds@latest add pgrundev/postgres-diagnostics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/pgrundev/postgres-diagnostics/raw
- Safety review: WARNING (external: skill-scanner PASS, skillspector CAUTION)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: pgrundev (https://skillmd.com/u/pgrundev)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/pgrundev/postgres-diagnostics

---


# PostgreSQL diagnostics with pgbot

pgbot is a **read-only** Postgres diagnostic tool. Its findings are computed
**deterministically** (in Go, not by a model) and it **never writes** to the
database. Treat pgbot's output as the source of truth. Your job is to run the
right command, then interpret, prioritize, and recommend — never to invent
diagnoses or act on the database.

## When to use this skill

- "why is my database slow", "what should I optimize", "is my DB healthy?"
- "which indexes can I drop", "what's using all the disk", "is autovacuum keeping up?"
- "what query is eating the database", "should I re-tune anything?"
- Any Postgres performance / health / bloat / index / vacuum / config question.

## How to run it

**If the pgbot MCP server is connected**, call its tools: `inspect` (start here),
`top_queries`, `vacuum_health`, `unused_indexes`, `why`. They return stable JSON.
(The `logs`, `waits`, `activity`, `erd`, and `report` commands are CLI-only for
now — run them through the shell.)

**Otherwise run the CLI.** It needs a connection string for a read-capable role
(ideally one with `pg_monitor`). Pick the command that matches the question:

| Question | Command |
|---|---|
| Open-ended / "is it healthy" | `pgbot inspect "$DSN"` (health score + worst-first findings) |
| Full detail | `pgbot inspect "$DSN" --full` |
| "What's eating the database?" | `pgbot queries "$DSN"` (top by total time; `--by-calls` to re-rank) |
| "What's using disk / biggest tables?" | `pgbot tables "$DSN"` (size + seq-vs-index scan = missing-index radar) |
| "Is autovacuum keeping up?" | `pgbot vacuum "$DSN"` |
| "Which indexes can I drop?" | `pgbot indexes "$DSN"` |
| "Should I re-tune config?" | `pgbot tune "$DSN"` |
| "Why is it slow RIGHT NOW?" | `pgbot why --duration 10s "$DSN"` (live wait sampling → evidence-gated cause: lock contention vs IO vs CPU vs client; refuses to guess on thin evidence) |
| "Who is connected / what's running?" | `pgbot activity "$DSN"` (live sessions: PIDs, states, waits, ages, scrubbed SQL) |
| "Where does database time go?" | `pgbot waits --duration 10s "$DSN"` (sampled wait classes + blockers named only with sustained evidence) |
| "What changed since yesterday?" | `pgbot why "$DSN"` / `pgbot diff --since 24h` (offline, from stored snapshots) |
| "What's in the server log?" | `pgbot logs "$DSN"` (`--live` to follow, `--level error` or `--level audit` to filter; needs one extra grant, printed when missing) |
| "What does the schema look like?" | `pgbot erd "$DSN"` (`--mermaid` for a renderable diagram, `--html > schema.html` for an interactive file) |
| Full report for a human to read | `pgbot report "$DSN" > report.html` (self-contained page: findings, queries, indexes, waits) |
| Machine-readable for parsing | `pgbot inspect "$DSN" --json` |

Add `--timeout 60s` for large or remote databases.

## First run — when a prerequisite is missing

Don't stall and don't improvise; each gap has one fix:

- **`pgbot` not on PATH:** install it — `curl -fsSL https://pgbot.dev/install | sh`
  (or `brew install pgrundev/tap/pgbot`, or run without installing via
  `npx @pgbot/cli`). Run it through the normal permission prompt so the user
  sees the command.
- **No connection string:** check `$DATABASE_URL`, then ask the user for one.
  Never guess or assemble credentials yourself.
- **Role or extension gaps:** if the installed pgbot has `init`, run
  `pgbot init --verify "$DSN"` — it checks pg_monitor and pg_stat_statements
  and names each fix. Older builds report the same gaps at connect time.
  To create the read-only role, generate the SQL with `pgbot init` (or use the
  Setup section of the pgbot README) and **hand it to the user to run as
  admin** — never execute it yourself: pgbot never writes, and neither do you.

## Rules — non-negotiable

1. **Findings are facts.** Do not invent a number, table, index, or query id
   beyond what pgbot reports. If pgbot didn't measure it, say what pgbot would
   need to collect to find out.
2. **Carry every caveat into the recommendation.** Example: "unused index" scan
   counts are per-node — on a primary, a replica may still use an index that
   looks unused. pgbot flags this; **never recommend dropping an index without
   its caveat.** (pgbot's `tables`/`indexes` and the JSON make replication state
   visible — check it.)
3. **Never execute the user's query to diagnose it.** No `EXPLAIN ANALYZE`, no
   running the statement "just to time it." Suggest only safe, non-executing
   steps (`EXPLAIN` without `ANALYZE` is fine; it doesn't run the query).
4. **Prioritize by impact, not by count.** Risk (time-to-incident: wraparound, a
   WAL-pinning replication slot, a filling disk) comes first. Then the biggest
   latency/storage win. A 9 GiB unused-index reclaim may matter *less* than one
   query eating 60% of DB time — say which to do first and why.
5. **Never write to the database.** pgbot is read-only; you recommend, you don't
   act. Hand the user the exact `DROP INDEX CONCURRENTLY` / `ALTER` / `VACUUM`
   statement to run themselves.
6. **Hedge low confidence.** A finding below 0.5 confidence is a possibility
   ("may", "possibly"), not an assertion.
7. **Label the evidence.** Keep three categories visibly distinct in your
   answer: findings proven by pgbot's data, recommendations that still need
   review (of code, replicas, or workload), and conclusions blocked by missing
   statistics or permissions.

## Output shape

1. One-line health verdict.
2. Worst-first, at most ~3 issues. For each: the problem *with pgbot's number* →
   a likely cause **only if the data (deltas/events) supports one** → a safe,
   concrete recommended step, with its caveat inline.
3. Briefly name what's healthy, so the user knows what was checked.

## Reading the signals

- **`tables`:** a large table with heavy `seq scans` and few `idx scans` is a
  likely missing-index candidate — cross-check it against `queries` (a top query
  filtering that table confirms it).
- **`queries`:** `share` is % of total DB execution time. A single query above
  ~30% is the real hot path; index-dropping won't touch it.
- **`vacuum`:** `due? yes` with a stale/`never` last-autovacuum means autovacuum
  is falling behind — the early signal for bloat and, downstream, wraparound.
- **replication-slot findings:** an inactive slot retaining WAL fills the disk;
  treat it as time-to-incident, not cosmetic.

## Safety and privacy

pgbot only ever reads. Nothing leaves the machine except `pgbot explain` /
`pgbot ask`, which send the **PII-free** findings (normalized query text, no
literals) to an LLM and say so first. Use a role with `pg_monitor`; never paste a
production superuser credential where it can leak.