Exact and Approximate Vector Search

The problem of finding the N closest points in a multi-dimensional (vector) space for a given point is known as nearest neighbor search or, in short: vector search. Two general approaches exist for solving vector search:

Exact vector search calculates the distance between the given point and all points in the vector space. This ensures the best possible accuracy, i.e. the returned points are guaranteed to be the actual nearest neighbors. Since the vector space is explored exhaustively, exact vector search can be too slow for real-world use.
Approximate vector search refers to a group of techniques (e.g., special data structures like graphs and random forests) which compute results much faster than exact vector search. The result accuracy is typically “good enough” for practical use. Many approximate techniques provide parameters to tune the trade-off between the result accuracy and the search time.

A vector search (exact or approximate) can be written in SQL as follows:

WITH [...] AS reference_vector
SELECT [...]
FROM table
WHERE [...] -- a WHERE clause is optional
ORDER BY <DistanceFunction>(vectors, reference_vector)
LIMIT <N>

The points in the vector space are stored in a column vectors of array type, e.g. Array(Float64), Array(Float32), or Array(BFloat16). The reference vector is a constant array and given as a common table expression. <DistanceFunction> computes the distance between the reference point and all stored points. Any of the available distance function can be used for that. <N> specifies how many neighbors should be returned.

Exact vector search

An exact vector search can be performed using above SELECT query as is. The runtime of such queries is generally proportional to the number of stored vectors and their dimension, i.e. the number of array elements. Also, since ClickHouse performs a brute-force scan of all vectors, the runtime depends also on the number of threads by the query (see setting max_threads).

Example

CREATE TABLE tab(id Int32, vec Array(Float32)) ENGINE = MergeTree ORDER BY id;

INSERT INTO tab VALUES (0, [1.0, 0.0]), (1, [1.1, 0.0]), (2, [1.2, 0.0]), (3, [1.3, 0.0]), (4, [1.4, 0.0]), (5, [1.5, 0.0]), (6, [0.0, 2.0]), (7, [0.0, 2.1]), (8, [0.0, 2.2]), (9, [0.0, 2.3]), (10, [0.0, 2.4]), (11, [0.0, 2.5]);

WITH [0., 2.] AS reference_vec
SELECT id, vec
FROM tab
ORDER BY L2Distance(vec, reference_vec) ASC
LIMIT 3;

returns

   ┌─id─┬─vec─────┐
1. │  6 │ [0,2]   │
2. │  7 │ [0,2.1] │
3. │  8 │ [0,2.2] │
   └────┴─────────┘

Approximate vector search

Vector Similarity Indexes

ClickHouse provides a special “vector similarity” index to perform approximate vector search.

Vector similarity indexes are available in ClickHouse version 25.8 and higher. If you run into problems, kindly open an issue in the ClickHouse repository.

Creating a Vector Similarity Index

A vector similarity index can be created on a new table like this:

CREATE TABLE table
(
  [...],
  vectors Array(Float*),
  INDEX <index_name> vectors TYPE vector_similarity(<type>, <distance_function>, <dimensions>) [GRANULARITY <N>]
)
ENGINE = MergeTree
ORDER BY [...]

Alternatively, to add a vector similarity index to an existing table:

ALTER TABLE table ADD INDEX <index_name> vectors TYPE vector_similarity(<type>, <distance_function>, <dimensions>) [GRANULARITY <N>];

Vector similarity indexes are special kinds of skipping indexes (see here and here). Accordingly, above ALTER TABLE statement only causes the index to be built for future new data inserted into the table. To build the index for existing data as well, you need to materialize it:

ALTER TABLE table MATERIALIZE INDEX <index_name> SETTINGS mutations_sync = 2;

Function <distance_function> must be

L2Distance, the Euclidean distance, representing the length of a line between two points in Euclidean space,
cosineDistance, the cosine distance, representing the angle between two non-zero vectors, or
dotProduct, the dot product (inner product), representing the sum of element-wise products of two vectors. Equivalent to cosineDistance on normalized data.

For normalized data, L2Distance is usually the best choice, otherwise cosineDistance is recommended to compensate for scale.

For distance functions L2Distance and cosineDistance, a smaller value means a higher similarity, whereas for dotProduct, a higher value means a higher similarity. As a result, vector indexes with L2Distance and cosineDistance can only be used by SELECT [...] ORDER BY [...] ASC queries (ASC is the default for ORDER BY), whereas vector indexes built for dotProduct can only be used by SELECT [...] ORDER BY [...] DESC queries.

<dimensions> specifies the array cardinality (number of elements) in the underlying column. If ClickHouse finds an array with a different cardinality during index creation, the index is discarded and an error is returned. The optional GRANULARITY parameter <N> refers to the size of the index granules (see here). Unlike regular skip indexes, which use a default index granularity of 1, vector similarity indexes use 100 million as default index granularity. This value makes sure that only few indexes are build internally even for large parts. We recommend changing the index granularity only for advanced users who understand the implications of what they are doing (see below). Vector similarity indexes are generic in the sense that they can accommodate different approximate search method. The actually used method is specified by parameter <type>. As of now, the only available method is HNSW (academic paper), a popular and state-of-the-art technique for approximate vector search based on hierarchical proximity graphs. If HNSW is used as type, users may optionally specify further HNSW-specific parameters:

