1. 从一次模糊查询的“慢”说起最近在排查一个老项目的性能问题现象很典型用户在前端搜索框里输入几个字比如“技术博客”后台的查询响应时间就飙升到好几秒。打开慢查询日志一看罪魁祸首是一条简单的LIKE查询SELECT * FROM articles WHERE content LIKE %技术博客%。在 PostgreSQL 里对于没有索引的文本字段做前导通配符%在开头的模糊匹配数据库只能进行全表扫描数据量一上来性能断崖式下跌是必然的。当时的第一反应是上全文检索。PostgreSQL 自带的tsvector和tsquery确实强大但一上手就遇到了本地化适配的麻烦。我们的内容主要是中文而 PostgreSQL 默认的文本搜索解析器pg_catalog.default是按空格和标点分词的对中文这种连续书写的语言它会把一整句当成一个词。虽然可以用zhparser这类第三方解析器但配置和词典维护又是一套不小的工程。更重要的是业务方提出的需求有时很“模糊”他们想要的是类似百度搜索那种输入“技博”也能匹配到“技术博客”的宽松匹配这对基于分词的严格全文检索来说有点强人所难。就在这个当口我重新审视了需求发现很多场景并不需要复杂的语义分析和词干处理核心诉求其实是高效的、支持任意位置子字符串匹配的模糊查询。这时一个久闻其名但未曾深究的扩展进入了视野pg_bigm。它不是一个全文检索的替代品而是一个精准解决“模糊字符串匹配”的利器。它采用基于2-gram二元语法的分词方案将文本拆解成连续的两个字符的片段并建立 GIN 或 GiST 索引从而让LIKE %任意子串%这类查询也能飞起来。今天我就结合自己的实战经验详细拆解pg_bigm的两种主流安装方式编译安装和 Docker 安装帮你绕过我踩过的那些坑。2. 理解 pg_bigm为什么是它而不是 LIKE 或全文检索在动手安装之前我们必须先搞清楚pg_bigm到底解决了什么问题以及它的工作原理。这决定了你是否应该选择它而不是别的方案。2.1 传统模糊查询 LIKE 的瓶颈LIKE操作符是 SQL 标准的一部分简单直观。但它的性能问题根源在于缺乏有效的索引支持。PostgreSQL 可以为column LIKE ‘pattern%’后缀匹配创建 B-Tree 索引因为它是按字典序比较的。然而一旦模式以通配符开头‘%pattern’或两端都有通配符‘%pattern%’B-Tree 索引就完全失效了。数据库优化器知道这一点所以它会选择最“笨”但最保险的全表扫描Seq Scan。当你的表有上百万行时每次查询都扫全表后果可想而知。2.2 全文检索 (Full Text Search) 的局限与重量级PostgreSQL 的全文检索FTS是一套完整的解决方案它引入了tsvector文档向量和tsquery查询向量的概念支持词干提取、停用词过滤、权重排名等高级功能。对于精确的词语搜索它的效率和准确性非常高。但是它并不适合我们开头提到的场景分词依赖FTS 严重依赖于分词器Parser和词典。对于英文等有空格分隔的语言很友好但对中文、日文等需要额外安装和配置像zhparser这样的插件引入了额外的复杂性和维护成本。匹配逻辑FTS 匹配的是“词元”lexeme。查询“技术博客”它期望匹配包含“技术”和“博客”这两个词的文档。如果你查询“技博”它无法理解这是“技术博客”的子串因此不会返回结果。这与我们想要的“模糊包含”逻辑不同。功能冗余如果你的需求仅仅是“字符串A是否包含字符串B”而不需要词干处理、同义词、排名等高级功能那么 FTS 就显得过于“重量级”了。2.3 pg_bigm 的核心原理2-gram 分词法pg_bigm采用了截然不同的思路。它不关心词语的边界和语义只做最机械的切分将文本按顺序每两个字符作为一个单元2-gram进行拆分。举个例子字符串“技术博客”会被拆分为‘技术’‘术博’‘博客’。注意这里为了演示用中文词语表示实际上在插件内部处理的是字符。当你在pg_bigm生成的索引列上执行LIKE ‘%术博%’时查询过程是这样的查询条件“术博”本身也被拆分为 2-gram‘术博’因为只有两个字符所以只有一个单元。数据库利用pg_bigm创建的 GIN 索引快速查找所有包含了‘术博’这个 2-gram 的文本行。由于“技术博客”的 2-gram 集合{‘技术’ ‘术博’ ‘博客’}包含了‘术博’因此该行会被命中。这就是pg_bigm强大的地方它将一个无法索引的模糊匹配查询转化为了对一系列确定的、可索引的 2-gram 单元的集合包含查询。GIN 索引通用倒排索引正是为了高效处理这种“集合包含”查询而生的。一个重要特性与限制因为基于 2-gram所以查询条件的最小长度是2个字符。查询单字符如LIKE ‘%A%’是无法使用pg_bigm索引的会回退到全表扫描。这是选择pg_bigm前必须明确的业务边界。3. 编译安装 pg_bigm掌控每一个细节编译安装适合需要在生产环境或自定义环境中深度定制的场景。它能让你对插件的依赖、编译参数有完全的控制权。下面是我在 CentOS 7 / Rocky Linux 8 和 Ubuntu 20.04 环境下反复验证过的步骤。3.1 环境准备与依赖检查编译pg_bigm的前提是有一套完整的 PostgreSQL 开发环境。这里的“开发环境”指的是头文件和库文件不是让你去开发 PostgreSQL 本身。# 对于 CentOS 7 / Rocky Linux 8 系列 (使用 yum/dnf) sudo yum install -y postgresql13-devel # 请根据你的PG主版本号调整如12, 14, 15等 sudo yum groupinstall -y Development Tools # 对于 Ubuntu 20.04 / Debian 系列 (使用 apt) sudo apt update sudo apt install -y postgresql-server-dev-13 # 同样请调整版本号 sudo apt install -y build-essential关键经验postgresqlxx-devel或postgresql-server-dev-xx这个包至关重要。它提供了pg_config这个工具后续编译过程会用它来定位 PostgreSQL 的安装路径、编译器标志等。安装完后可以运行pg_config --version确认其版本与你运行的 PostgreSQL 服务版本一致。版本不一致是后续编译失败最常见的原因。3.2 获取源码与编译pg_bigm的源码托管在 GitHub 上。推荐下载最新的稳定版本 Release。# 选择一个工作目录例如 /usr/local/src cd /usr/local/src # 下载源码包以当时最新的 1.2 版本为例请检查 GitHub 更新 wget https://github.com/pgbigm/pg_bigm/archive/refs/tags/v1.2.tar.gz # 解压 tar -zxvf v1.2.tar.gz cd pg_bigm-1.2接下来是标准的 PostgreSQL 扩展编译安装三步曲# 1. 使用 pg_config 确保编译配置正确 make USE_PGXS1 PG_CONFIG/usr/pgsql-13/bin/pg_config # 2. 编译检查可选但推荐 make USE_PGXS1 PG_CONFIG/usr/pgsql-13/bin/pg_config installcheck踩坑实录PG_CONFIG参数不是必须的如果系统 PATH 里只有唯一一个pg_config可以省略。但在生产服务器上可能同时存在多个版本的 PostgreSQL比如系统自带的老版本和你自己安装的新版本。显式指定PG_CONFIG的绝对路径是最稳妥的做法它能确保插件编译链接到正确的 PostgreSQL 库上。你可以通过which pg_config或find / -name pg_config 2/dev/null来找到正确版本的路径。如果make installcheck运行通过你会看到一系列测试用例执行成功的输出。这能极大增强你后续使用插件的信心。3.3 安装与部署到数据库编译成功后安装其实是将编译好的二进制文件和 SQL 脚本复制到 PostgreSQL 的扩展目录中。# 3. 安装插件文件到 PostgreSQL 的扩展目录 sudo make USE_PGXS1 PG_CONFIG/usr/pgsql-13/bin/pg_config install安装完成后文件会被复制到类似/usr/pgsql-13/share/extension/和/usr/pgsql-13/lib/的目录下。但这只是将插件“注册”到了 PostgreSQL 系统中要在某个具体的数据库中使用它还需要执行CREATE EXTENSION。# 切换到 postgres 用户或其他有权限的数据库用户 sudo -u postgres psql -d your_database_name -- 在目标数据库中创建扩展 your_database_name# CREATE EXTENSION pg_bigm; CREATE EXTENSION看到CREATE EXTENSION就成功了。你可以验证一下-- 查看已安装的扩展 your_database_name# \dx List of installed extensions Name | Version | Schema | Description -------------------------------------------------------------------------------------------- pg_bigm | 1.2 | public | text similarity measurement and index support based on bigrams plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language -- 测试一个 pg_bigm 函数 your_database_name# SELECT show_bigm(技术博客); show_bigm --------------------- {技术,术博,博客}4. Docker 安装 pg_bigm极致便捷的沙盒体验如果你是在开发、测试环境或者追求快速搭建和销毁Docker 无疑是更优雅的选择。它完美地封装了所有依赖和环境问题。4.1 选择合适的基础镜像PostgreSQL 的官方 Docker 镜像已经为我们考虑到了扩展的需求。主要有两种策略使用官方镜像在容器启动时安装官方镜像postgres:13或其他版本包含了apt-get或yum包管理器允许你在容器启动脚本中安装编译环境和插件。这种方式灵活但每次构建容器都需要下载编译工具略慢且容器体积会增大。使用已集成扩展的衍生镜像社区有一些维护的镜像直接集成了pg_bigm。但出于安全和对版本的精确控制我更喜欢第一种方式因为我知道插件是如何被安装进去的。这里我们采用第一种方式并利用 Docker 的Dockerfile来构建一个包含pg_bigm的自定义镜像。4.2 编写 Dockerfile 与构建镜像创建一个目录在里面新建一个Dockerfile文件# 使用官方 PostgreSQL 13 镜像作为基础 FROM postgres:13-bullseye # 安装编译 pg_bigm 所需的依赖 RUN apt-get update apt-get install -y \ postgresql-server-dev-13 \ build-essential \ wget \ rm -rf /var/lib/apt/lists/* # 下载并编译安装 pg_bigm WORKDIR /tmp RUN wget -q https://github.com/pgbigm/pg_bigm/archive/refs/tags/v1.2.tar.gz \ tar -zxvf v1.2.tar.gz \ cd pg_bigm-1.2 \ make USE_PGXS1 \ make USE_PGXS1 install # 清理临时文件减小镜像体积 RUN rm -rf /tmp/pg_bigm-1.2 /tmp/v1.2.tar.gz这个Dockerfile做了以下几件事从postgres:13-bullseye镜像开始。安装postgresql-server-dev-13和build-essential等编译工具。下载pg_bigm1.2 版本源码。在容器内编译并安装make install会将文件复制到容器内 PostgreSQL 的正确位置。清理源码包保持镜像相对精简。在Dockerfile所在目录执行构建docker build -t my-postgres-with-pgbigm:13 .4.3 运行容器并初始化扩展镜像构建好后运行容器的方式和运行普通 PostgreSQL 容器几乎一样关键是要将数据库的数据目录持久化到宿主机并且通过初始化脚本自动创建扩展。首先准备一个初始化 SQL 脚本init.sql-- init.sql CREATE EXTENSION IF NOT EXISTS pg_bigm;然后使用docker run启动容器并挂载初始化脚本# 创建本地数据目录用于持久化 mkdir -p /path/to/my_pg_data # 运行容器 docker run -d \ --name my-pg-bigm \ -e POSTGRES_PASSWORDyour_strong_password \ -v /path/to/my_pg_data:/var/lib/postgresql/data \ -v $(pwd)/init.sql:/docker-entrypoint-initdb.d/init.sql \ -p 5432:5432 \ my-postgres-with-pgbigm:13参数解释-v /path/to/my_pg_data:/var/lib/postgresql/data将宿主机目录挂载到容器的 PostgreSQL 数据目录实现数据持久化。即使容器删除数据也不会丢失。-v $(pwd)/init.sql:/docker-entrypoint-initdb.d/init.sql这是关键官方 PostgreSQL 镜像在首次启动容器即数据目录为空时会自动执行/docker-entrypoint-initdb.d/目录下的.sh或.sql脚本。我们通过挂载将写好的init.sql放入该目录从而在数据库初始化时就自动执行CREATE EXTENSION pg_bigm;。-e POSTGRES_PASSWORD设置默认的postgres用户密码。容器启动后你可以连接进去验证docker exec -it my-pg-bigm psql -U postgres postgres# \dx pg_bigm5. 实战使用 pg_bigm 加速模糊查询安装只是第一步让插件真正产生价值在于使用。下面我们从一个完整的例子出发看看如何将pg_bigm应用到实际表中。5.1 创建测试表与生成数据假设我们有一张articles表其中content字段存储长文本内容。-- 1. 创建测试表 CREATE TABLE articles ( id SERIAL PRIMARY KEY, title VARCHAR(255), content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 2. 为 content 字段添加 pg_bigm 索引 -- 首先需要创建一个使用 pg_bigm 分词函数的表达式索引 CREATE INDEX idx_articles_content_bigm ON articles USING gin (content gin_bigm_ops); -- 注意也可以使用 GiST 索引GIN 查询更快但构建和维护更慢占用空间更大。 -- CREATE INDEX idx_articles_content_bigm ON articles USING gist (content gist_bigm_ops);索引选型心得GIN 还是 GiSTGIN (Generalized Inverted Index)更适合静态或更新不频繁的数据。查询速度极快但索引创建时间和磁盘占用通常比 GiST 高对写操作INSERT/UPDATE的负担也更重。GiST (Generalized Search Tree)索引构建更快占用空间更小支持更广泛的操作符虽然对于pg_bigm两者支持的操作符一样。查询速度通常略慢于 GIN但更适合写多读少或数据频繁更新的场景。我的建议对于搜索为主的业务读远多于写优先选择 GIN 索引。如果数据量巨大且更新频繁可以测试 GiST 索引是否能满足性能要求。在不确定时用小规模数据测试两者性能。5.2 见证性能差异有索引 vs 无索引让我们插入一些测试数据并模拟一个模糊查询。-- 插入一些模拟数据这里用简化方式实际应用数据量应更大 INSERT INTO articles (title, content) SELECT 测试文章标题 || n, 这是一篇关于PostgreSQL数据库性能优化中pg_bigm插件应用的技术博客文章详细阐述了如何通过二元语法分词来解决中文模糊查询的痛点。 || n FROM generate_series(1, 100000) AS n; -- 插入10万行 -- 确保收集表统计信息让查询规划器做出正确决策 ANALYZE articles;现在执行一个模糊查询并观察执行计划-- 先禁用索引扫描模拟无索引情况仅用于演示不要在生产环境使用 SET enable_indexscan off; SET enable_bitmapscan off; EXPLAIN ANALYZE SELECT id, title FROM articles WHERE content LIKE %性能优化%;你大概率会看到Seq Scan on articles即全表扫描执行时间可能在几百毫秒到几秒取决于你的机器性能。-- 恢复索引扫描 SET enable_indexscan on; SET enable_bitmapscan on; -- 再次执行查询 EXPLAIN ANALYZE SELECT id, title FROM articles WHERE content LIKE %性能优化%;这次执行计划应该变成了Bitmap Heap Scan on articles后面跟着Bitmap Index Scan using idx_articles_content_bigm。执行时间会锐减到几毫秒到几十毫秒。这就是索引的威力。5.3 关键操作符与函数pg_bigm不仅仅支持LIKE它提供了一组专用的操作符和函数功能更强大LIKE/ILIKE 这是最常用的。ILIKE是大小写不敏感版本。~/~* 支持正则表达式匹配。~*是大小写不敏感版本。%操作符 这是pg_bigm提供的相似度查询操作符。column % ‘关键词’会返回相似度大于阈值默认0.2的行。相似度计算基于重叠的 2-gram 数量。-- 查找与‘技术博客’相似的内容 SELECT id, title, content - 技术博客 AS distance FROM articles WHERE content % 技术博客 ORDER BY distance ASC LIMIT 10;pg_bigm使用-操作符表示 1 - 相似度即距离值越小越相似。show_bigm() 如前所述用于查看一个字符串被拆分成哪些 2-gram。similarity() 计算两个字符串的相似度0到1之间。6. 避坑指南与进阶思考在实际使用中我总结了一些常见的坑点和优化建议。6.1 索引失效的典型场景查询模式长度小于2这是硬性限制。LIKE ‘%A%’无法使用pg_bigm索引。业务上需要规避或做特殊处理如前端限制输入长度。查询条件中 2-gram 全部是停用词pg_bigm有一个内置的停用词列表如英文的 ‘a’ ‘the’ 中文的标点符号。如果你的查询词拆解后全是停用词索引也会失效。可以通过SELECT * FROM pg_bigm.stoplword;查看停用词列表。数据类型不匹配索引建立在text类型的列上如果你用varchar列去查询有时会因为隐式类型转换导致索引失效。确保查询条件的数据类型与索引列一致。使用了函数或表达式WHERE lower(content) LIKE ‘%abc%’这种写法会使索引失效。如果需要进行大小写不敏感搜索应直接使用ILIKE或者在建表时就将数据统一转为小写存储。6.2 性能与存储的权衡pg_bigm的 GIN 索引体积可能会很大通常是原文本数据的数倍。因为它需要存储所有可能的 2-gram 及其位置信息。在创建索引前务必评估磁盘空间。对于超大的文本字段比如超过数 KB 的文章可以考虑只对部分字段建立索引或者使用表达式索引只索引关键部分-- 只对 content 字段的前 1000 个字符建立索引 CREATE INDEX idx_articles_content_partial ON articles USING gin (substring(content, 1, 1000) gin_bigm_ops);这能显著减少索引大小但代价是只能加速对前1000个字符内子串的查询。6.3 与全文检索的混合使用策略pg_bigm和 PostgreSQL 全文检索FTS并不是互斥的它们可以协同工作。一个常见的混合策略是第一层pg_bigm进行快速、宽松的召回。用户输入一个模糊词先用pg_bigm快速筛选出一批可能相关的候选行。这一步效率极高能过滤掉绝大部分不相关数据。第二层FTS 进行精准排序。对pg_bigm筛选出的结果集再利用 FTS 的ts_rank函数进行相关性打分和排序将最相关的结果排在前面。这种“粗筛 精排”的架构在很多搜索场景下能取得比单一方案更好的效果和性能平衡。6.4 维护与监控像所有索引一样pg_bigm索引也需要维护。大量的INSERT、UPDATE、DELETE操作会导致索引膨胀影响查询性能。定期在业务低峰期对表执行REINDEX INDEX idx_articles_content_bigm;或VACUUM ANALYZE articles;是必要的维护操作。通过pg_stat_user_indexes视图可以监控索引的使用情况如果发现某个pg_bigm索引很少被使用idx_scan值很低就要反思查询模式是否真的匹配或者考虑是否应该删除这个索引以节省空间。