# Vector search

> Store embeddings in PostgreSQL with pgvector, query by cosine distance, choose HNSW or IVFFlat indexes and filter results correctly.

AI Engineer roadmap · Building with LLMs · 18 min read · https://levelcode.org/roadmaps/ai-engineer/vector-databases

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:

```bash
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.

```sql
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`):

```python
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:

| Operator | Distance | Index operator class |
| --- | --- | --- |
| `<->` | Euclidean (L2) | `vector_l2_ops` |
| `<=>` | Cosine distance = 1 − cosine similarity | `vector_cosine_ops` |
| `<#>` | Negative inner product | `vector_ip_ops` |
| `<+>` | Manhattan (L1) | `vector_l1_ops` |

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:

```sql
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;
```

```text
        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.

```sql
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.

```sql
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.

| | HNSW | IVFFlat |
| --- | --- | --- |
| Speed vs recall | Better | Good |
| Build time and memory | Slower, more memory | Faster, less memory |
| Build on an empty table | Fine | No: needs data for k-means |
| Query knob | `hnsw.ef_search` | `ivfflat.probes` |

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:

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

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

> [!WARNING]
> The operator in the query must match the index's operator class. An index built with `vector_cosine_ops` is ignored by a query that sorts with `<->`, and you silently fall back to a full scan. Also note that `vector` indexes support up to 2,000 dimensions; for larger embeddings, index a `halfvec` (half-precision, up to 4,000 dimensions) expression instead.

## Filtering

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

```sql
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:

   ```sql
   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:

   ```sql
   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.

> [!IMPORTANT]
> Enforce tenant isolation in the `WHERE` clause, every time, from the authenticated session. Never retrieve across tenants and hope the model ignores the other customer's documents.

## 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:

```python
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.

**Solution**

```sql
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);
```

```python
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"))
```

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

(Your scores will differ with your articles.) With only a dozen rows, `EXPLAIN` will show a sequential scan or the `docs_tenant` B-tree: for tiny tables the planner correctly decides an exact scan is cheapest. The HNSW index starts to win once the table holds tens of thousands of rows, and when it does, check that the filtered query still returns `k` rows; if not, enable `hnsw.iterative_scan`.

## 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.

## Quiz

1. Your table has an HNSW index built with vector_cosine_ops. Which query can use it?

   A. SELECT id FROM docs ORDER BY embedding <-> $1 LIMIT 5
   B. SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 5
   C. SELECT id FROM docs ORDER BY 1 - (embedding <=> $1) DESC LIMIT 5
   D. SELECT id FROM docs WHERE embedding <=> $1 < 0.3

   **Answer: B.** The index is used for ORDER BY column <op> value LIMIT n where the operator matches the operator class. <-> is L2 (different class), sorting by an expression of the distance hides the operator from the planner, and a WHERE range condition on distance is not an indexed nearest-neighbour search.

2. A query filters WHERE tenant_id = 42 (2% of rows) and uses an HNSW index with default settings and LIMIT 10. It returns only 1 row although tenant 42 has hundreds of documents. Why?

   A. HNSW does not support WHERE clauses at all
   B. The index is corrupted and must be rebuilt
   C. The index produced about ef_search (40) candidates, and the filter was applied afterwards, leaving very few
   D. cosine distance cannot be combined with integer columns

   **Answer: C.** Filtering happens after the ANN scan returns its candidate list. Fix it with a B-tree index so the planner can exact-scan the tenant's rows, iterative index scans (pgvector 0.8+), a higher ef_search, partial indexes or partitioning.

3. Why should you create an IVFFlat index after loading data rather than on an empty table?

   A. IVFFlat computes its cluster centres with k-means over the rows present when the index is built
   B. IVFFlat cannot be created inside a transaction
   C. Postgres refuses to create any index on an empty table
   D. IVFFlat only supports vectors with fewer than 100 dimensions

   **Answer: A.** The lists are learned from existing data. With no data the centres are meaningless and recall is poor. HNSW has no training step, so it can be created up front.

4. You raise hnsw.ef_search from 40 to 200. What do you expect?

   A. Lower recall and faster queries
   B. No change; ef_search only affects index builds
   C. The index is rebuilt with more links per node
   D. Higher recall and somewhat slower queries

   **Answer: D.** ef_search is the query-time candidate list size. A larger list explores more of the graph, finding more true neighbours at the cost of latency. m and ef_construction are the build-time parameters.