How I built recall-first search on 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.
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. Before choosing that design, we considered Elasticsearch , 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.

I worked on this search system as part of the engineering team at Hyr.works , where Hyr's careers search is used to help candidates find open roles. The implementation described here is the result of that work.

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:

// Simplified from the previous search path.
const text = searchableFields.flat().filter(Boolean).join(" ").toLowerCase();
const matches = keywords.every((word) => text.includes(word));

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.

Our investigation started around skipped end-to-end tests, but we did not treat a skipped assertion as proof of a production defect. Manual reproduction confirmed careers-search problems; another reported hydration issue did not reproduce. That distinction mattered: fixing search did not require accepting every diagnosis attached to the original test audit.

The new requirement was broader than making those assertions pass. We needed lexical precision for named technologies and semantic recall for descriptions of work, while keeping company boundaries and explicit filters exact.

The application already used Next.js and Supabase/PostgreSQL. Jobs had multiple writers, including another backend and direct database operations. Any replacement needed to see those changes without making every writer responsible for updating search.

The intended change was larger than adding more fields to the string comparison:

That describes the capability we wanted, not yet the system we should buy or build.

Elasticsearch

The problem wasn't that Elasticsearch couldn't solve search.

It was that we'd have to introduce another service, another deployed system, another copy of the searchable inventory, and another indexing pipeline to keep that copy synchronized with PostgreSQL.

There was also a simpler practical question: introducing another service meant another piece of infrastructure to approve, deploy, monitor, and pay for. I didn't want to ask for that complexity and another recurring bill unless we had a strong reason to need it.

Our jobs already lived in PostgreSQL, and PostgreSQL could handle the filtering and lexical retrieval we needed.

Managed search

A search-as-a-service product reduced some of the operational work, but it didn't remove the integration problem.
We would still need to decide what to index, keep the external index synchronized, and make sure public visibility matched the source database. We'd also be giving another system part of the search policy itself.
For this release, we wanted to control how lexical matches, semantic candidates, hard filters, and relevance thresholds interacted.

So we didn't add another vendor either.

Running our own embedding model

Self-hosting EmbeddingGemma on our DigitalOcean infrastructure looked more attractive because it avoided another external API.
But spare CPU and memory aren't the same thing as having an inference service.We would own model deployment, resource allocation, concurrency, availability, and another runtime alongside the web application. More importantly, changing the embedding model isn't just changing an API call. Query vectors and indexed job vectors need to exist in a compatible embedding space.
That meant a new model would require a new compatible index generation.
We didn't have workload evidence that taking on that operational cost would materially improve this release.

Using what we already had

The application already used GPT/OpenAI models for several AI and agentic workflows. We already had the provider integration and the operational patterns around it. Using an embedding model through that existing integration gave us semantic retrieval without adding another inference runtime just for search. We chose text-embedding-3-small specifically for embeddings. GPT-generated text was not involved in ranking jobs.

The result was a deliberately small architecture:

We weren't trying to build another Elasticsearch. We were building the smallest search system that fit our workload.

That decision kept the number of systems down, but it also meant the search policy, indexing lifecycle, failure handling, and rollout were ours to solve.
Lexical retrieval reads the projection; hybrid retrieval also compares compatible vectors. The provider returns embeddings, while PostgreSQL decides which jobs are eligible and how candidates rank. These were architectural choices, not evidence that this stack would outperform every dedicated search product. We still had to measure the system we chose.

Give each retrieval mechanism a narrower job

Our first retrieval decision was to avoid asking one mechanism to handle incompatible notions of a match. Named technologies need lexical precision; descriptions of work need more flexibility. We combined weighted PostgreSQL full-text search, bounded typo recovery, and vector similarity.

Titles and primary skills receive the strongest lexical weight. Legacy skills, category, and industry follow; descriptions and location text receive less. The projection normalizes arrays, JSON-encoded skill lists, and legacy strings instead of assuming one clean source format.

Technical tokens need special treatment before ordinary normalization. We preserve distinct representations for C++, C#, and .NET, including versioned forms such as C++17 and .NET8. Reviewed aliases handle terms such as k8s and Kubernetes. Short or ambiguous tokens are not silently spell-corrected.

Typo recovery searches a tenant-local title-and-skill vocabulary, not whole descriptions. It inspects only the first eight query tokens. For inferred corrections, it requires separation between the best candidate and its runner-up; reviewed spelling corrections use an explicit map. Remaining query text stays intact. This bounds work and avoids turning a long natural-language sentence into a sequence of speculative rewrites.

Embeddings address the remaining vocabulary gap. They let us retrieve candidates whose descriptions concern the requested work even when their titles and skills do not contain the same words. That broader retrieval still operates inside a strict eligibility boundary.

