pg-bloat-detective

Read-only Postgres bloat detective: cheap timeline, named blocker, index bloat, evidence-linked report, before/after proof.

Quickstart

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

Heap: normal (steady-state, leave alone) · vacuum-starved · blocked-by-idle-xact|slot|prepared|active-xact · needs-rewrite. Index: index-bloated (pgstatindex bloat >30% → needs REINDEX, VACUUM can't fix it) · index-unused (idx_scan=0 across snapshots → DROP candidate).

Index bloat is the differentiator (pganalyze admits the gap): collect runs exact pgstatindex only on indexes under the 1GB cost guard (--max-bytes tunes it, --allow-large overrides), metric = 100 − avg_leaf_density. Exported as pgbloat_index_bloat_pct / pgbloat_index_scans, charted in the shadcn dashboard, Graphed in Grafana.

Benchmark (measured 2026-10-05, local PG16 — heap + index, live run just now)

30s churn, no blocker (autovacuum simply lost) → VACUUM ANALYZE + REINDEX:

Earlier hole, now closed: an index at 83.7% bloat (density 16.3) dropped to 9.9% (density 90.1) after REINDEX — VACUUM alone never touches that. Full log in REPORT.md, visual in report.html.

See PLAN.md for the detailed 3-week plan + competitor gap.

MCP server (Claude Desktop / Cursor / any MCP client)

5 tools, read-only on Postgres (5s statement timeout, read-only tx). bloat_check returns verdict + evidence + fix per table/index; bloat_live_diagnose snapshots a throwaway DB so your timeline file is never touched — safest for prod DSNs.

pip install -e .   # pulls mcp + psycopg
bloat-mcp          # stdio transport

Claude Desktop (~/Library/Application Support/Claude/claude_desktop_config.json):

{"mcpServers": {"bloat-detective": {
  "command": "/abs/path/pg-bloat-detective/.venv/bin/python",
  "args": ["-m", "bloatdetective.mcp_server"],
  "env": {"BLOAT_DSN": "dbname=bloatdemo user=postgres host=localhost port=5433",
          "BLOAT_DB": "/abs/path/pg-bloat-detective/timeline.db"}}}}

Public listing: mcp.so + PulseMCP accept GitHub submissions (server.json + README badge) — repo is ready, submit the URL after push.