RAG in Python · Chapter 15 of 37
Vectors in Postgres
By the end of this chapter you can store embeddings in a Postgres table, insert thousands of them quickly and safely, and find the nearest chunks to a question with one SQL query from Python. 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
Search in memory works on a laptop. In production it falls apart: Paisa Setu runs three app servers, so each one loads all 866 vectors at start, and when Kabir's team fixes a help article every copy is out of date until every server restarts. Merchants, payments and settlements already live in Postgres, with backups, transactions and access rules. Neha wants the chunks there too: "One copy, in the database we already audit."
pgvector adds a vector type and distance operators to Postgres. The chunks, their metadata and their embeddings live in one table, and the search is a query. Your numpy matrix from chapter 7 does not move into the database by itself: you need the type, a way to send vectors from Python, and a way to insert 866 rows that is not 866 round trips.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
store.py: create_table, a table with a vector column
Neha wants the chunks in Postgres. Write create_table(conn, table) in store.py: create the vector extension if it does not exist, drop the table if it exists, and create it with these columns: id text, the primary key; doc_id, title, audience and content, text and NOT NULL; heading, text; and embedding, vector(384) and NOT NULL.
lab-embed's vectors have 384 numbers: a column of type vector with no dimension is not enough. Calling create_table twice must work and leave an empty table.
- Task 2
store.py: insert_chunks, every row with parameters
Write insert_chunks(conn, rows, table) in store.py: insert every Row into the table and return how many you inserted (0 for an empty list). Use a cursor's executemany with one INSERT and a list of parameter tuples (or COPY): the check fails one execute per row, a round trip each.
Every value goes in the parameters: the six text fields as they are and the embedding as its numpy array. The SQL text holds only the table and column names and %s placeholders, never a value: the chunks with an apostrophe must come back exactly as they went in.
- Task 3
store.py: search, the nearest chunks in SQL
Write search(conn, vector, k, table) in store.py: the k rows nearest to vector, best first, as Hits (id, similarity), with one query. similarity is the cosine similarity: 1 minus the cosine distance, the <=> operator. Pass the vector and k as parameters.
For "BT error on UPI, what should i tell the customer", search(conn, vector, 5) returns 5 hits and the first is help-failed-payments#2. The check compares your ids and similarities with an exact search over every chunk, and counts your statements.
- Task 4 · interview question
Interview: store.py, neighbours, near copies in one query
Write neighbours(conn, doc_id, table) in store.py: for every chunk of that document, its nearest chunk in another document, as Copies (id, nearest, similarity: cosine, as in search), the most similar first. Use one query: no vector is fetched into Python.
neighbours(conn, "help-upi-payments-guide")[0] is help-upi-payments-guide#1, whose nearest is help-accept-upi-payments#1 at 0.9624. A chunk of the same document is never its nearest, and a document that is not in the table gives [].
Arjun will ask: Kabir says two help articles repeat each other. How do you find near copies without pulling every vector into Python?
What you will learn
- The vector type: a column of fixed-length vectors, vector(384).
- Distance operators: <->, <#> and <=>, and which one fits normalised embeddings.
- Vectors from Python: register_vector with psycopg, numpy arrays as parameters, and never a value in an f-string.
- Bulk inserts: one statement per row, executemany and COPY, timed.
More in Vectors in Postgres
Build "Vectors in Postgres" 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.