Vector Databases

The 30-second version

You embed your documents once and store the vectors. When a user searches, you embed their query and ask for the 10 nearest stored vectors: those are the documents most similar in meaning. That's the core of semantic search and of retrieval-augmented generation.

The catch is scale. Checking every stored vector is simple and always correct, but with millions of vectors it is too slow to do on every request (§02). So vector databases build an approximate index that checks only a small fraction of them.

The most popular approximate index, HNSWHNSW (Hierarchical Navigable Small World)The most widely used approximate vector index: a graph where each vector is linked to a few close vectors, stacked in layers that get sparser towards the top. A search walks the graph from the top layer down, always moving closer to the query., works exactly like asking for directions: it is a network of points where each one links to a few nearby points, and a search hops from point to point, always moving closer. Here is the difference on a tiny map of 38 real glossary terms:

Both found the same nearest point, but the approximate search measured only about a third of the points. That gap grows with every vector you add. The price: once in a while the hops lead to a point that is close, but not the closest. You will step through a search yourself in §06, including one that gets it wrong.

Exact search: compare with everything

The problem, stated precisely

You have N stored vectors, each with d numbers (its dimensionsDimensionHow many numbers a vector has. [3, 1] is 2-dimensional; an all-MiniLM-L6-v2 embedding is 384-dimensional. Each position is one dimension.). A query arrives as one more vector with d numbers. You want the k stored vectors that are most similar to it: the k nearest neighborsNearest neighbors (top-k)The k stored vectors most similar to a query vector, for example the 5 with the highest cosine similarity. Finding them by comparing with every stored vector is called brute-force or exact search., or "k-NN" for short. Typical values: N = 1 million documents, d = 384 or 1,536, k = 10.

