PG Bloat Detective

io.github.r69shabhv0.1.3Updated Oct 7, 2026

Read-only Postgres bloat verdicts: blockers, timelines, REINDEX proof.

VerifiedSTDIODesktop onlyDatabasesData & AnalyticsSecurity & Monitoring

Overview

AI-generated overview

Read-only Postgres bloat diagnosis: table and index verdicts, blocker identification, timelines, and REINDEX proof.

What it does
Exposes five read-only tools over a Postgres database. bloat_check returns a verdict, evidence, and suggested fix per table or index; bloat_collect snapshots a timeline and returns fresh verdicts; bloat_timeline shows dead-tuple and index-bloat series; bloat_blockers names the pid, query, xmin age, and slot behind vacuum starvation; bloat_live_diagnose takes a one-shot throwaway snapshot so the stored timeline file is untouched. Verdicts distinguish vacuum-starved, blocked-by-idle-xact, needs-rewrite, index-bloated, and index-unused states.
When to use it
Useful when investigating table or index bloat on a Postgres instance, deciding whether VACUUM or REINDEX is the right fix, or finding which long-running transaction or replication slot is blocking cleanup. Best suited to read-only diagnosis of production databases.
Requirements
Runs locally over stdio as a PyPI package (pg-bloat-detective) needing Python and psycopg. Requires a Postgres DSN in BLOAT_DSN and a SQLite timeline file path in BLOAT_DB, produced by the collect command. No authentication is declared; the DSN carries the database credentials.
Before you install
The Postgres DSN in BLOAT_DSN is a credential and should be scoped to a read-only role; the server enforces a 5s statement timeout and read-only transactions, but the account's own privileges still apply. Index bloat measurement runs pgstatindex on indexes, which costs I/O on large indexes and is guarded by a size limit that can be overridden. The tools only read, but the README's quickstart also shows separate commands that run VACUUM and REINDEX, which change data.

Installation

In SourceWeft

  1. Open PG Bloat Detective in the dashboard and add it to a workspace.
  2. Enable the server for the chats that should use its tools.

Desktop only via STDIO. STDIO servers start a local process, so they need the SourceWeft desktop host.

Other MCP clients

Follow the launch instructions in the repository.

README

pg-bloat-detective

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

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

Quickstart

bash
docker compose up -dpip 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 failurepython -m bloatdetective collect --db timeline.dbpython -m bloatdetective report --db timeline.db --html --out report.htmlpython -m bloatdetective serve --db timeline.db &   # :9187/metrics for Prometheuspytestbash 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:

momentpg_stat deadapprox dead%index bloat% (density)verdict
before fix5,108,201— (single-snapshot lag)41.9%vacuum-starved + index-bloated churn_pkey
after fix00.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.

bash
pip install -e .   # pulls mcp + psycopgbloat-mcp          # stdio transport

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

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"}}}}
toolwhat the agent gets
bloat_checkverdicts + evidence + action per table/index
bloat_collectsnapshot timeline, return fresh verdicts
bloat_timelinedead/approx/index-bloat series — when it started
bloat_blockerspid + query + xmin age + slot — who to kill
bloat_live_diagnoseone-shot throwaway snapshot, diagnose, discard

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

Source: README.md at commit 4e704af

Tools

0
Tool metadata has not been indexed yet.

Version history

1
  1. v0.1.3LatestOct 7, 2026