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
About

About this site and its sources

What this site is, what it is not, where its ideas come from, the sister sites, and how the facts and the SQL were checked.

This is a knowledge site about retrieval for agentic software: how the rows and passages a language model reads are found. It starts from a plain SELECT ... WHERE and climbs through full-text search, vectors computed by hand, embeddings, storage layouts, exact search, approximate indexes, pipelines, hybrid search, reranking, grounding, evaluation and production choices. At every step it asks one question: which rows did we find, and how do we know they are the right ones? It is written for engineers and architects who build agent systems, and for backend and database engineers who know SQL well and meet vector search for the first time.

One example runs through every page. An insurer receives a broker's request for a quote. An underwriting agent searches three corpora: the insurer's written guidance, past applicant profiles and underwriter precedents. It writes a recommendation with citations, and a human underwriter decides. The model recommends; a person decides.

What it is and is not#

It isIt is not
A ladder: one idea per step, each term in plain English with a tiny example before it is usedA standard, or the only way to build retrieval
One running example with the series cases UW-004, UW-005, UW-007 and this site's own cases UW-R01 to UW-R10A record of any real insurer. The cases, rules, fields, vectors and numbers are invented
A demonstration of mechanism: how a vector column, a distance, a filter, an index, a fusion step and a citation fit togetherA measurement of any model, database or product. No page reports how good a real system is
Code and SQL that were run: a standard-library Python example, SQL on a throwaway PostgreSQL, sqlite-vec in a throwaway environment, with their real outputA framework to install, or a benchmark. Costs are counted as distance computations, never as milliseconds
Products described from their own documentation, read on 2026-11-18, by what each does for retrievalA ranking or a buying guide. No product is ranked above another, and no price is quoted

What is original here and what is not#

PartStatus
Retrieval-augmented generation, embeddings, cosine similarity, BM25, HNSW, IVF, reciprocal rank fusion, recall@k, MRR, nDCGEstablished methods, described from their papers and documentation (listed below). This site explains them; it did not invent them
PostgreSQL, pgvector, SQLite, sqlite-vec, vec1, the Google Cloud products, the vector databases on the ecosystems page, LangChain and LlamaIndexProducts and libraries, described from their own documentation as read on 2026-11-18
The first primitive as stated on every page (one vector per row, compared, ranked, nearest k, the model reads only what was returned) and the four separations (layout, algorithm, model; model, dimension, index, metric; reindex, re-embed; budgets, table size)A teaching framing written for this site
The decision guide, the storage decision flow and the eight-step scaling playbookDesigns proposed on this site, assembled from common database and search practice. They contain no row-count thresholds on purpose
The toy encoders, the three corpora, the policy fixture, the cases UW-R01 to UW-R10 and the failure scenariosWritten for this site. All synthetic
The example code, its tests, the SQL files and the interactive labsWritten for this site. The labs' logic is a second implementation of the Python code, and a test fails if the two differ

Where a page takes an idea from a source, the page says so. Where this site proposes something, it calls it a design.

How the facts were checked#

Kind of claimHow it was checked
A named product, library, standard or paperRead in its own documentation on 2026-11-18. A source pack of 377 claim rows, each with its address and date, was built first (docs/facts/_sources/ in the repository); every item that looked surprising, such as a renamed product, a moved page or a non-default constant, was fetched a second time, and an independent second reader re-fetched the most consequential ones. Each page then logs its own claims in docs/facts/<page>.md, re-reading a source the same day when the pack did not cover a claim. Where the documentation is silent, the page says so
The exampleRun. Every command shown from examples/rag-uw/ was executed in a copy of the folder, and the output on the page is the output it printed. run_demo.py passes 13 of 13 cases and 8 of 8 debugger scenarios; python3 -m unittest runs 61 tests, all passing; two runs print the same bytes
The SQLschema.sql, seed.sql and queries.sql ran unchanged on a throwaway PostgreSQL 16.14 with pgvector 0.8.3, in a disposable container started for the run, on a database created before it and dropped after it. The recorded output is examples/rag-uw/sql/OUTPUT.md, and only that output is presented as run. Plans and row counts are one run on one laptop, toy data; not a benchmark
SQLitesqlite_exact.py (standard library, SQLite 3.53.4) was run in a copy of the example. sqlite_vec_demo.py ran with sqlite-vec 0.1.9 installed into a throwaway virtual environment outside the repository; its output is examples/rag-uw/SQLITE_VEC_OUTPUT.md
The labsThey read data generated from the example. node scripts/test_rag_core.mjs runs 304 checks that the JavaScript gives the same results as the Python, from vector math and BM25 to the chart filter of the ecosystems lab
Other codeA block that was not run says so, for example "documented shape; not run here". Every Python block on the site compiles in the build
Links and structureA build step checks every internal link and anchor, every diagram, every glossary reference and every JSON block before the site is published

