Use SQL for RAG retrieval¶
SQL is often treated as storage plumbing around the “real” RAG system. In BuildRAG, SQL is one of the retrieval languages.
It can answer table questions exactly, filter eligible Sources before ranking, search words with PostgreSQL, compare vectors with pgvector, and join every result back to the file or record it came from.
Start with filters, not similarity¶
Assume sources stores visibility and metadata while chunks stores text, locations, full-text vectors, and embeddings.
A Query should only rank Chunks the current request may access:
SELECT c.id, c.source_id, c.text, c.start_line, c.end_line
FROM chunks AS c
JOIN sources AS s ON s.id = c.source_id
WHERE s.visibility = %(visibility)s
AND (%(region)s IS NULL OR s.metadata->>'region' = %(region)s);
Use psycopg parameters as shown. Do not build this clause by concatenating user text.
I put filters first because top-K is a lossy operation. If you rank every Source and remove private results later, good public Chunks may already have been pushed out of the candidate set.
Rank lexical matches¶
PostgreSQL can create a Query and rank matching text in the same statement:
SELECT c.id,
ts_rank_cd(c.search_vector, query) AS score
FROM chunks AS c
JOIN sources AS s ON s.id = c.source_id,
websearch_to_tsquery('english', %(query)s) AS query
WHERE s.visibility = %(visibility)s
AND c.search_vector @@ query
ORDER BY score DESC
LIMIT %(limit)s;
websearch_to_tsquery gives user-friendly phrase and exclusion syntax. If your application needs stricter behavior, build the tsquery yourself from validated terms.
Rank vectors over the same eligible Sources¶
SELECT c.id,
c.embedding <=> %(query_embedding)s AS distance
FROM chunks AS c
JOIN sources AS s ON s.id = c.source_id
WHERE s.visibility = %(visibility)s
ORDER BY distance
LIMIT %(limit)s;
These two queries return the same stable Chunk IDs, which makes Reciprocal Rank Fusion, or RRF, straightforward in Python. You can also combine the queries inside SQL, but I would keep the first version in Python until you have tested the ranking behavior.
Use ordinary SQL for ordinary facts¶
For a plan limit, skip full-text and vector ranking:
SELECT included_build_minutes
FROM plans
WHERE lower(plan) = lower(%(plan)s);
The result is deterministic, cheap, and easy to cite back to the plan record.
Inspect the query plan¶
Use EXPLAIN (ANALYZE, BUFFERS) on representative Queries. Check that visibility and metadata conditions run where you expect and that indexes are used as the collection grows.
SQL keeps the retrieval policy visible. You can read which Sources are eligible, which operator produced the score, and where top-K happens. Preserve that visibility even if you later wrap the query in a repository function.