vvek

Vector search in Postgres with pgvector: choosing HNSW or IVFFlat, and making filters work

How pgvector's HNSW and IVFFlat indexes differ, which settings matter, and how iterative scans stop filtered similarity queries from returning too few rows.

Adding vector search to an app that already uses Postgres is appealing: one database, one backup story, and your similarity queries can sit next to ordinary SQL. The pgvector extension makes that possible. The part that surprises people is not the search itself. It is the index choice, and what happens when you add a WHERE clause.

This post walks through both, using the settings and defaults from the pgvector README.

The basic query

You store an embedding in a vector column and order by distance:

CREATE EXTENSION vector;

CREATE TABLE items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id bigint NOT NULL,
  body text NOT NULL,
  embedding vector(1536)
);

SELECT id, body
FROM items
ORDER BY embedding <=> $1
LIMIT 5;

Without an index, Postgres checks every row. That's exact and works fine for thousands of rows. Beyond that, you'll want an approximate index: it finds close matches fast, but might miss a few. That trade-off is called recall.

The operator must match the index. <-> is L2 distance, <=> is cosine distance, <#> is negative inner product and <+> is L1. If you use cosine in queries, build the index with vector_cosine_ops.

Two index types

pgvector offers HNSW and IVFFlat.

HNSW IVFFlat
Speed versus recall Better Lower
Build time and memory Slower, more memory Faster, less memory
Needs data first No, works on an empty table Yes, it trains on existing rows
Main build settings m, ef_construction lists
Main query setting hnsw.ef_search ivfflat.probes

For most apps, HNSW is the easier starting point - it works right away, even on empty tables. IVFFlat is worth a look when build time or memory is the tight constraint.

HNSW

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

Two build settings exist: m (connections per layer, default 16) and ef_construction (candidate list size while building, default 64). Higher values improve recall and slow the build. The defaults are a sensible place to begin.

At query time, hnsw.ef_search controls how many candidates are considered. The default is 40. Raise it for better recall at the cost of speed:

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

SET LOCAL keeps the change inside that one transaction, which is tidy when different queries need different trade-offs.

IVFFlat

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

IVFFlat splits vectors into lists groups, then searches only a few of them. Because it learns those groups from your data, create it after the table has rows. The README suggests starting lists at rows divided by 1000 for up to a million rows, and the square root of the row count above that.

At query time, ivfflat.probes is the number of lists searched. The default is 1, which is fast and often too low. A starting point is the square root of lists. Setting it equal to lists gives exact results, but then the index is not helping.

Tip

Measure recall yourself. Take a few hundred real queries, run them once with the index and once exact (for example, with the index disabled inside a test transaction), and compare the top results. Then change ef_search or probes and see what it costs.

The filtering trap

Here is the query most real applications need: similar items, but only for one organisation.

SELECT id, body
FROM items
WHERE org_id = 42
ORDER BY embedding <=> $1
LIMIT 10;

An approximate index applies the filter after the index scan. With HNSW and the default ef_search of 40, the index hands back about 40 candidates. If only a small share of all rows belong to organisation 42, most of those 40 are thrown away, and you may get three rows instead of ten. No error, just fewer results.

This is easy to miss in development, where one tenant owns most of the data, and easy to hit in production.

Iterative scans

Since pgvector 0.8.0, the index can keep scanning until it finds enough matching rows. You enable it per session or transaction:

BEGIN;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SELECT id, body
FROM items
WHERE org_id = 42
ORDER BY embedding <=> $1
LIMIT 10;
COMMIT;

There are two modes:

  • strict_order returns results in exact distance order.
  • relaxed_order allows results to be slightly out of order, in return for better recall.

IVFFlat supports relaxed_order. If you need exact ordering with relaxed scanning, the README suggests re-sorting the results yourself, for example in an outer query.

Iterative scans are bounded, so they cannot run forever. For HNSW, hnsw.max_scan_tuples limits how many index tuples are visited (default 20,000), and hnsw.scan_mem_multiplier lets the scan use a multiple of work_mem (default 1). For IVFFlat, ivfflat.max_probes is the cap. If a filter is extremely selective, the scan can hit the limit and still return fewer rows than you asked for. That is expected behaviour, so design for it.

Note

Check your installed version with SELECT extversion FROM pg_extension WHERE extname = 'vector';. Iterative scans need 0.8.0 or later, and managed services sometimes lag behind the latest release.

When a filter is very selective

For filters that match few rows, a different approach is usually better than leaning on the vector index. The README lists a few:

  • A B-tree index on the filter column. For a tiny slice, Postgres may choose to filter first and compute exact distances on the few rows left, which is both fast and perfectly accurate.
  • A partial index, when the filter has only a few distinct values: CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WHERE (category_id = 123);
  • Partitioning, when there are many distinct values, so each partition has its own smaller index.

If your tenants are isolated with row-level security, the same filtering issue applies, since the policy adds a WHERE condition to every query. Our guide to row-level security in Postgres covers that pattern. And for the general idea of which columns deserve an index, how database indexes speed up reads is a good refresher. Use EXPLAIN ANALYZE to see which index the planner actually picked.

Filtering by distance

Sometimes you want "everything within this distance" rather than "the nearest ten". Adding WHERE embedding <=> $1 < 0.3 directly can interact badly with the index. The README's advice is to compute the ordered, limited result in a materialized CTE and apply the distance condition outside it:

WITH nearest AS MATERIALIZED (
  SELECT id, embedding <=> $1 AS distance
  FROM items
  ORDER BY distance
  LIMIT 50
)
SELECT * FROM nearest WHERE distance < 0.3;

A short checklist

  1. Start with HNSW and your distance operator's matching operator class.
  2. Test recall on real queries before tuning anything.
  3. If your queries have a WHERE clause, enable iterative scans, or add a B-tree, partial index or partitioning for selective filters.
  4. Scope settings with SET LOCAL rather than changing them globally.
  5. Always check that you get as many rows as you asked for, and handle the case where you do not.

None of this is unusual. It's just standard Postgres practice: read the plan, measure on real data, and tweak one setting at a time.

Sources

Keep reading

A short email when we publish. Confirm once, unsubscribe any time.