PG Bloat Detective

pg-bloat-detective — PostgreSQL の肥大化チェック、VACUUM ブロッカーの特定、インデックス肥大化の検出

Postgres データベースが肥大化している原因、VACUUM をブロックしている対象、REINDEX が必要なインデックスを、証拠付きで読み取り専用のまま特定します。 vacuum-starved · blocked-by-idle-xact · needs-rewrite · index-bloated · index-unused — テーブル／インデックスごとに、PID とクエリの証拠付きで判定を 1 つ表示します。

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

pip install pg-bloat-detective · Docker 対応 · Claude/Cursor 用 MCP サーバー · Prometheus + Grafana · Next.js ダッシュボード

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 / Cursor

概要

他のツールでは答えがわからない、次の 3 つの疑問に答える PostgreSQL 肥大化分析ツールです。

1. このテーブルは肥大化しているか。その原因は何か。 — デッドタプルの推移（pg_stat_user_tables）と、pgstattuple_approx による正確な確認。判定：normal（そのままにする）、vacuum-starved（autovacuum が追いついていない）、blocked-by-idle-xact|slot|prepared|active-xact（backend_xmin を保持する PID とクエリを特定）、needs-rewrite（概算のデッドタプル率が 30% 超で、VACUUM では回収できない）。

2. VACUUM／autovacuum をブロックしているのは誰か。 — 各スナップショットで最も古い backend_xmin の保持者を追跡します。PID、トランザクション経過時間、クエリテキスト、古いレプリケーションスロット、準備済みトランザクションを確認できます。優先順位は、prepared-xact > stale-slot > idle-in-transaction です。対象を 1 つ終了して VACUUM を実行すれば完了です。

3. インデックスは肥大化しているか。（VACUUM では解決できません） — 1GB のコスト上限（--max-bytes で調整、--allow-large で上書き）を適用し、インデックスに対して正確な pgstatindex を実行します。指標は 100 − avg_leaf_density です。30% 超なら index-bloated となり、REINDEX をスケジュールします。すべてのスナップショットでスキャン数がゼロなら index-unused となり、DROP 候補です。これは pganalyze も認める未解決の課題で、当ツールの差別化要素です。実環境で、VACUUM では何も変わらない一方、REINDEX 後に 83.7% から 9.9% に改善することを確認しています。

SQLite ファイル 1 つで全期間の推移を記録します。静的ページ 1 枚でインシデント全体を把握できます。本番環境にエージェントを配置せず、書き込みも行いません（読み取り専用トランザクション、ステートメントタイムアウト 5 秒）。RDS／Neon／Supabase／セルフホストの PG 12～17 で動作します。

クイックスタート（60 秒デモ）

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.md

判定結果（指示として読み取ってください）

ヒープ：normal（安定状態。そのままにします）· vacuum-starved → VACUUM を実行／autovacuum を調整 · blocked-by-idle-xact|slot|prepared|active-xact → 指定されたセッションを終了してから VACUUM を実行 · needs-rewrite → pg_repack／VACUUM FULL。 インデックス：index-bloated（pgstatindex による肥大化率が 30% 超 → REINDEX が必要。VACUUM では修正できません）· index-unused（全スナップショットで idx_scan=0 → DROP 候補。まず pg_stat_statements で確認してください）。

インデックスの肥大化検出が差別化要素です（pganalyze も未解決の課題を認めています）。collect は、1GB のコスト上限（--max-bytes で調整、--allow-large で上書き）を下回るインデックスに限り、正確な pgstatindex を実行します。指標は 100 − avg_leaf_density です。pgbloat_index_bloat_pct／pgbloat_index_scans としてエクスポートされ、shadcn ダッシュボードにグラフ表示されます。 Grafana でもグラフ表示できます。

ベンチマーク（2026-10-05 にローカル PG16 で測定 — ヒープ＋インデックス、直近の実行結果）

30 秒間の負荷をかけ、ブロッカーなし（autovacuum が単に追いついていない状態）で、VACUUM ANALYZE と REINDEX を実行：

時点	pg_stat のデッド数	概算デッド率	インデックス肥大化率（密度）	判定
修正前	5,108,201	—（単一スナップショットの遅延）	41.9%	vacuum-starved + index-bloated churn_pkey
修正後	0	0.0%	9.9%	normal — 安定状態。そのままにします

