pg_bigmによるキーワード検索の高速化

概要

キーワード検索の高速化のために pg_bigm というPostgreSQLの全文検索エクステンションを導入しました。

PR: https://github.com/fjordllc/bootcamp/pull/9819

pg_bigmとは

pg_bigmは、PostgreSQLで 2-gram（bigram） ベースの全文検索を実現するエクステンションです。日本のNTTデータ系チームが開発しており、日本語検索に強いのが特徴です。

2-gramとは

テキストを2文字ずつに分割してインデックスを作る方式です。

"bootcamp" → "bo", "oo", "ot", "tc", "ca", "am", "mp"
"プラクティス" → "プラ", "ラク", "クテ", "ティ", "ィス"

この分割された断片（gram）をGINインデックスに格納することで、LIKE '%検索語%'のような中間一致検索でもインデックスが効くようになります。

なぜpg_trgm（3-gram）ではなくpg_bigm（2-gram）？

PostgreSQL標準のpg_trgmは3文字単位で分割するため、2文字以下の検索語ではインデックスが効きません。日本語は「DB」「Go」「OS」のような短い単語が多いため、2文字単位のpg_bigmの方が適しています。

	pg_bigm（2-gram）	pg_trgm（3-gram）
日本語対応	◎	△
1〜2文字検索	✅ インデックスが効く	❌ 効かない
Cloud SQL対応	✅	✅

今回の変更で何が変わったか

Before

• Ransackの_cont（= ILIKE '%keyword%'）で検索
• インデックスが効かず、テーブルフルスキャンが発生
• データ量が増えると検索が遅くなる

After

• pg_bigmが有効な環境: LIKE + GINインデックスで高速検索
• pg_bigmがない環境: 従来通りRansack（ILIKE）にフォールバック
• 開発環境にpg_bigmがなくても正常に動作する

インデックスが追加されたテーブル・カラム

テーブル	カラム
practices	title, description, goal
pages	title, body
reports	title, description
questions	title, description
answers	description
comments	description
announcements	title, description
products	body
events	title, description
regular_events	title, description
pair_works	title, description
users	login_name, name, name_kana, description

開発環境での扱い

pg_bigmがなくても大丈夫

QueryBuilderはpg_bigmの有無を自動検出します。pg_bigmが未インストールの開発環境では従来のRansack（ILIKE）検索にフォールバックするため、pg_bigmをインストールしなくても開発に支障はありません。

pg_bigmをローカルにインストールしたい場合（任意）

macOS + Homebrew の場合:

# ソースからビルド
git clone https://github.com/pgbigm/pg_bigm.git /tmp/pg_bigm
cd /tmp/pg_bigm
PG_SYSROOT="$(xcrun --show-sdk-path)" make USE_PGXS=1
make USE_PGXS=1 PG_SYSROOT="$(xcrun --show-sdk-path)" install

# postgresql.confに追加
# shared_preload_libraries = 'pg_bigm'

# PostgreSQL再起動
brew services restart postgresql@18

# マイグレーション実行
bin/rails db:migrate

本番環境（Cloud SQL）での設定

1. Cloud SQLのフラグ cloudsql.enable_pg_bigm を on に設定
2. rails db:migrate を実行

マイグレーションはpg_available_extensionsをチェックして、pg_bigmが利用可能な場合のみエクステンション有効化とインデックス作成を行います。

技術的な補足

LIKEとILIKEについて

pg_bigmはLIKEのみ対応でILIKE（大文字小文字無視）には非対応です。ただし、このアプリの検索対象はほぼ日本語のため、LIKEとILIKEで検索結果に差はありません。

association経由のカラム

Searcher::Configurationで指定されているカラムのうち、association経由のもの（例: discord_profile_account_name）はpg_bigmではなくRansack（ILIKE）で検索されます。