Vector search

In the embeddings lesson you turned text into vectors and ranked them by similarity with a few lines of NumPy. That works for a thousand documents. At a million, comparing the query against every vector on every request gets slow, and you also need the vectors to live next to the rest of your data so you can filter, join and update them safely. This lesson puts embeddings in PostgreSQL with the pgvector extension, explains the distance operators, shows how approximate nearest-neighbour (ANN) indexes trade accuracy for speed, and covers the part most tutorials skip: filtering.

#Why a database, and why PostgreSQL

Brute-force search is simple: compute the distance from the query to every stored vector and keep the closest k. The cost is proportional to the number of vectors times their dimension. One million 1,024-dimensional float4 vectors is 4 GB of data to read for every single query.

You need two things a NumPy array does not give you:

• An index that finds the nearest vectors without looking at all of them.
• Ordinary database features: transactions, WHERE clauses, joins to your users and documents tables, backups, access control.

pgvector adds a vector column type, distance operators and two ANN index types to PostgreSQL. If your application already runs on Postgres, your embeddings can sit in the same row as the text they describe, and deleting a document deletes its vector in the same transaction. No second system to keep in sync.

#Setting up pgvector

The quickest way to get PostgreSQL 17 with pgvector installed is the official Docker image:

docker run -d --name pgvector -e POSTGRES_PASSWORD=dev -p 5432:5432 pgvector/pgvector:pg17
psql postgresql://postgres:dev@localhost:5432/postgres

Enable the extension once per database, then declare a column with a fixed dimension. The dimension must match your embedding model exactly: 384 for all-MiniLM-L6-v2, 768 or 1,024 for larger models.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint NOT NULL,
    category   text   NOT NULL,
    title      text   NOT NULL,
    body       text   NOT NULL,
    embedding  vector(384) NOT NULL
);

Inserting a vector with the wrong length fails loudly (expected 384 dimensions, not 768), which is exactly what you want when someone swaps the embedding model.

From Python, the pgvector package teaches psycopg 3 to send and receive NumPy arrays (pip install "psycopg[binary]" pgvector sentence-transformers):

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")
conn = psycopg.connect("postgresql://postgres:dev@localhost:5432/postgres", autocommit=True)
register_vector(conn)  # run after CREATE EXTENSION, so the type exists

rows = [
    (1, "account", "Reset your password", "Click 'Forgot password' on the login page..."),
    (1, "billing", "Refund policy", "Refunds are issued within 14 days of purchase..."),
]
vectors = model.encode([f"{t}\n{b}" for _, _, t, b in rows], normalize_embeddings=True)

with conn.cursor() as cur:
    for (tenant, cat, title, body), vec in zip(rows, vectors):
        cur.execute(
            "INSERT INTO docs (tenant_id, category, title, body, embedding) VALUES (%s, %s, %s, %s, %s)",
            (tenant, cat, title, body, vec),
        )

For bulk loads of many thousands of rows, use COPY (psycopg's cursor.copy()) instead of row-by-row inserts, and create indexes after the load.

#Distance operators

pgvector defines one operator per distance function. Smaller always means "closer", so you always sort ascending:

Cosine distance is the safe default for text embeddings: it ignores vector length and compares only direction. <#> returns the negative inner product so that ascending order still puts the best match first. If your vectors are normalized to length 1 (as with normalize_embeddings=True), cosine, L2 and inner product all produce the same ranking, and inner product is slightly cheaper to compute.

Here is a toy table with 3-dimensional vectors so you can see real numbers:

SELECT title, round((embedding <=> '[0.8,0.3,0]')::numeric, 4) AS distance
FROM toy_docs
ORDER BY embedding <=> '[0.8,0.3,0]'
LIMIT 3;

        title         | distance
----------------------+----------
 Reset your password  |   0.0306
 Change account email |   0.0661
 Refund policy        |   0.5706
(3 rows)

If you want a similarity score for display, compute 1 - (embedding <=> $1) in the select list, but keep the ORDER BY on the raw operator. The planner can only use a vector index when the query has the shape ORDER BY column <op> constant LIMIT n.

#Exact vs approximate search

Without an index, the query above is an exact search: a sequential scan that computes every distance. Exact search has perfect recall and is perfectly fine for tens of thousands of rows, especially once you filter to a single tenant.

An approximate index answers "roughly the nearest k" much faster, at the cost of sometimes missing a true neighbour. You measure this with recall@k: of the true top k results, what fraction did the index return? Recall of 0.95 at k = 10 means that, on average, 9.5 of the 10 true nearest neighbours came back. For retrieval feeding an LLM, recall in the mid-90s is usually indistinguishable from exact search, because the generator only needs the relevant passages to be somewhere in the top results.

pgvector offers two index types.

#HNSW

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph where each vector links to its nearest neighbours. A search enters at the sparse top layer, greedily walks toward the query, and descends to denser layers to refine. It visits a tiny fraction of the vectors.

CREATE INDEX docs_embedding_hnsw ON docs
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);   -- these are the defaults

SET hnsw.ef_search = 100;              -- per session; default 40

• m: links per node. Higher improves recall but uses more memory.
• ef_construction: candidate list size while building. Higher gives a better graph and a slower build.
• hnsw.ef_search: candidate list size while querying. This is your main runtime knob: raise it for recall, lower it for speed. It must be at least your LIMIT.

#IVFFlat

IVFFlat clusters the vectors into lists groups with k-means. A query compares against the cluster centres, then scans only the closest probes clusters.

CREATE INDEX docs_embedding_ivf ON docs
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);       -- start with rows / 1000 (up to 1M rows), sqrt(rows) beyond

SET ivfflat.probes = 32;   -- default 1; start near sqrt(lists)

