【PostgreSQL从零到精通】第31篇：全文检索实战——构建postgresql自己的搜索引擎
·
上一篇【第30篇】序列与咨询锁——并发控制的实用工具
下一篇【第32篇】PostgreSQL特色功能拾遗——数组、并行查询与FDW
很多开发者在需要搜索功能时，第一时间想到的是 Elasticsearch 或 Solr。但其实 PostgreSQL 内置了强大的全文检索引擎，对于中小规模的搜索需求（百万级文档），完全够用，而且零额外部署成本。本文带你从零搭建 PostgreSQL 全文检索系统。
一、全文检索核心概念
1.1 文档与查询
全文检索将文本分成两个维度处理：
全文检索核心流程：
┌──────────┐ 分词+标准化 ┌───────────┐ 匹配 ┌───────────┐
│ 原始文档 │ ──────────────→ │ tsvector │ ──────→ │ 搜索结果 │
│ "PostgreSQL │ │ 文档向量 │ │ + 排名 │
│ is great" │ │ │ │ │
└──────────┘ └───────────┘ └───────────┘
↑ 匹配
┌───────────┐
│ tsquery │
│ 查询向量 │
└───────────┘
↑ 分词+标准化
┌───────────┐
│ "search" │
└───────────┘
1.2 tsvector——文档向量
-- to_tsvector：将文本转换为文档向量（自动分词、标准化）
SELECT to_tsvector('english', 'PostgreSQL is a great database');
-- 结果: 'databas':5 'great':4 'postgresql':1
-- 解释：PostgreSQL → 'postgresql'(位置1), is → 停止词(忽略),
-- a → 停止词(忽略), great → 'great'(位置4), database → 'databas'(位置5)
-- 查看分词细节
SELECT ts_lexize('english_stem', 'databases');
-- 结果: {databas}（词干提取：databases → databas）
-- 也可以手动创建 tsvector
SELECT 'a:1 cat:2 dog:3'::tsvector;
-- 结果: 'a':1 'cat':2 'dog':3
1.3 tsquery——查询向量
-- to_tsquery：将查询词转换为查询向量
SELECT to_tsquery('english', 'database');
-- 结果: 'databas'
-- 组合查询（AND / OR / NOT / <-> 跟随操作）
SELECT to_tsquery('english', 'postgres & database'); -- AND
SELECT to_tsquery('english', 'postgres | database'); -- OR
SELECT to_tsquery('english', 'postgres & !database'); -- NOT
SELECT to_tsquery('english', 'postgres <-> database'); -- 跟随（相邻）
--plainto_tsquery：简化版，自动用 AND 连接
SELECT plainto_tsquery('english', 'great postgresql database');
-- 结果: 'great' & 'postgresql' & 'databas'
-- websearch_to_tsquery：支持引号和 - 排除（PostgreSQL 11+）
SELECT websearch_to_tsquery('english', '"postgresql database" -mysql');
-- 结果: 'postgresql' <-> 'databas' & !'mysql'
1.4 匹配操作符 @@
-- @@ 操作符：检查文档是否匹配查询
SELECT to_tsvector('english', 'PostgreSQL is a great database')
@@ to_tsquery('english', 'database');
-- 结果: true
SELECT to_tsvector('english', 'PostgreSQL is a great database')
@@ to_tsquery('english', 'mysql');
-- 结果: false
-- 反向匹配
SELECT to_tsvector('english', 'PostgreSQL is a great database')
!! to_tsquery('english', 'mysql');
-- 结果: true（!! 等同于 NOT）
二、创建全文检索表
2.1 基本实现
-- 创建文章表
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
author TEXT,
published_at TIMESTAMP DEFAULT NOW(),
-- 预计算 tsvector 列（推荐方式）
search_vector TSVECTOR
);
-- 创建 GIN 索引（全文检索首选）
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);
-- 创建触发器自动更新 search_vector
CREATE OR REPLACE FUNCTION articles_search_update()
RETURNS TRIGGER AS $$
BEGIN
-- 将 title 和 content 合并，title 权重更高（A），content 权重较低（B）
NEW.search_vector :=
setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER articles_search_trigger
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_update();
-- 插入测试数据
INSERT INTO articles (title, content, author) VALUES
('PostgreSQL Tutorial', 'Learn PostgreSQL database from scratch with practical examples and exercises', 'Alice'),
('MySQL vs PostgreSQL', 'A comprehensive comparison between MySQL and PostgreSQL database systems', 'Bob'),
('Advanced SQL Queries', 'Master complex SQL queries including joins, subqueries, window functions and CTEs', 'Charlie'),
('Database Performance', 'Tips for optimizing database performance including indexing, query tuning and caching', 'Alice'),
('Python PostgreSQL', 'How to connect Python applications to PostgreSQL using psycopg2 library', 'Dave');
2.2 搜索与排名
-- 基本搜索
SELECT title, author
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');
-- 带排名的搜索（ts_rank）
SELECT title, author,
ts_rank(search_vector, to_tsquery('english', 'postgresql & database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database')
ORDER BY rank DESC;
-- 使用 plainto_tsquery（用户输入的搜索框）
-- 应用层只需将用户输入传给 plainto_tsquery
SELECT title,
ts_rank(search_vector, plainto_tsquery('english', 'postgresql database tutorial')) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database tutorial')
ORDER BY rank DESC;
-- 带权重排名的搜索（ts_rank_cd）
-- 权重 A=1.0, B=0.4, C=0.2, D=0.1（A最重）
SELECT title, author,
ts_rank_cd(
'{0.1, 0.4, 1.0, 1.0}', -- 自定义 D,C,B,A 权重
search_vector,
plainto_tsquery('english', 'postgresql')
) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql')
ORDER BY rank DESC;
-- 高亮匹配的词（使用 ts_headline）
SELECT title,
ts_headline('english', content,
plainto_tsquery('english', 'database'),
'StartSel=<b>, StopSel=</b>, MaxWords=50, MinWords=25'
) AS highlighted_content
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database');
-- 结果: ...a great <b>database</b> from scratch...
三、高级搜索功能
3.1 多字段搜索
-- 搜索指定字段
SELECT title
FROM articles
WHERE setweight(to_tsvector('english', title), 'A') @@
to_tsquery('english', 'postgresql');
-- 同时搜索标题和内容（不同权重）
SELECT title, author,
ts_rank(
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', content), 'B'),
plainto_tsquery('english', 'performance database')
) AS rank
FROM articles
WHERE setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', content), 'B') @@
plainto_tsquery('english', 'performance database')
ORDER BY rank DESC;
3.2 短语搜索
-- 搜索相邻的词组（<-> 操作符）
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'sql <-> queries');
-- 匹配 "sql queries"（相邻），不匹配 "complex sql ... queries"（中间有其他词）
-- 限定距离
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'sql <2> queries');
-- sql 和 queries 之间最多隔 1 个词
3.3 前缀搜索
-- 搜索以指定前缀开头的词
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgre:*');
-- 匹配 postgresql, postgres 等
-- 组合前缀搜索
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgre:* & datab:*');
四、自定义词典
4.1 创建自定义词典
-- 创建自定义同义词词典
CREATE TEXT SEARCH DICTIONARY my_synonyms (
TEMPLATE = synonym,
SYNONYMS = my_synonyms
);
-- 创建词典配置文件 my_synonyms.syn
-- 内容：
-- pg postgresql
-- db database
-- nosql not_only_sql
-- 创建自定义文本搜索配置
CREATE TEXT SEARCH CONFIGURATION my_chinese (
COPY = english
);
4.2 自定义停止词
-- 查看默认停止词列表
SELECT * FROM ts_stop_words('english');
-- 创建自定义停止词词典
CREATE TEXT SEARCH DICTIONARY my_stopwords (
TEMPLATE = simple,
STOPWORDS = my_stopwords -- 创建 my_stopwords.stop 文件
);
-- 自定义停止词文件示例：
-- a
-- an
-- the
-- is
-- are (可以添加中文停止词)
五、中文全文检索
5.1 使用 zhparser 扩展
PostgreSQL 默认不支持中文分词，需要安装 zhparser 扩展。
-- 安装 zhparser 后
CREATE EXTENSION zhparser;
-- 创建中文搜索配置
CREATE TEXT SEARCH CONFIGURATION chinese_zh (
PARSER = zhparser
);
-- 设置分词策略
ALTER TEXT SEARCH CONFIGURATION chinese_zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;
-- 测试中文分词
SELECT to_tsvector('chinese_zh', 'PostgreSQL是一个强大的开源数据库');
-- 结果会按中文词汇切分
-- 创建中文全文检索表
CREATE TABLE chinese_articles (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
search_vector TSVECTOR
);
-- 创建触发器
CREATE OR REPLACE FUNCTION chinese_search_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('chinese_zh', COALESCE(NEW.title, '')), 'A') ||
setweight(to_tsvector('chinese_zh', COALESCE(NEW.content, '')), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER chinese_articles_trigger
BEFORE INSERT OR UPDATE ON chinese_articles
FOR EACH ROW EXECUTE FUNCTION chinese_search_update();
-- 创建索引
CREATE INDEX idx_chinese_articles_search ON chinese_articles USING gin (search_vector);
-- 中文搜索
INSERT INTO chinese_articles (title, content) VALUES
('数据库入门', '本文介绍PostgreSQL数据库的基本概念和使用方法'),
('性能优化', '数据库性能优化是一个复杂的话题，涉及索引、查询优化等多个方面');
SELECT title,
ts_rank(search_vector, to_tsquery('chinese_zh', '数据库')) AS rank
FROM chinese_articles
WHERE search_vector @@ to_tsquery('chinese_zh', '数据库')
ORDER BY rank DESC;
六、全文检索 vs Elasticsearch
全文检索方案对比：
┌──────────────────┬────────────────────┬────────────────────┐
│ 对比维度 │ PostgreSQL FTS │ Elasticsearch │
├──────────────────┼────────────────────┼────────────────────┤
│ 部署成本 │ 零（内置） │ 需要独立集群 │
├──────────────────┼────────────────────┼────────────────────┤
│ 数据规模 │ 百万级文档 │ 十亿级文档 │
├──────────────────┼────────────────────┼────────────────────┤
│ 搜索延迟 │ 10-100ms │ 1-10ms │
├──────────────────┼────────────────────┼────────────────────┤
│ 中文分词 │ 需安装 zhparser │ 内置 ik 分词器 │
├──────────────────┼────────────────────┼────────────────────┤
│ 相关性算法 │ ts_rank/tf-idf │ BM25（更先进） │
├──────────────────┼────────────────────┼────────────────────┤
│ 聚合/分析 │ 基础 │ 非常强大 │
├──────────────────┼────────────────────┼────────────────────┤
│ 高亮/建议 │ ts_headline │ 非常丰富 │
├──────────────────┼────────────────────┼────────────────────┤
│ 数据一致性 │ 事务保证 │ 需要同步机制 │
├──────────────────┼────────────────────┼────────────────────┤
│ 运维复杂度 │ 低 │ 高 │
├──────────────────┼────────────────────┼────────────────────┤
│ 适用场景 │ 中小项目/内部系统 │ 大型搜索/电商平台 │
└──────────────────┴────────────────────┴────────────────────┘
七、性能优化
-- 1. 使用预计算的 tsvector 列（不要在查询时实时计算）
-- ❌ 慢：每次查询都要计算 to_tsvector
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database');
-- ✅ 快：使用预计算的列 + GIN 索引
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');
-- 2. GIN vs GiST 索引选择
-- GIN：查询更快，索引更大，更新成本高
-- GiST：查询稍慢，索引更小，更新成本低
-- 频繁更新的表考虑 GiST
-- 3. 定期更新统计信息
ANALYZE articles;
-- 4. 对大表使用 VACUUM ANALYZE
VACUUM ANALYZE articles;
-- 5. 搜索建议：使用 websearch_to_tsquery 解析用户输入
-- 它更安全，支持引号、排除等语法
八、总结
PostgreSQL 内置的全文检索引擎功能完备，适合中小规模搜索需求。核心流程是：
- 将文本转换为
tsvector（预计算 + GIN 索引） - 将查询转换为
tsquery - 使用
@@匹配，ts_rank排名
对于中文场景，安装 zhparser 扩展即可支持中文分词。
下一篇，我们回顾 PostgreSQL 的数组操作、并行查询和 SQL/MED等特色功能。
标签：PostgreSQL、全文检索、tsvector、tsquery、GIN索引、zhparser、搜索
上一篇【第30篇】序列与咨询锁——并发控制的实用工具
下一篇【第32篇】PostgreSQL特色功能拾遗——数组、并行查询与FDW
北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)