pgvector: HNSW or IVFFlat? Optimizing vector search for your n8n RAG

Your n8n RAG answers well, but every question takes several seconds to query Supabase? The usual suspect is neither the embedding model nor the LLM, but the pgvector table itself: without an index, every search compares the question's vector against every row in the table. As long as the database holds a few thousand chunks, nobody notices; at a hundred thousand, every query becomes a full scan. pgvector offers two approximate index types, IVFFlat and HNSW, with different trade-offs between build speed, memory, latency and result quality. This article compares them concretely, in the context of an n8n RAG pipeline like the one in our RAG with Supabase guide.

Without an index: exact search and its limits

By default, a similarity query on a vector column triggers an exact sequential scan: PostgreSQL computes the distance between the query vector and every row, then sorts. Two consequences:

• Quality is perfect: the k nearest neighbors returned are, by definition, the true k nearest neighbors.
• Cost is linear: query time grows with the number of rows and the vector dimension (1536 dimensions for the embeddings commonly used in RAGs).

In practice, up to a few thousand chunks — a starter document RAG fed by our PDF ingestion to Supabase pgvector workflow — the exact scan stays within comfortable latencies and there is no reason to create an index. It's beyond that point, when the table grows and search latency becomes noticeable in the chatbot's overall response time, that approximate nearest neighbor (ANN) search comes into play.

IVFFlat: partition to search less

IVFFlat splits the vector space into lists (clusters computed by k-means over the existing data). At query time, the index identifies the lists whose centroid is closest to the question's vector, and only compares against the vectors in those lists. This family of partition-based approaches descends from the foundational work on quantization, formalized in a 2011 study published in IEEE TPAMI (Jégou, Douze & Schmid, "Product Quantization for Nearest Neighbor Search" — Google Scholar) — IVFFlat is a simplified descendant: it partitions but stores vectors "flat," without compression.

Two parameters steer the trade-off:

• lists (at creation): the number of partitions. More lists = finer partitions, hence faster queries, but a higher risk of missing neighbors that sit just across a cluster boundary.
• probes (at query time, via SET ivfflat.probes = …): the number of lists explored. Raising probes improves recall and slows the query; at the extreme, probing every list amounts to an exact scan.

IVFFlat's structural weakness: centroids are computed at index creation time, from the data present at that moment. Two direct implications for a RAG:

• creating the index on an empty or nearly empty table makes no sense — you must ingest a representative volume first, then create the index;
• if the corpus shifts substantially (a very different new document collection), the centroids become less representative and recall degrades: you then need to rebuild the index.

In exchange, IVFFlat builds fast and uses less memory than HNSW — a real advantage on modest Supabase instances.

HNSW: a multi-layer graph

HNSW (Hierarchical Navigable Small World) builds a multi-layer proximity graph: the upper, sparse layers allow fast long-range traversals; the lower, dense layers refine the search locally. A query starts at the top and descends layer by layer, greedily moving closer to the target vector. The algorithm comes from a study published in IEEE TPAMI (Malkov & Yashunin, 2018, "Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs" — Google Scholar), which notably shows that this hierarchical structure offers an excellent recall-latency trade-off, robust even on high-dimensional data.

Three parameters to know:

• m (at creation): the maximum number of connections per node in the graph. Higher = better recall, but a bigger index and a slower build.
• ef_construction (at creation): the size of the candidate list explored during construction. Higher = a better-quality graph, a longer build.
• ef_search (at query time, via SET hnsw.ef_search = …): the size of the candidate list at query time. This is the main recall-latency knob in production.

HNSW's strengths for a RAG: no data needed at creation time (the index builds incrementally as rows are inserted, which fits continuous ingestion well), and generally higher recall than IVFFlat at equal latency. Its costs: a noticeably slower build on a large existing corpus and higher memory consumption, to be factored into instance sizing.

Recall vs latency: accepting approximation

What both indexes share — and the classic surprise when moving from exact scan to ANN: results can differ from the exact search. A relevant chunk can drop out of the top-k because it sat in an unprobed list (IVFFlat) or off the explored path in the graph (HNSW). For a RAG, that translates into a missing passage in the context, hence potentially a worse answer — which is why you should measure, not just time:

• build a small reference set of questions whose expected passages you know;
• compare indexed results against the exact scan on that set;
• tune probes or ef_search until recall is acceptable, while watching latency.

Practical recommendations for an n8n + Supabase RAG

• Start without an index. Under a few thousand chunks, the exact scan is fast and perfect in quality.
• Pick HNSW by default when an index becomes necessary: no pre-existing data constraint, a better recall-latency trade-off, and a RAG's continuous ingestion (new documents every week) doesn't degrade its structure the way it throws off IVFFlat's centroids. Reserve IVFFlat for cases where memory is tight and the corpus stable.
• Stay consistent on the operator. For LLM embeddings, cosine distance (<=>) is the standard; the index must be created with the matching cosine operator class, otherwise PostgreSQL won't use it for your queries.
• Fix the dimension at table creation (vector(1536) for example, depending on your embedding model) and never change it: mixing embeddings from different models in the same table makes distances incomparable. If you switch embedding models, re-ingest everything and rebuild the index.
• Create the index during off-peak hours on a large corpus (HNSW construction is resource-hungry), and consider building it in a non-blocking way so writes aren't frozen during the operation.

Where this plugs into n8n

In an n8n workflow, all of this stays invisible at the node level: the Supabase Vector Store node (used in insert mode for ingestion, in retrieval mode for question answering) queries the pgvector table through a similarity function on the Supabase side — it's that underlying SQL query that benefits from the index, with no change to the workflow. Concretely:

• the PDF ingestion to Supabase pgvector workflow feeds the table (see also our guide on connecting n8n to Supabase);
• the RAG question-answering API and the RAG chatbot with citations query the same table — their latency is what drops once the index is in place;
• the agent's conversation memory is a separate concern: don't store chat history in the documents table, as the two usages share neither lifespan nor query patterns.

One last point of caution: a fast index cuts search latency, not the latency of embedding model calls — if your bulk ingestion runs into 429 errors, the fix lives on the AI API rate limiting side, not in pgvector.

The RAG Assistant Pack (€119) delivers exactly this chain — ingestion, pgvector table, question-answering API and chatbot with citations — as importable JSON: the table structure and similarity queries are already consistent, all that's left is choosing the right index once your corpus justifies it.