All chapters
  1. Start hereRetrieval is a query. The model reads only what it returns
  2. Foundations · 1What retrieval means: lookup, keyword search, semantic search
  3. Foundations · 2Traditional SQL first: WHERE, indexes, full-text search and BM25
  4. Foundations · 3Vectors without AI: coordinates, distance and nearest neighbors
  5. Foundations · 4Embeddings: how a model turns text into a vector
  6. Foundations · 5Choosing an embedding model: quality, cost, privacy and fit
  7. Search · 6Schema and storage: where the vectors live
  8. Search · 7Exact vector search: compare the query with every row
  9. Search · 8Approximate nearest neighbors: HNSW, IVF and the recall trade
  10. Search · 9PostgreSQL and pgvector: vectors inside a relational database
  11. Search · 10SQLite vector search: one file on one machine
  12. Pipeline · 11Chunking and ingestion: from a document to retrievable units
  13. Pipeline · 12Retrieval pipelines: from a question to the context the model reads
  14. Pipeline · 13Hybrid search: words and vectors together
  15. Pipeline · 14Reranking: a second, more careful look at the candidates
  16. Pipeline · 15Top-k and scale: ten thousand, a hundred thousand, a million rows
  17. Pipeline · 16Two corpora: similar applicants and underwriter precedents
  18. Pipeline · 17Grounded generation: citations, abstention and conflicts
  19. Pipeline · 18Evaluating retrieval: recall@k, MRR, nDCG and faithfulness
  20. Practice · 19Google Cloud choices: Cloud SQL, AlloyDB, Vector Search, Agent Retrieval, RAG Engine, Agent Search
  21. Practice · 20Other ecosystems: libraries, engines and managed services
  22. Practice · 21Operations: migrations, deletes, permissions and freshness
  23. Practice · 22Advanced retrieval: rewriting, hierarchies, late interaction, graphs, agents
  24. Practice · 23Reference architectures: local-first, dual-corpus, managed, Postgres-native
  25. Practice · 24Framework recipes and the decision guide
  26. ReferenceGlossary
  27. AboutAbout this site and its sources
Search · 7

Exact vector search: compare the query with every row

The query vector is compared with each candidate row, the rows are sorted by distance and the first k are returned. SQL distance operators, bound parameters, metadata filters, joins and the query plan.

Every row compared
Exact search computes one distance per candidate row, sorts, and keeps the nearest k: ORDER BY embedding <=> $1 LIMIT k. The answer is the true top k.
Bound, never pasted
The query vector, the filter values and k travel as bound parameters. SQL built by pasting text into a string is unsafe, and this page shows it only labelled UNSAFE.
The answer key
A filter belongs inside the query, before the LIMIT. And the exact result is the ground truth that every approximate index is measured against.

The underwriting agent is working on SUB-1004. It has found similar applicants and now wants precedents: decided cases that resemble this one, so that the underwriter can see how similar risks were handled. The insurer keeps its books of business apart, and SUB-1004 belongs to the book called book-east. The first version of the precedent query ranks every active precedent by distance and keeps five.

The five rows come back fast and look relevant. Two of them, PRE-003 and PRE-021, belong to book-west, another book whose decisions this underwriter should never see. Nothing failed. The query did exactly what it said.

A developer notices and adds a line of application code after the query: drop every row whose tenant is not book-east. Now no foreign row leaks, but in the Python version of the example only two of five rows survive, and the agent reasons from two precedents where it asked for five. The first version leaked; the second starved. Both came from a query that "worked".

This is this site's own case UW-R07, and it is a question about where things sit inside one SQL statement: the filter, the parameters, the ranking and the limit. This page writes that statement correctly, runs it, reads its plan, and then shows why its answer, the exact one, becomes the answer key for everything that follows.

What this is not. This page does not cover indexes that skip rows to answer faster; those are on approximate nearest neighbors. The order of stages in a whole retrieval pipeline (filter, candidates, fusion, rerank) is on pipelines. A tenant leak as a guardrail problem, with defense in depth, belongs to guardrails.sammuthu.com.

Exact search in plain words#

Every page of this site keeps one sentence true. An embedding column holds one vector per row; a vector query compares the query vector with the rows' vectors, ranks the rows by distance and returns the nearest k; the model reads only what retrieval returned. Retrieval is a query. The model reads only what it returns.

