Callum Doty

Vectors in Postgres

How my catalog stores 1,536-number embeddings in Postgres with pgvector, why an exact nearest-neighbor search reads every vector, how an HNSW index walks a graph instead, and the keyword search that runs beside it. Part 2 of 3.

Systems & Engineering

In the first post, every piece of mail in my catalog became 1,536 numbers, and so did every search query. Search then reduces to one question: which pieces’ numbers sit nearest the query’s? The catalog answers it inside Postgres, the same database that already holds each piece’s client, state, date, text, and file location, using an extension called pgvector.

Postgres and pgvector

Postgres is a relational database. Data lives in tables of rows, queries are written in SQL, and a query planner decides how to run each one: which indexes to use, what order to join in, whether to read a whole table or jump straight to the rows it needs. Postgres is also built to be extended. An extension can add data types, operators, and kinds of index, and the planner treats them like built-in ones.

pgvector is that kind of extension. It adds:

  • a vector(n) column type that holds nn floats,
  • distance operators: <-> for Euclidean distance, <#> for negative inner product, and <=> for cosine distance,
  • two approximate nearest-neighbor indexes, IVFFlat and HNSW.

Turning it on is one statement, run in the catalog’s first migration:

CREATE EXTENSION IF NOT EXISTS vector;

In the application, the vector is one more column on the documents table, declared through SQLAlchemy with pgvector’s Vector type:

from pgvector.sqlalchemy import Vector

class Document(Base):
    __tablename__ = "documents"
    # … client, state, dates, extracted text, file paths …
    search_vector = Column(Vector(1536), nullable=True)
    embedding_model = Column(String(100), nullable=True)
    embedding_version = Column(Integer, nullable=True)
    embedding_provenance = Column(JSONB, nullable=True)

Writing a vector is ordinary attribute assignment. The Vector type converts the list of floats OpenAI returned into the column’s format:

document.search_vector = embeddings  # list of 1,536 floats
self.db.commit()

Keeping the vector in the same row as everything else is the point. One SQL statement can rank pieces by meaning, keep only the ones from a given state, and combine that with keyword matches, with no second system to keep in sync. Part 3 is about what happened when those parts of one statement interacted in a way I didn’t expect.

What a nearest-neighbor query costs

The nearest pieces to a query are one statement. SQLAlchemy’s cosine_distance compiles to pgvector’s <=> operator:

SELECT id FROM documents
ORDER BY search_vector <=> :query_embedding
LIMIT 20;

Run exactly, this computes the distance from the query to every row, sorts, and keeps the top 20. The arithmetic is not the problem. 9,828 documents times 1,536 dimensions is about 15 million multiply-adds, which a CPU finishes in milliseconds. The problem is that an exact search has to read every vector, every time, and each one is 1,536 four-byte floats, about 6 KB. Exact search grows linearly with the catalog. Approximate indexes exist to make it sublinear, and they do that by not looking at most of the data.

HNSW: walking a graph instead of sorting a list

pgvector’s HNSW index (Hierarchical Navigable Small World) doesn’t sort anything. It builds a graph. The catalog’s index is declared next to the model:

Index(
    "idx_documents_search_vector",
    Document.search_vector,
    postgresql_using="hnsw",
    postgresql_with={"m": 32, "ef_construction": 128},
    postgresql_ops={"search_vector": "vector_cosine_ops"},
)

Every vector is a node linked to its near neighbors, and m sets how many links each node gets. Above that bottom layer sit progressively sparser layers, each holding a random subset of the nodes joined by longer links, like express lanes over local roads. ef_construction is how wide a search the build runs when it picks each new node’s neighbors. A larger value builds a better graph, more slowly. I set both in February, when I tuned the index for a 10,000-document catalog. vector_cosine_ops builds the index for the cosine distance operator, <=>, and a query has to order by that same operator for the index to apply.