CREATE TABLE table
(
  [...],
  vectors Array(Float*),
  INDEX index_name vectors TYPE vector_similarity('hnsw', <distance_function>, <dimensions>[, <quantization>, <hnsw_max_connections_per_layer>, <hnsw_candidate_list_size_for_construction>]) [GRANULARITY N]
)
ENGINE = MergeTree
ORDER BY [...]

These HNSW-specific parameters are available:

<quantization> controls the quantization of the vectors in the proximity graph. Possible values are f64, f32, f16, bf16, i8, or b1. The default value is bf16. Note that this parameter does not affect the representation of the vectors in the underlying column.
<hnsw_max_connections_per_layer> controls the number of neighbors per graph node, also known as HNSW hyperparameter M. The default value is 32. Value 0 means using the default value.
<hnsw_candidate_list_size_for_construction> controls the size of the dynamic candidate list during construction of the HNSW graph, also known as HNSW hyperparameter ef_construction. The default value is 128. Value 0 means using the default value.

The default values of all HNSW-specific parameters work reasonably well in the majority of use cases. We therefore do not recommend customizing the HNSW-specific parameters. Further restrictions apply:

Vector similarity indexes can only be build on columns of type Array(Float32), Array(Float64), or Array(BFloat16). Arrays of nullable and low-cardinality floats such as Array(Nullable(Float32)) and Array(LowCardinality(Float32)) are not allowed.
Vector similarity indexes must be build on single columns.
Vector similarity indexes may be build on calculated expressions (e.g., INDEX index_name arraySort(vectors) TYPE vector_similarity([...])) but such indexes cannot be used for approximate neighbor search later on.
Vector similarity indexes require that all arrays in the underlying column have <dimension>-many elements - this is checked during index creation. To detect violations of this requirement as early as possible, users can add a constraint for the vector column, e.g., CONSTRAINT same_length CHECK length(vectors) = 256.
Likewise, array values in the underlying column must not be empty ([]) or have a default value (also []).

Estimating storage and memory consumption A vector generated for use with a typical AI model (e.g. a Large Language Model, LLMs) consists of hundreds or thousands of floating-point values. Thus, a single vector value can have a memory consumption of multiple kilobyte. Users who like to estimate the storage required for the underlying vector column in the table, as well as the main memory needed for the vector similarity index, can use below two formula: Storage consumption of the vector column in the table (uncompressed):

Storage consumption = Number of vectors * Dimension * Size of column data type

Example for the dbpedia dataset:

Storage consumption = 1 million * 1536 * 4 (for Float32) = 6.1 GB

The vector similarity index must be fully loaded from disk into main memory to perform searches. Similarly, the vector index is also constructed fully in memory and then saved to disk. Memory consumption required to load a vector index:

Memory for vectors in the index (mv) = Number of vectors * Dimension * Size of quantized data type
Memory for in-memory graph (mg) = Number of vectors * hnsw_max_connections_per_layer * Bytes_per_node_id (= 4) * Layer_node_repetition_factor (= 2)

Memory consumption: mv + mg

Example for the dbpedia dataset:

Memory for vectors in the index (mv) = 1 million * 1536 * 2 (for BFloat16) = 3072 MB
Memory for in-memory graph (mg) = 1 million * 64 * 2 * 4 = 512 MB

Memory consumption = 3072 + 512 = 3584 MB

Above formula does not account for additional memory required by vector similarity indexes to allocate runtime data structures like pre-allocated buffers and caches.

Using a Vector Similarity Index

To use vector similarity indexes, setting compatibility has be '' (the default value), or '25.1' or newer.

Vector similarity indexes support SELECT queries of this form:

WITH [...] AS reference_vector
SELECT [...]
FROM table
WHERE [...] -- a WHERE clause is optional
ORDER BY <DistanceFunction>(vectors, reference_vector)
LIMIT <N>

ClickHouse’s query optimizer tries to match above query template and make use of available vector similarity indexes. A query can only use a vector similarity index if the distance function in the SELECT query is the same as the distance function in the index definition. Advanced users may provide a custom value for setting hnsw_candidate_list_size_for_search (also know as HNSW hyperparameter “ef_search”) to tune the size of the candidate list during search (e.g. SELECT [...] SETTINGS hnsw_candidate_list_size_for_search = <value>). The default value of the setting 256 works well in the majority of use cases. Higher setting values mean better accuracy at the cost of slower performance. If the query can use a vector similarity index, ClickHouse checks that the LIMIT <N> provided in SELECT queries is within reasonable bounds. More specifically, an error is returned if <N> is bigger than the value of setting max_limit_for_vector_search_queries with default value 100. Too large LIMIT values can slow down searches and usually indicate a usage error. To check if a SELECT query uses a vector similarity index, you can prefix the query with EXPLAIN indexes = 1. As an example, query

EXPLAIN indexes = 1
WITH [0.462, 0.084, ..., -0.110] AS reference_vec
SELECT id, vec
FROM tab
ORDER BY L2Distance(vec, reference_vec) ASC
LIMIT 10;

