Vector indexes (HNSW and IVFFlat)

Without an index, a similarity search compares the query vector with every row. That's exact, and fine for a few thousand rows. Past that, each search gets slower in proportion to the table size, and on SupaHost it also uses CPU that other projects on the instance share.

A vector index makes search approximate: it finds the nearest rows very quickly but may occasionally miss one. The share of true nearest neighbours it finds is called recall, and every setting below trades recall against speed and memory.

HNSW or IVFFlat?

Use HNSW unless index build time or size is a real problem. It doesn't need retuning as the table grows.

Match the operator class to your query

An index only helps queries that use the same distance operator it was built for.

Indexes support up to 2,000 dimensions for vector and 4,000 for halfvec.

Create an HNSW index

For the recipes table used throughout this section, searched by cosine distance:

create index recipes_embedding_hnsw_idx
  on public.recipes
  using hnsw (embedding extensions.vector_cosine_ops);

Two build options control quality and cost:

create index recipes_embedding_hnsw_idx
  on public.recipes
  using hnsw (embedding extensions.vector_cosine_ops)
  with (m = 24, ef_construction = 100);

The defaults are a sensible start. Change them only after measuring recall on your own data.

Building on a shared instance

Index builds are fastest when the graph fits in maintenance_work_mem. Remember that your instance's RAM (4 GB on Builder, 8 GB on Pro, 16 GB on Business) is shared by Postgres and every project's services, so raise it modestly and only for the build:

begin;
set local maintenance_work_mem = '512MB';
set local statement_timeout = '30min';
create index recipes_embedding_hnsw_idx
  on public.recipes using hnsw (embedding extensions.vector_cosine_ops);
commit;

This runs in one go in Studio's SQL editor. It blocks writes to recipes until it finishes. On a busy table, use create index concurrently instead, which can't run inside a transaction: run it as the only statement in the SQL editor, or over psql through the SSH tunnel on Business. More in Indexes and Connections and timeouts.

If Postgres logs a notice that the graph no longer fits in maintenance_work_mem, the build continues but slows down considerably.

Tune HNSW at query time

hnsw.ef_search sets how many candidates a search considers. The default is 40. Higher values improve recall and cost time; it must be at least your limit to return that many rows.

Set it for one function rather than the whole database:

create or replace function public.match_recipes_precise(
  query_embedding extensions.vector(1536),
  match_count int default 20
)
returns table (id bigint, title text, distance float8)
language sql
stable
security invoker
set search_path = ''
set hnsw.ef_search = 100
as $$
  select r.id, r.title, r.embedding operator(extensions.<=>) query_embedding
  from public.recipes r
  order by r.embedding operator(extensions.<=>) query_embedding
  limit least(match_count, 100);
$$;

Filtered search and iterative scans

An HNSW scan returns the ef_search nearest candidates, and only then does Postgres apply your where clause. With a selective filter, such as one user's recipes out of a million, most candidates get thrown away and you can get fewer rows than you asked for.

From pgvector 0.8.0, iterative scans keep reading the index until enough rows pass the filter. Check your version with select extversion from pg_extension where extname = 'vector';, then enable them on the function:

create or replace function public.match_my_recipes(
  query_embedding extensions.vector(1536),
  match_count int default 10
)
returns table (id bigint, title text)
language sql
stable
security invoker
set search_path = ''
set hnsw.iterative_scan = relaxed_order
as $$
  select r.id, r.title
  from public.recipes r
  where r.user_id = (select auth.uid())
  order by r.embedding operator(extensions.<=>) query_embedding
  limit least(match_count, 50);
$$;

relaxed_order may return rows slightly out of distance order; use strict_order if exact ordering matters. hnsw.max_scan_tuples caps how far a scan goes. Other options for very selective filters:

• A partial index per common filter value: create index ... where cuisine = 'thai'.
• Partitioning by tenant or category, with an index per partition. See Table partitioning.
• No vector index for the filtered query: if the filter leaves only a few thousand rows, an exact scan over them using a B-tree index on user_id is fast and has perfect recall.

Create an IVFFlat index

IVFFlat clusters vectors into lists and searches only the closest clusters. Load your data first, because the clusters are learned from the rows present at build time.

create index recipes_embedding_ivf_idx
  on public.recipes
  using ivfflat (embedding extensions.vector_cosine_ops)
  with (lists = 100);

A common starting point is lists = rows / 1000 up to a million rows, and sqrt(rows) beyond that. At query time, ivfflat.probes (default 1) sets how many lists to search; try sqrt(lists):

set ivfflat.probes = 10;

Rebuild the index when the table has grown or changed a lot, for example with reindex index concurrently recipes_embedding_ivf_idx; run on its own.

Check the index is used

explain analyze
select id from public.recipes
order by embedding operator(extensions.<=>) (select embedding from public.recipes where id = 7)
limit 10;

You should see Index Scan using recipes_embedding_hnsw_idx. If you see Seq Scan and Sort, check that the operator matches the operator class and that the query has order by ... limit. On small tables Postgres may choose an exact scan anyway, because it's cheaper.

Index size counts toward your plan's database storage:

select pg_size_pretty(pg_relation_size('public.recipes_embedding_hnsw_idx'));

Related

• Faster vector search with an HNSW index
• Semantic search
• Query performance and EXPLAIN
• pgvector reference: github.com/pgvector/pgvector