VACUUM の INDEX_CLEANUP オプション（1）

2024-01-20 PostgreSQL

VACUUM の INDEX_CLEANUP オプション（1）

今回の記事の概要

先日、IndexOnlyScan の検証を行っていましたところ、VACUUM しても Visibility Map が全可視（all_visible）にならないテーブルが存在することに気がつきました。

原因を調査しましたところ、VACUUM の INDEX_CLEANUP オプションが影響していることが判明しました。
その VACUUM の INDEX_CLEANUP オプションについて確認した結果を、本記事に記載して説明できればと思います。

VACUUM をしても VisibilityMap が全可視（all_visible）にならない事象を再現

まずは pgbench_accounts テーブルを利用して現象を再現してみます。
環境は

Ubuntu 22.04
PostgreSQL 16.0

です。

pgbench_accounts テーブルの初期化

最初に pgbench_accounts テーブルを初期化します。

$ pgbench -i -s 10
dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.85 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 2.01 s (drop tables 0.70 s, create tables 0.00 s, client-side generate 0.88 s, vacuum 0.08 s, primary keys 0.34 s).

無事に初期化できましたので、pgbench_accounts テーブルの件数を確認します。

postgres=# SELECT COUNT(1) FROM pgbench_accounts;
  count  
---------
 1000000
(1 row)

pgbench_accounts テーブルに 100 万件のレコードが作成されていることを確認できました。

VisbilityMap の確認

この状態で、VisibilityMap を確認します。
VisibilityMap を確認するには、pg_visibility 拡張モジュールの pg_visibility 関数にテーブル名を指定して実行することで確認できます。
all_visible = t であれば「そのページは全可視」で、all_visible = f であれば「そのページは全可視でない」となります。

pg_visibility 拡張モジュールの詳細は、公式ドキュメントを参照してください。

【 PostgreSQL 15.4文書 - F.36. pg_visibility - 】
https://www.postgresql.jp/document/15/html/pgvisibility.html

pg_visibility 関数の結果はページごとに出力されますので、GROUP BY で集約するのが分かりやすいです。

postgres=# SELECT all_visible, COUNT(1) FROM pg_visibility('pgbench_accounts') GROUP BY all_visible ORDER BY all_visible;

all_visible | count 
-------------+-------
 t           | 16394
(1 row)

すべてのページが all_visible = t ということで、「すべてのページが全可視」であることを確認できました。

pgbench_accounts テーブルを更新して VisibilityMap の状態を確認

次に pgbench_accounts テーブルに対して更新を行います。
今回は、テーブル全体の 1 % にあたる件数の廃止行（dead tuple）が発生するように、 1 万件のレコードを更新します。

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

100 万件の 1 % にあたる、1 万件のレコードに対して更新を行いました。
更新後にもう一度、VisibilityMap の状態を確認します。

postgres=# SELECT all_visible, COUNT(1) FROM pg_visibility('pgbench_accounts') GROUP BY all_visible ORDER BY all_visible;
 all_visible | count 
-------------+-------
 f           |   329
 t           | 16229
(2 rows)

all_visible = f が 329 ページ存在することを確認できました。

ここで VisibilityMap を更新するために VACUUM を実行します。
VACUUM 実行後、もう一度 pg_visibility 拡張モジュールを利用して VisibilityMap の状態を確認します。

postgres=# VACUUM pgbench_accounts;
VACUUM

postgres=# SELECT all_visible, COUNT(1) FROM pg_visibility('pgbench_accounts') GROUP BY all_visible ORDER BY all_visible;
 all_visible | count 
-------------+-------
 f           |   164
 t           | 16394
(2 rows)

all_visible = f が 164 ページ、all_visible = t が 16394 ページとなりました。

INDEX_CLEANUP オプション

想定では、すべてのページが all_visible = t となっているのですが、実際には 164 ページが all_visible = f となっています。

この原因は、冒頭にも記述しましたとおり VACUUM の INDEX_CLEANUP オプションが影響しています。
この挙動を説明している公式ドキュメントの記述を下記に引用します。

【 PostgreSQL 15.4文書 - VACUUM - 】
https://www.postgresql.jp/document/15/html/sql-vacuum.html

通常、VACUUMは、テーブル内に無効なタプルがほとんどない場合インデックスのバキュームをスキップします。

デフォルトはAUTOで、適切な場合VACUUMはインデックスのバキュームをスキップします。

上記のとおり INDEX_CLEANUP オプションがデフォルトの auto の場合、更新された件数が少ない場合にインデックスに対する VACUUM がスキップされるようです。

ちなみに、PostgreSQL 13 から 14 にバージョンアップされた際に INDEX_CLEANUP オプションのデフォルト値が on から auto に変更されました。

VACUUM の実行結果を確認

VACUUM に VERBOSE オプションをつけることで、VACUUM で処理された情報が出力されますので、内容を確認します。

postgres=# VACUUM VERBOSE pgbench_accounts;
INFO:  vacuuming "postgres.public.pgbench_accounts"
INFO:  finished vacuuming "postgres.public.pgbench_accounts": index scans: 0
pages: 0 removed, 16558 remain, 165 scanned (1.00% of total)
tuples: 0 removed, 999966 remain, 0 are dead but not yet removable
removable cutoff: 219708586, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan bypassed: 164 pages from table (0.99% of total) have 10000 dead item identifiers
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 178 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
VACUUM

上記メッセージの真ん中あたりに「index scan bypassed: 164 pages」とあるように、164 ページはインデックスに対する VACUUM が回避されたとあります。

では実際に INDEX_CLEANUP を on にして VACUUM して結果を確認します。

postgres=# VACUUM (index_cleanup on) pgbench_accounts;
VACUUM

postgres=# SELECT all_visible, COUNT(1) FROM pg_visibility('pgbench_accounts') GROUP BY all_visible ORDER BY all_visible;
 all_visible | count 
-------------+-------
 t           | 16558
(1 row)

無事にすべてのページが全可視（all_visible = t）となり、求めていた挙動が実現されました。

インデックスに対する VACUUM がスキップされる条件

ここで気になるのが、INDEX_CLEANUP が auto の場合、どういった条件でインデックスに対する VACUUM がスキップされるのかということです。

ソースコードを確認したところ、src/backend/access/heap/vacuumlazy.c で宣言されている BYPASS_THRESHOLD_PAGES が、その閾値に該当することが分かりました。
下記に引用します。

/*
 * Threshold that controls whether we bypass index vacuuming and heap
 * vacuuming as an optimization
 */
#define BYPASS_THRESHOLD_PAGES  0.02    /* i.e. 2% of rel_pages */

つまり、INDEX_CLEANUP に auto が指定されている場合、廃止行が全体のレコード件数のうち、おおよそ 2 % 未満であれば、インデックスに対する VACUUM がスキップされるようです。

実際に検証していた際にも、廃止行の割合が 1.5 % を超えた場合にはインデックスに対する VACUUM は行われ、超えない場合にはインデックスに対する VACUUM はスキップされる挙動となりました。

まとめ

本記事では、VACUUM の INDEX_CLEANUP オプションについて、その影響などの確認を行いました。
次回以降の記事で、INDEX_CLEANUP オプションの on、off で VACUUM の実行速度にどれほどの差が発生するのかを確認したいと思います。

TECH BLOG
技術ブログ