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_searchorprobesand 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_orderreturns results in exact distance order.relaxed_orderallows 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
- Start with HNSW and your distance operator's matching operator class.
- Test recall on real queries before tuning anything.
- If your queries have a
WHEREclause, enable iterative scans, or add a B-tree, partial index or partitioning for selective filters. - Scope settings with
SET LOCALrather than changing them globally. - 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.


