Metadata-Version: 2.4
Name: pgverdict
Version: 0.1.0
Summary: Postgres performance MCP server with a verification loop — recommendations backed by measurement, not estimates
Project-URL: Homepage, https://github.com/Svaca33/pgverdict
Project-URL: Repository, https://github.com/Svaca33/pgverdict
Author: Ondřej Svačina
License-Expression: MIT
License-File: LICENSE
Keywords: explain,index,mcp,performance,postgres,postgresql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Requires-Python: >=3.11
Requires-Dist: asyncpg>=0.30
Requires-Dist: mcp>=1.2
Requires-Dist: sqlglot>=30.15.0
Description-Content-Type: text/markdown

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

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

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

```bash
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](testbed/profiles.toml) for the format) — a read-only role is all it needs.

## MCP tools (M1)

| Tool | What it does |
|---|---|
| `list_profiles` | The configured target databases; every other tool requires an explicit `profile` |
| `list_hotspots` | Queries ranked by total execution time (frequency × cost) from `pg_stat_statements` |
| `explain_query` | `EXPLAIN` by SQL or queryid, with a plain-language reading of what's expensive |
| `find_dead_weight` | Never-scanned indexes, exact duplicates, prefix-redundant pairs, low leaf density |
| `propose_index` | Candidate indexes cost-simulated with HypoPG, grounded from `pg_stats` (typical + worst + selective case). `evidence_level: simulated` — **never a recommendation** |
| `verify_index` | The verdict: real `CREATE INDEX` on a disposable Docker clone, `EXPLAIN (ANALYZE, BUFFERS)` before/after with grounded parameters, a **measured** write-cost micro-benchmark, and a regression check across top queries. `evidence_level: measured`, ACCEPT/REJECT with reasons |
| `recover_parameters` | Real production parameter values from an `auto_explain` log (jsonlog or stderr format) — always a better grounding source than statistics |
| `generate_migration` | Turns an ACCEPT verification into a migration **file** (raw SQL / Alembic / EF Core): `CREATE INDEX CONCURRENTLY` with the measured evidence embedded as a comment. Refuses non-ACCEPT verdicts; overrides are stamped into the comment |

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:

```bash
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](docs/pgverdict-spec-v0.3.md) for the full working spec.

## License

[MIT](LICENSE)
