pgrundev/pgbot

pgbot is a read-only PostgreSQL diagnostic tool that connects with a pg_monitor role, reads Postgres's own statistics views, and produces a findings-first health report — CRITICAL, WARNING, NOTE, then a GOOD list naming what it actually verified. The read-only guarantee lives in the role, not a flag; session-level settings (default_transaction_read_only, statement_timeout=15s, lock_timeout=2s) and BEGIN READ ONLY transactions are defense in depth on top of it.

The design philosophy is deliberate minimalism. One static binary, no collector, no time-series database, nothing installed in the database itself. Every finding is computed deterministically in Go from SQL. The optional AI layer — pgbot explain or pgbot ask — only interprets findings; it never generates them. The tool is explicit that host OS metrics (CPU, disk IOPS, memory) are unreachable over a SQL connection and doesn't pretend otherwise.

The baseline store is what makes repeated runs useful. After the first couple of runs, pgbot reports what changed: a query that got slower, a table that started sequential-scanning, an index that stopped being used. On serverless databases like Neon that discard statistics on scale-to-zero, pgbot detects the stats reset and suppresses counter-based findings rather than reporting a fake regression.

The --json output is versioned at 1.2.0 with a published JSON Schema, and every section carries an exactness label (sampled, cumulative, scraped, or unavailable) so a consumer never mistakes a cumulative total for a live rate. The pgbot mcp command exposes the same deterministic findings over the Model Context Protocol, with all tools read-only and connection strings never exposed to the model.

The advise command is the most technically interesting piece: it reads slow queries from pg_stat_statements, derives candidate indexes from the planner's sequential-scan filters, creates them hypothetically using the hypopg extension, re-plans the query, and only reports an index if the planner actually switches to it and estimated cost drops. Nothing is ever built. One example in the README shows a cost drop from 4653 to 4.1 — a reduction of 99.9% — on a validated recommendation.

CI integration is well-considered. The schema profile (--profile=schema) runs only catalog-derived findings, safe on an empty CI database. Paired with --fail-on-new, it gates only on findings a PR introduced, not pre-existing issues. SARIF output uploads directly to GitHub's Security tab. Exit codes are a stable contract: 0 clean, 1 warn, 2 critical, 3 connection failure, 64 usage error.

The privacy handling is specific: pg_stat_statements query text is normalized to $1 placeholders before it can reach the AI layer, and pg_stat_activity literals are scrubbed of strings, numerics, emails, and UUIDs. Connection strings are redacted in all output.