RAG: search that answers · Chapter 17 of 37
Search with conditions
By the end of this chapter you can combine a vector search with SQL conditions (audience, document type, the dates a version applies) without losing results, and know when Postgres applies them inside the index walk and when after it. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Vectors in Postgres
- 4 tasks
- In the all labs plan
The first 2 chapters of RAG: search that answers are free. This one comes with the all labs plan.
Why you need this
Neha has two rules for the assistant. A merchant must never be shown an internal runbook, not even as a passage the model reads. And an answer about settlements must come from the policy in effect: Paisa Setu moved from T+2 to the next working day on 1 March 2026, and the old 2024 documents are still in the table, because support needs them for old disputes.
Both are conditions on columns you already have (audience, effective_from, effective_to, type). The obvious code searches first and filters in TypeScript afterwards. It runs, it passes a quick test, and on the wrong question it returns almost nothing.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
merchantSearch: merchants never see internal chunks
The starter searches the 5 nearest chunks first and removes internal ones in TypeScript afterwards: for "what's the difference between SEV1, SEV2 and SEV3?" all 5 are runbooks, so it returns nothing. Put audience = 'merchant' in the SQL instead, and return the k nearest merchant chunks of the chunks table, best first, as { id, similarity } (1 minus the cosine distance).
For every labelled question, merchantSearch(db, vector, 5) returns exactly 5 merchant chunks, the same as an exact search among merchant chunks, and never an internal one.
- Task 2
asOf: the documents in effect on a date
Return the k nearest merchant chunks in effect on date (a 'YYYY-MM-DD' string, passed as a parameter), best first, as { id, similarity }. A chunk is in effect when effective_from is NULL or on or before the date, and effective_to is NULL or on or after it: effective_to is the last day a version applies.
asOf(db, vector of q39, '2026-10-09', 5) has no help-settlement-timeline-2024 chunk; on '2026-01-15' it has help-settlement-timeline-2024#2 and nothing from help-settlement-timeline (in effect from 2026-03-01). Chunks without dates are always in effect: a comparison with NULL is never true, so allow NULL on purpose.
- Task 3
policySearch: policies only, through the index
Return the k nearest chunks with type = 'policy', best first, as { id, similarity }, in a way that works when Postgres walks the HNSW index: BEGIN, SET LOCAL hnsw.iterative_scan = strict_order, the query, COMMIT.
The Check makes Postgres use the index (as it would on a big table). Without the iterative scan, the index keeps its 40 best candidates and the WHERE runs after: for "what does UPI error U30 mean?" none is a policy, so you would get 0 rows. With it, every labelled question gets 5 policy chunks in order, and the connection's hnsw.iterative_scan is still off afterwards.
- Task 4 · interview question
Interview: createMerchantIndex, an index of merchant chunks
Drop chunks_merchant_hnsw if it exists, then create it: an HNSW index on chunks (embedding vector_cosine_ops), partial, WHERE audience = 'merchant'. A search with WHERE audience = 'merchant' can then use it, and its graph holds no internal chunk, so even without an iterative scan a merchant search for the SEV question returns 5 rows.
Arjun will ask: 95% of searches are merchants'. How do you keep them fast, and make sure the index never even walks an internal runbook?
What you will learn
- Filtering after the search: why the top 5 can become 1, or 0.
- Filtering in the query: a WHERE next to the ORDER BY, and NULL dates.
- Filters and HNSW: why the index can still return too few rows, and pgvector 0.8's iterative scans.
- Partial indexes: an index that holds only the rows one kind of search needs.
More in Vectors in Postgres
Build "Search with conditions" yourself
Paisa Setu, a payments company, needs its help centre to answer merchants correctly. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of RAG: search that answers are free. This one comes with the all labs plan.