要说时下最流行、最优雅、功能最强大的数据库必然离不了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 $q

UNION SELECT c.name FROM city c WHERE c.name ILIKE $q

...

ORDER BY name

LIMIT 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 s

WHERE 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.name

FROM search_view s

ORDER BY ts_rank(

ㅤㅤs.search_vector,

ㅤㅤto_tsquery('english', $q)) DESC

LIMIT 10;

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

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

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

Google → Alphabet

X→Facebook → Meta

JPMorgan 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很好地满足了其业务模式和规模，并且通过利用该解决方案，每月节省了数百至数千刀的潜在费用。

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