「INSERTは速いのに、UPDATEで死ぬ」を卒業しよう。PostgreSQLのFILLFACTORを使いこなす技術
やあ、エンジニア諸君。PostgreSQLを触っていて、「最初は爆速だったのに、データが溜まって更新が増えた途端にパフォーマンスがガタ落ちした……」なんて経験はないだろうか。
特に、UPDATEが頻発するテーブルで、なぜかIOが跳ね上がり、CPUも張り付く。今日はそんな「PostgreSQLの更新地獄」を、テーブルのストレージ設定一つで劇的に改善する`FILLFACTOR`という裏技（というより、必須の知恵）について話そうと思う。
教科書的な説明は一旦置いておいて、まずは現場で起きている「物理的な現象」から紐解いていこうか。
—
なぜUPDATEでパフォーマンスが落ちるのか？
PostgreSQLのMVCC（多版型同時実行制御）の仕組みを思い出してほしい。PostgreSQLでは、UPDATEを行う際、実際には「古い行を削除して、新しい行を別の場所に書き込む」という手順を踏むんだ。
このとき、もし更新対象の行があるデータページ（8KBの領域）の中に、「新しい行を書き込むための空きスペース」がなかったらどうなると思う？
1. ページ溢れが発生： PostgreSQLは「新しい場所」を探して、別のページに書き込みに行く。
2. インデックスの肥大化： 本来なら同じページ内に収まれば済んだ話が、場所が変わることで、その行を指しているすべてのインデックス（B-tree）を更新しなきゃいけなくなる。
3. IOの爆増： これが高頻度で起きると、ディスクIOは飽和し、VACUUMの負荷も増大する。これが「更新地獄」の正体だ。
この「空き容量不足」をあらかじめ防ぐための設定が、`FILLFACTOR`なんだよ。
—
FILLFACTORって何者？
`FILLFACTOR`は、テーブルやインデックスの各データページを「何％まで埋めるか」を決めるパラメータだ。デフォルトは「100」。つまり、基本的には限界までデータを詰め込む設定になっている。
これを例えば「80」に設定すると、PostgreSQLは各ページに80%までしかデータを入れず、残りの20%を「UPDATE用の予備領域」として空けておいてくれる。
設定のコード例
テーブルを作成する時に指定するのは、至ってシンプルだ。
CREATE TABLE orders (
order_id serial PRIMARY KEY,
status text,
updated_at timestamp
) WITH (fillfactor = 80);
こうしておけば、UPDATEが発生したとき、同じページ内に新しい行を格納できる確率がグンと上がる。これを「HOT（Heap Only Tuple）更新」と呼ぶんだが、これが効くとインデックスの更新をスキップできる。パフォーマンスへの貢献度は計り知れないよ。
—
実践的な使い方のヒント
じゃあ、「じゃあ全部80にすればいいの？」と言われると、そうじゃない。ここが腕の見せ所だ。
- FILLFACTORを下げた方がいいケース
- UPDATEが頻繁に発生するテーブル（ステータス管理、カウンタなど）。
- 行のサイズが更新によって大きくなる可能性がある場合。
- デフォルト（100）のままでいいケース
- INSERTがほとんどで、UPDATEやDELETEが少ないログテーブル。
- 読み取り専用（ReadOnly）のマスターデータ。
下げすぎると、今度は「ページ数が増える＝フルスキャンが遅くなる＝キャッシュ効率が落ちる」というデメリットも出てくる。僕の経験則で言うと、激しい更新があるテーブルなら70〜85の間で調整するのが定石だ。
—
インデックスにも適用できることを忘れるな
`FILLFACTOR`はテーブルだけじゃなく、インデックスにも設定できる。
CREATE INDEX idx_orders_status ON orders (status) WITH (fillfactor = 90);
インデックスのページも、更新頻度が高いインデックスであれば少し余裕を持たせることで、インデックスの再構成（ページ分割）の頻度を抑えられる。これも微々たる差に見えて、高負荷時には効いてくるんだ。
—
先輩からのアドバイス：計測をサボるな
最後に一つだけ釘を刺させてほしい。「設定を変えるときは、必ず計測すること」。
`pg_stat_user_tables` の `n_tup_upd` や `n_tup_hot_upd` を監視して、HOT更新の比率が低いようなら、FILLFACTORを調整する余地があるサインだ。
— HOT更新の割合を確認するクエリ
SELECT relname,
n_tup_upd,
n_tup_hot_upd,
(n_tup_hot_upd::float / n_tup_upd::float) 100 as hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 0;
この `hot_ratio` が低いなら、設計を見直す良いチャンスだと思ってほしい。
データベースエンジニアの仕事は、魔法をかけることじゃない。物理的な仕組みを理解して、データの「住み心地」を整えてやることなんだ。ぜひ、次のチューニングで試してみてくれ。また現場で会おう。
コメント