ClickHouse Search

ClickHouse search spans five different mechanisms that share one keyword and almost nothing else: substring matching with LIKE and the string functions, skip indexes that let those matches skip granules, a full-text index that arrived as experimental and is maturing release by release, vector similarity search over embeddings, and the option of handing search to an external engine and keeping ClickHouse for the analytics.

Choosing among them is a question of what the query looks like, how much of the table it touches, and what latency the product needs. This page is the ClickHouse search map: each mechanism, the query shape it serves, the DDL, the version it needs, and the trap that catches teams on their first attempt.

The posts in this category cover the pieces: case-sensitive searches, the fan-trap performance problem, vector search storage, Manticore for full text, and the 26.x text index and Iceberg writes. This page is the decision guide across them.

Mechanism one: LIKE, ILIKE and the string functions

The simplest ClickHouse search is a substring predicate: LIKE '%chargeback%', ILIKE for case-insensitive, position(), match() for regular expressions, hasToken() for whole-word matching and multiSearchAny() for several needles in one pass. They are all full scans of the column over the granules the primary key admits, executed with SIMD string search, and on a narrow time range they are fast enough that nothing else is needed. The archive post ClickHouse case-sensitive searches works through the case rules and the collation surprises.

SELECT count()
FROM logs.app
WHERE ts >= now() - INTERVAL 1 HOUR
  AND service = 'checkout'
  AND message ILIKE '%chargeback%';

-- Whole-word, tokenised, faster than LIKE and index-friendly
SELECT count()
FROM logs.app
WHERE ts >= now() - INTERVAL 1 HOUR
  AND hasToken(message, 'chargeback');

The trap is the leading wildcard on a wide time range: LIKE '%needle%' over a month of logs reads every byte of the message column for that month, because nothing can skip. The fix is not a different function; it is mechanism two.

Mechanism two: skip indexes that make substring search skip granules

Three skip index types serve text. tokenbf_v1 is a bloom filter over the tokens in each granule and makes hasToken() and whole-word LIKE skip granules that cannot contain the word. ngrambf_v1 is a bloom filter over character n-grams and makes arbitrary substring LIKE '%abc%' skip granules. bloom_filter on a LowCardinality or string column serves equality. None of them finds rows; they exclude granules, and the remaining granules are still scanned, so the gain is proportional to how rare the needle is.

ALTER TABLE logs.app
    ADD INDEX idx_message_tokens message TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 4;
ALTER TABLE logs.app
    ADD INDEX idx_message_ngram  message TYPE ngrambf_v1(4, 32768, 3, 0) GRANULARITY 4;
ALTER TABLE logs.app MATERIALIZE INDEX idx_message_tokens;

-- Prove the index is used and how much it skips
EXPLAIN indexes = 1
SELECT count() FROM logs.app WHERE ts >= now() - INTERVAL 7 DAY AND hasToken(message, 'chargeback');
-- Skip  Name: idx_message_tokens  Type: tokenbf_v1  Parts: 40/40  Granules: 1210/48200

The parameters are the bloom filter size in bytes, the number of hash functions and the seed; the size sets the false-positive rate against the number of distinct tokens per index granule, and the archive’s fan-trap post below is where an undersized filter shows up as an index that costs more than it saves. A useful rule is that an index is worth keeping when EXPLAIN indexes shows it dropping more than three-quarters of the granules the primary key admitted.

Mechanism three: the full-text index

A skip index excludes granules; an inverted index finds rows. ClickHouse’s full-text index, introduced as the experimental inverted type in 23.x, renamed full_text in 24.x and reworked as the text index type in the 25.x and 26.x line, stores a posting list per token so that hasToken() and the search functions resolve to row ranges without scanning the column. The 26.x work also added the QBit-based text index and Iceberg write support, which the archive post ClickHouse 26.x: QBit, text index and Iceberg writes covers as a release analysis.

-- Requires the experimental flag on releases before the index is GA; confirm on your version
SET allow_experimental_full_text_index = 1;

ALTER TABLE logs.app
    ADD INDEX idx_message_text message TYPE text(tokenizer = 'default') GRANULARITY 1;
ALTER TABLE logs.app MATERIALIZE INDEX idx_message_text;

SELECT ts, service, message
FROM logs.app
WHERE hasToken(message, 'chargeback') AND hasToken(message, 'declined')
ORDER BY ts DESC
LIMIT 50;

Three cautions. The index is per part and per granule, so the posting lists are rebuilt on every merge, which is insert-side cost that a log table at high volume will notice. Tokenisation is configurable but not linguistic: there is no stemming, no synonym expansion and no relevance ranking in the engine, so a product search box that needs “ran” to match “running” is still mechanism five. And the feature moved between experimental and stable across releases; check system.settings for the flag and the release notes for the exact version before building on it.

