RAG: search that answers · 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 in a few statements, and find the nearest chunks to a question with one SQL query from TypeScript. 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
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. What you know so far (vectors in an array, a loop that scores them) does not move into the database by itself: you need the type, a way to send vectors from TypeScript, 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
createTable: a table with a vector column
Run CREATE EXTENSION IF NOT EXISTS vector, drop TABLE (my_chunks) 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 createTable twice must work and leave an empty table.
- Task 2
insertChunks: many rows per statement, with parameters
Insert rows into TABLE with batchSize rows per INSERT, and return how many rows were inserted (add up each result's rowCount). insertChunks(db, rows, 100) with the 866 chunks sends 9 INSERT statements and returns 866; 7 rows in batches of 3 is 3 statements. With no rows, send nothing and return 0.
Every value goes in the parameter list: the six text columns as they are, and the embedding through pgvector.toSql. Row k of a batch uses parameters k * 7 + 1 to k * 7 + 7. The SQL text holds only the column names and the placeholders, never a value: a chunk with an apostrophe would break it.
- Task 3
search: the nearest chunks, in SQL
Return the k rows of TABLE nearest to vector, best first, as { id, similarity }, with one query. similarity is the cosine similarity: 1 minus the cosine distance, the <=> operator. Pass the vector (through pgvector.toSql) and k as parameters.
For "BT error on UPI, what should i tell the customer", search(db, 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.
- Task 4 · interview question
Interview: neighbours, near copies in one query
Return the k chunks of TABLE most like the chunk with this id, best first, as { id, similarity } (cosine similarity, as in search), never the chunk itself. Use one query: join TABLE to itself, so the chunk's embedding stays in Postgres and is never fetched into Node.
neighbours(db, 'help-accept-upi-payments#1', 2) is help-upi-payments-guide#1 (similarity 0.9624), then help-upi-error-u69#2. An id that is not in the table returns [].
Arjun will ask: Kabir says two help articles repeat each other. How do you find near copies without pulling every vector into Node?
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 TypeScript: pgvector.toSql and registerTypes with the pg package, and every value as a parameter.
- Batches: many rows per INSERT, and the limit on how many.
More in Vectors in Postgres
Build "Vectors in Postgres" 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.