インデックスの「腐敗」と向き合う：PostgreSQLの肥大化をどう読み解くか
PostgreSQLで高負荷なシステムを長く運用していると、必ずと言っていいほど「インデックスの肥大化」という壁にぶつかります。クエリのパフォーマンスがじわじわと低下し、なぜかインデックススキャンが遅い。実行計画（EXPLAIN）を確認しても、コスト見積もりが実態と乖離している……そんな経験はありませんか？
今日は、そんな「インデックスの腐敗」を可視化し、いつ再構築（REINDEX）すべきかという、エンジニアの経験則に頼りがちな領域を、データに基づいて判断するための話をしようと思います。
なぜインデックスは「太る」のか
B-treeインデックスの内部構造を思い出してください。PostgreSQLのB-treeは、更新（UPDATE）や削除（DELETE）が繰り返されると、ページ内に「デッドタプル（削除済みレコードの残骸）」が蓄積されます。
もちろん、PostgreSQLにはHOT（Heap Only Tuple）更新という素晴らしい仕組みがありますが、インデックスに紐づくカラムが更新される場合や、長期間運用で断片化が進むと、ページ内の空きスペースは増える一方で、インデックス全体の容量だけが肥大化していきます。これが「インデックスの断片化」です。
結果として何が起きるか。単にディスク容量を食うだけではありません。
- キャッシュ効率の低下: インデックスサイズが大きくなると、メモリ（shared_buffers）に乗り切らなくなり、物理I/Oが激増します。
- スキャンの無駄: ページ内に有効なデータが少ないため、スキャン時に無駄なページを読み込むことになり、論理I/Oコストが跳ね上がります。
`pgstattuple` で「健康診断」をする
勘や経験で `REINDEX` を打つのは、もう卒業しましょう。まずは正確な「健康状態」を数値化することから始めます。ここで最強の相棒になるのが、標準的な拡張機能である `pgstattuple` です。
以下のクエリで、特定のインデックスがどれだけ「無駄」を抱えているかを確認できます。
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT FROM pgstatindex(‘idx_your_table_column’);
ここで注目すべきは以下の数値です：
- `avg_leaf_density`: リーフページの密度。これが低い（例えば60%以下など）場合、そのインデックスはスカスカの状態です。
- `fragmentation`: 断片化率。これが高いほど、再構築の恩恵を受けやすいと言えます。
判断基準：いつ「再構築」を実行すべきか
「断片化しているから即REINDEX」というのは、実はあまり賢い選択ではありません。特に大規模な環境では、インデックスの再構築自体が重い負荷となり、ロックやI/Oスパイクを誘発します。
私は、以下のステップで判断を下すようにしています。
1. 密度のしきい値: `avg_leaf_density` が 50-60% を下回り、かつテーブル全体のアクセス頻度が高い場合。
2. 実行計画への影響: `EXPLAIN ANALYZE` を見て、インデックススキャンの際の `Shared Hit` よりも `Read` が不自然に多い場合。
3. コストの再評価: そもそもそのインデックスは、現在のクエリパターンに対して最適か？（不要なインデックスは削除するのが最大の最適化です）。
運用上の「裏技」：オンライン再構築の活用
もし再構築を決断したなら、間違っても `REINDEX TABLE` をそのまま実行してはいけません。本番環境であれば、必ず `CONCURRENTLY` を使いましょう。
REINDEX INDEX CONCURRENTLY idx_your_table_column;
これを使えば、インデックス構築中もテーブルへの書き込みをブロックしません。ただし、実行時間は通常より長くなり、一時的にインデックスが2つ存在する状態になるため、ディスク容量と実行時間に余裕を持って実施するのが鉄則です。
最後に：エンジニアの直感とデータの融合
データベース運用において、「動いているから触らない」というのは、ある種の怠慢です。インデックスの肥大化は、システムの「血栓」のようなもの。放っておけば、いつかクエリのレイテンシという形で症状が現れます。
`pgstattuple` を使った定期的なモニタリングをルーチンに組み込み、数値が一定のラインを超えたらメンテナンスを行う。そうした「地味な積み重ね」こそが、数年後も安定して稼働する堅牢なデータベースを作る唯一の道だと、私は確信しています。
皆さんのインデックスは、今、どれくらい「健康」ですか？ぜひ今のうちに確認してみてください。
コメント