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
Practice · 24

Framework recipes and the decision guide

The same retrieval written as raw SQL, as direct SDK calls and as LangChain or LlamaIndex code, so the query under a retriever object stays visible, plus a decision guide for the whole site.

One retrieval, four ways
The guidance search of case UW-R01 written as raw SQL, as direct Python with psycopg, with LangChain and with LlamaIndex, side by side.
What a retriever hides
Five decisions sit inside every retriever object: the embedding model, the distance metric, where the filter runs, the value of k, and the SQL itself.
The whole site, as a guide
A table from your question to the page that answers it, and a decision flow for storage with no row-count thresholds.

The underwriting agent's guidance search works. For this site's own case UW-R01, the broker's sentence "roof replaced recently but aerial imagery shows an older roof" brings back the roof-conflict rule R-ROOF-03 from the current policy, version 2026.2, and the tests for UW-004 pass. The search is one short SQL statement with bound parameters.

Picture a cleanup a few weeks later. An engineer replaces that statement with a framework: a vector store object, one call to create a retriever, one call to run it. The code shrinks to four lines and the UW-004 test still passes, because R-ROOF-03 still comes back. What nobody wrote down is what the old SQL said and the new code does not. The old query had policy_version = $2 in its WHERE. The new call has no filter argument, so every superseded 2025.4 passage ties with its current twin at the same distance, exactly as in the unfiltered run below. For a question about the maximum roof age, that puts the old 30-year limit next to the current 25-year one. That is case UW-R03, reintroduced by a refactor that made the code shorter and the query invisible.

The framework was not wrong. It did what it was told, and it was told less. This last chapter writes one retrieval four ways so that the decisions inside each version can be pointed at, then turns the whole site into a guide: which page answers which question, and how to choose where the vectors live.

What this is not. This page does not cover retrieval as a tool the model decides to call, with its schema and its execution path; that is tools.sammuthu.com. It shows the query under the tool.

One retrieval has five decisions, whoever writes it#

Every way of writing the UW-R01 search has to answer the same five questions, whether the answer is in your code or in a library's defaults.

The first is the embedding model. The query text must become a vector through the same model, version and preprocessing as the stored rows, or the distances mean nothing (embeddings shows case UW-R06, where they look plausible and are wrong). The second is the distance metric: cosine distance, Euclidean distance or inner product (vectors). The third is where the filter runs: inside the same query, before the limit, or after the nearest rows were already chosen (exact search). The fourth is k, how many rows come back. The fifth is the SQL, or whatever the engine runs instead: which table, which index, exact or approximate, and how a match is joined back to its source row.

A retriever is an object that takes a query text and returns a list of documents, with those five decisions stored inside it. The LangChain retrieval page, as documented on 2026-11-18, defines it in nearly those words: an interface that takes an unstructured query and returns documents. Here is the smallest picture of the difference. In SQL you write WHERE policy_version = $2 ORDER BY embedding <=> $1 LIMIT $3, and the filter, the metric and k are on the screen. With a retriever you write retriever.invoke(query), and the same three things live in the arguments that built the object, or in defaults you never read.

Diagram: One retrieval, five decisions: on the screen in SQL, inside the object with a retriever

The top lane is the guidance search of case UW-R01 written in SQL: each box is a line a reviewer can read. The bottom lane is the same search as a retriever call: a text goes in, documents come out, and the five decisions sit in constructor arguments and defaults. Click a box for where that decision lives.

  1. Raw SQL: you write each decision
    1. Embedding model: The query is encoded by your code with the stored rows' model, and the model name is compared in the query.
    2. Distance metric: The operator in ORDER BY decides the metric: cosine distance, L2 or negative inner product.
    3. Filter placement: Exact conditions sit in the same query, so they hold before the nearest rows are counted.
    4. k: How many rows come back is a parameter you can see and log.
    5. Join back: Each match leads to its source row: here the vector is in the row, so the match is the row.
  2. A retriever call: the decisions are inside
    1. Query text in: The caller passes text; nothing on the call line names a metric, a filter or a table.
    2. Five decisions inside: Model object, distance strategy, filter, k and table were set when the store was built, or left to defaults.
    3. Documents out: The result is a list of documents; whether a score is a distance or a similarity is not stated on the pages read.

