Advanced pgvector: Optimizing Vector Search

Many people adopt pgvector for the promise that “one Postgres database can handle everything”: relational data, JSONB, full-text search, and vector search all live in one database, keeping things simple. The demo stage really is smooth—a thousand documents, one <=> calculation, and top-10 results return within milliseconds.

But the story changes as data volume grows. A hundred thousand rows may still be fine, things start slowing down at 500,000, and at the million-row scale P99 latency can reach several seconds. You stare at the slow-query log in disbelief: it is only calculating cosine distance, so why is it so slow?

The answer is simple: you are not using an ANN index, so pgvector is performing a full-table scan. This article explains the key techniques for making pgvector fast and accurate in production.

1. Why the Default Full-Table Scan Does Not Scale

pgvector's distance operators (<=> for cosine/inner product, <-> for L2, and <#> for negative inner product) are not inherently tied to an index. Without an index, the following SQL dutifully scans every row, calculates its distance, and then sorts the results:

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

A table with 100,000 rows requires 100,000 distance calculations. At 1,536 dimensions and tens of microseconds per calculation, the total quickly reaches hundreds of milliseconds or even seconds. This is exact KNN search—100% accurate, but O(N×D), with linear scaling: double the data and latency doubles.

Production systems must switch to an ANN (Approximate Nearest Neighbor) index: sacrifice a small amount of recall, usually 1–3%, in exchange for latency reductions of tens of times. pgvector includes two ANN indexes: IVFFlat and HNSW. Which one you choose and how you tune it determine your performance ceiling.

2. IVFFlat vs. HNSW: Choosing Between Two ANN Indexes

The two indexes work in fundamentally different ways. You need to understand those differences before choosing one.

IVFFlat (Inverted File Flat): clustered buckets. When building the index, K-means first divides the vector space into N buckets. At query time, the index identifies several nearby buckets and performs an exact scan only within them. Each bucket is flat, hence IVFFlat.

• Advantages: fast index construction, low memory usage, and a configurable lists value for the number of buckets.
• Disadvantages: recall is extremely sensitive to parameters; poorly formed buckets miss relevant results, skewed data distributions perform badly, and data must already exist for training, so building the index on an empty table is pointless.
• Key parameters: lists (a common starting point is rows / 1000) and probes (the number of buckets examined at query time; higher values are more accurate but slower).

HNSW (Hierarchical Navigable Small World): a hierarchical graph. It organizes vectors into a multilayer, skip-list-like nearest-neighbor graph. Queries begin at a sparse entry point on the top layer, search progressively through lower layers, and quickly approach the target by following edges.

• Advantages: fast queries and high recall, insensitivity to data distribution, and support for building on an empty table, with the graph maintained as rows are inserted.
• Disadvantages: slow index construction, high memory usage because the graph itself needs to remain in RAM, and local graph rearrangement during writes.
• Key parameters: m (connections per node), ef_construction (search width while building the graph), and ef_search (search width during queries).

For a sense of scale, consider one million 768-dimensional vectors. Building an HNSW index may take more than ten minutes and consume several gigabytes of memory, but query P99 can remain in the single-digit milliseconds and Recall@10 can easily exceed 95%. IVFFlat may build more than twice as fast and use roughly one-third as much memory, but poorly tuned probes can push recall down to 85%, with more variable query latency as well. In RAG, that difference of more than ten recall points can determine whether an answer is correct.

How should you choose? In one sentence: choose HNSW when quality comes first, and consider IVFFlat only when resources are extremely constrained or writes are intensive. Since pgvector 0.5, HNSW has become the preferred approach in both the official documentation and community practice. Unless you are running on a small machine with only a few gigabytes of memory, or writes are so intensive that graph-rearrangement costs become unacceptable, default to HNSW. IVFFlat is genuinely suitable when data is static, the machine is small, and you are willing to repeatedly tune lists and probes against an evaluation set—a level of patience most teams do not have.

3. Tuning HNSW's Three Parameters

