TECH BLOG
技術ブログ

前回（ https://www.insight-ltd.co.jp/tech_blog/postgresql/763/ ）に引き続き、今回も VACUUM の INDEX_CLEANUP オプションについての記事となります。

今回は、実際に INDEX_CLEANUP オプションが、VACUUM の実行時間にどの程度影響を与えるのかを確認したいと思います。

VACUUM の実行時間を検証

環境

検証に利用した環境は下記のとおりです。

• CentOS 7.9
• PostgreSQL 15.5

検証手順

以下の手順の 2 ~ 5 を繰り返して、VACUUM の実行速度を確認しました。

1. pgbench でテスト用データを作成
2. pgbench_accounts テーブルのデータを n % 更新
3. pgbench_accounts テーブルに対して VACUUM ( INDEX_CLEANUP = on ) を実行
4. もう一度、pgbench_accounts テーブルのデータを n % 更新
5. pgbench_accounts テーブルに対して VACUUM ( INDEX_CLEANUP = off ) を実行

上記の検証手順の結果より、下記 2 点を確認します。

• 3 と 5 の結果を比較することで、VACUUM の INDEX_CLEANUP オプションを on にした場合と、off にした場合の実行時間の差
• 2 と 4 の n % の n にあたる値を変動させて、更新件数と VACUUM の実行時間の関連性

pgbench_accounts テーブルの 1 % を更新する場合の、具体的な手順を以下に記載します。

検証手順の実例

(1) pgbench でテスト用データを作成

まずはいつものように pgbench を利用して、テスト用のデータを作成します。

[postgres@centos7 db_clusters]$ pgbench -i -s 100
dropping old tables...
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 21.14 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 25.03 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 21.31 s, vacuum 0.25 s, primary keys 3.47 s).

pgbench の -s オプションに 100 を指定して、1,000 万件のテストデータを作成しました。

[postgres@centos7 db_clusters]$ psql
psql (15.5)
Type "help" for help.

postgres=# \dt+ pgbench_accounts
                                         List of relations
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | pgbench_accounts | table | postgres | permanent   | heap          | 1281 MB | 
(1 row)

pgbench_accounts テーブルの大きさは、おおよそ 1.3 GB となっています。

postgres=# \di+ pgbench_accounts*
                                                     List of relations
 Schema |         Name          | Type  |  Owner   |      Table       | Persistence | Access method |  Size  | Description 
--------+-----------------------+-------+----------+------------------+-------------+---------------+--------+-------------
 public | pgbench_accounts_pkey | index | postgres | pgbench_accounts | permanent   | btree         | 216 MB | 
(1 row)

pgbench_accounts テーブルに定義されたインデックス（主キー）は、216 MB とのことです。

(2) pgbench_accounts テーブルのデータを 1 % 更新

次に pgbench_accounts テーブルの 1 % にあたる 10 万件のレコードに対して更新を行います。

postgres=# UPDATE pgbench_accounts SET aid = aid WHERE aid <= 100000;
UPDATE 100000

問題なく更新できました。

(3) INDEX_CLEANUP オプション = on で VACUUM を実行

この状態で、INDEX_CLEANUP オプションを on にした VACUUM を実行します。
VACUUM の実行時間は、VERBOSE オプションをつけることで確認できます。

postgres=# VACUUM (INDEX_CLEANUP on, VERBOSE) pgbench_accounts;
INFO:  vacuuming "postgres.public.pgbench_accounts"
INFO:  finished vacuuming "postgres.public.pgbench_accounts": index scans: 1
pages: 0 removed, 165574 remain, 3280 scanned (1.98% of total)
tuples: 100000 removed, 9999965 remain, 0 are dead but not yet removable
removable cutoff: 744, which was 0 XIDs old when operation ended
new relfrozenxid: 743, which is 1 XIDs ahead of previous value
index scan needed: 1640 pages from table (0.99% of total) had 99986 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 27697 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 1176.314 MB/s, avg write rate: 0.000 MB/s
buffer usage: 8851 hits, 27111 misses, 0 dirtied
WAL usage: 7109 records, 0 full page images, 1209017 bytes
system usage: CPU: user: 0.14 s, system: 0.03 s, elapsed: 0.18 s
VACUUM

