Vector search in PostgreSQL

Vectors and distance

Suppose a search produces query vector [1,0]. Give P7, the blue mug, that same vector. Give P9, the teapot, [0.8,0.6], and P3, the green tea, [0,1]. Each vector has two dimensions: two positions whose meanings must agree between stored values and queries.

L2 distance, written <->, is the straight-line gap. Between [1,0] and [0.8,0.6] it is √((1−0.8)² + (0−0.6)²), about 0.632. Cosine distance, written <=>, is one minus the cosine of the angle between the two vectors, treating each as a direction from the origin. It compares direction rather than length. Here its value is 0.2.

The inner product multiplies corresponding coordinates and sums them. P9's inner product with the query is 0.8. Higher inner products come first, but pgvector's <#> returns their negatives, so an ascending index scan can put the best score first. It returns −0.8 here. Multiplying that result by −1 recovers the inner product; subtracting cosine distance from 1 gives cosine similarity. Neither conversion makes the scores interchangeable.

Doubling P9 changes its length and its inner product, but leaves its direction unchanged. The cosine order stays the same while the inner-product order puts P9 ahead of P7. A choice of distance therefore changes the question. If both sides are normalised to unit length, L2, cosine and inner product give equivalent rankings; their numerical scores and thresholds still differ.

The representation's contract

Use the distance and preprocessing required by the embedding model, with compatible query and document encoders. Record the model version, dimensions, normalisation and source revision alongside the generation process. Two models can emit the same number of coordinates without making those coordinates comparable. A vector(768) column checks the length; it cannot establish that both values came from the same space.

Validate values before evaluating retrieval. The vector type stores single-precision components and rejects non-finite values and a mismatched declared dimension. NULL means no vector, rather than a distant product. Approximate indexes omit NULL vectors; cosine indexes also omit zero vectors, whose direction is undefined. Keep missing or unusable embeddings visible in generation monitoring rather than interpreting their absence as an approximate-search miss.

Check index limits as well as storage limits. In this version the HNSW and IVFFlat classes for ordinary vector support up to 2,000 dimensions. A wider representation needs another supported type, or a deliberate dimensionality or quantisation design, followed by its own recall check.

Exact nearest neighbours

An exact search computes the distance for every eligible vector and chooses the smallest. With these three rows, there is little work to save. Start from a separate copy of the shop after order 103: P3 has stock 10, P7 has stock zero, and P9 has stock 4.

-- Requires pgvector installed and CREATE EXTENSION vector completed.
CREATE TABLE product_vectors (
  product_id text PRIMARY KEY,
  name text NOT NULL,
  stock integer NOT NULL CHECK (stock >= 0),
  embedding vector(2) NOT NULL
);
INSERT INTO product_vectors VALUES
  ('P3', 'Green tea, 50 g', 10, '[0,1]'),
  ('P7', 'Blue mug',         0, '[1,0]'),
  ('P9', 'Teapot',           4, '[0.8,0.6]');

The request is “the two nearest products that are in stock”. Keep the condition, ascending distance and limit together:

SELECT product_id, embedding <-> '[1,0]'::vector AS distance
FROM product_vectors
WHERE stock > 0
ORDER BY embedding <-> '[1,0]'::vector
LIMIT 2;

The result is P9 at about 0.632, then P3 at about 1.414. P7's zero distance does not help it pass the stock condition. Choosing nearest products globally before testing stock asks a different question.

Choosing the global nearest two first selects P7 and P9. Filtering those two leaves P9 alone, even though P3 is available. This SQL expresses that second question:

SELECT product_id, distance
FROM (
  SELECT product_id, stock,
         embedding <-> '[1,0]'::vector AS distance
  FROM product_vectors
  ORDER BY embedding <-> '[1,0]'::vector
  LIMIT 2
) AS global_neighbours
WHERE stock > 0
ORDER BY distance;

The difference is observable without any approximate index. An exact query does not need an ANN index to be fast either. If an ordinary index on a filter column reduces a million-row catalogue to thirty eligible products, computing thirty distances and choosing ten can be cheap. Compare that route before building a structure which searches across all million vectors.

Once an approximate index exists, the planner can use it for the first query too, changing the retrieval guarantee. For a reliable exact baseline, evaluate a copy with no approximate index, or locally disable index scans and confirm a plan that evaluates every eligible vector. Retain the same vectors, distance, filter and visible data. Disabling one particular index by assumption is not evidence that the baseline was exact.

HNSW indexes

Exact work grows with the eligible population and the vector width. An approximate nearest-neighbour index, or ANN index, avoids evaluating every eligible vector, accepting that some true neighbours may be missed. HNSW, short for Hierarchical Navigable Small World, organises vectors as a graph of links between nearby points.

