「最近、クエリが重くない？」インデックス肥大化との付き合い方
現場でPostgreSQLを触っていると、運用開始からしばらく経った頃に「あれ、なんか最近インデックスが効いていない気がする？」なんて違和感を覚えることはないかな？
実はそれ、気のせいじゃないかもしれない。PostgreSQLのインデックス（特にB-tree）は、更新や削除を繰り返すと少しずつ「隙間」ができて肥大化していくんだ。これを放置すると、検索性能が落ちるだけじゃなくて、ディスク容量も無駄に食うし、バックアップの時間も延びるという、地味だけど厄介な爆弾になる。
今日は、そんな「インデックスの肥大化」をどうやって見つけ出し、どう対処すべきか、僕が現場でよく使う手法をシェアするよ。
—
なぜインデックスは「太る」のか？
PostgreSQLのMVCC（多版同時実行制御）の仕組みを思い出してほしい。データが更新（UPDATE）されるたび、古い行はそのままに新しい行が作られるよね。これと同じように、インデックスも更新されるたびに古いエントリーが残り、新しいエントリーが書き込まれる。
もちろん、PostgreSQLには「HOT（Heap Only Tuple）」のような賢い仕組みがあるけれど、インデックスのキー値が更新されたり、断続的に書き込みが発生したりすると、どうしてもページ内に「もう使われていない領域（デッドスペース）」が溜まっていくんだ。これが「断片化」の正体だね。
—
まずは「肥大化」を可視化しよう：pgstattupleの出番
勘で「インデックスが重いかも？」と判断するのは危険だ。まずは `pgstattuple` という拡張を使って、数字で現状を把握しよう。
— 拡張がまだならインストール
CREATE EXTENSION IF NOT EXISTS pgstattuple;
— インデックスの統計情報を取得
SELECT FROM pgstatindex(‘インデックス名’);
このコマンドを叩くと、いろいろな数字が出てくる。中でも注目すべきはここだ。
- avg_leaf_density: リーフページにおけるデータの密度。ここが低い（例えば60%以下とか）なら、かなり無駄が多い状態だと言える。
- fragmentation: その名の通り断片化率。ここが高いなら、インデックスがスカスカになっている証拠だね。
「断片化が30%を超えていたら再構築を検討する」というのが、僕の現場でのひとつの目安かな。
—
解決策：REINDEXの考え方
肥大化がひどいと分かったら、次は再構築だ。ここで一番シンプルなのは `REINDEX` コマンドだね。
— 特定のインデックスを再構築
REINDEX INDEX CONCURRENTLY インデックス名;
ここで大事なのは `CONCURRENTLY` をつけること。これを忘れると、再構築中にテーブルがロックされてしまい、本番環境で悲劇が起きる。「あ、しまった！」と叫ぶ前に、このオプションだけは指に覚え込ませておいてほしい。
ただし、`REINDEX` は万能じゃない。以下の点には注意が必要だ。
1. I/O負荷: 再構築はインデックスをイチから作り直すから、ディスクI/Oが激しくなる。ピークタイムにやるのは自殺行為だよ。
2. 一時的な肥大化: `REINDEX` 中は新しいインデックスと古いインデックスが共存するから、一時的にストレージを食う。空き容量がギリギリの時は要注意だ。
—
本当に再構築が必要か？一度立ち止まろう
さて、ここで先輩からのアドバイス。「数字が悪いからといって、すぐに再構築するな」。
インデックスの再構築にはコストがかかる。もしそのテーブルへの更新が非常に激しいなら、再構築してもすぐにまた肥大化してしまう。「いたちごっこ」になるぐらいなら、まずは運用を見直すべきだ。
- fillfactorの設定: インデックス作成時に `FILLFACTOR` を調整して、ページ内に余裕を持たせて更新時の再配置を減らす手法も有効だ。
- インデックスの断捨離: そもそも、そのインデックスは本当に使われている？使われていないインデックスを消すのが、実は一番のチューニングだったりする。`pg_stat_user_indexes` を見て、`idx_scan` がゼロに近いインデックスがないか探してみよう。
まとめ
1. 計測する: `pgstattuple` で現状を定量的に把握する。
2. 判断する: 肥大化率だけを見ず、検索性能への影響や更新頻度を考慮する。
3. 安全にやる: `REINDEX` を使うなら必ず `CONCURRENTLY` を忘れずに。
4. 根本を見る: 再構築より、不要なインデックスを消す方が先じゃないか確認する。
データベースのチューニングは、料理の味付けに似ている。闇雲に調味料（コマンド）を足すんじゃなくて、まずは素材（データ）の状態をしっかり味わうことが、最高のパフォーマンスを引き出すコツだよ。
また何か困ったことがあったら、いつでも聞いてくれ。一緒にいいデータベースを育てていこう！
コメント