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.
- Raw SQL: you write each decision
- Embedding model: The query is encoded by your code with the stored rows' model, and the model name is compared in the query.
- Distance metric: The operator in ORDER BY decides the metric: cosine distance, L2 or negative inner product.
- Filter placement: Exact conditions sit in the same query, so they hold before the nearest rows are counted.
- k: How many rows come back is a parameter you can see and log.
- Join back: Each match leads to its source row: here the vector is in the row, so the match is the row.
- A retriever call: the decisions are inside
- Query text in: The caller passes text; nothing on the call line names a metric, a filter or a table.
- Five decisions inside: Model object, distance strategy, filter, k and table were set when the store was built, or left to defaults.
- 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 methodfindByStatusOrderByCreatedAtDeschides aSELECT, 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.
| Decision | Raw SQL (Recipe 1) | psycopg + pgvector-python | LangChain (PGVectorStore / PGVector) | LlamaIndex (PGVectorStore) |
|---|---|---|---|---|
| Embedding model | your code; stored in embedding_model, guarded in WHERE | your code (encode_query); guard is your SQL | an embedding object; model identity not recorded on the pages read | a library default unless configured; the page's example uses OpenAI |
| Distance metric | the operator you write (<=>) | the operator you write | distance_strategy, default not stated | hnsw_dist_method in the example; default not stated |
| Where the filter runs | in the WHERE, before the LIMIT | in the WHERE, before the LIMIT | a filter dict; placement not stated | MetadataFilters; placement not stated |
| k | a bound parameter | a bound parameter | k or search_kwargs | similarity_top_k, default not stated |
| Exact or approximate | exact (tie-breaker, no index used) | whatever plan the SQL gets | exact unless an index is applied | approximate (HNSW, ef_search 40 by default) |
| The SQL | on the screen | on the screen | generated; table layout not described | generated; table name handling not stated |
| How checked here | run (recorded output) | documented shape; not run here | documented shape; not run here | documented 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 withPGVector, the class itself, without changing your code. Pin the version and compare the framework's top k forUW-R01with 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 decision | What goes wrong | Case | The check |
|---|---|---|---|
| Embedding model | a new query encoder against old stored vectors returns plausible, wrong rows | UW-R06 | store embedding_model per row; compare it on every query |
| Filter placement | a version or tenant filter applied after top k leaks or starves | UW-R03, UW-R07 | count rows returned against k; test with a foreign-tenant row present |
| Exact or approximate | a selective filter over an HNSW index returns fewer than k rows | UW-R09 | run the same query with index scans off and compare ids |
| k | a candidate budget of 2 drops the rule before the model reads it | UW-004 (debugger low_recall) | log k per stage in a retrieval trace (pipelines) |
| Metric | dot 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#
- Write the raw SQL version of every retriever first and keep it in the test suite as the answer key.
- For each retriever, write down the five decisions with the line of code or documentation that sets each one.
- Read the class's API reference for every default the integration page does not state (metric, k, table layout).
- Store the embedding model's name and version per row, and check it on the query side, framework or not.
- Put version, status and tenant conditions in the filter the framework sends to the database, and test that a foreign-tenant row never appears.
- If the framework builds an HNSW or IVFFlat index, measure index recall against the exact query on realistic filters.
- Log a retrieval trace around the call: k, filter, rows returned, ids.
- Pin the framework version; rerun the comparison on every upgrade.
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 is | Go 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. Constraints that rule options out
- Where must the data stay?: Residency and privacy rules remove options before any benchmark is worth running.
- 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. Who does which work
- Who chunks and embeds?: A managed pipeline takes over chunking and embedding, and with them your control over both.
- Who runs the servers?: A team that already runs PostgreSQL, a managed service, or an in-process library.
- 3. Measure on your queries
- Labeled queries, real filters: Run your labeled queries with your filters against exact search and record recall, latency and memory.
- 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.
| Option | What it is | Stores vectors where | Filtering | Hybrid | Updates | Who runs it | Source |
|---|---|---|---|---|---|---|---|
| PostgreSQL with pgvector, run by your team | An extension that adds a vector type, distance operators and HNSW and IVFFlat indexes | A vector column in your own rows | SQL WHERE; with an approximate index the filter is applied after the scan; iterative scans since 0.8.0 | You write it: full-text search plus RRF in SQL (ran as Q7 here) | INSERT, UPDATE, DELETE in transactions (Q14 here) | Your team runs the server | docs |
| Cloud SQL for PostgreSQL with pgvector | Managed PostgreSQL; pgvector 0.8.5 on PostgreSQL 13 and later | A vector column in your own rows | SQL WHERE, as pgvector | You write it, as pgvector | SQL writes in transactions | Managed; private IP via private services access | docs |
| SQLite with sqlite-vec (vec0) | A loadable extension; a vec0 virtual table, L2 distance by default | A virtual table beside your tables, joined by row id | Metadata columns usable in the KNN WHERE (up to 16); partition keys | Not stated in the sources read for this row | Not stated in the sources read for this row | No server: SQLite is a library inside your program | docs |
| SQLite with a registered function (standard library) | A distance function registered with create_function; exact search over a BLOB column | A BLOB column in your own rows | SQL WHERE in the same query (run here) | Not part of the example | Every row is compared on every query, so a new row is searchable at once (run here) | No server: SQLite is a library inside your program | docs |
| Vector Search (Google Cloud) | Approximate index as a service, built on ScaNN | A deployed index; text and metadata elsewhere | Token and numeric restricts | Dense plus sparse in one index, RRF | Batch updates, or streaming upserts on a streaming index | Managed; private endpoint or Private Service Connect | docs |
| RAG Engine (Google Cloud) | Managed retrieval pipeline: ingest, chunk, embed, index, retrieve | RagManagedDb (Spanner) by default, or another listed store | Not mentioned on the pages read | Not mentioned on the pages read | Import, upload, delete files | Managed | docs |
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.