【PostgreSQL】VACUUMとオートバキュームの仕組みを理解して肥大化を防ぐ

PostgreSQL
2023.01.26

こんにちは、かつコーチです。

「テーブルのデータ量は変わっていないのに、ディスク使用量だけがどんどん増えていく」。

PostgreSQLを本番運用していると、こういう現象に出くわすことがあります。

原因の多くは、VACUUM（不要になった行を回収してテーブルを整理する仕組み）が追いついていないことです。

この記事では、PostgreSQLのMVCC（多version同時実行制御）とVACUUMの関係、オートバキュームの動作条件、そしてチューニングのポイントを整理します。

上級者向けの内容なので、テーブル・インデックスの基本操作は理解している前提で進めます。

VACUUMが必要になる理由

MVCCとデッドタプルの発生

PostgreSQLは、MVCC（Multi-Version Concurrency Control）という方式で同時実行制御を行っています。

行をUPDATEすると、既存の行を書き換えるのではなく「新しいバージョンの行を追加し、古い行は無効マークを付ける」という動作をします。

DELETEも同様で、物理的にすぐ削除されるわけではなく、無効マークが付くだけです。

この「無効マークが付いた古い行」をデッドタプルと呼びます。

-- 100万件のUPDATEを実行すると、100万件分のデッドタプルが発生する
UPDATE orders SET status = 'shipped' WHERE status = 'processing';

デッドタプルはディスク上に残り続けるため、UPDATEやDELETEを繰り返すテーブルほどファイルサイズが肥大化していきます。

VACUUMが担う2つの役割

VACUUMには、大きく分けて2つの役割があります。

  1. デッドタプルの回収：デッドタプルの領域を「再利用可能」としてマークし、後続のINSERT/UPDATEで使い回せるようにする
  2. トランザクションIDの周回防止：PostgreSQL内部のトランザクションID（32bit）が上限に達して周回する事故を防ぐ

特に2つ目は見落とされがちですが、トランザクションID周回（XID Wraparound）が起きるとデータベース全体が読み取り専用になる重大インシデントにつながります。

# XID周回が近づくと、こういう警告がログに出る
WARNING:  database "app_production" must be vacuumed within 344212401 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

筆者は以前、長時間起動しっぱなしの検証環境でこの警告を見落とし、休日にアラートが鳴って慌ててVACUUM FREEZEを手動実行したことがあります。

放置していれば強制的に書き込み停止になっていたはずで、監視の重要性を痛感した出来事でした。

オートバキュームの動作条件

autovacuumが起動するタイミング

PostgreSQLは、デフォルトでautovacuumという自動実行の仕組みを持っています。

autovacuumは、テーブルごとに次の条件を満たすと自動でVACUUMを実行します。

更新・削除された行数 > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × テーブルの総行数

デフォルト値は、autovacuum_vacuum_threshold = 50、autovacuum_vacuum_scale_factor = 0.2です。

つまり、1万行のテーブルなら「50 + 0.2 × 10,000 = 2,050行」の更新・削除があった時点でVACUUMが走ります。

大規模テーブル（例：1億行）では、2,000万行以上の更新がないとVACUUMが走らない計算になり、これが「大テーブルほど肥大化しやすい」原因の一つです。

テーブル単位でパラメータを調整する

大規模テーブルや更新頻度が高いテーブルには、テーブル単位で個別に閾値を調整するのが実践的です。

-- 大規模テーブルは scale_factor を下げて、より早くVACUUMが走るようにする
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_cost_limit = 2000
);

autovacuum_vacuum_cost_limitは、VACUUMが1回の実行でどれだけI/Oリソースを使えるかの上限値です。

デフォルトのままだと大テーブルのVACUUMに時間がかかりすぎるため、リソースに余裕がある環境では引き上げると処理が早く終わります。

現在のデッドタプル量を確認する

pg_stat_user_tablesビューで、テーブルごとのデッドタプル数とautovacuum実行履歴を確認できます。

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

