PG Bloat Detective

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 (pg_stat_user_tables) + exact pgstattuple_approx 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).

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

3. 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 − avg_leaf_density. 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 → pg_repack / VACUUM FULL. Index: index-bloated (pgstatindex bloat >30% → needs REINDEX, VACUUM can't fix it) · index-unused (idx_scan=0 across snapshots → DROP candidate, verify with pg_stat_statements first).

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:

moment	pg_stat dead	approx dead%	index bloat% (density)	verdict
before fix	5,108,201	— (single-snapshot lag)	41.9%	vacuum-starved + index-bloated churn_pkey
after fix	0	0.0%	9.9%	normal — steady state, leave alone

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 pg-bloat-detective   # pulls mcp + psycopg
bloat-mcp                        # stdio transport (also: docker run -i pg-bloat-detective)

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"}}}}

tool	what the agent gets
bloat_check	verdicts + evidence + action per table/index
bloat_collect	snapshot timeline, return fresh verdicts
bloat_timeline	dead/approx/index-bloat series — when it started
bloat_blockers	pid + query + xmin age + slot — who to kill
bloat_live_diagnose	one-shot throwaway snapshot, diagnose, discard

Public listing: official MCP Registry (io.github.r69shabh/pg-bloat-detective) · mcp.so · awesome-mcp-servers (Databases).

Interfaces

• CLI — collect | analyze | report [--html|--json] | serve [--port] (this README's quickstart).
• Dashboard — Next.js single-incident card at dashboard/ (report --json feeds data.json): verdict banner, dead-tuple + index-bloat charts, blockers + findings tables.
• Prometheus + Grafana — serve exposes :9187/metrics (pgbloat_dead_tuples, pgbloat_approx_dead_pct, pgbloat_index_bloat_pct, pgbloat_index_scans, pgbloat_xmin_holder_age_seconds); grafana/ has dashboard JSON + provisioning, docker-compose.yml wires pg → exporter → Prometheus → Grafana.
• Docker — Dockerfile runs the MCP server over stdio (docker run -i); compose adds the demo DB + observability stack.

FAQ (how people actually search for this)

• "postgres table bloated how to check" → collect twice, analyze prints the verdict + dead/live + evidence. needs-rewrite means pg_repack/VACUUM FULL; anything else tells you the cheaper fix first.
• "postgres autovacuum not keeping up / dead tuples growing" → vacuum-starved (threshold 50 + 0.2×live from your live pg_settings) vs blocked-by-* (a session pinning xmin). The timeline chart shows whether dead is rising (losing) or spiked-then-zero (blocked-then-freed).
• "postgres vacuum blocked by idle in transaction" → we name the PID, query, and xmin age. SELECT pg_terminate_backend(pid) (or let it finish), then VACUUM table.
• "postgres index bloat check / reindex needed" → index-bloated with exact pgstatindex % — VACUUM cannot fix it, only REINDEX/pg_repack. Stale pg_stat counters are cross-checked against pgstattuple_approx so you don't chase ghosts.
• "postgres unused indexes find / drop" → index-unused (idx_scan=0 across every snapshot) with size in bytes. Confirm with pg_stat_statements, then DROP.
• "rds / neon / supabase bloat" → read-only by design; needs only pg_stat_* views + optional pgstattuple extension. No superuser, no agent install.
• "pg_bloat_check vs pgstattuple vs pganalyze vs pghero" → one-shot estimators give you a %; we give you the timeline + the blocker + the fix + the before/after proof, heap and index, in one open-source tool. See PLAN.md competitor table.

Keywords

postgresql bloat · postgres bloat check · pg_bloat_check alternative · vacuum blocked · autovacuum tuning · dead tuples · pgstattuple_approx · index bloat · pgstatindex · reindex needed · unused indexes · idle in transaction · backend_xmin · replication slot lag · prepared transaction · table bloat estimation · postgres dba tools · postgres monitoring prometheus grafana · MCP server postgres · Claude postgres DBA