may return

    ┌─explain─────────────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Project names)                                                                      │
│   Limit (preliminary LIMIT (without OFFSET))                                                    │
│     Sorting (Sorting for ORDER BY)                                                              │
│       Expression ((Before ORDER BY + (Projection + Change column names to column identifiers))) │
│         ReadFromMergeTree (default.tab)                                                         │
│         Indexes:                                                                                │
│           PrimaryKey                                                                            │
│             Condition: true                                                                     │
│             Parts: 1/1                                                                          │
│             Granules: 575/575                                                                   │
│           Skip                                                                                  │
│             Name: idx                                                                           │
│             Description: vector_similarity GRANULARITY 100000000                                │
│             Parts: 1/1                                                                          │
│             Granules: 10/575                                                                    │
    └─────────────────────────────────────────────────────────────────────────────────────────────────┘

In this example, 1 million vectors in the dbpedia dataset, each with dimension 1536, are stored in 575 granules, i.e. 1.7k rows per granule. The query asks for 10 neighbours and the vector similarity index finds these 10 neighbours in 10 separate granules. These 10 granules will be read during query execution. Vector similarity indexes are used if the output contains Skip and the name and type of the vector index (in the example, idx and vector_similarity). In this case, the vector similarity index dropped two of four granules, i.e. 50% of the data. The more granules can be dropped, the more effective index usage becomes.

To enforce index usage, you can run the SELECT query with setting force_data_skipping_indexes (provide the index name as setting value).

Post-filtering and Pre-filtering Users may optionally specify a WHERE clause with additional filter conditions for the SELECT query. ClickHouse will evaluate these filter conditions using post-filtering or pre-filtering strategy. In short, both strategies determine the order in which the filters are evaluated:

Post-filtering means that the vector similarity index is evaluated first, afterwards ClickHouse evaluates the additional filter(s) specified in the WHERE clause.
Pre-filtering means that the filter evaluation order is the other way round.

The strategies have different trade-offs:

Post-filtering has the general problem that it may return less than the number of rows requested in the LIMIT <N> clause. This situation happens when one or more result rows returned by the vector similarity index fails to satisfy the additional filters.
Pre-filtering is generally an unsolved problem. Certain specialized vector databases provide pre-filtering algorithms but most relational databases (including ClickHouse) will fall back to exact neighbor search, i.e., a brute-force scan without index.

What strategy is used depends on the filter condition. Additional filters are part of the partition key If the additional filter condition is part of the partition key, then ClickHouse will apply partition pruning. As an example, a table is range-partitioned by column year and the following query is run:

WITH [0., 2.] AS reference_vec
SELECT id, vec
FROM tab
WHERE year = 2025
ORDER BY L2Distance(vec, reference_vec) ASC
LIMIT 3;

ClickHouse will prune all partitions except the 2025 one. Additional filters cannot be evaluated using indexes If additional filter conditions cannot be evaluated using indexes (primary key index, skipping index), ClickHouse will apply post-filtering. Additional filters can be evaluated using the primary key index If additional filter conditions can be evaluated using the primary key (i.e., they form a prefix of the primary key) and

the filter condition eliminates at least one row within a part, the ClickHouse will fall back to pre-filtering for the “surviving” ranges within the part,
the filter condition eliminates no rows within a part, the ClickHouse will perform post-filtering for the part.

In practical use cases, the latter case is rather unlikely. Additional filters can be evaluated using skipping index If additional filter conditions can be evaluated using skipping indexes (minmax index, set index, etc.), Clickhouse performs post-filtering. In such cases, the vector similarity index is evaluated first as it is expected to remove the most rows relative to other skipping indexes. For finer control over post-filtering vs. pre-filtering, two settings can be used: Setting vector_search_filter_strategy (default: auto which implements above heuristics) may be set to prefilter. This is useful to force pre-filtering in cases where the additional filter conditions are extremely selective. As an example, the following query may benefit from pre-filtering:

SELECT bookid, author, title
FROM books
WHERE price < 2.00
ORDER BY cosineDistance(book_vector, getEmbedding('Books on ancient Asian empires'))
LIMIT 10

Assuming that only a very small number of books cost less than 2 dollar, post-filtering may return zero rows because the top 10 matches returned by the vector index could all be priced above 2 dollar. By forcing pre-filtering (add SETTINGS vector_search_filter_strategy = 'prefilter' to the query), ClickHouse first finds all books with a price of less than 2 dollar and then executes a brute-force vector search for the found books. As an alternative approach to resolve above issue, setting vector_search_index_fetch_multiplier (default: 1.0, maximum: 1000.0) may be configured to a value > 1.0 (for example, 2.0). The number of nearest neighbors fetched from the vector index is multiplied by the setting value and then the additional filter to be applied on those rows to return LIMIT-many rows. As an example, we can query again but with multiplier 3.0:

SELECT bookid, author, title
FROM books
WHERE price < 2.00
ORDER BY cosineDistance(book_vector, getEmbedding('Books on ancient Asian empires'))
LIMIT 10
SETTING vector_search_index_fetch_multiplier = 3.0;