The top lane is what raw SQL makes you write, one reviewable line per box. The bottom lane is what a retriever call shows. Neither lane is wrong; the question for each version below is where each of the five boxes went.

Before AI. Backend engineers know this trade from object-relational mappers. A Spring Data repository method findByStatusOrderByCreatedAtDesc hides a SELECT, and the team that ships it still turns on SQL logging the first time a page is slow, because the generated query is the only thing that explains the plan. A retriever is a repository method for nearest-neighbor search. It deserves the same habit.

Recipe 1: raw SQL with bound parameters#

The reference version is SQL, because SQL shows all five decisions. The block below is the prepared statement that ran as Q1 on a throwaway PostgreSQL 16.14 with pgvector 0.8.3 (recorded in examples/rag-uw/sql/OUTPUT.md, one run on one laptop, toy data; not a benchmark). The query vector for UW-R01 was computed by the toy encoder toy-a@1, 16 dimensions, a deterministic stand-in for an embedding model, and bound as $1; k is bound as $2. A successful run proves the statement is exact and parameterized; it does not prove anything about a real model's quality.

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);
     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

All five decisions are visible. The metric is <=>, cosine distance. The filter is trusted, and it is the only one: there is no version condition, so each superseded passage ties with its current twin at the same distance, because the two versions of R-ROOF-03 use the same words. The order has a tie-breaker, passage_id, so the result is repeatable. k is the bound $2. The join back needs no join at all: the vector lives in the same row as passage_id, rule_id, policy_version and the passage text, so the match is the source row (storage calls this a vector column in the row).

The run shows the failure of the opening, before any framework is involved. The fix is one more bound parameter. The statement below ran as Q3 in the same session, with the query vector of UW-R03 ("maximum roof age in wind zone C"), first with the version left empty and then with '2026.2':

PREPARE guidance_versioned(vector, text, int) AS
  SELECT passage_id, policy_version, round((embedding <=> $1)::numeric, 4) AS cosine_distance
  FROM guidance_passages
  WHERE trusted AND ($2::text IS NULL OR policy_version = $2)
  ORDER BY embedding <=> $1, passage_id
  LIMIT $3;
EXECUTE guidance_versioned(:'q_r03', NULL, 5);
EXECUTE guidance_versioned(:'q_r03', '2026.2', 5);
     passage_id      | policy_version | cosine_distance 
---------------------+----------------+-----------------
 GD-NOTE-02          | 2026.2         |          0.1350
 GD-2025.4-R-ROOF-01 | 2025.4         |          0.1486
 GD-2026.2-R-ROOF-01 | 2026.2         |          0.1486
 GD-2025.4-R-ROOF-02 | 2025.4         |          0.4775
 GD-2026.2-R-ROOF-02 | 2026.2         |          0.4775

     passage_id      | policy_version | cosine_distance 
---------------------+----------------+-----------------
 GD-NOTE-02          | 2026.2         |          0.1350
 GD-2026.2-R-ROOF-01 | 2026.2         |          0.1486
 GD-2026.2-R-ROOF-02 | 2026.2         |          0.4775
 GD-NOTE-01          | 2026.2         |          0.6722
 GD-2026.2-R-ROOF-03 | 2026.2         |          0.6916

With the filter, the superseded rule cannot appear, and the rows it took are replaced by current rows further down. The version value travels as a parameter, never pasted into the SQL text, which is the rule exact search explains: a bound value cannot change the statement.

The recorded run did not execute UW-R01 with the version filter, so the filtered result is shown from the example's Python instead. The script below, written for this page and run in a scratch copy of examples/rag-uw, does the same five steps in the standard library: one vector per row from toy-a@1, the query encoded with the same encoder, the filter applied before ranking, an exact sort, k = 5, and a join back from each matched id to its row. The guard argument makes exact_topk refuse a query vector from another encoder.

"""UW-R01 written out by hand: encode, filter, rank exactly, keep k, join back."""
import data
from encoders import TOY_A1
from search import VectorTable, exact_topk

