← Back to Blog
Oct 26, 2026 · 11 min read

Your pgvector Index Is Lying About Recall. Measure It in SQL.

RAGpgvectorPostgreSQLPerformance

You added a vector column, ran CREATE INDEX ... USING hnsw, watched p95 drop from 27ms to under a millisecond, and closed the ticket. Nobody asked the only question that matters: are those 10 rows actually the 10 nearest neighbors?

An approximate index is allowed to be wrong. That is the deal you signed for the speed. What you usually do not know is how wrong, because nothing in Postgres reports it. Default settings are a compromise chosen by someone else, for a dataset that is not yours.

On 50,000 vectors with 384 dimensions, the default HNSW settings in pgvector 0.8.7 returned 95.9% of the true top 10 in my test. Setting ef_search to 10 dropped that to 84.1%. Both queries looked identical from the application side: fast, no errors, ten rows back.

Measuring it takes one SQL function and about ten minutes. Everything below runs locally in Docker. No Python, no embedding API, no vector database to install.

What you need

  • Docker 24 or newer (docker version)
  • A psql client, or just use docker exec as shown
  • Around 1GB of free RAM and a spare port
  • Comfortable writing SQL

Step 1: start Postgres with pgvector

docker run -d --name pgv \
  --shm-size=1g \
  -e POSTGRES_PASSWORD=bench \
  -p 5433:5432 pgvector/pgvector:pg17

docker exec -it pgv psql -U postgres -c 'CREATE EXTENSION vector;'
docker exec -it pgv psql -U postgres -c "SELECT extversion FROM pg_extension WHERE extname = 'vector';"

That prints 0.8.7 on top of PostgreSQL 17.11.

The --shm-size=1g flag is not optional if you plan to raise maintenance_work_mem during the index build. Without it, the container keeps the default 64MB of /dev/shm, and the build dies with:

ERROR: could not resize shared memory segment "/PostgreSQL.2933610774"
to 265326048 bytes: No space left on device

I hit that on the first attempt with a 1GB container. It reads like a disk problem. It is not. Parallel index builds use dynamic shared memory, and Docker caps that separately from your disk.

Step 2: create 50,000 vectors with SQL only

You could install a Python stack and embed 50,000 documents. It would take longer than the benchmark. Instead, generate deterministic pseudo-random vectors from an md5 hash: 200 cluster centers, each row is a center plus noise, normalized to unit length. The clustering matters. Real embeddings cluster, and HNSW recall depends heavily on that geometry.

CREATE TABLE centers AS
SELECT c.id,
       ARRAY(
         SELECT ((('x' || substr(md5('center' || c.id || ':' || s.n), 1, 8))::bit(32)::int)::float8 / 2147483648.0)
         FROM generate_series(1, 384) s(n)
       ) AS v
FROM generate_series(1, 200) c(id);

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(384));

INSERT INTO items (id, embedding)
SELECT g.id,
       l2_normalize(ARRAY(
         SELECT c.v[s.n] + 0.6 * ((('x' || substr(md5(g.id::text || ':' || s.n), 1, 8))::bit(32)::int)::float8 / 2147483648.0)
         FROM generate_series(1, 384) s(n)
         CROSS JOIN LATERAL (SELECT cn.v FROM centers cn WHERE cn.id = 1 + (g.id % 200)) c
       )::vector)
FROM generate_series(1, 50000) g(id);

ANALYZE items;

Two details that bite. The inner generate_series must reference the outer row (g.id), otherwise Postgres evaluates the subquery once and every row gets the same vector. I wasted a run on exactly that: 50,000 identical embeddings, all cosine distances equal, and a "recall" of 0% that meant nothing. If count(DISTINCT embedding::text) is not 50000, stop and fix the statement.

The second detail is l2_normalize, available since pgvector 0.7. Cosine distance on unnormalized vectors still works, but normalized vectors make the distance distribution behave like production embeddings.

Step 3: build the ground truth

Ground truth means the exact answer, computed without any index. You disable the index scans, let Postgres do a sequential scan plus top-N sort, and store the result.

CREATE TABLE queries (qid int, embedding vector(384));