ClickHouse will fetch 3.0 x 10 = 30 nearest neighbors from the vector index in each part and afterwards evaluate the additional filters. Only the ten closest neighbors will be returned. We note that setting vector_search_index_fetch_multiplier can mitigate the problem but in extreme cases (very selective WHERE condition), it is still possible that less than N requested rows returned. Rescoring Skip indexes in ClickHouse generally filter at the granule level, i.e. a lookup in a skip index (internally) returns a list of potentially matching granules which reduces the number of read data in the subsequent scan. This works well for skip indexes in general but in the case of vector similarity indexes, it creates a “granularity mismatch”. In more detail, the vector similarity index determines the row numbers of the N most similar vectors for a given reference vector. With setting vector_search_with_rescoring = 1, ClickHouse reads the original full-precision vectors for candidate rows and computes the final distance in the regular SQL pipeline. When the query plan allows it, ClickHouse filters the scan to the candidate rows returned by the vector index before the final distance computation. This step is called rescoring and can improve accuracy, especially with quantized vector indexes, because the final ranking uses the stored vectors instead of index distances. If additional filters remove too many candidates or more recall is needed, increase setting vector_search_index_fetch_multiplier so the vector index returns more candidate rows for rescoring. ClickHouse therefore provides an optimization which disables rescoring and returns the most similar vectors and their distances directly from the index. The optimization is enabled by default, see setting vector_search_with_rescoring. The way it works at a high level is that ClickHouse makes the most similar vectors and their distances available as a virtual column _distance. To see this, run a vector search query with EXPLAIN header = 1:

EXPLAIN header = 1
WITH [0., 2.] AS reference_vec
SELECT id
FROM tab
ORDER BY L2Distance(vec, reference_vec) ASC
LIMIT 3
SETTINGS vector_search_with_rescoring = 0

Query id: a2a9d0c8-a525-45c1-96ca-c5a11fa66f47

    ┌─explain─────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Project names)                                                                              │
│ Header: id Int32                                                                                        │
│   Limit (preliminary LIMIT (without OFFSET))                                                            │
│   Header: L2Distance(__table1.vec, _CAST([0., 2.]_Array(Float64), 'Array(Float64)'_String)) Float64     │
│           __table1.id Int32                                                                             │
│     Sorting (Sorting for ORDER BY)                                                                      │
│     Header: L2Distance(__table1.vec, _CAST([0., 2.]_Array(Float64), 'Array(Float64)'_String)) Float64   │
│             __table1.id Int32                                                                           │
│       Expression ((Before ORDER BY + (Projection + Change column names to column identifiers)))         │
│       Header: L2Distance(__table1.vec, _CAST([0., 2.]_Array(Float64), 'Array(Float64)'_String)) Float64 │
│               __table1.id Int32                                                                         │
│         ReadFromMergeTree (default.tab)                                                                 │
│         Header: id Int32                                                                                │
│                 _distance Float32                                                                       │
    └─────────────────────────────────────────────────────────────────────────────────────────────────────────┘

A query run without rescoring (vector_search_with_rescoring = 0) and with parallel replicas enabled may fall back to rescoring.

Performance tuning

Tuning compression In virtually all use cases, the vectors in the underlying column are dense and do not compress well. As a result, compression slows down inserts and reads into/from the vector column. We therefore recommend to disable compression. To do that, specify CODEC(NONE) for the vector column like this:

CREATE TABLE tab(id Int32, vec Array(Float32) CODEC(NONE), INDEX idx vec TYPE vector_similarity('hnsw', 'L2Distance', 2)) ENGINE = MergeTree ORDER BY id;

Tuning index creation The life cycle of vector similarity indexes is tied to the life cycle of parts. In other words, whenever a new part with defined vector similarity index is created, the index is create as well. This typically happens when data is inserted or during merges. Unfortunately, HNSW is known for long index creation times which can significantly slow down inserts and merges. Vector similarity indexes are ideally only used if the data is immutable or rarely changed. To speed up index creation, the following techniques can be used: First, index creation can be parallelized. The maximum number of index creation threads can be configured using server setting max_build_vector_similarity_index_thread_pool_size. For optimal performance, the setting value should be configured to the number of CPU cores. Second, to speed up INSERT statements, users may disable the creation of skipping indexes on newly inserted parts using session setting materialize_skip_indexes_on_insert. SELECT queries on such parts will fall back to exact search. Since inserted parts tend to be small compared to the total table size, the performance impact of that is expected to be negligible. Third, to speed up merges, users may disable the creation of skipping indexes on merged parts using session setting materialize_skip_indexes_on_merge. This, in conjunction with statement ALTER TABLE […] MATERIALIZE INDEX […], provides explicit control over the life cycle of vector similarity indexes. For example, index creation can be deferred until all data was ingested or until a period of low system load such as the weekend. Tuning index usage SELECT queries need to load vector similarity indexes into main memory to use them. To avoid that the same vector similarity index is loaded repeatedly into main memory, ClickHouse provides a dedicated in-memory cache for such indexes. The bigger this cache is, the fewer unnecessary loads will happen. The maximum cache size can be configured using server setting vector_similarity_index_cache_size. By default, the cache can grow up to 5 GB in size. The following log messages (system.text_log) indicate that the vector similarity index is being loaded. If such messages appear repeatedly for different vector search queries, this indicates that the cache size is too low.

