pg-perf-mcp
# pg-perf-mcp
[](https://github.com/dixitgurv/pg-perf-mcp/actions/workflows/ci.yml)
An MCP server that lets an AI agent (Claude, or any MCP client) diagnose PostgreSQL performance the way a DBA would: read the plan, find where the time actually went, and propose a specific fix.
It is read-only by design. The agent can look at anything and change nothing.
```
You: orders lookups by status got slow, can you check?
Agent: → explain_query("SELECT id, customer_id, total_cents FROM orders WHERE status = 'disputed'")
The query takes 21 ms because a sequential scan reads 1,000,000 rows and throws
away 995,001 of them. An index on status fixes it; since 'shipped' is 90% of the
table, a partial index keeps it small:
CREATE INDEX CONCURRENTLY ON orders (status) WHERE status <> 'shipped';
```
## What the agent gets
| Tool | What it answers |
|---|---|
| `explain_query` | Runs `EXPLAIN (ANALYZE, BUFFERS)` on one query and returns the nodes where time was spent plus rule-based findings with fixes |
| `slow_queries` | Top queries from `pg_stat_statements` by total time, mean time, calls or disk reads |
| `table_health` | Seq-scan vs index-scan ratio, dead tuples, last vacuum/analyze per table |
| `unused_indexes` | Indexes never scanned, largest first (excludes unique and constraint indexes) |
| `redundant_indexes` | Indexes whose columns are a prefix of another index on the same table |
| `active_queries` | What is running now, for how long, and what it is waiting on |
| `database_overview` | Cache hit rate, temp file usage, deadlocks, memory settings, connection count |
## The plan analyzer
The interesting part is [`plan_analyzer.py`](src/pg_perf_mcp/plan_analyzer.py). It walks the JSON plan and applies rules that encode what an experienced DBA looks for:
| Rule | Fires when | Suggests |
|---|---|---|
| `seq_scan_filter_waste` | A sequential scan discards 10k+ rows and 10x more than it keeps | An index on the filter columns, written out as DDL |
| `join_scans_whole_table` | A hash/merge join reads 100k+ rows of a table to keep under 1% | An index on that table's join key (usually an unindexed foreign key) |
| `row_misestimate` | Actual rows differ from the estimate by 10x or more | `ANALYZE`, then `CREATE STATISTICS` on the correlated columns |
| `sort_spilled_to_disk` | A sort used disk (summed across parallel workers) | A sized `work_mem`, or an index / `LIMIT` when the sort is too big for memory |
| `hash_multi_batch` | A hash table split into batches | `work_mem` / `hash_mem_multiplier`, or a smaller build side |
| `nested_loop_repeated_scan` | The inner side of a nested loop is a scan repeated 1000+ times | An index on the inner join column |
| `index_only_scan_heap_fetches` | An index-only scan still visits the heap for 20%+ of rows | `VACUUM` to refresh the visibility map |
| `cold_cache_reads` | Most blocks came from disk rather than shared buffers | Re-run warm; check working set vs `shared_buffers` |
The rules make the decisions, and the model on the other end explains them and talks them through with you. The model never decides what is wrong on its own, so the same plan always gives the same findings, and every rule has a unit test.
It also reports **exclusive** time per node (a node's time minus its children's), which is what tells you where the time went. Under a `Gather`, worker loops run concurrently, so their time is not multiplied by loop count. Getting that wrong is a common mistake and makes parallel scans look several times slower than they are.
## Results on the demo database
[`demo/seed.sql`](demo/seed.sql) builds 1M orders, 2M order items and 100k customers, with problems planted on purpose. I ran each query in [`demo/queries.sql`](demo/queries.sql), applied the fix the tool suggested, and ran it again (median of 5, PostgreSQL 14, M1 laptop):
| Query | Finding | Fix applied | Before | After |
|---|---|---|---|---|
| Orders by status | `seq_scan_filter_waste` | Partial index on `status` | 21.4 ms | 3.4 ms |
| Customer's orders | `join_scans_whole_table` | Index on `orders.customer_id` | 34.1 ms | 0.7 ms |
| Country + city count | `row_misestimate` (20x under) | `CREATE STATISTICS ON country, city` | estimate 249 rows | estimate matches |
| Sort 2M rows | `sort_spilled_to_disk` | See note | 541 ms | n/a |
The sort is the honest case. It spills 73 MB, and holding it in memory took 205 MB of `work_mem`, which only brought it to 591 ms. The tool says so: past a point, the fix is to stop sorting that much (index or `LIMIT`), not to throw memory at it.
## Safety
An agent with database access needs guard rails that don't depend on the agent behaving:
1. Every session sets `default_transaction_read_only=on`, so Postgres itself rejects writes, including data-modifying CTEs.
2. `EXPLAIN ANALYZE` runs inside a transaction that is always rolled back.
3. `statement_timeout` (default 15 s) bounds every statement.
4. A static check rejects multi-statement input and functions that have side effects even in a read-only transaction (`pg_terminate_backend`, `set_config`, `pg_sleep`, `dblink`, and so on). String literals and comments are stripped first, so `SELECT ';'` is still allowed.
5. For production, connect as a role that has only `pg_monitor` and `pg_read_all_data`.
The integration tests run these refusals against a real server.
## Setup
Requires Python 3.12+ and [uv](https://docs.astral.sh/uv/). `slow_queries` needs the `pg_stat_statements` extension.
```bash
git clone https://github.com/dixitgurv/pg-perf-mcp && cd pg-perf-mcp
uv sync
```
**Claude Code**
```bash
claude mcp add pg-perf --env PG_PERF_DSN="postgresql://readonly@localhost/mydb" \
-- uv --directory /path/to/pg-perf-mcp run pg-perf-mcp
```
**Claude Desktop** (`claude_desktop_config.json`)
```json
{
"mcpServers": {
"pg-perf": {
"command": "uv",
"args": ["--directory", "/path/to/pg-perf-mcp", "run", "pg-perf-mcp"],
"env": { "PG_PERF_DSN": "postgresql://readonly@localhost/mydb" }
}
}
}
```
A production-style read-only role:
```sql
CREATE ROLE perf_reader LOGIN PASSWORD '...';
GRANT pg_monitor, pg_read_all_data TO perf_reader; -- PostgreSQL 14+
```
## Development
```bash
uv run pytest # unit tests + integration tests against a throwaway cluster
uv run ruff check
```
The integration suite starts a temporary PostgreSQL cluster with the local `initdb`/`pg_ctl`, loads the demo data and runs every tool through a real MCP client in-process. Set `PG_PERF_TEST_DSN` to use an existing server instead, which is how CI runs it.
See [docs/HOW_IT_WORKS.md](docs/HOW_IT_WORKS.md) for the design notes.
## Roadmap
- Oracle: parse AWR reports (top SQL, wait events, load profile) with the same findings model
- `hypopg` support, to check that a suggested index would actually be used before anyone builds it
- Plan diffing: compare a query's plan before and after a change
## License
MIT
TDQS
Scored across 7 tools
Each tool targets a clearly distinct object: a single query plan (explain_query), historical stats (slow_queries), live activity (active_queries), table-level stats (table_health), index hygiene (unused_indexes vs redundant_indexes, clearly separated by 'never used' vs 'prefix duplicate'), and cluster-level stats (database_overview). No two tools can plausibly be confused for the same task.
All names are lowercase snake_case and uniformly descriptive, with no mixed conventions or stray prefixes. The only minor deviation is verb style: explain_query is verb_noun while the rest are adjective/noun phrases, but the pattern remains predictable.
Seven tools is tightly scoped to the performance-diagnostics domain, and each one covers a distinct angle (plan, history, live, table, index, cluster). Nothing looks like filler or a duplicate entry point.
Covers the core diagnostic loop: plan analysis, slow-query history, live activity, table health, index waste and instance overview. Minor gaps remain, such as explicit lock/blocking-chain analysis, missing-index recommendations (only unused/redundant are surfaced), and no write/DML plan analysis, but agents can work around these.