INSERT INTO queries (qid, embedding)
SELECT g.qid,
       l2_normalize(ARRAY(
         SELECT c.v[s.n] + 0.6 * ((('x' || substr(md5('query' || g.qid || ':' || s.n), 1, 8))::bit(32)::int)::float8 / 2147483648.0)
         FROM generate_series(1, 384) s(n)
         CROSS JOIN LATERAL (SELECT cn.v FROM centers cn WHERE cn.id = 1 + ((g.qid * 7) % 200)) c
       )::vector)
FROM generate_series(1, 100) g(qid);

SET enable_indexscan = off;
SET enable_bitmapscan = off;

CREATE TABLE truth AS
SELECT q.qid, array_agg(t.id ORDER BY t.dist) AS exact_ids
FROM queries q
CROSS JOIN LATERAL (
  SELECT i.id, i.embedding <=> q.embedding AS dist
  FROM items i ORDER BY i.embedding <=> q.embedding LIMIT 10
) t
GROUP BY q.qid;

RESET enable_indexscan;
RESET enable_bitmapscan;

100 queries at 27.6ms each is under three seconds of work. The exact scan is the slow part, and that is the point: you are paying for it once, offline, to find out what the fast path is missing.

Step 4: one function that reports recall and latency

CREATE OR REPLACE FUNCTION recall_at_10() RETURNS TABLE(avg_ms numeric, recall_pct numeric) AS $$
DECLARE
  q record; t0 timestamptz; ids bigint[]; hits int;
  total_ms numeric := 0; total_hits int := 0; n int := 0;
BEGIN
  FOR q IN SELECT qid, embedding FROM queries LOOP
    t0 := clock_timestamp();
    SELECT array_agg(id) INTO ids
    FROM (SELECT id FROM items ORDER BY embedding <=> q.embedding LIMIT 10) s;
    total_ms := total_ms + extract(epoch FROM clock_timestamp() - t0) * 1000;

    SELECT count(*) INTO hits
    FROM (SELECT unnest(ids) INTERSECT SELECT unnest(t.exact_ids) FROM truth t WHERE t.qid = q.qid) x;

    total_hits := total_hits + hits;
    n := n + 1;
  END LOOP;

  avg_ms := round(total_ms / n, 3);
  recall_pct := round(100.0 * total_hits / (n * 10), 1);
  RETURN NEXT;
END; $$ LANGUAGE plpgsql;

Recall here is overlap with the exact top 10, averaged over 100 queries, so a score of 95.9 means the index missed roughly 4 of every 100 true neighbors. INTERSECT ignores duplicates and order, which is fine: if the index returns the right ten IDs in a different order, that is not a retrieval failure. Reranking handles order later.

Step 5: the baseline

SELECT avg_ms, recall_pct FROM recall_at_10();

With no index on the column: 27.6ms and 100% recall, trivially, because the planner has no other option. That is your ceiling for latency and your definition of correct.

Step 6: build the index and sweep ef_search

SET maintenance_work_mem = '256MB';
CREATE INDEX items_hnsw ON items
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 40;   -- the default
SELECT avg_ms, recall_pct FROM recall_at_10();

SET hnsw.ef_search = 100;
SELECT avg_ms, recall_pct FROM recall_at_10();

The build took 10.4 seconds and produced a 102MB index for 50,000 rows of 384 dimensions. The sweep on 100 queries:

  • exact scan: 27.6ms, 100% recall
  • ef_search 10: 0.80ms, 84.1% recall
  • ef_search 40 (default): 0.75ms, 95.9% recall
  • ef_search 100: 0.58ms, 99.0% recall
  • ef_search 200: 0.76ms, 100% recall

Read the latency column with suspicion. The whole index fits in page cache, so the differences between 0.58ms and 0.80ms are noise, not signal. At 50,000 rows the honest conclusion is that speed is flat and recall is the only thing moving. On a table with 10 million vectors the latency curve becomes real, and that is where ef_search becomes a genuine cost decision.

What the table does tell you: the default of 40 leaves about 4% of true neighbors on the floor. Dropping to 10 costs you 16 points of recall and buys nothing measurable here. For a RAG system I would rather pay one extra millisecond than quietly omit the chunk that contained the answer.

