利用PostgreSQL实现毫秒级全文检索

Postgre SQL 2020-06-22

Lateral是一家内容推荐服务提供商，其模拟程序使用Postgresql存储文档。每个文档包含一个 text列和一个存储标题、日期和URL等元数据的JSON列。他们希望为模拟程序创建快速搜索功能，搜索文档全文和标题，生成推荐内容。近日，Lateral首席技术官Max撰文介绍了他们的做法。

为了实现这一目标，可以选择开源解决方案Apache Solr或Elasticsearch，也可以选择托管解决方案Elastic或Algolia，但出于以下考虑，他们选择了Postgresql的全文搜索功能：

不需要额外安装软件或库
可以重用他们在应用程序中使用的数据库接口
不需要配置额外的服务器
不增加成本
数据可以存储在可控的地方
不需要在不同的数据源之间同步数据

虽然Postgresql 搜索的精度和大规模查询速度存在缺陷，但Max认为，它可以满足他们的应用场景。以下是他们的做法：

创建一个列tsv，存储tsvector值；

在新建的列上创建索引，并用下面的语句填充列：

UPDATE data_rows SET tsv
=setweight(to_tsvector(coalesce(Meta->>'title','')),'A') 
||setweight(to_tsvector(coalesce(text,'D');

此处需要注意，JSON列的权重为A，text列的权重为D；

创建tsv列更新函数；在表上创建触发器，当更新和新增行时，执行tsv列更新函数。

当一切就绪后，替换下面代码中的“你的查询”并执行：

SELECT id,Meta->>'title' as title,Meta FROM (
  SELECT id,Meta,tsv
  FROM data_rows,plainto_tsquery('你的查询') AS q
  WHERE (tsv @@ q)
) AS t1 ORDER BY ts_rank_cd(t1.tsv,plainto_tsquery('你的查询')) DESC LIMIT 5;

经测试，该查询大约50毫秒即可完成。如果返回文档全文，则会增加大约350毫秒，这更多的可能是受网络负载影响。如果只返回文档中的200个字符，则仅仅增加大约100毫秒。

PosegreSQL基础回顾(第 4 章 SQL语法)

来源：http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键词必须以一个...

PosegreSQL基础回顾(第 8 章数据类型)

来源：http://www.postgres.cn/docs/11/ 8.1. 数字类型数字类型由2、4或8字节的整数以及4或8...

PosegreSQL基础回顾(第 5 章数据定义)

来源：http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。当一个表被读...

PosegreSQL基础回顾(第 6 章数据操纵、第 7 章查询)

来源：http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据有时在修改行的操作过程中...

PosegreSQL基础回顾(第 13 章并发控制)

来源：http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别读已提交是PostgreSQL中的...

PosegreSQL基础回顾(第 9 章函数和操作符)

来源：http://www.postgres.cn/docs/11/ 9.7. 模式匹配 PostgreSQL提供了三种独立的实现模式匹...

利用PostgreSQL实现毫秒级全文检索

相关文章