Posted inデータベース

脱・オーバーエンジニアリング：Elasticsearchなしで実現する高速PostgreSQL全文検索

Database tutorial - IT technology blog
Database tutorial - IT technology blog

本番環境でのPostgreSQL検索運用：6ヶ月の記録

6ヶ月前、私は壁にぶつかりました。クライアントのアプリケーションが成長し、基本的な LIKE %keyword% クエリでは結果が返るまでに5〜8秒もかかるようになってしまったのです。実質的に使い物にならない状態でした。開発チームの反応は予想通りでした。「Elasticsearchクラスターを立ち上げよう」というものです。

Elasticsearchは強力ですが、管理が非常に大変です。追加のサーバー、複雑なデータ同期（CDC）ロジック、そして高額なクラウド利用料という「余計な荷物」がついてきます。火の中に飛び込む前に、私はPostgreSQL標準の全文検索（FTS）でどこまで行けるか試してみることにしました。

半年が経過した現在、私たちはこの構成を120万行のコンテンツを含む本番環境で運用していますが、検索クエリは依然として100ミリ秒以下を維持しています。ほとんどのSaaS製品にとって、Elasticsearchは過剰装備（オーバーエンジニアリング）です。ここでは、すでに手元にあるデータベースの中に、本番レベルの検索システムを構築した具体的な方法を解説します。

わずか5分で完了するセットアップ

機能的な検索エンジンを稼働させるには、tsvector（検索対象のドキュメント）と tsquery（検索の意図）という2つの概念を理解するだけです。以下は、一般的なブログ投稿テーブルへの堅牢な実装例です。

-- 1. 自動検索カラムを持つテーブルを作成する
-- (生成列を利用するためPostgreSQL 12以降が必要)
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED
);

-- 2. テストデータを挿入する
INSERT INTO posts (title, body) VALUES 
('PostgreSQLの最適化', 'GINインデックスを使用することで、クエリ時間を数秒からミリ秒単位に短縮できます。'),
('モダンなWebアプリ', '高速なアプリには、効率的なデータ取得と最小限のレイテンシが必要です。'),
('データベース設計', 'インフラを追加せずに最大限のスケールを実現するためのテーブル構造。');

-- 3. 高速検索用のGINインデックスを作成する
CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

これで、@@ 演算子を使ってクエリを実行できます。このクエリは、「database」または「optimizing」を含む投稿を瞬時に見つけ出します。

SELECT title 
FROM posts 
WHERE search_vector @@ to_tsquery('english', 'database | optimizing');

仕組みの解説

標準的な文字列照合は、文字通りの一致を探す「リアリスト」だと考えてください。一方、PostgreSQLの全文検索（FTS）はよりスマートで、語彙素（lexemes）を探します。例えば、to_tsvector('english', 'running') を実行すると、Postgresはそれを語根である「run」に簡略化します。この「語幹抽出（stemming）」と呼ばれるプロセスにより、「runs」や「running」での検索がドキュメントと完璧に一致するようになります。

tsvector 型は、これらの正規化されたトークンとその位置を保存します。一方、tsquery はユーザーが何を探しているかを表し、& (AND)、| (OR)、! (NOT) といったブール論理をサポートします。

GINインデックスの魔法

多くのデータベースでデフォルトとなっているB-Treeインデックスは、全文検索には役に立ちません。文字列の先頭しかインデックス化できないからです。GIN（Generalized Inverted Index：汎用逆インデックス）は、500ページの教科書の巻末にある索引のように機能します。すべてのユニークな単語を、それが出現するIDのリストに関連付けます。ユーザーが「PostgreSQL」を検索すると、インデックスはデータベースに正確な行の場所を即座に伝え、遅いフルテーブルスキャンを回避します。

高度な機能：スコアリングとロジック

ユーザーは単に結果が欲しいのではなく、「最適な」結果を一番上に求めています。Postgresは単に一致を見つけるだけでなく、ts_rank を使ってスコアリングを行います。カラムごとに重みを割り当てることも可能で、例えばタイトルでの一致を本文での一致よりも重要視させることができます。

-- ランキングとタイトル重み付けを用いたクエリ
SELECT title, ts_rank(search_vector, query) as rank
FROM posts, to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

レガシーデータとインポートの処理

Postgres 11以前を使用している場合は生成列が使えません。その場合は、トリガーを使用して tsvector カラムの同期を保ちます。これにより、アプリケーションコード側を意識させることなく、インデックスを常に最新の状態に保つことができます。

このプロジェクトで450MBのレガシーCSVデータを移行した際、シードスクリプトを素早く準備する必要がありました。私は toolcraft.app/ja/tools/data/csv-to-json を使用しました。これは完全にブラウザ内で動作するため、データがマシンから流出することがなく、セキュリティチームを安心させつつ検索ベクトルの初期準備を進めることができました。

現場で学んだ運用のコツ

180日間のモニタリングを経て、パフォーマンスを予測可能に保つための3つの重要なポイントをまとめました。

  • 重み付けは必須： 5単語のタイトルでの一致は、1,000単語の本文での一致よりも常に上位に来るべきです。setweight() を使って、「A」（タイトル）や「B」（要約）といったフィールドの優先順位を決めましょう。
  • Websearchクエリの活用： ユーザーに & や | といった構文を覚えさせてはいけません。websearch_to_tsquery を使いましょう。これは、フレーズ検索のための引用符や除外のためのマイナス記号など、「Googleスタイル」の入力を標準で処理してくれます。
  • GINのメンテナンス： テーブルに1日5万件以上の書き込みがある場合、GINインデックスが肥大化（bloat）する可能性があります。autovacuum を積極的に設定しましょう。私は検索負荷の高いテーブルに対して週に一度 VACUUM ANALYZE をスケジュールし、クエリプランナーの精度を維持しています。

本当にElasticsearchに移行すべきタイミングは？

PostgreSQLは非常に強力ですが、限界はあります。基本的なトライグラム（trigram）を超える高度な曖昧検索（Fuzzy Matching）が必要な場合や、数十億行のデータをミリ秒以下のレイテンシで検索する必要がある場合は、Elasticsearchの苦労を背負う価値があります。しかし、大多数のSaaS製品、ブログ、社内ツールにとって、PostgreSQLは既存のスタック内で完結する最高のパフォーマンス発揮マシンです。

Share:
Tags: