pgverdict

Status: pre-alpha, feature-complete for v1. All four milestones work against the bundled testbed: M1 (hotspots, plan explanation, dead-weight detection), M2 (grounded HypoPG index simulation), M3 (measured verification on a disposable clone, with a write-cost benchmark and regression check) and M4 (evidence-carrying migrations, auto_explain parameter recovery, weekly digest). Not yet released to PyPI beyond a name-reservation placeholder.

Postgres performance MCP server with a verification loop. Instead of "I think this index will help", pgverdict aims for:

"This index reduced query X from 340 ms to 18 ms on a data clone, cost ~0.3 ms per write across 3 400 writes/day, no regression across the top 20 queries — here is the migration."

Every recommendation carries evidence. When evidence cannot be produced, the tool says so explicitly instead of guessing. A recommendation without a measurement is a bug, not a feature.

Why

Small teams run production Postgres without a DBA. When something gets slow they either ask an LLM and apply a plausible-sounding index blind, or open a dashboard they lack the expertise to interpret. Existing index advisors emit suggestions from planner cost estimates — they don't measure the result, don't account for write amplification, and don't check whether the new index regresses other queries.

pgverdict is an MCP server: the agent (e.g. Claude Code) drives the iteration, pgverdict provides the method — identify, ground, simulate, measure, check regressions, and only then deliver a verdict with the migration.

The verification loop

1. Identify — rank real hotspots from pg_stat_statements and table/index statistics
2. Ground — recover realistic bind parameters (real values matter: three orders of magnitude can hide behind a $1)
3. Simulate — HypoPG virtual index, EXPLAIN cost delta — estimates only
4. Measure — real index on a data-bearing clone, EXPLAIN (ANALYZE, BUFFERS) — estimates become evidence
5. Regress — re-run the top-N workload, estimate write amplification, produce a net verdict

Every tool output carries an evidence_level: estimate_only | simulated | measured. Only measured results can become a verdict and a migration.

Safety posture

• Local developer tool, stdio transport, no listening port
• Production is opened read-only (pg_monitor-based role, read-only transactions, strict timeouts); no code path writes to production
• Generated migrations are files, never executed
• Database-derived text is treated as data, never as instructions; literals are redacted by default

Try it in five minutes (no real database needed)

The repo ships a synthetic testbed — a Docker Postgres with a multi-tenant schema, a workload generator, and deliberately planted pathologies (a missing index, a dead index, duplicate and prefix-redundant indexes, a heavily skewed tenant). It doubles as the acceptance-test suite: every pathology must be findable by the corresponding tool.

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

Then register the MCP server with Claude Code (from the repo directory):

claude mcp add pgverdict --env PGVERDICT_PROFILES=./testbed/profiles.toml -- uv run pgverdict

and ask things like "what are the hotspots in the testbed profile?" or "is there dead weight among the indexes?". To point it at your own database, write a ~/.config/pgverdict/profiles.toml (see testbed/profiles.toml for the format) — a read-only role is all it needs.

MCP tools (M1)

There is also pgverdict-report — a CLI for cron/Task Scheduler that writes a markdown digest (hotspots, dead weight, simulated candidates) with no server and no port:

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

Every response echoes profile + environment and carries an evidence_level (estimate_only / simulated / measured). Only measured results carry a verdict: net_ms_per_day = Σ Δread × reads/day − Δwrite × writes/day, REJECT on any top-query regression. Clones are provisioned per verification (clone_provider = "docker") and always destroyed afterwards — data never leaves your machine, and never lingers on it either. Text values sampled from pg_statistic are redacted by default (redact = false per profile to opt out).

Roadmap

• M1 — hotspot listing, plan explanation, dead-weight (unused/duplicate index) detection; bundled Docker testbed with a synthetic workload so you can try it in minutes without risking a real database
• M2 — HypoPG simulation and index proposals, parameter recovery
• M3 — data-bearing clones and real measurement with regression checks
• M4 — migration generation, auto_explain parsing, weekly report mode

See docs/pgverdict-spec-v0.3.md for the full working spec.

License

MIT