この記事は レコチョク Advent Calendar 2023 の10日目の記事となります。
はじめに
株式会社レコチョクでバックエンド領域を担当している山根と申します。 一つ前の記事にて山本より紹介のあったバックエンドアーキテクトGに所属しております。 現在は、主に楽曲マスタDBの管理・運用をメインに担当しております。
好きな音楽ジャンルはピアノの演奏を聞くのが好きで、 最近はよくSchroeder-Headzさんの曲を鬼リピートしております。
本記事では、PostgreSQLにおける全文検索の方法についてナレッジベースで整理してみた記事となります。
背景
担当領域にて Amazon CloudSearch を利用した全文検索機能を実現しているシステムがあったのですが、利用方法の見直しとコスト削減ができないか模索している中で、そもそも全文検索を実現するにあったて、どういう手法があったか振り返りたくなり、ナレッジベースですが整理した内容を今回まとめてみました。
PostgreSQLの全文検索を実現する方法
- LIKE/ILIKE/正規表現でのパターンマッチング（*標準搭載）
- textsearch モジュールの利用（*標準搭載）
- pg_trgmモジュールの利用 (*標準搭載)
- pg_bigm 拡張モジュールの利用 (*サードパーティ製のプラグイン)
- PGroonga 拡張モジュールの利用 (*サードパーティ製のプラグイン)
1. LIKE/ILIKE/正規表現 でのパターンマッチング
PostgreSQLをインストールしてすぐに実現可能な手法。 一番シンプルでかつ標準で使える方法で、検索対象のデータが少なければ十分高速に利用できる手法だと考えています。 （※DBのパフォーマンスを最適化するにはインデックスの作成も考慮に入れる必要あり。）
-- %を使用して任意の文字列を検索する例
SELECT * FROM <table> WHERE <column> LIKE '%pattern%';
-- 大文字小文字を区別せずに検索する例 (※日本語に対しては大小の区別がないためLIKEと同じ結果が返却される)
SELECT * FROM <table> WHERE <column> ILIKE '%pattern%';
--正規表現を使用して特定のパターンを検索する例
SELECT * FROM <table> WHERE <column> ~ '^[A-Za-z]+$';
2. textsearch モジュールの利用
PostgreSQLをインストールしてすぐに実現可能な手法。 検索対象となるテキストをパースして単語 (トークン) がテキスト内のどの位置にあるかを情報をもつ tsvector と tsvector をどうやって検索するかを示す tsquery のデータ型を利用する方法となります。
（利用手順例）
-- 検索対象のサンプル
INSERT INTO documents (content) VALUES ('This is the first document.');
INSERT INTO documents (content) VALUES ('This document is the second one.');
INSERT INTO documents (content) VALUES ('And this is the third document.');
-- contents カラムに「first」という単語が含まれる文書を検索するクエリを実行
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'first');
-- Point1: 検索対象テキストから tsvector を生成するために to_tsvector 関数を利用して単語ごとに分割する
-- Point2: to_tsquery 関数を使って検索したい文字列を tsquery 形式に変換する
-- Point3: @@ 演算子を利用して tsvector と tsquery の間でマッチングを行い、クエリにマッチする行を検索させる。
※日本語対応する場合は別途モジュール（textsearch_ja）の利用が必要ですが、長期間メンテナンスされていないようです。
3. pg_trgm モジュール/ 4. pg_bigm 拡張モジュールの利用
各モジュールごとに解析方法が違いますが、どちらもSQL上で全文検索を容易に実行できる拡張ツールです。 → それぞれの特徴をまとめられた下記スライド資料がすごくわかりやすかったです。 🔗 https://www.slideshare.net/hadoopxnttdata/pgtrgm 🔗 https://www.slideshare.net/masahikosawada98/pg-bigm → （補足）日本語などマルチバイト文字列の検索には、pg-bigm の利用が適しているようですね。
なお、これら2つのモジュールとも Amazon RDS for PostgreSQL の拡張機能にてサポートされております。 → サポート一覧表は下記URL 🔗 https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html
（利用手順例）
-- 1. 拡張機能の有効化（*デフォルトは無効化されているため有効化する手順が必要）
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_bigm;
-- 2. 有効化された拡張機能は下記で確認できる
SELECT * FROM pg_extension;
-- 3. インデックスの作成
CREATE INDEX trgm_idx ON your_table USING gist (your_column gist_trgm_ops); -- pg_trgmの場合
CREATE INDEX bigm_idx ON your_table USING gin (your_column gin_bigm_ops); -- pg_bigmの場合
-- 4. SQLで検索
SELECT * FROM <table> WHERE <column> % 'pattern'; -- pg_trgmの場合
SELECT * FROM <table> WHERE <column> LIKE '%pattern%'; -- pg_bigmの場合
5. PGroonga 拡張モジュールの利用
pg_bigmと似ていますが2文字の検索性能に大きな差がないです。 ただ3文字以上の検索に対して高速に処理することに対応しており、汎用性がより高いツールというイメージです。
使い方 (チュートリアル) も公式にまとめられていますので是非 🔗 https://pgroonga.github.io/ja/tutorial/
pg_bigm と比較した ベンチマーク が分かりやすかったので気になる方は以下URLより是非 🔗 https://pgroonga.github.io/ja/reference/pgroonga-versus-pg-bigm.html
なお、現時点で Amazon RDS for PostgreSQL の拡張機能にてサポートはされていませんが下記のようの手法での仕組みが紹介されておりました。
▼Amazon RDS + Amazon EC2 + ロジカルレプリケーションを使った低コスト高速全文検索 🔗 https://www.clear-code.com/blog/2019/11/18.html
まとめ
改めてPostgreSQL下で全文検索を実現するにはどのような手法があるか整理してみましたが、 手軽に始められる方法から要件次第で短所にも長所にもなり得る手段があるなという印象です。 弊社では、クラウドにAWSを積極的に利用しているため個人的には、RDS for PostgreSQL 下で pg_bigm モジュール利用が良いと思いましたが、取り扱っているデータの特性上 PGroonga の利用も検討してみたいところだなと考えております。以降、技術選定とPostgreSQLにおける全文検索の実現が完了したら、その際は記事にまとめられればと考えています。
おわりに
最後まで読んで頂き、ありがとうございます。 レコチョクでは、ソフトウェア開発だけでなけではなく幅広い技術を自分が主体となって経験したい方・挑戦したい方を募集しています。少しでも興味があれば、是非レコチョクの採用ページをご覧ください！
レコチョク (採用ページ) 🔗 https://recruit.recochoku.jp/
明日の レコチョク Advent Calendar 2023 は 11日目の、 【iOS】Kotlin MultiplatformでApp Clip対応アプリを2か月で作ったので振り返るについてです。 お楽しみに！
sho.yamane
