インデックスは「負債」になり得るか？――PostgreSQLにおけるインデックスのライフサイクル管理
データベースエンジニアとして長く現場に立っていると、「とりあえずインデックスを貼っておこう」という言葉を何度耳にしたかわかりません。確かに、初期段階のクエリパフォーマンス向上には魔法のような効果をもたらします。しかし、インデックスはタダではありません。書き込み時のオーバーヘッド、ストレージ容量、そして何より「保守という名のメンテナンスコスト」がのしかかってきます。
今日は、PostgreSQLにおいて、この「見えない負債」をどう管理し、健全な状態を保つかについて、少し深掘りしてみたいと思います。
—
pg_stat_user_indexes が語る真実
PostgreSQLには `pg_stat_user_indexes` という非常に優秀なビューがあります。皆さんも `idx_scan` や `idx_tup_read` を眺めて、「お、このインデックスはちゃんと使われているな」と確認したことはあるでしょう。
しかし、ここで一つ問いかけたいのは、「そのインデックスが本当に最適か？」という点です。
例えば、`idx_scan` がゼロではないものの、`idx_tup_read` と `idx_tup_fetch` の比率が極端に悪いケース。これは、インデックスが使われてはいるものの、インデックススキャン後にヒープ（テーブル本体）へのアクセスが多発している、つまり「インデックスが十分にカバーできていない」ことを示唆しています。
— よく使う監視用のクエリ
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
`idx_scan` が 0 のインデックスは、言わずもがな「無用の長物」です。ですが、ここで即座に `DROP INDEX` を実行するのは熟練エンジニアの流儀ではありません。統計情報がリセットされたタイミングや、バッチ処理でしか使われないインデックスを見落としている可能性があるからです。
「未使用」と判断する前に考えるべきこと
インデックスを削除する前には、必ず最低でも数週間から一ヶ月程度のスパンで監視を行うべきです。特に注意すべきは以下のケースです。
- 季節性のクエリ: 四半期ごとのレポートや、年末年始のバッチ処理でのみ使われるインデックス。
- 外部キー制約: `idx_scan` は増えなくても、データ整合性のために必要な場合があります（ただし、これは `pg_constraint` を見るべきですね）。
- 部分的インデックス: 特定のフラグが立ったレコードのみを対象にしたインデックスは、通常クエリでの利用頻度が低くても、特定の条件下で劇的な効果を発揮します。
インデックス削除の「安全なプロセス」
本当に不要だと確信が持てたら、いきなり削除するのではなく、まずは `pg_stat_user_indexes` をリセットして様子を見るか、あるいはインデックスを一度「無効化」するテクニックが有効です。
PostgreSQLでは、残念ながら `SET INDEX UNUSED` のような便利なコマンドはありませんが、以下のようなステップを踏むのが安全です。
1. 名前の変更: `ALTER INDEX idx_xxx RENAME TO idx_xxx_unused_tmp;`
2. 監視: 数日間運用し、アプリケーションエラー（インデックスがなくてクエリが遅延、あるいは失敗）が出ていないか確認。
3. 削除: 問題なければ削除。
このプロセスを経ることで、万が一のロールバック（名前を戻すだけ）が可能になります。
パフォーマンスチューニングのその先へ
インデックス設計は、単に「遅いクエリを速くする」だけの作業ではありません。「書き込み負荷をどれだけ許容し、どれだけ効率的に読み込ませるか」というトレードオフの最適化です。
特に PostgreSQL においては、HOT（Heap Only Tuple）更新の効率を維持するためにも、不要なインデックスを削ることは非常に重要です。インデックスが増えれば増えるほど、更新時にインデックスエントリの更新が発生し、HOT更新の確率が下がり、結果としてバキュームの負荷が増大する……という悪循環に陥ります。
「インデックスは貼るよりも、剥がす方が難しい」。
データベースの健全性を保つのは、新しい技術を導入することよりも、こうした地味なメンテナンスの積み重ねにあると私は信じています。皆さんのデータベースに眠る「負債」を、今一度見直してみてはいかがでしょうか。
—
著者プロフィール:
長年大規模トラフィックを捌くデータベースの設計・運用に従事。PostgreSQLの内部構造を追いかけるのが趣味。最近の悩みは、最適化しすぎたインデックスの管理運用フローをチームにどう浸透させるか。
コメント