The upper layers contain fewer points and longer routes across the space. A search follows those links towards the query, then descends to denser layers. At the ground layer it explores a candidate list, keeping promising points and visiting their neighbours. The search can finish without reaching a better point elsewhere in the graph.

Consider a chosen graph where the search reaches C, at distance 2 from the query, and can also explore D at distance 3. The route through D leads to E, only 0.5 away. These are illustrative query distances, not geometric positions or a captured pgvector graph.

In the narrow search, C's distance 2 beats D's 3, so the search discards the route through D. E, at distance 0.5, never enters the answer. Keeping D lets the wider search find E. The extra work is useful precisely because the path to a closer point need not improve at every step. This chosen route isolates the exploration trade; it does not predict which links a particular build creates.

CREATE INDEX product_vectors_hnsw ON product_vectors
USING hnsw (embedding vector_l2_ops)
WITH (m = 16, ef_construction = 64);

The operator class must match the query's distance. vector_l2_ops supports <->; cosine needs vector_cosine_ops and <=>, and inner product needs vector_ip_ops and <#>. Preserve the direct distance expression, ascending order and limit in the retrieval query. An expression such as ORDER BY 1 - cosine_distance DESC can express the desired order while preventing the expected index path.

Two choices belong to the build. m controls graph connectivity; more links consume more space and add maintenance work. ef_construction controls the candidate list used while finding links. A more thorough construction spends build and insertion work to improve the resulting graph. HNSW needs no initial clustering training, so it can be built on an empty table. Its graph still has to be constructed and maintained as vectors arrive.

The query's hnsw.ef_search controls its search candidate list, defaulting to 40 in pgvector 0.8.7. Raising it spends more exploration to improve recall. It is neither a result count nor a promise that exactly that many vectors are visited:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT product_id, embedding <-> '[1,0]'::vector AS distance
FROM product_vectors
WHERE stock > 0
ORDER BY embedding <-> '[1,0]'::vector
LIMIT 2;
COMMIT;

Build memory matters separately. HNSW initially holds its graph in maintenance_work_mem; spilling into the on-disk build phase can make construction substantially slower. Budget memory against concurrent work and measure the build. Raising a query setting cannot repair a poorly connected graph without paying more search work each time.

IVFFlat indexes

IVFFlat uses lists instead of a graph. During the build it learns representative centres from a sample of existing vectors, then assigns vectors to their closest centre. A query compares itself with the centres and probes some of their lists. It computes point distances inside those lists while skipping the others.

The closest centre does not necessarily own the closest point. In a one-dimensional example, choose centres 0, 4 and 8, and query at 1.9. Centre 0 is closest to the query, but point B at 2.1 belongs to centre 4’s list. The query and its nearest point lie on opposite sides of a list boundary.

Suppose the nearest point A in centre 0’s list is 1.5 from the query. Probing that list alone returns A; searching centre 4’s list also finds B, only 0.2 away. A perfectly computed distance inside the first list did not establish the global nearest point. Searching more lists trades the saving from skipping vectors for a better chance of retaining the true neighbours.

-- An alternative to the HNSW index; build after loading representative data.
CREATE INDEX product_vectors_ivf ON product_vectors
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 20);

BEGIN;
SET LOCAL ivfflat.probes = 5;
SELECT product_id, embedding <-> '[1,0]'::vector AS distance
FROM product_vectors
WHERE stock > 0
ORDER BY embedding <-> '[1,0]'::vector
LIMIT 2;
COMMIT;

The definition shows the controls, rather than a sensible twenty-list index for three products. lists is a build choice; probes is a query choice. More lists can reduce the vectors searched at a fixed probe count, but can also put useful neighbours in lists the query skips. Raising probes searches more lists. Probing all lists removes that omission, with the work of an exact search; the planner may choose a scan instead.

Train on a representative loaded population, rather than the first handful of rows. Later inserts are assigned to the existing centres; they do not retrain the partition. A substantial change in distribution can concentrate new vectors into awkward lists and make the original speed–recall trade worse. Compare recall after growth, and rebuild when the measured change warrants it. IVFFlat's smaller structure and training cost belong in the comparison alongside HNSW's graph construction, memory and query work.

Filtering approximate results

Putting WHERE stock > 0 before the ordered limit is necessary, but an ANN index still explores an approximate population. In a shared index, the stock test is applied to rows produced by that index scan. Nearby out-of-stock rows can consume the exploration budget. A query asking for ten may therefore return three even when hundreds of eligible products exist.

This is a second reason for a short answer, distinct from explicitly limiting a global subquery before filtering. The SQL can ask the right question while the bounded approximate path finds too few eligible rows. Do not move stock or authorisation checks out of the query to disguise that shortfall. A nearby vector establishes no permission to return its row.

Searching further

