【実務・中級編】インデックスの肥大化（Bloat）と対策 – PostgreSQL

やあ。最近、データベースのパフォーマンスがなんとなく落ちてきたな、なんて悩んでいないかい？

「インデックスもちゃんと貼っているし、クエリも最適化したはずなのに、なぜか遅い」。そんな時に真っ先に疑うべき犯人の一人が、インデックスの肥大化（Bloat）だ。

今日は、PostgreSQLの運用で避けては通れないこの「見えない敵」との向き合い方について、現場の知見を共有しようと思う。教科書通りの理屈だけでなく、実際にどう検知して、どう対処すべきか。実戦的な話をしていこう。

—

PostgreSQLのMVCC（多版型同時実行制御）の仕組みを思い出してほしい。PostgreSQLはデータの更新（UPDATE）が発生すると、古いレコードを消すのではなく、新しいレコードを書き込むという挙動をする。

インデックスも同じだ。データが更新されると、インデックス側も「新しいポインタ」を指すように書き換えられる。その際、古いインデックスエントリはすぐには物理削除されず、ゴミとして残ってしまう。これが積み重なると、インデックスの中に「スカスカの領域」がどんどん増えていく。これが「肥大化」の正体だ。

クエリを投げると、本来なら数ページで読めるはずのインデックスを、この肥大化したゴミのせいで何倍ものページを読み込む羽目になる。結果として、I/O負荷が跳ね上がり、レスポンスが劣化するわけだ。

—

まずは現状把握だ。なんとなく「肥大化している気がする」という勘で作業するのはNG。PostgreSQLには `pgstattuple` という便利な拡張機能がある。これを使えば、インデックスの「有効活用率」が一発でわかる。

— 拡張機能をインストール（まだ入っていなければ）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

— インデックスの統計情報を確認
SELECT FROM pgstatindex(‘インデックス名’);

ここで注目すべきは `avg_leaf_density`（リーフページの密度）だ。これが極端に低い（例えば50%以下とか）なら、そのインデックスはかなり太っている可能性が高い。

—

肥大化を解消する方法は大きく分けて2つ。状況に応じて使い分けるのがプロの流儀だ。

PostgreSQLの `autovacuum` が正常に動いていれば、ある程度のゴミは自動で回収される。だが、更新頻度があまりに高いテーブルだと、autovacuumが追いつかないことがある。

まずは、設定値を見直そう。`autovacuum_vacuum_scale_factor` を小さくするなどして、掃除の頻度を上げるのが第一歩だ。

それでも解消しない、あるいは特定のタイミングで強制的に綺麗にしたい場合は `REINDEX` を使う。

ここでのポイントは、必ず `CONCURRENTLY` をつけることだ。これがないと、インデックスの再構築中にテーブルがロックされてしまい、本番環境なら「サービス停止」という大事故につながる。

— 本番環境では必ずCONCURRENTLYを使う！
REINDEX INDEX CONCURRENTLY インデックス名;

`CONCURRENTLY` を使うと、少し時間はかかるが、読み書きをブロックせずに裏側で新しいインデックスを作って差し替えてくれる。これぞまさに、現場で使える「止まらないメンテナンス」の鉄板だ。

—

最後に一つだけ、アドバイスがある。

「闇雲にREINDEXを打ちまくるな」ということだ。

REINDEXは万能薬のように見えるけれど、実行中はそれなりのI/O負荷がかかる。もし肥大化のスピードが速すぎるなら、それはインデックスの問題というより、データ設計や更新頻度そのものに無理があるサインかもしれない。

まずは `pgstattuple` で本当に困っているかを定量的に確認し、本当に必要な場所だけに絞ってメンテナンスする。そんなふうに、冷静にDBの状態と対話できるようになると、君の運用スキルは一段階上のステージに上がれるはずだよ。

インデックスの整理は、いわば部屋の片付けと同じだ。溜め込みすぎると探し物が見つからなくなる。定期的に、でも計画的に。このリズムを掴んでみてほしい。

さて、今日はここまで。また何か詰まったら聞きに来てくれよな！