Set it per query instead of globally, so a batch job can use a tighter budget than a user-facing search:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id, content FROM chunks ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Step 7: m and ef_construction

m is the maximum number of connections per graph layer, 16 by default. ef_construction is the candidate list size during the build, 64 by default. Both default values are documented in the pgvector README, and the README's own advice is to use the defaults unless you are seeing low recall.

Same 50,000 vectors, three builds, recall measured at two ef_search values:

  • m=8: 7.1s build, 89.4% at ef_search 40, 98.8% at 100
  • m=16: 10.3s build, 98.9% at ef_search 40, 99.0% at 100
  • m=32: 20.5s build, 97.0% at ef_search 40, 100% at 100

Build time doubles from m=16 to m=32 while recall at the default setting moves inside noise on this dataset. Index size barely moved either: 102MB for both m=8 and m=16, 106MB for m=32. If your recall is bad, raising ef_search at query time is the cheaper fix. Raising m costs you build time and index size on every write from then on.

The m=32 result at ef_search 40 (97.0%) scoring below m=16 (98.9%) is a reminder that recall differences of one or two points on 1,000 neighbor checks are not a verdict. Use more queries before you draw conclusions from small gaps.

Step 8: IVFFlat as the comparison

IVFFlat builds a k-means partition instead of a graph. Same table, lists = 100 (the README suggests rows divided by 1000 for up to a million rows):

CREATE INDEX items_ivf ON items
  USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

SET ivfflat.probes = 10;  -- README suggests starting at sqrt(lists)
SELECT avg_ms, recall_pct FROM recall_at_10();
  • build: 1.4 seconds, 78MB
  • probes=1: 0.55ms, 100% recall
  • probes=10: 2.99ms, 100% recall
  • probes=50: 27.5ms, 100% recall

The 100% at one probe is a property of this synthetic dataset, not a general result. My queries were generated from the same 200 centers as the data, so the query lands in the correct list and the nearest neighbors are inside it. Real embeddings overlap between clusters, and one probe will miss. What generalizes is the shape: IVFFlat builds about seven times faster and uses less disk, then pays for it during search, where probes=50 is already slower than a sequential scan.

Two IVFFlat rules that cost people whole afternoons:

  1. Build the index after the table has data. An index built on an empty table has no rows to cluster.
  2. probes equal to lists gives exact search, and the planner will not use the index at all. The 27.5ms at probes=50 and 26.8ms at probes=100 are both the sequential scan you were trying to avoid.

When to use this and what to skip

Below roughly 50,000 vectors, measure first and consider skipping the index. A 27ms sequential scan is acceptable for an internal tool, and it cannot lose neighbors. Above that, HNSW with defaults plus an ef_search you have actually tested beats any amount of tuning folklore.

Skip pgvector when the index no longer fits in RAM with reasonable headroom, or when you need quantization and filtering pushed into the index layer. That is a Qdrant or Milvus conversation, and it comes with an extra service to operate. Do not have that conversation before you have measured recall, because you cannot tell whether the new system is better without a number to compare.

Gotchas worth writing down

  • Docker shared memory: raise --shm-size before raising maintenance_work_mem, or the build fails with a misleading disk error.
  • Uncorrelated subqueries: if the vector generator does not reference the outer row, every row gets the same vector. Check count(DISTINCT ...) before you trust any benchmark.
  • Uniform random vectors are the worst case: my first run used uniform random vectors instead of clusters, and recall@10 collapsed to 5.2% at ef_search = 10. If your data is genuinely spread through the whole space, HNSW will disappoint you and no parameter fixes it.
  • Rebuild after bulk re-embedding: change your embedding model and the old graph indexes vectors that no longer mean the same thing.
  • Cache-warm numbers lie: run each configuration at least twice and report the second run.

Next step

Replace the synthetic generator with 5,000 rows from your real corpus, run recall_at_10() at ef_search 10, 40, and 100, and pick the smallest value that keeps recall above your threshold. Then put that number in a comment next to the CREATE INDEX statement, so the next person changing it knows what it protects.

References

Need Help Implementing This?

I help teams design and build scalable cloud infrastructure, DevOps pipelines, and production-grade systems.

Book a Free Consultation