PostgreSQLは組み込みの全文検索機能を持ちます。 Elasticsearchのような外部サービスを使わずに、SQLだけでテキスト検索を 高速に行えるのがメリットです。日本語対応には追加設定が必要ですが、英語テキストは即利用できます。

tsvectorとtsquery

全文検索の核となる2つのデータ型です。

• tsvector: 検索用に正規化されたトークン列
• tsquery: 検索クエリの表現

SELECT to_tsvector('english', 'PostgreSQL is powerful database');
-- 'databas':4 'postgresl':1 'power':3

SELECT to_tsquery('english', 'powerful & database');
-- 'power' & 'databas'

全文検索の実行

SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & performance');

GINインデックスで高速化

-- 検索カラムに事前計算列を追加
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('english', body);

-- GINインデックスを作成
CREATE INDEX idx_search ON articles USING GIN(search_vector);

-- トリガーで自動更新
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', body);

ランキング付き検索

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

まとめ

PostgreSQLの全文検索はシンプルなユースケースに十分対応できます。 日本語全文検索が必要な場合はpg_bigmやpg_mecabの導入を検討してください。