So far the vectors have lived in a NumPy file that is loaded and scanned in full on every question. That is fine for eight thousand chunks on a laptop and wrong for anything you would ship: there is no index, no way to update one post without rewriting the file, and nowhere to keep the metadata next to the vectors. This lesson moves everything into Postgres with the pgvector extension we set up in lesson 2.
It also adds a second, bigger embedding model, to see what "better" costs. And it contains two bugs, both instructive: one Postgres refused loudly, and one produced plausible, wrong benchmark numbers without any error at all.
Code: ask_lmsc/store.py for the database, ask_lmsc/embed.py for the
models, and lessons/04_pgvector.py with three commands, ingest,
ask and bench.
The table
def recreate(conn: psycopg.Connection, model: str) -> None:
dim = MODELS[model][1]
conn.execute(sql.SQL("DROP TABLE IF EXISTS {}").format(table(model)))
conn.execute(
sql.SQL("""
CREATE TABLE {} (
id bigserial PRIMARY KEY,
url text NOT NULL,
category text NOT NULL,
title text NOT NULL,
heading text NOT NULL,
content text NOT NULL, -- the chunk text exactly as embedded
embedding vector({}) NOT NULL
)""").format(table(model), sql.Literal(dim))
)
One row per chunk: where it came from, the exact text that was embedded, and the vector.
vector(384) is pgvector's column type, and the number is fixed: a vector column holds
vectors of exactly one length. That is one reason for one table per embedding model
(chunks_small, chunks_base). The other is that vectors from two different
models live in unrelated spaces; comparing them is meaningless even if the lengths happened to
match.
The table name is built with psycopg.sql.Identifier, never with an f-string. It comes
from our own code here, but the habit is the one that keeps SQL injection out of the code that
doesn't.
Loading and indexing
def load_rows(conn: psycopg.Connection, model: str, chunks, vectors) -> None:
with conn.cursor() as cur:
cur.executemany(
sql.SQL("INSERT INTO {} (url, category, title, heading, content, embedding) "
"VALUES (%s, %s, %s, %s, %s, %s)").format(table(model)),
[(c.url, c.category, c.title, c.heading, c.text, v) for c, v in zip(chunks, vectors)],
)
def create_index(conn: psycopg.Connection, model: str) -> None:
conn.execute(
sql.SQL("CREATE INDEX ON {} USING hnsw (embedding vector_cosine_ops)").format(table(model))
)
conn.execute(sql.SQL("ANALYZE {}").format(table(model)))
register_vector(conn) in connect() teaches psycopg to send NumPy arrays
as pgvector values, so the embedding model's output goes straight into the insert.
The index is HNSW (hierarchical navigable small world): a graph in which each vector links to its near neighbours, searched by hopping greedily toward the query. It is approximate. It finds the nearest vectors almost always, not always, in exchange for not comparing the query with every row. Two details:
vector_cosine_opsmust match the operator you query with. We search with<=>, cosine distance; an index built for a different operator is silently ignored.- The index is built after loading. Building the graph once from all rows is much faster than updating it on every insert.
The first bug: a NUL byte
python lessons/04_pgvector.py ingest --model small
psycopg.DataError: PostgreSQL text fields cannot contain NUL (0x00) bytes
One post, the Java data types table, contains a literal NUL character where it means Java's
'\u0000', the default value of a char. Postgres text cannot
store NUL at all. The chunker now writes it the way the post intends:
soup = BeautifulSoup(post["html"].replace("\x00", "\\u0000"), "html.parser")
Real corpora contain bytes like this. It is better to meet them at ingest, where the error names the problem, than as a missing post you never notice.
Searching
def search(conn: psycopg.Connection, model: str, query_vec, k: int = 5, exact: bool = False) -> list[dict]:
with conn.transaction():
if exact:
conn.execute("SET LOCAL enable_indexscan = off")
rows = conn.execute(
sql.SQL("""
SELECT url, title, content, 1 - (embedding <=> %s) AS score
FROM {}
ORDER BY embedding <=> %s
LIMIT %s""").format(table(model)),
(query_vec, query_vec, k),
).fetchall()
return [{"url": u, "title": t, "text": c, "score": float(s)} for u, t, c, s in rows]
ORDER BY embedding <=> query LIMIT k is the shape the HNSW index accelerates.
Cosine distance is 1 minus cosine similarity, so 1 - distance gives back the
same score the NumPy version printed. The exact flag turns the index off for one query,
which we need to measure how approximate the index is.
First check after any migration: same question, same results. For "What is a Python decorator?" pgvector returned exactly lesson 4's NumPy results, 0.872, 0.858, 0.811 and so on. If those numbers had differed, something in the move would be wrong, and every later comparison would be built on it.
The second bug: a benchmark that lied
bench runs 50 queries (post titles stand in for questions until the next lesson
builds real ones), timing each with and without the index, and counting how often the index returns
the same top five as exact search. The first run said:
small: table+index 51 MB query HNSW 25.9 ms exact 26.6 ms HNSW recall@5 vs exact 100.0%
An index that is no faster than scanning every row, and exactly 100% accurate. Either HNSW is
useless here, or the index isn't being used. EXPLAIN ANALYZE on a single query said the
index was used, in 0.3 ms. So the query was fast, and the benchmark was measuring something
else.
It was a transaction bug. psycopg connections are not in autocommit mode by default, so the
benchmark's first query (a check that the table exists) silently opened a transaction that never
closed. Every with conn.transaction() after that became a savepoint inside it,
and SET LOCAL lasts until the end of the outer transaction. The first exact
search turned the index off, and it stayed off for every query after it. The benchmark had been
comparing exact search with itself.
def connect() -> psycopg.Connection:
# autocommit matters: without it the first query opens a transaction that stays open, every
# `with conn.transaction()` below becomes a savepoint inside it, and `SET LOCAL` in search()
# then outlives the query - silently turning the index off for everything that follows.
conn = psycopg.connect(
os.environ.get("DATABASE_URL", "postgresql://rag:rag@localhost:5434/rag"), autocommit=True
)
register_vector(conn) # lets psycopg send and receive NumPy arrays as `vector`
return conn
The lesson is wider than psycopg: when a benchmark result is suspiciously clean, check the
mechanism before believing the number. Identical timings and a perfect 100% were the clues.
EXPLAIN costs a minute.
Small versus base
bge-base-en-v1.5 is the bigger model in the same family: 768 dimensions instead of
384, and the same tokenizer and 512-token limit, so the chunks are identical and only the model
differs. Both were ingested into their own tables and benchmarked after the fix:
Model Embed 11,701 Insert HNSW build Table+index Query HNSW Query exact HNSW recall@5
---------------- ------------- ------ ---------- ----------- ---------- ----------- -------------
bge-small (384d) 78 s 0.4 s 1.1 s 51 MB 2.7 ms 26 ms 100%
bge-base (768d) 210 s 0.4 s 1.9 s 103 MB 6.4 ms 34 ms 100%
- The index is about ten times faster than a full scan (2.7 ms against 26 ms), and on the server alone the gap is bigger, HNSW 0.3 ms vs 14.6 ms. At 11,701 rows that saves milliseconds; at a few million rows it is the difference between usable and not.
- Recall was 100% on all 50 queries, now for real. HNSW's default search width is
generous for a table this size. On a much larger table, check it again, and raise
hnsw.ef_searchif it drops. - The bigger model costs 2.7 times the embedding time, twice the storage and about 2.4 times the query time. Whether it is better is a different question, and a benchmark of speed cannot answer it.
python lessons/04_pgvector.py ask --model small --retrieve-only "What is a Python decorator?"
python lessons/04_pgvector.py ask --model base --retrieve-only "What is a Python decorator?"
On that one question bge-base put three Python decorator chunks in the top five
instead of two. Is that a better model, or one lucky question? We have now reached the limit of
judging by looking. The next lesson builds the measurement that settles questions like this, and it
changes our minds about the model more than once.