2026-02-03 07:39:10.351635 [1386] f0ac5c85-1b1c-4f35-8848-87a1d1aa00ba : VectorSimilarityIndex Start loading vector similarity index

<...>

2026-02-03 07:40:25.217603 [1386] f0ac5c85-1b1c-4f35-8848-87a1d1aa00ba : VectorSimilarityIndex Loaded vector similarity index: max_level = 2, connectivity = 64, size = 1808111, capacity = 1808111, memory_usage = 8.00 GiB, bytes_per_vector = 4096, scalar_words = 1024, nodes = 1808111, edges = 51356964, max_edges = 233395072

The vector similarity index cache stores vector index granules. If individual vector index granules are bigger than the cache size, they will not be cached. Therefore, please make sure to calculate the vector index size (based on the formula in “Estimating storage and memory consumption” or system.data_skipping_indices) and size the cache correspondingly.

We reiterate that verifying and, if necessary, increasing the vector index cache should be the first step when investigating slow vector search queries. The current size of the vector similarity index cache is shown in system.metrics:

SELECT metric, value
FROM system.metrics
WHERE metric = 'VectorSimilarityIndexCacheBytes'

The cache hits and misses for a query with some query id can be obtained from system.query_log:

SYSTEM FLUSH LOGS query_log;

SELECT ProfileEvents['VectorSimilarityIndexCacheHits'], ProfileEvents['VectorSimilarityIndexCacheMisses']
FROM system.query_log
WHERE type = 'QueryFinish' AND query_id = '<...>'
ORDER BY event_time_microseconds;

For production use-cases, we recommend that the cache is sized large enough so that all vector indexes remain in memory at all times. Tuning quantization Quantization is a technique to reduce the memory footprint of vectors and the computational costs of building and traversing vector indexes. ClickHouse vector indexes supports the following quantization options:

Quantization	Name	Storage per dimension
f32	Single precision	4 bytes
f16	Half precision	2 bytes
bf16 (default)	Half precision (brain float)	2 bytes
i8	Quarter precision	1 byte
b1	Binary	1 bit

Quantization reduces the precision of vector searches compared to searching the original full-precision floating-point values (f32). However, on most datasets, half-precision brain float quantization (bf16) results in a negligible precision loss, therefore vector similarity indexes use this quantization technique by default. Quarter precision (i8) and binary (b1) quantization causes appreciable precision loss in vector searches. We recommend both quantizations only if the size of the vector similarity index is significantly larger than the available DRAM size. In this case, we also suggest enabling rescoring (vector_search_index_fetch_multiplier, vector_search_with_rescoring) to improve accuracy. Binary quantization is only recommended for 1) normalized embeddings (i.e. vector length = 1, OpenAI models are usually normalized), and 2) if the cosine distance is used as distance function. Binary quantization internally uses the Hamming distance to construct and search the proximity graph. The rescoring step uses the original full-precision vectors stored in the table to identify the nearest neighbours via cosine distance. Tuning data transfer The reference vector in a vector search query is provided by the user and generally retrieved by making a call to a Large Language Model (LLM). Typical Python code which runs a vector search in ClickHouse might look like this

search_v = openai_client.embeddings.create(input = "[Good Books]", model='text-embedding-3-large', dimensions=1536).data[0].embedding

params = {'search_v': search_v}
result = chclient.query(
   "SELECT id FROM items
    ORDER BY cosineDistance(vector, %(search_v)s)
    LIMIT 10",
    parameters = params)

Embedding vectors (search_v in above snippet) could have a very large dimension. For example, OpenAI provides models that generate embeddings vectors with 1536 or even 3072 dimensions. In above code, the ClickHouse Python driver substitutes the embedding vector by a human readable string and subsequently send the SELECT query entirely as a string. Assuming the embedding vector consists of 1536 single-precision floating point values, the sent string reaches a length of 20 kB. This creates a high CPU usage for tokenizing, parsing and performing thousands of string-to-float conversions. Also, significant space is required in the ClickHouse server log file, causing bloat in system.query_log as well. Note that most LLM models return an embedding vector as a list or NumPy array of native floats. We therefore recommend Python applications to bind the reference vector parameter in binary form by using the following style:

search_v = openai_client.embeddings.create(input = "[Good Books]", model='text-embedding-3-large', dimensions=1536).data[0].embedding

params = {'$search_v_binary$': np.array(search_v, dtype=np.float32).tobytes()}
result = chclient.query(
   "SELECT id FROM items
    ORDER BY cosineDistance(vector, reinterpret($search_v_binary$, 'Array(Float32)'))
    LIMIT 10"
    parameters = params)

In the example, the reference vector is sent as-is in binary form and reinterpreted as array of floats on the server. This saves CPU time on the server side, and avoids bloat in the server logs and system.query_log.

Administration and monitoring

The on-disk size of vector similarity indexes can be obtained from system.data_skipping_indices:

SELECT database, table, name, formatReadableSize(data_compressed_bytes)
FROM system.data_skipping_indices
WHERE type = 'vector_similarity';

Example output:

