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

RAG engineering · retrieval for agentic software

Retrieval is a query. The model reads only what it returns.

A language model knows nothing about your rows. When an agent answers from your policy, your cases or your documents, it is because ordinary code ran a query, picked some rows and handed their text to the model. This site starts from SELECT ... WHERE and climbs, one idea at a time, through full-text search, vectors computed by hand, embeddings, indexes, hybrid search, reranking, grounding, evaluation and production choices, asking one question at each step: which rows did we find, and how do we know they are the right ones?

For engineers who build agents, and for backend and database engineers who will host their retrieval. One running example, and code that runs without a model, a network or an API key.

You will understand
Why a model cannot know your rows, and how a vector in each row lets a query find the similar ones.
You will be able to say
What retrieval-augmented generation is, in one sentence, and where it fails quietly.
You will know
The insurance example and its three corpora, which every page uses, and where this site sits in its series.

An email arrives at an insurer's submissions inbox. A broker wants a quote for an office building:

From: broker@brokerage.example
To: submissions@insurer.example
Subject: Quote request SUB-1004

Please quote property cover for a masonry office, built 2001, 20,000 sq ft, sprinklered. The owner says the roof was replaced 9 years ago. Wind zone C. No losses.

The insurer's software does the first pass on requests like this one. It checks the roof with an outside roof-data provider, which reports 22 years, not 9. Now the software has a question that no field of the email answers: what does the insurer's own written policy say when the roof age on the application and the provider's roof age disagree?

The team plans to let a language model reason about the case, and here the problem starts. Ask a model that question with nothing attached and it answers fluently. It has never read this insurer's policy. It does not know that the policy changed on 2026-07-01, that the roof limit dropped from 30 years to 25, or that a rule called R-ROOF-03 sends a 13-year disagreement to a senior underwriter. Whatever it writes comes from text it read in training. It can sound right and cite nothing. So how does the right paragraph of the right version of the policy get in front of the model, and how does anyone check that it did?

A model knows nothing about your rows#

The answer is a search that runs before the model writes anything. Application code turns the question into a query, the query runs against a collection of stored texts, and the texts it returns are placed in the model's input with their ids. The model then writes from what it was handed, and cites it. That pattern is called retrieval-augmented generation, RAG for short: retrieval picks the rows; the model reads them and writes an answer that points back to them. The collection the query searches is a corpus, here the passages of the insurer's policy, and later two more.

A query that matches words works when the words match. The broker's situation, written as "roof replaced recently but aerial imagery shows an older roof", shares almost no words with the rule that governs it. This site's own case UW-R01 records what happens: LIKE '%mismatch%' finds 0 rows, and keyword ranking puts the rule fourth. What the agent needs is a query that finds passages by meaning, and that is where vectors come in.

Here is the idea in plain words before any model. Give every row a short list of numbers that places it in a space, so that rows about similar things sit close together. A list of numbers like [0.9, 0.8] is a vector, and it is a point: here, far along a "roof concern" axis and high on a "wind exposure" axis. Store one vector per row, in a column of the row. Turn the question into a vector the same way. Then compare the question's vector with each row's vector, rank the rows by distance and keep the nearest few.

Five toy rows show the whole mechanism. Each has a two-number vector; the query point is [0.85, 0.70], "an older roof in a high-wind zone". The script below, run in a scratch copy of the example, computes one Euclidean distance per row (the straight-line distance between two points), sorts, and keeps k = 3. A successful run proves only the arithmetic; the numbers were chosen by hand, not by a model.

"""The first primitive on five rows: one distance per row, sort, keep k."""
from vecmath import FIVE_ROWS, FIVE_ROWS_QUERY, l2

k = 3
ranked = sorted(FIVE_ROWS, key=lambda row: (l2(row["vec"], FIVE_ROWS_QUERY), row["id"]))
for rank, row in enumerate(ranked, start=1):
    keep = "returned" if rank <= k else "not returned"
    print(f"{rank}  id={row['id']}  {row['vec']}  L2={l2(row['vec'], FIVE_ROWS_QUERY):.3f}  {row['label']:<30} {keep}")