下から 2 行目の「system usage: 」で始まるメッセージの末尾、「elapsed: 0.18 s」というのが VACUUM の実行時間になります。
INDEX_CLEANUP オプションを on にした VACUUM では、0.18 sec = 180 ms かかったということを確認できました。

(4) もう一度、pgbench_accounts テーブルのデータを 1 % 更新

ではもう一度、pgbench_accounts テーブルの 1 % にあたる 10 万件のレコードを更新します。

postgres=# UPDATE pgbench_accounts SET aid = aid WHERE aid <= 100000;
UPDATE 100000

(5) INDEX_CLEANUP オプション = off で VACUUM を実行

最後に、INDEX_CLEANUP オプションを off にした VACUUM を実行し、実行時間を確認します。

postgres=# VACUUM (INDEX_CLEANUP off, VERBOSE) pgbench_accounts;
INFO:  vacuuming "postgres.public.pgbench_accounts"
INFO:  finished vacuuming "postgres.public.pgbench_accounts": index scans: 0
pages: 0 removed, 165574 remain, 3280 scanned (1.98% of total)
tuples: 100000 removed, 9999965 remain, 0 are dead but not yet removable
removable cutoff: 745, which was 0 XIDs old when operation ended
new relfrozenxid: 744, which is 1 XIDs ahead of previous value
index scan bypassed: 1641 pages from table (0.99% of total) have 99965 dead item identifiers
avg read rate: 0.000 MB/s, avg write rate: 0.377 MB/s
buffer usage: 6618 hits, 0 misses, 1 dirtied
WAL usage: 3281 records, 0 full page images, 385642 bytes
system usage: CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.02 s
VACUUM

下から 2 行目の末尾に「elapsed: 0.02 s」とありますので、INDEX_CLEANUP オプションを off にした VACUUM では、0.02 s = 20 ms の実行時間であることを確認できました。

データ更新件数と VACUUM 実行時間の一覧

データ更新件数の割合を色々と変えて VACUUM の実行時間を計測した結果が、下記となります。

グラフ化

上記の値をグラフにすると下記のような形になります。

データ更新件数に比例して、INDEX_CLEANUP の on / off での VACUUM の実行時間の差も大きくなっていることが、グラフから読み取れます。
これは、インデックスに対する VACUUM が行われているかいないかの違いによって発生した差であると判断できます。
今回の検証では、テーブルサイズは 1.3 GB 前後、インデックスサイズは 210 MB 前後のテーブルに対して行いました。
VACUUM の実行時間はデータ更新によって発生した廃止行（dead tuple）の件数に比例しますので、テーブルサイズやインデックスサイズがもっと大きくなれば、INDEX_CLEANUP オプションの on / off での差も、より広がっていくかと思います。

INDEX_CLEANUP オプションを off にする際の注意点

VACUUM を高速化したいからといって、INDEX_CLEANUP オプションを off にするのは注意が必要です。
INDEX_CLEANUP オプションを off にして、VACUUM を高速化したところで、結局のところ、インデックスに対する必要なメンテナンス処理が行われていないことになりますので、SELECT が遅延するなどの事態につながります。
特にデータ更新によって発生した廃止行が多い場合には、インデックスに対してもメンテナンスが行われるべきですので、データ更新量が多い時ほど、（ INDEX_CLEANUP のデフォルト値である auto もそういう挙動になっているように）INDEX_CLEANUP = on で VACUUM することが推奨となります。

まとめ

今回の検証では、INDEX_CLEANUP オプションの on / off での、VACUUM の実行速度を確認しました。
その結果、インデックスに対する VACUUM というのは、それなりに負荷の高い処理であることを確認できました。
インデックスを無駄に定義していると、それだけ VACUUM の実行時間が増加することになりますので、「インデックスは必要な分だけ定義する」というのが理想的な運用方法であることを、今回の検証結果から再認識しました。