Vector Search
VECTOR, HALFVEC, SPARSEVEC and BIT columns (see Data Types) rank rows by distance the same way any other column ranks by value: ORDER BY <distance>(col, query) LIMIT k. This page covers running that query without an index (exact, always correct), with an anode/manode index (approximate, fast, see CREATE INDEX), combined with a WHERE filter, as a distance threshold, and as a nearest-neighbour join - one query surface for all four, over the same live table your application already writes to.
Exact search
With no index, ORDER BY <distance> LIMIT k scans the table and keeps the closest k rows in a bounded heap. This is exact (never approximate), and it is what serves every query on a column with no vector index, or while one is building, regrowing, or turned off:
CREATE TABLE articles (id BIGINT PRIMARY KEY, title TEXT, embedding VECTOR(3));
INSERT INTO articles VALUES
(1, 'intro to scramdb', '[0.10,0.20,0.30]'),
(2, 'vector search', '[0.11,0.19,0.31]'),
(3, 'loading data', '[0.90,0.10,0.05]');
SELECT id, title, embedding <-> '[0.10,0.20,0.30]' AS distance
FROM articles
ORDER BY distance
LIMIT 2;
DROP TABLE articles;
A WHERE <distance> < threshold predicate with no index is the same fused exact
kernel, just filtering instead of ranking (see Distance thresholds
below for the indexed case). Combine either with an ordinary WHERE on other columns
exactly as you would any other query; the tighter that filter, the fewer rows the
distance kernel has to touch.
Indexed search
Build an anode (single node) or manode (distributed) index on the column, and the
same ORDER BY <distance> LIMIT k query is rewritten to use it automatically, no SQL
change required:
CREATE TABLE articles (id BIGINT PRIMARY KEY, title TEXT, embedding VECTOR(3));
INSERT INTO articles VALUES
(1, 'intro to scramdb', '[0.10,0.20,0.30]'),
(2, 'vector search', '[0.11,0.19,0.31]'),
(3, 'loading data', '[0.90,0.10,0.05]');
CREATE INDEX articles_embedding_idx ON articles USING anode (embedding);
SELECT id, title
FROM articles
ORDER BY embedding <-> '[0.10,0.20,0.30]'
LIMIT 2;
DROP TABLE articles;
The index returns a candidate list, which is always re-ranked against the exact
distance of the real row before it is returned: an approximate index can miss a true
neighbour, but it never reports the wrong distance for a row it does return. EXPLAIN
names the tier the planner chose and, when the index is not used, the reason (see
EXPLAIN on the Statements page).
Per-statement session parameters (SET anode.<key> = ..., open to every role) trade recall for latency
without touching the index: over_fetch, the six search knobs (probe_permille,
near_permille, walk_budget, beam, rerank_extra, rank_head), max_scan_tuples,
and iterative_scan. SET anode.enable_index = off forces the exact path for the rest
of the session; EXPLAIN names this, alongside every other reason an index was not
used, in a plain one-sentence index not used: <reason> line. See Tuning: Vector
Indexes for what each knob trades off, and Configuration:
Vector Indexes for the server-wide and per-index
equivalents.
Filtering and the three tiers
A WHERE predicate on another column of the same table, beside the ORDER BY <distance> LIMIT, is answered by whichever of three tiers the estimated selectivity of
that predicate calls for. You never pick the tier; the planner does, from the same
statistics ANALYZE already collects, and EXPLAIN names the one it picked:
| Tier | When | What runs |
|---|---|---|
| 1: exact | The predicate is expected to leave few enough rows: at or under exact_tier_ratio matches per requested row (default 4), or no more rows than one index search would visit anyway | An exact scan over the filtered rows with the fused top-k; the index is never consulted |
| 2: traversal | Otherwise, while the predicate's selectivity is at or under selectivity_boundary (default 0.55) | The predicate is evaluated into a row-id bitmap first, then passed to the index as an allow-list; one index call, no widening, finished exactly over the allow-list if that call comes back short (below) |
| 3: post-filter | The predicate is broader than selectivity_boundary | A plain index search sized by the estimated selectivity, widened as needed (below) |
CREATE TABLE articles (id BIGINT PRIMARY KEY, category TEXT, embedding VECTOR(3));
INSERT INTO articles VALUES
(1, 'tutorial', '[0.10,0.20,0.30]'),
(2, 'tutorial', '[0.11,0.19,0.31]'),
(3, 'reference', '[0.90,0.10,0.05]');
CREATE INDEX articles_embedding_idx ON articles USING anode (embedding);
SELECT id FROM articles
WHERE category = 'tutorial'
ORDER BY embedding <-> '[0.10,0.20,0.30]'
LIMIT 2;
DROP TABLE articles;
Both boundaries are per-node settings (vector.exact_tier_ratio,
vector.selectivity_boundary_permille), priors rather than a universal
optimum; see Tuning: Vector Indexes.
Iterative scan modes
When tier 3's first index call returns fewer than k re-ranked survivors, the
iterative_scan setting (off, relaxed_order, strict_order; SET anode.iterative_scan = ... per session, vector.iterative_scan server-wide, or per
index through ALTER INDEX ... SET) decides what happens next:
strict_order(the default): the index is asked again for a longer candidate prefix, every survivor seen so far is re-ranked together, and the rows come back in exact distance order. Widening stops atmax_scan_tuplescandidates; a search that still has fewer thankrows there finishes over the table itself, so aLIMITis filled whenever enough rows match. A search that fillskin its first round pays nothing for this.relaxed_order: the same widening, but each round's new survivors are appended as they come. Rows across rounds are not guaranteed to come back in exact distance order.off(pgvector's default): the first round's survivors are returned as is, possibly fewer thankrows.EXPLAIN ANALYZEand theunderfilledcounter report it; nothing widens on its own.
Tier 2 (the allow-list) makes one index call and never widens it. On a large index that
call is a budgeted walk, and a predicate that keeps very few rows can leave the walk with
fewer than k rows although at least k rows pass the predicate. Such a search is
finished exactly over the allow-list (only the rows the predicate keeps are read, never
the table) in strict_order and relaxed_order, so a LIMIT is filled whenever enough
rows match. off keeps pgvector's behaviour and returns the walk's rows as is, reported
by the underfilled counter. EXPLAIN ANALYZE shows the rows read this way as exact completion rows.
A filtered search that runs beside updates and deletes answers every row the predicate admits that the index held for the whole of the search, once, in a version it held during the search: the old vector or the new one, never neither, and never a row the predicate does not admit. A row inserted or deleted while the search runs may or may not be answered. The same holds while the index places its rows for the first time or refits them.
Distance thresholds
WHERE <distance>(col, q) < t (or <=) over an indexed column runs a threshold
search instead of a top-k one, with or without an ORDER BY ... LIMIT beside it:
CREATE TABLE articles (id BIGINT PRIMARY KEY, embedding VECTOR(3));
INSERT INTO articles VALUES
(1, '[0.10,0.20,0.30]'), (2, '[0.11,0.19,0.31]'), (3, '[0.90,0.10,0.05]');
CREATE INDEX articles_embedding_idx ON articles USING anode (embedding);
SELECT id FROM articles
WHERE embedding <-> '[0.10,0.20,0.30]' < 0.05;
DROP TABLE articles;
The index walk stops once its frontier's estimated distance passes t * (1 + threshold_slack) (threshold_slack default 0.10), and every candidate it returns is
re-ranked exactly and checked against < t (or <=) before it reaches you: the answer
is exact up to the max_scan_tuples cap. A true match beyond that cap is not silently
dropped; it is counted in the underfilled metric and shown by EXPLAIN ANALYZE, the
same honesty rule top-k widening follows. Without an index, the same predicate runs as
the exact fused kernel.
Nearest-neighbour joins
A LATERAL join whose inner query is a plain ORDER BY <distance>(b.col, a.col) LIMIT k over an indexed column batches its searches instead of running one per outer row:
CREATE TABLE queries (id BIGINT PRIMARY KEY, embedding VECTOR(3));
CREATE TABLE articles (id BIGINT PRIMARY KEY, embedding VECTOR(3));
INSERT INTO queries VALUES (1, '[0.10,0.20,0.30]'), (2, '[0.90,0.10,0.05]');
INSERT INTO articles VALUES (1, '[0.11,0.19,0.31]'), (2, '[0.89,0.11,0.06]'), (3, '[0.50,0.50,0.50]');
CREATE INDEX articles_embedding_idx ON articles USING anode (embedding);
SELECT q.id AS query_id, nearest.id AS article_id, nearest.distance
FROM queries q
JOIN LATERAL (
SELECT id, embedding <-> q.embedding AS distance
FROM articles
ORDER BY distance
LIMIT 1
) nearest ON true;
DROP TABLE queries;
DROP TABLE articles;
Outer rows are grouped into chunks of knn_join_batch (default 64) query vectors,
each chunk searched in one call; every chunk's candidates are re-ranked exactly with
one batched row fetch, never one index search or one row fetch per outer row. The same
filter tiers apply per query when the inner side also carries a WHERE. Without an
index the join runs as an exact nested loop, chunked the same way for cache locality.
The inner side can also rank by an expression an index keys on: ORDER BY b.embedding::halfvec(3) <-> a.q LIMIT k (with a.q a HALFVEC(3) column) searches an
index created on (embedding::halfvec(3)) the same way, and re-ranks its candidates by
that same expression. EXPLAIN shows AnnIndexJoin on <table> using <index> and the
tier it chose when an index serves the join, and exact scan when none does. A partial
index never serves a join.
Inside a transaction block the join reads the transaction's own changes, with or without
an index: rows it inserted (by INSERT or COPY, into a distributed
table too), updated or deleted rank as they will after the commit, a
row with a NULL vector ranks last, and a statement never ranks the
rows it is itself still writing.
Metric notes per opclass
| Opclass family | Metric | Notes |
|---|---|---|
*_l2_ops (default) | Euclidean distance | The reference cascade: quantized coarse estimators plus an exact re-rank |
*_cosine_ops | Cosine distance | Ranks on unit-normalized vectors; a zero-norm row has no direction to normalize, so it is never indexed and is served by the exact path instead, counted rather than silently dropped |
*_ip_ops | Negative inner product | Ranks natively rather than through the L2 cascade. When stored norms vary widely, cells are formed by direction and ranked by the largest norm each holds, so a long vector in a distant cell is still found; recall is reported, not assumed |
*_l1_ops | Taxicab (L1) distance | A four-bit sweep of its own ranks the candidates and the exact binary16 re-rank orders them, without the one-bit filter a large L2 or cosine index runs ahead of the re-rank |
bit_hamming_ops | Hamming distance | Exact on the integer and packed-popcount planes |
bit_jaccard_ops | Jaccard distance | MinHash-signature candidates through the Hamming path, exact Jaccard re-rank; recall is measured rather than assumed, since no published reference number exists for this combination |
A BIT row is stored one bit per bit in the arena, so a bit_hamming_ops or
bit_jaccard_ops index over BIT(64000) rows costs roughly 128 KiB of arena memory
per row; size vector.index_memory_budget accordingly for wide bit columns.
A customer-shaped example: daily load-curve matching
A common shape: a fixed-length numeric feature vector built by hand (no embedding
model), one row per entity per day, searched for the historically closest match to
today. Here it's a building's electricity load curve, 96 samples at 15-minute
intervals over a day, each value normalized to 0.0-1.0:
CREATE TABLE load_profile_signatures (
meter_id TEXT NOT NULL,
reading_date DATE NOT NULL,
profile_vector VECTOR(96),
daily_kwh DOUBLE PRECISION,
peak_kw DOUBLE PRECISION,
PRIMARY KEY (meter_id, reading_date)
);
INSERT INTO load_profile_signatures (meter_id, reading_date, profile_vector, daily_kwh, peak_kw) VALUES
('main-electric-meter', '2026-06-01',
'[0.150,0.157,0.163,0.170,0.177,0.183,0.190,0.197,0.204,0.211,0.219,0.227,0.236,0.246,0.257,0.270,0.284,0.300,0.318,0.338,0.360,0.384,0.409,0.436,0.462,0.488,0.513,0.536,0.555,0.571,0.582,0.587,0.587,0.580,0.569,0.551,0.530,0.504,0.475,0.444,0.412,0.380,0.349,0.319,0.290,0.264,0.240,0.219,0.201,0.185,0.172,0.161,0.153,0.147,0.144,0.143,0.144,0.149,0.156,0.165,0.177,0.192,0.208,0.226,0.245,0.265,0.284,0.302,0.318,0.332,0.342,0.348,0.350,0.348,0.342,0.332,0.318,0.302,0.284,0.265,0.245,0.226,0.208,0.191,0.177,0.164,0.154,0.146,0.141,0.137,0.136,0.136,0.137,0.140,0.144,0.148]',
342.5, 22.4),
('main-electric-meter', '2026-06-08',
'[0.160,0.166,0.172,0.178,0.184,0.189,0.195,0.201,0.207,0.213,0.218,0.225,0.231,0.238,0.246,0.254,0.264,0.275,0.288,0.302,0.318,0.336,0.356,0.378,0.401,0.425,0.449,0.473,0.496,0.517,0.534,0.548,0.558,0.562,0.561,0.555,0.543,0.527,0.506,0.481,0.454,0.425,0.395,0.364,0.335,0.306,0.280,0.255,0.233,0.213,0.196,0.182,0.170,0.161,0.154,0.150,0.148,0.149,0.152,0.158,0.167,0.179,0.193,0.210,0.229,0.249,0.271,0.293,0.315,0.335,0.353,0.368,0.380,0.388,0.391,0.389,0.383,0.373,0.359,0.343,0.324,0.303,0.283,0.262,0.243,0.225,0.209,0.195,0.184,0.175,0.169,0.165,0.162,0.162,0.162,0.164]',
336.9, 21.6),
('main-electric-meter', '2026-06-15',
'[0.550,0.557,0.563,0.569,0.575,0.580,0.585,0.590,0.593,0.596,0.598,0.600,0.600,0.600,0.598,0.596,0.593,0.590,0.585,0.580,0.575,0.569,0.563,0.557,0.550,0.543,0.537,0.531,0.525,0.520,0.515,0.510,0.507,0.504,0.502,0.500,0.500,0.500,0.502,0.504,0.507,0.510,0.515,0.520,0.525,0.531,0.537,0.543,0.550,0.557,0.563,0.569,0.575,0.580,0.585,0.590,0.593,0.596,0.598,0.600,0.600,0.600,0.598,0.596,0.593,0.590,0.585,0.580,0.575,0.569,0.563,0.557,0.550,0.543,0.537,0.531,0.525,0.520,0.515,0.510,0.507,0.504,0.502,0.500,0.500,0.500,0.502,0.504,0.507,0.510,0.515,0.520,0.525,0.531,0.537,0.543]',
528.0, 14.4);
CREATE INDEX load_profile_signatures_vec_idx
ON load_profile_signatures USING anode (profile_vector vector_cosine_ops);
-- Find historical days for this meter with a load curve closest to today's
-- (today's curve given directly here; a driver binds it as a parameter instead, below)
SELECT reading_date, daily_kwh, peak_kw,
cosine_distance(profile_vector,
'[0.140,0.147,0.153,0.160,0.166,0.172,0.179,0.185,0.191,0.197,0.204,0.210,0.218,0.226,0.235,0.245,0.258,0.273,0.291,0.312,0.337,0.366,0.398,0.433,0.471,0.510,0.548,0.585,0.618,0.644,0.664,0.675,0.677,0.668,0.651,0.625,0.592,0.553,0.510,0.466,0.421,0.378,0.337,0.300,0.266,0.237,0.212,0.190,0.172,0.157,0.145,0.135,0.127,0.122,0.118,0.117,0.119,0.123,0.131,0.143,0.159,0.178,0.202,0.229,0.259,0.290,0.323,0.354,0.382,0.406,0.425,0.436,0.440,0.436,0.425,0.406,0.382,0.354,0.323,0.290,0.259,0.229,0.202,0.178,0.159,0.143,0.131,0.123,0.118,0.115,0.115,0.117,0.120,0.125,0.130,0.135]'
) AS similarity_score
FROM load_profile_signatures
WHERE meter_id = 'main-electric-meter'
ORDER BY similarity_score ASC
LIMIT 5;
DROP TABLE load_profile_signatures;
The two rows built from a similar morning-and-evening shape rank ahead of the flat,
shifted-load third row, and the meter_id equality filter runs through tier 1 or 2
depending on how many days that one meter has on file (see Filtering and the three
tiers). A real client binds today's curve as a
parameter instead of inlining it, as below.
JDBC
String sql = "SELECT reading_date, cosine_distance(profile_vector, ?::vector) AS distance " +
"FROM load_profile_signatures " +
"WHERE meter_id = ? " +
"ORDER BY distance ASC LIMIT ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, "[0.140,0.147,0.153, ... ]"); // the 96-value literal
ps.setString(2, "main-electric-meter");
ps.setInt(3, 5);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) System.out.println(rs.getDate(1) + " " + rs.getDouble(2));
}
}
psycopg
import psycopg
query_vector = "[0.140,0.147,0.153, ... ]" # the 96-value literal
with psycopg.connect("postgresql://scramdb@localhost:5432/scramdb") as conn:
with conn.cursor() as cur:
cur.execute(
"SELECT reading_date, cosine_distance(profile_vector, %s::vector) AS distance "
"FROM load_profile_signatures WHERE meter_id = %s ORDER BY distance ASC LIMIT %s",
(query_vector, "main-electric-meter", 5),
)
for row in cur.fetchall():
print(row)
Both drivers bind the vector as an ordinary string parameter and cast it with
::vector in the SQL text; no client-side vector type or extension is required, since
VECTOR's text form is a plain bracketed, comma-separated literal (see Data
Types).