Running vector search in Postgres

If your data already lives in Postgres, pgvector lets the vectors live there too, which removes a whole system from the diagram: one database, one backup, one transaction, and joins between your embeddings and everything else you already store. This section is a real 50,000-row index, with the timings, the index size, the recall measured against exact search, and the filtering behaviour that returns an empty page in production while looking entirely correct in code. Every number below came from running it.

A table, a column and a query#

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        int primary key,
  document  int,
  tenant    boolean,          -- whatever you will filter on
  content   text,
  embedding vector(384)       -- the dimension is fixed at declaration
);

-- Nearest neighbours by cosine distance. The operator picks the metric:
--   <=>  cosine        <->  L2        <#>  negative inner product
SELECT id, content
FROM chunks
ORDER BY embedding <=> $1
LIMIT 10;

With no index that query is a sequential scan, and for 50,000 rows of 384 dimensions it took 31 ms. The table itself was 80 MB. That is already usable for a small corpus, and it has perfect recall, so do not add an index until the numbers say to.

HNSW index settings#

CREATE INDEX ON chunks
  USING hnsw (embedding vector_cosine_ops)   -- must match the query operator
  WITH (m = 16, ef_construction = 64);

-- Build output on 50,000 rows of 384 dimensions:
--   NOTICE: hnsw graph no longer fits into maintenance_work_mem
--           after 28367 tuples
--   DETAIL: Building will take significantly more time.
--   11.6 seconds, index size 98 MB

Three settings matter and they split cleanly. The operator class has to match the operator you query with — build with vector_cosine_ops and query with the L2 operator and Postgres will simply not use the index, which shows up as a query that was fast in testing and is slow in production. Then m and ef_construction are build-time: higher means a better graph, a larger index and a slower build, and 16 and 64 are sensible defaults. Finally ef_search is query-time, changeable per session, and is the knob you actually tune.

Measuring recall against exact search#

An approximate index has a recall, and you do not know it until you measure it on your own data. The measurement is simple: take a hundred real queries, get the true top ten by forcing a sequential scan, get the indexed top ten, and report the overlap.

-- The ground truth: turn the index off for this session.
SET enable_indexscan = off;
SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 10;

-- The indexed answer, at a given ef_search.
SET enable_indexscan = on;
SET hnsw.ef_search = 40;
SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 10;

-- Overlap over 100 queries, 50,000 rows:
--   ef_search   recall     query time
--      10        0.974       1.7 ms
--      20        0.994       1.8 ms
--      40        0.995       1.9 ms
--     100        0.995       1.6 ms
--   exact        1.000      31.3 ms

Filters that return too few rows#

This is the one that ships. The query below is correct SQL, it is the obvious thing to write, and on this data it returns nothing at all — for a tenant that has 1,059 matching documents.

SELECT id FROM chunks
WHERE tenant                       -- 1,059 of the 50,000 rows
ORDER BY embedding <=> $1
LIMIT 10;

--  0 rows

EXPLAIN ANALYZE on the same query:
  Index Scan using chunks_embedding_idx on chunks
    (actual time=1.407..1.407 rows=0 loops=1)
    Rows Removed by Filter: 40

The mechanism is that the graph traversal does not know about your WHERE clause. It fetches ef_search candidates by vector distance alone and the filter is applied to whatever comes back, so the rarer the filtered category, the more likely every candidate is rejected. A tenant with two per cent of the rows breaks at the default; a tenant with twenty per cent does not, which is exactly why this reaches production — it works for every customer you tested with.

-- Fix 1: widen the search until enough survive the filter.
SET hnsw.ef_search = 1000;
SELECT id FROM chunks WHERE tenant ORDER BY embedding <=> $1 LIMIT 10;
--  10 rows

-- Fix 2: for a selective filter, skip the index entirely. 1,059 rows
-- is nothing to scan exactly, and the answer is exactly right.
SET enable_indexscan = off;
SELECT id FROM chunks WHERE tenant ORDER BY embedding <=> $1 LIMIT 10;
--  10 rows

-- Fix 3: a partial index per high-traffic tenant, so the graph
-- contains only rows that can survive the filter.
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WHERE tenant;

Changing embedding models#

Vectors from two different models are not comparable, so changing model invalidates the entire column at once — there is no incremental path and no way to serve both from one index. The workable migration is a second column, backfilled in the background while the old one keeps serving, an index built on it, then a switch of the query and a drop of the old column. The dimension is declared in the table, so a model with a different width needs the new column regardless.

Price it before you choose the model rather than after. On the numbers above, the index alone was 98 MB for 50,000 chunks, so a few million chunks is tens of gigabytes of index that has to be rebuilt, plus the embedding cost and the hours of throughput to produce them. That figure is what decides whether a model upgrade is a Tuesday or a quarter.