The entire behavior of HNSW comes down to three parameters. Remember one general rule: higher values improve accuracy, but they are also slower.

Build the index:

-- Recommended configuration for medium-scale data using 1,536-dimensional cosine distance
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

vector_cosine_ops is the operator class and must match the distance operator used by the query: use _cosine_ops with <=>, _l2_ops with <->, and _ip_ops with <#>. Using the wrong operator class makes the index ineffective, a classic pitfall.

Tune ef_search at query time. Note that it is a session-level GUC, not an index parameter:

SET hnsw.ef_search = 60;  -- Default is 40; increase it if recall is insufficient

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

In practice, m=16, ef_construction=200 is a safe starting point and sufficient for most use cases. If recall is below target, increase ef_search first because it only adds query-time cost and does not require rebuilding the index. If that is still insufficient, increase m and ef_construction and rebuild. Do not start with m=64; memory consumption and write latency will teach you the consequences.

4. Hybrid Search: Vector + Full Text with RRF Fusion

Pure vector search has a longstanding weakness: it often retrieves proper nouns, SKU numbers, and names poorly when exact wording matters. For a string such as PostgreSQL-15.4, a semantic model may decide that latest database version is the closest match, even though you actually need a literal match. Full-text search can fill that gap.

The greatest advantage of running pgvector on Postgres is that vector search and tsvector full-text search naturally live in the same database, so there is no need to add Elasticsearch. Run both retrieval methods and combine their results with RRF (Reciprocal Rank Fusion):

-- Vector retrieval: top 20
WITH vec AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rk
  FROM chunks
  ORDER BY embedding <=> $1
  LIMIT 20
),
-- Full-text retrieval: top 20
fts AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY ts_rank(tsv, q) DESC) AS rk
  FROM chunks, websearch_to_tsquery('chinese', $2) q
  WHERE tsv @@ q
  ORDER BY ts_rank(tsv, q) DESC
  LIMIT 20
)
-- RRF fusion: k=60 is an empirical default
SELECT id, SUM(1.0 / (60 + rk)) AS score
FROM (
  SELECT id, rk FROM vec
  UNION ALL
  SELECT id, rk FROM fts
) t
GROUP BY id
ORDER BY score DESC
LIMIT 10;

The beauty of RRF is that scores do not need to be normalized. Vector distance and ts_rank use completely different scales, so directly weighting and adding them is largely guesswork. Combining the reciprocals of their respective rankings is much more robust. For more detailed fusion strategies, refer to the earlier article on hybrid search; the example here provides a production-ready foundation.

5. Filter Before Searching: Do Not Waste Vector Calculations

Production searches almost always include conditions, such as restricting results to a particular product, time range, or tenant. A naive implementation puts the filters in the same SQL statement:

-- ❌ Dangerous: this may cause the HNSW index to degrade into a full scan
SELECT id FROM chunks
WHERE tenant_id = 42 AND status = 'published'
ORDER BY embedding <=> $1
LIMIT 10;

Why is this dangerous? HNSW is an unsupervised, graph-based approximate-search index, and it does not understand your WHERE clause. The optimizer has two possible execution paths: retrieve top-k results from the index and then filter them, which may leave zero rows and require another attempt, or filter the subset first and then sort it, which degrades into a full scan when the subset is large. If the candidate set after filtering is smaller than HNSW's ef_search, the index provides almost no benefit.

pgvector 0.8+ provides a remedy: push filtering into the index so rows are filtered before distances are calculated:

-- ✅ Use a > constraint so pgvector takes the "filter-first" path
SET hnsw.iterative_scan = strict_order;  -- Strictly ensure enough results remain after filtering
SET hnsw.max_scan_tuples = 20000;        -- Scan limit as a safeguard against runaway work

SELECT id FROM chunks
WHERE tenant_id = 42 AND status = 'published'
ORDER BY embedding <=> $1
LIMIT 10;

