RAG in Python · 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 you 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
  • Python
  • In the all labs plan

The first 2 chapters of RAG in Python 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 force: 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 the chunks table already has (audience, type, effective_from, effective_to). The obvious code searches first and filters in Python afterwards. It runs, it passes a quick test, and on the wrong question it returns almost nothing.

Every example here searches the chunks table that lab index builds (paisa.build_index: every chunk with its embedding and an HNSW index, what chapters 15 and 16 built by hand). Run lab index once first.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    filters.py: merchant_search, merchants never see internal chunks

    Neha's first rule. The starter's merchant_search searches the 5 nearest chunks first and removes internal ones in Python 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 table, best first, as Hit(id, similarity), where similarity is 1 minus the cosine distance.

    For every labelled question, merchant_search(conn, vector, 5, table=...) returns exactly 5 merchant chunks, the same as an exact search among merchant chunks, and never an internal one. k says how many.

  2. Task 2

    filters.py: as_of, the documents in force on a date

    Write as_of(conn, vector, day, k=5, table): the k nearest merchant chunks in force on day (a datetime.date, passed as a parameter), best first, as Hit(id, similarity). A chunk is in force when effective_from is NULL or on or before the day, and effective_to is NULL or on or after it: effective_to is the last day a version applies.

    as_of(conn, vector of q39, date(2026, 10, 9), 5) has no help-settlement-timeline-2024 chunk; on date(2026, 1, 15) it has help-settlement-timeline-2024#2. Chunks without dates are always in force: a comparison with NULL is never true, so allow NULL on purpose. The check also plants a dated internal runbook: it must never come back.

  3. Task 3

    filters.py: policy_search, policies only, through the index

    Write policy_search(conn, vector, k=5, table): the k nearest chunks with type = 'policy', best first, as Hit(id, similarity), in a way that works when Postgres walks the HNSW index: inside one transaction, SET LOCAL hnsw.iterative_scan = strict_order, then the query.

    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?" one policy chunk comes back. With it, every labelled question gets 5 policy chunks, best first, at least 95% of them the exact 5, and afterwards the connection's hnsw.iterative_scan is off again and no transaction is left open.

  4. Task 4 · interview question

    Interview: filters.py, create_merchant_index

    Write create_merchant_index(conn, table="chunks"): drop the index named f"{table}_merchant_hnsw" if it exists, then create it: an HNSW index on the table's embedding with 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. Calling it twice works.

    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, with sources. 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 in Python are free. This one comes with the all labs plan.