┌─database─┬─table─┬─name─┬─formatReadab⋯ssed_bytes)─┐
│ default  │ tab   │ idx  │ 348.00 MB                │
└──────────┴───────┴──────┴──────────────────────────┘

Differences to regular skipping indexes

As all regular skipping indexes, vector similarity indexes are constructed over granules and each indexed block consists of GRANULARITY = [N]-many granules ([N] = 1 by default for normal skipping indexes). For example, if the primary index granularity of the table is 8192 (setting index_granularity = 8192) and GRANULARITY = 2, then each indexed block will contain 16384 rows. However, data structures and algorithms for approximate neighbor search are inherently row-oriented. They store a compact representation of a set of rows and also return rows for vector search queries. This causes some rather unintuitive differences in the way vector similarity indexes behave compared to normal skipping indexes. When a user defines a vector similarity index on a column, ClickHouse internally creates a vector similarity “sub-index” for each index block. The sub-index is “local” in the sense that it only knows about the rows of its containing index block. In the previous example and assuming that a column has 65536 rows, we obtain four index blocks (spanning eight granules) and a vector similarity sub-index for each index block. A sub-index is theoretically able to return the rows with the N closest points within its index block directly. For queries with vector_search_with_rescoring = 1, ClickHouse can use these row positions to filter rows before computing the final distance from the stored vectors when the query plan allows this optimization. Without rescoring, ClickHouse uses the distances from the vector index directly through the virtual column _distance. Both modes still use the surrounding granule ranges to schedule reads, which is different from regular skipping indexes that skip data at the granularity of index blocks. The GRANULARITY parameter determines how many vector similarity sub-indexes are created. Bigger GRANULARITY values mean fewer but larger vector similarity sub-indexes, up to the point where a column (or a column’s data part) has only a single sub-index. In that case, the sub-index has a “global” view of all column rows and can directly return all granules of the column (part) with relevant rows (there are at most LIMIT [N]-many such granules). With vector_search_with_rescoring = 1, ClickHouse can then read the matching row positions and compute the exact distance for those rows. With a small GRANULARITY value, each sub-index can return up to LIMIT N candidate rows. As a result, more candidate rows may need to be read and post-filtered. Note that the search accuracy is with both cases equally good, only the processing performance differs. It is generally recommended to use a large GRANULARITY for vector similarity indexes and fall back to a smaller GRANULARITY values only in case of problems like excessive memory consumption of the vector similarity structures. If no GRANULARITY was specified for vector similarity indexes, the default value is 100 million.

Example

Queries:

Query

CREATE TABLE tab(id Int32, vec Array(Float32), INDEX idx vec TYPE vector_similarity('hnsw', 'L2Distance', 2)) ENGINE = MergeTree ORDER BY id;

INSERT INTO tab VALUES (0, [1.0, 0.0]), (1, [1.1, 0.0]), (2, [1.2, 0.0]), (3, [1.3, 0.0]), (4, [1.4, 0.0]), (5, [1.5, 0.0]), (6, [0.0, 2.0]), (7, [0.0, 2.1]), (8, [0.0, 2.2]), (9, [0.0, 2.3]), (10, [0.0, 2.4]), (11, [0.0, 2.5]);

WITH [0., 2.] AS reference_vec
SELECT id, vec
FROM tab
ORDER BY L2Distance(vec, reference_vec) ASC
LIMIT 3;

Response

   ┌─id─┬─vec─────┐
1. │  6 │ [0,2]   │
2. │  7 │ [0,2.1] │
3. │  8 │ [0,2.2] │
   └────┴─────────┘

Further example datasets that use approximate vector search:

Vector search with quantized codecs

The Quantized codec is experimental. Enable it with SET allow_experimental_codecs = 1. If you run into problems, kindly open an issue in the ClickHouse repository.

Introduction

A vector similarity index answers a nearest-neighbor query by traversing a graph and performs very well when the graph can be held in memory. Two conditions limit its applicability:

Scale. The time to build the graph and the memory required to store it — in addition to the vectors themselves — become the dominant cost.
Filtering. Under a selective WHERE filter, graph traversal becomes ineffective, because it either cannot reach the small set of rows that satisfy the predicate or must inspect a disproportionate number of candidates to locate them.

An exhaustive scan is subject to neither limitation: it requires no auxiliary structure, parts merge by concatenation, and a filter simply reduces the number of rows to be scanned. Its single disadvantage is the volume of data it must read: a scan over vectors stored at full Float32 precision is dominated by storage I/O, because the entire vector column has to be read from disk (or object storage) — for a dense embedding column that is the largest column in the table, and it compresses poorly. The Quantized column codec addresses this disadvantage. Each vector is stored twice: the original full-precision values, unchanged, together with a compact quantized code in a companion stream. A vector-search query first scans the codes using an inexpensive, SIMD-friendly distance function to assemble a shortlist of the most promising candidates, and then re-ranks that shortlist against the full-precision vectors. Because a code is a fraction of the size of the raw vector, the shortlist scan reads far fewer bytes from storage — and touches the full-precision column only for the handful of shortlisted candidates — while the final ranking remains accurate.

Declaring the codec

