Metadata-Version: 2.4
Name: pgverdict
Version: 0.0.1
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 :: 2 - Pre-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
Description-Content-Type: text/markdown

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

## License

[MIT](LICENSE)
