PostgreSQLのVACUUMとautovacuum運用設計｜テーブル肥大化とXID周回を防ぐ設定の考え方

この記事のポイント

・VACUUMはデッドタプルを再利用可能にし、VACUUM FULLは物理圧縮するが排他ロックが必要
・autovacuumの起動条件はthreshold + scale_factor × 行数で決まる
・XID周回障害を防ぐにはage(relfrozenxid)を定期監視してFreeze VACUUMを計画的に実行
・大量更新テーブルはALTER TABLEでautovacuumパラメータをテーブル単位で上書きする

-- データベース全体をVACUUM
$ sudo -u postgres psql -c "VACUUM VERBOSE;"

-- 特定テーブルのみVACUUM
$ sudo -u postgres psql -d mydb -c "VACUUM VERBOSE orders;"

INFO:  vacuuming "public.orders"
INFO:  scanned index "orders_pkey" to remove 154230 row versions
DETAIL:  CPU: user: 0.38 s, system: 0.07 s, elapsed: 1.89 s
INFO:  "orders": removed 154230 row versions in 1543 pages
DETAIL:  CPU: user: 0.25 s, system: 0.12 s, elapsed: 2.71 s
INFO:  "orders": found 154230 removable, 9821034 nonremovable row versions in 28341 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4021938294
CPU: user: 1.98 s, system: 0.41 s, elapsed: 11.42 s
VACUUM

-- テーブル肥大化が深刻な場合（メンテナンス時間帯に実行）
$ sudo -u postgres psql -d mydb -c "VACUUM FULL VERBOSE orders;"

INFO:  vacuuming "public.orders"
INFO:  "orders": found 0 removable, 2841023 nonremovable row versions in 38412 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 3.28 s, system: 1.42 s, elapsed: 12.74 s.
VACUUM

-- VACUUM + 統計情報更新を同時に実行
$ sudo -u postgres psql -d mydb -c "VACUUM ANALYZE orders;"

-- 設定ファイルの場所を確認する
$ sudo -u postgres psql -c "SHOW config_file;"

                config_file
--------------------------------------------
 /var/lib/pgsql/16/data/postgresql.conf
(1 row)

# /var/lib/pgsql/16/data/postgresql.conf

# autovacuumを有効化（デフォルト on）
autovacuum = on

# 同時に動かすautovacuumワーカー数（デフォルト: 3）
autovacuum_max_workers = 5

# 次のテーブルを確認するまでの間隔（デフォルト: 1min）
autovacuum_naptime = 30s

# VACUUM起動閾値（scale_factorのデフォルトは0.2 → 5%に絞る）
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05

# ANALYZE起動閾値（プランナ統計の更新）
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# I/Oコスト制御（cost_limitを上げてautovacuumを速くする）
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 400

$ sudo -u postgres psql -c "SELECT pg_reload_conf();"

 pg_reload_conf
----------------
 t
(1 row)

$ sudo -u postgres psql -c "
SELECT name, setting, unit FROM pg_settings
WHERE name IN (
  'autovacuum_vacuum_scale_factor',
  'autovacuum_vacuum_cost_delay',
  'autovacuum_vacuum_cost_limit',
  'autovacuum_max_workers'
);"

              name               | setting | unit
---------------------------------+---------+------
 autovacuum_max_workers          | 5       |
 autovacuum_vacuum_cost_delay    | 2       | ms
 autovacuum_vacuum_cost_limit    | 400     |
 autovacuum_vacuum_scale_factor  | 0.05    |
(4 rows)

-- XID年齢が古いテーブルを上位10件表示
$ sudo -u postgres psql -d mydb -c "
SELECT schemaname, relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;"

 schemaname |    relname    | xid_age |  size
------------+---------------+---------+---------
 public     | orders        | 1842301 | 4821 MB
 public     | order_items   | 1839450 | 2341 MB
 public     | users         |  983201 |  234 MB
 public     | product_cache |  412050 |   18 MB
 public     | sessions      |  387901 |  127 MB