Attach a Quantized(...) codec to an Array(Float32) (or Array(Float64) / Array(BFloat16)) column. The codec is experimental, so enable allow_experimental_codecs first:

SET allow_experimental_codecs = 1;

CREATE TABLE vectors
(
    id UInt32,
    vec Array(Float32) CODEC(Quantized('rabitq', 1536))
)
ENGINE = MergeTree ORDER BY id;

The full-precision data is stored as usual; the codec only adds the companion code stream. The codec is fixed at table creation and cannot be added or changed with ALTER TABLE.

Quantization methods

Each method is a different point on the size / accuracy / metric trade-off. The dimensions argument is the vector length.

Quantized('rabitq', dimensions) — one sign bit per coordinate plus an unbiased cosine-correction factor (dimensions/8 + 4 bytes). A small, popcount-cheap, strong default. cosineDistance only.
Quantized('turboquant', dimensions) — two bits per coordinate (a 1-bit MSE code and a 1-bit residual code) for higher-fidelity candidates (dimensions/4 + 4 bytes). cosineDistance only.
Quantized('int8', dimensions) — one Int8 code per coordinate plus the vector norm (dimensions + 4 bytes); the largest but most faithful flat code. Supports L2Distance and cosineDistance.
Quantized('prefix', dimensions, leading_dimensions, 'int8'|'bf16') — Matryoshka: keeps only the leading_dimensions leading coordinates, as Int8 (with a per-vector scale) or BFloat16. Tiny codes for embeddings trained with Matryoshka Representation Learning. Supports L2Distance and cosineDistance.
Quantized('product', dimensions, nbits, m) — Product Quantization: a per-part codebook trained with k-means; each vector becomes m codes of nbits bits (so dimensions must be a multiple of m). The most compact option and highest recall per byte, at the cost of a training step during insert. Supports L2Distance and cosineDistance.

rabitq and turboquant require dimensions to be a multiple of 8.

Searching transparently

There is no special query syntax — write the same top-k query you would use for exact search:

WITH [/* reference vector of `dimensions` floats */] AS reference_vec
SELECT id
FROM vectors
ORDER BY cosineDistance(vec, reference_vec) ASC
LIMIT 10
SETTINGS vector_search_use_quantized_codes = 1;

With vector_search_use_quantized_codes = 1, the optimizer rewrites the query into the two-stage plan automatically: it scans the quantized codes to collect a shortlist, then rescores the shortlist against the full-precision vec. The setting is off by default, so without it the same query runs as a plain exact scan — the codec never changes results, it only offers a faster path when you opt in. Use a distance function the chosen method supports: cosineDistance for all methods, L2Distance additionally for int8, prefix and product.

Settings

allow_experimental_codecs — must be enabled to declare a Quantized codec (default: 0).
vector_search_use_quantized_codes — enable the two-stage shortlist-and-rescore rewrite (default: 0). When off, matching queries scan the full-precision vectors exactly.
vector_search_index_fetch_multiplier — how many candidates to shortlist relative to the query’s LIMIT: the scan keeps the top LIMIT × multiplier codes before rescoring. Larger values improve recall at the cost of more rescoring. The default is 1 (no oversampling), so raising it — for example to 10 or more — is usually needed for good recall.

Built for scale

The codec is a good fit for ClickHouse because the expensive part — the scan — is exactly what the ClickHouse engine is built to do well:

Vectorized. The scan kernels are written for SIMD, with runtime dispatch to the widest instructions the CPU supports: a hardware popcount for the sign-code methods (rabitq, turboquant) and wide fused-multiply-add for the others.
Parallel across cores and parts. A flat scan is trivially parallel, and ClickHouse treats it as such: distances are computed across all available threads and over all parts of a table at once, with only the final top-k merge serialized.
Distributed. On a sharded cluster the work fans out across machines — each shard scans its own slice in parallel and the coordinator merges the shortlists.
Columnar and filter-friendly. The quantized codes occupy their own column, compressed and read through the same I/O path as every other column, so a selective WHERE simply leaves fewer codes to scan.
No separate build step. The codes are produced as the vectors are written and merge by concatenation — there is no index to construct, tune, or rebuild, so a table is ready to search as soon as its data lands.

The codes are only ever a candidate generator; the full-precision column, retained in place, provides the final accurate ranking.

Quantized Bit (QBit)

One common approach to speed up exact vector search is to use a lower-precision float data type. For example, if vectors are stored as Array(BFloat16) instead of Array(Float32), the data size is reduced by half, and query runtimes are expected to decrease proportionally. This method is known as quantization. While it speeds up computation, it may reduce result accuracy despite performing an exhaustive scan of all vectors. With traditional quantization, we lose precision both during search and when storing the data. In the example above, we would store BFloat16 instead of Float32, meaning we can never perform a more accurate search later, even if desired. One alternative approach is to store two copies of the data: quantized and full-precision. While this works, it requires redundant storage. Consider a scenario where we have Float64 as original data and want to run searches with different precision (16-bit, 32-bit, or full 64-bit). We would need to store three separate copies of the data. ClickHouse offers the Quantized Bit (QBit) data type that addresses these limitations by:

Storing the original full-precision data.
Allowing quantization precision to be specified at query time.

