要实现PostgreSQL中的全文检索，需要

2 年 ago

新, 韵

2 minutes

这篇文章是2023年Recochoku音乐推介日历的第10天的文章。

首先

我是山根，负责株式会社Recochoku的后端领域。
我隶属于山本在上一篇文章中介绍的后端架构师G团队。
目前，我主要负责音乐主数据库的管理和运营工作。

我喜欢听钢琴演奏这个音乐流派，并且最近一直在疯狂重复播放Schroeder-Headz（斯恰丽德的博士）的歌曲。

本文我们整理了关于在PostgreSQL中进行全文搜索的方法的知识库文章。

背景

在我负责的领域中，有一个使用亚马逊云搜索实现全文搜索功能的系统。在寻求重新审视使用方法和削减成本的过程中，我想回顾一下实现全文搜索的不同方法。虽然这是一个知识库，但我整理了一些内容，现在将它们总结在一起。

实现PostgreSQL全文搜索的方法

使用textsearch模块（*已标配）

使用pg_trgm模块（*已标配）

使用pg_bigm扩展模块（*第三方插件）

使用PGroonga扩展模块（*第三方插件）

使用LIKE/ILIKE/正規表現进行模式匹配。

在安装PostgreSQL之后，可以立即实现的方法。
我认为这是一种最简单且可以在标准环境中使用的方法，如果待搜索的数据量较小，则可以足够快速地使用。
（※为了优化数据库性能，需要考虑创建索引。）

-- %を使用して任意の文字列を検索する例
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和展示如何使用tsquery来搜索tsvector的方法。

（利用手順例）

-- 検索対象のサンプル
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上方便执行全文搜索的扩展工具。
→ 下面的幻灯片资料将它们各自的特点总结得非常清楚易懂。

pg_trgmと全文検索 from NTT DATA OSS Professional Services

pg_bigm(ピージーバイグラム)を用いた全文検索のしくみ from Masahiko Sawada

→（补充说明）对于搜索包括日语等多字节字符的情况，似乎更适合使用pg-bigm。

此外，这两个模块都得到了 Amazon RDS for PostgreSQL 的扩展功能的支持。
→ 支持列表请参阅下方链接
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相似，但在搜索两个字符方面性能差距不大。
然而，它能够快速处理三个字符以上的搜索，并具有更高的通用性。

请务必参考官方提供的《使用方法（教程）》进行学习：
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 + 逻辑复制的方式进行低成本高速全文搜索。

总结

我在PostgreSQL环境下重新整理了实现全文搜索的方法，发现从简单易行的方法到根据要求而变成优点或缺点的手段都是可能的。考虑到我们公司积极使用AWS云服务，我个人认为在RDS for PostgreSQL下使用pg_bigm模块是不错的选择，但考虑到处理的数据特性，我也想考虑使用PGroonga。在技术选择和在PostgreSQL中实现全文搜索完成后，我希望能将其总结成一篇文章。

就这样吧。

非常感谢您一直阅读到最后。
Recochoku不仅招募软件开发方面的人才，还欢迎那些希望广泛学习技术并主动经历的人。如果您对此有丝毫兴趣，请务必访问Recochoku的招聘页面！

“レコチョク（招聘页面）
https://recruit.recochoku.jp/”

明天的2023年レコチョク Advent Calendar是关于回顾我们在第11天制作了支持App Clip的【iOS】Kotlin Multiplatform应用程序的过程，只用了两个月。敬请期待！

这篇文章是转载自RecoChoku工程师博客的一篇文章。