Documentation changes, and in this field it changes monthly. By the day these pages were read, Google Cloud's documentation presented Vertex AI as part of Gemini Enterprise Agent Platform and had renamed several retrieval products, LangChain's documentation had moved to a new address, and the langchain-postgres README deprecated a class that LangChain's own integration page still shows. Treat every statement about a product as true on the day it was read, and read the product's page again before you build on it.

What the example does not show#

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.

In the exampleWhat it really is
The embedding modelToy encoders (toy-a@1, toy-b@1, toy-a@2) that map words to 16 named concept dimensions and normalize. They behave like an embedding model in the ways the site teaches and are not a model. Real models produce 384, 768 or more dimensions
The answer writerA script that builds the prompt, cites, abstains and applies precedence. No model is called. There is no network and no key
The cross-encoderA deterministic stand-in whose scores are not probabilities
HNSW and IVFToy implementations with fixed seeds, measured in distance computations. Their numbers say how the mechanism trades work for recall, not how fast any library is
The corpora18 guidance passages, 240 applicant profiles and 40 precedents, all synthetic; precedents are marked simulated: true and are never history
MetricsComputed on synthetic labels written for the example; they describe the example, not any model
Google Cloud, the vector databases, LangChain and LlamaIndexDescribed from documentation; not run here

The two builds the case studies come from#

Two projects by the author appear as case studies. Pages state only facts confirmed from their source on 2026-11-18, and say "in that build" or "in the code read".

The insurance build is a production-shaped insurance quote workflow with simulated vendors, a Java service (Spring, JDBC) on PostgreSQL 16 with pgvector. In that build, both retrieval corpora use all-MiniLM-L6-v2 at 384 dimensions, run in process through ONNX Runtime with no embedding API call; vectors are L2-normalized in code and compared by cosine distance. Its applicant-profile corpus embeds a fixed banded template, never raw numbers or personal data, and has an HNSW index (m = 16, ef_construction = 64, ef_search = 80). Its precedent corpus has no approximate index: a btree on provision, status and decision date plus an exact distance sort, because per-provision sets are small, then a reranker of 0.6 × matching bands + 0.3 × cosine similarity + 0.1 × recency. It insures homes in its own domain; the commercial-property example on this site is a separate, synthetic reconstruction.

Mnemos is the author's open-source, local-first personal RAG application (TypeScript, Next.js; version 0.21 read). In the code read, it keeps everything in one SQLite file with the sqlite-vec extension loaded, with vectors in a separate vec0 virtual table joined to its chunk table by id. It embeds locally with a 384-dimension model through ONNX in process, chunks with a recursive character splitter of about 1,000 characters and about 200 overlap, retrieves by dense search only, and by default sends nothing off the machine; an external model, if configured, receives the retrieved chunks only.

The sister sites#

This site is one of a series on AI-native engineering. Each owns one subject and links the others at the seams.

SiteWhat it owns
prompt.sammuthu.comInstructions: how they are written, composed and defended
context.sammuthu.comWhat the model can see now: selection, retrieval, budget
memory.sammuthu.comDurable information and saved execution state
a2a.sammuthu.comMessages between agents; A2A versus MCP
observe.sammuthu.comLogs, metrics, traces, audit events, replay
guardrails.sammuthu.comEnforceable checks around a probabilistic agent
tools.sammuthu.comCallable capabilities: schema, execution, MCP tools
mcp.sammuthu.comShared capabilities across teams: when MCP is justified, identity, authorization, governance
schema.sammuthu.comTyped contracts: shape, validation, repair, evolution
orchestrate.sammuthu.comThe control plane: what runs next
reliable.sammuthu.comAcceptable outcomes despite failures and load
harness.sammuthu.comThe runtime around a model call
eval.sammuthu.comMeasurement: graders, golden sets, release gates
diff-to-deploy.sammuthu.comShipping a change with coding agents

