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