QUERY = "roof replaced recently but aerial imagery shows an older roof"

table = VectorTable.build("guidance_passages", data.guidance_passages(),
                          "passage_id", "text", TOY_A1)            # one vector per row, model recorded
query_vec = TOY_A1.encode_query(QUERY)                               # same encoder as the rows
result = exact_topk(table, query_vec, k=5,
                    flt={"policy_version": "2026.2"},                # exact filter, before the limit
                    query_model=TOY_A1.name)                         # the guard compares model names
print(f"rows {result.rows_total}, after filter {result.rows_after_filter}, "
      f"distance computations {result.distance_computations}")
for hit in result.hits:
    row = table.row(hit["id"])                                       # join back to the source row
    print(f"{hit['distance']:.4f}  {hit['id']:<20} rule={row['rule_id']}  status={row['status']}")
rows 18, after filter 13, distance computations 13
0.2293  GD-NOTE-01           rule=None  status=current
0.3035  GD-2026.2-R-ROOF-03  rule=R-ROOF-03  status=current
0.3792  GD-2026.2-R-ROOF-02  rule=R-ROOF-02  status=current
0.4007  GD-2026.2-R-ROOF-01  rule=R-ROOF-01  status=current
0.4636  GD-NOTE-06           rule=None  status=current

The distances match the PostgreSQL run row for row where both rank the same passages: 0.2293 for GD-NOTE-01, 0.3035 for the current R-ROOF-03. The filter ran before ranking, so 13 rows were compared, not 18, and five came back. This output is the answer key for every recipe that follows: whatever a framework returns for UW-R01 with a version filter should be these five rows in this order, or the difference needs a reason. The standard-library SQLite version of the same query, with bound ? parameters and the model name in the WHERE, is sqlite_exact.py:search_guidance_sql (SQLite).

Recipe 2: direct Python with psycopg and pgvector-python#

Most production services do not type SQL into psql. They call the database from application code through a driver. The driver here is psycopg 3, and pgvector-python adds the vector type to it. The block below is assembled from the pgvector-python README and the psycopg documentation as documented on 2026-11-18; it was not run here. It shows that "direct" code keeps all five decisions in view, and what it costs: you own the encoder and the guard.

# documented shape; not run here (pgvector-python README and psycopg 3 docs, read 2026-11-18)
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

SEARCH_GUIDANCE = """
    SELECT passage_id, rule_id, policy_version, body, embedding <=> %s AS cosine_distance
    FROM guidance_passages
    WHERE policy_version = %s AND status = 'current' AND trusted AND embedding_model = %s
    ORDER BY embedding <=> %s, passage_id
    LIMIT %s
"""


def search_guidance(conn: psycopg.Connection, query_vec: list[float], model: str,
                    version: str = "2026.2", k: int = 5) -> list[tuple]:
    """Exact cosine top-k over the current guidance; every value is a bound parameter."""
    q = Vector(query_vec)
    return conn.execute(SEARCH_GUIDANCE, (q, version, model, q, k)).fetchall()


conn = psycopg.connect("dbname=rag_uw_scratch")
register_vector(conn)                      # teaches psycopg the vector type
rows = search_guidance(conn, encode_query("roof replaced recently but aerial imagery shows an older roof"),
                       model="toy-a@1")

The pieces map one to one onto the SQL recipe. register_vector(conn) lets psycopg send a Vector as a parameter. The %s placeholders are psycopg's parameter markers, and its documentation says the query and the parameters are sent to the server separately and warns never to merge values into the query with % or +. The embedding_model = %s condition is the guard of case UW-R06 written in SQL: a query vector from another model matches no row and returns nothing, which is a failure you can see. encode_query is the one function this block does not define, on purpose: it is the embedding model, and nothing in the driver or in pgvector knows which model made the stored vectors. That knowledge lives in the embedding_model column only if you put it there.

This recipe hides nothing. Its weakness is the other side of that: every safety the framework might add, you write. The version default is a decision in code review, not in a library.

Recipe 3: LangChain with langchain-postgres#

