pgverdict

pgverdict turns your AI agent into a Postgres performance engineer that refuses to guess.

It is an MCP server for Claude Code (or any MCP client). You ask "why is my app slow?" — the agent uses pgverdict to find the hot queries, propose an index, actually build it on a disposable clone of your data, measure it, check that it doesn't slow anything else down, and hand you a migration file with the proof embedded:

"This index reduced query X from 340 ms to 18 ms, costs ~0.02 ms per write across 3 400 writes/day, no regression across the top queries — here is the migration."

Classic index advisors stop at planner estimates. pgverdict treats an estimate as a hypothesis, not an answer: every output is labelled estimate_only, simulated, or measured, and only measured results can become a verdict. A recommendation without a measurement is a bug, not a feature.

Install & first result in a minute

You need uv (no Python setup required) and a Postgres with the pg_stat_statements extension. Docker is needed only for the measuring step.

1. Describe your database in ~/.config/pgverdict/profiles.toml — a read-only role is all it needs:

[profiles.myapp-prod]
prod_dsn       = "postgresql://pgverdict_ro@localhost:5432/myapp"   # password via ~/.pgpass
environment    = "production"
clone_provider = "docker"   # enables measured verification

2. Register the server with Claude Code:

claude mcp add pgverdict -- uvx pgverdict

3. Ask Claude:

"Use pgverdict: what are the worst queries in myapp-prod, and is any index worth adding — verified, not guessed?"

No database at hand? Demo with planted bugs

The repo ships a testbed: a Docker Postgres with a realistic multi-tenant schema and deliberately planted pathologies — a missing index, a dead index, duplicate indexes, a heavily skewed tenant. Every pathology doubles as an acceptance test: pgverdict must find it.

git clone https://github.com/Svaca33/pgverdict && cd pgverdict
docker compose -f testbed/docker-compose.yml up -d --wait
uv run pgverdict-workload --iterations 300
claude mcp add pgverdict --env PGVERDICT_PROFILES=./testbed/profiles.toml -- uv run pgverdict

Then ask "what are the hotspots in the testbed profile?" — and let the agent walk the whole loop down to a measured verdict.

Related MCP server: Postgres MCP Pro

How it works — the verification loop

1. Identify — rank real hotspots from pg_stat_statements and table/index statistics

2. Ground — recover realistic bind parameters: real values from auto_explain logs when available, otherwise synthesized from pg_stats (typical + worst case — three orders of magnitude can hide behind a $1)

3. Simulate — HypoPG virtual index, EXPLAIN cost delta — estimates only

4. Measure — real CREATE INDEX on a disposable data-bearing clone, EXPLAIN (ANALYZE, BUFFERS) before/after, plus a measured write-cost micro-benchmark

5. Regress — re-measure the top-N production queries with the index present

The verdict is arithmetic, not vibes: net_ms_per_day = Σ Δread × reads/day − Δwrite × writes/day, REJECT on any top-query regression. Accepted verifications become migration files (raw SQL / Alembic / EF Core) with the evidence embedded as a comment, so the proof travels into code review.

MCP tools

There is also pgverdict-report, a cron-friendly CLI that writes a markdown digest (hotspots, dead weight, simulated candidates) — no server, no port:

pgverdict-report --profile myapp-prod --out reports/weekly.md

Safety posture

• Local developer tool, stdio transport, no listening port

• Production is opened read-only (read-only transactions, 5 s statement / 1 s lock timeouts on every statement); no code path writes to the analysed database

• Clones are provisioned per verification and always destroyed — your data never leaves your machine, and never lingers on it either

• Generated migrations are files, never executed

• Database-derived text is treated as data, never as instructions; text values sampled from statistics are redacted by default (redact = false per profile to opt out)

• Every response echoes profile + environment, so the transcript always shows which database was touched; production profiles carry a warning banner

Status

0.1.0 — first functional release. The whole loop works end-to-end and is exercised in CI against the testbed (CHANGELOG). Honest caveat: real-world validation is testbed-grade so far — if you run pgverdict against a real database, your feedback is exactly what this release is for. Planned next: Neon / Database Lab clone providers for large databases, and automatic grounding from auto_explain.

Full working spec: docs/pgverdict-spec-v0.3.md · Domain glossary: CONTEXT.md

License

MIT