1  id=1  [0.9, 0.8]  L2=0.112  older roof, wind zone C        returned
2  id=5  [0.5, 0.5]  L2=0.403  roof age unknown, wind zone B  returned
3  id=3  [0.8, 0.1]  L2=0.602  older roof, wind zone A        returned
4  id=2  [0.2, 0.9]  L2=0.680  new roof, wind zone C          not returned
5  id=4  [0.1, 0.2]  L2=0.901  new roof, wind zone A          not returned

Every row was compared, five distances for five rows. In SQL the same idea is one ORDER BY on a distance and a LIMIT; with the pgvector extension for PostgreSQL it reads ORDER BY embedding <-> $1 LIMIT 3, where <-> is Euclidean distance and $1 is the query vector, bound as a parameter. Notice what the vector cannot say: row 2 is in wind zone C, the same as the query, and still misses the cut. Exact facts like a wind zone stay in a WHERE clause.

The first primitive. 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.

Diagram: The first primitive: the application and the database pick the rows; the model reads them

Follow the arrows from the question to the answer. The query text becomes a vector, the database compares it with each row's vector under the exact filters, ranks the rows and keeps k. Only then does the model see anything, and it sees only those rows. Click a box for the SUB-1004 example.

  1. Application
    1. Question in words: The agent needs guidance for a situation described in plain words, with no rule id in it.
    2. Query vector: The question is turned into a vector by the same model and preprocessing that made the stored vectors.
  2. Database
    1. One vector per row: Each stored passage carries its own vector next to its id, version and text.
    2. Filter, compare, rank: Exact conditions remove rows; each remaining row's distance to the query vector is computed and the rows are sorted.
    3. Keep the nearest k: Only the first k rows leave the database, with their ids.
  3. Model
    1. Reads only those rows: The model writes from the passages it was handed; a passage the query missed does not exist for it.

Read the diagram for who does what. The model's lane holds one box, and it is the last one. Every box that decides which rows the model sees, the encoding, the filter, the ranking, the cut at k, sits in the application's and the database's lanes. That is why this site spends most of its pages on the query: whatever the query returns sets the ceiling of the answer.

Before AI. A backend engineer has written this query many times. "Find the stores nearest to this customer" is ORDER BY distance(store.location, $1) LIMIT 5: one distance per row, a sort, a limit, maybe a spatial index to skip far-away rows. A vector query is the same shape. What is new is where the coordinates come from: a model that turns text into numbers, which the embeddings page opens up.

In real systems the vectors are not chosen by hand. An embedding model computes them, a list of 384 or 768 numbers per text, and the comparison runs over thousands or millions of rows, often through an index that skips most of them. Every one of those additions is a page of this site. None of them changes the primitive.

The example in one minute#

Software does not work from the email. An intake step reads it once and writes the facts down as one structured record, called a submission: a JSON record of one quote request that every later step reads instead of the email. In the scenario it has 63 fields; the example carries about ten.

{
  "submission_id": "SUB-1004",
  "occupancy": "office",
  "construction": "masonry",
  "year_built": 2001,
  "area_sq_ft": 20000,
  "sprinklered": true,
  "roof_age_years": 9,
  "wind_zone": "C",
  "prior_losses_5y": 0
}

The submission goes to the underwriting agent, a program that calls a language model in a loop and runs the tools the model asks for in between. It gathers evidence, applies the insurer's rules and writes a recommendation: one of approve, decline, request_info or escalate, with reasons and the ids of the rules and evidence it rests on. The person who decides is the underwriter, the insurer's employee who accepts or refuses a risk. The model recommends; a person decides.

