RAG in Python · Chapter 16 of 37
Indexes that guess
By the end of this chapter you can build HNSW and IVFFlat indexes on a vector column, tune how hard they search, and measure with recall how often their fast answer is the right one. 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
- 5 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
Kabir wants two years of resolved support tickets searchable next to the help centre: about 100,000 chunks. Chapter 15's search reads every row and sorts them all. On 866 chunks that is nothing; on 100,000 it is tens of milliseconds of several CPU cores per search, and the assistant runs several searches per question while support agents search as they type.
A vector index answers in about a millisecond by not reading every row. The catch: it guesses. Unlike a B-tree on an id, a vector index can miss the true nearest chunk, and nothing in the result tells you. You need the settings that trade speed for accuracy, and a number for the accuracy.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
indexes.py: exact_search, the true top k with the index off
Write exact_search(conn, table, vector, k) in indexes.py: the ids of the k rows of table nearest to vector by cosine distance (<=>), best first, exactly. Inside with conn.transaction(), run SET LOCAL enable_indexscan = off, then the query with the vector and k as parameters (and prepare=False, so psycopg never reuses a plan made for another setting), and return the ids.
The check gives the table a weak HNSW index: if your query uses it, you get its guess instead of the true top k. Afterwards the connection's enable_indexscan must still be on (a plain SET would switch the index off for every later query on it), and no transaction may be left open.
- Task 2
indexes.py: create_hnsw, build an HNSW index
Write create_hnsw(conn, table, m, ef_construction) in indexes.py: drop the index named table + "_hnsw" if it exists, then build it: CREATE INDEX on table USING hnsw (embedding vector_cosine_ops) WITH (m = m, ef_construction = ef_construction). Return a Built: ms is how long the CREATE INDEX took in milliseconds (time.perf_counter before and after), bytes is pg_relation_size of the index.
create_hnsw(conn, table, 8, 32) leaves an index whose options are m=8 and ef_construction=32; a second call with 4 and 10 replaces it.
- Task 3
indexes.py: ann_search, ef_search for one query
Write ann_search(conn, table, vector, k, ef_search) in indexes.py: the ids of the k nearest rows, best first, the way the HNSW index finds them with hnsw.ef_search set for this one query: inside with conn.transaction(), SELECT set_config('hnsw.ef_search', the value as text, true) (or SET LOCAL), then the same query as exact_search, with prepare=False.
The index returns at most ef_search rows, so use the larger of ef_search and k: ann_search(conn, table, v, 60, 20) returns 60 ids, not 20. Do not switch index scans off. Afterwards the connection's hnsw.ef_search is still 40, the default.
- Task 4 · interview question
Interview: indexes.py, recall_at, recall against the exact scan
Write recall_at(conn, table, queries, k, ef_search) in indexes.py: for each query vector, take the exact top k (exact_search) and the index's top k (ann_search with ef_search). Its recall is how many of the exact ids the index returned, divided by k. Return the average over all queries, and 0 for an empty list.
The check compares your number with its own on a weak index: recall at ef_search 10 is clearly lower than at 200.
Arjun will ask: how do you know the index is good enough, and what does a bigger ef_search cost you?
- Task 5
indexes.py: create_ivfflat, the right number of lists
Write create_ivfflat(conn, table) in indexes.py: count the rows of table. lists is rows / 1000 rounded, at least 1, up to a million rows, and the square root of rows (rounded) above that. Drop the index named table + "_ivf" if it exists, build it USING ivfflat (embedding vector_cosine_ops) WITH (lists = lists), and return lists. 3,000 rows give 3 lists; 400 rows give 1.
IVFFlat learns its lists from the rows already in the table, so on an empty table raise ValueError whose message says to load the data first, and build nothing.
What you will learn
- The exact scan: what Postgres does without an index, in EXPLAIN ANALYZE.
- HNSW: a graph of neighbours, with m, ef_construction and ef_search.
- IVFFlat: vectors sorted into lists, with lists and probes.
- Recall: the share of the true top k an index finds, measured against the exact scan.
More in Vectors in Postgres
Build "Indexes that guess" 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.