PostgreSQLのUPDATEの特徴とfillfactorをわかりやすく説明してみました
AWSのaurora PostgreSQLを使ったシステムで性能チューニングを行う機会がありましたのでそこで得た知識をまとめていこうと思います。
まずはfillfactorからです。
この記事では技術にあまり詳しくなくてもイメージできることを目的としています。
大まかに理解したうえで詳しい資料を読む方が理解しやすいですよね。
なので、意図的に技術的な詳細を省略・簡略化している個所があります。
技術的な詳細は参考URL等を参考にしてください。
利用したPostgreSQLバージョンは13.7です。
要約
- fillfactorを100以外で設定するとUPDATEを早くできる可能性がある。
その分、ディスク容量が余計に必要になる。 - PostgreSQLは仕組み上UPDATEが遅いため、大きな性能向上につながる場合がある。
- fillfactorとは「充填率＝どのくらいデータを詰め込んで格納するか」を指定する設定値。1～100で指定可能。デフォルトは100。
- 端的な言葉だとこうですが言葉だけだとイメージしづらいので説明したのがこの記事です。
PostgreSQLのデータ格納とfillfactor
fillfactorを理解するためにはPostgreSQLのデータ格納を理解する必要があります。
PostgreSQLのデータ格納のポイントは以下です。
- ブロックという固定サイズ（デフォルト8k）で領域を用意し、この単位で管理。
- データは1行ずつブロックに格納。
- fillfactorはデフォルト100(100%のこと)で詰め込めるだけ詰め込む
- fillfactorを50(=50%)とすると、50%までは詰め込み、残りは空けておく
fillfactor=100(100%)の場合
ブロックにデータをできるだけ詰め込みます。
fillfactor=50(50%)の場合
データを格納するのはブロックの50%までで、最低50%は空けておくように格納します。
これによりブロックが２つ必要になっています。
ここまでだと単純に無駄なデータ領域が必要になっているだけですね。
PostgreSQLのUPDATEの特徴
fillfactorの効果を説明する前に、ここでいったんPostgreSQLの特徴を説明します。
特定の項目をUPDATE文で変更する場合、通常だと「その項目だけを変更」するのをイメージすると思います。Oracle等はこの形式です。
ですが、PostgreSQLは「追記型アーキテクチャ」というものを採用していて、新たな一行データが追加されることになります。
通常イメージするUPDATE
更新する項目の値だけが変更される。
PostgreSQLのUPDATE
更新があったらその内容の1行データが新たに追加される。
ブロック２の領域を新たに確保し、さらに1行分丸ごとのデータ書き込みが発生します。
PostgreSQLのUPDATEではこのように「新たな領域の確保」「1行分のデータ書き込み」が発生するため、UPDATE処理は他のDBMSに比べて遅い傾向があります。
fillfactorを100以外に設定した場合の挙動
上記例のUPDATEですが、fillfactorで空き領域を残しておくのがここで生きてきます。
fillfactor=50の場合だと以下のようになります。
この場合、fillfactor=100に比べて以下のような違いがあります。
- 新たな領域確保が発生しない（すでに確保済みであるため）
- 同一ブロック内での変更になる
こうした条件を満たしたUPDATEは「HOT」と呼ばれ、UPDATE処理を早く行うことができます。
（HOTを詳しく知りたい方は参考URLの記事を参照ください）
fillfactorを100以外を設定することでUPDATE処理が早くなるのはこの「HOT」が使えた場合です。
fillfactorをデフォルトから変更すべきかどうかの判断基準
fillfactorを設定しHOTが効くようにすることで性能改善につながりそうなことはわかってきたかと思います。
後はfillfactorをどのように設定するかがテーマになります。
ここでは、まずは大まかに「fillfactorをデフォルトから変更すべきかどうか」の考え方を記載します。
判断材料：fillfactor設定での性能改善の効果量
fillfactorを設定しHOTが効けばUPDATEの処理が早くなると書きましたが、その効果はどの程度なのかが重要です。
私の担当システムで実測したところ1件での効果は微々たるもので1万件でも数秒早くなる程度でした。
PostgreSQLのバージョンや1行のデータ量等にも左右されますが、1万件程度の更新であればfillfactorを気にする必要はなさそうに思います。
上記のことから、オンライン画面上で操作できる程度の件数でデータ更新するのが中心のシステムなどもHOTが効くかどうかは気にする必要がなく、fillfactorもデフォルトの100で問題ないと思います。
なので、fillfactorを設定して効果が大きいのは大量データを更新するバッチ処理、さらにその処理が多数ある場合などになると思います。
ちなみに、私の担当したのはまさに大量データ更新を行っており、数百万件のデータに対し何度もUPDATEを行っているバッチ処理でした。
HOTが効くようにfillfactor設定をしたところ、全体としては10分を超える性能改善になりました。
おわりに
今回はここまでにします。
fillfactorをいざ設定変更しようとする場合の調査方法等は技術者向けになりますので別記事として書こうと思います。
fillfactorの説明自体はググれば詳しく書いてくださっている記事も多いのですが、どうしても技術者向けです。
私は技術にそれほど詳しくない人にも説明して理解してもらう必要がある立場なので、その観点だと既存の記事は詳細過ぎてポイントがわかりづらく感じたので、この記事ではわかりやすさを重視して書いたつもりです。
「ここをもう少しわかりやすく説明があると良い」などのアドバイスやコメントをいただければありがたいです。
参考URL
HOTの仕組みについては以下が詳しいです。
Discussion