PostgreSQL フルテキスト検索：あなたのアプリに十分なとき

実際のアプリで「十分な検索」はどう見えるか\n\n多くの人は「フルテキスト検索が欲しい」とは言いません。求めているのは、速く感じられて最初のページで意図した結果が見つかる検索ボックスです。結果が遅い、ノイズが多い、順序が不自然だと、ユーザーは使われた技術（PostgreSQL FTS か別エンジンか）には関心がなく、単に検索を信頼しなくなります。\n\nここでの決定は一つです：検索を Postgres 内に留めるか、専用の検索エンジンを追加するか。目標は完璧な関連性ではなく、迅速に出荷でき、運用が簡単で、実際の利用法に対して十分に良い堅実な基準です。\n\n多くのアプリでは、PostgreSQL のフルテキスト検索で長い間十分です。タイトル、説明、メモなどいくつかのテキストフィールド、基本的なランキング、ステータスやカテゴリなどのフィルタが一つ二つある程度なら、Postgres で追加インフラ無しに処理できます。可動部が少なく、バックアップが簡単で、「アプリは動いているのに検索だけ落ちているのはなぜ？」という事故も減ります。\n\n「十分」は通常、同時に三つの目標を満たせることを意味します：\n\n- 品質： 共通クエリで正しいアイテムが上位に来る。明白な一致が欠けない。\n- 速度： 通常負荷で結果が速く返る（開発マシンだけで速いのではない）。\n- 運用努力： チームが二つ目のクラスタ、追加監視、別のインデックスパイプラインを運用せずに管理できる。\n\n具体例：ユーザーがプロジェクト名やノートで検索する SaaS ダッシュボード。"onboarding checklist" のようなクエリで関連プロジェクトがトップ5に入り、1 秒未満で返り、解析や再インデックスに常時手間をかけていなければ、それは「十分」です。これらの目標を複雑さを積み上げないと達成できないなら、「組み込み検索 vs 検索エンジン」を本気で考える時です。\n\n## よくある検索要件とそれが示す本当の意味\n\nチームはしばしば機能で検索を語りがちで、成果では語りません。それぞれの機能を、構築・調整・信頼性維持にかかるコストに翻訳するのが有用です。\n\n初期の要求は典型的に：誤字許容、ファセットとフィルタ、ハイライト、「賢い」ランキング、オートコンプリートといったものです。最初のバージョンでは、必須と望ましいものを分けて考えましょう。基本的な検索ボックスは、関連項目を見つけ、一般的な語形（複数形や時制）を扱い、単純なフィルタを尊重し、テーブルが増えても速さを保つことが求められます。ここが Postgres FTS が適する領域です。\n\nPostgres は、コンテンツが通常のテキストフィールドにあり、検索をデータに近いところで行いたい場合に得意です：ヘルプ記事、ブログ投稿、サポートチケット、社内ドキュメント、製品タイトルや説明、顧客レコードのメモなど。これらは主に「正しいレコードを見つける」問題であり、「検索製品を作る」問題ではありません。\n\n一方、望ましい機能が複雑さを呼び込みます。誤字許容やリッチなオートコンプリートは通常、追加ツールを促します。ファセットは Postgres でも可能ですが、多数のファセット、深い分析、巨大データセットにわたる迅速なカウントが必要なら、専用エンジンの魅力が増します。\n\n見えにくいコストはライセンス料ではなく、第二のシステムです。検索エンジンを追加すると、データ同期やバックフィル（とそれに伴うバグ）、監視とアップグレード、「検索が古いデータを出すのはなぜか？」というサポート業務、二つの関連性調整セットが増えます。\n\n迷ったら Postgres から始め、シンプルなものを出荷し、満たせない明確な要件が出てきたときだけもう一つ加えましょう。\n\n## シンプルな判断ルール：組み込み検索 vs 専用エンジン\n\n三つのチェックルールを使います。三つともパスすれば PostgreSQL FTS に留まってください。どれか一つを大きく失敗するなら専用検索エンジンを検討します。\n\n### 三つのチェック項目\n\n1) 関連性の必要度： 「十分に良い」結果で受け入れられるか、それとも誤字・同義語・"people also searched"・パーソナライズなど多数のエッジケースでほぼ完璧な順位付けが必要か。多少の順序の不完全さを許容できるなら、Postgres は通常十分です。\n\n2) クエリ量とレイテンシ： ピーク時に秒間どれだけの検索が来るか、実際のレイテンシ目標は何か。検索が全トラフィックの小さい部分で、適切なインデックスで速さを保てるなら Postgres で問題ありません。検索が主要なワークロードになり、コアの読み書きと競合し始めるなら警告です。\n\n3) 複雑さ： 一〜二のテキストフィールドを検索するだけか、多数のシグナル（タグ、フィルタ、時間減衰、人気度、権限）や多言語を組み合わせるか。ロジックが複雑なほど SQL 内で摩擦を感じるでしょう。\n\n安全な出発点はシンプルに：Postgres でベースラインを出し、遅いクエリや「ヒットなし」検索をログに残し、それから判断してください。多くのアプリは成長しても Postgres で足りますし、二つ目のシステムを早すぎて導入するリスクを避けられます。\n\n専用エンジンを示唆するレッドフラグ：\n\n- 高品質な誤字許容、同義語、オートコンプリートが必須。\n- 多数のフィールドに跨るファセットと複雑なフィルタ。\n- 検索トラフィックがプライマリ DB に負荷をかける。\n- 多言語で辞書以上の関連性が必要。\n- 学習型ランキングやクリックフィードバックのような高度なランキング機能が必要。\n\nPostgres に留まるグリーンフラグ：\n\n- 検索は「正しいページを見つける」用途で、コンテンツの発見が目的ではない。\n- 運用と信頼性のために一つの DB に留めたい。\n- コンテンツが数列（タイトル、本文、タグ）に収まる。\n- 単純な関連性チューニングを受け入れてゆっくり改善できる。\n- チームが可動部を減らしたい（同期パイプラインや二重書き込みを避ける）。\n\n## PostgreSQL フルテキスト検索の仕組み（必要なことだけ）\n\nPostgreSQL FTS は、テキストをデータベースが速く検索できる形式に変換する組み込みの仕組みです。すべての行をスキャンせずに済むようにするもので、コンテンツが既に Postgres にある場合に予測可能な運用で速くまともな検索を提供します。\n\n知っておくべき三つの要素：\n\n- tsvector： テキストの「検索可能な形式」。Postgres がテキストをトークン化し正規化して保存します。\n- tsquery： ユーザーが探しているものを構造化して表現したもの（単語、AND/OR、フレーズ風の演算子）。\n- ランキング： 結果を並べるためのスコア。ts_rank（または ts_rank_cd）で関連性の高い行を上にできます。\n\n言語設定は、単語の扱いを変えるため重要です。適切な設定があれば “running” と “run” が一致したり（ステミング）、一般的な不要語が無視されたりします。間違った設定だと、通常のユーザー表現がインデックスと一致しなくなり、検索が壊れたように感じられます。\n\nプレフィックスマッチはタイプアヘッド風の挙動を実現したいときに使われます（例：「dev」で「developer」にマッチ）。Postgres FTS では通常プレフィックス演算子（例 term:*）で実現します。見た目の品質は上がりますが、クエリごとの作業が増えることが多いので、デフォルトではなくオプション的な扱いが良いでしょう。\n\nPostgres が目指していないのは、あらゆる機能を備えた完全な検索プラットフォームです。曖昧検索のスペル補正、先進的なオートコンプリート、フィールドごとの複雑なアナライザ、複数ノードに跨る分散インデックスが必要なら、組み込みの快適域外です。しかし多くのアプリでは、PostgreSQL FTS はユーザーが期待するほとんどの機能を、はるかに少ない可動部で提供します。\n\n## コピーして使えるスタータークエリ\n\n検索したいコンテンツに対する小さく現実的な形：\n\nsql\n-- Minimal example table\nCREATE TABLE articles (\n id bigserial PRIMARY KEY,\n title text NOT NULL,\n body text NOT NULL,\n updated_at timestamptz NOT NULL DEFAULT now()\n);\n\n\nPostgreSQL FTS の良いベースラインは：ユーザー入力からクエリを作り、可能なら先に行をフィルタし、その後残ったマッチをランキングすることです。\n\nsql\n-- $1 = user search text, $2 = limit, $3 = offset\nWITH q AS (\n SELECT websearch_to_tsquery('english', $1) AS query\n)\nSELECT\n a.id,\n a.title,\n a.updated_at,\n ts_rank_cd(\n setweight(to_tsvector('english', coalesce(a.title, '')), 'A') ||\n setweight(to_tsvector('english', coalesce(a.body, '')), 'B'),\n q.query\n ) AS rank\nFROM articles a\nCROSS JOIN q\nWHERE\n a.updated_at \u003e= now() - interval '2 years' -- example safe filter\n AND (\n setweight(to_tsvector('english', coalesce(a.title, '')), 'A') ||\n setweight(to_tsvector('english', coalesce(a.body, '')), 'B')\n ) @@ q.query\nORDER BY rank DESC, a.updated_at DESC, a.id DESC\nLIMIT $2 OFFSET $3;\n\n\n後で時間を節約するいくつかのポイント：\n\n- 安価なフィルタ（status, tenant_id, date ranges）を WHERE に先に置いてからランキングする。ランク付けする行が少なくなり速くなります。\n- ORDER BY にタイブレーカー（例えば updated_at や id）を常に追加して、同じランクの結果でページングが安定するようにする。\n- ユーザー入力には websearch_to_tsquery を使う。引用符やシンプルな演算子を扱う際に人が期待する挙動になります。\n\nこのベースラインが動いたら、to_tsvector(...) 式を保存列に移して、毎回計算しないようにし、インデックス化を簡単にします。\n\n## 効果のあるインデックス設定\n\n「PostgreSQL FTS が遅い」という話の多くは一つに帰着します：データベースが毎回検索ドキュメントを作っていること。まずはプリビルドの tsvector を保存してインデックスを作ることが解決策です。\n\n### tsvector を保存する：生成列かトリガーか？\n\n検索ドキュメントが同じ行の列から構築されるなら生成列が最もシンプルです。更新時に自動で正しく保たれ、忘れにくいです。\n\nドキュメントが関連テーブルに依存する場合（例えば製品行とカテゴリ名を組み合わせるなど）や、単一の生成式で表現しにくいカスタムロジックがある場合は、トリガーで維持してください。トリガーは可動部分を増やすので、小さくしてテストしましょう。\n\n### ほとんどの場合欲しいインデックス\n\ntsvector 列に GIN インデックスを作るのがベースラインで、多くのアプリ検索で瞬時に感じられるようになります。\n\n多くのアプリで有効なセットアップ：\n\n- 検索対象の tsvector を最も頻繁に検索する行と同じテーブルに置く。\n- その tsvector に GIN インデックスを追加する。\n- クエリが to_tsvector(...) を毎回計算せず、保存された tsvector に対して @@ を使っていることを確認する。\n- 大量のバックフィルの後は VACUUM (ANALYZE) を行い、プランナが新しいインデックスを理解するようにする。\n\n同じテーブルにベクトルを置くのが通常は速く簡単です。ベーステーブルが非常に書き込み多となる場合や、複数テーブルに跨る結合ドキュメントを自分のスケジュールで更新したい場合は、別の検索テーブルが有用なこともあります。\n\n部分インデックスは、検索対象が一部の行だけ（例：status = 'active'、マルチテナントの単一テナント、特定言語）であればインデックスサイズを小さくし検索を速くできますが、クエリが常に同じフィルタを含む場合に限ります。\n\n## 過度な設計を避けて許容できる関連性を得る方法\n\n関連性ルールをシンプルで予測可能にしておけば、Postgres FTS でも驚くほど良い結果が得られます。\n\n最も簡単な改善はフィールド重み付けです：タイトルのヒットは本文のヒットより重要にすべきです。タイトルに高い重みを与えた結合 tsvector を作り、ts_rank や ts_rank_cd でランク付けします。\n\n「新しさ」や「人気」を浮上させたいなら慎重に行ってください。小さなブーストは問題ありませんが、テキスト関連性を上書きしないように。実用的なパターンは：まずテキストでランク付けし、同点は新しさで分ける、あるいは上限付きのボーナスを与えて無関係な新着が完璧な古い一致に勝たないようにすることです。\n\n同義語やフレーズマッチは期待が分かれる箇所です。同義語は自動的には付かないので、シソーラスやカスタム辞書を追加するか、クエリ側で用語を展開する必要があります（例：「auth」を「authentication」と扱う）。フレーズマッチもデフォルトではありません：通常クエリは単語をどこでもマッチさせます。ユーザーが引用符付きフレーズや長い質問を入力するケースが多ければ、phraseto_tsquery や websearch_to_tsquery の使用を検討してください。\n\n混在言語のコンテンツは方針が必要です。ドキュメントごとに言語が分かっているならそれを保存し、適切な設定（English、Russian など）で tsvector を生成してください。分からない場合の安全策は simple 設定（ステミングしない）でインデックスするか、既知言語用のベクトルとすべてに対する simple ベクトルの両方を保持することです。\n\n関連性を検証するには小さく具体的に：\n\n- ユーザー（またはサポートチャット）からの実際のクエリを 10〜20 件収集する。\n- 各クエリに対して上位に来るべき 1〜3 件の結果を書き出す。\n- 各チューニング変更後に実行し、何が改善し何が悪化したかを記録する。\n- アプリにとって「十分」に感じられたらそこで止める。\n\nこれで多くのアプリ検索ボックス（テンプレート、ドキュメント、プロジェクトなど）では PostgreSQL FTS で十分です。\n\n## Postgres 検索が悪く見える一般的なミス\n\n「PostgreSQL FTS が遅い／関連性が低い」という話の多くは回避可能なミスから来ます。それらを直すのは、別の検索システムを導入するより簡単なことが多いです。\n\nよくある落とし穴の一つは tsvector を計算済みの値として扱い、挿入や更新時に確実に更新しないことです。tsvector を列に保存しているのに毎回更新されていなければ、インデックスとテキストが一致せず結果がランダムに見えます。逆にクエリ内で to_tsvector(...) を毎回計算すると結果は正しくても遅くなり、専用インデックスの恩恵を受けられません。\n\nもう一つの性能を悪化させるパターンは、候補セットを狭める前にランキングしてしまうことです。ts_rank は有用ですが、通常はインデックスでマッチした行を絞った後に実行すべきです。巨大な部分でランクを計算したり、先に他テーブルと結合したりすると、速い検索をテーブルスキャンに変えてしまいます。\n\n人々はまた「contains 検索」を LIKE '%term%' のように期待しますが、前方ワイルドカードはフルテキスト検索の単語ベースの性質に合いません。製品コードや部分 ID にはトリグラム索引など別手段を使ってください。\n\nパフォーマンスの問題はマッチングではなく結果処理に由来することも多いです。注意すべき二つのパターン：\n\n- 大きな OFFSET を使うページング。ページを進めるごとに Postgres がより多くの行をスキップする。\n- 無制限の結果セットで、クエリが数万行以上を返す可能性がある。\n\n運用面も重要です。頻繁な更新の後にインデックス膨張（bloat）が起き、再インデックスが高コストになることがあります。大きな変更前後で EXPLAIN ANALYZE を使って実際のクエリ時間を測ってください。数値なしでは、Postgres FTS を「直す」つもりが別の形で悪化させてしまいがちです。\n\n## クイックチェックリスト：クエリとインデックスの健全性\n\nPostgreSQL FTS を責める前に、次のチェックを走らせてください。ほとんどの「Postgres 検索が遅い／関連性が低い」バグは基本が欠けているだけです。\n\n### データ + インデックスの健全性チェック\n\n本物の tsvector を構築する：生成列か維持された列に保存し（クエリで毎回計算しない）、適切な言語設定（english, simple 等）を使い、フィールドを混ぜるなら重み付けを適用する（title > subtitle > body）。\n\nインデックス化の対象を正規化する：ID、定型文、ナビゲーションテキストのようなノイズなフィールドは tsvector から外し、ユーザーが検索しない巨大な BLOB は切り詰める。\n\n正しいインデックスを作る：tsvector 列に GIN インデックスを追加し、EXPLAIN で使われていることを確認する。検索対象が一部の行だけなら部分インデックスでサイズを削れますが、クエリが常にそのフィルタを含む場合に限ります。\n\nテーブルを健康に保つ：デッドタプルはインデックススキャンを遅くします。頻繁に更新されるコンテンツでは定期的な vacuum が重要です。\n\n再インデックス計画を持つ：大規模なマイグレーションや膨張したインデックスは制御されたウィンドウで再インデックスが必要になることがあります。\n\nデータとインデックスが正しくなったら、クエリ形状に注力してください。PostgreSQL FTS は候補セットを早く絞れると速く動きます。\n\n### クエリ + 実行時の健全性チェック\n\n先にフィルタし、その後ランキング：厳しいフィルタ（tenant, language, published, category）を先に適用してからランク付けする。後で破棄する何千行ものランク計算は無駄です。\n\n安定した順序付け：rank の後に updated_at や id のようなタイブレーカーを順序に入れて、リフレッシュで結果が跳ねないようにする。\n\n「クエリですべてやろうとしない」：曖昧マッチや誤字許容が必要なら意図的に行い（そして計測する）、誤ってシーケンシャルスキャンを強いるような実装は避ける。\n\n実際のクエリでテストする：トップ 20 の検索を収集し、手で関連性を確認し、小さな期待結果リストを保持して回帰を検出する。\n\n遅い経路を監視する：遅いクエリをログし、EXPLAIN (ANALYZE, BUFFERS) を確認し、インデックスサイズやキャッシュヒット率を監視して成長が挙動を変えるときに気づけるようにする。\n\n## 例: 基本的なサイト検索から成長の必要へ\n\nSaaS のヘルプセンターは出発点として良い例です。目的は単純：その質問に答える記事を見つけること。数千の記事があり、それぞれにタイトル、短い要約、本文がある。訪問者は通常 "reset password" や "billing invoice" のような 2〜5 単語を入力します。\n\nPostgreSQL FTS でこれを驚くほど速く「完了」扱いにできます。結合したフィールドの tsvector を保存し、GIN インデックスを追加し、関連性でランク付けします。成功は次のように見えます：結果が 100 ms 未満で返り、トップ 3 が通常正しく、システムを常時監視して世話をする必要がない状態です。\n\nその後プロダクトが成長します。サポートは製品領域、プラットフォーム（web, iOS, Android）、プラン（free, pro, business）でフィルタしたがり、ドキュメント作成者は同義語や「did you mean」、誤字の扱いを望みます。マーケティングは「0 件の結果の多い検索」の分析を求めます。トラフィックが増え検索が最も負荷のかかるエンドポイントの一つになります。\n\nこれらが専用検索エンジンを検討すべき信号です：\n\n- 同じページで多数のフィルタとファセットが必要。\n- ファーストクラスの曖昧マッチや誤字許容、オートコンプリートが必要。\n- 検索分析や関連性のためのフィードバックループが必要。\n- 検索トラフィックが多く、プライマリ DB から切り離す必要がある。\n\n実用的な移行パスは、専用エンジン追加後も Postgres をソースオブトゥルースとして保持することです。まず検索クエリやノーヒットケースをログし、非同期同期ジョブで検索対象フィールドだけを新しいインデックスにコピーします。しばらくは両方を並行運用し、段階的に切り替えるのが安全です。一斉に賭けるのは避けましょう。\n\n## 次のステップ: ベースラインを出荷し、計測してから判断\n\n検索が主に「これらの単語を含む文書を見つける」もので、データセットが巨大でなければ、PostgreSQL FTS で十分なことが多いです。まずそこから始め、動かしてから専用エンジンが必要かどうか判断してください。\n\nまとめとして持っておくべき点：\n\n- tsvector を保存し、GIN インデックスを追加し、ランク要件が基本的なら Postgres FTS を使う。\n- ひとつのスタータークエリとインデックス設定を出荷し、実際のレイテンシと「ユーザーが見つけられたか」を計測する。\n- 関連性のチューニングは小さく明白な変更（重み、言語設定、クエリパース）で行い、大規模な書き換えは避ける。\n- オートコンプリート、強い誤字許容、ファセット、あるいはサイズや負荷の成長といった明確なギャップが出たときだけ検索エンジンを計画する。\n\n実用的な次の一歩：前節のスタータークエリとインデックスを実装し、1 週間ほど簡単なメトリクスをログしてください。p95 クエリ時間、遅いクエリ、そして「検索 → クリック → すぐ離脱しない」などの粗い成功シグナルを追いましょう（基本的なイベントカウンタで十分です）。それで、より良いランキングが必要か、単に UX（フィルタ、ハイライト、スニペット）が必要かがすぐ見えてきます。\n\n強力なオートコンプリートやキーストロークごとの即時検索のような要件、強い誤字許容やスニペットが必須、複数フィールドに跨る高速集計・ファセット、学習型ランキングやクエリごとのブーストが必要、あるいは持続的な高負荷で大きなインデックスを高速に保てない、というケースになったら専用エンジンを計画してください。\n\nアプリ側を素早く進めたいなら、Koder.ai (koder.ai) は検索 UI と API をチャットでプロトタイプするのに便利です。スナップショットとロールバックを使って安全に反復し、ユーザー行動を計測しながら改善できます。