(5 rows)

-- ordersテーブル：scale_factorを0.02（2%）に下げてautovacuumをより頻繁に起動
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

-- 設定を確認
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

   relname   |                                    reloptions
-------------+----------------------------------------------------------------------------------
 orders      | {autovacuum_vacuum_scale_factor=0.02,autovacuum_analyze_scale_factor=0.01,autovacuum_vacuum_cost_delay=2}
(1 row)

-- テーブル単位の設定をデフォルトに戻す
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);

-- デッドタプルが多いテーブルを確認
$ sudo -u postgres psql -d mydb -c "
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_ratio_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;"

   relname   | n_live_tup | n_dead_tup | dead_ratio_pct |        last_autovacuum        |      last_autoanalyze
-------------+------------+------------+----------------+-------------------------------+-------------------------------
 orders      |   9821034  |   1243198  |           11.2 | 2026-08-25 03:14:22.341256+09 | 2026-08-25 03:14:24.881943+09
 order_items |   28341029 |    843022  |            2.9 | 2026-08-25 06:42:10.123400+09 | 2026-08-25 06:42:14.334521+09

-- テーブルサイズとlive行数の比率でbloatを把握する
$ sudo -u postgres psql -d mydb -c "
SELECT relname,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       n_live_tup,
       pg_size_pretty(
         pg_relation_size(relid) / NULLIF(n_live_tup, 0)
       ) AS bytes_per_live_row
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY pg_relation_size(relid) DESC
LIMIT 10;"

-- 実行中のautovacuumプロセスを確認
$ sudo -u postgres psql -c "
SELECT pid, datname, query_start, state, query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%';"

  pid  |  datname  |          query_start          | state |                           query
-------+-----------+-------------------------------+-------+------------------------------------------------------------
 10234 | mydb      | 2026-09-24 02:15:01.234+09    | active| autovacuum: VACUUM public.access_logs (to prevent wraparound)
 10501 | mydb      | 2026-09-24 02:16:23.891+09    | active| autovacuum: VACUUM public.order_items
(2 rows)

-- メンテナンス時間帯に手動で実行（VERBOSEで進捗確認）
$ sudo -u postgres psql -d mydb -c "VACUUM (VERBOSE, ANALYZE) orders;"

-- XID年齢が深刻な場合はFREEZEも付ける
$ sudo -u postgres psql -d mydb -c "VACUUM (VERBOSE, FREEZE) orders;"

-- 5分以上続いているトランザクションを確認する
$ sudo -u postgres psql -c "
SELECT pid, now() - xact_start AS duration, state, LEFT(query, 60) AS query_head
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;"

 pid  |  duration  | state |          query_head
------+------------+-------+------------------------------
 8821 | 00:47:32   | idle  | BEGIN
 9104 | 00:12:15   | active| SELECT count(*) FROM large_t
(2 rows)

# postgresql.confに追加（250ms以上かかったautovacuumをログに記録）
log_autovacuum_min_duration = 250ms

# Rocky Linux 9 / RHEL 9の場合
grep -i autovacuum /var/lib/pgsql/16/data/log/postgresql-2026-09-24_000000.log | tail -10

# Ubuntu 24.04の場合
grep -i autovacuum /var/log/postgresql/postgresql-16-main.log | tail -10

2026-09-24 02:15:33.108 JST [10234] LOG:  automatic vacuum of table "mydb.public.order_items": index scans: 1
	pages: 0 removed, 18341 remain, 0 skipped due to pins
	tuples: 182430 removed, 1203551 remain, 0 are dead but not yet removable, oldest xmin: 4021938294
	I/O timings: read: 1247.839 ms, write: 92.144 ms
	avg read rate: 9.813 MB/s, avg write rate: 0.726 MB/s
	system usage: CPU: user: 1.52 s, sys: 0.53 s, elapsed: 10.22 s