LangChain's PostgreSQL integration lives in the package langchain-postgres, and on 2026-11-18 it has two vector store classes. The README of the package says that from v0.0.14 PGVector is deprecated and users should migrate to PGVectorStore. The LangChain documentation page titled "PGVector integration" still shows PGVector and does not mention the deprecation. Both statements are true on the same day, so this page shows both classes and says which source says what. (The old address python.langchain.com now redirects to docs.langchain.com/oss/.)

The first block uses PGVectorStore, the class the README recommends. It is assembled from the README and the PGVectorStore documentation page; it was not run here. The README shows synchronous calls (init_vectorstore_table, create_sync, similarity_search); the documentation page shows the asynchronous versions with k and filter and says that all async methods have corresponding sync methods. Passing k and filter to the synchronous similarity_search is that sentence applied, not a line either page shows.

# documented shape; not run here (langchain-postgres README + PGVectorStore page, read 2026-11-18)
from langchain_postgres import Column, PGEngine, PGVectorStore

engine = PGEngine.from_connection_string(url="postgresql+psycopg://rag:secret@localhost:5432/rag")
engine.init_vectorstore_table(
    table_name="guidance_lc",
    vector_size=384,                                          # the model's dimension; says nothing about the model
    metadata_columns=[Column("policy_version", "TEXT"), Column("status", "TEXT")],
)
store = PGVectorStore.create_sync(
    engine=engine,
    table_name="guidance_lc",
    embedding_service=embedding,                              # the embedding model, as an object
    metadata_columns=["policy_version", "status"],
)
docs = store.similarity_search(
    "roof replaced recently but aerial imagery shows an older roof",
    k=5,
    filter={"policy_version": {"$eq": "2026.2"}, "status": {"$eq": "current"}},
)

The second block uses PGVector, the class the documentation page shows. It is that page's documented shape with this site's names; it was not run here.

# documented shape; not run here (LangChain "PGVector integration" page, read 2026-11-18)
from langchain_postgres import PGVector

vector_store = PGVector(
    embeddings=embedding,
    collection_name="guidance",
    connection="postgresql+psycopg://rag:secret@localhost:5432/rag",   # psycopg 3 only
    use_jsonb=True,
)
docs = vector_store.similarity_search(
    "roof replaced recently but aerial imagery shows an older roof",
    k=5,
    filter={"policy_version": {"$eq": "2026.2"}},
)
retriever = vector_store.as_retriever(search_kwargs={"k": 5})
docs = retriever.invoke("roof replaced recently but aerial imagery shows an older roof")

Now ask the five questions of these blocks, from the pages read on 2026-11-18.

The embedding model is an object passed in (embedding_service, embeddings). The store calls it for documents and queries. The pages read do not mention recording which model made the stored vectors, so the UW-R06 guard is not something these pages describe; vector_size=384 checks the length, and the embeddings page shows why the length proves nothing about the space.

The distance metric is not shown in either example. The PGVectorStore page says a different distance_strategy can be used, and gives neither an example value nor the default. The PGVector page mentions neither. The pages read also do not say whether similarity_search_with_score returns a distance or a similarity. Until you read the code or the API reference, you do not know which of <->, <=> or <#> runs.

Where the filter runs is described only as syntax: a dictionary with operators such as $eq, $in, $between, $like, and $exists for PGVectorStore. PGVectorStore can keep chosen fields as dedicated columns (metadata_columns) and the rest in one JSON column; PGVector keeps metadata as JSON. Neither page says whether the filter is part of the same ORDER BY ... LIMIT query or applied afterwards. The general vector store page says metadata filtering support varies by implementation.

k is an argument. In the retriever version it moves into search_kwargs, and the PGVector page's own retriever example uses search_type="mmr" with k = 1, a different ranking from plain similarity. The page does not show a filter inside search_kwargs, so the block above leaves it out rather than guess; the version filter of the opening is exactly what a retriever built like this loses.

The SQL is not shown. The PGVector page does not describe its table layout and warns that a schema change in the vector store requires recreating the tables and re-adding the documents. PGVectorStore creates a table you name, with a UUID id by default and metadata in a JSON column unless you add columns; it can apply HNSWIndex or IVFFlatIndex, which turn the search approximate, and the page mentions hybrid search through HybridSearchConfig without detail.

