PostgreSQL Autovacuum最適化：I/Oボトルネックの診断、Wraparound防止、本番環境チューニング

火曜日の午前3時、PagerDutyの緊急アラートで叩き起こされました。プライマリPostgreSQLインスタンスのディスクI/Oが100%に張り付き、読み取りレイテンシは4msから840msへと跳ね上がっていたのです。

わずか2分でアプリケーションのコネクションプールは枯渇し、APIは504 Gateway Timeoutを返し始めました。原因はユーザートラフィックの急増でも、インデックスの貼られていないマイグレーションの実行でもありませんでした。3つの200GBテーブルに対してautovacuumが一斉に激しいクリーンアップを開始したことで、AWS gp3ストレージボリュームのIOPSが枯渇し、クラスターが緊急のトランザクションID wraparound停止へと追い込まれかけていたのです。

PostgreSQLは追記型のマルチバージョン同時実行制御（MVCC）を採用しています。行データをインプレースで更新し変更内容をundoログに記録するMySQL InnoDBとは異なり、Postgresは元のレコードをそのまま残します。

完全に新しいバージョンのタプルをディスクに書き込み、古いバージョンには不要（dead）フラグを立てます。定期的なガベージコレクションが行われないと、不要タプル（dead tuple）は急速に蓄積していきます。テーブルは肥大化し、バッファキャッシュヒット率は99%から80%未満へと低下し、統計情報の不正確さによってクエリプランナは低速なシーケンシャルスキャンを選択するようになります。

Table of Contents

Autovacuum戦略：デフォルト vs グローバル一括設定 vs テーブル個別チューニング

vacuumによるストレージ枯渇に直面したエンジニアリングチームは、極端な対応に走りがちです。autovacuumを完全に無効化するか、クラスター全体の設定を最大スループットまで引き上げるかの二者択一です。しかし、どちらのやり方もうまくいきません。本番環境における代表的な3つのアプローチを比較してみましょう。

アプローチ1：デフォルトの標準設定

PostgreSQLのデフォルト値は、1GBのRAMとHDDを搭載した最小限の仮想マシンでも問題なく動作するよう、非常に保守的なパラメータに設定されています。例えば、autovacuum_vacuum_cost_limit = 200はバックグラウンドI/Oを厳しく制限しているため、vacuumのスループットは15〜20 MB/sを超えることが滅多にありません。最新のストレージ環境では、これが人工的なボトルネックを生み出してしまいます。

アプローチ2：グローバルでのアグレッシブなチューニング

テーブルの肥大化に頭を悩ませたエンジニアは、postgresql.conf全体の設定を無差別に引き上げてしまうことがよくあります。autovacuum_max_workersを8や10に増やし、autovacuum_vacuum_scale_factorを0.02まで削り、膨大なコスト制限を設定します。この設定では、わずか500行の静的な国コードマスタテーブルも、毎秒5,000回の書き込みが発生する2億行の決済台帳テーブルもまったく同じように扱われてしまいます。

アプローチ3：適切なベースライン設定＋対象テーブルごとの個別オーバーライド

このバランスの取れた設計パターンでは、ハードウェアスペックに応じた最新のクラスターベースラインを確立しつつ、書き込み負荷の高いテーブルに対して個別のストレージパラメータを付与します。更新頻度の低いマスタテーブルはバックグラウンドで静かにクリーンアップされる一方、高頻度で更新されるトランザクションテーブルは、不要タプルが蓄積する前に小さなバッチで頻繁かつ低負荷にvacuumされます。

トレードオフの比較・評価

デフォルトの標準設定

メリット： セットアップ時間が不要。低スペックの開発用VMや旧型HDDにおけるディスク帯域の飽和を防げる。
デメリット： 大規模データセットで破綻する。5,000万行のテーブルでは、デフォルトのスケールファクタ（20%）だと1,000万行の更新が行われるまでautovacuumが起動すらしない。その頃にはテーブルサイズがギガバイト単位で肥大化し、その後のクリーンアップ処理が何時間もディスク帯域を圧迫し続ける。

グローバルでのアグレッシブなチューニング

メリット： データベース全体の不要タプル数を極小に保てる。テーブルファイルサイズが倍増する前に肥大化を食い止めることができる。
デメリット： アクセス頻度の低いコールドテーブルにメモリとIOPSを浪費する。アグレッシブに設定されたワーカーが8プロセス同時に実行されると、低レイテンシが求められるOLTPトランザクションからストレージ帯域を奪い、ユーザー向けAPIの応答時間を直接悪化させる。

適切なベースライン設定＋テーブル個別オーバーライド

メリット： ディスクI/Oを安定させ、クエリキューの滞留を防ぎ、アプリケーション性能を損なうことなくトランザクションIDのフリーズ処理を実行できる。
デメリット： 各テーブルの更新頻度を継続的に監視する必要があり、個別パラメータを保守するための規律あるDDL管理が求められる。

本番ワークロード向け推奨設定

現代の本番クラスターは通常、NVMeストレージ、あるいはAWS gp3（3,000+ IOPS）やGCP Hyperdiskなどのプロビジョンドパフォーマンスを備えたクラウドボリューム上で稼働しています。このようなハードウェア環境では、エンジンのデフォルト設定はあまりにも制限が厳しすぎます。以下の設定は、8〜16コア、32GB〜64GBのRAMを搭載したデータベースサーバーに最適なベースラインです。

本番向けグローバルベースライン（postgresql.conf）