The rules come from the policy fixture UW-GUIDE, the insurer's written guidance in this example. Its current version is 2026.2, effective 2026-07-01; the earlier 2025.4, effective 2025-01-01, had a 30-year roof limit where the current one has 25. Its rules (R-ROOF-01, R-ROOF-02, R-ROOF-03, R-SPRK-01, R-HUMAN-01) are illustrative.

Retrieval serves the agent through two tools, and behind them sit three corpora. Each corpus answers a different question, embeds different text and keeps different facts as exact filters.

CorpusThe question it answersToolWhat is embeddedWhat stays an exact filter
Guidance (guidance_passages)What does the current policy say about this?search_guidance(query)one passage of the policy fixturepolicy_version, rule_id, status, section
A: applicant profiles (applicant_profiles)Which past applicants look like this one?find_similar_decisions(submission_id), step 1a banded profile summarywind_zone, risk_region, occupancy, construction, tenant
B: underwriter precedents (precedents)How did underwriters decide similar cases, and why?find_similar_decisions(submission_id), step 2the decision rationale and contextdecision, policy_version, decided_role, override, status, tenant

Guidance carries the most authority: current policy outranks any past decision. A precedent is decision support, never a replacement for current policy or for the human decision, and the precedents in the example are synthetic (simulated: true), never history.

Diagram: One submission, two tools, three corpora, one recommendation for a person

Read left to right. The submission feeds two retrieval tools. search_guidance searches the policy passages; find_similar_decisions searches past applicant profiles first and then the precedents of similar cases. Everything lands in a recommendation with citations, and an underwriter decides. Click a box for what it holds in the example.

  1. Input
    1. Submission: The structured record of the broker's quote request; later steps read it, never the email.
  2. Tools and corpora
    1. search_guidance: Searches the passages of the policy fixture UW-GUIDE, current version by default.
    2. Corpus A: Past applicants whose banded profile looks like this one, with wind zone and tenant as exact filters.
    3. Corpus B: How similar cases were decided and why, each with its policy version, decider and override flag.
  3. Output
    1. Recommendation: One of approve, decline, request_info or escalate, with reasons and the ids it rests on.
    2. Underwriter decides: The model recommends; a person decides.

For SUB-1004 the pieces meet in one result. search_guidance finds the current passage of R-ROOF-03 from the roof description. find_similar_decisions finds comparable applicants and how similar cases were decided, each labeled with the policy version it was decided under. The scripted answer writer of the example, run in a scratch copy, then produces case UW-004, the default case on most pages:

PASS  UW-004  Default case: escalate, cites R-ROOF-03
      recommendation=escalate citations=['R-ROOF-03'] passages=['GD-2026.2-R-ROOF-03'] evidence=['EV-ROOF-0412'] requires_human=True policy_version=2026.2
        reason: The application states a roof age of 9 years; the roof-data provider assessed 22 years (EV-ROOF-0412).
        reason: The two sources differ by 13 years, more than R-ROOF-03 allows (5): a senior underwriter should review.
        reason: The model recommends; a person decides.

Every id in that output came from a retrieved row: the passage GD-2026.2-R-ROOF-03, its policy_version 2026.2, the evidence EV-ROOF-0412. If retrieval had returned the 2025.4 version instead, the recommendation would rest on a superseded rule, and it would still look well cited. That is the failure this site keeps coming back to: retrieval that returns the right shape and the wrong rows.

Honest boundary. The series example is an illustrative reconstruction for teaching. Cases, rules, fields, vectors and every number are invented. Nothing here reports the quality of a real system or model. Example roof thresholds are synthetic, not insurance practice.

The example code is standard-library Python. Toy encoders stand in for embedding models: deterministic functions that map words to 16 named concept dimensions and normalize the result. They behave like an embedding model in the ways this site teaches (paraphrases land close, exact ids blur, a second encoder with the same dimension has a different space), and they are not a model. The answer writer is a script. So the results show how retrieval works, not how good any model or product is. Real embedding models produce 384, 768 or more dimensions; the insurance build that inspired the example uses all-MiniLM-L6-v2 at 384, in that build's own code.

Try it: five rows and a query point#