Mechanism four: vector similarity search over embeddings

Vector search in ClickHouse stores embeddings as Array(Float32) and answers nearest-neighbour queries with cosineDistance() or L2Distance(). Without an index that is an exact scan, which on a few million vectors of a few hundred dimensions is seconds and on tens of millions is too slow. The vector_similarity skip index, using the usearch HNSW implementation and stable since 25.x after an experimental period as annoy and usearch types, makes the query approximate and fast. The archive’s Vector search storage in ClickHouse, part two covers the storage side, including quantisation.

CREATE TABLE search.documents
(
    doc_id     UInt64,
    tenant_id  UInt32,
    title      String,
    embedding  Array(Float32),
    CONSTRAINT dim CHECK length(embedding) = 768,
    INDEX idx_emb embedding TYPE vector_similarity('hnsw', 'cosineDistance', 768) GRANULARITY 100000000
)
ENGINE = MergeTree
ORDER BY (tenant_id, doc_id);

-- Approximate nearest neighbours, filtered by tenant, then re-ranked exactly
WITH ${QUERY_EMBEDDING} AS q
SELECT doc_id, title, cosineDistance(embedding, q) AS dist
FROM search.documents
WHERE tenant_id = 4711
ORDER BY dist
LIMIT 20;

The design rules are the same as for any skip index plus two of their own. The HNSW graph is built per part at merge time, so the table wants large, stable parts and a partitioning that keeps hot vectors together. And a filter on another column, such as the tenant above, is applied after the approximate search unless the sort key puts the tenant first, so multi-tenant vector search sorts by tenant and accepts a per-tenant graph. Quantising the embeddings to BFloat16 or Float16, supported since 24.x and 25.x, halves the storage with a small recall cost.

Mechanism five: an external search engine beside ClickHouse

When the product needs relevance ranking, stemming, synonyms, highlighting and faceted navigation, the honest answer is an engine built for it: Manticore, Elasticsearch or OpenSearch, or Typesense, fed from the same pipeline that feeds ClickHouse, with ClickHouse keeping the analytics and the engine keeping the search box. The archive post ClickHouse and Manticore full-text search with a plain index shows the pairing in detail.

The integration is a materialized view or a Kafka topic that mirrors the searchable columns, and the query path joins search results to ClickHouse by id. This is also the pattern for teams migrating logs off Elasticsearch, where ClickHouse takes the storage and aggregation and a small search tier stays for the interactive text queries.

The fan trap: the ClickHouse search performance problem nobody expects

A search query joined to a fact table with a one-to-many relationship, then aggregated, multiplies the fact rows by the number of matches and produces both a slow query and a wrong total. The archive post ClickHouse fan trap performance works through the mechanism; the fix is to resolve the search to a set of ids first, deduplicate, and then join, which in ClickHouse is an IN subquery over the search result rather than a join.

-- Wrong: join fans out the events
SELECT count()
FROM analytics.events AS e
JOIN search.documents AS d ON e.doc_id = d.doc_id
WHERE hasToken(d.title, 'chargeback');

-- Right: resolve the search to ids, then filter
SELECT count()
FROM analytics.events
WHERE doc_id IN (SELECT doc_id FROM search.documents WHERE hasToken(title, 'chargeback'));

Choosing among the five ClickHouse search mechanisms

Most log platforms end on a combination of one, two and three: the token bloom index for the common case, the text index for the search page, and plain LIKE for the ad-hoc query on the last hour. Most RAG platforms end on four with a tenant-first sort key. Most customer-facing product search ends on five, with ClickHouse doing what it is best at behind it.

Measuring a ClickHouse search query

The evidence is the same as for any query. EXPLAIN indexes = 1 shows which index ran and how many granules survived it, which is the number that decides whether mechanism two or three is earning its keep. system.query_log gives read_rows and read_bytes, which for a text search should be a small fraction of the column’s total after the index is in place. And for vector search, recall against an exact scan on a sample is the number that validates the HNSW parameters; a recall below 0.9 on the product’s own queries means the graph was built too coarsely.

-- Recall check for the vector index: exact vs approximate on 100 sample queries
WITH exact AS
(
    SELECT doc_id FROM search.documents
    WHERE tenant_id = 4711
    ORDER BY cosineDistance(embedding, ${QUERY_EMBEDDING})
    LIMIT 20
    SETTINGS use_skip_indexes = 0
),
approx AS
(
    SELECT doc_id FROM search.documents
    WHERE tenant_id = 4711
    ORDER BY cosineDistance(embedding, ${QUERY_EMBEDDING})
    LIMIT 20
)
SELECT count() / 20 AS recall
FROM approx WHERE doc_id IN (SELECT doc_id FROM exact);

