PostgreSQLのVACUUMとは、autovacuumと肥大化の監視

PostgreSQL を運用していると、テーブルのサイズが中のデータの量に比べて大きくなっている、以前より検索が遅くなった、ログに must be vacuumed within ... transactions という警告が出た、といったことに出会います。どれも VACUUM に関係しています。この記事では、VACUUM と autovacuum が何をしているのか、うまく回っているかをどう確かめるか、回っていないときにどう直すかを順に説明します。

VACUUM が必要な理由

PostgreSQL は行を UPDATE しても、元の行をその場で書き換えません。行の新しいバージョンを書き足し、古いバージョンは「もう使わない行」として残します。DELETE も、行をすぐに消すのではなく、使わない印をつけるだけです。こうしておくと、更新の途中でも、ほかのトランザクションは古いバージョンを読めます。この仕組みを MVCC (多版型同時実行制御) と呼びます。

使わなくなった古いバージョンの行を「不要な行」(dead tuple) と呼びます。不要な行はどのトランザクションからも見えなくなったあとも、誰かが片付けるまでテーブルに残ります。片付けるのが VACUUM です。

PostgreSQL のドキュメントは、VACUUM の役割として次の 4 つを挙げています。

更新や削除で不要になった行の領域を、再利用できるようにする
クエリの実行計画に使う統計情報を更新する (ANALYZE と組み合わせたとき)
可視性マップを更新し、インデックスオンリースキャンを速くする
トランザクション ID の周回によって古いデータが失われるのを防ぐ

1 が回らないとテーブルが肥大化し、4 が回らないとデータベースが書き込みを受け付けなくなります。後者は頻度は低いものの影響が大きいので、この記事の後半で詳しく扱います。

VACUUM と VACUUM FULL の違い

	VACUUM	VACUUM FULL
すること	不要な行の領域を、同じテーブルで再利用できるようにする	テーブルを新しく書き直して詰める
ディスクの空き	ふつうは OS に返さない (末尾の空きページだけ返す)	縮んだ分を OS に返す
ロック	通常の読み書きと同時に動く	`ACCESS EXCLUSIVE` ロック。読み取りも止まる
追加のディスク	ほぼ不要	書き直したテーブルの分が一時的に必要
autovacuum が行うか	行う	行わない

通常の VACUUM は空いた場所を再利用できるようにするだけなので、ファイルの大きさはふつう小さくなりません。それでも、それ以上大きくなるのを防げます。ドキュメントも、VACUUM FULL は日常の運用には勧めず、特別な場合に使うものとしています。サービスを止めずにテーブルを詰めたい場合は、pg_repack のような拡張機能 (PostgreSQL 本体には含まれません) を使う方法もあります。

手動で実行するときは、次のように書きます。VACUUM はトランザクションブロックの中では実行できません。

VACUUM (VERBOSE, ANALYZE) orders;

VERBOSE をつけると、消した行の数や、消せなかった行の数が表示されます。

autovacuum とは

autovacuum は VACUUM と ANALYZE を自動で実行する仕組みで、既定で有効です。ランチャーと呼ばれるプロセスが autovacuum_naptime (既定 1 分) ごとに各データベースを見回り、条件を満たしたテーブルにワーカーを割り当てます。同時に動くワーカーの数は autovacuum_max_workers (既定 3) までです。

autovacuum が動く条件

テーブルの更新・削除で生じた不要な行の数が、次の値を超えると VACUUM の対象になります。

しきい値 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × テーブルの行数

既定値は autovacuum_vacuum_threshold が 50 行、autovacuum_vacuum_scale_factor が 0.2 (20%) です。テーブルの行数は pg_class.reltuples の推定値を使います。

計算してみると、1,000 行のテーブルなら 50 + 200 = 250 行の不要な行で動きます。100 万行なら 50 + 200,000 で約 20 万行、1 億行なら約 2,000 万行です。大きなテーブルほど、不要な行がたくさんたまるまで autovacuum が動きません。大きなテーブルが肥大化しやすいのはこのためです。

PostgreSQL 18 からは、このしきい値に上限 autovacuum_vacuum_max_threshold (既定 1 億行) が加わりました。それより前のバージョンでは上限がありません。

