pgverdict

Status: pre-alpha, under active development. Nothing here is usable yet — this repository is public from day one by design. The first useful milestone (M1) is in progress.

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

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