BT

如何利用碎片时间提升技术认知与能力? 点击获取答案

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

| 作者 谢丽 关注 9 他的粉丝 发布于 2015年5月14日. 估计阅读时间: 3 分钟 | GMTC大前端的下一站,PWA、Web框架、Node等最新最热的大前端话题邀你一起共同探讨。

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

为了实现这一目标,可以选择开源解决方案Apache SolrElasticsearch,也可以选择托管解决方案ElasticAlgolia,但出于以下考虑,他们选择了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好读者)。

评价本文

专业度
风格

您好,朋友!

您需要 注册一个InfoQ账号 或者 才能进行评论。在您完成注册后还需要进行一些设置。

获得来自InfoQ的更多体验。

告诉我们您的想法

允许的HTML标签: a,b,br,blockquote,i,li,pre,u,ul,p

当有人回复此评论时请E-mail通知我
社区评论

允许的HTML标签: a,b,br,blockquote,i,li,pre,u,ul,p

当有人回复此评论时请E-mail通知我

允许的HTML标签: a,b,br,blockquote,i,li,pre,u,ul,p

当有人回复此评论时请E-mail通知我

讨论

登陆InfoQ,与你最关心的话题互动。


找回密码....

Follow

关注你最喜爱的话题和作者

快速浏览网站内你所感兴趣话题的精选内容。

Like

内容自由定制

选择想要阅读的主题和喜爱的作者定制自己的新闻源。

Notifications

获取更新

设置通知机制以获取内容更新对您而言是否重要

BT