pgvector and Hybrid Retrieval

1) The problem before syntax

A hybrid retrieval system can look impressive while its evidence is weak. A vector query may return a semantically plausible document, an approximate index may be fast, and a lexical channel may recover exact terminology—but none of those observations alone proves relevance. This lesson therefore treats retrieval as an explicit contract. The deliverable is Hybrid Retrieval Evaluation Record v1: a row-level artifact that keeps the labelled query, embedding version, distance metric, exact vector rank, approximate rank, lexical rank, fused rank, recall@k, latency observation, decision, and residual risk separate. The goal is bounded and falsifiable: build one local PostgreSQL retrieval pipeline, compare exact and approximate vector behavior, retain the lexical baseline from DAT-008, fuse ranks explicitly, and state what the evidence does not prove.

2) Route orientation and lesson boundary

DAT-008 is the hard prerequisite because its PostgreSQL full-text search evidence becomes the lexical channel here. This lesson owns pgvector retrieval and the hybrid evaluation boundary. It does not own embedding-model training, embedding API calls, universal relevance evaluation, or the JSON Schema contract work that follows in DAT-012. Foundation learners get an explicit evidence table and one guided failure probe. Repair learners isolate the broken assumption before rebuilding the pipeline. Experienced learners may compress syntax, but they still must preserve the exact baseline, negative evidence, evaluation labels, and independent transfer. No route may replace observed evidence with intuition.

3) Mental model: preserve independent channels before fusion

Use the following chain:

labelled query → frozen query vector + embedding_version → exact vector baseline → ANN candidate path → lexical FTS baseline → explicit rank fusion → recall/rank/latency comparison → filter/version probes → bounded decision

The exact vector baseline is the comparison anchor for ANN recall. The lexical channel is independently inspectable and should not disappear after fusion. Fusion is a decision layer, not proof that either input is correct. The labelled fixture supplies the relevance judgment used for this lesson; PostgreSQL and pgvector supply database/search observations. Those are different authorities. The evidence record must keep them separate so that a fast ANN result, a low vector distance, or agreement between lexical and vector ranks cannot silently become “semantic truth.”

4) Foundation: exact search, ANN, metric, and provenance

Current pgvector documentation states that nearest-neighbor search is exact by default. Approximate indexes are optional and trade some recall for speed. The supported approximate index families used here are HNSW and IVFFlat. This creates the first non-negotiable evaluation rule: record an exact top-k result before judging approximate recall. Without that baseline, “ANN quality” is merely an impression.

The distance metric is also contractual. pgvector exposes different operators and index operator classes for L2, inner product, and cosine distance. A query using cosine distance belongs with the cosine operator class; an L2 comparison is a different contract. A metric can be technically valid while still being inappropriate for a particular embedding model or task. The lab therefore leaves the metric decision open and requires the learner to record the chosen operator, operator class, and embedding version together.

Embedding provenance is upstream evidence. pgvector stores and searches vectors; it does not establish that two vectors were produced by the same model/version or that labelled relevance is trustworthy. For this reason the baseline fixture declares a frozen embedding_version. Mixing versions is a deliberate failure probe, not a normal baseline condition.

5) Deep explanation: HNSW, IVFFlat, filtering, and measurable trade-offs

HNSW builds a multilayer graph. Current pgvector guidance describes it as having better query performance in the speed/recall trade-off than IVFFlat, while typically requiring more memory and slower builds. IVFFlat partitions vectors into lists, requires data to be present for meaningful training, and exposes lists and probes as important controls. These descriptions are starting points, not universal winners. The learner must evaluate the bounded fixture rather than turn vendor documentation into a blanket architecture rule.

Approximate search also interacts with filtering. With an ANN index, a category predicate can be applied after a candidate scan, so the number of qualifying results may be lower than expected. Current pgvector documents iterative index scans as a mitigation that can scan more candidates within configured limits. This is why the evidence artifact has a filter-boundary row: the learner compares a restrictive category query with the exact/filter-aware baseline and records whether candidate loss occurred. Missing ANN candidates under a filter are not automatically corruption; they are evidence to diagnose.

Latency observations are deliberately bounded. A tiny disposable fixture is useful for learning behavior and proving that the evidence pipeline exists, but it cannot establish production-scale performance. Record timing if observed, but state the environment and never convert one local timing into a capacity claim.

6) Worked example: keep vector, lexical, and fused ranks visible

Suppose a support query asks about “connection pool timeout.” First load the labelled query row and its frozen query vector. Record the declared embedding version and chosen distance metric. Run exact vector top-k and preserve document IDs and distances. Then run an ANN path using either HNSW or IVFFlat and compute observed recall@k against the exact vector result. Separately, run the PostgreSQL FTS baseline inherited from DAT-008 and preserve lexical rank positions.

Only after both channels are visible should fusion be applied. Reciprocal Rank Fusion is one documented combination option, but the lesson does not declare it universally optimal. If RRF is chosen, record the exact formula parameter and fuse ranks, not incompatible raw lexical and vector scores. Then compare the fused list against labelled relevance. A fused rank can be useful even when neither raw score scale is directly comparable, but the final relevance judgment still comes from the evaluation fixture, not from the formula itself.

The evidence row should make contradictions visible. If ANN misses a document found by the exact baseline, preserve the miss and calculate recall@k. If lexical search finds a token-exact document that vector retrieval ranks poorly, preserve that difference. If fusion improves a labelled query but harms another, record both outcomes. The objective is an inspectable decision, not a polished list with inconvenient evidence removed.

7) Required failure modes and negative evidence

ANN recall failure: change hnsw.ef_search or ivfflat.probes within the allowed local fixture and compare ANN top-k with the exact top-k. Predict the direction of the trade-off before execution. Record what actually happened rather than assuming the documented tendency must appear on every tiny dataset.

