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> = ...) 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 says which one it picked and why:

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
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), measured 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 at max_scan_tuples candidates; a search that still has fewer than k rows there finishes over the table itself, so a LIMIT is filled whenever enough rows match. A search that fills k in 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 than k rows. EXPLAIN ANALYZE and the underfilled counter report it; nothing widens on its own.

Tier 2 (the allow-list) never widens: evaluating the predicate into a bitmap first makes the one index call exact over the predicate, so there is nothing left to widen.

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.

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	The exact plane only, no quantized coarse estimator: still correct, but visits more candidates per search than an L2 or cosine index at the same recall
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).