1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@matsutomu

PostgreSQL16でVACUUMとautovacuumの挙動を観察する ─ テーブル膨張とdead tupleをpgstattupleで可視化する

1
Posted at

1. はじめに

本記事では、PostgreSQLのVACUUM（バキューム）とautovacuum（自動バキューム）の挙動を実測で確認します。

PostgreSQLはMVCC（Multi-Version Concurrency Control：多版型同時実行制御）によって更新・削除された行を即座に物理削除しません。不要になった古いバージョンの行（dead tuple：デッドタプル）はVACUUMによって回収されるまでテーブルに残り続け、放置するとテーブル膨張（table bloat）とパフォーマンス低下につながります。

1.1 検証環境

項目 内容
OS AmazonLinux2023
PostgreSQL 16
インスタンスタイプ t3.micro (2 vCPU、1GiB)
データ pgbench scale factor 100（約1,500万行）

1.2 VACUUMの基本

種類 説明
VACUUM dead tupleを回収してフリースペース（free space）に戻す。テーブルサイズは縮まない
VACUUM FULL テーブルを書き直してサイズを縮小する。AccessExclusiveLock を取るため本番注意
autovacuum バックグラウンドで自動実行されるVACUUM。pg_stat_user_tables で確認できる

2. 問題のある状態を作る

2.1 autovacuumを無効にしてdead tupleを蓄積させる

./pgsql/autovacuum_off_test.sqlとして下記のSQLを保存します。

-- autovacuumを無効化（検証用）
ALTER TABLE pgbench_accounts SET (autovacuum_enabled = false);

-- 大量UPDATEでdead tupleを発生させる
UPDATE pgbench_accounts SET abalance = abalance + 1;
UPDATE pgbench_accounts SET abalance = abalance + 1;
UPDATE pgbench_accounts SET abalance = abalance + 1;

実行します。

$ psql -U postgres -d pgbench_test -f ./pgsql/autovacuum_off_test.sql
ALTER TABLE
UPDATE 10000000
UPDATE 10000000
UPDATE 10000000

2.2 pg_stat_user_tablesでdead tupleを確認する

下記のSQLを./pgsql/pgstat_user_tables.sqlとして保存します。

