PG Bloat Detective MCP server

pg-bloat-detective — PostgreSQL Bloat Checker, Vacuum Blocker Finder & Index Bloat Detector

Find why your Postgres database is bloated, who is blocking VACUUM, and which indexes need REINDEX — read-only, with proof.

vacuum-starved · blocked-by-idle-xact · needs-rewrite · index-bloated · index-unused — one verdict per table/index, with PID + query evidence.

mcp-name: io.github.r69shabh/pg-bloat-detective

pip install pg-bloat-detective · Docker ready · MCP server for Claude/Cursor · Prometheus + Grafana · Next.js dashboard

pip install pg-bloat-detective          # or: pip install -e ".[dev]" from source
python -m bloatdetective collect --db timeline.db   # read-only snapshot (5s timeout)
python -m bloatdetective analyze --db timeline.db   # verdicts + evidence
python -m bloatdetective report --db timeline.db --html --out report.html
bloat-mcp                              # MCP server (stdio) for Claude Desktop / Cursor

What this is

A PostgreSQL bloat analysis tool that answers three questions other tools leave open:

1. Is this table bloated, and why? — dead-tuple timeline (pgstatusertables) + exact pgstattupleapprox confirmation. Verdicts: normal (leave it alone), vacuum-starved (autovacuum losing), blocked-by-idle-xact|slot|prepared|active-xact (named PID + query holding backend_xmin), needs-rewrite (approx dead% >30%, VACUUM can't reclaim).

1. Who is blocking VACUUM / autovacuum? — oldest backend_xmin holder tracked per snapshot: PID, transaction age, query text, stale replication slots, prepared transactions. Priority: prepared-xact > stale-slot > idle-in-transaction. Kill one, VACUUM, done.
2. Is the index bloated? (VACUUM never fixes this) — exact pgstatindex on indexes under a 1GB cost guard (tunable --max-bytes, override --allow-large), metric 100 − avgleafdensity. Over 30% → index-bloated, schedule REINDEX. Zero scans across all snapshots → index-unused, DROP candidate. This is the gap pganalyze admits to — our differentiator, proven live: 83.7% → 9.9% after REINDEX while VACUUM did nothing.

One SQLite file = full timeline. One static page = the whole incident. No agents on prod, no writes (read-only transactions, statement timeout 5s), works on RDS / Neon / Supabase / self-hosted PG 12–17.

Quickstart (60-second demo)

docker compose up -d
pip install -e ".[dev]"
python -m bloatdetective collect --db timeline.db   # snapshot prod (read-only)
python workload/churn.py --mode idle-xact --secs 60 &  # create the failure
python -m bloatdetective collect --db timeline.db
python -m bloatdetective report --db timeline.db --html --out report.html
python -m bloatdetective serve --db timeline.db &   # :9187/metrics for Prometheus
pytest
bash scripts/benchmark.sh   # full before/after proof -> REPORT.md

Verdicts (read them as instructions)

Heap: normal (steady-state, leave alone) · vacuum-starved → run VACUUM / tune autovacuum · blocked-by-idle-xact|slot|prepared|active-xact → end the named session, then VACUUM · needs-rewrite → pgrepack / VACUUM FULL. Index: index-bloated (pgstatindex bloat >30% → needs REINDEX, VACUUM can't fix it) · index-unused (idxscan=0 across snapshots → DROP candidate, verify with pgstatstatements first).

Shortened. The full README is on GitHub.