以前の見落としは解消済みです。肥大化率 83.7%（密度 16.3）のインデックスは、REINDEX 後に 9.9%（密度 90.1）まで低下しました。VACUUM だけでは決して解消できません。全ログは REPORT.md、視覚的なレポートは report.html をご覧ください。

詳細な 3 週間の計画と競合ツールとのギャップについては、PLAN.md をご覧ください。

MCP サーバー（Claude Desktop／Cursor／任意の MCP クライアント）

Postgres を読み取り専用で操作する 5 つのツールです（ステートメントタイムアウト 5 秒、読み取り専用トランザクション）。bloat_check はテーブル／インデックスごとに判定・根拠・修正方法を返します。bloat_live_diagnoseは使い捨ての DB のスナップショットを取得するため、タイムラインファイルには一切触れません。これにより、本番環境の DSN でも最も安全に利用できます。

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

ツール	エージェントが得られる情報
bloat_check	テーブル／インデックスごとの判定・根拠・アクション
bloat_collect	タイムラインのスナップショットを取得し、新しい判定を返す
bloat_timeline	デッドタプル／概算値／インデックス肥大の推移 — 発生時期
bloat_blockers	PID + クエリ + xmin の経過時間 + スロット — 終了させる対象
bloat_live_diagnose	使い捨てスナップショットを 1 回取得し、診断して破棄

公開リスト：公式 MCP Registry（io.github.r69shabh/pg-bloat-detective） · mcp.so · awesome-mcp-servers（データベース）。

インターフェース

• CLI — collect | analyze | report [--html|--json] | serve [--port]（この README のクイックスタート）。
• ダッシュボード — dashboard/ にある Next.js の単一インシデントカード（report --json が data.json にデータを渡します）：判定バナー、デッドタプルとインデックス肥大のグラフ、ブロッカーと検出結果のテーブル。
• Prometheus + Grafana — serve は :9187/metrics を公開します（pgbloat_dead_tuples、pgbloat_approx_dead_pct、pgbloat_index_bloat_pct、pgbloat_index_scans、pgbloat_xmin_holder_age_seconds）。grafana/ にはダッシュボード JSON とプロビジョニングがあり、docker-compose.yml は pg → exporter → Prometheus → Grafana を接続します。
• Docker — Dockerfile は stdio 経由で MCP サーバーを実行します（docker run -i）。compose にはデモ用 DB とオブザーバビリティスタックが追加されています。

FAQ（実際によく検索される内容）

• 「postgres のテーブル肥大を確認する方法」 → collect を 2 回実行し、analyze で判定 + dead/live + 根拠を表示します。needs-rewrite は pg_repack/VACUUM FULL が必要という意味です。それ以外の場合は、まずより低コストな修正方法が示されます。
• 「postgres の autovacuum が追いつかない／デッドタプルが増えている」 → vacuum-starved（現在の pg_settings の値に基づくしきい値 50 + 0.2×live）と、blocked-by-*（セッションが xmin を保持している状態）を確認します。タイムラインのグラフで、デッドタプルが増加中（解消できていない）なのか、急増後にゼロになった（ブロック後に解放された）のかを確認できます。
• 「postgres の VACUUM が idle in transaction によってブロックされる」 → PID、クエリ、xmin の経過時間を特定します。SELECT pg_terminate_backend(pid) を実行する（または処理が終わるのを待つ）と、その後 VACUUM table を実行できます。
• 「postgres のインデックス肥大を確認／REINDEX が必要か」 → index-bloated には正確な pgstatindex の % が表示されます。VACUUM では修正できず、REINDEX/pg_repack のみが有効です。古い pg_stat カウンターは pgstattuple_approx と照合するため、誤った情報に振り回されません。
• 「postgres の未使用インデックスを見つけて削除する」 → すべてのスナップショットで idx_scan=0 だった index-unused が、サイズをバイト単位で表示します。pg_stat_statements で確認してから、DROP を実行してください。
• 「rds／neon／supabase の肥大」 → 設計上読み取り専用です。必要なのは pg_stat_* ビューと、任意で pgstattuple 拡張機能のみです。スーパーユーザー権限も、エージェントのインストールも必要ありません。
• 「pg_bloat_check と pgstattuple と pganalyze と pghero の比較」 → 1 回限りの推定ツールでは % が得られます。このオープンソースツールでは、ヒープとインデックスの両方について、タイムライン + ブロッカー + 修正方法 + 修正前後の証拠をまとめて確認できます。PLAN.md の競合製品比較表をご覧ください。

キーワード

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