标准化数据是 10x 工程组织利用的强大工具。如果你还没有读过这篇文章！我们将在这篇文章中超越这一主张，但重申要点，
Postgres 支持：
- 词干
- 排名/提升
- 多种语言
- 拼写错误的模糊搜索
- 口音支持
这对于大多数用例来说已经足够好了，而不会给您的应用程序带来任何额外的问题。但是，如果您曾经尝试过大规模提供相关搜索结果，您会意识到您需要的远不止这些基础知识。ElasticSearch 具有各种同类最佳功能，例如最先进的 BM25 的修改版本（在 1970 年代开发），这是 Postgres 使用的基于词频 (TF) 的排名之外您需要的众多功能之一...但是，ElasticSearch 方法是一个死胡同，原因有两个：
- 尝试使用 TF-IDF 和 BM25 等统计数据提高搜索相关性就像尝试制造飞行汽车一样。你想要的是一架直升机。
- 为 BM25 计算逆文档频率 (IDF) 会降低您的搜索索引性能，这导致通过分布式计算引发大量后续问题，原因最初是可疑的。
学者们花了几十年的时间发明了许多算法，这些算法使用更多数量级的计算来勉强获得稍微好一点的结果，而这些结果在实践中通常是不值得的。不要普遍贬低学术界，他们的工作一直在改善我们的世界，但我们需要注意权衡。SQL 是另一个在 1970 年代类似地首创的首字母缩写词。SQL 和 BM25 之间的一个区别是每个人在阅读这篇博文之前都听说过前者，这是有充分理由的。
如果您确实想要有意义地改进搜索结果，通常需要添加新的数据源。相关性通常通过其他事物与文档相关的方式来揭示，而不是文档本身的内容。谷歌在 23 年前就证明了这一点。Pagerank 不依赖于页面内容本身，而是使用来自页面链接的元数据。我们生活在一个相互关联的世界中，事物之间的相互作用揭示了它们的相关性，无论是网站链接、产品销售、社交帖子分享……重要的是文档周围的更大背景。
如果你想改善你的搜索结果，不要依赖昂贵的 O(n*m) 词频统计。取而代之的是获取新的数据源。相关性的关系性质是关系数据库形成理想搜索引擎的基础。
Postgres 做出了正确的决定，以避免在搜索索引中计算逆文档频率所需的成本，因为它的好处微乎其微。相反，它提供了功能最完整的关系数据平台。Elasticsearch 会告诉你，你不能在读取时将数据加入一个天真的分布式系统中，因为它非常昂贵。相反，您必须在索引时急切地加入数据，这更加昂贵。这对他们的业务有好处，因为您是为它买单的人，并且它会扩展直到您破产。
您真正应该做的是将数据标准化留在 Postgres 中，这将允许您在查询时加入额外的相关数据。索引和搜索规范化语料库所需的计算量将减少多个数量级，这意味着在需要分配工作负载之前您将有更长的时间（可能永远），然后也许您可以智能地而不是天真地做到这一点。与其花时间构建和维护管道以在系统之间随机更新，您可以处理新的数据源以真正提高相关性。
使用 PostgresML，当您拥有相关数据时，您现在可以直接跳到完整的机器学习上。您可以将特征存储加载到与搜索语料库相同的数据库中。每个数据源都可以存在于自己的独立表中，具有自己的更新节奏，而不必在单个事物发生变化时将整个文档重新索引和反规范化回 ElasticSearch，或者更糟的是，整个语料库的大部分。
使用单个 SQL 查询，您可以进行多次重新排名、修剪和个性化以优化搜索相关性分数。
- 基本术语相关性
- 嵌入相似性
- XGBoost 或 LightGBM 推理
这些查询可以在具有 Postgres 的多索引策略的大型生产规模的语料库上以毫秒为单位执行。您可以在不向堆栈添加任何新基础架构的情况下完成所有这些工作。
以下完整示例仅用于演示第三代搜索引擎。您可以在 PostgresML Gym 中对其进行真实测试，以建立完整的理解。
试试 PostgresML Gym
搜索.sql
WITH query AS (
-- construct a query context with arguments that would typically be
-- passed in from the application layer
SELECT
-- a keyword query for "my" OR "search" OR "terms"
tsquery('my | search | terms') AS keywords,
-- a user_id for personalization later on
123456 AS user_id
),
first_pass AS (
SELECT *,
-- calculate the term frequency of keywords in the document
ts_rank(documents.full_text, keywords) AS term_frequency
-- our basic corpus is stored in the documents table
FROM documents
-- that match the query keywords defined above
WHERE documents.full_text @@ query.keywords
-- ranked by term frequency
ORDER BY term_frequency DESC
-- prune to a reasonably large candidate population
LIMIT 10000
),
second_pass AS (
SELECT *,
-- create a second pass score of cosine_similarity across embeddings
pgml.cosine_similarity(document_embeddings.vector, user_embeddings.vector) AS similarity_score
FROM first_pass
-- grab more data from outside the documents
JOIN document_embeddings ON document_embeddings.document_id = documents.id
JOIN user_embeddings ON user_embeddings.user_id = query.user_id
-- of course we be re-ranking
ORDER BY similarity_score DESC
-- further prune results to top performers for more expensive ranking
LIMIT 1000
),
third_pass AS (
SELECT *,
-- create a final score using xgboost
pgml.predict('search relevance model', ARRAY[session_level_features.*]) AS final_score
FROM second_pass
JOIN session_level_features ON session_level_features.user_id = query.user_id
)
SELECT *
FROM third_pass
ORDER BY final_score DESC
LIMIT 100;如果您想通过交互式笔记本在 Postgres 数据库中生成搜索相关性模型，请在 PostgresML Gym 中尝试。对好奇的读者来说，一个练习是将上述所有三个分数组合成一个代数函数进行排名，然后组合成第四个学习模型......
非常感谢和❤️所有支持这项努力的人。我们很乐意听取更广泛的 ML 和工程社区关于应用程序和其他现实世界场景的反馈，以帮助确定我们工作的优先级。
原文标题：Postgres Full Text Search is Awesome!
原文作者：Montana Low
原文链接：https://postgresml.org/blog/postgres-full-text-search-is-awesome/
