「インデックスが遅い？」その原因、実は“肥大化”かもしれません。PostgreSQLのインデックス・メンテナンスを極める
現場でバリバリコードを書いていると、ふとこんな瞬間にぶつかりませんか？
「昨日までサクサク動いていたクエリが、なぜか最近重い」
「データ量はそこまで増えていないのに、インデックスのサイズだけが肥大化している」
もし心当たりがあるなら、それはPostgreSQL特有の「インデックスの肥大化（Bloat）」が原因かもしれません。今日は、経験の浅いエンジニアが意外と見落としがちな、PostgreSQLのインデックス・メンテナンスという「縁の下の力持ち」な仕事について、現場の知恵を共有しようと思います。
—
なぜインデックスは「太る」のか？
PostgreSQLはMVCC（多版同時実行制御）という仕組みを採用しています。これは素晴らしい設計なのですが、副作用として「更新のたびに古いデータが残る」という性質があります。
テーブルのデータが変わると、インデックスも更新されます。しかし、PostgreSQLのインデックスは「古いエントリを今すぐ消す」という効率的な処理が難しく、不要になったエントリがゴミとして残り続けます。これが積み重なると、インデックスのサイズが物理的に膨らみ、スキャン効率がガタ落ちする。これが「肥大化（Bloat）」の正体です。
まずは「検知」から始めよう
闇雲にメンテナンスするのではなく、まずは今の状況を数値で把握しましょう。PostgreSQLには `pgstattuple` という便利な拡張機能があります。
— 拡張機能が未導入ならインストール
CREATE EXTENSION IF NOT EXISTS pgstattuple;
— 特定のインデックスの肥大化状況を確認
SELECT FROM pgstatindex(‘index_name’);
ここで注目すべきは `avg_leaf_density` です。これが80〜90%を下回っている場合、かなり中身がスカスカになっている証拠。パフォーマンスに影響が出始めているサインです。
—
解決策1：VACUUMの正しい理解
「とりあえずVACUUMすればいいんでしょ？」と思うかもしれませんが、注意が必要です。
標準の `VACUUM` は、インデックス内の不要なエントリをマークして再利用可能にします。しかし、インデックス自体を縮小してディスク容量を解放するわけではありません。
つまり、VACUUMは「これ以上の肥大化を防ぐための日常的なケア」であって、「すでに肥大化したインデックスを直す特効薬」ではないんです。自動バキューム（autovacuum）が適切に動いていれば、ある程度の肥大化は抑えられますが、激しい更新があるテーブルでは限界があります。
—
解決策2：REINDEX CONCURRENTLY でスマートに再構築
肥大化したインデックスを物理的に綺麗さっぱり作り直したいとき、最強のコマンドが `REINDEX` です。ただし、昔ながらの `REINDEX TABLE` は、処理中にテーブルをロックしてしまうので、本番環境で使うとサービスが停止してしまいます。
そこで必ず覚えておいてほしいのが、`CONCURRENTLY` オプションです。
— テーブルをロックせずにインデックスを再構築
REINDEX INDEX CONCURRENTLY idx_users_email;
これを使えば、裏側で新しいインデックスを構築してから古いものと入れ替えてくれるので、読み書きを止めることなく安全にメンテナンスできます。
※ただし、処理時間はかかるしCPUやI/Oを食うので、DBの負荷が低い時間帯を狙うのが「現場の作法」です。
—
現場で役立つチェックリスト
最後に、運用で気をつけているポイントをまとめておきます。
1. インデックスの数は適正か？：肥大化の最大の原因は「使われていないインデックス」です。`pg_stat_user_indexes` を確認して、`idx_scan`（スキャン回数）が極端に少ないインデックスは削除を検討しましょう。
2. 更新頻度が高いカラムにインデックスを貼っていないか？：頻繁に更新されるカラムにインデックスを貼ると、肥大化のスピードが跳ね上がります。本当に必要なインデックスか、常に問いかけましょう。
3. 定期的なメンテナンス計画：アクセスが少ない時間帯に、`REINDEX CONCURRENTLY` を定期実行するスクリプトを組むのが、中規模以上のシステムでは一般的です。
—
最後に：メンテナンスは「掃除」と同じ
インデックスのメンテナンスは、部屋の掃除と一緒です。毎日少しずつ片付けていれば（autovacuum）、年末の大掃除（REINDEX）も楽になります。
DBパフォーマンスのチューニングというと、SQLの書き換えやインデックスの追加ばかりに目が行きがちですが、こういった「足元のメンテナンス」こそが、長期間安定して稼働する堅牢なシステムを作る秘訣です。
何か疑問があれば、いつでも聞いてくださいね。一緒に現場のDBを快適に保っていきましょう！
コメント