The lab below holds the five rows from the script above. Drag the query point, click the plot, or move it with the arrow keys; the distances, the ORDER BY ... LIMIT k result and the SQL with its bound parameter update as it moves. Turn on the WHERE filter to see the difference between an exact condition and a ranking: the filter keeps only rows whose wind_zone is exactly C and cannot say "similar". The table after it holds the same information.

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 (query [0.85, 0.70], toy numbers in this example). The two tools answer different questions and combine: the filter decides which rows may appear, the distance decides their order.

Four separations every page defends#

The words around retrieval blur easily, and most production mistakes start with two of them treated as one. The site keeps four apart.

Where a vector is stored is one decision; how it is searched is another; which model computed it is a third. A vector column in the source row, a side table, a SQLite virtual table and a dedicated engine are storage layouts (storage); exact search and an approximate index are search algorithms (exact search, ANN indexes); the embedding model is a separate choice again (choosing a model).

The model, the dimension, the index and the metric are four different things. Two models that both produce 384 numbers do not share a space, so a query from one against rows from the other returns plausible, wrong neighbors (case UW-R06). HNSW is an index, a data structure, never a model. Cosine, Euclidean and inner product are metrics.

Reindexing rebuilds a search structure; re-embedding recomputes vectors after a model or content change. They are different jobs with different costs (operations).

Top 50 candidates, rerank to 10, 5 passages in the context are per-query budgets, not storage sizes. Growing a table from 40 rows to 4,000 changes none of them and requires neither a reindex nor a re-embedding by itself (case UW-R10, scale).

Kept apartNot the same asWhere it is explained
Storage layoutsearch algorithm, embedding modelStorage
Embedding modeldimension, index, distance metricEmbeddings
Reindexre-embedOperations
Per-query budgets 50 / 10 / 5the size of the tablePipelines
In a coding agent. A coding agent retrieves from a repository the same way. Grep and symbol lookup are lexical and exact: they find retry_with_backoff when you type it. "Find where we retry provider calls" is a question of meaning, which needs vectors over code chunks. Identifiers need both, which is hybrid search. And after a refactor, chunks that point at deleted functions are an operations problem, not a model problem.
What this is not. This site is about finding the right rows. Choosing and fitting what enters the model's context window, of which retrieval is one source, is context.sammuthu.com. Retrieval offered as a tool the model decides to call, with its schema and execution, is tools.sammuthu.com.
When retrieval-augmented generation is the wrong toolA lookup beats a search whenever the key is known

RAG earns its place when the question is in words and the answer is in someone else's words: a broker's description and a rule's text. It is the wrong tool when the key is known. The agent that needs SUB-1004's wind zone should run SELECT wind_zone FROM submissions WHERE submission_id = $1; a semantic search for the same fact adds a way to be wrong and nothing else. The same holds for the current text of a rule the agent has already identified: WHERE rule_id = 'R-ROOF-03' AND policy_version = '2026.2' is complete and exact.

Two other cases need care. A question whose answer is a computation over many rows ("how many sprinklered offices in wind zone C were declined last year?") is an aggregate query, not a retrieval; handing the model twenty retrieved rows and asking it to count them is slower and less reliable than COUNT(*). And a small, stable body of text that always applies may belong in the instructions rather than in a corpus. The retrieval page separates lookup, keyword search and semantic search, and says when you do not need RAG at all.

A vector index is not necessarily a vector databaseExtensions, libraries, engines and managed services run the same primitive

The phrase "vector database" is often used for anything that stores vectors, and the confusion costs teams a second system they may not need. On this site, a vector database is a dedicated engine whose main job is vector search. pgvector is an extension that gives PostgreSQL a vector column, distance operators and two approximate index types, so retrieval stays a SELECT inside the same transactions, joins and backups as the facts it describes. sqlite-vec is an extension for SQLite. FAISS is a library inside a program.

