PostgreSQL肥大化対策の実務：VACUUM/Autovacuum/Index再編成を止めずに回す運用プレイブック

PostgreSQL を長期運用すると、遅かれ早かれぶつかるのが bloat（テーブル/インデックス肥大化）です。CPU やメモリを増やしても、実体は不要領域の蓄積なので、根本原因を処理しない限り性能は戻りません。

本記事では、サービス停止なしで bloat を抑える運用を目標に、Autovacuum 設計、監視、メンテ手順を実践ベースで解説します。

1. なぜ肥大化が起きるのか

PostgreSQL は MVCC を採用しているため、UPDATE/DELETE で古い行バージョンが即時削除されません。不要バージョンは VACUUM で回収されますが、追いつかないと肥大化します。

肥大化が進むと以下が起こります。

  • 同じデータ量でも I/O が増える
  • インデックス探索が遅くなる
  • キャッシュ効率が落ち、p95 レイテンシが悪化
  • 自動メンテの時間がさらに伸びる（悪循環）

重要なのは、「遅くなってから対処」だと回復コストが高いという点です。

2. 最初に見るべき指標

運用でまず可視化するのは次の4つです。

  1. n_dead_tup（死んだタプル数）
  2. last_autovacuum（最後に vacuum が走った時刻）
  3. テーブルサイズ・インデックスサイズ推移
  4. age(relfrozenxid)（XID 消費進行）

確認クエリ例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

XID の健全性チェック:

1
2
3
4
5

SELECT
  datname,
  age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

xid_age が高いのに vacuum が遅れている場合は、緊急度が高いです。

3. Autovacuum の基本パラメータ設計

デフォルト設定は小規模環境向けで、更新量が多い本番には不足しやすいです。まずは「全体設定 + ホットテーブル個別設定」に分けて調整します。

代表的パラメータ:

  • autovacuum_max_workers
  • autovacuum_naptime
  • autovacuum_vacuum_cost_limit
  • autovacuum_vacuum_scale_factor
  • autovacuum_vacuum_threshold

考え方:

  • 更新頻度が高いテーブルは scale_factor を下げる（例: 0.2 → 0.02）
  • 小さなテーブルは threshold 主体、大きなテーブルは scale factor 主体
  • まず vacuum が「間に合う」状態を作る

個別テーブル例:

1
2
3
4
5
6

ALTER TABLE events
  SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.02
  );

4. インデックス肥大化の見落としに注意

テーブル側だけ見ていて、実際のボトルネックがインデックス側というケースは非常に多いです。特に更新頻度の高い B-Tree インデックスで顕著です。

実務では次を定期確認します。

  • 使用頻度が低い巨大インデックス
  • 重複インデックス
  • インデックスサイズ増加率（週次）

重複候補を探す SQL（簡易）:

1
2
3
4
5
6

SELECT
  indexrelid::regclass AS index_name,
  indrelid::regclass AS table_name,
  pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indisvalid = true;

実際は pg_stat_user_indexes と組み合わせ、idx_scan がほぼゼロのものを優先削減します。

5. REINDEX と pg_repack の使い分け

肥大化したインデックスを戻すには REINDEX が基本ですが、ロック影響を避けたい場合は REINDEX CONCURRENTLY を選びます。

  • 影響小で安全重視: REINDEX INDEX CONCURRENTLY
  • まとめて再編成: pg_repack（導入・権限管理が必要）

例:

1

REINDEX INDEX CONCURRENTLY idx_orders_created_at;

注意点:

  • ディスク空き容量を事前確認（再構築時に追加領域が必要）
  • 長時間トランザクションがあると完了しない
  • 実行ウィンドウを決め、監視を付ける

6. vacuum が進まない時の切り分け

「Autovacuum が動いているのに改善しない」時は、次の順で確認します。

  1. 長時間トランザクションが残っていないか
  2. レプリカ遅延や hot_standby_feedback で cleanup が妨げられていないか
  3. I/O 飽和で vacuum が極端に遅くなっていないか
  4. freeze 対象の backlog が巨大化していないか

長時間トランザクション確認:

1
2
3
4
5

SELECT pid, usename, state, xact_start, now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;

tx_age が長い接続は、vacuum の前進を阻害する最優先要因です。

7. 実運用で効くスケジュール設計

本番では「毎晩まとめて重い処理」より、小さく高頻度に回す方が安定します。

  • 日中: autovacuum でこまめに回収
  • 深夜: 重いテーブルの VACUUM (ANALYZE) を計画実行
  • 週次: 重要インデックスの肥大化確認
  • 月次: 上位肥大化テーブルの再編成計画レビュー

ANALYZE を外すとプランが古くなるため、統計更新を一体運用にします。

8. 典型インシデントと復旧手順

ケースA: API レイテンシ急上昇

兆候:

  • CPU は高くないがクエリ時間が増加
  • 特定テーブルの n_dead_tup が急増

対処:

  1. 長時間トランザクションを特定
  2. 対象テーブルに VACUUM (VERBOSE, ANALYZE)
  3. 重度ならインデックス再構築を計画

ケースB: ストレージ逼迫

兆候:

  • disk 使用率が短期間で増加
  • UPDATE 多発テーブルが存在

対処:

  1. サイズ上位テーブル・インデックスを抽出
  2. 不要インデックス削除
  3. REINDEX CONCURRENTLY / pg_repack を段階実行

ケースC: wraparound 警告

兆候:

  • autovacuum: preventing wraparound ログ

対処:

  1. 緊急度を最優先に切替
  2. 長時間 TX を停止
  3. freeze 対象テーブルを優先 vacuum

9. 導入時チェックリスト

  • 上位更新テーブルに個別 autovacuum パラメータがある
  • n_dead_tup と last_autovacuum を監視している
  • 長時間トランザクションのアラートがある
  • インデックス使用率 (idx_scan) を定期レビューしている
  • REINDEX 実行時の空き容量基準を定義している
  • wraparound 対応 runbook がある

10. 30日改善プラン（最短で効果を出す）

Week 1

  • 現状計測（dead tuple、サイズ、xid age）
  • ホットテーブル上位10件を特定

Week 2

  • テーブルごとに autovacuum 個別設定
  • 長時間 TX 監視アラート導入

Week 3

  • 低利用/重複インデックス整理
  • 対象インデックスを REINDEX CONCURRENTLY

Week 4

  • 実行後の p95 クエリ時間、ストレージ増加率を比較
  • 設定の再チューニングと runbook 更新

PostgreSQL の肥大化対策は、一発のメンテで終わる作業ではありません。観測 → 個別設定 → 段階的再編成 → 監視改善を繰り返すことで、停止なしでも安定して性能を維持できます。