Vector similarity search in ClickHouse

Vector search answers one question: given a vector, which stored vectors are closest to it? It is the core operation behind semantic search, recommendations and “find me similar images”. ClickHouse can do it with plain SQL, and with a secondary index when the table gets big.

Vectors, norms and distances in two minutes

A vector is an ordered list of numbers. In ClickHouse it is just an Array(Float32) (or a Tuple):

SELECT [2.5, -1.3, 0.7] AS v1, [-3.9, 4.2, 6.8] AS v2;

A norm measures the size of one vector. A distance measures how far apart two vectors are. The common ones differ only in how they combine the coordinates:

Name	Also called	Formula (sketch)	ClickHouse function
L1	Manhattan, taxicab	sum of absolute values	L1Norm, L1Distance
L2	Euclidean	square root of the sum of squares	L2Norm, L2Distance
L-infinity	max norm	the largest absolute coordinate	LinfNorm, LinfDistance
cosine	angle between vectors	1 - cos(angle)	cosineDistance

SELECT
    L1Distance([1, 2, 3], [4, 6, 8])   AS l1,
    L2Distance([1, 2, 3], [4, 6, 8])   AS l2,
    LinfDistance([1, 2, 3], [4, 6, 8]) AS linf,
    cosineDistance([1, 0], [0, 1])     AS cosine,
    L2Norm([3, 4])                     AS norm;

l1 = 12, l2 = 7.0710678118654755, linf = 5, cosine = 1, norm = 5

Orthogonal vectors have cosine distance 1, identical directions have 0. For text and image embeddings cosine and L2 are the usual choices; use whatever your embedding model was trained for.

An embedding is a vector produced by a model so that similar things (sentences, pictures, products) land close together. “Similar” then becomes “small distance”. How you generate the embeddings is outside ClickHouse; the table just stores them.

Brute force: exact search

Without any index, nearest-neighbour search is a scan that computes a distance per row and keeps the smallest few:

CREATE TABLE docs
(
    id UInt32,
    title String,
    embedding Array(Float32)
)
ENGINE = MergeTree ORDER BY id;

INSERT INTO docs
SELECT number, concat('doc ', toString(number)),
       [sin(number), cos(number), sin(number / 3), cos(number / 7)]
FROM numbers(10000);

SELECT id, title, L2Distance(embedding, [0.0, 1.0, 0.0, 1.0]) AS dist
FROM docs
ORDER BY dist
LIMIT 3;

   0  doc 0     0
2507  doc 2507  0.00009072665125131607
9896  doc 9896  0.0003153085708618164

This is exact and surprisingly fast thanks to vectorised execution, so start here. It stops being acceptable when the table holds many millions of vectors and every query touches all of them.

You can also filter by radius instead of taking the top N: WHERE L2Distance(embedding, @query) < 0.5.

Approximate search with a vector similarity index

A vector similarity index trades a little recall for a lot of speed. It is a MergeTree skip index that builds an HNSW graph over the vectors, so the engine can find candidate neighbours without computing every distance.

CREATE TABLE docs_indexed
(
    id UInt32,
    title String,
    embedding Array(Float32),
    INDEX vec embedding TYPE vector_similarity('hnsw', 'L2Distance', 4)
        GRANULARITY 100000000
)
ENGINE = MergeTree ORDER BY id;

The type arguments are the algorithm (hnsw), the distance function (L2Distance or cosineDistance) and the number of dimensions, which must match your arrays (here 4). The index accepts further optional tuning parameters (graph connectivity, build effort, quantisation); see the MergeTree vector index docs for the current list. The documentation recommends a very large GRANULARITY for this index type, so that one graph covers a whole part rather than a few granules.

The index is only considered for the query shape it was built for: ORDER BY <same distance function>(column, constant vector) LIMIT N.

EXPLAIN indexes = 1
SELECT id FROM docs_indexed
ORDER BY L2Distance(embedding, [0.0, 1.0, 0.0, 1.0])
LIMIT 3;

The plan lists a Skip entry named vec of type vector_similarity. Always check this on your own data: if the distance function, the column or the query shape differs from the index definition, the index is silently not used and you are back to a full scan.

What changed since the early versions

• The first ClickHouse vector index was built on Annoy and gated behind allow_experimental_annoy_index. Annoy is gone: on 26.4 TYPE annoy(...) is rejected as an unknown index type, and vector_similarity is the way to do it.
• allow_experimental_vector_similarity_index is listed as an obsolete setting in 26.4 and the index works without any experimental flag. On older servers you may still need it, so check your version.
• There are query-level knobs worth knowing: vector_search_with_rescoring, vector_search_filter_strategy and max_limit_for_vector_search_queries (see system.settings).

Practical advice

• Keep the dimension count in the type and the arrays in sync: inserting a 3-element array into a 4-dimension index fails with INCORRECT_DATA.
• The index is built per part, so insert in healthy batches; building it costs CPU at insert and merge time.
• Approximate means approximate: measure recall against the brute-force query on a sample before you trust the index.
• Combine vector ranking with ordinary filters (WHERE country = 'DE') freely; the filter strategy setting controls whether filtering happens before or after the index lookup.