The insurance build that inspired the example runs both of its corpora in PostgreSQL 16 with pgvector, in that build's own code: one table per corpus, an HNSW index on one and an exact distance sort on the other, because its per-provision precedent sets are small. Whether a dedicated engine is worth adding is a measured question about filters, update rate, memory and operations, never a row count; storage, scale and the decision guide on recipes work through it.

Why retrieval failures are quietEvery failure on this site returns a list that looks reasonable

A database that cannot connect throws an error. Retrieval rarely does. A guidance search without a version filter returns the superseded 30-year rule next to the current 25-year rule (UW-R03). A query vector from a new model against rows from the old one returns five rows that all sound like underwriting, one of them right (UW-R06). A tenant filter applied after the top k leaks another book's precedent or returns too few rows (UW-R07). A selective filter over an approximate index returns fewer rows than asked, and on a real PostgreSQL run it returned zero (UW-R09, pgvector).

In each case the model then writes a fluent, cited answer from the rows it was given. Nothing downstream can recover a passage that retrieval dropped, and nothing in the answer says a passage is missing. That is why the site pairs every mechanism with a check: an exact query as the answer key, a guard on the model identity, a count of rows returned against k, labeled queries with recall@k (evaluation), and a retrieval trace per query (pipelines).

Where this fits#

RAG Engineering is one of fifteen subjects in a series of guides by the same author. Each site owns one question about agentic software and links to the others where the subjects meet, instead of repeating them. This one is marked below.

Three neighbors matter most for this site. Context engineering decides how much of what retrieval found fits in the model's input and in what order. Tools engineering covers retrieval offered as a tool the model calls. Evals covers graders, golden sets and release gates, while this site owns the retrieval metrics themselves. Where a page reaches one of those subjects, it says what the subject is not here, in one sentence, and links the site that owns it.

How to read this site#

The pages follow the order in which questions come up when a team gives its agent its first retrieval. First the foundations: what retrieval means, what SQL already does, vectors by hand, embeddings and how to choose a model. Then the search itself: where vectors live, exact search, indexes, and the two databases this site runs. Then the pipeline that turns a question into the context a model reads, and how to evaluate it. A practice part closes the chapters with products, operations, advanced techniques, whole designs and framework recipes.

Foundations. From a WHERE clause to a vector with a meaning.

Search. Where vectors live and how a query finds the nearest ones.

Pipeline. From a question to the passages a model reads, and how to know it worked.

Practice. Products, operations, designs and code you will meet in production.

If you are new to retrieval, read in order: every page assumes only the pages before it. If you already run a retrieval system, jump to the question of the day. Each page recaps what it needs in a sentence and links the full explanation, and each folds its harder material (failure cases, competing designs, security, scale and operations) into blocks like the three above, so the first reading stays short.

If your question isStart at
Do I need RAG, or a lookup?Retrieval
Why does my vector search miss an exact rule id?Hybrid search
Why does my filtered query return fewer rows than asked?pgvector, then ANN indexes
Do I need a vector database at 100,000 rows?Top-k and scale
How do I change the embedding model without downtime?Operations
What does a LangChain or LlamaIndex retriever hide?Recipes

In one breath#

A model knows nothing about my rows, so retrieval picks the rows and the model reads them: that hand-off, plus an answer that cites what it was handed, is retrieval-augmented generation. With vectors, each row carries one vector in an embedding column; a query vector is compared with each row's vector, the rows are ranked by distance and the nearest k come back, while exact facts such as a policy version stay in the WHERE. The insurance example runs this over three corpora, guidance through search_guidance and applicant profiles and precedents through find_similar_decisions, and for SUB-1004 it yields escalate, citing R-ROOF-03. Retrieval is a query. The model reads only what it returns.

The agent's needs for SUB-1004 are not all the same kind of question. It needs the submission's own fields, which have a key; the sprinkler rule, which has an exact name; and the roof-conflict rule, which has only a meaning. The next page, retrieval, separates lookup, keyword search and semantic search, and shows what each one can and cannot find.