pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?

Q: pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?

**Target Audience:** Developers adding semantic search to PostgreSQL, and candidates preparing for backend or ML-infrastructure interviews **Difficulty:** Hard **Engines:** PostgreSQL with the pgvector extension. pgvector is not installed on the machine that verified this page, so the index behaviour is explained with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and PostgreSQL 16.15; pgvector SQL is marked "not run here" and its facts are cited from the [pgvector README](https://github.com/pgvector/pgvector) and changelog. > **How to read this doc:** Every new term is explained in plain words first. When it is a term interviewers expect you to know, it is tagged `📌 Interview term:` so you can learn the vocabulary too. ## 1. Why This Even Matters — A Story First Picture a library with a million books and no catalogue. Someone asks for "books like this one". The honest method is to compare the request with every single book. It always finds the best match, and it takes al

The problem

Question presented to candidate: "We store OpenAI-style embeddings for a few million support articles in Postgres with pgvector and run cosine-similarity search. Which index would you put on the embedding column, HNSW or IVFFlat, and how would you tune it?"

What a strong answer should cover:

Cosine distance, the <=> operator and the matching vector_cosine_ops operator class; why normalized vectors make cosine, inner product and L2 rank alike
Both indexes are approximate (ANN): they trade recall for speed, so recall@k has to be measured against exact search
IVFFlat: k-means lists built from existing data, lists at build time and ivfflat.probes at query time; cheaper to build, must be built after the data is loaded and rebuilt when the data drifts
HNSW: a layered neighbour graph, m and ef_construction at build time and hnsw.ef_search at query time; better speed-recall trade-off, slower to build and more memory, no training step (pgvector README)
Version facts: HNSW arrived in pgvector 0.5.0 (2023), iterative index scans in 0.8.0 (2024); indexes on vector are capped at 2,000 dimensions, halfvec allows 4,000
Operations: memory for the build (maintenance_work_mem), the index only helps ORDER BY embedding <=> $1 LIMIT k, and filtered queries need extra care

Clarifying questions expected:

How many vectors, how many dimensions, and how fast does the table grow or change?
What recall is acceptable (for example 95% of the true top 10), and what latency budget per query?
Do queries also filter on other columns (tenant, category, language)?

Code / implementation expected: Optional — the index DDL and tuning settings, plus a way to measure recall against an exact query.

Schema used by the examples:

pgvector
hnsw
cosine-search

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers adding semantic search to PostgreSQL, and candidates preparing for backend or ML-infrastructure interviews Difficulty: Hard Engines: PostgreSQL with the pgvector extension. pgvector is not installed on the machine that verified this page, so the index behaviour is explained with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and PostgreSQL 16.15; pgvector SQL is marked "not run here" and its facts are cited from the pgvector README and changelog.

How to read this doc: Every new term is explained in plain words first. When it is a term interviewers expect you to know, it is tagged 📌 Interview term: so you can learn the vocabulary too.

1. Why This Even Matters — A Story First

Picture a library with a million books and no catalogue. Someone asks for "books like this one". The honest meth

The solution is hidden so you can try first

14 min read · 13 sections

Run the code

Edit the query and run it against the tables for this question.

SQLExact cosine search: rank every row by cosine distance to the query (the ground truth)

SQLSimulate IVFFlat: scan only the nearest lists, and measure recall@3 for probes = 1, 2, 3

SQLSimulate an HNSW layer-0 walk: hop to the closest neighbour until none is closer

PostgreSQL without a vector index: ORDER BY distance LIMIT 3 is a Seq Scan plus a top-N sort

CREATE INDEX docs_e1_e2_idx ON docs (e1, e2);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id
FROM docs
ORDER BY 1 - (e1 * 0.643 + e2 * 0.766) / sqrt(e1 * e1 + e2 * e2)
LIMIT 3;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.