AI & ML / RAG & embeddings / 10_pgvector_in_production.md

pgvector in production

Updated 5 interview angles 5 min read source
On this page7
  1. The operators decide the index
  2. HNSW vs IVFFlat
  3. The filtered-query cliff
  4. Keeping it fast
  5. When to leave
  6. Related
  7. Interview angle

pgvector in production

Vector search inside Postgres. The reason it wins so often is not benchmark numbers — it is that your vectors sit in the same transaction, the same backup, the same access control and the same ops runbook as everything else.

sql 
CREATE EXTENSION vector;

CREATE TABLE chunks (
    id        BIGSERIAL PRIMARY KEY,
    doc_id    BIGINT REFERENCES documents(id),
    content   TEXT NOT NULL,
    embedding VECTOR(1536),
    meta      JSONB
);

Note: as of 2026-08 pgvector is 0.8.6. It also has halfvec (half-precision, up to 4,000 dims), binary quantisation and sparsevec, which are the levers when the index stops fitting in RAM.

The operators decide the index

Operator Distance Index opclass
<=> cosine vector_cosine_ops
<-> L2 vector_l2_ops
<#> negative inner product vector_ip_ops

An index built for one operator will not be used by a query written with another. This is the most common “why is it doing a sequential scan” answer, and it is silent — you get correct results, slowly.

HNSW vs IVFFlat

sql 
CREATE INDEX ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX ON chunks
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);
HNSW IVFFlat
Build time slow fast
Memory higher lower
Recall/speed better acceptable
Needs data first no yes

IVFFlat must be built on populated data, because lists are centroids derived from the existing rows. Build it on an empty table and recall is poor forever — a real production trap during a re-index. HNSW has no such requirement, which is one reason it is the default choice now.

Rules of thumb: m = 16 and ef_construction = 64 are fine defaults; raise m for higher recall at the cost of memory. For IVFFlat, lists ≈ rows/1000 up to a million rows, then sqrt(rows).

ef_search is the dial you actually turn

sql 
SET hnsw.ef_search = 100;      -- default 40
SET ivfflat.probes = 10;       -- default 1

This is the recall/latency knob, set per session or per query, not at index build. Raising it searches more candidates: better recall, more time. Being able to say “I tuned recall with ef_search rather than rebuilding the index” is the concrete detail that separates having used pgvector from having read about it.

Tune it against a golden set — see Agentic RAG and evaluating retrieval.

The filtered-query cliff

The failure mode that surprises people:

sql 
SELECT id, content
FROM chunks
WHERE meta->>'tenant' = 'acme'
ORDER BY embedding <=> $1
LIMIT 10;

An ANN index returns approximately the nearest ef_search candidates, and the filter is applied to those. If acme is 1% of the table, most candidates are discarded and you may get back three rows instead of ten — or Postgres gives up on the index and scans.

Three ways out:

  1. Partial indexes per high-cardinality value, when the set is small and known — one index per tenant.
  2. Raise ef_search so more candidates survive the filter. Cheap, and usually enough for mild selectivity.
  3. Partition the table by the filter column, so each partition has its own index and the filter becomes partition pruning.

Iterative index scans in recent pgvector versions soften this by fetching more candidates when the filter eats too many, but the reasoning is still what an interviewer wants to hear.

Keeping it fast

  • Index build is memory-hungry. Raise maintenance_work_mem before creating an HNSW index or it spills and takes hours. Build concurrently on a live table.
  • Vacuum matters as much as anywhere else. Updated embeddings leave dead tuples the index still walks.
  • Dimension is storage. 1536 float32 dims is ~6 KB per row before the index. halfvec halves it for a small recall cost; binary quantisation with rescoring goes much further.
  • Store the chunk text alongside the vector. Retrieval that needs a second round trip to fetch content is the easiest latency win to skip.

When to leave

pgvector comfortably handles the low millions of vectors on one machine. The honest triggers for a dedicated store are: tens of millions of vectors, a need to scale search independently of your OLTP database, or built-in features you would otherwise write — native hybrid ranking, multi-tenant isolation, distributed sharding.

Starting on pgvector and moving later is the right order for almost every product, because the migration is a re-index, not a rewrite.

Interview angle 5

  • “Why pgvector rather than a dedicated vector database?” - the vectors live in the same transaction, backup, access control and ops runbook as the rest of the data, and you can join them to it. For the low millions of vectors that is a smaller system with fewer failure modes, and moving later is a re-index rather than a rewrite.
  • “HNSW or IVFFlat?” - HNSW by default: better recall for the latency, and no build-order constraint. IVFFlat builds faster and uses less memory, but its lists are centroids computed from existing rows, so building it on an empty table gives permanently poor recall.
  • “How do you trade recall against latency?” - hnsw.ef_search, set per session or per query rather than at build time. It widens the candidate list: more recall, more time. Tune it against a golden set instead of guessing.
  • “Why did my filtered vector query get slow or return too few rows?” - the ANN index returns ef_search candidates and the filter is applied to those, so a selective filter discards most of them. Fix with partial indexes, a higher ef_search, or partitioning by the filter column.
  • “Why is Postgres ignoring my vector index?” - the query uses a different operator from the one the index was built for. An index on vector_cosine_ops does nothing for a <-> query, and the failure is silent: right answers, sequential scan.
Previous
Agentic RAG and evaluating retrieval