低代码到底是不是行业毒瘤?一线大厂怎么做的?戳此了解>>> 了解详情
写点什么

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

2015 年 5 月 13 日

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

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

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

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

  1. 创建一个列 tsv,存储 tsvector 值;
  2. 在新建的列上创建索引,并用下面的语句填充列:
复制代码
UPDATE data_rows SET tsv
=setweight(to_tsvector(coalesce(meta->>'title','')), 'A')
||setweight(to_tsvector(coalesce(text,'')), 'D');

此处需要注意,JSON 列的权重为 A,text 列的权重为 D;
3. 创建 tsv 列更新函数;
4. 在表上创建触发器,当更新和新增行时,执行 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 毫秒。


感谢崔康对本文的审校。

给InfoQ 中文站投稿或者参与内容翻译工作,请邮件至 editors@cn.infoq.com 。也欢迎大家通过新浪微博( @InfoQ @丁晓昀),微信(微信号: InfoQChina )关注我们,并与我们的编辑和其他读者朋友交流(欢迎加入 InfoQ 读者交流群InfoQ 好读者)。

2015 年 5 月 13 日 08:229009
用户头像

发布了 1008 篇内容, 共 315.1 次阅读, 收获喜欢 284 次。

关注

评论

发布
暂无评论
发现更多内容

机器学习算法评估指标——2D目标检测

做技术BP的文案Gou

匆忙的一周 ARTS第二周

困到清醒

原创 | 使用JUnit、AssertJ和Mockito编写单元测试和实践TDD (十三)编写测试-生命周期方法

编程道与术

Java 编程 TDD 单元测试 JUnit

从技术到管理,我在极客时间的成长历程

邓建春

游戏夜读 | 2020周记(5.24-5.31)

game1night

【Java 25周年有奖征文获奖名单公布!!!】关于Java,你最想赞扬、吐槽、期待的变化是什么?

InfoQ写作平台官方

写作平台 Java25周年 活动专区

Go语言分布式系统配置治理

田晓亮

微服务

一个人,沿着童年的路究竟可以走多远?

zhoo299

童年 NASA 航天

Linux 自动化运维工具 ansible

杨仪军

Linux 运维自动化

MySQL实战笔记-事务隔离和MVCC

shiziwen

MySQL 学习 事务隔离级别

奈学:传授“带权重的负载均衡实现算法”独家设计思路

奈学教育

分布式

MySQL的各种日志

超超不会飞

MySQL

Redis持久化了解一波!

不才陈某

redis 程序员 后端

情绪的力量:如何使用情绪来达成目标

七镜花园-董一凡

情绪

Python 自动化办公之"你还在手动操作“文件”或“文件夹”吗?"

JackTian

Python 自动化

我的 windows 利器

玄兴梦影

工具

# LeetCode 863. All Nodes Distance K in Binary Tree

liu_liu

算法 LeetCode

ARTS 第二周打卡

陈文昕

从 0 到 1 搭建技术中台之发布系统实践:集泳道、灰度、四端和多区域于一体的设计与权衡

伴鱼技术团队

架构 系统设计 系统架构 系统性思考 架构设计

这些Java8官方挖的坑,你踩过几个?

牧码哥

Java 踩坑 加密 「Java 25周年」

关于区块链的“去中心化”,90% 的人都搞错了

CECBC区块链专委会

CECBC 区块链技术 去中心化 专制

【求锤得锤的故事】Redis锁从面试连环炮聊到神仙打架。

why技术

redis 分布式锁 分布式系统

Vue生态篇(二)

shirley

Vue

程序员修炼的务实哲学

博文视点Broadview

程序员 软件 编程思维 工程师 编程之路

这是一个测试文档

Geek_073cad

互联网时代的界限管理

非著名程序员

程序员 职场 提升认知 界限管理

架构学习历程

你不知道的SSD那些事

焱融科技

分布式 存储 SSD nvme

开源分布式文件系统大检阅

焱融科技

sds 存储 开源项目 焱融科技 文件存储

杂谈-JSONP探索

卡尔

Java jsonp

patroni 通过服务启动报错

yafeishi

数据库 高可用 AntDB

2021 ThoughtWorks 技术雷达峰会

2021 ThoughtWorks 技术雷达峰会

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