Recipe 4: LlamaIndex with its PostgreSQL vector store#

LlamaIndex wraps the same table in a different set of objects. Its PostgreSQL integration is the package llama-index-vector-stores-postgres with the class PGVectorStore (the same class name as LangChain's, a different library). A VectorStoreIndex is built on the store, and a retriever comes from index.as_retriever(...) and runs with retriever.retrieve(query), returning a list of nodes. The block is assembled from the LlamaIndex Postgres integration page and its retriever guide, read on 2026-11-18; it was not run here.

# documented shape; not run here (LlamaIndex Postgres integration page, read 2026-11-18)
from llama_index.core import VectorStoreIndex
from llama_index.core.vector_stores.types import MetadataFilter, MetadataFilters
from llama_index.vector_stores.postgres import PGVectorStore

vector_store = PGVectorStore.from_params(
    database="rag", host="localhost", password="secret", port=5432, user="rag",
    table_name="guidance_li",
    embed_dim=384,
    hnsw_kwargs={"hnsw_m": 16, "hnsw_ef_construction": 64, "hnsw_ef_search": 40,
                 "hnsw_dist_method": "vector_cosine_ops"},
)
index = VectorStoreIndex.from_vector_store(vector_store=vector_store)
filters = MetadataFilters(filters=[MetadataFilter(key="policy_version", value="2026.2")])
retriever = index.as_retriever(similarity_top_k=5, filters=filters)
nodes = retriever.retrieve("roof replaced recently but aerial imagery shows an older roof")

The five questions again. The embedding model does not appear in the block at all. The integration page never sets one; its setup section configures an OpenAI key that "will be used to created embeddings", and its logs show calls to an OpenAI embeddings endpoint. The page does not say what happens when no model is configured. So the model that encodes the UW-R01 query is whichever default the library resolves, and embed_dim=384 checks only the length.

The metric is the one place this example is explicit: hnsw_dist_method set to vector_cosine_ops, which is cosine distance. The page states no default when it is left out. The filter is a MetadataFilter with operators such as >=, in, nin and contains, combined with "and" or "or"; the page does not say where it runs relative to the index scan. k is similarity_top_k; the pages read always pass it and state no default. The SQL is hidden behind table_name, and the page does not say whether the library adds a prefix to it.

One more thing hides here, and it changes the kind of search. hnsw_kwargs builds an HNSW index, so this retriever runs an approximate search, not the exact one of Recipe 1, and the page says hnsw_ef_search is 40 by default and can be raised per query through vector_store_kwargs. An approximate index with a selective filter can return fewer rows than asked, which is case UW-R09; on the throwaway database the same pattern returned zero rows until hnsw.iterative_scan was turned on (pgvector).

The four recipes side by side#

The table collects where each decision lives.

DecisionRaw SQL (Recipe 1)psycopg + pgvector-pythonLangChain (PGVectorStore / PGVector)LlamaIndex (PGVectorStore)
Embedding modelyour code; stored in embedding_model, guarded in WHEREyour code (encode_query); guard is your SQLan embedding object; model identity not recorded on the pages reada library default unless configured; the page's example uses OpenAI
Distance metricthe operator you write (<=>)the operator you writedistance_strategy, default not statedhnsw_dist_method in the example; default not stated
Where the filter runsin the WHERE, before the LIMITin the WHERE, before the LIMITa filter dict; placement not statedMetadataFilters; placement not stated
ka bound parametera bound parameterk or search_kwargssimilarity_top_k, default not stated
Exact or approximateexact (tie-breaker, no index used)whatever plan the SQL getsexact unless an index is appliedapproximate (HNSW, ef_search 40 by default)
The SQLon the screenon the screengenerated; table layout not describedgenerated; table name handling not stated
How checked hererun (recorded output)documented shape; not run heredocumented shape; not run heredocumented shape; not run here

The conclusion is not "avoid frameworks". Each framework column has at least one "not stated" cell, and each such cell is a question a team answers before the code ships: read the class's source or API reference, then run the framework version against the answer key of Recipe 1 and compare by passage id.

Production pitfall. A framework upgrade can change a default, or, as with PGVector, the class itself, without changing your code. Pin the version and compare the framework's top k for UW-R01 with the raw SQL top k on every upgrade, next to the labeled queries of evaluation.

How a hidden decision fails, case by case#

Each of the five hidden decisions has a case on this site that shows what goes wrong, and a check that catches it.

Hidden decisionWhat goes wrongCaseThe check
Embedding modela new query encoder against old stored vectors returns plausible, wrong rowsUW-R06store embedding_model per row; compare it on every query
Filter placementa version or tenant filter applied after top k leaks or starvesUW-R03, UW-R07count rows returned against k; test with a foreign-tenant row present
Exact or approximatea selective filter over an HNSW index returns fewer than k rowsUW-R09run the same query with index scans off and compare ids
ka candidate budget of 2 drops the rule before the model reads itUW-004 (debugger low_recall)log k per stage in a retrieval trace (pipelines)
Metricdot product on unnormalized vectors ranks long vectors first(vectors)assert the operator class of the index and the operator of the query agree

None of these failures raises an error, so each check compares with something you trust: the exact SQL, the labeled queries, or a count.

A checklist for framework code#

In a coding agent. A coding agent's code search is often a framework retriever too: an object that takes "find where we retry provider calls" and returns snippets. The same five questions apply: which model embedded the repository, whether the branch filter runs inside the search, and how grep results and vector results are fused. When the agent edits the wrong file, the answer is in those settings, not in the prompt.

The decision guide for the whole site#

The four recipes all assumed the guidance passages already lived in PostgreSQL. That choice, and every choice before it, is what the other 23 chapters are for. This section turns them into a guide. Start from the question you have today; the page named next to it holds the full answer, and each page recaps what it needs from earlier pages.

If your question isGo to
Do I need RAG at all, or a lookup?Retrieval
Why can't WHERE, LIKE or full-text search find a paraphrase?SQL first
How does a distance between two lists of numbers mean "similar"?Vectors
Why do two models with 384 dimensions not match?Embeddings
Which embedding model should we use?Choosing a model
Where should the vectors live: same row, side table, separate store?Storage
How do I write the nearest-neighbor query safely, with filters?Exact search
When do I need an index, and what does it miss?ANN indexes
Why does my pgvector query return fewer rows than asked?pgvector
Can this run on one machine with no server?SQLite
How should documents be cut into chunks?Chunking
Which stage dropped the passage the model needed?Pipelines
Why does vector search miss an exact rule id?Hybrid search
How do I order 50 candidates better?Reranking
What changes at 100,000 or a million rows?Top-k and scale
One table for applicants and decisions, or two?Two corpora
How do I make the answer cite, abstain, and let current policy win?Grounding
How do I know retrieval got better?Evaluation
Which Google Cloud product does what?Google Cloud
How do I read a vector-database comparison chart?Ecosystems
How do I change the model, delete a document everywhere, or isolate tenants?Operations
What are HyDE, parent-child chunks, late interaction, graph RAG?Advanced
What does a whole design look like?Architectures
What does a framework hide?this page, above

Storage is the decision teams most want a rule of thumb for, and the one where a rule of thumb does the most damage. "Past N rows you need a vector database" is the sentence this site refuses to write, because the row count is rarely what decides: scale showed that selectivity, update rate and memory decide plans, and that the same table can be served exactly or through an index depending on its filters. The flow below asks the questions in the order that removes options fastest. The first questions are about constraints you cannot measure away; the last one is about measurement.

Diagram: Choosing where vectors live: constraints first, responsibilities second, measurement last

Read left to right. The first lane removes options you cannot measure away: where the data must stay and whether vectors must change in the same transaction as the facts. The second lane decides who does the work. The third lane is measurement on your own labeled queries. No box mentions a row count.

  1. 1. Constraints that rule options out
    1. Where must the data stay?: Residency and privacy rules remove options before any benchmark is worth running.
    2. Change with the facts?: If a passage's status and its vector must change together, vectors belong in the database that holds the facts.
  2. 2. Who does which work
    1. Who chunks and embeds?: A managed pipeline takes over chunking and embedding, and with them your control over both.
    2. Who runs the servers?: A team that already runs PostgreSQL, a managed service, or an in-process library.
  3. 3. Measure on your queries
    1. Labeled queries, real filters: Run your labeled queries with your filters against exact search and record recall, latency and memory.
    2. Targets met?: If the current store meets the targets, the decision is made whatever the row count; if not, change one thing and measure again.

The order matters more than any box. Data residency and where the system must run come first, because they rule options out before any benchmark is worth running; Mnemos keeps everything in one SQLite file on the user's machine for that reason (SQLite). Whether the vectors must change in the same transaction as the facts they describe comes second, because a policy revision that updates a passage's status and its vector together is what keeps case UW-R03 from returning (storage). Whether chunking and embedding should be the service's job comes third. Only then comes the measured question: does the current store meet recall, latency and memory targets on your labeled queries with your filters? If it does, the decision is made, whatever the row count.

The lab below applies that flow to six options read from documentation. Tick the conditions of your project; each row is marked as fitting, not fitting or unknown for that condition, with the reason in its cell. An unknown is a question the sources did not answer, never a pass. The table after the lab holds the same information for a reader without JavaScript, and the sources are linked in each row.

OptionWhat it isStores vectors whereFilteringHybridUpdatesWho runs itSource
PostgreSQL with pgvector, run by your teamAn extension that adds a vector type, distance operators and HNSW and IVFFlat indexesA vector column in your own rowsSQL WHERE; with an approximate index the filter is applied after the scan; iterative scans since 0.8.0You write it: full-text search plus RRF in SQL (ran as Q7 here)INSERT, UPDATE, DELETE in transactions (Q14 here)Your team runs the serverdocs
Cloud SQL for PostgreSQL with pgvectorManaged PostgreSQL; pgvector 0.8.5 on PostgreSQL 13 and laterA vector column in your own rowsSQL WHERE, as pgvectorYou write it, as pgvectorSQL writes in transactionsManaged; private IP via private services accessdocs
SQLite with sqlite-vec (vec0)A loadable extension; a vec0 virtual table, L2 distance by defaultA virtual table beside your tables, joined by row idMetadata columns usable in the KNN WHERE (up to 16); partition keysNot stated in the sources read for this rowNot stated in the sources read for this rowNo server: SQLite is a library inside your programdocs
SQLite with a registered function (standard library)A distance function registered with create_function; exact search over a BLOB columnA BLOB column in your own rowsSQL WHERE in the same query (run here)Not part of the exampleEvery row is compared on every query, so a new row is searchable at once (run here)No server: SQLite is a library inside your programdocs
Vector Search (Google Cloud)Approximate index as a service, built on ScaNNA deployed index; text and metadata elsewhereToken and numeric restrictsDense plus sparse in one index, RRFBatch updates, or streaming upserts on a streaming indexManaged; private endpoint or Private Service Connectdocs
RAG Engine (Google Cloud)Managed retrieval pipeline: ingest, chunk, embed, index, retrieveRagManagedDb (Spanner) by default, or another listed storeNot mentioned on the pages readNot mentioned on the pages readImport, upload, delete filesManageddocs

Read the table for its blanks as much as its cells. No row fits every condition, and a row with many fits is only a row whose documentation answered many questions. An unknown is the next thing to read or to test. The full product descriptions, with every source, are on Google Cloud and Ecosystems.

When a framework earns its place, and when a thin repository layer is betterCompeting designs: the framework's abstraction, or your own twenty lines over SQL

A vector store abstraction is a common interface over many engines: add documents, delete, search by similarity with k and a filter. Its value is portability and speed of first build. The same similarity_search call can run against PostgreSQL in one deployment and another engine in a test, and the framework brings document loaders, splitters and model clients that a team would otherwise write.

The cost is that the interface is the lowest common denominator of what its engines document. The LangChain vector store page says metadata filtering support varies by implementation; a filter that runs inside the search on one engine may run after it on another, behind the same call. For a corpus whose filters are permission boundaries (tenant, ACL) or authority boundaries (current policy version), that variation is the risk.

A competing design is a thin repository layer of your own: one function per retrieval, such as search_guidance(conn, query_vec, model, version, k), whose body is the SQL of Recipe 2. It is about twenty lines per corpus, it has no defaults you did not write, and its plan can be read with EXPLAIN ANALYZE. Teams often use both: the framework for ingestion (loading, splitting, embedding) and their own query functions for retrieval, writing to tables they designed. The decision is per corpus. A prototype over public documents tolerates hidden defaults; the precedent corpus with its tenant filter does not.

Moving from PGVector to PGVectorStore is a storage migrationA deprecation in a library becomes a data change in your database

The langchain-postgres README deprecates PGVector for PGVectorStore from v0.0.14 and points to a migration notebook (examples/migrate_pgvector_to_pgvectorstore.ipynb, which was not read for this page). The two classes are documented with different table setups: PGVector with a named collection and JSON metadata, PGVectorStore with a table you initialize, optional dedicated metadata columns and a JSON column for the rest.

That makes the class change a storage-layout migration in the sense of storage and operations, not a code refactor. Treat it as one: build the new table beside the old, copy or re-add the documents, run the labeled queries against both, switch reads, keep the old table until the comparison holds. Whether the stored vectors can be copied rather than recomputed depends on keeping the same embedding model and preprocessing; if either changes in the same release, it is also a re-embedding, and the UW-R06 guard is what tells you. The PGVector page's own warning, that any schema change requires recreating the tables and re-adding the documents, is the reason to plan this as a migration with a rollback rather than an upgrade.

Filter dictionaries are an injection surface and a permission boundaryWho builds the filter matters more than which syntax it uses

Recipe 2 shows the database rule: values travel as bound parameters, never as text in the statement. A framework's filter dictionary is translated into SQL by the library; the pages read describe the operators but not the translation, so the guarantee that values are bound is in the library's code, not in its documentation. Read it once for the operators you use, in particular $like and $ilike, whose values are patterns.

The larger risk is not the syntax but the author of the filter. If the model's output, or text retrieved from a corpus, can shape the filter dictionary, a passage such as the injected sentence of case UW-005 could try to widen it. The tenant and version conditions of the precedent and guidance corpora must be set by application code from the session and the policy fixture, outside anything the model writes, and a test must prove that a book-west precedent never reaches a book-east query (UW-R07). Defense in depth for injected text belongs to guardrails.sammuthu.com; the retrieval-side rule is that a filter is code, not a prompt.

Seeing the query under a retrieverThree ways to recover the five decisions from running code

When the documentation does not state a default, the running system still knows it. Three ways recover it. First, the database: turn on statement logging for a test database, run the retriever once, and read the SQL it sent; then run that SQL with EXPLAIN ANALYZE to see whether an index scan or a sort ran (pgvector). Second, the library: read the source of the class's search method for the metric, the filter translation and the default k, and note the version you read. Third, the result: compare the framework's ids for UW-R01 with the raw SQL answer key; equal ids and equal order mean the decisions agree for this query, and a difference points at one of the five.

Whichever you use, keep the finding next to the code, as a comment with the library version, so that the next upgrade has something to compare with. A retrieval trace around the call (pipelines) records k, the filter and the returned ids per query in production, which turns a disputed recommendation into a lookup instead of an investigation.

In one breath#

Every retrieval, in any library, decides five things: the embedding model, the distance metric, where the filter runs, k, and the SQL that executes. Raw SQL and direct driver code show all five; LangChain's and LlamaIndex's PostgreSQL stores move them into constructor arguments and defaults, several of which their pages read on 2026-11-18 do not state, and LangChain's README deprecates the PGVector class that its own integration page still shows. Keep an exact SQL query as the answer key, check each framework's result against it by id, and choose storage by constraints first and measurement last, never by row count.

This page ends the chapters. The agent's guidance search for UW-R01 can now be written four ways and checked against one answer key, and every page of the site is one row in the guide above. For a term met anywhere on the site, the glossary has its plain meaning and where it appears in the example; for where the facts came from and how the code and SQL were checked, see about.