Within PostgreSQL, every retrieval channel starts from the same eligible inventory. Tenant, publication status, employment type, and work mode are constraints applied before candidate limits. Default browsing shows open jobs; closed jobs require an explicit selection. Drafts and unrecognized statuses stay outside public search.

We did not convert arbitrary natural-language phrases into hard filters. A request for "remote work in Bangalore" leaves unanswered whether the location describes the office or the candidate's permitted residence. The explicit UI filters remain authoritative.

We started with exact vector search over the eligible tenant inventory. Approximate nearest-neighbor indexing was an option to measure later, not a prerequisite for using vectors. Chunk matches collapse to one candidate per job using the strongest qualifying chunk, so a long description cannot occupy several result positions.

A good rank is not proof of relevance

Each channel contributes at most 100 candidates. We combine lexical and semantic ranks using reciprocal rank fusion:

score(job) = 1 / (60 + lexical_rank)
           + 1 / (60 + semantic_rank)

A missing channel contributes zero. Typo recovery feeds the lexical channel rather than casting another independent vote. Strong phrase matches in title/primary-skill text receive priority, and posting date breaks ties rather than allowing a newer weak match to outrank a stronger one merely through freshness.

RRF avoids trying to compare a full-text rank directly with a cosine similarity. It does not determine whether either candidate list deserves to exist. The nearest vector to an unrelated query is still the nearest vector.

We therefore apply a similarity threshold before fusion and preserve a reviewed set of explicit technical-token constraints on semantic candidates. Those safeguards improve control; they do not establish universal relevance.

The dimensionality experiment made that limitation concrete. We expanded an initially small synthetic fixture into 337 jobs and 214 queries, using sanitized public-job snapshots from two employers alongside synthetic regressions. Source IDs were replaced and contact details removed. The descriptions produced 835 chunks. We evaluated both dimensions with real provider embeddings and actual SQL retrieval in disposable local databases.

The smaller representation lost more ranking quality than our planned 0.01 NDCG tolerance, making 1,536 dimensions the stronger candidate. Its measured embedding relation, including storage and index overhead, occupied about 7.18 MB versus 2.58 MB in the fixture. That was a modest absolute increase for this dataset, not evidence that larger dimensions are free. We did not establish their CPU or memory cost under representative production concurrency.

More importantly, neither configuration met our original 90% semantic precision target. Three of four unrelated held-out queries also returned results. We initially withheld semantic activation rather than interpreting the stronger ranking score as a pass.

The measurements left us with a product question the choice of engine could not answer: was excluding a relevant vacancy worse than showing extra weak matches?

Recall-first was a product decision, not a passing precision score

For this careers experience, the answer became explicit: relevant jobs should remain discoverable even if the result set also contains loosely related or unrelated jobs. We selected 1,536 dimensions and adopted recall-first calibration. Precision stayed visible, but it was no longer the release blocker it had originally been.

On our labelled set, the original 90% target had failed because semantic retrieval admitted too many weak candidates, not because it consistently buried the desired job. RRF could rank a desired role first while leaving unrelated commercial or technical roles below it. Increasing dimensions improved ranking but barely moved precision. The calibration experiments did not establish a threshold that met the original target.

Recall-first changed what we optimized and how we checked it. We measured whether labelled relevant jobs appeared across the admitted result pages, then examined their ranking. Recall@10 alone could not establish completeness for queries with more than ten relevant jobs.

It did not remove the admission threshold or authorize unrestricted similarity results. Tenant and publication eligibility, selected hard filters, explicit technical-token constraints, and the 100-candidate channel limits stayed in place. A job from another company was still incorrect, however similar its description. A closed job still could not enter the default open-jobs view. Exact and typo-query regressions still mattered.

Within that eligible inventory, we knowingly accepted more relevance noise. The distinction was between broadening discovery and weakening the conditions under which a job could appear at all.

Under the revised criterion, the 1,536-dimensional evaluation included every labelled relevant job across all returned pages. That statement has limits. The labels had not received independent human review, and the previously inspected holdout was now a regression set rather than a fresh blind test.

Candidate caps also remained real: 119 of 204 evaluated held-out queries reported channel truncation. No labelled job was missing in this snapshot, but arbitrary future queries can exceed the bounded candidate set. The UI discloses truncation rather than implying that a paginated list necessarily contains every possible match.

We changed the acceptance criterion, not the meaning of the earlier measurements.

Semantics without embedding every search

Broader semantic discovery did not justify invoking a model on every interaction. We had designed separate paths for browsing, typing, and searches that warranted semantic retrieval.

We rejected a pipeline that embedded every keystroke. We also rejected the simpler rule "use vectors only when lexical search returns nothing." Incidental words in descriptions can produce lexical hits without answering the query.

Instead, the UI and server divide the work:

