Persist retrieval with PostgreSQL and pgvector¶
Your brute-force retriever works, but it keeps vectors in Python memory and compares every Chunk on every Query. That is fine for a lesson-sized collection. It is awkward once ingestion and retrieval run in different processes or the collection grows.
We will move the same data into PostgreSQL, a database server that stores information in tables and lets us search it with SQL. I chose PostgreSQL because it can keep Source details, structured records, full-text search, and vectors in one inspectable place.
pgvector is a PostgreSQL extension. An extension adds a feature to the database. pgvector adds a vector column type and operators for comparing vectors.
Create the tables¶
The SQL below creates two tables. A table stores records in named columns, much like a CSV with stricter rules. Connect to a PostgreSQL database with pgvector installed, then run:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE sources (
id text PRIMARY KEY,
path text NOT NULL UNIQUE,
content_hash text NOT NULL,
visibility text NOT NULL,
metadata jsonb NOT NULL DEFAULT '{}'
);
CREATE TABLE chunks (
id text PRIMARY KEY,
source_id text NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
text text NOT NULL,
start_line integer NOT NULL,
end_line integer NOT NULL,
heading text,
embedding vector(384),
search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', text)) STORED
);
Use the vector dimension produced by your embedding model. The local model from the previous lesson produces 384 values.
Notice what sits beside the vector: the stable IDs, Source relationship, text, heading, and line range. If you drop those details, you lose the record of where the vector came from and cannot produce a useful Citation.
With the data in PostgreSQL, our first goal is not speed. I want to reproduce the exact ranking from Python so we know the move did not change retrieval.
Run exact search first¶
With psycopg, pass the Query vector and visibility as parameters:
SELECT c.id, c.source_id, c.text,
c.embedding <=> %(query_embedding)s AS distance
FROM chunks AS c
JOIN sources AS s ON s.id = c.source_id
WHERE s.visibility = %(visibility)s
ORDER BY distance
LIMIT %(limit)s;
The <=> operator calculates cosine distance, so smaller values rank first. Compare these results with the Python brute-force baseline. They should agree before you add an approximate index.
The visibility condition belongs inside the query. Top-K means the first K results, where K is the number you choose to return. If you retrieve top-K first and filter private Sources afterward, you may return too few results or reveal information through scores and diagnostics.
Only after exact search agrees with the Python baseline do I consider a faster index. That gives us a known-correct result for measuring what the speedup may lose.
Add an approximate index when you need it¶
CREATE INDEX chunks_embedding_hnsw
ON chunks USING hnsw (embedding vector_cosine_ops);
HNSW stands for Hierarchical Navigable Small World. It builds paths between nearby vectors so the database can avoid comparing every vector. This usually makes search faster, but it may miss a result that exact search would find.
Run a sample of Evaluation Queries through exact and HNSW search, then measure both recall and latency. Recall tells you how often the expected result was found. If the collection is small, the index may add complexity without a useful speedup.
I use explicit SQL here because the operators, filters, and ranking order are the lesson. An object-relational mapper, usually called an ORM, would translate Python objects into SQL and hide some of those details.
Your lexical and vector results now share stable Chunk IDs. That gives us a clean way to combine them next.