pgvector: HNSW vs IVFFlat and the Filtering Trap

Opaeron stores embeddings in PostgreSQL 16 with pgvector, so semantic search sits next to the relational data it searches. The part that bites later is not the embedding model. It is the index choice and one behavior of filtered queries that returns fewer rows than you asked for without any error.

#The two index types

pgvector offers two approximate indexes. HNSW builds a multilayer graph and searches it. IVFFlat divides vectors into lists and searches the nearest few. The pgvector documentation describes the tradeoff plainly: the graph index costs more to build and holds more in memory, and in exchange it gives better query performance for the recall you get.

#Creating and querying

The operator must match the operator class on the index, or Postgres will ignore the index. Cosine distance uses <=> with vector_cosine_ops.

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

SET hnsw.ef_search = 100;

SELECT id, content
FROM chunks
ORDER BY embedding <=> $1
LIMIT 10;

Index builds are faster when the graph fits in maintenance_work_mem, and the README warns against setting it so high that the server runs out of memory. Parallel builds are governed by max_parallel_maintenance_workers, which defaults to 2.

#The filtering trap

Real queries are scoped. In a multi-tenant platform every search carries an account id. With an approximate index, the scan returns its candidate set first and the filter runs afterwards. The README's own example: with ef_search at 40, a condition that matches 10% of rows yields about 4 rows on average. There is no error. Retrieval simply returns less context, and a RAG answer gets worse for reasons that are invisible in the logs.

• Raise ef_search. Widens the candidate set, at a cost in speed.
• Iterative scans. Setting hnsw.iterative_scan to strict_order or relaxed_order keeps scanning until enough rows survive the filter or hnsw.max_scan_tuples (default 20,000) is reached. Relaxed order allows slightly out-of-order results for better recall.
• Partial index. For a filter with few distinct values, index only those rows.
• Partitioning. For many distinct values such as tenant ids, partition by the filter column so each query touches one small index.
• B-tree first. If the filter matches a tiny fraction of rows, an ordinary index plus an exact scan of the survivors is both correct and fast.

#Measure recall yourself

Defaults are starting points. The reliable check is to run a sample of real queries twice, once with the index and once with the index disabled for an exact scan, and compare the top-k ids. If recall is low, raise ef_search or probes until it is not, then look at latency. Do this per filter shape, because a tenant-scoped query and an unscoped query behave differently.

#Related notes

• Supabase to Self-Hosted Postgres: A Production Migration
• LLM Model Routing to Keep Inference Costs Predictable