Rule of thumb: for highly selective filters that remove 90% or more of rows, always add a regular B-tree index such as CREATE INDEX ON chunks(tenant_id, status). Let the B-tree filter reduce the candidate set before HNSW searches within it. This often improves performance by an order of magnitude more than simply increasing index parameters. The principle is the same as metadata filtering: embeddings determine whether something is similar, metadata determines whether it is usable, and anything that can be rejected early should be rejected early.

6. Best Practices for Million-Scale Data

Once a dataset reaches millions of rows, parameter tuning alone is not enough. Several engineering decisions must also be correct:

1. Do not use more dimensions than necessary. 768 dimensions, as used by BGE-base and text-embedding-3-small, are sufficient for most use cases. 1,536 dimensions, as used by OpenAI ada/text-embedding-3, may be more accurate but double memory consumption. If 768 dimensions solve the problem, do not jump to 1,536. Index memory ≈ row count × dimensions × 4 bytes × 1.5 for HNSW overhead. Calculate the cost first.
2. Insert in batches and build the index afterward; never build it row by row. HNSW graph construction is expensive. Individual INSERT operations trigger graph rearrangement every time and can become painfully slow. The correct approach is to insert all data first without the index, or after dropping it, and then build it once with CREATE INDEX. Building an index for a million rows in bulk may take several to more than ten minutes; doing it row by row may take all night.
3. Separate writes from queries. Frequent writes cause repeated HNSW graph rearrangement and memory growth. In production, send writes through an offline or asynchronous path and queries to a read-only replica, or use a partitioning strategy such as TimescaleDB to shard by time.
4. Run REINDEX or rebuild periodically. After many deletions or updates, the HNSW graph develops “holes,” and recall quietly declines. Monitor it regularly and rebuild without downtime when necessary, as described in the article on zero-downtime vector-index rebuilding.
5. Disable JIT when appropriate. PostgreSQL JIT is often overhead for short vector queries. SET jit = off is frequently faster for pure vector-search workloads.

7. Monitoring: Do Not Tune by Instinct

Parameter tuning cannot rely on intuition. You must monitor three metrics:

-- Is the index actually being used?
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM chunks
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

If the execution plan contains Index Scan using ..._hnsw, the index is being used. If it shows Seq Scan, the index is doing nothing, usually because the operator class does not match or the optimizer abandoned the ef_search path.

• Query latency: monitor P95 and P99, not the average. HNSW occasionally has long-tail latency from graph rearrangement, so P99 reflects the actual user experience.
• Recall: you need an evaluation set. Keep 200 labeled query-relevant pairs, rerun them after each parameter change, and calculate Recall@10. Tuning without a recall metric is guesswork.
• Memory and disk: HNSW indexes reside in RAM. Use pg_relation_size to inspect disk usage and EXPLAIN ... BUFFERS to determine whether the system is constantly swapping pages.

Summary

• At scale, you must use an ANN index because the default full-table scan cannot handle much more than 100,000 rows. Choose HNSW when quality comes first, as recommended since pgvector 0.5, and consider IVFFlat only when memory is extremely constrained.
• HNSW has three key parameters: m (16–48), ef_construction (128–256), and ef_search (40–100). If recall is insufficient, tune ef_search first because it does not require rebuilding, then adjust m and ef_construction. The operator class must match the distance operator.
• Hybrid search combines pgvector with tsvector. RRF is robust because it does not require score normalization. Vectors find semantic similarity, while full-text search finds literal matches; they complement each other.
• Filter before searching: create B-tree indexes for highly selective conditions to reduce the candidate set before running HNSW. pgvector 0.8+ supports pushing filters into the index.
• Million-scale systems require engineering discipline: choose 768–1,536 dimensions, insert in batches and build the index once, separate reads from writes, rebuild periodically, and disable JIT when necessary. Monitor with EXPLAIN ANALYZE, P99 latency, and a Recall@10 evaluation set, not intuition.

Key takeaway: pgvector is not a switch that becomes fast when installed; it becomes fast only when tuned correctly. Index selection determines the floor, parameters and filtering strategy determine the ceiling, and the evaluation set tells you whether you are tuning in the right direction.