Both timers start from the latest input change; the pauses are not cumulative. The lexical-only shortcut is intentionally conservative: a short query must contain recognized concepts and have a strong phrase match in title/primary-skill text. The server makes the decision with deterministic rules, not an LLM query parser. A client request phase does not authorize arbitrary provider work.

The query-vector cache holds at most 1,000 entries for 30 minutes per process. Its key includes semantic text and the embedding specification. Filter and page changes reuse the vector. Concurrent requests for the same vector share one provider call, and one cancelled caller does not cancel another caller's shared work.

We did not cache result lists. Avoiding stale job visibility was more useful than adding another invalidation problem.

This design does not make novel semantic queries free. In the evaluation workload, selective routing avoided only 8.33% of cold query embeddings while matching always-hybrid NDCG. That result did not support an extravagant efficiency claim. Cache reuse and real query mix determine the eventual savings.

Progressive results also introduced races. An old preview could arrive after settled results, or after the user had moved to page two. Aborting a fetch was insufficient as a correctness mechanism.

The UI associates responses with the current query/filter revision and paging state, rejecting obsolete successes and errors. Existing cards and keyboard focus remain while richer retrieval runs.

Provider failure is an explicit lexical_fallback mode. Database failure is a service error, not an empty result. Those distinctions are visible to the UI because they describe different things the user can reasonably do next.

The asynchronous index needs a commit protocol

Fast query handling would not help if a job edit left the vector index describing the wrong vacancy. A new search endpoint also could not maintain its index by intercepting only its own application's writes. Other writers would bypass it.

We put projection updates in a database trigger. Each source-job transaction refreshes the private search document and normalized filters. Changes to title, skills, or description change a content hash and make embedding work pending. Unrelated metadata does not require another embedding call.

An authenticated worker in Next.js claims small batches using database leases and FOR UPDATE SKIP LOCKED. The claim transaction finishes before any provider request. A host timer invokes the worker roughly a minute after the previous run ends; it claims at most four jobs per invocation.

The critical operation is completion. In simplified form:

accept embeddings only if:
    the source document still exists and is eligible
    AND current_content_hash = claimed_content_hash
    AND current_lease_token = claimed_lease_token
    AND the lease has not expired
    AND the embedding specification is compatible

Without that check, a slow worker could overwrite an edited job with vectors for its previous description. Hashes identify the content; lease tokens identify the worker's current right to finish. Both are necessary when work can be retried.

Semantic retrieval independently checks the content hash and specification. Stale vectors cannot participate while replacement work is pending. Lexical search remains available throughout. Status changes take effect without waiting for re-embedding, and deleting a job removes its derived artifacts.

The specification includes model, dimension count, and preprocessing version. Switching dimensions is consequently an index migration, not just a provider option. Our approved upgrade operated on an empty careers vector index and refused populated embeddings or active leases. A populated incompatible generation would require a separately built replacement.

Long descriptions are split into token-bounded chunks with a title/skills prefix, an 800-token maximum complete input, and up to 80 tokens of overlap. We preserve the full source text rather than quietly dropping its tail.

Preparation had its own performance surprise. A JavaScript tokenizer initially looked sufficient, but its Unicode-heavy test exceeded a 15-second deadline. Replacing it with the server-side WASM tokenizer brought that text-test group to roughly 0.3 seconds of execution. Chunk preparation deserved measurement just as much as vector retrieval did.

The evaluation corpus is separate from this lifecycle. Adding a new production job does not require editing a JSON fixture; the trigger and worker discover it automatically. Conversely, expanding the fixture improves our evidence, not the production model's knowledge.

The latency problem was not only the embedding call

Once the retrieval path worked, latency still needed investigation. One avoidable cost was in our own eligibility lookup.

To accommodate source representations, the first implementation extracted identifiers and status by converting complete rows to JSON. Conceptually:

-- Before: serialize the source row to read a few fields.
to_jsonb(source)->>'job_id'
to_jsonb(source)->>'company_id'
to_jsonb(source)->>'job_status'

-- After: read only the required columns.
source.job_id::text
source.company_id::text
source.job_status::text

Read-only production comparisons returned identical job IDs while measuring approximately 102-106 ms for the original eligibility lookup and 8-11 ms for direct-column access. We also replaced a JSON-extracted company-username predicate with a direct-column predicate.

These were component timings, not end-to-end search results. They nevertheless identified useful work: the application performs lexical retrieval before deciding whether to perform hybrid retrieval, so unnecessary eligibility cost affects both stages. We could remove it without changing the vector model, candidate limits, or relevance behavior.

The fix shipped as a guarded SQL upgrade that changed only the expected expressions and preserved populated embeddings, indexing state, ownership, and grants. An unexpected function definition caused the upgrade to stop rather than rewriting unknown SQL.

