【テクニカル・上級編】 pg_trgm拡張によるトリグラムインデックス – PostgreSQL

曖昧検索の「最後の砦」：pg_trgmで文字列検索の限界を突破する

PostgreSQLを使っていると、いつかは直面する壁があります。`LIKE ‘%keyword%’` という、インデックスが効かないあの絶望的なクエリです。テーブルが数万件程度ならまだしも、百万件を超えたあたりで、DBエンジニアとしての直感が「これはマズい」と警鐘を鳴らし始めますよね。

そんな時、我々が迷わず手に取るのが `pg_trgm` です。単なる「便利な拡張機能」として片付けるには惜しい、このトリグラム・インデックスの深淵について、今日は少し踏み込んで語ってみましょう。

—

トリグラムの正体：なぜ「3文字」なのか

`pg_trgm` の本質は、文字列を3文字の連続するシーケンス（トリグラム）に分解し、それをGISTまたはGINインデックスに格納することにあります。

例えば `’postgreSQL’` という文字列なら、` p`, ` po`, `pos`, `ost`, `stg`, `tgr`, `gre`, `res`, `esq`, `sql`, `ql ` といった形で分解されます。

なぜ「3文字」なのか。これは計算理論的なトレードオフの産物です。

2文字だと： インデックスサイズが肥大化しすぎる割に、検索の絞り込み精度（選択率）が低く、多くの偽陽性（False Positive）を拾ってしまいます。
4文字以上だと： インデックスサイズは抑えられますが、短いキーワードでの検索が不可能になります。

この「3」という数字は、ストレージ効率と検索精度のバランスを極限までチューニングした、先人たちの黄金比なのです。

GIN vs GIST：パフォーマンスの分かれ道

`pg_trgm` を導入する際、インデックスの種類を GIN と GIST のどちらにするかで頭を悩ませるはずです。

GIN (Generalized Inverted Index):

検索パフォーマンスを最優先するなら、間違いなくGINです。トリグラムをすべて転置インデックスとして保持するため、読み取り速度は圧倒的。ただし、更新（INSERT/UPDATE）時のコストは凄まじいものがあります。高頻度で更新されるテーブルに貼ると、`maintenance_work_mem` をどれだけ積んでも追いつかなくなる瞬間が来ます。

GiST (Generalized Search Tree):

GINよりも更新コストは軽微です。また、`fastupdate` オプションによる遅延書き込みの恩恵も受けやすい。ただし、検索性能はGINに劣ります。

私の経験則ですが、「読み取り専用に近いマスタデータ」ならGIN、「頻繁に書き込まれるログやイベントデータ」ならGiST というのが定石です。

パフォーマンストラブルシューティングの勘所

もし、`pg_trgm` を導入してもクエリが遅いなら、まず以下のポイントを確認してください。

1. データ分布と選択率:
非常に短いキーワード（例：’ab’）で検索していませんか？トリグラムインデックスは、短すぎるキーワードに対してはインデックスをスキャンした結果、大量の行をフェッチする「インデックス・スキャン・ブラインド」状態に陥ります。`EXPLAIN ANALYZE` を叩いて、`Rows Removed by Filter` が肥大化していないか確認してください。

2. similarity閾値の呪縛:
`%` 演算子による類似度検索を行っている場合、デフォルトの閾値（0.3）が適切でないことがあります。`set_limit()` で値を調整しても改善しない場合は、インデックスの「無駄なスキャン」が起きている可能性が高いです。

3. インデックスサイズの肥大化:
GINインデックスは強力ですが、巨大なテキストカラムに対して適用すると、テーブル容量を数倍に膨れ上がらせます。`pg_relation_size` でインデックスのサイズを監視し、本当にそのカラム全体が必要か、あるいは必要な文字数だけをインデックスに含める（サブセットインデックス）ことで解決できないか検討しましょう。

最後に：銀の弾丸ではないということ

`pg_trgm` は確かに強力ですが、すべてを解決する魔法ではありません。もし、クエリが複雑で検索条件が多岐にわたるなら、PostgreSQLの外側にある Elasticsearch や OpenSearch のような専用の検索エンジンを検討すべきタイミングかもしれません。

しかし、システムの構成を複雑にせず、「PostgreSQLの中だけで完結させたい」というエンジニアの矜持を支えてくれるのが `pg_trgm` です。

トリグラムの裏側にある「文字列を集合として捉える」という発想を理解しておけば、パフォーマンスチューニングの幅は間違いなく広がります。さて、皆さんの環境のインデックス、無駄に肥大化していませんか？一度 `pg_stat_user_indexes` を眺めてみることをお勧めします。

技術は、細部にこそ宿るものですから。