なぜ今、PostgreSQLの「fillfactor」を深掘りするのか
データベースのパフォーマンスチューニングにおいて、インデックスの選定やクエリプランの最適化に時間を割くのは定石です。しかし、PostgreSQLの深淵を覗くと、物理レイヤーの設計——特に「ページ」という単位でのデータの振る舞い——が、システムの命運を握っていることに気づかされます。
今回は、その中でも特に地味ながら、高負荷環境では劇薬となり得る「fillfactor」について、現場の知見を交えて語ろうと思います。
—
fillfactor：更新頻度の高いテーブルの「余白」をどう設計するか
PostgreSQLのストレージエンジンにおいて、データは8KBの「ページ」単位で管理されています。通常、INSERTが行われると、PostgreSQLはページが一杯になるまでデータを詰め込もうとします。
しかし、ここで問題になるのが UPDATE です。
PostgreSQLのMVCC（多版同時実行制御）アーキテクチャでは、UPDATEは「古い行を無効にし、新しい行を別の場所に挿入する」という挙動をとります。もし、UPDATE対象の行が格納されているページに十分な空きがない場合、何が起きるか。
- ページ外への配置: 新しいバージョン（行）が別のページに書き込まれます。
- インデックスの肥大化: ページが変われば、その行を指す全てのインデックス（TID参照）を更新しなければなりません。
- HOT (Heap Only Tuple) の恩恵が受けられない: これが最大の痛手です。
HOTアップデートを活かすための「余白」
もし、ページ内にあらかじめ「空き領域」を確保できていれば、同じページ内に新しいバージョンを書き込めます。これが「HOTアップデート」です。インデックスを更新する必要がなく、CPU負荷もI/Oも劇的に抑えられる。この「空き領域」を制御するのが `fillfactor` です。
デフォルトの100は「限界まで詰め込む」設定ですが、更新頻度が高いテーブルであれば、ここを80や90に下げることで、劇的なパフォーマンス改善が見込めるケースは少なくありません。
—
インデックスに対するfillfactorの重要性
実は、fillfactorはテーブルだけでなく、インデックスにも設定可能です。ここが面白いところです。
B-Treeインデックスの場合、fillfactorを下げると、ページ分割（Page Split）の頻度を抑えることができます。特に、シーケンシャルな値ではなく、ランダムな値（UUIDなど）が頻繁に挿入されるインデックスでは、デフォルトの100設定だとページ分割が頻発し、ツリー構造が不必要に深くなってしまいます。
- 挿入が多いインデックス: fillfactorを80〜90程度に下げ、ページ内の断片化を防ぐ。
- 読み取り専用に近いインデックス: 100に設定し、ストレージ効率を最大化する。
このトレードオフを、アプリケーションの特性に合わせて微調整できるかどうかが、熟練エンジニアとそうでないエンジニアの分かれ道です。
—
パフォーマンストラブルシューティングの現場から
私が以前担当した、秒間数千リクエストが飛んでくる基幹システムで、CPU使用率が異常に高騰したことがありました。原因は「頻繁なUPDATEによるインデックスの肥大化」でした。
調査の際、まずは以下のクエリでHOT率を確認しました。
SELECT relname, n_tup_upd, n_tup_hot_upd,
(n_tup_hot_upd::float / n_tup_upd) as hot_rate
FROM pg_stat_user_tables;
もし `hot_rate` が極端に低いなら、それは「ページ内に新しい行を収める場所がない」という警告です。このケースでは、テーブルのfillfactorを下げ、さらに `FILLFACTOR` を考慮したインデックス設計に切り替えることで、UPDATE時のインデックスオーバーヘッドを削減し、CPU負荷を3割ほど低減させることに成功しました。
—
最後に：銀の弾丸はない
ここまでfillfactorを推奨してきましたが、当然副作用もあります。
fillfactorを下げれば下げるほど、テーブルやインデックスの物理サイズは大きくなり、フルスキャン時のI/O量は増大します。
「更新時のオーバーヘッド」と「読み取り時のスキャン量」、この天秤をどちらに傾けるのがビジネス的に正解か。それを判断するために、私たちはアーキテクチャの裏側を理解し、メトリクスを計測し続ける必要があります。
教科書的な回答を鵜呑みにせず、自分の環境で `EXPLAIN (ANALYZE, BUFFERS)` を叩き、実際の統計情報を睨みつける。結局、それが一番の近道なんです。
皆さんのデータベースが、今日も健やかに駆動することを願っています。
コメント