Sources and reading list#

Each link below was opened on 2026-11-18. The fact logs in the repository list every claim with the exact page it came from.

Standards and papers#

SourceWhy read it
Retrieval-Augmented Generation for Knowledge-Intensive NLP TasksThe paper that named the pattern: a model's parametric memory combined with a retrieved, non-parametric index
Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs and the hnswlib parameter notesHNSW: layers, M, ef_construction, ef_search
The Probabilistic Relevance Framework: BM25 and BeyondBM25 and its parameters k1 and b
Reciprocal Rank Fusion outperforms Condorcet and individual Rank Learning MethodsReciprocal rank fusion and its constant k = 60
Cumulated Gain-Based Evaluation of IR TechniquesDiscounted cumulated gain, the basis of nDCG
ColBERT, HyDE and Matryoshka Representation LearningLate interaction, hypothetical-document retrieval and truncatable embeddings, on the advanced and model pages
ScaNNThe approximate search library behind several Google Cloud products

PostgreSQL and pgvector#

SourceWhy read it
pgvector and its changelogThe vector type, operators, HNSW and IVFFlat, iterative scans, filtering. Latest release 0.8.7 on 2026-10-01; 0.9.0 unreleased
pgvector-pythonBinding vectors from psycopg, SQLAlchemy and other drivers
PostgreSQL full-text search and EXPLAINtsvector, ranking, GIN indexes, and reading plans

SQLite#

SourceWhy read it
sqlite-vec, its vec0 documentation and KNN queriesThe vec0 virtual table, metadata and partition columns, the default L2 distance
Vec1 Vector Extension and the Vec1 User ManualThe SQLite project's own vector extension, version 0.8, with exhaustive modes and an IVFADC index
Python sqlite3create_function, used for exact search with the standard library

Google Cloud#

SourceWhy read it
Gemini Enterprise Agent Platform overview and release notesVertex AI is now part of this platform; the naming table of the April 22, 2026 release
Vector Search and Agent RetrievalThe index service and the search engine formerly called Vector Search 2.0
RAG Engine and Agent SearchThe managed retrieval pipeline and the search application
Cloud SQL extensions and AlloyDB vector searchpgvector in managed PostgreSQL, and AlloyDB's ScaNN index

Other engines#

SourceWhy read it
FAISS, Milvus, Qdrant hybrid queries, Weaviate vector indexesA library and three engines, each read for indexes, filtering and fusion
Pinecone hybrid search, Elasticsearch dense vectors, OpenSearch vector search, ChromaManaged and search-engine options on the ecosystems page
MongoDB Atlas Vector Search deployment options and Redis FT.HYBRIDTwo databases that added vector search to an existing engine

Frameworks#

SourceWhy read it
LangChain PGVector integration, PGVectorStore and the langchain-postgres READMERead all three: the README deprecates PGVector for PGVectorStore; the integration page does not mention it
LangChain retrievalWhat a retriever is in LangChain
LlamaIndex Postgres vector store, VectorStoreIndex and retrieversThe same table behind LlamaIndex's objects
psycopg 3: passing parametersWhy a bound parameter is sent separately from the SQL text

Models and benchmarks#

SourceWhy read it
all-MiniLM-L6-v2 and bge-small-en-v1.5The two 384-dimension models of the case studies
MTEBThe public embedding benchmark, and what it can and cannot tell you about your queries
Sentence Transformers: retrieve and rerank and cross-encodersBi-encoder retrieval followed by a cross-encoder reranker
Gemini embedding modelsA hosted embedding option on Google Cloud

Who wrote it#

Written by Sam Muthu, an engineer who builds agentic systems. The example follows the shape of a real project by the author; everything in it is synthetic. Corrections are welcome.