"Most similar" means highest Cosine similarityCosine similarityA score from −1 to 1 for how closely two vectors point in the same direction, ignoring their length. For embeddings, higher means more similar meaning. For unit-length vectors it equals the dot product (multiply matching numbers and add them up).. When every vector has length 1 (most embedding models normalize their output, and MiniLM's are), cosine similarity is simply the Dot productDot productMultiply two vectors' matching numbers and add the results: [3, 1] · [2, 2] = 3×2 + 1×2 = 8. Large when the vectors point the same way and are long; 0 when they are at right angles.: multiply the matching numbers and add them up. Databases usually talk about distance instead, where smaller is closer: cosine distance = 1 − cosine similarity.

What one comparison costs

A dot product of two 384-number vectors is 384 multiplications, each added to a running total. Each of those steps is one Multiply-addMultiply-addOne multiplication followed by one addition: s += a[i] * b[i]. Comparing two 384-number vectors with a dot product takes 384 of them, so it is the natural unit for counting the work of a vector search.:

for i := range a {
	s += a[i] * b[i] // one multiply-add; for 384 dimensions this line runs 384 times
}

Exact searchExact (brute-force) k-NN searchFinding the k nearest vectors by measuring the distance from the query to every stored vector and keeping the k best. Always correct, but the work grows in step with the number of vectors. does one such comparison per stored vector. So the work per query is simply:

work per query = N × d multiply-adds

  10,000 vectors × 384 dims =         3,840,000   (3.8 million)
 1,000,000 vectors × 384 dims =     384,000,000   (384 million)
1,000,000 vectors × 1,536 dims = 1,536,000,000   (1.5 billion)

Turning that into time

We measured a plain Go loop (like the one above) on our test machine, a 4-core cloud container: it scanned 1,000,000 vectors × 384 numbers in about 0.5 seconds, roughly 1.3 nanoseconds per multiply-add, on one CPU core. Your numbers will differ, but the shape won't:

• 0.5 s per query is far too slow for a search box. Users notice anything over about 100 ms.
• At 50 queries per secondQPS (queries per second)How many requests a system handles each second, the basic unit of load in capacity planning. 1 million requests a day is only about 12 QPS on average, but traffic is never spread evenly, so designs plan for the peak., you'd need 50 × 0.5 = 25 CPU cores busy doing nothing but this.
• Every query also reads all the vectors from memoryRAM (main memory)The computer's working memory: fast (~100 ns per access) but lost when the power goes off. Programs and their data live here while running.: 1,000,000 × 384 × 4 bytes = 1.5 GB per query. At 50 QPS that's 77 GB every second. One CPU core can pull only tens of GB per second from memory (its Memory bandwidthMemory bandwidthHow many bytes per second can flow between RAM and the CPU (tens of GB/s on a typical server core). Streaming through a big array is limited by bandwidth; jumping around is limited by latency.), so the scan is limited by memory as much as by arithmetic, and you'd need many cores just to read the data fast enough.

Optimized code helps a lot. SIMDSIMDSingle Instruction, Multiple Data: CPU instructions that do the same operation on several values packed side by side, e.g. compare 8 or 16 bytes against one value at once. instructions do 8–16 multiply-adds at once, and libraries built for this are roughly ten times faster than our plain loop. But a 10× faster scan of 10× more data is right back where you started. The cost still grows linearly: double the vectors, double the work.

When exact search is the right answer

Don't skip past this. For up to tens of thousands of vectors, exact search is often the best choice:

• 10,000 × 384 multiply-adds takes about 5 ms even with our plain loop.
• It has perfect recall (it never misses, §05), no index to build, tune or keep in memory, and no surprises with filters (§09).
• pgvector does exactly this when a table has no vector index (§10).

The rest of this page is about what to do when N gets too big for that.

Brute-force k-NN in Go and JavaScript

Our test data for the whole page: the 3,173 glossary definitions on this site (like "Race condition: A bug where the result depends on the timing…"), each turned into a 384-number vector by the all-MiniLM-L6-v2 embedding model, run offline. We saved them to docs.json as [{ "key", "text", "vec": [384 numbers] }, …], and embedded one question into query.json.

// package main and imports (encoding/json, fmt, os, slices) omitted

type Doc struct {
	Key  string    `json:"key"`
	Text string    `json:"text"`
	Vec  []float32 `json:"vec"`
}

type Hit struct {
	Key   string
	Score float32
}

// dot multiplies matching numbers and adds them up. For unit-length
// vectors (MiniLM's are) this IS the cosine similarity.
func dot(a, b []float32) float32 {
	var s float32
	for i := range a {
		s += a[i] * b[i] // ← one multiply-add per dimension
	}
	return s
}

// exactKNN compares the query with every vector: N × d multiply-adds.
func exactKNN(docs []Doc, q []float32, k int) []Hit {
	hits := make([]Hit, 0, len(docs))
	for _, d := range docs {
		hits = append(hits, Hit{d.Key, dot(q, d.Vec)})
	}
	slices.SortFunc(hits, func(a, b Hit) int { // highest similarity first
		if a.Score > b.Score {
			return -1
		}
		if a.Score < b.Score {
			return 1
		}
		return 0
	})
	return hits[:k]
}

func main() {
	var docs []Doc
	var q Doc
	load("docs.json", &docs) // 3,173 glossary entries, 384 numbers each
	load("query.json", &q)   // load = os.ReadFile + json.Unmarshal

	fmt.Printf("query: %q\n", q.Text)
	for i, h := range exactKNN(docs, q.Vec, 3) {
		fmt.Printf("%d. %-15s %.3f\n", i+1, h.Key, h.Score)
	}
	fmt.Printf("work: %d vectors × %d dims = %d multiply-adds\n",
		len(docs), len(q.Vec), len(docs)*len(q.Vec))
}

Both print exactly this (real output, Go 1.24 and Node 22):

query: "my program crashes because two threads write the same variable"
1. race-condition  0.544
2. data-race       0.471
3. thread          0.449
work: 3173 vectors × 384 dims = 1218432 multiply-adds

Notice the scores: the best match is only 0.544, and the third is 0.449. Similarity scores for real text are rarely close to 1, and the gap between "right" and "pretty close" is small. Keep that in mind; it's why approximate search can slip (§04).

Two easy improvements

• Don't sort everything. Sorting all N scores to keep 10 is wasteful. Keep a small Top-KTop-KFinding the K largest (or smallest) items out of many, e.g. the 10 slowest requests out of a billion. Classic trick: a min-heap of size K whose root is the item to beat. min-heap of size k instead: Heaps §08 shows how. It saves the sort, but not the N × d multiply-adds, which are the real cost.
• Store vectors in one flat array. A single []float32 of N × d numbers (or a Float32Array in JavaScript) keeps the data contiguous in memory, which the CPU reads much faster than thousands of separate small arrays. Our 0.5-second benchmark in §02 used this layout.

Neither changes the basic fact: every query touches every vector. To do less work, you need an index.

Why a B-tree can't help

What a B-tree is good at

A B-treeB-treeA wide, shallow, always-sorted tree that most databases use for tables and indexes. Each node fills one disk page and holds hundreds of keys, so finding one row among millions takes only 3–4 page reads. index keeps values sorted, so finding email = 'ada@example.com' or price BETWEEN 10 AND 20 takes 3–4 page reads even among millions of rows. Indexes & B-Trees §03 shows how. Everything it does relies on one fact: values that are close in the sort order sit next to each other in the index.

Why sorting fails for vectors

Try it in your head with 2-D points first. Sort them by their x coordinate and look up the query's x. The points right next to it in the sort order have a similar x, but their y could be anything: one is around the corner, one is across town. Sorting by one coordinate ignores all the others.

A 384-dimension embedding has 384 coordinates, and the meaning is spread across all of them. No single number, and no fixed combination like "sort by coordinate 1, then 2" (that's what a composite index does), keeps similar vectors next to each other. So a B-tree can't narrow the search, and the database falls back to a Full table scanFull table scan (Seq Scan)Reading every page of a table from start to end and checking each row against the WHERE condition. Postgres calls it a Seq Scan (sequential scan). Fine for small tables, slow for big ones.: exact search.

The curse of dimensionality

For 2-D or 3-D data there are good tricks. Maps and geographic databases split space into boxes (k-d trees, R-trees, Postgres's GiSTGiST indexGeneralized Search Tree: a Postgres index framework for data that doesn't sort in a line — shapes, map coordinates, ranges — answering “overlaps” or “nearest to” questions. indexes) and only search the boxes near the query. Why not do that in 384 dimensions?

Two effects, together called the Curse of dimensionalityCurse of dimensionalityA family of effects that appear when points have hundreds of coordinates: distances between random points become almost equal, and tricks that work on a 2-D map (sorting, splitting space into boxes) stop narrowing the search., get in the way:

1. Too many boxes. Cut each axis in half once and 2-D space has 4 boxes. 384-D space has 2³⁸⁴, a number with 116 digits. Nearly every box is empty, and a query's true neighbors are scattered across many of them.
2. Everything is about equally far away. In high dimensions, the nearest and the farthest point end up at almost the same distance, so "only search nearby boxes" stops ruling much out.

Here is the second effect, measured. On the left, random points; on the right, our 3,173 real MiniLM vectors:

Read the right-hand chart carefully. The best match for a typical query scores about 0.58, the 10th-best about 0.43, and the 100th-best about 0.30, while half of all vectors score above about 0.1. The true top 10 are only slightly ahead of hundreds of runners-up. Any shortcut that skips vectors has a real chance of skipping one of the winners.

Approximate search and recall@k

The deal

An ANN index is built ahead of time, when vectors are inserted. At query time it uses that structure to jump to the promising region and measure only a few hundred or few thousand vectors, instead of all N. On our 3,173-vector test set, a typical HNSW query made about 500 distance computationsDistance computationMeasuring how far one stored vector is from the query (a dot product or similar over all its dimensions). Counting these is a fair, machine-independent way to compare search methods. and still found 98% of the true top 10 (§07 lets you explore this).

The price is that sometimes a true neighbor is never measured, so it can't be returned. The results still look perfectly plausible: you get 10 rows, sorted by distance. Nothing tells you a better match existed. So you have to measure how often this happens.

Recall@k, defined

Recall@kRecall@kOf the k true nearest neighbors (found by exact search), what fraction did the approximate search return? Returning 9 of the true top 10 is recall@10 = 0.9. Averaged over many test queries. compares the approximate answer with the exact one (from brute-force search, §03):

recall@k = (how many of the true top k the index returned) ÷ k

A worked example. The true top 5 are real exact-search results from our data; the "approximate" list is hypothetical, made up to show the arithmetic:

One query tells you little. You measure recall on many queries (a few hundred is typical) and look at two numbers:

• Average recall, e.g. 0.98: "on average, 98% of the true top 10 came back."
• Worst-case recall: the average can hide queries that fail badly. In our test, at a low setting HNSW averaged 0.91, but the worst query got only 5 of its true top 10: the search got stuck among the wrong group of vectors (§06 shows how this happens, §07 shows the query).

What recall doesn't tell you

• It's measured against your embeddings, not against the truth. If the embedding model thinks "chargeback" is the best match for "stop a payment being charged twice" (it did, in our data; the better answer, "idempotency key", ranked 25th), exact search returns that too, with recall 1.0. Recall only measures the index. Whether the results help the user is a separate question about embeddings, chunking and ranking; see Embeddings and RAG.
• A miss usually costs little. If the index returns the true #11 instead of the true #9, the user rarely notices. Losing the true #1 matters more. That's why "recall ≈ 0.95–0.99" is a common target, not 1.0.

HNSW: a graph you can walk

We'll build the idea in three steps, each fixing a problem with the one before.

Step 1: a graph of neighbors, searched greedily

Picture every stored vector as a dot, and draw a line from each dot to a handful of its nearest dots. That's a GraphGraphA set of nodes (vertices) connected by edges — cities and roads, users and friendships, tasks and their dependencies. Edges can have directions and weights.: the dots are nodesNode (vertex)One “thing” in a graph: a city, a person, a package, a web page. Vertex is the mathematician's word for the same idea., the lines are edgesEdgeA connection between two nodes: a road, a friendship, an import, a link. An edge can have a direction (an arrow) and a weight (a number). (or "links").

To search, start at any node and repeat: measure the distance from the query to each of the current node's neighbors, and move to the closest one, if it's closer than where you are. When no neighbor is closer, stop. That's Greedy searchGreedy search (graph)At each step, look at the current node's neighbors and move to whichever is closest to the target; stop when no neighbor is closer. Fast, but it can stop at a node that is only the best in its neighborhood (a local minimum)., and it measures only the nodes along the way, not all N.

Two things go wrong:

• Long trips. If every link is short, crossing from one side of the data to the other takes hundreds of tiny hops.
• Dead ends. You can stop at a node that is closer than all of its neighbors but still isn't the closest overall, like reaching the top of a small hill in fog and thinking it's the summit. This is called a local minimum.

Step 2: layers, for long trips

HNSW stands for Hierarchical Navigable Small World. "Small world" is the "six degrees of separation" idea: a Small-world graphSmall-world graphA graph where most links are short (to nearby nodes) but a few are long, so you can get from any node to any other in a handful of hops, like the “six degrees of separation” between people. has mostly short links plus a few long ones, so any node can reach any other in a few hops. HNSW gets its long links by stacking layers:

• Layer 0 contains every vector, each linked to its close neighbors.
• Layer 1 contains a random small fraction of them, linked to each other. Because there are fewer nodes, "nearest neighbor on this layer" is often far away: these are the long links.
• Layer 2 has fewer still, and so on. Real indexes over millions of vectors have 4–6 layers.

A search starts at the Entry pointEntry point (HNSW)The fixed node, sitting on the top layer, where every HNSW search starts. Usually the first vector that was given the highest layer. on the top layer and searches greedily there. When it can't get closer on that layer, it drops down to the same node on the layer below and continues. Think of driving: motorway to the right region, main road to the right neighbourhood, side streets to the door.

Step 3: a candidate list, for dead ends

On layer 0, where the final answer is found, HNSW doesn't keep just one "current best" node. It keeps a sorted Candidate listCandidate list (beam)The best few points a graph search has found so far, kept sorted by distance. The search keeps expanding the closest unexplored one until nothing nearer can be found. Its size is ef_search. of the best few nodes found so far, and keeps exploring the neighbors of the closest one it hasn't explored yet. It stops when the closest unexplored candidate is farther than the worst node on the list: from there, nothing nearer can be reached.

The list's size is called ef_searchef_search (HNSW)The length of the candidate list a search keeps while it walks layer 0. Bigger: higher recall, more distance computations per query. It also caps how many rows one index scan can return. pgvector's default is 40.. A longer list explores more side roads, so it escapes more dead ends (higher recall), but it measures more vectors (slower). It's the main knob you'll turn (§07).

Try it

The same 38-point map as in §01. Step through the race question, then the tricky spot with ef_search 3 (it fails), then the tricky spot with ef_search 6. You can also click anywhere on the map to drop your own query.

What to notice:

• Each layer narrows the region. On the top layer the search jumps across the map; on layer 0 it only takes short steps.
• The tricky spot fails at ef_search 3. The query sits in a gap between two groups. The walk arrives from the top, reaches the group on one side of the gap first, and the short list fills up with points from that group. The true nearest points are in the other group, and nothing on the list links there. With a list of 6 the search explores enough extra neighbors to cross over.
• Distance computations count the real cost. On this toy the saving over 38 is small; on our 3,173 real vectors it's about 6×, and on millions it's hundreds of times.

How the graph gets built

Vectors are inserted one at a time, and each insert uses the search you just watched:

1. Pick the new vector's top layer at random, like rolling dice: most vectors get only layer 0, roughly 1 in M also get layer 1, roughly 1 in M² also get layer 2. (M is the links-per-node setting, §07.) This is what keeps upper layers sparse.
2. Search for its nearest neighbors on each of its layers, with a candidate list of size ef_constructionef_construction (HNSW)How many candidates the index looks at while choosing a new vector's neighbors during the build. Bigger: a better-connected graph, slower inserts. pgvector's default is 64..
3. Link it to the closest MM (HNSW)How many links each node gets per layer when it is inserted (layer 0 allows twice as many). Bigger M: better recall and more robust graphs, but more memory and slower builds. pgvector's default is 16. of them, in both directions. If a neighbor now has too many links, it drops its farthest ones.

Because inserting is "search, then link", there's no separate training step: you can create an HNSW index on an empty table and insert as you go. That's a real advantage over IVF (§08). The cost is that every insert does a search, so building an index over millions of vectors takes minutes to hours (§12).

M, ef_construction, ef_search: the three knobs

Measured on real data

We built an HNSW over our 3,173 real MiniLM vectors and ran 199 real test queries (section summaries from other topics), comparing each result with exact search. Every number below is measured, not estimated. Move the slider, then switch to M and to IVF (explained in §08):

What the measurements say

• ef_search has diminishing returns. Going from 10 to 40 raised average recall from 91% to 98% for about twice the work. Going from 40 to 400 bought the last 2% for four times more. Most teams settle somewhere in the "knee" of that curve.
• Watch the worst query, not just the average. At ef_search = 10, the worst query found only 5 of its true top 10, and it stayed at 5 at ef_search = 16 before jumping to 9 at 24. It was a vague cheat-sheet summary, "The whole topic on one screen." Vague queries are the hardest: nothing matches them strongly, so their true neighbors are scattered across the graph instead of sitting in one tight group the walk can find.
• M costs memory and build time. M = 32 reached 99.5% recall at ef_search = 40, but each vector stored about twice as many links as with M = 16, and building took 2.6 times more distance computations. M = 4 was cheap and found only 79%.

The same thing in pgvector

On the same vectors and queries, pgvector 0.8.7's HNSW index (m = 16, ef_construction = 64) measured:

It's a little better than our teaching version at every setting because it chooses neighbors more carefully (§06), but the shape is the same. Changing ef_search is a plain setting, so you can raise it for one query that needs high recall:

BEGIN;
SET LOCAL hnsw.ef_search = 100;   -- only for this transaction
SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

A tuning recipe

1. Build with the defaults (M = 16, ef_construction = 64).
2. Measure recall@10 on a few hundred real queries (your golden set, §05).
3. If recall is too low, raise ef_search until it's high enough, and check latency at your expected load.
4. Only if ef_search gets uncomfortably high, rebuild with a larger M or ef_construction. That costs memory and build time, so measure again.

IVF lists and quantization

How IVF works

Build (once, needs data):

1. Choose a number of lists, say 1,000.
2. Run k-meansk-meansA simple clustering method: pick k starting centres, put every point in the group of its nearest centre, move each centre to the average of its group, and repeat until the groups stop changing. on (a sample of) your vectors to find 1,000 centroidsCentroidThe average position of a group of points: add up their vectors number by number and divide by how many there are. In IVF, each list is represented by its centroid.: centre points that each represent one region of the data.
3. Put every vector in the list of its nearest centroid.

Search:

1. Compare the query with all 1,000 centroids (cheap: 1,000 comparisons).
2. Pick the nearest few lists. How many is the probes / nprobeprobes / nprobe (IVF)How many of the nearest lists an IVF search scans. 1 is fastest but misses neighbors that sit just across a list boundary; scanning all lists is the same as exact search. setting.
3. Compare the query exactly with every vector in those lists, and keep the best k.

The name comes from search engines: an "inverted file" maps each bucket to the items in it, like a book index maps each word to its pages.

Try it

Our 38-point map split into 4 lists. The transaction question is selected, with probes 1: one of its 3 nearest points sits in the neighbouring list.

With 1 probe, IVF scanned only one list and missed neighbors just across the boundary. With 2 probes it found them, at the cost of scanning more points. On our real 3,173 vectors split into 56 lists, the explorer in §07 (switch it to IVF) shows the same pattern: 1 probe found only 50% of the true top 10; 8 probes found 90%; 32 probes found 99.4% but scanned more than half the data.

HNSW or IVF?

pgvector's own README sums it up the same way: HNSW has the better speed–recall trade-off but slower builds and more memory; IVFFlat builds faster and uses less memory. For most applications starting today, HNSW is the default choice. IVF shines when memory or build time is the constraint, and very large systems often combine IVF with quantization (next): an IVF to pick the region, compressed vectors inside each list.

Quantization: make each vector smaller

Both indexes are fastest when everything fits in RAM, and a 384-number float32 vector is 1,536 bytes. QuantizationQuantizationStoring numbers with fewer bits to save space, for example rounding each 4-byte float to a 1-byte integer, or keeping only its sign (1 bit). Similarities become slightly less precise. stores each number with fewer bits (Embeddings covers the accuracy side in detail). Measured on our 3,173 vectors and 199 queries, using exact search over the compressed vectors:

Half precision is close to free: half the memory, the same neighbors. One bit per number is 32× smaller but, used alone, misses 4 of every 10 true neighbors on our data. The common pattern is the last row: use the tiny codes to shortlist candidates quickly, then compute exact distances on just the shortlist. Fetch a longer shortlist to get closer to 100%. pgvector supports both (halfvec columns and indexes, and binary_quantize() for expression indexes, which index a computed value instead of a plain column).

Filtering: when WHERE meets ANN

The query everybody writes

SELECT id, body
FROM docs
WHERE tenant_id = 7                -- only this customer's documents
ORDER BY embedding <=> $1          -- nearest to the query vector
LIMIT 10;

The intent is obvious: "the 10 nearest documents that belong to tenant 7". (In a Multi-tenantMulti-tenant (tenant)One app serving many customer organisations (tenants) from shared infrastructure, like Slack workspaces or Shopify stores. Each tenant only sees its own data. app, every query looks like this.) There are two basic ways a database can run it.

Post-filtering: index first, then WHERE

Post-filteringPost-filteringLetting the vector index find its candidates first, then dropping the ones that fail the WHERE condition. If few rows match, most candidates are thrown away and you get fewer results than you asked for.: the HNSW index walks the graph as usual and produces its candidate list of ef_search nearest vectors (40 by default in pgvector). Then the WHERE condition throws away every candidate that isn't tenant 7.

If 10% of rows belong to tenant 7, then about 10% of the 40 candidates match: about 4 rows. You asked for 10. We ran exactly that on our real data in pgvector 0.8.7 (3,173 rows, tenant_id spread evenly over 0–9):

       key       | distance
-----------------+----------
 lock-contention |    0.576
 reentrant       |    0.659
 use-after-free  |    0.690
 gc-mutator      |    0.695
(4 rows)

No error, no warning. In a RAGRAG (retrieval-augmented generation)Answering a question by first searching your own documents for relevant passages, then putting those passages in the prompt so the model answers from them, ideally with citations. app, the model would simply get 4 passages instead of 10. With a 2% filter, our JavaScript HNSW (the widget below) returned nothing at all for 42% of queries.

Pre-filtering: WHERE first, then distances

Pre-filteringPre-filteringApplying the WHERE condition first (often with an ordinary index), then finding the nearest neighbors only among the matching rows. Exact and cheap when few rows match.: find the matching rows first (an ordinary B-tree index on tenant_id does that in a few page reads), then compute exact distances to just those rows. It's exact, and it's cheap when few rows match: for a 2% filter on our data that's 64 distance computations instead of 3,173. When most rows match, it degrades into exact search over almost everything.

Explore it

The pattern in the averages:

• 50% of rows match: post-filtering is fine (10 rows, 97% recall).
• 10% match: post-filtering returns 3.9 rows on average; recall against the true filtered top 10 drops to 39%.
• 2% match: post-filtering is useless, and even the "keep scanning" fix costs more than exact search over the whole table. Pre-filtering wins easily.

The fixes, in pgvector terms

1. Iterative index scansIterative index scan (pgvector)A pgvector feature (0.8.0+) that keeps scanning further through an HNSW or IVF index when a filter has discarded too many candidates, until enough rows are found or a limit is reached. (pgvector 0.8.0+). The index keeps walking the graph until enough rows pass the filter, up to a limit:

   SQL

   Copy

   SET hnsw.iterative_scan = strict_order;  -- or relaxed_order: faster, results may be slightly out of order

   On our data this turned the 4-row answer into the correct 10 rows. Scanning stops at hnsw.max_scan_tuples (20,000 by default), so a very selective filter can still come up short.

2. An ordinary index on the filter column. With CREATE INDEX ON docs (tenant_id), the planner can choose to filter first and compute exact distances on the matching rows. pgvector's README recommends starting here. Check which plan you got with EXPLAIN ANALYZE.

3. Partial indexes or partitions. For a few fixed filter values, one HNSW index per value: CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = 7. For many values (like thousands of tenants), partition the table by tenant so each partition has its own small index.

Dedicated vector databases handle this inside the index, typically by checking the filter while walking the graph so non-matching nodes are skipped but still used as stepping stones. It's one of their real advantages (§11), but it isn't magic: very selective filters are hard for every graph index, and many of them fall back to exact search over the matching rows, which is pre-filtering again.

pgvector: vector search inside Postgres

Set up a table

CREATE EXTENSION IF NOT EXISTS vector;   -- once per database

CREATE TABLE docs (
  id        bigserial PRIMARY KEY,
  key       text,
  body      text,
  tenant_id int NOT NULL,
  embedding vector(384)                  -- ← 384 numbers, must match your embedding model
);

A Postgres extensionPostgres extensionAn add-on package that teaches PostgreSQL new data types, functions or index types. Once installed on the server, you enable it per database with CREATE EXTENSION. has to be installed on the database server first (managed Postgres services list which extensions they offer; pgvector is widely available). vector(384) rejects vectors of any other length, which catches a common bug: switching embedding models without re-embedding. Vectors are written as text like '[0.12,-0.03,…]'.

Query: ORDER BY distance, LIMIT k

pgvector's operators return a distance (smaller = closer):

SELECT key, embedding <=> $1 AS distance
FROM docs
ORDER BY embedding <=> $1      -- nearest first
LIMIT 5;

With our "two threads write the same variable" question as $1, both with and without an index:

       key       | distance
-----------------+----------
 race-condition  |    0.456
 data-race       |    0.529
 thread          |    0.551
 false-sharing   |    0.576
 lock-contention |    0.576
(5 rows)

These are the same neighbors as our Go and JS programs in §03: 0.456 is 1 − 0.544.

Add an HNSW index

CREATE INDEX docs_embedding_hnsw ON docs
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);     -- these are the defaults; shown for clarity

On our 3,173 rows the build took about 1.3 seconds on our machine and the index was 6,360 kB. Now the plan uses it:

In psql we kept our saved question vectors in a small asks table, so the query vector comes from a subquery (that's the InitPlan lines; $0 is its result):

EXPLAIN (ANALYZE, COSTS OFF)
SELECT key FROM docs ORDER BY embedding <=> (SELECT embedding FROM asks WHERE id = 3) LIMIT 5;

 Limit (actual time=0.747..0.757 rows=5 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using asks_pkey on asks (actual time=0.012..0.012 rows=1 loops=1)
           Index Cond: (id = 3)
   ->  Index Scan using docs_embedding_hnsw on docs (actual time=0.746..0.754 rows=5 loops=1)
         Order By: (embedding <=> $0)
 Planning Time: 0.149 ms
 Execution Time: 0.783 ms

The line to look for is Index Scan using docs_embedding_hnsw. Without the index the same query was a Seq Scan over all 3,173 rows followed by a Sort (exact search). Indexes & B-Trees §07 explains how to read plans like this.

From Go and JavaScript

You need the embedding of the user's question first (from your embedding model's API or a local model; see Embeddings). Then it's an ordinary parameterized query. Here we read a saved query vector from query.json and send it in pgvector's text format, so no extra library is needed:

// package main and imports (context, encoding/json, fmt, log, os, strconv, strings,
// github.com/jackc/pgx/v5) omitted

// toVectorLiteral turns []float32{0.1, 0.2} into "[0.1,0.2]", pgvector's text format.
func toVectorLiteral(v []float32) string {
	parts := make([]string, len(v))
	for i, x := range v {
		parts[i] = strconv.FormatFloat(float64(x), 'f', -1, 32)
	}
	return "[" + strings.Join(parts, ",") + "]"
}

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL")) // github.com/jackc/pgx/v5
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	var q struct {
		Vec []float32 `json:"vec"`
	}
	b, err := os.ReadFile("query.json") // in a real app: the embedding API's answer
	if err != nil {
		log.Fatal(err)
	}
	if err := json.Unmarshal(b, &q); err != nil {
		log.Fatal(err)
	}

	rows, err := conn.Query(ctx, `
		SELECT key, embedding <=> $1 AS distance
		FROM docs
		ORDER BY embedding <=> $1   -- ← uses the HNSW index
		LIMIT 3`, toVectorLiteral(q.Vec))
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
		var key string
		var dist float64
		if err := rows.Scan(&key, &dist); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%-15s %.3f\n", key, dist)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
}

Both print (real output; pgx v5.12.0 with Go 1.24, pg 8.23.1 with Node 22):

race-condition  0.456
data-race       0.529
thread          0.551

For production code, the official helper packages (pgvector-go and pgvector for Node) add a proper vector type and binary encoding, which is faster for large batches. The SQL stays the same.

Just use Postgres, or a dedicated vector database?

Both kinds of system use the same ideas from this page: HNSW or IVF, quantization, recall-vs-speed knobs. The difference is everything around the index.

When "just use Postgres" wins

• One system, not two. No second database to deploy, secure, monitor, back up and pay for. No sync job copying rows from Postgres to the vector store, and no bugs when that job falls behind or fails.
• Transactions. The document row and its embedding are written in the same Database transactionDatabase transactionA group of database changes that happen all together or not at all (COMMIT keeps them, ROLLBACK undoes them). How much other users can see or interfere with the half-done state depends on the isolation level.. When it commits, the new document is searchable; when it rolls back, it never was. With two systems, you have to handle "saved in one, not the other" yourself.
• Joins and filters are just SQL. "Nearest 10 products that are in stock, under €50, in the user's country, joined with their reviews" is one query on data that is always consistent.
• Your existing operations work. Backups, point-in-time recovery, read replicasReplicaA full copy of a database kept on another machine and updated as the original changes, for reading load and for taking over if the original dies., access control, and the monitoring you already have all cover the vectors too.
• Scale is bigger than people expect. A few million 384-d vectors is a few GB of vectors plus a similar-sized index (§12): ordinary territory for one Postgres server.

When a dedicated vector database helps

• Very large collections. Hundreds of millions to billions of vectors don't fit one machine's RAM. Dedicated systems split the index across many machines (shardsShardOne piece of a split-up dataset, together with the database server that stores it. “Tenant 42 is on shard 3” means its rows live on that server.) for you and run each query on all of them (§12), where with Postgres you'd build that yourself.
• Heavy write and rebuild load. Constant high-volume inserts, deletes and re-embedding can compete with your main application's queries on a shared Postgres. A separate service isolates that load.
• Search features built in. Filtering inside the graph walk (§09), built-in keyword plus vector (hybridHybrid searchRunning a keyword search and a vector search for the same query and merging the two ranked lists, so exact words (names, error codes) and meaning both count.) search, multiple vectors per item, aggressive quantization tuned for their storage engine, tiered storage on disk.
• Managed scaling. A Managed serviceManaged serviceA piece of infrastructure (a database, a queue, a load balancer) that the cloud provider installs, patches, backs up and keeps running for you. You configure it and use it through an API; you never log into its servers. that adds capacity, replicas and shards as you grow, if you'd rather not tune Postgres at that size.

The trade-offs flip too: a separate service means a sync pipeline, eventual consistencyEventual consistencyA promise that if writes stop, every copy or derived view of the data (replicas, caches, read models, other services) will end up agreeing — but for a while, different readers may see different (stale) versions. between the two stores, another bill, and another thing on call.

A way to decide

Staff-level: memory, builds, updates, sharding, recall in production

1. Memory: vectors plus links

HNSW is fast only while the vectors and the graph sit in RAMRAM (main memory)The computer's working memory: fast (~100 ns per access) but lost when the power goes off. Programs and their data live here while running.; once it has to read pages from disk, every hop can become a disk read. So size it:

vectors:  N × d × bytes per number
links:    N × (about 2·M neighbor ids on layer 0) × bytes per id   (upper layers add a little)

10 million × 768 dims × 4 bytes    = 30.7 GB   of vectors
10 million × 32 links × ~8 bytes   ≈  2.6 GB   of links
                                   ≈ 33 GB, plus per-row overhead, plus the table itself

Our measurement keeps that honest: pgvector's index over our 3,173 vectors of 384 dims was 6,360 kB, about 2.0 kB per vector, where the vector itself is 1.5 kB. The rest is links and page/row overhead. Note too that Postgres stores the vector twice: once in the table and once in the index.

The cheapest lever is the number format (§08): halfvec halves both copies with no measurable recall loss on our data. Binary quantization with re-ranking shrinks the index 32×, at the cost of a re-rank step and some recall. Fewer dimensions (some embedding models let you truncate their vectors) is another lever, but changes what "similar" means, so re-measure recall.

2. Building the index takes a while

Every insert is a search (§06), so building costs roughly "N searches with a candidate list of ef_construction". Our simple HNSW needed about 1,100 distance computations per vector to build with M = 16, and 2.6× more with M = 32. pgvector built our 3,173 vectors in about 1.3 s on our machine; at millions of rows, builds run from minutes to hours.

What helps, per the pgvector README (0.8.7):

• Give the build enough memory. Builds are much faster while the graph fits in maintenance_work_mem; pgvector prints a notice when it no longer does. Don't set it so high that the server runs out of memory.
• Use parallel workers: SET max_parallel_maintenance_workers = 7; (plus the leader process).
• Load first, index after. Bulk-loading rows into a table without the index and then running CREATE INDEX is faster than inserting into an existing index row by row. Use CREATE INDEX CONCURRENTLY on a live table so writes aren't blocked during the build.

3. Updates and deletes in a graph

• Insert: search + link. Fine, and the main advantage over IVF.
• Update: Postgres's MVCCMVCC (multi-version concurrency control)The database keeps several versions of a row available at once, so each transaction can read the version that matches its snapshot. PostgreSQL writes each new version next to the old one; MySQL/InnoDB and Oracle update in place but can rebuild old versions from an undo log. Either way, readers never wait for writers and writers never wait for readers. turns an UPDATE into "new row version + old row version left behind", so changing an embedding is effectively an insert plus a delete.
• Delete: you can't just remove a node: other nodes route through it. The deleted row stays in the graph as a Dead row (dead tuple) / bloatDead row (dead tuple) / bloatIn Postgres, an UPDATE or DELETE leaves the old row version behind (a “dead tuple”) until VACUUM removes it. Too many dead rows is called bloat: wasted space and slower scans. until VACUUMVACUUMPostgreSQL's clean-up job: it finds row versions that no running or future transaction can see any more (“dead” versions) and frees their space for reuse. Autovacuum runs it automatically in the background. removes it and repairs its neighbors' links, which is expensive. pgvector's README notes that vacuuming HNSW indexes can take a while and suggests REINDEX INDEX CONCURRENTLY first. Until then, dead entries still occupy candidate-list slots, so a table with heavy churn can see fewer results and lower recall.
• Re-embedding with a new model is the big one: every vector changes, and old and new vectors can't be compared. Add a new column (embedding_v2), backfill it in batches, build its index, switch reads behind a flag after checking recall and quality, then drop the old column. Store the model name with every vector so you always know which is which.

4. Sharding vectors

When one machine isn't enough, split the vectors across shardsShardOne piece of a split-up dataset, together with the database server that stores it. “Tenant 42 is on shard 3” means its rows live on that server.. The question is how queries find their neighbors:

• Shard by something every query filters on, typically the tenant. Then a query goes to exactly one shard, and each shard's index is small. This is the best case, and it works with plain Postgres partitioning too.
• Otherwise, every query visits every shard. That's Scatter-gatherScatter-gatherRunning a query by sending it to every shard (scatter), waiting for all the answers, and merging them (gather). Needed when the query doesn't include the shard key.: send the query to all shards, ask each for its own top k, merge the lists, keep the overall top k. Each shard must return the full k (the overall best 10 could all live on one shard). The query is only as fast as the slowest shard, so tail latency (p99p99 (99th percentile)The value that 99% of measurements are below — i.e. how bad the slowest 1% is. Averages hide slow outliers; p99 shows them.) grows with the shard count, and one slow machine slows every query.

Sharding covers shard keys and rebalancing in general; the vector-specific twist is that a nearest-neighbor query almost never has a natural shard key unless you give it one.

5. Measure recall in production

Recall can drop silently: more data, a churned graph, a changed setting, a new embedding model. Build it into your routine:

1. Keep a Golden setGolden set (for recall)A fixed sample of real queries, saved with their exact nearest neighbors, that you re-run after every index or settings change to measure recall on your own data. of a few hundred real queries (sampled from logs, with personal data removed).

2. On a schedule, compute their exact top k with the index turned off, and their approximate top k as production runs them. pgvector's README shows the exact-search trick:

   SQL

   Copy

   BEGIN;
   SET LOCAL enable_indexscan = off;   -- force exact search for this transaction
   SELECT id FROM docs ORDER BY embedding <=> $1 LIMIT 10;
   COMMIT;

3. Chart average and worst-case recall@10, and alert when they drop. Also track how many rows filtered queries return (§09).

This is the index-level half of quality. Whether the retrieved documents actually help the user, and whether the final answer is good, is measured with the evaluation techniques in Evaluating LLM Apps.

6. Hybrid search: don't throw away keywords

Vectors are good at meaning and weak at exact strings: product codes, error messages like ERR_CONN_RESET, people's names, version numbers. A query that contains one of those should match documents containing exactly that string, and an embedding may rank a vaguely related document higher. Keyword (full-text) searchKeyword (full-text) searchSearch that matches the words themselves, usually ranked with a formula such as BM25 that rewards rare words appearing often. Good at exact names, IDs and codes; blind to synonyms. handles those well. (It's no cure-all: for our "stop a payment being charged twice" question, the right entry, idempotency key, ranked only 25th by vectors, but its definition shares almost no words with the question either. Fixing that is about better chunks and queries, covered in RAG.) Hybrid searchHybrid searchRunning a keyword search and a vector search for the same query and merging the two ranked lists, so exact words (names, error codes) and meaning both count. runs both searches and merges the ranked lists; a common merge is reciprocal rank fusion (RRF): each result scores 1 / (60 + its rank) in each list, and the scores are added up, so items ranked well by both searches rise to the top. Postgres can do both in one database (full-text search plus pgvector). How to combine, re-rank and evaluate them is covered in RAG.

Check yourself

Predict the answer before you click. Q1 is covered in §02, Q2 in §05, Q3 in §07, Q4 in §09, Q5 in §08 and Q6 in §11.

SELECT id FROM docs
WHERE lang = 'de'
ORDER BY embedding <=> $1
LIMIT 10;

Cheat sheet