【実務・中級編】 BRINインデックスの最適化 – PostgreSQL

「B-Treeインデックスが重すぎて、テーブルの肥大化に耐えきれない……」

PostgreSQLを触っていると、一度はこんな壁にぶつかりますよね。数億行、あるいは数十億行のログテーブルを前に、「どうせインデックスを貼っても更新で死ぬし、かといってフルスキャンは遅すぎる……」と頭を抱えた経験があるなら、今日紹介するBRIN（Block Range Index）は、あなたの救世主になるかもしれません。

今日は、巨大データとの付き合い方がガラリと変わる、この「軽量級の伏兵」について深掘りしていきましょう。

—

BRINって結局なにもの？

一言で言うと、BRINは「データの物理的な並び順」を逆手に取った、非常にコンパクトなインデックスです。

B-Treeインデックスは、全てのキーとRID（レコードの物理位置）を1対1で記録しようとするので、データ量に比例してインデックスも巨大化します。これに対し、BRINは「ブロック範囲（デフォルトで128ページ分）」ごとの最小値と最大値だけを保持します。

「この範囲の中に、探している値が含まれている可能性があるか？」を判定するだけの、言わば「ざっくりとした地図」なんです。だから、インデックスサイズはB-Treeと比べて数百分の一、下手すれば数千分の一まで小さくなります。

どんな時に輝くのか？

BRINが真価を発揮するのは、「データが時系列順（あるいは特定の列の順序）で挿入されている巨大テーブル」です。

例えば、ログデータや計測データ。これらは基本的に「古いものから順に」保存されますよね。この場合、物理的なページとデータの値には強い「相関」が生まれます。

相関あり（BRIN向き）: タイムスタンプ順にデータが並んでいる。
相関なし（BRIN不向き）: ランダムなIDやUUIDが主キーで、物理的にバラバラに配置されている。

もし、物理的にバラバラなデータに対してBRINを貼るとどうなるか？最小値と最大値の幅が広がりすぎて、結局「ほとんどの範囲が対象」になってしまい、フルスキャンと変わらない性能になってしまいます。ここだけは注意してくださいね。

実践：pages_per_range でチューニングする

BRINを使う際、最も重要なパラメータが `pages_per_range` です。これは「何ページ分をまとめて1つのインデックスエントリにするか」を決める設定値です。

デフォルトは `128` ですが、これが最適とは限りません。

チューニングの考え方

値を小さくする: インデックスが細かくなり、検索精度が上がりますが、インデックスサイズは増えます。
値を大きくする: インデックスは劇的に小さくなりますが、精度が落ち、読み込むブロック数が増えて検索が遅くなります。

例えば、データが非常に巨大で、そこまで細密な検索が必要ないなら、`pages_per_range` を `256` や `512` に増やすことで、メモリ効率をさらに高めることができます。

実装コード例

— ログテーブルにBRINインデックスを作成
— pages_per_rangeをあえて256に設定して、サイズを抑える戦略
CREATE INDEX idx_logs_created_at
ON logs USING BRIN (created_at)
WITH (pages_per_range = 256);

この設定で、テーブルの更新負荷を最小限に抑えつつ、期間指定のクエリを劇的に高速化できます。

先輩からのアドバイス：運用上の注意点

BRINは魔法ではありません。以下の「現場の落とし穴」には気をつけてください。

1. データの追加順序を意識する
もし `COPY` コマンドなどで過去の日付のデータを一括で挿入すると、物理的な並び順とインデックスの最小・最大値の相関が崩れます。これが起きると検索効率がガタ落ちします。そんな時は `brin_summarize_range` を使うか、インデックスを再作成（`REINDEX`）することを検討してください。

2. B-Treeとの使い分け
「特定のキーを1つだけ特定する」ような検索にはBRINは向きません。あくまで「範囲検索」のためのインデックスです。ID検索はB-Tree、期間検索はBRIN、というように「適材適所」で使い分けるのがプロの技です。

3. まずは `EXPLAIN ANALYZE`
導入前には必ず実行計画を見てください。「インデックスを使っているのに、結局大量のブロックを読み込んでいる（Heap Fetchesが多い）」ようなら、`pages_per_range` を見直すサインです。

—

いかがでしたか？
数TBクラスのテーブルを扱うとき、インデックスの維持コストは無視できない問題です。「全部B-Treeでいいや」と思わずに、BRINという選択肢を武器に加えておくと、いざという時に「さすがだね」と言われるような最適化ができるはずです。

もし現場で「巨大テーブルが重い！」と悲鳴が聞こえたら、まずはそのデータの並び順を確認してみてください。そこにBRINの出番があるかもしれませんよ。

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