2.2.4. Vector Search and RAG Patterns

💡 First Principle: A pgvector RAG query is ordinary SQL with one exotic ingredient: an ORDER BY clause that sorts by distance to a query vector. Everything you already know about SQL — WHERE filters, LIMIT, joins — composes with it. That composability is the RAG pattern.

pgvector exposes three distance operators, and each must match the operator class its index was built with:

The exam's canonical query — semantic retrieval with a metadata filter, straight from the syllabus bullet:

SELECT content, metadata,
       embedding <=> %(qvec)s AS distance
FROM chunks
WHERE metadata @> '{"department": "billing"}'
ORDER BY embedding <=> %(qvec)s
LIMIT 5;

The full request cycle, connecting back to the RAG flow in 1.1.2:

The metadata filter isn't decoration — it's the difference between "nearest five chunks in the whole corpus" and "nearest five chunks the user is allowed to see." Filters implement tenancy, freshness, and access control inside retrieval. Beyond pure vector search, PostgreSQL supports hybrid retrieval: combine full-text search (tsvector) with vector ranking so exact keywords and semantic similarity both contribute — the standard fix when pure semantic search misses jargon and product codes.

⚠️ Exam Trap: Querying with <-> when the index was built with vector_cosine_ops (or vice versa) silently bypasses the index — results come from an exact sequential scan: correct, but mysteriously slow. If a scenario says "we added an index but queries didn't speed up," check operator/opclass mismatch before anything else.

Hybrid retrieval deserves one implementation note: full-text and vector scores live on different scales, so naive addition lets one signal drown the other. The standard remedies are reciprocal rank fusion (combine the two result rankings rather than raw scores) or normalizing ts_rank and distance into comparable ranges before weighting. Exam scenarios stay at the pattern level, but "combine the rankings, not the raw scores" is the phrase to remember when a hybrid question mentions one signal dominating results.

Reflection Question: Why should a multi-tenant RAG system enforce tenancy in the SQL WHERE clause rather than filtering the model's answer afterward?