# 並行性とメモリ
autovacuum = on
autovacuum_max_workers = 4
maintenance_work_mem = 512MB

# コスト遅延とスロットリング（最新のNVMe/SSDストレージ向けに最適化）
autovacuum_vacuum_cost_delay = 2ms          # PG 12以降のデフォルト（旧バージョンでは20ms）
autovacuum_vacuum_cost_limit = 1000         # ワーカーがスリープするまでの処理量を5倍に緩和

# トリガー閾値
autovacuum_vacuum_scale_factor = 0.05       # 不要タプルが20%ではなく5%に達した時点でトリガー
autovacuum_vacuum_threshold = 1000          # 最低1,000行の変更が必要
autovacuum_analyze_scale_factor = 0.02      # 2%の更新でカタログ統計情報の更新をトリガー
autovacuum_analyze_threshold = 500

# Wraparound安全マージン
autovacuum_freeze_max_age = 200000000       # 2億トランザクションでスキップ不可のanti-wraparound vacuumを起動
vacuum_freeze_min_age = 50000000            # 5,000万トランザクションより古いタプルをフリーズ

これらのコストパラメータは、ワーカースレッドが一時停止（スリープ）する頻度を決定します。PostgreSQLでは、共有バッファ内のページをスキャンするのに1トークン、ディスクからの読み取りに10トークン（PG 14以降）、クリーンなページをダーティ化するのに20トークンのコストを消費します。コスト上限が200のデフォルト設定では、ワーカーは頻繁にスリープしてしまいます。制限を1000に引き上げることで、SSD環境でのクリーンアップスループットはおよそ80〜100 MB/sまで向上し、書き込みスパイクが発生する前にautovacuumの処理を完了させることができます。

ステップ別実践ガイド

ステップ1：テーブルの肥大化と不要タプルの診断

まずは、データベース内でどのテーブルが最も多くの不要タプルを抱えているかを特定します：

SELECT 
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_tuple_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE (n_live_tup + n_dead_tup) > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;

アクティブなテーブルでdead_tuple_ratioが恒常的に10〜15%を超えている場合、アプリケーションの書き込みペースに対してautovacuumの実行頻度が追いついていません。

ステップ2：リアルタイムのAutovacuum I/Oと進行状況の監視

ストレージのレイテンシが上昇した際は、実行中のワーカーがどのテーブルを処理しているかを確認します：

SELECT 
    pid,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    index_vacuum_count
FROM pg_stat_progress_vacuum;

ワーカーがvacuuming indexesフェーズで何時間も止まっている場合、インデックス構造が著しく肥大化しているか、maintenance_work_memの値が小さすぎます。メモリ割り当てが不足していると、ワーカーはインデックスページを複数回に分けて高負荷な走査を行わざるを得なくなります。

ステップ3：トランザクションID（TXID）Wraparoundの防止

PostgreSQLは各トランザクションに32ビットの識別子を割り当てており、約42億9,000万個の一意なIDを使用できます。エンジンは2^32を法とする剰余演算を行い、21億4,000万トランザクションを「過去」、21億4,000万トランザクションを「未来」として扱います。もしデータベースが古いレコードをフリーズせずに21億4,000万トランザクションを消費してしまうと、過去のトランザクションが未来のものと認識され、古いデータが不可視化してしまいます。データのサイレントな破損を防ぐため、トランザクションの経過数がこの限界に近づくと、PostgreSQLは強制シャットダウンに入り新規の書き込みを拒否します。

以下の診断クエリで残りの猶予を確認できます：

SELECT 
    datname,
    age(datfrozenxid) AS xid_age,
    2147483648 - age(datfrozenxid) AS tx_until_wraparound,
    ROUND((age(datfrozenxid)::numeric / 2147483648) * 100, 2) AS wraparound_percentage
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

xid_ageがautovacuum_freeze_max_age（デフォルトで2億）に達すると、PostgreSQLは通常のコストスロットリングを無視し、スキップ不可能なanti-wraparound vacuumを強制起動します。もしフリーズ処理されないまま5億トランザクションに近づいている場合は、オフピークのメンテナンスウィンドウに手動でvacuumをスケジュールしてください：

VACUUM FREEZE ANALYZE VERBOSE public.orders;

ステップ4：対象テーブルへの個別オーバーライド設定

例えば、毎秒3,000件のINSERTやUPDATEが発生するaudit_eventsテーブルを考えてみましょう。クラスター全体のグローバル設定を変更するのではなく、そのテーブル専用の閾値を設定します：

-- 書き込み頻度の高いテーブル向けに閾値を1%に下げ、スリープ遅延を無効化
ALTER TABLE audit_events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 500,
    autovacuum_vacuum_cost_limit = 2000,
    autovacuum_vacuum_cost_delay = 0
);

-- 適用されたパラメータを確認
SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'audit_events';

ステップ5：ダウンタイムなしで設定を安全に反映する

autovacuumの設定変更は、データベース接続を切断したりサービスを再起動したりすることなくリロードできます：

# PostgreSQLに設定ファイルの再読み込みシグナルを送信
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# またはsystemdデーモンを直接リロード
sudo systemctl reload postgresql

その後48時間は、iostat -xz 1でディスクメトリクスを監視してください。ベースラインのワーカー制限を最適化し、更新頻度の高いテーブルを専用のスケールファクタで分離することで、ディスクI/Oの急激なスパイクは解消され、予測可能で安定したバックグラウンド処理へと変わるはずです。