ほかに、INSERT だけが続くテーブルにも VACUUM をかける条件 (autovacuum_vacuum_insert_threshold と autovacuum_vacuum_insert_scale_factor) と、ANALYZE をかける条件 (autovacuum_analyze_threshold 50 行と autovacuum_analyze_scale_factor 0.1) があります。

テーブルごとに調整する

更新の多い大きなテーブルだけ、早めに autovacuum が動くようにできます。設定はテーブルのストレージパラメータで行います。

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

この例では、1,000 万行のテーブルで約 20 万行の不要な行がたまったところで動くようになります。データベース全体の設定を変えるより影響が小さいので、まずは問題のテーブルだけ調整するのが扱いやすい方法です。

autovacuum はほかの処理の邪魔をしないよう、一定の量を処理するごとに少し休みながら動きます (autovacuum_vacuum_cost_delay と autovacuum_vacuum_cost_limit)。不要な行がたまる速さに処理が追いつかない場合は、これらを調整して休む時間を減らします。

autovacuum が回っているかを確かめる

不要な行が多いテーブルを探す

テーブルごとの不要な行の数と、最後に VACUUM されたのはいつかは、pg_stat_user_tables で見られます。

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

列	意味
`n_live_tup`、`n_dead_tup`	生きている行と不要な行の推定数
`last_vacuum`	最後に手動で VACUUM した時刻 (VACUUM FULL は含まない)
`last_autovacuum`	最後に autovacuum が VACUUM した時刻
`autovacuum_count`	autovacuum が VACUUM した回数

見るべきなのは、n_dead_tup が多いのに last_autovacuum が古い (または NULL の) テーブルです。上の計算式のしきい値を超えているのに長く動いていなければ、autovacuum が追いついていないか、動いても消せていない可能性があります。

n_dead_tup は統計からの推定値です。肥大化の量を正確に知りたいときは、拡張機能 pgstattuple を使います。pgstattuple_approx は可視性マップを使って読むページを減らすので、テーブル全体を読む pgstattuple より軽く済みます。

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('orders'::regclass);

dead_tuple_percent は不要な行の割合、approx_free_percent は再利用を待っている空き領域の割合です。空き領域が大きいままなら、VACUUM はできているがファイルが縮んでいない状態です。

いま動いている VACUUM の進み具合を見る

大きなテーブルの VACUUM は数時間かかることがあります。進み具合は pg_stat_progress_vacuum で見られます。

SELECT
  pid,
  relid::regclass AS table_name,
  phase,
  heap_blks_scanned,
  heap_blks_total,
  round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS scanned_pct,
  index_vacuum_count
FROM pg_stat_progress_vacuum;

index_vacuum_count が何度も増えている場合は、不要な行を覚えておくためのメモリ (maintenance_work_mem、autovacuum では autovacuum_work_mem) が足りず、インデックスを何回にも分けて処理しています。

ログに残す

log_autovacuum_min_duration を設定すると、指定した時間以上かかった autovacuum がログに出ます。現行のバージョンの既定は 10 分です (PostgreSQL 14 までは既定で無効)。0 にするとすべての autovacuum が記録されます。ログには消した行の数と、まだ消せなかった行の数が出るので、次の節の「消せない」状態に気づく手がかりになります。

autovacuum が動いても不要な行が消えないとき

VACUUM が消せるのは、どのトランザクションからも見えなくなった行だけです。古いスナップショットを持ち続けているものがあると、それより新しい不要な行は消せません。autovacuum が何度動いても n_dead_tup が減らない場合は、次の 3 つを疑います。

長く開いたままのトランザクション

いちばん多いのがこれです。BEGIN したまま放置された接続や、何時間も動いている集計クエリが、古いスナップショットを持ち続けます。

SELECT
  pid,
  usename,
  application_name,
  state,
  now() - xact_start AS xact_age,
  backend_xmin,
  left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

xact_age が長いセッション、特に state が idle in transaction のものが原因の候補です。idle_in_transaction_session_timeout を設定すると、この状態が一定時間を超えたセッションを自動で切れます。

使われていないレプリケーションスロット

