初めに
インデックスは以下の理由からメンテナンスをする必要があります。
インデックスの肥大化
PostgreSQLのインデックスは、データベースのテーブルに対する操作（INSERT、UPDATE、DELETEなど）によって徐々に大きくなることがあります。これをインデックスの肥大化と言います。肥大化したインデックスはディスクスペースを無駄に消費し、インデックスの検索性能を低下させる可能性があります。
インデックスの断片化
テーブルのデータが頻繁に更新されると、インデックスのページ内に未使用のスペース（断片）が多くなることがあります。これをインデックスの断片化と言います。断片化したインデックスは、データの物理的な配置がバラバラになるため、データの読み込み性能を低下させる可能性があります。
インデックスのクラスタ性の欠落
テーブルのデータが頻繁に更新されると、インデックスの順序とテーブルのデータの物理的な順序が一致しなくなることがあります。これをクラスタ性の欠落と言います。クラスタ性が欠落したインデックスは、テーブルの全体的なパフォーマンスを低下させる可能性があります。
メンテナンスの実施
以下のような操作を行うことが推奨されます。
-
VACUUMコマンド: テーブルとインデックスの未使用スペースを回収し、再利用可能にします。 -
REINDEXコマンド: インデックスを再構築し、肥大化や断片化を解消します。 -
CLUSTERコマンド: テーブルのデータをインデックスの順序に従って物理的に再配置し、クラスタ性を改善します。
ではメンテナンスをどのようなときに実施すればいいのでしょうか？
メンテナンスの実施判断基準
肥大化についてautovacuumで定期的に実行されることが多いと思うので省きます。
-
インデックスのクラスタ性の欠落
インデックスのクラスタ性の欠落に関しては事前に判断することが現状はできないため、CLUSTERコマンドを利用する際に、前後でEXPLAIN (ANALYZE, BUFFERS)を実施して比較、パフォーマンスが改善していたらクラスタ性の欠落があったと判断する。 -
インデックスの断片化
- テーブルのレコード数を確認します。これは、
SELECT COUNT(*) FROM テーブル名;というSQL文を実行することで取得できます。 - テーブルの1レコードあたりの平均サイズを確認します。これは、
pg_column_size()関数を使用して、各レコードのサイズを確認し、それらの平均値を計算することで取得できます。 - テーブルのレコード数と1レコードあたりの平均サイズを掛けることで、テーブル全体のデータ量を予想します。
- インデックスの期待サイズを予想するには、テーブル全体のデータ量に対して一定の比率を掛ける方法が一般的です。この比率は、インデックスの種類や設定、使用されているハードウェアなどにより異なりますが、一般的にはテーブル全体のデータ量の10%〜30%程度とされることが多いです。
上記はインデックスと利用する前提でまとめてますが、そもそも使われていなければ削除しておくことをお勧めします。
まとめ
PostgreSQLのインデックスは、適切なメンテナンスを行うことで、そのパフォーマンスを最大限に引き出すことができます。肥大化、断片化、クラスタ性の欠落といった問題を理解し、それぞれの対策を適切に実施することで、データベース全体のパフォーマンスを向上させることができます。
参考