Because the clusters come from the data, build IVFFlat after loading a representative set of rows. Built on an empty table, every vector lands in arbitrary lists and recall collapses.

Start with HNSW unless build time or memory is a real constraint. Give builds memory with SET maintenance_work_mem = '2GB'; and check that queries actually use the index:

EXPLAIN SELECT id FROM docs ORDER BY embedding <=> '[...]' LIMIT 5;

 Limit
   ->  Index Scan using docs_embedding_hnsw on docs
         Order By: (embedding <=> '[...]'::vector)

#Filtering

Real queries almost always filter: this tenant's documents, this product, published only.

SELECT id, title
FROM docs
WHERE tenant_id = 42 AND category = 'billing'
ORDER BY embedding <=> $1
LIMIT 5;

With an ANN index there is a trap. The HNSW scan produces ef_search candidates (40 by default) and then Postgres applies the WHERE clause. If only 2% of rows belong to tenant 42, about one of those 40 candidates survives, and your LIMIT 5 query returns one row. Nothing errors; results are just quietly missing.

Your options, from simplest:

1. Let the planner use a B-tree. With CREATE INDEX ON docs (tenant_id); a selective filter can be answered by fetching the tenant's rows and computing exact distances. For a tenant with 5,000 documents this is fast and has perfect recall.

2. Iterative index scans (pgvector 0.8+). The index keeps scanning until enough rows pass the filter:

   SET hnsw.iterative_scan = strict_order;   -- or relaxed_order (faster, may be slightly out of order)
   SET hnsw.max_scan_tuples = 20000;         -- safety cap (the default)

   IVFFlat supports SET ivfflat.iterative_scan = relaxed_order;. With relaxed ordering, re-sort the final rows yourself:

   WITH hits AS MATERIALIZED (
       SELECT id, title, embedding <=> $1 AS distance
       FROM docs WHERE tenant_id = 42
       ORDER BY distance LIMIT 5
   )
   SELECT * FROM hits ORDER BY distance;

3. Partial indexes for a small set of common filter values: CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WHERE category = 'billing';

4. Partitioning by tenant or region when filters are always on the same column, so each partition has its own small index.

#Measuring recall on your own data

Do not guess ef_search. Compare the index against exact search for a sample of real queries. Inside a transaction you can turn index scans off to get ground truth:

def top_ids(conn, query_vec, k=10, exact=False):
    with conn.transaction():
        if exact:
            conn.execute("SET LOCAL enable_indexscan = off")
        rows = conn.execute(
            "SELECT id FROM docs ORDER BY embedding <=> %s LIMIT %s", (query_vec, k)
        ).fetchall()
    return {r[0] for r in rows}

def recall_at_k(conn, query_vecs, k=10):
    scores = [
        len(top_ids(conn, q, k) & top_ids(conn, q, k, exact=True)) / k
        for q in query_vecs
    ]
    return sum(scores) / len(scores)

Run it at a few ef_search values (40, 100, 200), note latency alongside recall, and pick the cheapest setting that meets your target. Re-run after large data changes.

#Dedicated vector databases

Pinecone, Qdrant, Weaviate, Milvus and Chroma are databases built specifically around vector search. They can make sense at very large scale (hundreds of millions to billions of vectors), at very high query rates, or when you want built-in features such as managed sharding or native hybrid search. The cost is another system to operate and keep consistent with your source of truth. For most applications, start with pgvector; you can move later because the concepts (distance metric, HNSW parameters, recall, filtering) are the same everywhere.

#Practice

Build a small, filtered semantic search:

1. Create the docs table above with a vector(384) column.
2. Insert at least 12 short help-centre articles across two tenants and three categories, embedded with all-MiniLM-L6-v2 and normalized.
3. Create an HNSW cosine index and a B-tree index on tenant_id.
4. Write search(conn, tenant_id, query, k=3) that returns (title, similarity) pairs for one tenant only.
5. Confirm with EXPLAIN which index the planner picked for a filtered query.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    category text NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    embedding vector(384) NOT NULL
);
CREATE INDEX docs_embedding_hnsw ON docs USING hnsw (embedding vector_cosine_ops);
CREATE INDEX docs_tenant ON docs (tenant_id);

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")
conn = psycopg.connect("postgresql://postgres:dev@localhost:5432/postgres", autocommit=True)
register_vector(conn)

def search(conn, tenant_id: int, query: str, k: int = 3) -> list[tuple[str, float]]:
    q = model.encode(query, normalize_embeddings=True)
    rows = conn.execute(
        """
        SELECT title, 1 - (embedding <=> %(q)s) AS similarity
        FROM docs
        WHERE tenant_id = %(tenant)s
        ORDER BY embedding <=> %(q)s
        LIMIT %(k)s
        """,
        {"q": q, "tenant": tenant_id, "k": k},
    ).fetchall()
    return [(title, round(sim, 3)) for title, sim in rows]

print(search(conn, 1, "I forgot my login"))

[('Reset your password', 0.71), ('Two-factor authentication', 0.48), ('Change account email', 0.44)]

#Key takeaways

• pgvector stores embeddings in a vector(n) column next to your data, so filters, joins and transactions work as usual.
• Use <=> (cosine distance) for text embeddings, sort ascending, and keep the ORDER BY ... LIMIT shape so indexes apply; the index operator class must match the operator.
• Exact search is fine for small or well-filtered sets; ANN indexes trade a little recall for large speedups.
• HNSW is the default choice (tune hnsw.ef_search); IVFFlat builds faster but needs data first (tune ivfflat.probes).
• Filters applied after an ANN scan can silently return too few rows: use B-tree pre-filtering, iterative scans, partial indexes or partitions, and measure recall against exact search.