pgvector 0.8.7 can continue an approximate scan when the executor needs more rows. HNSW offers strict_order and relaxed_order iterative scans. IVFFlat offers relaxed_order. Iteration stops when enough rows have been found or the permitted search is exhausted; it is not an unbounded guarantee of a full answer.

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = strict_order;
SET LOCAL hnsw.max_scan_tuples = 20000;
SELECT product_id, embedding <-> '[1,0]'::vector AS distance
FROM product_vectors
WHERE stock > 0
ORDER BY embedding <-> '[1,0]'::vector
LIMIT 2;
COMMIT;

strict_order preserves distance order among the rows returned. It does not mean the search found the exact nearest eligible set. HNSW's max_scan_tuples is an approximate bound for additional exploration, not the initial scan; memory can also stop further exploration. hnsw.scan_mem_multiplier sets that memory budget relative to work_mem. For IVFFlat, ivfflat.max_probes bounds iterative probing.

Relaxed scans can return a later, closer row after an earlier, farther one. Materialise the retrieved candidates and sort them before presentation. On PostgreSQL 18, the + 0 below requests a fresh outer sort:

BEGIN;
SET LOCAL ivfflat.probes = 5;
SET LOCAL ivfflat.iterative_scan = relaxed_order;
SET LOCAL ivfflat.max_probes = 20;
WITH candidates AS MATERIALIZED (
  SELECT product_id, embedding <-> '[1,0]'::vector AS distance
  FROM product_vectors
  WHERE stock > 0
  ORDER BY embedding <-> '[1,0]'::vector
  LIMIT 20
)
SELECT product_id, distance
FROM candidates
ORDER BY distance + 0, product_id
LIMIT 2;
COMMIT;

The inner twenty is a candidate budget, the outer two the requested answer. Sorting can fix the order of found candidates and choose the best two among them. It cannot recover a neighbour absent from those twenty. Re-ranking with a better distance or model has the same limit: candidate generation must first retain the useful row.

Narrowing the population

When a filter is very selective, prefer checking an exact path through its ordinary index first. For a few stable, frequently requested populations, a partial ANN index can build the structure over just that population:

CREATE INDEX product_vectors_in_stock_hnsw ON product_vectors
USING hnsw (embedding vector_l2_ops)
WHERE stock > 0;

This removes out-of-stock vectors from this index's search population. It remains approximate within the in-stock population, and stock changes now change index membership. The query must imply the index predicate at planning time. A generic parameterised condition does not necessarily do that. Many per-value partial indexes add planning, build and maintenance costs; they are not free subdivisions of one index.

Partitioning can provide separate search populations for a suitable partition key, with pruning selecting relevant partitions. It also brings partition design and operational costs, especially when a request spans many populations. Choose between exact filtering, broader ANN exploration, partial indexes and partitioning from the actual filter distribution and request mix. Table partitioning and data lifecycle develops that design.

Measuring recall and latency

Nearest-neighbour recall measures how much of the exact answer an approximate search recovered. Take a request whose exact top four are A, B, C and D. An approximate answer of A, C, E and F recovers two of the four, so recall is 0.5. It has four rows and can be perfectly sorted. Neither fact repairs the missing B and D.

For top-k, divide the number of recovered exact identities by the exact answer's size. If only three eligible searchable rows exist and k is ten, use three as the denominator. An empty eligible population has no neighbours to recover: report the empty case separately rather than dividing by zero. Count distinct identities, with the same rule for duplicate product chunks or versions on both sides.

Ties at the cutoff need an explicit convention. A stable identity tie-breaker can select one exact reference set; an equally distant alternative may then score as a miss. A tie-aware evaluation instead accepts any boundary-tied neighbour while retaining every strictly closer one. Record which convention is used. Do not add a secondary sort to an ANN retrieval query and assume its access path stays the same: retrieve candidates first, then sort, and inspect the plan.

Measure relevance separately using representative customer tasks and judged useful results. If exact neighbours are unhelpful, a higher recall against them only makes retrieval more faithful to an unhelpful representation. Linguistic matching and lexical ranking belong to Full-text search in PostgreSQL. Combining lexical and vector retrieval still needs evaluation of the combined answer.

A measured comparison

The experiment below uses 6,000 invented eight-dimensional vectors, L2 and k = 10. Eight fixed query vectors are run without a filter, then with a cohort condition selecting 300 rows, or 5% of the population. Cohorts and coordinates have no business meaning. Exact identities are collected before any ANN index exists; only one ANN index exists at a time afterwards.

The comparison uses native PostgreSQL 18.6 and pgvector 0.8.7 on an owned disposable Linux arm64 server. Each configuration has eight recall observations per filter family and five instrumented executions of each request: forty latency samples. They run sequentially on a recently loaded fixture, without concurrent updates or application/network work. The numbers show this fixture's trade, rather than a production throughput claim.