n_dead_tupがn_live_tupに対して大きい割合を占めているテーブルは、VACUUM設定を見直す候補です。

つまずきやすいポイント・トラブル対処

❌Before：VACUUM FULLを本番中に実行してしまう

膨れ上がったテーブルを見て「とにかくVACUUM FULLで縮めよう」と考えるのは危険です。

-- ❌ 本番稼働中にこれを実行すると、テーブルがロックされて障害になる
VACUUM FULL orders;

VACUUM FULLはテーブル全体を作り直すため、実行中は排他ロックがかかり、対象テーブルへの読み書きが一切できなくなります。

数百万行のテーブルであれば、処理に数十分かかることも珍しくありません。

✅After：pg_repackなど別手段を検討する

本番稼働中にテーブルを縮小したい場合は、VACUUM FULLではなくpg_repack拡張の利用を検討します。

# pg_repackは元テーブルをロックせずにテーブルを再構築する
pg_repack -d app_production -t orders

pg_repackは新しいテーブルをバックグラウンドで作り直し、最後に短時間のロックだけで切り替えるため、サービス影響を最小限に抑えられます。

どうしてもVACUUM FULLを使う必要がある場合は、必ずメンテナンスウィンドウ（アクセスが少ない深夜帯など）を確保してから実行してください。

❌Before：autovacuumを無効化してしまう

「VACUUMが重いから」という理由で、autovacuum = offにしてしまうケースも見かけます。

# postgresql.conf
# ❌ 一時しのぎでも危険な設定
autovacuum = off

これは前述のXID周回リスクを自ら引き受けることになり、根本的な解決になりません。

✅After：問題のテーブルだけ個別チューニングする

全体を止めるのではなく、負荷が高いテーブルのコストパラメータだけを調整して、実行タイミングを分散させるのが正しいアプローチです。

-- 特定テーブルだけコストを抑えて、頻繁に軽く実行させる
ALTER TABLE access_logs SET (
    autovacuum_vacuum_cost_delay = 10
);

autovacuum_vacuum_cost_delayを小さくすると、VACUUMの1サイクルあたりの休止時間が短くなり、より頻繁に少しずつ処理が進むようになります。

応用：VACUUMの状況を継続的に監視する

pg_stat_activityでVACUUM実行中を確認する

現在実行中のVACUUM処理は、pg_stat_activityから確認できます。

SELECT
    pid,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%'
  AND state != 'idle';

長時間実行されているVACUUMがないか、定期的にチェックする運用にしておくと、パラメータ調整のタイミングを判断しやすくなります。

監視ツールとの連携

zabbixやDatadogなどの監視ツールを使っている場合は、n_dead_tupの割合やage(datfrozenxid)（XID周回までの残り距離）をメトリクス化しておくと、事前にアラートを設定できます。

-- データベースごとのXID周回までの残り距離を確認
SELECT
    datname,
    age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

この値がautovacuum_freeze_max_age（デフォルト2億）に近づいてきたら、警告として扱うのが実践的です。

まとめ

この記事のポイント

  • PostgreSQLはMVCCの仕組み上、UPDATE/DELETEのたびにデッドタプルが発生し、VACUUMで回収する必要がある
  • VACUUMにはデッドタプル回収に加えて、XID周回を防ぐという重要な役割もある
  • autovacuumのデフォルト設定は大規模テーブルに合わない場合が多く、テーブル単位のチューニングが有効
  • VACUUM FULLは排他ロックがかかるため本番中の実行は避け、pg_repackなど代替手段を検討する

次に読むべき記事

  • pg_dumpでバックアップ・リストアする方法
  • EXPLAIN ANALYZEで実行計画を読む
  • PostgreSQLのインデックスの種類（B-tree・GiST・GIN・BRIN）

タグ: PostgreSQL, 上級者向け, 運用

タイトルとURLをコピーしました