PostgreSQLでのテーブル・インデックスメンテナンスについて内部構造からポイントを理解する

はじめに

本記事ではPostgreSQLで、

テーブルメンテナンス・インデックスメンテナンスについて

内部構造について図解しながらポイントをまとめました。

テーブルメンテナンス

まずはテーブルメンテナンスについて見ていきます。

テーブルメンテナンスが必要な理由

PostgreSQLでは追記型アーキテクチャであるため、

レコードの更新・削除を行なっても、

更新前の情報が不要領域として残り、

更新後の情報が登録されます。

データ更新時の追記型のイメージ

このPostgreSQLの追記型アーキテクチャのため、

発生した不要領域を削除したり、

どこが不要領域でどこが有効な領域なのかを、

を管理したりメンテナンスする必要があります。

テーブルメンテナンスを行うVACCUMの動作

VACUUMとは、こんな感じ

VACUUMの動作イメージ

VACUUMでは空き領域が発生した時に、

空き領域を再利用可能にしています。

(トランザクションID周回問題回避の役割も担っていますが、

その問題にぶち当たることはそうそうないと思うので今回そこの説明は省略)

VACUUMは可視性マップ(VM)を利用して、

不要領域があり、再利用可能にする対象テーブルを判定したり、

空き領域マップ(FSM)に空き領域の大きさをカテゴリ分けして

空き領域情報を更新します。

FSMとVMイメージ図

これまで説明してきたVACUUMは、

自動VACUUMで、

設定に従って

テーブルの不要領域(非アクティブ)レコードの割合、

テーブルの更新レコードの割合

閾値を超えた際に自動的に起動します。

自動VACUUMではページロックをしてメンテナンスしているので、

他ページに対しての更新などは行いながらVACUUMができます。

また、VACUUMにより不要領域を再利用可能にしたら、

インデックスファイルの方の更新や、

テーブル統計情報の更新も行われます。

自動VACUUM設定における注意点とVACUUM FULL対策

基本的にはここまで見てきた自動VACUUMにより、

自動的に全テーブルの不要領域がメンテナンスされていくのですが、

例えば、

更新が頻繁に行われるテーブル(VACUUMが自動で頻繁に行われる)

によって日中帯に運用による負荷をデータベースにかけたくない場合、

テーブルを指定して自動バキュームの対象から外す
テーブルを指定して自動バキューム実行の閾値を調整する

など、デフォルト設定から細かく設定し直す必要があります。

また、自動VACUUMによるテーブルメンテナンスが機能されない場合は、

VACUUM FULLが動作します。

そもそも、

VACUUM FULLが発生することは異常異常事態

(発生しないようにすべき)なのですが、

VACUUM FULLが発生するということはこのようなことが起きます。

実行中は該当テーブルが排他ロックされる(参照・更新負荷)
大きいテーブルだと数十分〜数時間かかる
テーブルやインデックスをコピーすることになる(一時的にディスク使用量が倍になる)

FULL VACUUMが発生してしまう原因としてはLong Transactionがあります。

Long Transactionとは、

バッチ処理などでトランザクション開始後、

長時間コミットやロールバックがされずに、

長時間存在してしまうトランザクションです。

Long Transaction実行中は、

他トランザクションでレコード更新が実行されても、

自動VACUUMが正常に起動しないため、

VACUUM FULLが必要になってしまいます。

特にオンラインから頻繁に更新されるタイミングの裏で、

バッチ処理で長時間かける必要があるようなケースでは、

負荷試験でVACUUM FULLが発生していないか？？

に注意が必要です。

インデックスメンテナンス

ここからはインデックスメンテナンスについて見ていきます。

インデックスメンテナンスが必要な理由

インデックスメンテナンスは以下の3つのケースを防ぐことで、

アクセス性能を維持するためです。

インデックスファイルの肥大化
インデックスファイルの断片化
インデックスファイルのクラスタ性劣化(データが順番に辿れなくなる)

インデックスファイル肥大化による影響と改善策

テーブル更新時と同様にPostgreSQLの追記型アーキテクチャのため、

インデックスファイルも不要領域を消していく必要があります。

通常は自動VACUUMにより不要領域は自動メンテナンスされるので、

あまり気にすることなく自動メンテナンスされます。

インデックスファイル断片化による影響と改善策

インデックスファイルは以下の感じで登録されています。

インデックスファイルイメージ

データが増えると、

ツリーのインターなるやリーフが分割し、

ページも分かれていきます。

インデックスを貼っているカラムが順番に必ず登録されるなら、

無駄なく左から順に空きなしで登録されていきますが、

実際のインデックスは、

バラバラに更新されたり、論理的に間に登録されたり

運用しているうちにページが分かれて、断片化します。

断片化すると、

論理的に近いデータが物理的に少し離れた箇所に格納されるので、

キャッシュヒット率が下がり検索性能も低下することになります。

この断片化は完全に防ぐことはできないので、

REINDEXコマンドでインデックスを再定義します。

ただし、

オプションなしでのREINDEX中はテーブルロックをかけるので、

例えば裏でオンライン処理を起動したままREINDEXする場合、

CONCURRENTLYオプションをつけて、

ロックをかけないようにすることに注意が必要です。

インデックスファイルのクラスタ性劣化による影響と改善策

クラスタ性とは、

テーブルデータの物理的な並び順を指し、

インデックスの並びとテーブルデータの並びが近いと

より効率的に検索できます。

データベースを運用している間に、

テーブルデータの並びとインデックスとの並びが乖離してきて、

インデックススキャンの場合でも

I/O回数が増加してしまいます。

(ここイメージ図があるといいかな。。。)

クラスタ性を改善するにはCLUSTERコマンドを実行すれば良いのですが、

VACUUM FULLとほぼ同じロジックがCLUSTERコマンドでは実行されるため、

以下に注意が必要です

一時的にテーブルとインデックスのディスク容量が倍増する
CLUSTER実行時は排他ロックをかける

まとめ

今回はテーブルメンテナンスとインデックスメンテナンス

について少しだけ内部構造も見つつまとめてみました。

リリース前の負荷試験では、

オンラインだけでなくバッチ処理を同時実行するシナリオも流す必要があり、

VACUUM FULLを行なってしまっていないかをチェック。

実際の運用の中では、

テーブルやインデックスメンテナンスは、

基本的には自動VACUUMで行い、

運用の中で性能劣化が検出されたら、

REINDEXやCLUSTERを行う必要があるかの観点でも調査。

この辺りも意識してアプリケーション開発や保守開発

が行えるアプリケーションエンジニアチームに一人がいてくれると

とても心強いです！！！

以上、参考になれば幸いです。

データベース関連の現場で役立つまとめ記事を作ったので、

ぜひこちらも参考にしてみてください。

2022年3月21日現場で役立つデータベースの基礎知識まとめ

参考文献

リンク