ClickHouse search on the sort key: the mechanism zero

Before any of the five, there is the case the primary index already solves. A ClickHouse search whose predicate is a prefix of the sort key, or an equality on a low-cardinality column that leads it, is not a search problem at all: the primary index binary-searches the marks and reads a handful of granules. A table sorted by (tenant_id, event_type, toStartOfHour(ts)) answers “all login_failed events for tenant 42 this afternoon” without a skip index, a text index or a string function.

The first question on every ClickHouse search ticket is whether the column being searched belongs in the sort key instead, and the answer is yes surprisingly often when the column is an enum, a status, a type or an identifier rather than free text.

The limit is that the primary index is a prefix structure. A predicate on the third sort-key column with no condition on the first two still reads every granule, which is where mechanism two starts. EXPLAIN indexes = 1 shows which of the two indexes pruned the read; when the PrimaryKey line already reports a small fraction of parts and granules, no further ClickHouse search machinery will improve the query meaningfully.

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE tenant_id = 42
  AND event_type = 'login_failed'
  AND ts >= now() - INTERVAL 6 HOUR;
-- PrimaryKey: Parts 3/48, Granules 41/61440
-- nothing left for a skip index to do

Common ClickHouse search mistakes and what each one costs

The first mistake is a leading wildcard on an indexed column: LIKE '%error%' cannot use a prefix index, and a tokenbf skip index built for exact tokens does not help either when the term is mid-token. Only an ngrambf index, or the text index with an n-gram tokenizer, prunes a mid-string ClickHouse search. The second mistake is building a skip index with GRANULARITY 1 on a column with high per-granule cardinality, which makes the bloom filter saturate and skip nothing while still costing the read of the index file.

The third is forgetting that a skip index applies only to parts written or materialized after it was added; a ClickHouse search on the old parts reads everything until MATERIALIZE INDEX has run, and that mutation is a full rewrite of the index files across the table.

The fourth mistake is case. ILIKE and positionCaseInsensitive are correct and slow; a lower-cased materialized column with the index on it is the fast form, and the archive post on case-sensitive searches walks through the measured difference.

The fifth is the fan trap covered above, and it is the one that produces the most severe incidents because the query looks right and the numbers are wrong rather than slow. Each of these is visible in system.query_log before anyone complains: rising read_rows for the same query hash, or result_rows jumping after a join was added, is the signal.

Sizing the index for a ClickHouse search workload

Skip indexes and the text index are not free at write time. A tokenbf index on a 1 KB log line column adds a bloom filter per granule, and the bits_per_row and hash-function parameters decide both the false-positive rate and the size on disk; system.data_skipping_indices reports data_compressed_bytes per index so the cost is measurable per table rather than guessed. The text index is larger again, typically a meaningful fraction of the indexed column, and its cost is paid at insert and at every merge.

A ClickHouse search design that adds three indexes to a 50 TB table has changed the merge budget of the cluster, and system.merges will show it as longer merges before the query-side benefit is even visible.

The sizing rule used in ChistaDATA engagements is to index one column per search shape, not every column a user might filter, and to measure the ratio of granules skipped to index bytes read on the real query mix. An index that skips less than half the granules on the queries that matter is usually better replaced by a sort-key change or a projection, and a text index that serves one query a day is better removed than maintained through every merge.

Version notes

Token and n-gram bloom skip indexes are 20.x and later. The inverted index arrived experimental in 23.1, was renamed full_text in 24.x, and the text index type with the newer tokenisers is the 25.x and 26.x form; the experimental flag and its name changed along the way, so confirm both on the running version. vector_similarity with usearch is stable since 25.x, replacing the experimental annoy and usearch index types. BFloat16 arrived in 24.x. The reference for the current state is the ClickHouse full-text index documentation, and it changes with every release.

Reading the archive

Start with the case-sensitive-search post for mechanism one and the fan-trap post before writing any search that joins. The 26.x release post covers where the text index is heading, the vector-storage post covers mechanism four, and the Manticore post is the worked example of mechanism five.

ChistaDATA’s ClickHouse consulting practice scopes search designs against this map, including the Elasticsearch-to-ClickHouse migrations where the answer is usually a split rather than a replacement, and 24×7 ClickHouse support covers the clusters afterwards. Prototype the chosen mechanism on staging with a production-sized sample and the product’s real queries, measure granules skipped or recall, and only then commit to the index, because every one of the five is expensive to change once the table is large.