レプリケーションスロットは、スタンバイや論理レプリケーションの購読側がまだ必要としている行を消さないよう、xmin を保持することがあります。購読側が止まったまま放置されたスロットがあると、不要な行がたまり続けます。

SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM pg_replication_slots;

active が false で xmin か catalog_xmin が古いスロットは、本当に不要か確かめたうえで削除を検討します。スタンバイで hot_standby_feedback を有効にしている場合も、スタンバイで動いている長いクエリがプライマリの VACUUM を止めることがあります。詳しくはレプリケーション遅延の原因と監視を参照してください。

コミットもロールバックもされていない準備済みトランザクション

二相コミット (PREPARE TRANSACTION) を使っている場合、決着していない準備済みトランザクションも VACUUM を止めます。

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;

トランザクション ID の周回 (XID wraparound)

何が起きるのか

PostgreSQL は書き込みを行うトランザクションごとに 32 ビットのトランザクション ID (XID) を割り当て、行がどのトランザクションで書かれたかを XID で記録しています。32 ビットなので約 40 億で一周し、比較は「自分より約 20 億前までが過去」という形で行われます。古い行の XID を放っておくと、いつか「未来に書かれた行」と判定されて見えなくなってしまいます。

これを防ぐのが、VACUUM の「凍結」(freeze) です。十分に古い行に「すべてのトランザクションから見て過去」という印をつけ、XID の比較の対象から外します。autovacuum は、テーブルの最も古い未凍結の XID の年齢が autovacuum_freeze_max_age (既定 2 億) を超えると、autovacuum を無効にしていても、周回を防ぐための VACUUM を強制的に実行します。

それでも凍結が追いつかないと、周回までの残りが 4,000 万トランザクションを切ったところで、次の警告がログとクライアントに出始めます。

WARNING:  database "mydb" must be vacuumed within 39985967 transactions

さらに残りが 300 万を切ると、データベースは新しい XID の割り当てを拒否し、書き込みができなくなります。

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"

監視する SQL

データベースごとの最も古い XID の年齢は次の SQL で見られます。

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

テーブルごとに見るには、TOAST テーブルも含めて次のようにします。

SELECT
  c.oid::regclass AS table_name,
  greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

ふつうは xid_age が autovacuum_freeze_max_age (2 億) を少し超えたあたりで autovacuum が凍結し、値が下がります。2 億を大きく超えて増え続けているなら、凍結の VACUUM が終わっていないか、前の節の原因で止められています。警告が出る約 21 億に近づく前に対処が必要です。Amazon RDS for PostgreSQL では、CloudWatch の MaximumUsedTransactionIDs が同じ値の目安になります。AWS も、周回に備えて CloudWatch のアラームを設定するよう勧めています。

周回が近づいたときの対処

ドキュメントが示している手順は次のとおりです。

古い準備済みトランザクションを決着させる
長く動いているトランザクションを終わらせる
不要になったレプリケーションスロットを削除する
その後、VACUUM を実行する

ここで実行するのは通常の VACUUM で、VACUUM FULL や VACUUM FREEZE ではありません。止めている原因を先に取り除かないと、VACUUM を何度実行しても XID の年齢は下がりません。

この確認を続けるには

ここで紹介した pg_stat_user_tables の n_dead_tup と last_autovacuum、age(datfrozenxid) を cron で 1 日 1 回ほど記録する、監視ツールで XID の年齢や RDS の MaximumUsedTransactionIDs にアラートを設定する、log_autovacuum_min_duration でログを残す、といった方法で状態を見続けられます。不要な行の数はテーブルの大きさや更新の量で大きく違うので、テーブルごとのふだんの推移と比べて判断すると見落としが減ります。

AI に任せる方法もあります。Scandock は、接続文字列を登録すると AI がテーブルの構成と実データを読んで監視項目を決め、固定の閾値ではなく、値の推移や過去の通知の結末を踏まえて異常かどうかを判断するサービスです。異常と判断すると、読み取り専用のクエリで関連するデータを調べ、原因・影響・対処案をつけて通知します。PostgreSQL のほか、Neon、Amazon RDS、Cloud SQL などにも対応しています。