Why we built recall-first search on PostgreSQL
Why we built recall-first search on PostgreSQL
为什么我们选择在 PostgreSQL 上构建“召回优先”的搜索系统
Our careers page search often felt broken. Searching for “Java” could return JavaScript roles. A misspelling could produce no results at all. Searching for “manager” could collect assistant-manager vacancies across unrelated departments, without distinguishing what those jobs involved. The implementation was matching words inside job metadata. It did not understand spelling variations, equivalent terminology, or descriptions of the work a candidate wanted to do. Relevant vacancies could disappear simply because the candidate and employer used different words.
我们的招聘页面搜索功能经常显得“坏了”。搜索“Java”可能会返回 JavaScript 相关的职位；拼写错误可能导致没有任何结果；搜索“manager”可能会抓取到各个不相关部门的助理经理职位，而无法区分这些工作的具体内容。原有的实现方式只是在职位元数据中进行简单的关键词匹配，它无法理解拼写变体、等效术语，也无法理解候选人想要从事的工作描述。仅仅因为候选人和雇主使用的词汇不同，相关的职位就可能消失不见。
Replacing that behavior with hybrid retrieval solved part of the problem—and exposed another. Our first hybrid evaluation achieved an NDCG@10 of 0.9944, yet returned precision@5 on semantic queries was only 55%. We could now find relevant jobs that literal matching missed, but we still had to decide how many weak matches should accompany them. We eventually built selective hybrid retrieval inside our existing Next.js and PostgreSQL stack, using our existing OpenAI integration for embeddings.
用混合检索（Hybrid Retrieval）替代原有方案解决了一部分问题，但也暴露了新的问题。我们首次进行的混合检索评估中，NDCG@10 指标达到了 0.9944，但语义查询的 precision@5（前 5 个结果的精确率）仅为 55%。虽然我们现在能找到字面匹配所遗漏的相关职位，但我们仍需决定应该包含多少“弱匹配”结果。最终，我们利用现有的 Next.js 和 PostgreSQL 技术栈，结合已有的 OpenAI 嵌入（Embedding）集成，构建了选择性混合检索系统。
Before choosing that design, we considered a dedicated search engine, managed search, and self-hosted inference. Each would have solved part of the problem while changing which systems we had to operate and keep consistent. Choosing the smaller architecture left substantial engineering work of our own. The evaluation would force us to define what a useful result set meant. Production would test whether that definition survived stale data, late responses, and provider failures.
在确定该方案前，我们考虑过专用搜索引擎、托管搜索服务以及自托管推理模型。每种方案都能解决部分问题，但同时也改变了我们需要运维和保持一致性的系统范围。选择这种较小的架构意味着我们需要承担大量的工程工作。评估过程迫使我们定义什么是“有用的结果集”，而生产环境则会考验这一定义在面对陈旧数据、延迟响应和供应商故障时是否依然有效。
The limits of fixing keyword matching
修复关键词匹配的局限性
The keyword path preceding hybrid search assembled job titles, categories, skills, locations, and other metadata into lowercase text. A job matched when every query term appeared somewhere in that text. This was easy to understand, but its failure modes followed directly from the representation. “kubernets” did not contain “kubernetes”. Aliases needed explicit treatment. A description of responsibilities could fail to overlap with the title or metadata. Conversely, a substring match could blur distinctions such as Java and JavaScript.
在混合搜索之前的关键词路径中，我们将职位标题、类别、技能、地点和其他元数据汇集成小写文本。当查询中的每个词都出现在该文本中时，职位即被匹配。这种方式易于理解，但其失效模式直接源于这种表示方法：“kubernets”不包含“kubernetes”；别名需要显式处理；职责描述可能与标题或元数据没有重叠。反之，子字符串匹配可能会模糊 Java 和 JavaScript 之间的区别。
Why we didn’t reach for Elasticsearch
为什么我们没有选择 Elasticsearch
Elasticsearch was an obvious option to consider. It would give careers search a dedicated engine rather than requiring us to assemble retrieval inside the application database. We rejected it early because we could not justify introducing that additional system for this workload. PostgreSQL already held the jobs and the fields that determined eligibility. It could perform the lexical search and filtering we needed. Adding Elasticsearch would mean another deployed service and a second representation of the inventory, with an indexing pipeline responsible for propagating edits, closures, and deletions.
Elasticsearch 是一个显而易见的考虑选项。它能为招聘搜索提供一个专用引擎，而无需我们在应用数据库内拼凑检索功能。但我们很早就否决了它，因为对于当前的工作负载，引入这一额外系统缺乏足够的理由。PostgreSQL 已经存储了职位信息和决定资格的字段，它完全能够执行我们所需的词法搜索和过滤。添加 Elasticsearch 意味着要多部署一个服务，并维护第二份数据副本，同时还需要一个索引管道来负责同步编辑、关闭和删除操作。
Managed search moved the operational boundary
托管搜索改变了运维边界
An external search-as-a-service platform could take engine operations off our hands. It would not remove our responsibility for deciding what to index, delivering updates, or testing whether public visibility stayed correct. It would also add another external dependency and couple retrieval to another vendor’s API and ranking controls. We wanted to choose how exact technical terms, semantic candidates, hard filters, and admission thresholds interacted. A managed product might support those choices, but we would have to express and validate them through its model of search rather than directly in our SQL.
外部的“搜索即服务”（Search-as-a-Service）平台可以帮我们分担引擎运维工作，但它无法免除我们决定索引内容、推送更新或测试公开可见性是否正确的责任。此外，它还会增加一个外部依赖，并将检索功能与另一个供应商的 API 和排序控制绑定。我们希望能够自主选择技术术语精确匹配、语义候选、硬过滤和准入阈值之间的交互方式。托管产品或许支持这些选择，但我们必须通过其搜索模型来表达和验证，而不是直接在 SQL 中操作。
Self-hosted embeddings solved a different problem
自托管嵌入模型解决了另一个问题
We also considered running an open-source embedding model, including EmbeddingGemma, on our DigitalOcean infrastructure. The application deployment had resource headroom, so using it for local inference was a reasonable proposal. But spare capacity was not a serving plan.
我们还考虑过在 DigitalOcean 基础设施上运行开源嵌入模型（包括 EmbeddingGemma）。应用部署环境有资源冗余，因此将其用于本地推理是一个合理的提议。但“闲置容量”并不等同于“服务方案”。