fillfactorの効果を検証（インデックス）

前回の記事（ https://www.insight-ltd.co.jp/tech_blog/postgresql/pg-tuning/900/ ）では、テーブルに対して fillfactor を設定した際の効果を確認しました。
今回は、インデックスに対して fillfactor を設定した際の効果を確認した結果を記載します。

fillfactor についての基本的な説明は、前回の記事に記載していますので、そちらを参照してください。

実際に効果を検証

では早速、インデックスに対して設定された fillfactor の効果を確認します。

環境

検証に利用した環境は下記となります。

-RockyLinux9.3
-PostgreSQL17.2

検証方法

検証方法は前回と同じく、pgbench のテーブルを利用して「更新用の SQL を 2 分間実行する」を 5 回繰り返し、その tps の平均値を取得しました。
検証に利用した SQL も前回と同様ですが、改めて下記に記載します。
それぞれインデックス列に対する更新 SQL と、非インデックス列に対する更新 SQL となっています。

-- インデックス列に対する更新 SQL
UPDATE pgbench_accounts
SET aid = aid * -1
WHERE aid = (SELECT FLOOR(RANDOM() * MAX(aid) + 1) FROM pgbench_accounts)::INTEGER;

-- 非インデックス列に対する更新 SQL
UPDATE pgbench_accounts
SET bid = FLOOR(RANDOM() * 10), abalance = FLOOR(RANDOM() * 100000)
WHERE aid = (SELECT FLOOR(RANDOM() * MAX(aid) + 1) FROM pgbench_accounts)::INTEGER;

結果

tps の平均値と、最大値をそれぞれグラフにしたものが下記となります。
縦軸が tps で、横軸が fillfactor です。tps は高い方が性能がよいという指標になります。

※ 非インデックス列に対する更新 SQL の方が tps が低いのは、SQL の内容が少しだけ複雑なことが原因である可能性が高いです。

結果から分かること

2 つのグラフからは、fillfactor と処理性能の間に関連性は見られませんでした。
テーブルに対して fillfactor を設定した場合には、fillfactor と処理性能に関連性は見られましたが、インデックスの場合には fillfactor と処理性能は関係なさそうなことを確認できました。

追加の確認

更新処理の性能ではなく、断片化の程度についても確認を行いました。

1. pgbench のベンチマークスクリプトを実行
   • 合計で 8 万回トランザクションを実行
2. pgbench_accounts テーブルの pgbench_accounts_pkey インデックスの断片化の程度を確認

断片化の程度は pgstattuple 拡張モジュールの pgstatindex 関数を実行した結果の leaf_fragmentation となります。
leaf_fragmentation = 0 が全く断片化していない状態となり、値が大きくなるにつれて断片化が進んでいることを表しています。

追加の確認結果

fillfactor の設定値によって、ベンチマーク後の断片化の程度を確認した結果が下記となります。

予想通り、fillfactor の設定値と断片化の程度は比例していました。
fillfactor の設定値が低い = 空き領域が多い、ということですので、断片化が起こりにくいという理屈となります。

追加の追加の確認

では、ここで気になるのが「fillfactor」と「断片化の程度」と「参照処理の処理性能」の関係となります。
断片化の程度が低ければ fillfactor の値に関係なく参照処理も高速なるのか、などが気になりましたので、追加で検証を行いました。
検証に利用した SELECT 文は下記となります。

SELECT
    COUNT(1)
FROM
  pgbench_accounts
WHERE
  aid = (SELECT FLOOR(RANDOM() * MAX(aid) + 1) FROM pgbench_accounts)::INTEGER;

pgbench のベンチマークスクリプトを実行した後、上記の SELECT 文を実行して検証を行いました。

追加の追加の確認結果

「SELECT 文を 2 分間実行し続ける」を 5 回繰り返し行い、その tps の最大値、平均値、最小値をグラフ化したものが下記となります。

追加の追加の確認結果から分かること

グラフからは以下のことが読み取れました。

1. 断片化の程度と、SELECT 文の処理性能に関連性は見られないこと
2. fillfactor が 80 未満または 100 の場合、SELECT 文の処理性能が劣化すること

グラフが右肩上がりになっていないため、断片化の程度と SELECT 文の処理性能に関しては関連性がなさそうです。
fillfactor = 80 未満となった場合と、fillfactor = 100 の場合に、明確に処理性能が落ちていることも確認できました。
一番性能が良かったのが fillfactor = 95 の場合です。最大と最小の差が小さいのも fillfactor = 90 または 95 の場合でした。
そのため、インデックスの fillfactor は基本的にはデフォルトの 90 でよさそう、というのを確認できました。

本記事のまとめ

今回の検証では、インデックスの fillfactor の効果を確認しました。
今回の検証を行った範囲内での結論となりますが、明確な理由がなければデフォルト値の 90 のままでよい、ということが分かりました。
当然ですが更新のされ方や頻度によって結果は変わるかと思いますが、基本的にはデフォルト値のままでよさそうです。
fillfactor はディスク領域の消費や、HOT 更新の発生条件にも関連していますので、PostgreSQL を安定的に運用するためにも、理解しておきたい内容となっています。