【実務・中級編】インデックスの肥大化と再構築 – PostgreSQL

「インデックスが遅い？」と思ったら。PostgreSQLの肥大化と上手な付き合い方

現場でPostgreSQLを運用していると、ふとこんな瞬間に遭遇しませんか？
「以前はサクサク動いていたクエリが、最近なんだか重い気がする……」
「レコード数もそんなに増えていないはずなのに、なぜかパフォーマンスが落ちている」

もし心当たりがあるなら、それは「インデックスの肥大化（Bloat）」が原因かもしれません。今日は、地味だけど避けては通れない「インデックスのメンテナンス」について、僕なりの実戦的な知見をシェアしたいと思います。

—

PostgreSQLにはMVCC（多版同時実行制御）という仕組みがありますよね。これは「更新時に古いデータを消すのではなく、新しいレコードを書き込む」という仕組みです。

これがインデックスにも影響します。更新（UPDATE）や削除（DELETE）を繰り返すと、インデックスのページ内には「無効になった古いデータ」のゴミが溜まっていきます。これが「肥大化」です。

本来なら100ページで収まるはずのインデックスが、ゴミのせいで300ページに膨れ上がっている……なんてこともザラにあります。結果、DBは無駄なページまでディスクから読み込むことになり、当然ながらクエリの速度は低下します。

「なんとなく遅いかも」で作業を始めるのはNGです。まずは定量的にチェックしましょう。僕はよく `pgstattuple` 拡張を使います。

— 拡張のインストール（まだなら）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

— インデックスの状況を確認
SELECT FROM pgstatindex(‘idx_your_table_column’);

ここで `avg_leaf_density`（リーフページの密度）に注目してください。これが極端に低い（例えば50%以下など）場合は、間違いなくメンテナンスのサインです。

—

さて、肥大化が確認できたら再構築が必要です。ここで使うのが `REINDEX` コマンドですが、やり方には大きく分けて2つの流儀があります。

REINDEX INDEX idx_your_table_column;

これはシンプルですが、インデックスを排他ロックします。つまり、再構築が終わるまでそのテーブルへの書き込みがブロックされるんです。深夜のメンテナンスタイムなら良いですが、24時間稼働のサービスでは命取りになります。

オンラインサービスでインデックスを張り直すなら、絶対的にこちらです。

REINDEX INDEX CONCURRENTLY idx_your_table_column;

これを使うと、ロックをかけずにバックグラウンドで新しいインデックスを作成してくれます。ただし、これにはいくつか「知っておくべき作法」があります。

—

僕が現場で運用する際、気をつけているポイントを3つだけ伝授しますね。

全てのインデックスを闇雲に再構築するのはリソースの無駄です。`pgstatindex` で本当に肥大化しているものだけを選別しましょう。

`CONCURRENTLY` を実行すると、古いインデックスと新しいインデックスが一時的に両方存在することになります。ディスク容量がカツカツだと、再構築中にOSごと止まるリスクがあります。

「月に1回自動実行」にするより、前述の `pgstatindex` を定期的に監視して、しきい値を超えたらアラートを飛ばす、といった運用のほうがDBには優しいです。

—

データベースのメンテナンスは、車のオイル交換と似ています。
「まだ走れるから大丈夫」と放置していると、ある日突然、大きなトラブルがやってきます。でも、きちんとケアしてあげれば、PostgreSQLは驚くほど長く、安定してパフォーマンスを発揮してくれる頼もしい相棒です。

もし今、あなたのDBが少し重いと感じているなら、まずは調査から始めてみてください。それがパフォーマンス改善への第一歩です。

それでは、良いデータベースライフを！