「そのインデックス、本当に必要？」PostgreSQLの肥満を解消するインデックス・メンテナンスの話
データベースを運用していて、こんな経験はないだろうか？
「とりあえずクエリが遅いからインデックスを貼ろう」
「よくわからないけど、念のため複合インデックスも作っておこう」
……その結果、気づけばテーブルサイズが膨れ上がり、INSERTやUPDATEのたびにインデックスの更新コストで悲鳴を上げている――。僕も駆け出しの頃、まさにこの沼にハマって先輩に怒られたものだ。
PostgreSQLは非常に賢いデータベースだけど、ユーザーが「無駄なインデックス」というお荷物を背負わせれば、当然パフォーマンスは低下する。今日は、現場で僕が必ずと言っていいほどチェックする、`pg_stat_user_indexes` を使った「インデックスの断捨離」について話をしようと思う。
—
なぜインデックスを消す勇気が必要なのか
インデックスは、読み取り（SELECT）を爆速にするための魔法の杖だけど、書き込み（INSERT/UPDATE/DELETE）にとってはただの足かせだ。テーブルにデータが書き込まれるたびに、関連するすべてのインデックスを更新しなきゃいけないんだからね。
もし、そのインデックスがほとんどスキャンに使われていないとしたら？ それは、ただの「書き込み遅延の犯人」でしかない。
まずは「使われていないインデックス」を洗い出す
PostgreSQLには `pg_stat_user_indexes` という、インデックスの利用状況を記録している神のようなビューがある。これを見れば、そのインデックスが実際に役に立っているのか、それともただの「お飾り」なのかが一発でわかる。
さっそく、現場で役立つクエリを紹介しよう。
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 — 1回もスキャンに使われていない
AND indexrelname NOT LIKE ‘pg_toast_%’ — TOASTテーブル関連は除外
ORDER BY
idx_scan ASC;
このクエリを叩いてみてほしい。もし `idx_scan` が 0 のインデックスがずらりと並んだら……おめでとう。君のデータベースは、今すぐ軽量化できる伸びしろがあるってことだ。
「0回」のインデックスを即削除していいのか？
ここで一つ注意点がある。「idx_scan = 0 だからといって、脳死で DROP してはいけない」ということだ。
理由はいくつかある：
1. 稼働直後である: データベースを再起動した直後や、統計情報がリセットされた直後なら、まだカウントが溜まっていない可能性がある。
2. バッチ処理専用: 月に一度、深夜のバッチ処理でしか使われないインデックスかもしれない。
3. ユニーク制約: `UNIQUE` 制約のために張られたインデックスは、検索に使われなくてもデータ整合性のために絶対に必要なものだ。
だから、僕が現場でやる手順はこうだ。
1. 一定期間待つ: 最低でも直近のデプロイや月次処理を跨いだ期間（できれば1ヶ月程度）の統計を見る。
2. 制約を確認する: `pg_constraint` をチェックして、それが外部キーやユニーク制約と紐付いていないか確認する。
3. インデックスを「無効化」する: いきなり削除するのではなく、まずは `SET enable_indexscan = off;` などで様子を見るか、あるいは一旦削除して何かあったら即座に再作成できるスクリプトを用意しておく。
一歩踏み込んだ分析：書き込みコストとのトレードオフ
`idx_scan` が「100回」だったとしたらどうする？
「使われているから残すべきだ」と思うかもしれない。でも、もしそのテーブルが毎秒1,000件更新されるようなログテーブルだとしたら？
100回の検索のために、膨大な書き込みコストを支払い続けるのは割に合わないかもしれない。インデックスの有用性は「検索頻度」と「更新負荷」のバランスで決まる。ここを見極めるのが、ただの運用者と「データベースエンジニア」の境界線だ。
まとめ：定期的な健康診断を習慣に
インデックスの整理は、一度やって終わりじゃない。アプリケーションの機能が変われば、必要なインデックスも変わる。
- 半年に一度は `pg_stat_user_indexes` を確認する
- 「これ何のためにあるんだっけ？」をコードベースで確認する
- 使われていないインデックスは勇気を持って消す
この習慣があるだけで、データベースのパフォーマンスは劇的に変わる。無駄なインデックスを一つ消すだけで、CPU負荷がガクッと下がる瞬間は、エンジニアとして最高に気持ちがいいものだよ。
さて、君のDBには、今日でお役御免になる「お荷物」は眠っていないかな？ さっそく確認してみよう。
コメント