Provider variability was a separate problem. The initial 550 ms query deadline produced timeouts in both local and deployment-region experiments. We increased it to 1,000 ms to give novel queries more time to obtain an embedding; query calls still have no SDK retries, while indexing uses a separate ten-second deadline.

That decision trades a longer wait on some novel queries for a better chance of obtaining semantic results. It does not make a 700 ms API target true. SDK deadlines are also not exact end-to-end wall-clock ceilings: database work and request handling sit outside them.

The public endpoint changed our trust boundary

An anonymous search that can invoke a paid provider needs more protection than an in-process concurrency counter. We added a generous per-IP token bucket before body parsing, database access, and embedding calls: 240 immediate requests, replenishing at 20 per second.

The bucket itself was small. Establishing whose IP it counted was the harder part.

Production traffic passed through Cloudflare and Caddy before reaching Next.js. Counting the immediate proxy address would group unrelated visitors. Accepting an arbitrary forwarding header would let callers choose their own buckets.

Caddy now overwrites a dedicated search identity header. Only connections from the trusted CDN ranges may supply the CDN's visitor address; other connections use their actual remote address. The production application port is bound to loopback so public callers cannot bypass that sanitization by reaching Node directly.

Review caught a subtler mismatch: the first proxy matcher covered valid tenant names, but the dynamic application route could receive malformed tenant paths. Because rate limiting ran before tenant validation, those paths could reach the limiter without having their identity header sanitized. The proxy matcher needed to cover the route family's malformed inputs too.

We tested that behavior with a disposable loopback backend, including spoofed headers, simulated trusted proxies, encoded and overlong tenants, and unrelated routes. We did not flood the production database to prove a 429.

The limiter remains deliberately modest. It is per process, resets on restart, and does not protect direct anonymous Supabase RPC calls. Those calls require their own database validation, bounded work, permissions, and effective caller statement deadlines. More replicas or a different traffic pattern would change the requirements.

When a browser does receive a 429, it retains the previous cards, identifies them as previous results, respects a cooldown, and retries the latest query only on request. Rate limiting must not turn a visible result page into a misleading "no jobs found."

What our validation established

We used different tests for different claims. Provider mocks exercised timeout and cancellation control flow without making normal CI depend on OpenAI. Disposable PostgreSQL tests executed real retrieval, permission, lease, stale-completion, and migration behavior. The dimensionality experiment used actual embeddings. None substituted for the others.

The final protection changes passed 105 focused tests and TypeScript checking. The enabled-search Playwright suite covered 17 cases, including stale preview successes and failures, pagination races, normalized filters, explicit fallback, rate-limit recovery, and keyboard behavior at mobile width. The standard E2E command now runs both the existing flag-off suite and the enabled-search suite.

Deployment had its own evidence gap. At one checkpoint, the migration had succeeded and the application contained the worker route, but the database had zero embeddings and the host had no indexing timer. Deploying code had not scheduled work. We configured the worker separately, completed the backfill, and verified current embeddings for all 724 eligible jobs before activation.

Rollout proceeded through lexical search, proxy protection, rate limiting, and semantic activation. Production checks confirmed tenant identity and status filters, and a natural-language retail query displayed relevant roles in the browser with focus preserved.

The final smoke test also showed the limitation we had designed around. Three initial embedding requests fell back to lexical search. Later hybrid calls succeeded: cached public requests measured 224-309 ms, one fresh finance query took 685 ms, and other successful uncached responses were around 1.2 seconds.

The samples were too small to establish p95 latency or a steady-state fallback rate. We did not certify performance at twice observed peak traffic.

Semantic search was enabled with that limitation explicit. Returning HTTP 200 after a fallback was evidence that the failure path worked, not that semantic latency had passed.

Search correctness has more than one boundary

The original vocabulary problem was real: literal containment could not reliably connect a candidate's wording to an employer's description. Hybrid retrieval widened that connection. Its usefulness depended on the boundaries around it.

Eligibility belongs in SQL before ranking. Ranking quality does not establish candidate relevance. An embedding belongs to a particular content version, not merely a job ID. A late response does not own the current browser state. A forwarded address is not a caller identity until a trusted proxy establishes it.

Those distinctions gave us concrete ways to test the system and explain its failures. They also clarified the architecture decision. PostgreSQL and our existing provider integration gave us enough primitives for this workload without a separate search service or inference runtime. In return, we owned the behavior connecting those primitives: eligibility, index freshness, query routing, and failure handling.

A dedicated engine or managed platform could change where that work lives. It would not decide which weak matches the product should tolerate, make incompatible vectors comparable, or determine whether an old response still belongs on the screen.

The most consequential decision was accepting that relevance itself needed a product definition. Our high ranking score had not answered whether extra results were acceptable. Once that choice was explicit, we could optimize for finding the labelled relevant jobs without pretending that a recall-first system had passed the precision target it replaced.