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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.