🗂

新卒3年目DBエンジニアが教えるDB基礎講座（Postgres編第4回：PostgreSQLにおけるテーブルと索引のメンテ）

2023/07/03に公開

PostgreSQL

RDB

tech

今回の内容

今回は短いと思いますが、テーブルとインデックスのメンテナンスをやっていこうと思います。
毎日使っていくとデータがたまってDiskが足らないだのCPU使用率が高いだとあると思いますが、今回はそういったレイヤーの話ではなく、もう少しPostgreSQLの内部の話に絞ってやっていければいいかなと思います。

今回はハンズオンないので読み流すだけでOKです。

では今回のアジェンダ以下の通りです。

1.テーブルのメンテナンス
2.インデックスのメンテナンス

1.テーブルのメンテナンス

そもそもなぜテーブルのメンテナンスのメンテが必要なのでしょうか。
というかテーブルのメンテナンスとはそもそも何なのでしょうか。

これを理解するためには個人的にPostgreSQLは追記型のアーキテクチャである
これを認識することが大事だと思っています。
追記型、ということはupdateしたら後ろに入ったらdeleteしても簡単に言うとフラグみたいなものが経つということになります。

つまり1~10までデータがあったとして1と4と9にしか入ってなかったらその間のやつはもう使われないやつになります。
こんなものさっさと消してFileSizeを減らしたほうがいいに決まっています。
また、FileSizeだけでなく読み込むときも無駄データを読み込むことになります。
つまり最後は性能低下につながるわけです。

VACUUM

まずはVACUUMに関してです。
基本的に今のPostgreSQLではAutovaccumであるのでそこまで困ることはないと思います。
昔は手動で大変だったみたいです。(その時代知らないので自分も言えないんですが、、、)
VACCUMの役割は「不要領域の削除」になります。

参考：https://www.techscore.com/blog/2013/05/27/postgresql-index-only-scan-奮闘記-その1/

詳しくは下記の通りです
1.各テーブルのページを先頭からチェック
2.VMをチェックして不要行あるならその行の情報を抜く。
3.索引のメンテを行って削除
4.FSMを更新する。この時末端ページがからなら切り詰める。

復習
**VM**
PostgreSQLの可視性マップ（Visibility Map）は、テーブルの各ページについて、全てのタプルがすべてのスナップショットに対して可視であるかどうかを追跡する高速化機能です。
これにより、バージョン情報（MVCC: Multi-Version Concurrency Control）をチェックすることなく、ページ全体をスキップすることが可能になり、これは特にVACUUMやインデックスのみのスキャン（Index Only Scans）といった操作でパフォーマンスを大幅に向上させます。
ビットが設定されている場合、そのページの全てのタプルは全ての可能なスナップショットに対して可視であることを意味します。そのため、そのページはVACUUM操作やインデックスのみのスキャンで無視することができます。

**FSM**
PostgreSQLにおけるFSM（Free Space Map）は、テーブルおよびインデックス内の各ページについて、利用可能な空きスペースを追跡するシステムです。
これは、新しいタプルの挿入や既存のタプルの更新に対して、どのページに十分な空きスペースがあるかを迅速に判断するために使用されます。
FSMは動的に更新されます。
つまり、タプルが挿入されたり、削除されたり、更新されたりすると、関連するページのFSMエントリが適切に更新されます。
しかし、完全に正確な情報を保持するためには、定期的なメンテナンス（たとえばVACUUM操作）が必要

VACCUMはXIDの周回する問題も避けるためにも使います。
PostgreSQLでは更新に関するトランザクションにはxidというidが付けられます。
これは40億程度あるのですが、周回した場合そのidで行った更新が見えないという事象が発生します。
避けるために「2.VMをチェックして不要行あるならその行の情報を抜く。」この部分でFREEZE処理を行います。これはVMを使って効率化されます。

FREEZE処理ではFREEZE操作は、特定のタプルが全ての現存するトランザクションに対して「古い」（つまり、そのタプルはこれらの全てのトランザクションに対して可視であり、その状態は変わらない）ことをマークします。これにより、そのタプルのトランザクションIDは特殊な「凍結された」IDに置き換えを行います。

参考:https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/tuningrule9-base/

さてこんな便利なVACCUMですが、コミットもロールバックも長い間していないトランザクションが存在していた場合行うことができません。VACCUMはそのタイミングで流れているXIDより古いものを対象にするのでこれより新しいもので大きな変更があっても削除できません。
こんな時はVACCUM FULLを行います。
なお、積極的にこの作業を行うことはアンチパターンですので避けてください。

VACCUM FULLでは以下を行います。
1.テーブルのデータをすべて取り出す
2.あたらしいテーブルに詰め込む
3.索引などを作る
4.テーブルを入れ替える

このようなVACCUMFULLですが、古いものと新しいものを同時に持つので容量は要監視です。
このような場合にはpg_dumpコマンドで論理バックアップを取るなりcopyコマンドを使いましょう

統計更新

PostgreSQLにも当然実行計画が存在していますが、この更新も実際の情報と統計が古ければ最適な実行計画をオプティマイザが選ぶことはできません。
よっておおきなテーブルの変更などがある場合は統計などを更新すべきです。
ただこれもデフォルトで閾値を超えると自動で走るのでそこまで気にしなくてもいいと思います。
手動で細かい設定をしたい場合はALTER TABLE SET STATICSを使えば問題ないです。
その時取りたい場合はANALYZE [Tablename];
と打っていただければと思います。

2.索引のメンテナンス

索引がだめならば結局性能低下を引き起こすのでこちらもやります。
これらが問題を引き起こすとしたらいかが考えられます
・肥大化
・断片化
・クラスタ性の低下

肥大化はテーブルファイルと同じで間に空白があり結局無駄なスペースが生まれてしまう問題です。

断片化は索引はB-Treeをイメージすべきです。左と右の木は半分づつになるべきですが、削除や更新があると必ず断片化が起こりキャッシュヒット率が下がります。

クラスタ性の欠如はテーブルファイルと索引ファイルの物理的の配置順序が異なることになります。

3つの問題とも結局はVACUUMをすれば任せていいのですが、１つのテーブルに複数の索引がある場合は断片化が起きがちなのでREINDEXを視野に入れましょう。つまりは再定義(作り直し)です。

REINDEX (VERBOSE) SCHEMA public;
REINDEX (VERBOSE) TABLE test;
REINDEX (VERBOSE) INDEX idx_01;

クラスタ性が下がっている場合は復活させリためにCLUSTERコマンドを使用します。
このコマンド発行時にはREINDEXも行るのでどちらか一方で構いません。
ただVACCUM FULLと同じ点に注意してください。(特に排他ロック取るとこ)

CLUSTER table01 USING idx_new