# pgverdict

[![PyPI](https://img.shields.io/pypi/v/pgverdict)](https://pypi.org/project/pgverdict/)
[![CI](https://github.com/Svaca33/pgverdict/actions/workflows/ci.yml/badge.svg)](https://github.com/Svaca33/pgverdict/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)

**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](https://docs.astral.sh/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:

```toml
[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:

```bash
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.

```bash
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.

## 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

| Tool | What it does | Evidence |
|---|---|---|
| `list_profiles` | The configured target databases; every other tool requires an explicit `profile` | — |
| `list_hotspots` | Queries ranked by total execution time (frequency × cost) | `estimate_only` |
| `explain_query` | `EXPLAIN` by SQL or queryid, with a plain-language reading of what's expensive | `estimate_only` |
| `find_dead_weight` | Never-scanned indexes, exact duplicates, prefix-redundant pairs, low leaf density | `estimate_only` |
| `propose_index` | Candidate indexes cost-simulated with HypoPG, grounded parameters — **never a recommendation** | `simulated` |
| `verify_index` | Real index on a disposable clone: measured timings, write cost, regression check, ACCEPT/REJECT | `measured` |
| `recover_parameters` | Real production parameter values from an `auto_explain` log | — |
| `generate_migration` | Migration file from an ACCEPT verification; refuses unproven indexes, overrides are stamped | `measured` |

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

```bash
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](CHANGELOG.md)). 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](docs/pgverdict-spec-v0.3.md) · Domain glossary: [CONTEXT.md](CONTEXT.md)

## License

[MIT](LICENSE)