Exact search is that sentence carried out literally. Every candidate row is compared with the query, so the k rows returned are the true k nearest. "Brute force" is another name for it. The vectors page did it by hand on five rows: five distances, one sort, the first two rows.

Before AI. A backend engineer has seen this plan many times. SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 on a table with no index on created_at makes the database read every row and sort. It is slow on a large table and always correct. Exact vector search is the same plan with a computed sort key: a distance instead of a timestamp.

The cost: one distance per candidate row, each proportional to the dimension (16 for the example's toy vectors, 384 for the model the insurance build uses). Double the rows and the work doubles. Nothing is built in advance, and nothing can be missed.

The smallest query: distance, order, limit#

The SQL has three parts, and each one has a job.

The first part is a distance operator: a symbol between two vectors that returns a number, smaller meaning closer. pgvector, the PostgreSQL extension the example uses, writes cosine distance as <=>. embedding <=> $1 therefore means "the cosine distance between this row's vector and the query vector". The pgvector page lists the other operators.

The second part is ORDER BY on that distance, which sorts the rows nearest first. The third is LIMIT k, which keeps the first k. Together, ORDER BY embedding <=> $1 LIMIT k is the whole of exact top-k.

Here it is on the example's guidance passages for this site's case UW-R01, the query "roof replaced recently but aerial imagery shows an older roof". This is Q1 in examples/rag-uw/sql/queries.sql. A successful run proves that one SQL statement compares a query vector with every row and returns the nearest five; it does not prove anything about a real embedding model, because the vectors come from the toy encoder toy-a@1 (16 dimensions, a deterministic stand-in for a model, not a model).

-- Q1 from examples/rag-uw/sql/queries.sql (ran on the throwaway database, 2026-11-18)
SELECT embedding AS q_r01 FROM query_vectors WHERE name = 'UW-R01' \gset
PREPARE guidance_exact(vector, int) AS
  SELECT passage_id, policy_version, status, round((embedding <=> $1)::numeric, 4) AS cosine_distance
  FROM guidance_passages
  WHERE trusted
  ORDER BY embedding <=> $1, passage_id     -- the second key makes this an exact sort (no index)
  LIMIT $2;
EXECUTE guidance_exact(:'q_r01', 5);

The output is from sql/OUTPUT.md: PostgreSQL 16.14, pgvector 0.8.3, a throwaway container, one run on one laptop, toy data; not a benchmark.

== Q1  exact cosine top-k over guidance, bound parameters (UW-R01)
PREPARE
     passage_id      | policy_version |   status   | cosine_distance 
---------------------+----------------+------------+-----------------
 GD-NOTE-01          | 2026.2         | current    |          0.2293
 GD-2025.4-R-ROOF-03 | 2025.4         | superseded |          0.3035
 GD-2026.2-R-ROOF-03 | 2026.2         | current    |          0.3035
 GD-2025.4-R-ROOF-02 | 2025.4         | superseded |          0.3792
 GD-2026.2-R-ROOF-02 | 2026.2         | current    |          0.3792

Three details matter. The roof-conflict rule R-ROOF-03 is second, found by meaning for a query that never names it. The superseded 2025.4 copy ties with the current 2026.2 copy, because their texts differ only in a number the toy encoder cannot see; distance cannot separate versions, and a filter will. And the second sort key, passage_id, returns the tied rows in the same order on every run, so a test that checks "the first row" does not flake.

Diagram: Exact search: filter exactly, compare every remaining row, sort, keep k

Read left to right. The bound query vector and filter values enter one statement. The WHERE clause keeps only rows that pass the exact filter. Every remaining row gets one distance. The sort runs over rows and LIMIT keeps the nearest k, which are the only rows the model will read. Click a box for the example's numbers.

  1. Bound inputs
    1. Bound parameters: Values travel separately from the SQL text; none of them becomes SQL.
  2. Inside one statement
    1. Exact filter: Rows that fail an exact condition never enter the ranking.
    2. One distance per row: Every remaining row is compared with the query vector: nothing is skipped.
  3. Rank and cut
    1. Sort by distance: Rows are ordered nearest first; the id breaks ties so the order is repeatable.
    2. Keep the nearest k: The true top k for this query, filter and metric: the answer key for any index.

The diagram is the plan in four steps. The exact filter removes rows first, every remaining row gets one distance, the sort runs over rows, and LIMIT keeps k. Only those k rows reach the model; a row cut at any step does not exist for it.

Bound parameters: why the vector never enters the SQL text#

The standard is simple to state: the query vector, every filter value and k must reach the database as bound parameters, never as text pasted into the SQL string.

A bound parameter is a placeholder in the SQL text, $1 in PostgreSQL or ? in SQLite, whose value travels to the database separately from the statement. In Q1, PREPARE sends the statement with $1 and $2 in it; EXECUTE guidance_exact(:'q_r01', 5) sends the two values. The PostgreSQL documentation, read on 2026-11-18, describes PREPARE as parsing, analyzing and rewriting the statement once, with parameters referenced by position as $1, $2, and planning at EXECUTE time, so that the plan can depend on the values supplied. The values never become SQL.

The mistake this standard prevents looks harmless. It builds the SQL with string formatting, so that whatever the caller passed becomes part of the statement. The example prints it as text and never runs it:

UNSAFE (never do this; shown as text, not executed):
    sql = f"SELECT ... WHERE status = '{user_status}' ORDER BY cosine_distance(embedding, '{vec}') LIMIT {k}"
    con.execute(sql)   # user text becomes SQL: injection, and no plan reuse

Three things go wrong at once. A status value such as ' OR TRUE; -- closes the quote and rewrites the filter, so a tenant or version condition stops applying; the Python sqlite3 documentation shows exactly this attack and says to always use placeholders instead of string formatting. The vector itself becomes a long literal: 384 numbers of about ten characters each is roughly 4,000 characters of SQL per query (an illustrative estimate), which the database must parse again every time. And every query text is different, so no plan can be reused.

Common confusion. "The vector comes from our own encoder, not from a user, so pasting it is safe." The vector may be safe; the filter values next to it rarely are, and a codebase that pastes one value pastes the next one too. Bind everything, and the question never has to be asked again.

Metadata filters: exact facts stay in WHERE#

Distance captures resemblance. It cannot see a policy version, a tenant or a status, and the tie in Q1 shows why. A metadata filter is an ordinary WHERE condition on the row's other columns, such as policy_version = '2026.2' or tenant = 'book-east'. It keeps or drops rows exactly, the way the SQL first page describes, and the ranking then runs only over the rows it kept.

Case UW-R04 is the Corpus A question: past applicants that look like SUB-1004, restricted to wind zone C and to the agent's own book. Q4 filters on wind_zone and tenant, ranks the survivors by distance, and returns the source columns of each row in the same statement. A successful run shows that the filter and the ranking compose in one query, and that the result already carries the facts an underwriter reads. It does not show that the profiles are good matches in any absolute sense.

-- Q4 from examples/rag-uw/sql/queries.sql (ran on the throwaway database, 2026-11-18)
PREPARE similar_profiles(vector, text, text, int) AS
  SELECT p.profile_id, p.submission_ref, p.occupancy, p.construction, p.year_built, p.area_sq_ft, p.roof_age_years,
         round((p.embedding <=> $1)::numeric, 4) AS cosine_distance
  FROM applicant_profiles AS p
  WHERE p.wind_zone = $2 AND p.tenant = $3
  ORDER BY p.embedding <=> $1, p.profile_id
  LIMIT $4;
EXECUTE similar_profiles(:'q_prof', 'C', 'book-east', 5);
 profile_id | submission_ref | occupancy  | construction | year_built | area_sq_ft | roof_age_years | cosine_distance 
------------+----------------+------------+--------------+------------+------------+----------------+-----------------
 PRF-0218   | SUB-2218       | office     | masonry      |       1994 |       4500 |             23 |          0.0018
 PRF-0206   | SUB-2206       | retail     | frame        |       1960 |      36500 |             22 |          0.0045
 PRF-0091   | SUB-2091       | office     | concrete     |       1979 |       5500 |             23 |          0.0048
 PRF-0172   | SUB-2172       | restaurant | frame        |       1999 |      11000 |             26 |          0.0066
 PRF-0197   | SUB-2197       | restaurant | frame        |       1984 |      10000 |             20 |          0.0070

Every row is in wind zone C and in book-east, because rows outside never entered the ranking. The Python version of the case reports that 82 of the 240 profiles passed the filter, so 82 vectors were compared, not 240. A retail frame building ranks second: a banded summary through a toy encoder is a coarse signal.

Joining a match back to its source row#

A vector match is only useful if it leads back to facts a person can check. When the embedding column sits in the same row as the source data, as in this schema, the join is free: the SELECT list names the columns. When vectors live in another table or another system, the match returns an id and the query joins on it; the storage page compares those layouts.

Joins also connect a match to other tables. Case UW-R05 returns precedents, and some were decided under the old policy version 2025.4. The second half of Q5 joins three precedents to the current text of the rule each one cites, so the underwriter sees the old decision next to today's rule. The output is trimmed: the long rule text is cut after its first sentence.

-- second statement of Q5 from examples/rag-uw/sql/queries.sql (ran on the throwaway database, 2026-11-18)
SELECT pr.precedent_id, pr.decision, pr.policy_version, g.passage_id AS current_rule_passage, g.body AS current_rule
FROM precedents AS pr
JOIN guidance_passages AS g ON g.rule_id = ANY (pr.rule_ids) AND g.status = 'current'
WHERE pr.precedent_id IN ('PRE-001', 'PRE-002', 'PRE-004')
ORDER BY pr.precedent_id;
 precedent_id | decision | policy_version | current_rule_passage | current_rule
--------------+----------+----------------+----------------------+-------------
 PRE-001      | approve  | 2025.4         | GD-2026.2-R-ROOF-01  | R-ROOF-01 (UW-GUIDE 2026.2, illustrative). Roof older than 25 years in wind zone C: not eligible. ...
 PRE-002      | decline  | 2026.2         | GD-2026.2-R-ROOF-03  | R-ROOF-03 (UW-GUIDE 2026.2, illustrative). Roof age on the application and from the provider differ by more than 5 years: ...
 PRE-004      | approve  | 2025.4         | GD-2026.2-R-ROOF-01  | R-ROOF-01 (UW-GUIDE 2026.2, illustrative). Roof older than 25 years in wind zone C: not eligible. ...

PRE-004 is the past approval of the series case UW-007, decided under 2025.4, when the roof limit was 30 years. Placed next to the current 25-year rule, it reads as what it is: decision support from an older policy, which current policy outranks. A precedent never replaces the rule or the human decision. The model recommends; a person decides.

Where the filter sits: before the limit, or after it#

Back to UW-R07. There are two places a tenant filter can go, and only one is correct.

A pre-filter is a condition inside the query, in the WHERE clause, applied before ranking and before LIMIT. The database ranks only the rows that pass. If at least k rows pass, k rows come back, and every one of them passes.

A post-filter runs after the ranking: the query keeps the top k of all rows, and then the application (or a later stage) drops the rows that fail the condition. It is the mistake in the opening story, and it fails in two directions. If the post-filter is forgotten or broken, foreign rows leak. If it works, it can only remove rows from a list that was already cut to k, so fewer than k remain: the query starves.

The leak is visible in Q6, which is the precedent query with the tenant condition removed:

== Q6  tenant filter inside the query vs none (UW-R07): PRE-003 belongs to book-west
PREPARE
 precedent_id |  tenant   | decision | cosine_distance 
--------------+-----------+----------+-----------------
 PRE-002      | book-east | decline  |          0.0326
 PRE-003      | book-west | escalate |          0.0834
 PRE-021      | book-west | approve  |          0.1089
 PRE-014      | book-east | approve  |          0.1262
 PRE-040      | book-east | approve  |          0.1275

The starvation is easiest to see in the example's Python, where search.exact_topk takes a postfilter flag. The script below is a short file added to a scratch copy of examples/rag-uw. It runs the same precedent query for SUB-1004 three ways. A successful run proves that the position of the filter, not its condition, decides how many rows come back and whose rows they are; it says nothing about the quality of the precedents.

# tenant_filter.py, run from a scratch copy of examples/rag-uw
from encoders import TOY_A1
from pipeline import find_similar_decisions, precedent_table
from search import exact_topk

table = precedent_table()
qv = TOY_A1.encode_query(find_similar_decisions("SUB-1004")["precedent_query"])
want = {"status": "active", "tenant": "book-east"}

for label, post in (("pre-filter (WHERE, then rank)", False), ("post-filter (rank, top 5, then filter)", True)):
    res = exact_topk(table, qv, 5, want, TOY_A1.name, postfilter=post)
    ids = [h["id"] for h in res.hits]
    print(f"{label}: {len(ids)} rows {ids}, distance computations {res.distance_computations}")

top5 = exact_topk(table, qv, 5)
print("no filter at all:", [(h["id"], table.row(h["id"])["tenant"], table.row(h["id"])["status"]) for h in top5.hits])
pre-filter (WHERE, then rank): 5 rows ['PRE-002', 'PRE-014', 'PRE-040', 'PRE-039', 'PRE-019'], distance computations 32
post-filter (rank, top 5, then filter): 2 rows ['PRE-002', 'PRE-014'], distance computations 40
no filter at all: [('PRE-002', 'book-east', 'active'), ('PRE-003', 'book-west', 'active'), ('PRE-021', 'book-west', 'active'), ('PRE-006', 'book-east', 'quarantined'), ('PRE-014', 'book-east', 'active')]

The unfiltered top 5 holds two book-west rows and one quarantined row, so the post-filter keeps two. The pre-filter compares only the 32 active book-east precedents and returns five, the same five as the first statement of Q5 on PostgreSQL. It also does less work: 32 distance computations instead of 40.

Diagram: Where the tenant filter sits decides whose rows come back and how many

Two orders for the same precedent query of case UW-R07. Top row: the filter is inside the query, before LIMIT, so only the agent's own book is ranked and five rows return. Bottom row: everything is ranked, the top five are cut, and only then is the filter applied, so foreign rows leak if the filter is missing and only two rows survive if it works. Click a box for the example's output.

  1. Correct: pre-filter
    1. Filter first: Only the 32 active book-east precedents enter the ranking.
    2. Rank, then LIMIT 5: Five rows return, all from the agent's own book.
  2. Mistake: post-filter
    1. Rank all, LIMIT 5: The top five of every book come back, two of them from book-west.
    2. Drop afterwards: A missing post-filter leaks foreign rows; a working one leaves fewer than five.

The rule the diagram carries: a filter that must hold for every returned row belongs in the WHERE of the same statement, before LIMIT. With an approximate index the database itself can end up filtering after a partial scan; that is on the ANN and pgvector pages.

In our insurance agent. The tenant condition is part of the precedent query itself, written once in the function that builds it. No caller can ask for precedents without it, and no later stage is trusted to remove another book's rows.

The lab: bind the parameters, read the plan#

The SQL lab queries 18 synthetic guidance passages with four templates: WHERE only, ORDER BY embedding <=> $1 LIMIT $2, filter plus vector, and string interpolation, which it shows as UNSAFE text with what the database would receive and never runs. Bind $1, $2 and $3 and watch the result and a plan sketch change. The plan sketch is labelled as a sketch, not EXPLAIN output. Toy encoders and synthetic data show how the mechanism works, not how good any model or product is.

The same results without JavaScript (query "roof replaced recently but aerial imagery shows an older roof", cosine distance, k = 5, in this example):

#No filterdistancepolicy_version = '2026.2' AND trusteddistance
1GD-NOTE-010.2293GD-NOTE-010.2293
2GD-2025.4-R-ROOF-03 (superseded)0.3035GD-2026.2-R-ROOF-030.3035
3GD-2026.2-R-ROOF-030.3035GD-2026.2-R-ROOF-020.3792
4GD-2025.4-R-ROOF-02 (superseded)0.3792GD-2026.2-R-ROOF-010.4007
5GD-2026.2-R-ROOF-020.3792GD-NOTE-060.4636

WHERE section = 'Roof' AND status = 'current' alone returns GD-2026.2-R-ROOF-01, -02 and -03 with no ranking. The filter removed the superseded copies; the ranking put the current roof-conflict rule second.

The five-row lab from the vectors page shows the same in two dimensions: drag the query point and the bound SQL and its result update; a WHERE toggle keeps only exact matches in the ranking.

Without JavaScript (query [0.85, 0.70], Euclidean distance, LIMIT 3; x = roof concern, y = wind exposure; in this example):

RankidRowroof_typewind_zoneVectorL2 distanceIn LIMIT 3?
11older roof, wind zone CasphaltC[0.9, 0.8]0.112yes
25roof age unknown, wind zone BtileB[0.5, 0.5]0.403yes
33older roof, wind zone AasphaltA[0.8, 0.1]0.602yes
42new roof, wind zone CmetalC[0.2, 0.9]0.680no
54new roof, wind zone AmetalA[0.1, 0.2]0.901no

With WHERE wind_zone = 'C' only rows 1 and 2 remain, ranked 1 then 2: a pre-filter, ranked after.

What the plan says: a scan and a sort#

A query plan is the database's description of how it will run a statement: which tables it reads, in what order, and which indexes or sorts it uses. EXPLAIN prints it; EXPLAIN ANALYZE also runs the statement and reports what actually happened. The example runs it with TIMING OFF on purpose, because milliseconds on a toy table say nothing.

Q10 runs an exact query on a 20,000-row toy table of 16-dimensional vectors, with index scans switched off so that the plan is the exact one. The query forces this plan; it does not show which plan the planner would choose on its own. The long vector literal is shortened here to '[...]'.

 Limit (actual rows=10 loops=1)
   ->  Sort (actual rows=10 loops=1)
         Sort Key: ((embedding <=> '[...]'::vector(16)))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on scale_points (actual rows=20000 loops=1)

Read it from the bottom. Seq Scan ... actual rows=20000 is every row read: the definition of exact. Sort Key is the distance, computed for each row. top-N heapsort means the sort kept only the best ten while it scanned instead of sorting all 20,000, which is why its memory stays small. Limit returns the ten. Whenever someone asks whether a query was exact, this shape is the evidence: a full scan (or a scan of the filtered rows) feeding a sort on the distance.

SQLite says the same in its own words: the standard-library version below prints SCAN guidance_passages and USE TEMP B-TREE FOR ORDER BY. The SQLite documentation, read on 2026-11-18, defines SCAN as a full-table scan and the temporary b-tree as the structure that sorts the output, and says the EXPLAIN QUERY PLAN format is for interactive debugging and may change between releases.

The same exact search with the standard library#

SQLite has no vector operator. Python's sqlite3 module can register a Python function under a name that SQL can call, and that is enough for exact search. examples/rag-uw/sqlite_exact.py stores each vector as a float32 BLOB, registers cosine_distance(a, b) with create_function, and binds the query vector, the filters and k as ?. The excerpt below is from that file; the run is from a scratch copy. A successful run proves that exact search needs nothing beyond a distance function and ORDER BY ... LIMIT; it does not show an extension such as sqlite-vec, which the SQLite page covers.

# excerpt from examples/rag-uw/sqlite_exact.py (connect and the guidance query)
con = sqlite3.connect(":memory:")
con.create_function("cosine_distance", 2, sql_cosine_distance, deterministic=True)

SQL_GUIDANCE = """
SELECT passage_id, policy_version, cosine_distance(embedding, ?) AS dist
FROM guidance_passages
WHERE status = ? AND embedding_model = ?
ORDER BY dist, passage_id
LIMIT ?"""
cd /tmp/rag-uw && /opt/homebrew/bin/python3 sqlite_exact.py --verbose
...
Guidance (bound parameters: query vector, status, model, k):
SELECT passage_id, policy_version, cosine_distance(embedding, ?) AS dist
FROM guidance_passages
WHERE status = ? AND embedding_model = ?
ORDER BY dist, passage_id
LIMIT ?

  query: 'roof replaced recently but aerial imagery shows an older roof'
    0.2293  GD-NOTE-01 (2026.2)
    0.3035  GD-2026.2-R-ROOF-03 (2026.2)
    0.3792  GD-2026.2-R-ROOF-02 (2026.2)

  query: 'large warehouse without sprinklers'
    0.3044  GD-2026.2-R-SPRK-01 (2026.2)
    0.3422  GD-NOTE-08 (2026.2)
    0.4874  GD-NOTE-04 (2026.2)

EXPLAIN QUERY PLAN (guidance):
    SCAN guidance_passages
    USE TEMP B-TREE FOR ORDER BY

Every row was compared: this is exact search (no index).
Nothing left the machine: embeddings are computed in process; the database is in memory.
...

The output is trimmed at the top (the Mnemos-like notes) and at the bottom (the UNSAFE text quoted earlier). The distances match PostgreSQL's Q1 to four decimals, and the superseded rows are gone because status = ? is bound to current. The query also filters on embedding_model, a guard against comparing vectors from different encoders; the embeddings page shows what happens without it.

Exact search is the answer key#

Exact search has a second job, and it may matter more than the first. Because it compares every candidate, its result is the true top k for that query, filter and metric. That makes it ground truth search: the reference result that any faster method is checked against.

An approximate index skips most rows and may miss some true neighbors. The only way to know how many it missed is to run the exact query for the same inputs and compare the two lists. The share of the exact top k that the index also returned is called index recall. pgvector's README, read on 2026-11-18, suggests exactly this check: run the query with index scans disabled inside a transaction, and compare with the indexed result. The ANN page builds indexes and measures them this way, and the pgvector page does it on the throwaway database.

Common confusion. Index recall is not the same as recall@k. Index recall compares an index with exact search: did the index find what exact search found? Recall@k compares any result with human relevance labels: did retrieval find the passages that actually answer the question? An index can reach index recall 1.0 and still return irrelevant rows, if exact search over those vectors returns irrelevant rows. The evaluation page computes recall@k by hand.

How exact search fails#

Exact search cannot miss a neighbor, so its failures are all in how the query is written. Two were covered above: a filter after the limit (UW-R07) and values pasted into the SQL. Three more are common.

No tie-breaker. Two rows at the same distance come back in either order. Add the id as a second sort key.

Similarity sorted as if it were distance. ORDER BY 1 - (embedding <=> $1) ascending returns the least similar rows first. pgvector's troubleshooting section adds a second reason to keep the plain operator: an index is only used when ORDER BY is the distance operator itself, in ascending order.

Vectors from another encoder. The arithmetic runs and the neighbors are wrong. Store embedding_model per row and filter on it, as Q8 and the SQLite query do.

Production pitfall. "Exact" says which vectors were compared, not whether the right rows were in the table: superseded passages come back when they are nearest, as the Q1 tie shows.

What the products document#

pgvector's README, as documented on 2026-11-18, says that by default pgvector performs exact nearest-neighbor search, which gives perfect recall, and that adding an approximate index trades some recall for speed. For queries without an index it suggests raising max_parallel_workers_per_gather so that PostgreSQL can split the scan across workers. For filtered queries it suggests first indexing the filter column with an ordinary index, because exact search suits conditions that match a small share of rows; Q13 on the pgvector page shows the resulting plan.

FAISS, a C++ library, names its exact indexes IndexFlatL2 and IndexFlatIP, as documented on 2026-11-18; there, filters and joins back to source rows are the application's job (ecosystems).

In the insurance build. In that build, the precedent corpus has no approximate index at all: a btree on (provision_key, status, decided_at DESC) narrows the rows, and an exact distance sort ranks them, because each provision's set of precedents is small. That build's documentation also reports a planner finding: with its selective filter, the planner used the btree and an exact sort over about 190 rows (in that build, not a benchmark).
In a coding agent. A coding agent that searches a repository mixes two kinds of retrieval. Grep and symbol lookup are exact and lexical: they find retry_provider_call when the name is known. "Find where we retry provider calls" is a vector query over code chunks, and on a single repository an exact scan over every chunk is often affordable. The same filter rule holds: a condition such as "only files in this package" or "only the current branch" belongs inside the query, before the limit, or the agent gets five chunks where it asked for ten.

A checklist for Monday#

QuestionWhy it matters
Is every value bound ($1, ?), including the vector and k?Pasted text is injection and defeats plan reuse
Does every mandatory filter (tenant, version, status) sit in the WHERE of the same statement?A filter after LIMIT leaks or starves (UW-R07)
Is there a tie-breaker after the distance in ORDER BY?Ties otherwise return in any order and tests flake
Is the sort the plain distance operator, ascending?Smaller-is-closer; and pgvector only uses an index for that form
Does the query filter on embedding_model?Same dimension does not mean same space
Does the result carry the source columns, or join to them?A match is useful only with checkable facts
Can you run the same query exactly, on demand?It is the answer key for every index you add later
Prepared statements, generic plans and connection poolsWhat binding changes inside the database, and one trap

Binding does more than stop injection. The PostgreSQL documentation, read on 2026-11-18, describes two kinds of plan for a prepared statement. A custom plan is made for each execution with the actual parameter values; a generic plan is made once and reused. With the default plan_cache_mode = auto, the server uses custom plans for the first five executions, then builds a generic plan and keeps it unless its estimated cost is much higher than the average custom plan. EXPLAIN EXECUTE name(...) shows which one ran: a generic plan prints $n symbols, a custom plan prints the values.

This matters for filtered vector queries because selectivity depends on the value. To take an illustrative case, tenant = 'book-east' may match a third of the rows and another tenant only a hundred. A generic plan chosen for one value can be a poor plan for another: a sequential scan where a btree on the filter would have been better, or the reverse. When a filtered query behaves well in tests and badly for one tenant in production, compare EXPLAIN EXECUTE for both values before changing anything else.

Prepared statements live for one database session. Behind a connection pool that hands a different server session to each transaction, a statement prepared on one connection may not exist on the next. Drivers and pools handle this in different ways; check what yours documents. Binding as a safety property does not depend on PREPARE: the application hands the value to the driver as a parameter instead of formatting it into the string, and how the driver then transmits it is something to read in that driver's documentation.

When exact search is the production design, not the prototypeSelective filters, small partitions and perfect recall

Exact search is often described as the thing to replace once the table grows. That treats the table size as the cost, and it is the wrong number. The cost is the number of rows that survive the filters, times the dimension. A million-row table where every query is restricted to one tenant, one region and one status may leave a few hundred rows to compare per query. Ranking those exactly gives perfect recall, needs no index build, no tuning parameter and no recall test, and never returns fewer rows than asked.

The insurance build makes this choice for its precedents: a btree narrows the rows by provision, status and date, then an exact distance sort ranks them. Its schema notes an approximate index as something to consider only past about 50,000 rows per provision. The point is not the number; it is that the decision is made per filtered set and written down, so it can be measured again later.

The example's UW-R09 case shows the same thing from the other side. A 5% filter on 400 points leaves 17 rows: an exact scan of those costs 17 distance computations, while an approximate index with an iterative scan did 817 to reach the same ten rows. The scale page turns this into a measurement playbook; the rule to take from here is to count the rows left after the filter before deciding that exact search is too expensive.

The tenant filter as a security boundaryMake it impossible to forget, then test that it holds

UW-R07 is a correctness bug and a security bug at the same time. Another book's precedent in the context is data the underwriter is not entitled to see, and the model will cite it fluently. Text that reaches the model cannot be taken back.

Three design choices keep the condition in place. First, one function builds every precedent query, and the tenant is a required argument, not an optional filter; there is no code path that runs the query without it. Second, the tenant value comes from the authenticated session on the server, never from the request body or from the model. Third, a test runs the query for each tenant and asserts that every returned row belongs to that tenant, the way the example's permission_leak debugger scenario does. A test that only checks "five rows came back" passes on the leaking query.

Defense in depth beyond the query (database-enforced policies, output checks, audit) is a guardrail topic, and guardrails.sammuthu.com treats it. Within retrieval, the rule stays short: the filter is part of the query, before the limit.

Float precision, near-ties and reproducible answer keysWhy the exact top k can differ between Python and PostgreSQL

"Exact" means every row was compared, not that every system computes the same digits. pgvector's vector type stores single-precision values, as its README documents; the example's Python computes in double precision. The example's sql/README.md notes the consequence: near-ties can swap places between the two. The distances in Q1 and in the SQLite run agree to four decimals because the vectors are short and the gaps are large; with 384 dimensions and dense neighborhoods, two rows a millionth apart can trade ranks.

For an answer key this matters. If index recall is computed by comparing an index result with an exact result from a different system, a near-tie can look like a miss. Compute the exact reference in the same database, with the same column type and the same tie-breaker. The example's ann.index_recall goes one step further and is tie-aware: a returned row counts if its distance is no larger than the k-th exact distance, so a row that ties with the true k-th neighbor is not penalized.

In one breath#

Exact search compares the query vector with every candidate row, sorts by the distance and keeps the nearest k, which in SQL is ORDER BY embedding <=> $1 LIMIT k plus a tie-breaker. The vector, the filter values and k are bound parameters, never text pasted into the statement. Every filter that must hold for the returned rows sits in the WHERE of the same query, before the limit, or the result leaks or starves. And because nothing is skipped, the exact result is the answer key for everything faster.

The agent now retrieves the right precedents for SUB-1004, from its own book, with the version and the source facts attached, and the plan proves every row was compared. The remaining risk is cost: the work grows with every row that survives the filters, and UW-R10 grows the precedent table from 40 to 4,000 rows. The next page, approximate nearest neighbors, builds indexes that skip most of those rows, and uses this page's exact result to measure what they miss.