PG Bloat Detective

io.github.r69shabhv0.1.3更新于 Oct 7, 2026

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

已验证STDIO仅桌面Databases Security & Monitoring Data & Analytics

概览

AI 生成的概览

只读的 Postgres 膨胀诊断：表与索引判定、阻塞者定位、时间线以及 REINDEX 前后对比证据。

功能: 通过五个只读工具访问 Postgres 数据库。bloat_check 为每张表或每个索引返回判定、证据与建议操作；bloat_collect 采集时间线快照并返回最新判定；bloat_timeline 展示死元组与索引膨胀的时间序列；bloat_blockers 指出导致 vacuum 饥饿的进程号、查询、xmin 年龄与复制槽；bloat_live_diagnose 使用一次性临时快照，不触碰已保存的时间线文件。判定可区分 vacuum 饥饿、被空闲事务阻塞、需要重写、索引膨胀与索引未使用等状态。
适用场景: 适合排查 Postgres 实例上的表或索引膨胀、判断该用 VACUUM 还是 REINDEX，以及找出是哪条长事务或复制槽阻碍了清理。最适用于对生产数据库做只读诊断。
运行要求: 以 PyPI 包（pg-bloat-detective）形式通过 stdio 在本地运行，需要 Python 与 psycopg。需要 BLOAT_DSN 中的 Postgres 连接串，以及 BLOAT_DB 指向由 collect 命令生成的 SQLite 时间线文件。未声明认证方式，数据库凭据包含在连接串中。

安装前请注意

BLOAT_DSN 中的 Postgres 连接串属于凭据，应限定为只读角色；服务端会强制 5 秒语句超时与只读事务，但账号自身权限仍然生效。索引膨胀测量会对索引执行 pgstatindex，在大索引上产生 I/O 开销，虽有大小上限保护但可被覆盖。这些工具只做读取，但 README 的快速开始还包含单独运行 VACUUM 与 REINDEX 的命令，会修改数据。

安装

在 SourceWeft 中

打开控制台中的 PG Bloat Detective，将其添加到工作区。
为需要使用其工具的对话启用该服务。

Desktop only，通过 STDIO。 STDIO 服务会启动本地进程，因此需要 SourceWeft 桌面宿主。

其他 MCP 客户端

参照仓库中的启动说明。

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

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:

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.

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

{"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: mcp.so + PulseMCP accept GitHub submissions (server.json + README badge) — repo is ready, submit the URL after push.

来源：README.md，提交 4e704af

工具

工具元数据尚未被收录。

版本历史

v0.1.3最新Oct 7, 2026