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.
A PostgreSQL bloat analysis tool that answers three questions other tools leave open:
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).
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.
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.
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)
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):
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
Ainda não há lista de ferramentas. Servidores remotos recebem uma após a primeira verificação de saúde.
Perguntas frequentes
Perguntas
Como instalo PG Bloat Detective?
Copie nesta página a configuração stdio para Cursor, Claude Desktop, Claude Code ou VS Code. Ela usa a instalação do publicador, não um proxy do PlugMCP.
PG Bloat Detective precisa de uma chave de API?
O publicador lista estas variáveis de ambiente: BLOAT_DSN, BLOAT_DB.
Qual transporte PG Bloat Detective usa?
PG Bloat Detective roda localmente via stdio.
Quando PG Bloat Detective foi verificado pela última vez?