A search enters at the top layer and moves greedily: from the current node, hop to whichever neighbor is closer to the query, and stop when none is. Then drop a layer and repeat. By the time it reaches the bottom layer, the search is already in the right neighborhood, and it switches to a wider best-first search.

That last step decides the answer. The bottom-layer search keeps a list of the best candidates found so far, with a fixed capacity efef. It repeatedly expands the closest candidate it hasn’t explored yet, adds any of that node’s neighbors that beat the worst entry on the list, and stops when the closest unexplored candidate is farther away than the worst one already kept. Whatever is on the list at that point is the result.

The work grows with the size of that list and the number of links per node, and only slowly with the size of the table. The price is that the result is approximate: a true nearest neighbor that the walk never reaches is never returned.

ef_construction has a query-time counterpart, hnsw.ef_search, which sets the capacity of that list during a search. Its default is 40, and I never set it. Part 3 is about why that mattered.

The shape the index needs

The planner only uses an HNSW index for one shape of query. pgvector’s documentation spells out the rule: the query needs an ORDER BY and a LIMIT, and the ORDER BY has to be a distance operator, not an expression, in ascending order. ORDER BY search_vector <=> :q LIMIT 20 qualifies. Computing a similarity for every row and filtering or sorting on it some other way does not.

I learned that the slow way. When I tuned the index in February, the search query computed a distance for every row without the ORDER BY … LIMIT shape, so the planner never used the index. Every semantic search read the whole table for about ten weeks, and the tuning did nothing, until I rewrote the query in April.

Meaning isn’t everything. Someone searching for a candidate’s name or a bill number wants the pieces that contain those exact words, and an embedding of a summary may not carry them. So every search also runs Postgres’s built-in full-text search, over each piece’s raw extracted text rather than its summary.

Full-text search needs its own representation. A tsvector is a sorted list of normalized words: stop words like “the” are dropped and the rest are reduced to stems, so “voting” and “votes” both become “vote”. The catalog stores one per piece as a generated column, computed from the filename and the text Claude extracted, and indexes it with GIN:

ts_vector = Column(
    TSVECTOR,
    Computed(
        "to_tsvector('english', coalesce(filename, '') || ' ' || "
        "coalesce(extracted_text, ''))",
        persisted=True,
    ),
)

Index("idx_documents_ts_vector", Document.ts_vector, postgresql_using="gin")

A GIN index is an inverted index: for each word, the list of rows that contain it, like the index at the back of a book. It answers the opposite question from HNSW. Instead of “what is near this point?”, it asks “which rows contain this word?”, and its answer is exact.

At search time, the query goes through the same normalization with plainto_tsquery, which joins its words with AND. The @@ operator checks whether a piece’s tsvector matches, and ts_rank_cd scores the match by how often the query’s words appear and how close together they sit:

ts_query = func.plainto_tsquery("english", query)
keyword_matches = (
    select(
        Document.id.label("id"),
        func.ts_rank_cd(Document.ts_vector, ts_query, 32)
            .label("text_relevance"),
    )
    .filter(Document.ts_vector.bool_op("@@")(ts_query))
    # (filters omitted here; part 3 covers where they go)
)

The 32 is a normalization flag that maps a rank rr to r/(r+1)r / (r + 1), which keeps every keyword score between 0 and 1.

Combining the two

The candidates for a search are the union of both lookups: the pieces nearest the query by vector, and the pieces that match its words. Each candidate gets one score:

relevance=0.7×cosine similarity+0.3×keyword rank\text{relevance} = 0.7 \times \text{cosine similarity} + 0.3 \times \text{keyword rank}

The weights are hand-set, and the two scores sit on different scales, so in practice vector similarity tends to dominate. Comparing these weights against an approach like Reciprocal Rank Fusion is on my list, once I have a judged set of real queries to compare them on.

On paper, that is the whole search: embed the query, ask the HNSW index for the nearest pieces, find the keyword matches, merge, and score. In production, the first lookup asked the index for 500 candidates and got 40. That is part 3.