HNSW with ef_search=10 recovered every unfiltered neighbour in these eight requests. Under the 5% cohort filter it returned zero or one row. Raising the search list to 100 still left every filtered answer short. Strict iteration filled all ten places and reached mean recall 0.975, but its filtered median was about 0.301 ms against 0.041 ms for exact filtering. The ordinary cohort index made the exact search cheap.

IVFFlat with one probe filled every filtered answer, yet recovered only 43.75% of the exact identities on average. Five probes raised that to 88.75%. Relaxed iteration made no further difference here: the first batch already supplied ten eligible rows, so the executor did not request another batch. Filling the limit was not the same as satisfying a recall target.

The ANN comparisons deliberately discouraged sequential scans and removed the ordinary cohort index, exposing each approximate path. Every measured ANN plan was checked for its intended index name. They are controlled mechanics comparisons, not the planner's normal competition between all available indexes. Exact requests retained normal settings: a sequential scan without the filter, and a bitmap search of the cohort index with it.

The access counts explain why a faster lookup is not simply “fewer pages”. The unfiltered exact scan made 57 shared-buffer accesses; narrow HNSW made 108–166, but avoided computing all 6,000 distances. Under the filter, exact used 58–59 accesses while strict iterative HNSW used 1,039–1,481. These ranges cover the eight retained request plans, without summing parent and child counters.

HNSW's single build took about 428 ms and occupied 1.92 MiB; IVFFlat's twenty-list build took about 9 ms and occupied 0.40 MiB. Both used one build worker and a 64 MiB maintenance-memory setting. A single build of narrow synthetic vectors establishes neither a universal ratio nor steady-state insertion and cleanup costs. Those need the intended workload.

The retained identities and plans, guarded native verifier and fixture and measurement record give the exact data formula, settings and limits. The verifier requires the specifically named disposable database, user and cluster and rolls its changes back. Recall uses a stable identity reference at ties; it is measured once per request, separately from the five instrumented timing runs.

Before trusting a measured improvement, inspect the actual plan. The distance must appear as the ANN index scan's Order By. Read the filter and its rejected rows, the returned count, and the shared-buffer accesses alongside time. Buffers count accesses, including repeats, rather than distinct pages or physical drive reads. A smaller reported execution time does not prove less CPU work everywhere, and a full result does not prove high recall.

For an application decision, use a held-out set of real request vectors, common and rare filters, representative vector widths and a stable data snapshot for each exact/approximate comparison. Keep model generation outside retrieval timings, then measure the whole customer request separately. Compare repeated latency distributions, warm and cold cache conditions, concurrent readers and writers, build time, peak memory, index size and insertion cost. Retain normal planner choices as well as any deliberately forced mechanics tests.

A query average can hide the requests a shared index handles badly. Group recall and short-answer rates by filter selectivity and request type, and check the slow tail as well as the median. Choose the cheapest tested configuration that meets the required recall and latency for those groups. A specialised index earns its place only when that saving survives its ongoing upkeep.

Keeping vectors and indexes useful

A text update and a vector update are different work. Changing P7's description does not make an external embedding model run inside PostgreSQL. Until generation finishes, the database can have an accurate current description beside a stale vector. Searching that vector exactly is still stale retrieval.

Track the source revision each vector represents and the embedding model's version. A background generator should publish its result only if it still corresponds to the intended source revision; otherwise a slow old generation can overwrite a newer one. Decide whether an unembedded or stale product is excluded, served lexically, or shown through another path, and monitor that population explicitly.

A model migration changes the retrieval space. Generate a compatible new query representation and new document vectors, validate dimensions and metric, build the corresponding index, and evaluate the new answers before switching readers. Keep versions separated through the migration. Mixing old and new coordinates in one distance order can produce plausible scores without meaningful comparisons. PostgreSQL schema changes under load owns the live build and cutover procedures.

Ordinary inserts, updates and deletes also maintain the search structure and PostgreSQL's row versions. Dead tuples and delayed cleanup can affect the number of candidates available to an approximate scan. Track vacuum, table and index sizes, result counts and recall over time. HNSW cleanup can be expensive; rebuilding is a possible maintenance choice with its own space, write and availability cost. IVFFlat additionally needs reassessment when its training population stops representing current vectors.

Keep extension binaries and SQL version dependencies with the database's restore and upgrade requirements. An index can be rebuilt from retained vectors; lost source text, model identity or generation rules may not be recoverable from the index. Types and extensions in PostgreSQL owns those installation, dependency and restore procedures.

The retrieval choice follows the eligible workload. Exact search is a useful baseline and often a useful final design. HNSW spends graph construction and memory to save query exploration; IVFFlat spends training and list selection to skip parts of the population. In both, keep the model's usefulness, nearest-neighbour recall, filter behaviour and lifetime cost visible as separate measurements.