pgvector Index Tuning: HNSW vs IVFFlat, Explained

by

pgvector Index Tuning: What Actually Matters

If you're running vector search on Postgres with pgvector, three decisions determine whether you get a search service you can reason about or one that quietly lies to you: which index type you pick, how you tune it, and how you handle filtered queries. Get the first two wrong and you eat unnecessary latency or memory. Get the third wrong and you'll ship something that returns four rows when you asked for ten — degrading retrieval quality by a margin nobody can quite explain, and throwing no errors at all while doing it. That one cost me a week once. It doesn't have to cost you one.

📖 Read the full guide: pgvector in Production: What the Quickstart Skips

▶ Watch the video walkthrough: pgvector: Postgres as your vector database
https://www.youtube.com/watch?v=aQV3wqdRMuQ

pgvector HNSW vs IVFFlat: Which Index to Pick

Both are approximate nearest neighbor indexes, and "approximate" is the operative word — neither guarantees exact recall, and no default configuration is tuned for your embedding distribution.

HNSW (Hierarchical Navigable Small World)

  • Better recall at comparable speed, especially as your dataset grows
  • No training/build step tied to data distribution — you can insert into it immediately
  • Slower to build and heavier on memory during index creation
  • The default choice for most new pgvector deployments

IVFFlat (Inverted File with Flat compression)

  • Faster and cheaper to build
  • Requires a representative sample of data before you build the index, or recall suffers badly on a cold/empty table
  • Needs periodic rebuilds if your data distribution shifts significantly
  • Still useful when memory is tight or build time is a hard constraint

If you're not sure, start with HNSW. Switch to IVFFlat only if build time or memory pressure forces the issue.

Tuning hnsw.ef_search for Recall vs. Speed

hnsw.ef_search controls how many candidate nodes HNSW examines at query time. Higher values mean better recall and higher latency; lower values mean faster queries and more missed neighbors.

SET hnsw.ef_search = 100;

Defaults are conservative and rarely match your actual data. Benchmark against a labeled recall set — even a rough one — at a few values (40, 80, 120, 200) and pick the point where recall stops improving meaningfully. Don't guess.

Tuning IVFFlat: Lists and Probes

IVFFlat splits vectors into lists clusters at build time, then searches only probes of them per query.

  • lists: rule of thumb is roughly rows / 1000 for smaller tables, trending toward sqrt(rows) as tables grow large. Too few lists means each cluster is huge and search is slow; too many means poor cluster quality and missed neighbors.
  • probes: how many of those lists get searched per query. Default is 1, which is almost never enough for good recall. Start at 10 and adjust based on your recall benchmark.
SET ivfflat.probes = 10;

Rebuild the index after major data growth — an IVFFlat index trained on 10K rows doesn't generalize well to 10M.

The Filtered Similarity Search Problem

This is the one that bites people. Combine a WHERE clause with a LIMIT on an ANN index, and older pgvector versions apply the vector search first, then the filter — meaning if your filter is selective, you can end up with fewer rows than you asked for, or worse, plausible-looking but suboptimal matches, with zero errors thrown.

pgvector's iterative index scan (available in recent versions) fixes this by re-scanning additional candidates when the filtered result set comes up short, instead of returning a truncated set silently. If you're on an older version or haven't enabled it, verify manually:

SET pgvector.enable_iterative_scan = 'relaxed_order';

Test filtered queries specifically — don't just benchmark unfiltered similarity search and assume filtered performance follows the same curve. It doesn't.

pgvector vs. Qdrant: When Postgres Is Enough

pgvector wins when you already run Postgres and want vectors alongside relational data — joins, transactions, filters, and backups all stay in one system. That's a real operational advantage: one database to tune, monitor, and back up instead of two.

Qdrant (or a dedicated vector database) pulls ahead when you need very large-scale ANN search with tighter latency SLAs, native support for hybrid search out of the box, or horizontal scaling patterns Postgres doesn't offer natively.

For most application-embedded search — RAG pipelines, recommendation features, semantic search bolted onto an existing product — pgvector is enough, and keeping vectors in Postgres avoids a second system to operate. If you're already spending time tuning query performance across Postgres instances, a tool like MyDBA can help you keep an eye on how these vector queries behave alongside the rest of your workload.

The vector Type and Quick Basics

CREATE EXTENSION IF NOT EXISTS vector;

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

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

Match your distance operator (vector_cosine_ops, vector_l2_ops, vector_ip_ops) to how your embeddings were trained — cosine similarity is standard for most text embedding models, but check your provider's docs before assuming.

Quick Checklist

  • Pick HNSW by default; use IVFFlat only for build-time or memory constraints
  • Tune hnsw.ef_search or ivfflat.probes against a real recall benchmark, not defaults
  • Rebuild IVFFlat indexes after significant data growth
  • Explicitly test filtered similarity search — enable iterative scan if you're on an affected version
  • Match your distance operator to your embedding model

Get those right and pgvector holds up fine at real-world scale. Skip the filtered-search step and you'll find out the hard way.

Leave a Comment