fillfactorの効果を検証（テーブル）

fillfactorについて

PostgreSQL では「ページ」という単位で、テーブルやインデックスのデータを管理します。
1ページの大きさはデフォルトでは 8 KB となっています。
データはページを跨ぐことはできませんので、格納したいデータ分の空き領域がない場合には、新しいページにデータが格納されます。
例えば、ページの空き領域が 200B で、格納したいデータのサイズが 400B だった場合、そのデータは新しいページに格納されることになります。

PostgreSQLでは、fillfactorという機能（設定）によりページに一定の空き領域を確保することができます。
テーブルの fillfactor は 100、インデックスは 90 がデフォルト設定となっています。
例えば、fillfactor を90 に設定したテーブルでは、ページの 90% までデータが格納される挙動になります。
「fillfactor」という名前からすると、空き領域の確保というよりも、「ページにデータを格納できる割合」という表現の方が正確かもしれません。

fillfactor のメリット・デメリット

fillfactor を 100 未満にして、ページに空き領域を確保するするメリットですが、テーブルとインデックスで、それぞれメリットが少し違います。
テーブルでのメリットは HOT 更新と呼ばれる効率的なデータ更新処理が行われやすくなるということが挙げられます。
インデックスでのメリットは、インデックスのページ分割が行われにくくなることが挙げられます。
また、fillfactor を 100 未満にすることには、デメリットもあります。
具体的には、ディスク領域の消費が増加するということが挙げられます。

実際に効果を検証

仕組みはともかく、運用する上で気になるのは、「fillfactor を設定した方がよいのか」「設定した方がよいのであれば、適正値はいくつか」ということになると思います。
適正値に関してはシステムによりけりですので、一概に定義することはできません。
fillfactor を設定した方がよいテーブルもあれば、設定しなくても問題ないテーブルもあります。

一般的な目安としては、非インデックス列に対する UPDATE が多く行われるテーブルには fillfactor の効果が期待でき、逆に、INSERT のみ行われるテーブルに対しては fillfactor の効果は期待できない、というものになるかと思います。

そこで、今回はテーブルの fillfactor と更新 SQL の速度について、実際に速度検証を行いました。
その結果を以下に記載します。

環境

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

-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 つのグラフから以下のことを確認できました。

1. インデックス列への更新 SQL について
   • fillfactor と性能の関連性は見られない
2. 非インデックス列への更新 SQL について
   • fillfactor = 100 の場合の性能が落ちていると判断できる
   • fillfactor = 50 から 95 では、fillfactor と性能の関連性は見られない

結果として、「非インデックス列への更新 SQL が多数発生するテーブル」に関しては、fillfactor を 100 未満にすることで性能が向上する可能性がある、ということが分かりました。
インデックス列への更新 SQL では、fillfactor と性能の関連性が見られませんでしたが、「HOT 更新はインデックス列に対する更新では行われない」ということが理由だと考えられます。
その他にも、同時に多数のレコードを更新するような場合にも HOT 更新は行われませんので、少数のレコードを対象として繰り返し更新されるようなテーブルに対しては、fillfactor の効果が期待できます。

本記事のまとめ

簡単ではありますが、以上がテーブルに fillfactor を設定した場合の効果を検証した結果となります。
次回以降では、インデックスに fillfactor を設定した場合の効果を検証した結果や、HOT 更新についての解説なども行えればと思います。