PostgreSQL 実践講座 #5 VACUUM と autovacuum — dead tuple 掃除の原理とチューニング

基礎第 6 回で予告した請求書が届きました。MVCC は UPDATE・DELETE を「新バージョンの追加 + 旧バージョンへの印」で処理するので、印を付けられた旧バージョン(dead tuple)が積もり続けます。これを片付けるのが VACUUM で、PostgreSQL の運用知識でただ 1 つの中心を挙げるならこれです。「チューニングしていない autovacuum + 更新の多い大きいテーブル」の組み合わせは、運用事故リストの最上位の常連です。

VACUUM の 3 つの仕事 #

• 空間の再利用マーク: dead tuple が占めていた空間に「もう書いていい」と印を付けます。注意すべきは、ファイルが縮むのではなく再利用可能になるだけという点です。すでに膨らんだテーブルのサイズはそのままです。
• 可視性マップの更新: 「このブロックの行は全部、誰からも見える」を記録します。前回の Index Only Scan が成立する条件が、まさにこのマップの新しさでした。
• トランザクション ID 周回の防止: MVCC のバージョン判別に使うトランザクション ID は有限なので、定期的に「凍結」処理が必要です。これが滞ると、最悪の場合 DB が書き込みを拒否する事態まで行きます。autovacuum を切ってはいけない決定的な理由です。

これに加えて統計の収集(ANALYZE)も autovacuum が一緒に回します。基礎第 5 回の「予想 rows がずれたら ANALYZE」が普段は自動で回っている理由です。

autovacuum のしきい値: 大きいテーブルほど遅れます #

autovacuum はテーブルごとに「dead tuple がしきい値を超えたら」目を覚まします。しきい値の既定値が問題の種です。

しきい値 = autovacuum_vacuum_threshold(50) + autovacuum_vacuum_scale_factor(0.2) × 行数

行数の 20% という比率のしきい値は、小さいテーブルには適当ですが、1 億行のテーブルなら dead tuple が 2000 万個積もってやっと掃除が始まるという意味です。その間、テーブルとインデックスは膨らみ、スキャンは死んだ行の上を泳ぐことになります。大きいテーブルほど掃除が遅れる構造なので、更新の多い大きいテーブルにはテーブル単位でしきい値を下げてやります。

-- このテーブルは 1% 積もったら掃除を開始
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

「頻繁に、少しずつ」が方向です。掃除の周期が短いほど 1 回の掃除が軽く、ブロートが育つ隙がありません。ちなみに PostgreSQL はバージョンごとに VACUUM 自体を着実に速くしてきましたが(17 のメモリ構造の改善、18 の非同期 I/O など)、それでも「いつ目を覚ますか」は上のしきい値が決めるので、このチューニングの価値は変わりません。

掃除を妨げる犯人: 長時間トランザクション #

VACUUM は回っているのに dead tuple が減らない日が来ます。原理を知れば当然です。どこかのトランザクションがまだ見えるバージョンは、消せません。 数時間もののスナップショットを握ったトランザクションが 1 つあると、その時点以降のすべての dead tuple が、全テーブルで保存されます。実践第 3 回で idle in transaction を警戒しろと言った理由がこれです。診断はあのときのルーチンそのままに pg_stat_activity で一番古いトランザクションを探すことで、予防は idle_in_transaction_session_timeout の設定と「トランザクションの中で外部を待たない」というコードの規律です。

肥大化の確認と後始末 #

現状の把握は統計ビューから始めます。

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

n_dead_tup が n_live_tup に比べて異常に大きい、あるいは last_autovacuum がはるか昔なら、上の 2 つの原因(しきい値、長時間トランザクション)を順に疑います。すでに大きく膨らんだテーブルの空間を実際に回収するには、テーブルを書き直すしかないのですが、標準コマンドの VACUUM FULL は作業の間ずっとテーブル全体を排他ロックするので、稼働中のサービスでは事実上使えません。実務ではロックなしでオンラインに書き直す pg_repack のような拡張を使うか、メンテナンス時間に計画して回します。最善はもちろんここまで来ないこと、つまり上の予防です。

まとめ #

• VACUUM は空間の再利用マーク、可視性マップの更新、トランザクション ID の凍結の 3 つの仕事をします。autovacuum は切るものではなく、チューニングするものです。
• 既定のしきい値(20%)は大きいテーブルほど掃除を遅らせます。更新の多い大きいテーブルは scale_factor をテーブル単位で下げます。
• 方向は「頻繁に、少しずつ」です。軽い掃除が頻繁なほうが、重い掃除がまれなのより良いです。
• 長時間トランザクションは全テーブルの掃除を妨げます。idle in transaction のタイムアウトとコードの規律で予防します。
• VACUUM FULL は排他ロックなので稼働中は禁物です。次回はこのロックの話の本体、ロックと同時実行です。