Postgres全文検索で日本語検索を実装する — インデックス設計とクエリ

はじめに：台帳が100行を超えたら grep では足りなくなった

当社（合同会社ジョインクラス）は、Zenn・note・Qiita 向けの記事を launchd ジョブと Claude Code で自動生成しています。記事テーマが重複しないように、生成した記事をすべて TSV の台帳 article-theme-ledger.tsv に記録しています。

article-theme-ledger.tsv（実データ抜粋）

2026-09-12	zenn	claude-code-20260912094315-1089	Claude Codeのデータフローを整理する — 何がどこに送信されるのか
2026-09-12	note	ai-2-5-20260912094500-8685	AI利用料が前月比2倍になった月に見直した5箇所 — トークン最適化の実務
2026-09-13	note	ai-1-20260913220018-28000	AIへの指示書を1枚にまとめたら暴走が止まった — 指示書の構成
2026-09-14	zenn	mcp-stdio-20260914220022-32061	MCPサーバーのテストとデバッグ — stdioトランスポートの挙動を追う方法

台帳は2か月で114行になりました。この規模になると「MCP のデバッグ系はもう書いたか？」を grep MCP で確認しても、表記揺れ（「エムシーピー」「Model Context Protocol」）や部分一致の取りこぼしが出ます。そこで台帳を Postgres に取り込み、日本語で検索できるようにしました。この記事では、そのときに選んだインデックス設計とクエリをまとめます。

補足：当社スクリプトの本番運用は TSV ベースのままです。この記事の SQL は、その台帳を Postgres 16 に取り込んで検証したときのものです。

前提：Postgres 標準の to_tsvector は日本語を分かち書きしない

最初の落とし穴です。

SELECT to_tsvector('simple', 'MCPサーバーのテストとデバッグ');
--  'mcpサーバーのテストとデバッグ':1

日本語は単語の間に空白がないので、文全体が1トークンになります。この状態では デバッグ で検索してもヒットしません。選択肢は主に3つあります。

方式	拡張	長所	短所
トライグラム	pg_trgm（contrib 同梱）	追加インストール不要。部分一致と類似度に強い	2文字以下の検索語でインデックスが効きにくい
形態素解析	pgroonga / textsearch_ja	単語単位で検索できる	拡張の導入が必要。マネージドDBでは使えない場合がある
アプリ側で分かち書き	MeCab 等 + simple 辞書	Postgres 側は標準のまま	投入と検索の両方で前処理が必要

Supabase や RDS でも追加作業なしで使えることを優先して、pg_trgm を主軸にし、短い語は別の方法で補う設計にしました。

手順1：テーブル作成と TSV の取り込み

schema.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE article_ledger (
  id         bigserial PRIMARY KEY,
  created_on date        NOT NULL,
  platform   text        NOT NULL CHECK (platform IN ('zenn','note','qiita')),
  slug       text        NOT NULL UNIQUE,
  title      text        NOT NULL,
  -- 検索用の正規化カラム（後述）
  title_norm text GENERATED ALWAYS AS (lower(normalize(title, NFKC))) STORED
);

CREATE INDEX article_ledger_title_trgm
  ON article_ledger USING gin (title_norm gin_trgm_ops);

ここでのポイントは title_norm です。normalize(..., NFKC) で全角英数を半角にそろえ（ＭＣＰ→MCP）、lower() で大文字小文字の違いをなくします。GENERATED ... STORED にしておけば、取り込み側のスクリプトは正規化を意識しなくて済みます。

取り込みは \copy 1行で終わります。

psql "$DATABASE_URL" -c "\copy article_ledger(created_on, platform, slug, title) \
  FROM '.company/scripts/article-theme-ledger.tsv' WITH (FORMAT text, DELIMITER E'\t')"
# COPY 114

落とし穴： normalize() は Postgres 13 以降でしか使えず、DB のエンコーディングが UTF8 である必要があります。古い環境では title_norm をアプリ側で作って INSERT してください。

手順2：検索クエリ

3文字以上の語は ILIKE + トライグラムで検索

SELECT created_on, platform, title
FROM article_ledger
WHERE title_norm ILIKE '%' || lower(normalize('デバッグ', NFKC)) || '%'
ORDER BY created_on DESC;

ILIKE '%...%' でも、gin_trgm_ops のインデックスがあれば Bitmap Index Scan が使われます。B-tree では前方一致にしか効かないので、ここが pg_trgm を使う最大の理由です。

表記揺れには similarity を使う

テーマの重複チェックでは、完全一致よりも「似ているか」を知りたい場面のほうが多くありました。

SELECT title, similarity(title_norm, lower(normalize($1, NFKC))) AS score
FROM article_ledger
WHERE title_norm % lower(normalize($1, NFKC))   -- インデックスが効く演算子
ORDER BY score DESC
LIMIT 5;

% 演算子のしきい値は pg_trgm.similarity_threshold（デフォルト 0.3）で決まります。日本語のタイトルは文字の種類が多いぶんスコアが低めに出るので、当社のデータでは 0.2 に下げると「MCPサーバーのテスト」と「MCPのデバッグ手法」がちょうど拾えました。

SET pg_trgm.similarity_threshold = 0.2;  -- セッション単位で設定

2文字以下の語（例：「AI」「税」）

トライグラムは3文字単位なので、2文字の検索語ではインデックスがほぼ効かず、シーケンシャルスキャンになります。数百行程度ならそれで問題ありません。数十万行の規模になったら、ここだけ pgroonga を使うか、タグカラムを別に持って = ANY(tags) で引く方法に切り替えるのが現実的です。

手順3：インデックスが効いているか確認する

インデックスを作ったことに満足して終わりにせず、必ず EXPLAIN で確認します。

SET enable_seqscan = off;  -- 行数が少ないとプランナが seq scan を選ぶため、検証時のみ
EXPLAIN (ANALYZE, BUFFERS)
SELECT title FROM article_ledger WHERE title_norm ILIKE '%デバッグ%';

Bitmap Heap Scan on article_ledger  (actual rows=3 ...)
  Recheck Cond: (title_norm ~~* '%デバッグ%'::text)
  ->  Bitmap Index Scan on article_ledger_title_trgm

Bitmap Index Scan on article_ledger_title_trgm が出ていれば成功です。

落とし穴： 114行のような小さいテーブルでは、インデックスがあってもプランナは Seq Scan を選びます。これは正しい判断なので、「インデックスが効かない」と慌てる必要はありません。検証のときだけ enable_seqscan = off にしてください。

運用してわかったこと

• 重複検知の精度が上がった：grep では見逃していた「言い回しだけ違う同じテーマ」を similarity 0.2 で検出できるようになりました
• 正規化は DB に寄せる：取り込み側（シェルスクリプト）で NFKC 変換をすると漏れが出ます。生成カラムにしたことで、正規化の漏れはなくなりました
• 最初から形態素解析を入れなくてよい：数千行までなら pg_trgm だけで十分実用になります

おわりに

「仕組みの初期投資は大きい。だが一度作れば自動で回る」というのが、AI で会社を回してきた僕の実感です。記事を自動で量産すると、次は「何をもう書いたか」を管理する問題が必ず出てきます。台帳を検索できるようにしておくことは、自動化を長く続けるための地味ですが効く投資でした。

当社が launchd・cron・Hooks で記事生成やテーマ管理を回している構成全体は、書籍『Claude Code 全自動化バイブル』にまとめています。

• 書籍一覧: https://zenn.dev/joinclass?tab=books