This is achieved by storing data in a bit-grouped format (meaning all i-th bits of all vectors are stored together), enabling reads at only the requested precision level. You get the speed benefits of reduced I/O and computation from quantization while keeping all original data available when needed. When maximum precision is selected, the search becomes exact. To declare a column of QBit type, use the following syntax:

column_name QBit(element_type, dimension[, stride])

Where:

element_type – the type of each vector element. Supported types are Int8, BFloat16, Float32, and Float64
dimension – the number of elements in each vector
stride – optional. A divisor of dimension that partitions the dimensions into dimension / stride contiguous groups stored in separate streams, so a search over only the leading dimensions reads fewer streams (useful for Matryoshka embeddings). Defaults to dimension, in which case the type is byte-identical to a non-strided QBit. See the QBit data type page for details.

Creating a `QBit` Table and Adding Data

CREATE TABLE fruit_animal (
    word String,
    vec QBit(Float64, 5)
) ENGINE = MergeTree
ORDER BY word;

INSERT INTO fruit_animal VALUES
    ('apple', [-0.99105519, 1.28887844, -0.43526649, -0.98520696, 0.66154391]),
    ('banana', [-0.69372815, 0.25587061, -0.88226235, -2.54593015, 0.05300475]),
    ('orange', [0.93338752, 2.06571317, -0.54612565, -1.51625717, 0.69775337]),
    ('dog', [0.72138876, 1.55757105, 2.10953259, -0.33961248, -0.62217325]),
    ('cat', [-0.56611276, 0.52267331, 1.27839863, -0.59809804, -1.26721048]),
    ('horse', [-0.61435682, 0.48542571, 1.21091247, -0.62530446, -1.33082533]);

Vector Search with `QBit`

Let’s find the nearest neighbors to a vector representing word ‘lemon’ using L2 distance. The third parameter in the distance function specifies the precision in bits - higher values provide more accuracy but require more computation. You can find all available distance functions for QBit here. Full precision search (64-bit):

SELECT
    word,
    L2DistanceTransposed(vec, [-0.88693672, 1.31532824, -0.51182908, -0.99652702, 0.59907770], 64) AS distance
FROM fruit_animal
ORDER BY distance;

   ┌─word───┬────────────distance─┐
│ apple  │ 0.14639757188169716 │
│ banana │   1.998961369007679 │
│ orange │   2.039041552613732 │
│ cat    │   2.752802631487914 │
│ horse  │  2.7555776805484813 │
│ dog    │   3.382295083120104 │
   └────────┴─────────────────────┘

Reduced precision search:

SELECT
    word,
    L2DistanceTransposed(vec, [-0.88693672, 1.31532824, -0.51182908, -0.99652702, 0.59907770], 12) AS distance
FROM fruit_animal
ORDER BY distance;

   ┌─word───┬───────────distance─┐
│ apple  │  0.757668703053566 │
│ orange │ 1.5499475034938677 │
│ banana │ 1.6168396735102937 │
│ cat    │  2.429752230904804 │
│ horse  │  2.524650475528617 │
│ dog    │   3.17766975527459 │
   └────────┴────────────────────┘

Notice that with 12-bit quantization, we get a good approximation of the distances with faster query execution. The relative ordering remains largely consistent, with ‘apple’ still being the closest match.

Performance Considerations

The performance benefit of QBit comes from reduced I/O operations, as less data needs to be read from storage when using lower precision. Moreover, when the QBit contains Float32 data, if the precision parameter is 16 or below, there will be additional benefits from reduced computation. The precision parameter directly controls the trade-off between accuracy and speed:

Higher precision (closer to the original data width): More accurate results, slower queries
Lower precision: Faster queries with approximate results, reduced memory usage

References

Blogs:

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Exact vector search

Example

Approximate vector search

Vector Similarity Indexes

Creating a Vector Similarity Index

Using a Vector Similarity Index

Performance tuning

Administration and monitoring

Differences to regular skipping indexes

Example

Vector search with quantized codecs

Introduction

Declaring the codec

Quantization methods

Searching transparently

Settings

Built for scale

Quantized Bit (QBit)

Creating a `QBit` Table and Adding Data

Vector Search with `QBit`

Performance Considerations

References

​Exact vector search

​Example

​Approximate vector search

​Vector Similarity Indexes

​Creating a Vector Similarity Index

​Using a Vector Similarity Index

​Performance tuning

​Administration and monitoring

​Differences to regular skipping indexes

​Example

​Vector search with quantized codecs

​Introduction

​Declaring the codec

​Quantization methods

​Searching transparently

​Settings

​Built for scale

​Quantized Bit (QBit)

​Creating a QBit Table and Adding Data

​Vector Search with QBit

​Performance Considerations

​References

Exact vector search

Example

Approximate vector search

Vector Similarity Indexes

Creating a Vector Similarity Index

Using a Vector Similarity Index

Performance tuning

Administration and monitoring

Differences to regular skipping indexes

Example

Vector search with quantized codecs

Introduction

Declaring the codec

Quantization methods

Searching transparently

Settings

Built for scale

Quantized Bit (QBit)

Creating a `QBit` Table and Adding Data

Vector Search with `QBit`

Performance Considerations

References