pg-bloat-detective
Exposes Postgres bloat and index health metrics (pgbloat_index_bloat_pct, pgbloat_index_scans) on a Prometheus-compatible /metrics endpoint for scraping and monitoring.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@pg-bloat-detective check bloat on my orders table and tell me how to fix it"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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 / CursorWhat this is
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) + exactpgstattuple_approxconfirmation. Verdicts:normal(leave it alone),vacuum-starved(autovacuum losing),blocked-by-idle-xact|slot|prepared|active-xact(named PID + query holdingbackend_xmin),needs-rewrite(approx dead% >30%, VACUUM can't reclaim).Who is blocking VACUUM / autovacuum? — oldest
backend_xminholder 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
pgstatindexon indexes under a 1GB cost guard (tunable--max-bytes, override--allow-large), metric100 − avg_leaf_density. Over 30% →index-bloated, scheduleREINDEX. 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.
Related MCP server: MCP-PostgreSQL-Ops
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.mdVerdicts (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% |
|
after fix | 0 | 0.0% | 9.9% |
|
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 |
| verdicts + evidence + action per table/index |
| snapshot timeline, return fresh verdicts |
| dead/approx/index-bloat series — when it started |
| pid + query + xmin age + slot — who to kill |
| 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 --jsonfeedsdata.json): verdict banner, dead-tuple + index-bloat charts, blockers + findings tables.Prometheus + Grafana —
serveexposes: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.ymlwires pg → exporter → Prometheus → Grafana.Docker —
Dockerfileruns 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" →
collecttwice,analyzeprints the verdict + dead/live + evidence.needs-rewritemeans pg_repack/VACUUM FULL; anything else tells you the cheaper fix first."postgres autovacuum not keeping up / dead tuples growing" →
vacuum-starved(threshold50 + 0.2×livefrom your livepg_settings) vsblocked-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), thenVACUUM table."postgres index bloat check / reindex needed" →
index-bloatedwith exactpgstatindex% — VACUUM cannot fix it, only REINDEX/pg_repack. Stalepg_statcounters are cross-checked againstpgstattuple_approxso 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 + optionalpgstattupleextension. 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
Available Tools
5 toolsbloat_blockersA
Who is pinning VACUUM: blocker pid + query + xmin age + slot.
Read-only on the local timeline file. Priority: prepared-xact > stale-slot
idle-in-transaction. Fix: end the named session, then VACUUM the table. Args: db: Path to the SQLite timeline file (default: BLOAT_DB env). Returns: {blockers: [{ts, kind, pid, age, query, slot}] — newest first, max 20. Use pid with SELECT pg_terminate_backend(pid) after verification.
Inputs: db.
bloat_checkA
Diagnose PostgreSQL bloat from a collected timeline.
Read-only: only reads the local SQLite timeline file, never touches Postgres. Args: db: Path to the SQLite timeline file (default: BLOAT_DB env, 'timeline.db'). Collect one first with bloat_collect, or pass any timeline.db path. Returns: findings: list of {table, verdict, evidence, action} — verdict is one of normal | vacuum-starved | blocked-by-idle-xact | blocked-by-slot | blocked-by-prepared | blocked-by-active-xact | needs-rewrite | index-bloated | index-unused. Evidence carries dead/live counts, pgstattuple approx %, pgstatindex %, blocker pid+query.
Inputs: db.
bloat_collectA
Take a read-only Postgres snapshot into the timeline, then return fresh verdicts.
Safe on prod: read-only transaction, 5s statement timeout, needs only pg_stat_* views + optional pgstattuple extension. No writes, no agents. Args: dsn: Postgres DSN, e.g. 'dbname=app user=readonly host=db.internal'. Default: BLOAT_DSN env. Never logs the password. Returns: {snapshot: 'saved', db: path, findings: [...with action per table/index]}.
Inputs: dsn.
bloat_live_diagnoseA
One-shot live diagnosis without touching your timeline file.
Snapshots into a throwaway temp DB, diagnoses, discards it. Read-only on Postgres (read-only tx, 5s timeout). Safest option for prod DSNs. Args: dsn: Postgres DSN (default: BLOAT_DSN env). Password never logged. Returns: {findings: [...with action], blockers: [...]} — same verdict set as bloat_check, plus the current xmin-holder evidence.
Inputs: dsn.
bloat_timelineA
Dead-tuple + approx + index-bloat time series: when the bloat started.
Read-only on the local timeline file. Args: table: Table name to filter (e.g. 'churn'). Empty = all tables. db: Path to the SQLite timeline file (default: BLOAT_DB env). Returns: {table: {dead: [{ts, live, dead}], approx_pct: [{ts, dead_pct}]}, index_bloat: {index: [{ts, bloat_pct}]}}. Rising dead = vacuum-starved; spike-then-zero = was blocked, blocker left.
Inputs: db, table.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 5 tools
The set separates live collection (bloat_collect) from one-shot live diagnosis (bloat_live_diagnose) and from timeline readers (bloat_check, bloat_blockers, bloat_timeline). bloat_collect and bloat_live_diagnose return similar findings but descriptions clearly state saving vs discarding the snapshot. bloat_check and bloat_blockers overlap since check already reports blockers, though blockers adds focused pid/slot detail.
All names use snake_case with a consistent 'bloat_' prefix, making the domain and tool family immediately obvious. The pattern is predictable and no naming ambiguity exists.
Five tools is well-scoped for a focused diagnostic server: collect snapshot, check current state, live one-shot diagnosis, identify blockers, and show timeline. Each tool earns its place without redundant or missing core operations.
Coverage is strong for read-only PostgreSQL bloat diagnosis: collection, live diagnosis, timeline analysis, blocker identification, and current verdicts. Minor gaps exist around timeline management (pruning, export, listing snapshots) and remediation, though read-only safety appears intentional.
Maintenance
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Review your AI agent's database changes on your schema, with plain-language risk, before they run.
Query your Postgres from ChatGPT or Claude without exposing the database or handing over credentials. Run npx boltschema connect next to your database and it dials out over HTTPS — no inbound firewall rule, no open port, works with localhost and VPC-private databases. Read-only is enforced by a SQL guard, a Postgres READ ONLY transaction, and a scoped role generated for you.
Related MCP Servers
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to inspect and monitor PostgreSQL databases with read-only DBA tools for performance analysis, bloat detection, locks/deadlocks, autovacuum, schema discovery, replication, and maintenance recommendations.-
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to perform PostgreSQL database operations, monitoring, and management via natural language, covering performance analysis, bloat detection, lock/deadlock monitoring, autovacuum and schema inspection with read-only production-safe queries.-
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to monitor and operate PostgreSQL databases through natural language queries, covering performance analysis, bloat detection, lock/deadlock monitoring, autovacuum and schema inspection, and maintenance recommendations with read-only, extension-optional workflows across PostgreSQL 12–18.-
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to inspect and monitor PostgreSQL 12-18 databases through natural language, covering performance analysis, slow-query identification, bloat and lock detection, autovacuum health, replication/WAL status, and schema exploration. All operations are read-only and work without requiring any extensions, though pg_stat_statements and pg_stat_monitor optionally enhance query analytics.-