SELECT
  relname                           AS table_name,
  n_live_tup                        AS live_tuples,
  n_dead_tup                        AS dead_tuples,
  ROUND(n_dead_tup * 100.0
    / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze
FROM pg_stat_user_tables
WHERE relname = 'pgbench_accounts';

UPDATEを3回繰り返した後の出力例です。

    table_name    | live_tuples | dead_tuples | dead_ratio_pct |          last_vacuum          | last_autovacuum |
   last_analyze
------------------------------------------------------------------------------------------------------------------
-------------------------
 pgbench_accounts |    10000035 |    29999974 |           75.0 | 2026-05-06 11:47:12.198267+00 |                 | 2026-
05-06 11:47:19.729915+00
(1 row)

dead_tuples=29999974（3回分のUPDATE）がVACUUMされずに残っています。dead_ratio_pct=75.0 は全体の75%がdead tupleであることを示しており、この状態ではSeq Scanのコストが大幅に増加します。

2.3 pgstattupleで物理的な膨張を確認する

pg_stat_user_tables の統計情報は推計値です。物理的な実態を確認するには pgstattuple 拡張を使います。./pgsql/create_pgstattuple.sqlとして、下記のSQLを保存します。

-- pgstattuple拡張のインストール
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 物理的な膨張状況を確認
SELECT
  table_len                               AS total_bytes,
  dead_tuple_count,
  dead_tuple_len,
  ROUND(dead_tuple_percent::numeric, 1)   AS dead_pct,
  free_space,
  ROUND(free_percent::numeric, 1)         AS free_pct
FROM pgstattuple('pgbench_accounts');

$ psql -U postgres -d pgbench_test -f ./pgsql/create_pgstattuple.sql
 total_bytes | dead_tuple_count | dead_tuple_len | dead_pct | free_space | free_pct
-------------------------------------------------------------------------------
  5371797504 |         10000000 |     1210000000 |     22.5 | 2633436868 |     49.0
(1 row)

dead_tuple_len=1,210,000,000（約1.2GB）が物理的に占有されています。free_pct=49.0 はVACUUMによる回収がまだ行われていないことを示しています。

3. VACUUMを実行して効果を確認する

3.1 手動VACUUMの実行

./pgsql/vacuum_pgbench_accounts.sqlとして下記のSQLを保存します。

-- 実行時間を計測
\timing on
VACUUM VERBOSE pgbench_accounts;
\timing off

VACUUMの出力例です。

$ psql -U postgres -d pgbench_test -f ./pgsql/vacuum_pgbench_accounts.sql
Timing is on.
psql:pgsql/vacuum_pgbench_accounts.sql:2: INFO:  vacuuming "pgbench_test.public.pgbench_accounts"
psql:pgsql/vacuum_pgbench_accounts.sql:2: INFO:  finished vacuuming "pgbench_test.public.pgbench_accounts": index scans: 3
pages: 0 removed, 655737 remain, 655737 scanned (100.00% of total)
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 17008006, which was 0 XIDs old when operation ended
new relfrozenxid: 17008004, which is 8 XIDs ahead of previous value
frozen: 3 pages from table (0.00% of total) had 44 tuples frozen
index scan needed: 491803 pages from table (75.00% of total) had 29999939 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 65.301 MB/s, avg write rate: 46.317 MB/s
buffer usage: 663761 hits, 1304324 misses, 925140 dirtied
WAL usage: 1417660 records, 877327 full page images, 2004532694 bytes
system usage: CPU: user: 15.37 s, system: 7.35 s, elapsed: 156.04 s
VACUUM
Time: 156069.221 ms (02:36.069)
Timing is off.

結果のログを見るとかなりの量の処理が行われた事がわかります。

  • 10,000,000 removed: 不要になったデータ（ゴミ）を1000万件削除
  • 10,000,000 remain : まだ有効なデータが1000万件あり
  • WAL usage: ・・・, 2004532694 bytes ：VACUUM処理で、約2GB分のログ

3.2 VACUUM後のpgstattupleで回収状況を確認する

再度テーブルの状況を確認します。

$ psql -U postgres -d pgbench_test -f ./pgsql/create_pgstattuple.sql
psql:pgsql/create_pgstattuple.sql:2: NOTICE:  extension "pgstattuple" already exists, skipping
CREATE EXTENSION
 total_bytes | dead_tuple_count | dead_tuple_len | dead_pct | free_space | free_pct
-------------------------------------------------------------------------------
  5371797504 |                0 |              0 |      0.0 | 4031468868 |     75.1
(1 row)

dead_tuple_count=0 になりdead tupleは回収されましたが、total_bytes は変わっていません。VACUUMはフリースペースを作るだけで テーブルファイルのサイズ自体は縮小しません。free_spaceが、約 4.03 GB（4,031,468,868）として残っています。free_pctは、75.1%が空き領域として残っている状況です。このままでは、テーブルサイズとして全体は5GBの領域を占めた状態になります。サイズを縮小するには VACUUM FULL が必要ですが、AccessExclusiveLock を取るため本番では pg_repack の利用を検討してください。

3.3 autovacuumの動作をpg_stat_user_tablesで観察する

autovacuumを再度有効にして、自動実行されるタイミングを観察します。./pgsql/autovacuum_on.sqlとしてSQLを保存します。

-- autovacuumを再有効化
ALTER TABLE pgbench_accounts RESET (autovacuum_enabled);

-- dead tupleを発生させる
UPDATE pgbench_accounts SET abalance = abalance + 1;

SQLを実行します。

$ psql -U postgres -d pgbench_test -f ./pgsql/autovacuum_on.sql

$ watch -n 3 "psql -U postgres -d pgbench_test -f ./pgsql/pgstat_user_tables.sql"
    table_name    | live_tuples | dead_tuples | dead_ratio_pct |          last_vacuum          | last_autovacuum |   last_analyze
------------------------------------------------------------------------------------------------------------------
-------------------------
 pgbench_accounts |    10021323 |    10000000 |           49.9 | 2026-05-06 12:06:46.038678+00 |		 | 2026-05-06 11:47:19.729915+00
(1 row)

しばらく経過すると下記の表示に変わります。

  • dead_tuples：10000000が0に変化
  • dead_ratio_pct：49.9が0.0に変化
  • last_autovacuum：実行されて 2026-05-06 12:28:35が入りました
    table_name    | live_tuples | dead_tuples | dead_ratio_pct |          last_vacuum          |        last_autovacuum       |         last_analyze
-------------------------------------------------------------------------------------------------------------------
--------------------------------------
 pgbench_accounts |    15010623 |           0 |            0.0 | 2026-05-06 12:06:46.038678+00 | 2026-05-06 12:28:35.543009+00 | 2026-05-06 11:47:19.729915+00
(1 row)

このように自動でVACUUMが実行されて、不要領域が空になった事がわかります。autovacuumがトリガーされる条件はデフォルトで以下の通りです。

パラメータ デフォルト値 意味
autovacuum_vacuum_threshold 50 dead tupleがこの数を超えると候補になる
autovacuum_vacuum_scale_factor 0.2 テーブル行数の20%を超えるとトリガー
autovacuum_vacuum_cost_delay 2ms I/Oスロットリング（throttling）の間隔

n_dead_tup > 50 + (テーブル行数 × 0.2) の条件を満たすと autovacuum が起動します。

3.4 log_autovacuumでautovacuumの動作ログを確認する

# postgresql.conf
log_autovacuum_min_duration = 0   # すべてのautovacuumをログに記録（0=全件）

値を有効にするために再起動します。

$ sudo systemctl stop postgresql
$ sync && echo 3 | sudo tee /proc/sys/vm/drop_caches
$ sudo systemctl start postgresql

# もう一度 データを更新します。
$ psql -U postgres -d pgbench_test -f ./pgsql/autovacuum_on.sql

$ sudo tail -n 300 /var/lib/pgsql/data/log/postgresql-Wed.log
2026-05-06 12:43:42.903 UTC [5136] LOG:  automatic vacuum of table "pgbench_test.public.pgbench_accounts": index scans: 1
	pages: 0 removed, 655737 remain, 327871 scanned (50.00% of total)
	tuples: 10000000 removed, 14925290 remain, 0 are dead but not yet removable
	removable cutoff: 17008012, which was 0 XIDs old when operation ended
	new relfrozenxid: 17008012, which is 5 XIDs ahead of previous value
	frozen: 163938 pages from table (25.00% of total) had 10000000 tuples frozen
	index scan needed: 163935 pages from table (25.00% of total) had 9999964 dead item identifiers removed
	index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
	avg read rate: 22.099 MB/s, avg write rate: 22.464 MB/s
	buffer usage: 337362 hits, 537436 misses, 546327 dirtied
	WAL usage: 1202062 records, 546511 full page images, 2961574477 bytes
	system usage: CPU: user: 13.05 s, system: 6.23 s, elapsed: 189.99 s

tuples: 10000000 removed でdead tupleが自動回収されたことを確認できます。

3.5 pg_repackについて

pg_repackを新しくインストールします。対象のEC2から外部に接続できる状態で、本コマンドは実行しています。

# リポジトリファイルを手動で作成
$ sudo tee /etc/yum.repos.d/pgdg16.repo << 'EOF'
[pgdg16]
name=PostgreSQL 16 for RHEL/CentOS 9
baseurl=https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-9-x86_64/
enabled=1
gpgcheck=0
EOF

# インストール
$ sudo dnf install -y pg_repack_16
# フルパスで確認
$ /usr/pgsql-16/bin/pg_repack --version

# パスを通す
$ echo 'export PATH=/usr/pgsql-16/bin:$PATH' >> ~/.bash_profile
$ source ~/.bash_profile

# 再確認
$ pg_repack --version
pg_repack 1.5.3

ここからはうまくいかなかった部分があったのですが、最終的に実行できた方法をメモしておきます。

# 追加でインストール
$ sudo dnf install -y postgresql16-server-devel

# 個別にインストールしたものを環境に応じた先にリンク
$ sudo ln -s /usr/pgsql-16/share/extension/pg_repack* /usr/share/pgsql/extension/
$ sudo ln -s /usr/pgsql-16/lib/pg_repack.so /usr/lib64/pgsql

# 反映のために再起動
$ sudo systemctl restart postgresql
# 拡張機能を有効化
psql -U postgres -d pgbench_test -c "CREATE EXTENSION pg_repack;"

pg_repackをクライアントとして、お試しで実行してみます。

$ pg_repack \
  -h localhost \
  -U postgres \
  -d pgbench_test \
  --dry-run \
  --no-order \
  -t pgbench_accounts

また大量に更新を行います。

$ psql -U postgres -d pgbench_test -f ./pgsql/autovacuum_off_test.sql
ALTER TABLE
UPDATE 10000000
UPDATE 10000000
UPDATE 10000000

# 状況を確認すると大量に`dead_tuple`が発生
$ psql -U postgres -d pgbench_test -f ./pgsql/create_pgstattuple.sql
psql:pgsql/create_pgstattuple.sql:2: NOTICE:  extension "pgstattuple" already exists, skipping
CREATE EXTENSION
 total_bytes | dead_tuple_count | dead_tuple_len | dead_pct | free_space | free_pct 
-------------+------------------+----------------+----------+------------+----------
  5371797504 |         10000056 |     1210006776 |     22.5 | 2633429860 |     49.0
(1 row)

対象テーブルに対して、pg_repackを実行します。

$ pg_repack \
  -h localhost \
  -U postgres \
  -d pgbench_test \
  --no-order \
  -t pgbench_accounts
INFO: repacking table "public.pgbench_accounts"

# pg_repack後に結果を確認
$ psql -U postgres -d pgbench_test -f ./pgsql/create_pgstattuple.sql
psql:pgsql/create_pgstattuple.sql:2: NOTICE:  extension "pgstattuple" already exists, skipping
CREATE EXTENSION
 total_bytes | dead_tuple_count | dead_tuple_len | dead_pct | free_space | free_pct 
-------------+------------------+----------------+----------+------------+----------
  1342955520 |                0 |              0 |      0.0 |   18365340 |      1.4

pg_repackを利用すれば、対象テーブルに対してロックをかけずに、不要領域の解放が可能です。
また、実行前後で総合計のサイズ（total_bytes）が大幅に縮小しています。
実行前：5,371,797,504 (約5.3GB)
実行後：1,342,955,520 (約1.3GB)

なお、pg_repack はオンラインで実行可能ですが、一時的に対象テーブルの約2倍のディスク容量を消費します。ディスク残量には注意して実行してください。

4. まとめ

4.1 テーブル膨張確認から対処までの流れ

クエリが遅い・テーブルサイズが大きい
  │
  ├─ pg_stat_user_tables で n_dead_tup を確認
  │    └─ dead_ratio が高い → VACUUMが必要
  │
  ├─ pgstattuple で物理的な膨張を確認
  │    └─ dead_tuple_percent が高い → VACUUM実行
  │
  ├─ 手動VACUUM で即時回収
  │    └─ テーブルサイズは縮まない（縮小はVACUUM FULL または pg_repack）
  │
  └─ log_autovacuum_min_duration でautovacuumの動作を確認
       └─ 頻繁すぎる or 遅すぎる場合は autovacuum_vacuum_scale_factor を調整

4.2 autovacuumチューニングの目安

症状 確認ポイント 対処
dead tupleが増え続ける last_autovacuum が古い autovacuum_vacuum_scale_factor を小さくする（例：0.05）
autovacuumが頻繁すぎてI/Oを圧迫する log_autovacuum のelapsed時間 autovacuum_vacuum_cost_delay を増やす
大テーブルだけVACUUMが遅い pgstattupleでdead_tuple_len テーブル単位で autovacuum_vacuum_scale_factor をオーバーライド
インデックスが肥大化している pgstatindex (pgstattuple拡張の一部) REINDEX または pg_repack の実行

実運用においては、autovacuumの実行タイミングの細かな制御や、サービス停止を伴わない pg_repack の活用、あるいはインデックスの再構築（REINDEX）など、状況に応じた多様な考慮が必要になります。

しかし、高度なチューニングを行う前には必ず「今、何が起きているのか」を正しく把握するスキルが欠かせません。まずは本記事で紹介した pg_stat_user_tables や pgstattuple を使いこなし、PostgreSQLの内部で起きている「膨張」を可視化できるようになることから始めてみてください。

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?