实践案例:使用PostgreSQL构建可扩展搜索

安全运维得看我 2024-04-02 02:24:07

要说时下最流行、最优雅、功能最强大的数据库必然离不了PostgreSQL,很多企业将其基础架构的数据库都迁移成了PostgreSQL。本文我们就学习一个实践案例,使用PostgreSQL构建起全新的支撑搜索系统,实现快速模糊搜索,即时呈现结果。

概述

在Levels.fyi是一个在线招聘网站。其架构中要求可以实现处理月度千万搜索,并且p99的查询性能的目标不高于百分之二秒(20ms)。

业务量

企业 3.5w+城市 6200+区域 300+国家 180+标题 100+专业 40+

六个表中的总记录数超过42000条记录。 除此之外,另外还支持复合搜索类型,例如 Company x Title (Google Software Engineer)和Title x Company (Software Engineer Google/Software Engineer at Google),从而产生数十万条附加记录。

简单查询

以一个简单搜索子为例,搜索JPMorgan Chase。起始SQL查询可能如下所示:

@set q = 'jp%'SELECT c.name FROM company c WHERE c.name ILIKE $qUNION SELECT c.name FROM city c WHERE c.name ILIKE $q...ORDER BY nameLIMIT 10;

该查询对所有表执行顺序扫描。为了优化这一点,可以简单地在搜索列上添加一个小写的BTREE索引,并使用LIKE代替 ILIKE进行查询。否则数据库就不会走索引。

为了提高查找效率,第一个优化就是创建索引:

CREATE INDEX company_name_lower_index ON company(LOWER(name));CREATE INDEX city_name_lower_index ON city(LOWER(name));...

注意:使用CREATE INDEX会对表锁写入和更新。如果不允许该锁定可以CREATE INDEX CONCURRENTLY代替。

优化

创建如此多的索引并聚合如此多的表并不是最优的。为了更进一步优化,使用物化视图,会创建一个单独的基于磁盘存储的实体,因此支持索引。当然要考虑其持续更新的问题。

CREATE MATERIALIZED VIEW search_view ASㅤㅤSELECT c.name FROM company c UNIONㅤㅤSELECT c.name FROM city c UNION ...;CREATE INDEX search_view_name_lower_indexㅤㅤON search_view(LOWER(name));

之后搜索查询语句为:

@set q = 'jp%'SELECT s.name FROM search_view sWHERE LOWER(s.name) LIKE LOWER($q)ORDER BY s.name;

为了保持物化视图同步,使用事件桥规则,即每隔一段时间调用一次lambda X分钟刷新物化视图。这意味着搜索结果最多可能会过时X分钟,最终仍然会保持一致。

可以考虑使用定时任务,闲时实施INDEX ONLY SCAN通过在索引中包含所有选定的列可以减少堆查找的需要,但这种方法确实有其局限性。

核心问题

这样搜索确实有局限性。例如,搜索jp morgan%不会产生任何结果,因为存储的名称是JPMorgan Chase。甚至是前缀+后缀查询-%jp morgan%在这里没有帮助。 使用任意通配符扩展搜索%jp%morgan%会有帮助,但它仍然无法进行jp chase%这样搜索。口音和语言是也是一个要考虑的问题,如果用户搜索the sofy,可能真正想搜sofi,th是一个停用词并且可能会被误听为y。

通配符搜索很有帮助,但随着查询变得更加随意,这些搜索开始崩溃。随着用户搜索的多样性,还需要弄清楚如何对搜索结果进行排名。所以需要更进一步优化处理。

非前缀搜索会导致顺序扫描。要有效地使用任意通配符进行搜索,可以使用三元组索引。

全文搜索

直接搜索姓名栏是非常具有挑战性的。为了改善搜索结果,转而使用全文搜索tsvector,一种允许文本搜索的数据类型。

@set q = 'the:*&jp:*&chase:*'SELECTㅤㅤs.name,ㅤㅤto_tsvector('english', s.name),ㅤㅤto_tsquery('english', $q),ㅤㅤts_rank(ㅤㅤㅤㅤto_tsvector('english', s.name),ㅤㅤㅤㅤto_tsquery('english', $q))FROM search_view s;

为了消除name转换的tsvector开销到,嵌入tsvector作为物化视图中预先计算的实体。还使用广义倒排索引(Trigram)来优化查询的运行时间。经过这些更改,搜索查询修改为:

@set q = 'the:*&jp:*&chase:*'SELECT s.nameFROM search_view sORDER BY ts_rank(ㅤㅤs.search_vector,ㅤㅤto_tsquery('english', $q)) DESCLIMIT 10;

对于像这样的查询D E Shaw,新方法审查排名和搜索列:

还有其他方法可以将查询转换为向量,例如websearch_to_tsquery,不同类型的排名方法,例如similarity, ts_rank_cd并突出显示类似的帮助者 ts_headline这可能更适合某些用例

搜索别名

有几家公司有不同的名称,要么是因为业务重塑,要么是因为它们在不同名称之间有很强的联系:

Google → AlphabetX→Facebook → MetaJPMorgan Chase → JPMC

通过维护可能的替代公司名称的完整列表。 为了在搜索中考虑备用名称,搜索列变成名称及其别名的组合,并自动在搜索结果中考虑它们。

调整排名

当寻找goo,很可能是在搜索Google但查询则会返回:

虽然这些结果与每个词位匹配具有相同的排名,但它并不能提供最佳的用户体验。 为了解决这个问题,通过开发相关性算法similarity和ts_rank。问题是,但是需要确定哪个结果更有可能被点击呢?一个简单的指标是结果收到的点击次数,这样确保结果越受欢迎,排名就越高。可以使用的一些参数为:

完全匹配(排名#1)

使用匹配词位的频率ts_rank

使用相似度得分similarity

记录类型

搜索结果的受欢迎程度

结果别名与查询之间的相似度

结果字符串长度的倒数

这种方法使得够微调搜索排名并向用户提供相关结果。

复合搜索

注意到用户正在组合搜索类型,例如“Product Manager at Coinbase”或“Coinbase Product Manager”。这带来了挑战,因为按公司搜索会将用户引导至薪资页面,之后用户仍然需要导航至其职位。为了消除该额外的步骤,新添加了对复合类型的支持,特别是“Company x Title”和“Title x Company”组合。

由于使用是物化视图,因此使用公司和标题表交叉联接的结果更新其定义非常简单。还添加了一些额外的检查,以避免引导用户访问不存在的页面。

总结

Levels.fyi的案例中通过迁移到PostgreSQL,他们获得了先进的查询功能和经济高效、可扩展的解决方案来满足我们的数据需求。PostgreSQL很好地满足了其业务模式和规模,并且通过利用该解决方案,每月节省了数百至数千刀的潜在费用。

这个案例给了我们一个可以学习的案例,如何使用一个新的强大的工具重建我们的基础架构。当然探索一个新的工具是要一定的探索的精神和风险在里头的,但是一旦成功则可以带来架构优化、额外新增功能、后续可扩展性,以及直接的性能上和成本上的改善。

0 阅读:1

安全运维得看我

简介:感谢大家的关注