PostgreSQLのFILLFACTORとHOTについて

調べたことまとめ。


FILLFACTORとは

FILLFACTOR とは、ページにテーブルやインデックスを格納する際、敢えて空き領域を作成していく仕組みとなります。公式ページの説明です。

テーブルの場合。

テーブルのフィルファクタ(fillfactor)は10から100までの間の割合(パーセント)です。 100(すべて使用)がデフォルトです。 より小さな値を指定すると、INSERT操作は指定した割合までしかテーブルページを使用しません。 各ページの残りの部分は、そのページ内の行の更新用に予約されます。 これによりUPDATEは、元の行と同じページ上に更新済みの行を格納することができるようになります。 これは別のページに更新済みの行を格納することよりも効率的です。 項目の更新がまったくないテーブルでは、すべてを使用することが最善の選択ですが、更新が非常に多いテーブルではより小さめのフィルファクタが適切です。

PostgreSQL - CREATE TABLE

インデックスの場合。

インデックス用のフィルファクタは割合(パーセント)で、インデックスメソッドがインデックスページをまとめ上げる時にどの程度ページを使用するかを決定するものです。 B-treeでは、リーフページは初期インデックス構築時と右側(新しい最大キー値を追加する方向)にインデックスを拡張する時にこの割合分までページを使用します。 その後ページすべてが完全に使用されると分割され、インデックスの効果が徐々に劣化します。 B-treeのデフォルトのフィルファクタは90ですが、10から100までの任意の整数値を設定することができます。 テーブルが静的な場合、100が最善でインデックスの物理サイズを最小化できます。 更新が非常に多い場合は、ページ分割の頻度を少なくするために、より小さなフィルファクタを設定する方が良いです。

PostgreSQL - CREATE INDEX

では、どんな程度の値を設定すべきなのか、ネットを彷徨ってみたのですが、明快な回答は見つかりませんでした。以下のサイトでも何%とすべきか質問の投稿がされていますが、回答はありませんでした。。。

What Is the Best Value for Your Fill Factor?

とりあえず、インターネット上の集合知を踏まえると、こんな感じなのでしょうか。

  • テーブルのFILLFACTORは、70%から80%にしておく
  • 2レコード分の2倍ほどの空き領域を設定しておく
    • そうすれば、空き領域を交互に使う構成となるので、新規ページへ書き込みになりにくくなる

HOTとは

FILLFACTORという空き領域の話をしたところで、HOT(Heap Only Tuple) という仕組みの話。HOTの実行には、以下の条件と効果を持ちます。

  • 条件
    • インデックスを持たない列への更新処理時
    • 更新対象行と同じページに空き領域がある
  • 効果
    • インデックス側への更新処理をスキップ
    • VACUUM処理を待たずに、dead tupleとなったレコードを再利用可能領域とする

「インデックスを持たない列への更新」なら、そもそもインデックスは更新されないでしょ、と一瞬思うのですが、PostgreSQLは追記型アーキテクチャのため、どの列が更新される場合でも更新レコードの情報がページに追記されていきます。そのため、インデックス側で持つテーブルデータへのポインタ情報(Oracle DBでいうrowid)が必ず変更となり、インデックス側データでも更新が必要となるのでした。HOTの機能は、新旧レコード間でリンク情報を貼ってあげることで、このインデックス側の更新処理を不要とするものらしいです。

次に、では何故Vacuum処理を待たずに、dead tupleを再利用可能とできるのか。Vacuum処理時には、そのdead tupleが本当に他から参照されていないか、確認する処理が実行されているらしいです。インデックス側からポインタが貼られていないか、といった点を確認しているらしいです。HOT機能で不要領域となったtupleは、上記の仕組みよりインデックスから参照されていないことが明確なため、Vacuum処理を待たずに、ポスグレ側で再利用可能領域に変更してくれるそうです。