Restrictive filter: apply a category filter to an approximate query and compare returned candidates with the exact filtered baseline. If candidate loss appears, test an iterative-scan setting or make a bounded decision. Do not claim that iterative scans guarantee complete results under every limit.

Embedding-version break: mutate one controlled case so vectors with two declared embedding_version values are compared inside one evaluation set. Reject the comparison as a clean metric contract unless the provenance issue is resolved. A numerical distance remains computable; comparability is what has been broken.

Naive raw-score addition: attempt to combine a lexical score and a vector distance/similarity by raw addition. The units and directions are not automatically compatible. Use this failure to justify an explicit rank-fusion or reranking strategy rather than hiding normalization assumptions.

8) Experienced bridge: compress syntax, not evaluation

Experienced practitioners may already know CREATE EXTENSION vector, vector operators, HNSW, IVFFlat, and PostgreSQL FTS. They can move quickly through setup. They cannot skip four obligations: exact baseline, ANN-vs-exact comparison, visible lexical evidence before fusion, and a provenance/residual-risk statement. An experienced answer that says “HNSW is faster, so use HNSW” is weaker than a beginner answer that produces a bounded recall/latency comparison and explains what the fixture cannot prove.

The same applies to fusion. Knowing RRF or cross-encoders does not close the decision. State why the selected combination method fits the evidence available here, identify a plausible alternative, and preserve the fact that relevance labels are external to pgvector.

9) Alternatives and trade-offs remain explicit

Exact vector search is a legitimate baseline and may remain the production choice for small filtered sets. HNSW and IVFFlat are alternatives with different build, memory, and query characteristics. Lexical-only retrieval may be sufficient for token-exact domains; vector-only retrieval may be useful where semantic similarity dominates; hybrid retrieval is justified only when the bounded objective benefits from both channels and the added complexity is evaluated.

RRF is not the only fusion method. A cross-encoder or another reranking layer may be appropriate in a different system, but that expands runtime and model evidence requirements beyond this local lab. This lesson names that boundary instead of simulating external model evidence. Likewise, ANN parameter tuning here is instructional; production sizing and performance engineering remain separate work.

10) Guided and independent practice

For the guided pass, state a retrieval objective before writing SQL. Example: “For this frozen support corpus and labelled query set, compare exact vector, one ANN candidate path, and the DAT-008 lexical baseline; use explicit fusion only after preserving channel ranks, and report recall@k plus bounded latency observations.” Add a non-goal such as “This fixture does not prove universal search quality.”

The starter intentionally leaves open the distance metric, ANN family, ANN parameter, fusion parameter/decision, and final acceptance decision. Predict one negative probe before executing it. Complete the Evidence Record only with observed database output or clearly marked evaluation labels. Do not invent timings because psql is unavailable, and do not infer an ANN result from the fixture vectors without execution.

For independent transfer, a new product area has stricter category filters and a newer frozen embedding version. Decide which indexes or vector sets must be separated or rebuilt, which historical exact/ANN comparisons are no longer comparable, whether the lexical baseline still transfers, and what evaluation rows must be rerun. The transfer must reuse the method, not copy the original conclusion.

11) Verification and Hybrid Retrieval Evaluation Record v1

Each query row must retain at least: Query, Relevance Labels, Embedding Version, Distance Metric, Exact Vector Rank, ANN Rank, Lexical Rank, Fused Rank, Recall@k, Latency Observation, Decision, and Residual Risk. Add a clear observation_vs_judgment_note. Database observations include returned IDs, distances, rank positions, and observed timings/plans. Relevance labels are fixture authority. Decisions and residual risk are learner judgment.

A strong package contains positive baseline evidence, at least one deliberate failure, a repair or bounded restriction, changed-context transfer, and one limitation. If a real PostgreSQL 18 + pgvector runtime was not executed, runtime status remains NOT_RUN; no exact neighbors, ANN neighbors, recall values, lexical ranks, fused ranks, or timings may be fabricated. Static validation proves only that the candidate structure and safety/evidence contracts are present. The executable lab additionally binds the metric name to its distance operator and pgvector operator class, checks PostgreSQL/pgvector versions, runs only learner-completed submissions inside a generated schema on a disposable loopback database, and requires cleanup plus a hashed runtime receipt before runtime evidence becomes claimable.

12) Assessment and transfer

A passing learner can explain why exact search is the ANN evaluation baseline; compare HNSW and IVFFlat without universalizing either; keep vector metric and index operator class consistent; preserve lexical and vector ranks before fusion; calculate observed recall@k from real top-k sets; diagnose filter-related candidate loss; detect an embedding-version provenance break; reject naive score addition; and defend a bounded hybrid decision with residual uncertainty.

The blocking transfer task changes category constraints and embedding version. The learner must identify what evidence becomes incomparable and what must be rerun. The answer fails if it treats a nearest vector as relevance truth, assumes ANN preserves perfect recall, declares a fusion rule universally optimal, or attributes embedding-model governance to pgvector.

13) Sources and freshness

The source boundary is intentionally small: PGVECTOR-README and PG18-FTS. The frozen retrieval date is 2026-09-02; the earliest review due date is 2026-10-17. On 2026-09-15, the same official locators were rechecked. pgvector still documents exact search as default, HNSW and IVFFlat as approximate index types, filtering/iterative-scan behavior, and hybrid use with PostgreSQL full-text search plus RRF or cross-encoder options. PostgreSQL 18 full-text-search documentation remains the lexical baseline authority. No source was added and no claim boundary was expanded. If these versioned behaviors change or the review date arrives, the affected claims must be revalidated before reuse.