ClickHouse Text Indexes, Direct Read, and the Compatibility Trap
Daniel Brodsky9 min read
How we shipped a text index that did byte-for-byte nothing, why every diagnostic said it was working, and what the relevant settings actually mean.
TL;DR
Our ClickHouse Cloud service runs a 26.2 binary with compatibility = '25.4'. That setting pins every setting introduced after 25.4 back to its old default. One of them, use_skip_indexes_on_data_read, is a prerequisite for direct read on ClickHouse < 26.4. With it off, our text index on conversation_items.message could only prune granules. For a common search term nothing prunes, so ClickHouse fell back to reading the entire message column — the exact full scan the index was added to eliminate.
session text search, 'error', 30d before after
bytes read 18.68 GiB 716 MiB (26x)
duration 8.06 s 0.45 s (18x)
peak memory 5.27 GiB 215 MiB (25x)Two changes are required, not one: enable the setting and restructure the query.
The symptom
After deploying the text index, text search behaved bimodally:
needle duration bytes read
zzqqxrare (matches nothing) 0.24 s 0 B
error (matches ~13% of rows) 34.2 s 18.4 GiBA rare needle was instant. A common needle was worse than useless. That shape is the fingerprint of the problem described below, and it is worth learning to recognise: an index that only helps when you don't need it.
Background: what a skip index actually does
Granules
ClickHouse does not index rows. It splits each column into granules — by default 8192 rows, capped by index_granularity_bytes (10 MB), which is what actually binds for wide text columns. In our production table a granule is ~152 rows; in the synthetic table used below, ~7042.
A skip index stores a small summary per granule (or per N granules, the GRANULARITY clause).
The two-phase contract
This is the part most people miss. A classic skip index (minmax, set, bloom_filter, tokenbf_v1, ngrambf_v1) works in two phases:
- Prune. Use the per-granule summary to rule out granules that definitely cannot match.
- Re-check. For every granule that survives, read the actual column data and evaluate the original predicate row by row.
Phase 2 is not optional, because these summaries are lossy. A bloom filter answers "maybe" or "definitely not" — never "definitely yes". The ClickHouse docs are explicit:
ClickHouse still verifies each surviving row using the original predicate against the original column data.
The consequence
Skip-index value comes entirely from phase 1. If your term appears in most granules, nothing prunes, phase 2 reads everything, and you have paid for an index that bought you nothing — plus the cost of loading the index itself.
This is why a term matching 13% of rows, scattered uniformly, defeats a classic skip index completely. 13% of rows spread across granules means ~100% of granules contain at least one match.
Text indexes are different — in principle
A text index is not a lossy summary. It is a real inverted index: for each token it stores a postings list of exactly which rows contain it. No false positives.
ALTER TABLE conversation_items
ADD INDEX idx_conv_items_message_text message
TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lowerUTF8(message))tokenizer— how text is split into tokens.splitByNonAlphasplits on any non-alphanumeric character. Alternatives includengrams(n)for substring search andarrayfor pre-split values.preprocessor— an expression applied to the column before tokenizing.lowerUTF8(message)gives case-insensitive matching for free. Without it the index is case-sensitive, which for us meantsundaymissed 98.73% of its true matches.
Because the postings list is exact, the engine can skip phase 2 entirely. That capability has a name.
Direct read
Direct read answers the query exclusively using the text index (i.e. text index lookups) without accessing the underlying text column.
That is the whole point of a text index. Without direct read, a text index degrades into a somewhat better bloom filter — still stuck in the two-phase contract above.
Direct read supports hasToken, hasAllTokens, hasAnyTokens, combinable with AND, OR, NOT. It does not apply to like, position, positionCaseInsensitive, or multiSearchAny — those force a column read no matter what index exists. This bites: a "safety" re-check such as hasAllTokens(...) AND positionCaseInsensitive(...) silently defeats direct read, because the second conjunct needs the column.
The settings, defined
setting what it controls default ours
use_skip_indexes granule pruning at planning time (phase 1) 1 1
use_skip_indexes_on_data_read skip indexes applied during the read phase, 1 0
enabling row-level filtering
query_plan_direct_read_from_text_index master on/off for direct read 1 1
query_plan_text_index_add_hint adds a text-index-derived hint filter 1 1
text_index_hint_max_selectivity apply that hint only if the term is selective 0.2 0.2
compatibility pins settings to an older version's defaults (unset) '25.4'use_skip_indexes vs use_skip_indexes_on_data_read
These sound like the same thing. They are not — they differ by stage in the pipeline:
use_skip_indexes→ planning stage. The index selects which granule ranges to read. This is phase 1, granule pruning.use_skip_indexes_on_data_read→ read stage. The index is consulted while rows are being read, so it can filter at row level and satisfy the predicate itself.
Direct read is built on the second path. From the docs:
use_skip_indexes_on_data_read was a prerequisite for direct read in ClickHouse versions < 26.4.
So on 26.2 it is a hard gate. Note the asymmetry that makes this so easy to misdiagnose: the master switch query_plan_direct_read_from_text_index was 1 in production the whole time. It is necessary but not sufficient on our version.
Measured on a 500k-row table, common token, everything else identical:
settings rows scanned bytes read
use_skip_indexes=0 (index fully disabled) 500 k 665.58 MiB
use_skip_indexes=1, on_data_read=0 ← prod 500 k 665.58 MiB
use_skip_indexes=1, on_data_read=1 500 k 488.28 KiBThe middle row is the finding: with the flag off, the text index was worth exactly nothing, byte-identical to having no index at all. Rows scanned never change — only whether the column is read.
Turning off use_skip_indexes while leaving the other flag on still produces a genuine full scan, which confirms the two settings govern different stages.
compatibility
compatibility = 'X.Y' makes the server behave as though it were version X.Y for the purposes of setting defaults. Any setting introduced after X.Y silently reverts to its pre-existing default. It is a legitimate tool for pinning behaviour across upgrades. It is also a footgun, because a low value means you upgrade the binary and get none of the new optimizations, with no warning anywhere.
Ours is 25.4, on a 26.2.1.525 binary. Currently suppressed:
setting stock 26.2 ours
use_skip_indexes_on_data_read 1 0
use_skip_indexes_for_disjunctions 1 0
use_skip_indexes_if_final 1 0
use_skip_indexes_if_final_exact_mode 1 0Only the first was investigated. The other three are unexamined and may be costing us elsewhere.
The causal chain
compatibility = '25.4'
└─> use_skip_indexes_on_data_read = 0 (stock default is 1)
└─> on 26.2, direct read's prerequisite is unmet
└─> text index degrades to granule-pruning only
└─> common term prunes nothing
└─> full re-check of ~4M rows = 18 GiB, 8-13 sEvery link is backed by both documentation and a measurement.
Evidence
1. Isolate the column read
Same filters, differing only by the text predicate:
-- A: with the text predicate -> 18.06 GiB, 3.98M rows, 10.6 s
-- B: text predicate removed -> 187 MiB, 4.01M rows, 0.1 sIdentical rows scanned, ~17.9 GiB apart. The delta is the message column. This rules out "it's just the other columns" and proves direct read was not happening.
2. The setting fixes it on production
... AND hasToken(message, 'error')
SETTINGS use_skip_indexes_on_data_read = 1;
-- 169 MiB, 0.48 s, identical result109x less data, 26x faster.
3. compatibility is the driver, confirmed on production
... SETTINGS compatibility = '26.2';
-- 179 MiB, 0.33 sRaising compatibility alone reproduces the fix, which is what distinguishes "compatibility pinned it" from "someone set the flag explicitly."
4. Reproduced locally, in both directions
On a stock OSS 26.2.1.1139 container (same version line as prod), with a synthetic table:
variant bytes read
stock defaults 62.45 MiB
compatibility='25.4' 727.55 MiB
positionCaseInsensitive full-scan reference 727.55 MiBThe degraded number is byte-identical to a genuine full scan — the index contributes nothing. Three alternating-order trials per variant, all returning identical results.
What we ruled out
Each of these was tested and made no difference. Recorded so nobody re-runs them:
- Packed / Compact part format — Cloud uses both (prod: 32 Compact, 20 Wide). Forcing Compact parts locally, direct read still works.
- Patch version — direct read works identically on 26.2.1.1139 and 26.2.18.8.
- Extra WHERE conjuncts — direct read survives added predicates on other columns; the control (same conjuncts, no text predicate) differs by exactly the 488 KiB of postings.
- Legacy indexes on the same column — tokenbf_v1 and ngrambf_v1 also exist on message. Excluding them via ignore_data_skipping_indices changed nothing (18.07 GiB either way), and three coexisting indexes reproduce fine locally.
- SharedMergeTree / Cloud-only bug — this was our leading theory for a long time and it was wrong. ClickHouse's own write-up confirms text indexes are fully compatible with ClickHouse Cloud, including the packed part format.
Second finding: query shape blocks pushdown
Enabling the setting is necessary but not sufficient for our actual session-search query. The text predicate must sit in its own subquery scan rather than in a WHERE alongside a JOIN, otherwise it is not applied at scan time.
-- BAD: text predicate entangled with the JOIN
FROM conversation_items ci
INNER JOIN (...) tf ON tf.trace_id = ci.trace_id
WHERE <base filters> AND hasToken(ci.message, 'error') AND NOT (...)
-- GOOD: text filter gets its own scan, then joins
FROM (
SELECT session_id, trace_id, type FROM conversation_items
WHERE <base filters> AND hasToken(message, 'error')
) ci
INNER JOIN (...) tf ON tf.trace_id = ci.trace_id
WHERE NOT (...)Production, 3 alternating trials each, all returning the identical 9,673 sessions:
shape on_data_read bytes best peak RAM
current 0 (today) 18.68 GiB 8.06 s 5.27 GiB
current 1 12.70 GiB 4.27 s 5.32 GiB
restructured 0 18.68 GiB 9.46 s 364 MiB
restructured 1 716 MiB 0.45 s 215 MiBNote the restructure alone cuts peak memory 5.27 GiB → 364 MiB. That was a separate mystery we had been attributing to index postings; it is the JOIN shape.
Caveat: this effect does not reproduce on a 500k-row local table, where both shapes are byte-identical. Only the compatibility effect reproduces at small scale. Verify query-shape changes at production scale.
The fix
- Per-query setting. Add
SETTINGS use_skip_indexes_on_data_read = 1to the text-search query builder. Preferred over raising the service-wide compatibility, which changes behaviour for every query on the platform and deserves its own deliberate rollout. It becomes a harmless no-op once we are on 26.4+. - Restructure the query so the text predicate gets its own scan before the JOIN.
- Avoid positionCaseInsensitive re-checks next to hasToken/hasAllTokens. The re-check forces the column read and cancels direct read. Case-insensitivity belongs in the index preprocessor, not in the predicate.
Playbook: diagnosing this class of problem
- Isolate the column. Run the query with and without the text predicate. Equal rows scanned but a large byte delta means the column is being read and your index is not doing its job.
- Check compatibility first. Before blaming the engine, the Cloud build, or the query, run
SELECT version(), getSetting('compatibility'); - Diff settings against a stock container of the same version. Do not look settings up individually — some are absent from Cloud's system.settings entirely, so only a diff surfaces them.
SELECT name, value FROM system.settings
WHERE name LIKE '%text_index%' OR name LIKE '%skip_indexes%' ORDER BY name;- Measure bytes, not wall time. read_bytes is structural; duration moves with cache and cluster load. Several early conclusions in this investigation were wrong because runs taken hours apart were compared.
- Run variants back-to-back in alternating order, with repeats. A single A/B pair is not evidence.
- Be suspicious of an index that only helps on rare terms. That is the signature of prune-only mode.
Why this hid so well
Nothing errors. The docs say the optimization is on by default. EXPLAIN shows the index being used. query_plan_direct_read_from_text_index reads 1. The index is materialized and correct. The only visible signal is bytes read.
A low compatibility makes ClickHouse lie by omission: the feature is present, documented as default-on, and simply never runs.
Reproduce it yourself
No production access needed. Spin up clickhouse/clickhouse-server:26.2.1.1139, build a 500k-row table with a text index, and run the two variants:
CREATE TABLE ci (
service_name String, tenant_id String, type String, end_timestamp DateTime,
trace_id String, session_id String, message String,
INDEX idx_msg_text message
TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lowerUTF8(message)) GRANULARITY 1)
ENGINE = MergeTree ORDER BY (service_name, tenant_id, end_timestamp)
SETTINGS allow_experimental_full_text_index = 1;
-- stock defaults: answered from the index
SELECT count() FROM ci WHERE hasToken(message, 'error');
-- prod's configuration: full column read, identical result
SELECT count() FROM ci WHERE hasToken(message, 'error')
SETTINGS compatibility = '25.4';Expect the second to match a positionCaseInsensitive full scan byte for byte.
References
- ClickHouse text indexes — direct read, supported functions, settings.
- ClickHouse full-text search — background on why direct read was added; it names our exact symptom, that frequent terms leave nearly every granule surviving and turn phase 2 into a full scan.