PostgreSQL AUTOVACUUMの調整とテーブルBloat（肥大化）の防止

PostgreSQL Database Performance DevOps
結論

大型テーブルには autovacuum_vacuum_scale_factor を小さく（例: 0.05）個別設定して肥大化を防ぎます。

-- PostgreSQL公式仕様：大規模テーブルに対する AUTOVACUUM 個別チューニング
ALTER TABLE large_orders SET (
  autovacuum_vacuum_scale_factor = 0.05, -- レコードの 5% 変更時点で発火
  autovacuum_vacuum_threshold = 1000      -- 基本閾値
);

PostgreSQL公式仕様：AUTOVACUUM 発火の閾値計算

PostgreSQL公式ドキュメント（postgresql.org/docs/current/routine-vacuuming.html）における、自動バキューム（AUTOVACUUM）のトリガー判定公式です。

発火閾値 = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × 全レコード数)
  • デフォルト値: threshold = 50, scale_factor = 0.2 (20%)

実際に起こる障害：大規模テーブルでの Bloat 肥大化と本番全ロック事故

障害1: デフォルト 20% 設定による巨大テーブルの放置

例えば 1000 万件のレコードを持つテーブルの場合、デフォルト設定では 200 万件の死にタプル（更新・削除領域）が溜まるまで AUTOVACUUM が一切起動しません。

その結果、無効な領域が数GB単位でテーブル内に残り続ける「Bloat（肥大化）」が発生し、ディスク容量が枯渇する障害が発生します。

障害2: あせって実行した VACUUM FULL によるテーブル全停止

Bloat を解消しようと VACUUM FULL コマンドを手動実行すると、対象テーブルに最高強度の AccessExclusiveLock（アクセス全排他ロック） がかかります。

これにより、処理が完了するまでの数十分〜数時間、対象テーブルに対するあらゆる SELECT / INSERT / UPDATE が完全ブロックされ、本番Webサイトが全停止する大障害 が発生します。

設定・解消手順

  1. pg_stat_user_tables ビューで n_dead_tup（死にタプルの数）が多いテーブルを監視・特定する
  2. レコード数が100万件を超える大規模テーブルに対して、ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01 ~ 0.05) を設定し、こまめにクリーンアップさせる
  3. 既に巨大化したテーブルの領域回収には、本番アクセスを止めないオープンソースツール pg_repack を使用する