Overview
ClickHouse 25.9 introduces streaming secondary indices, a fundamental change to how secondary indexes (minmax, set, bloom filter, vector, text) are evaluated during query execution. Instead of fully scanning indexes upfront before reading table data, the new approach interleaves index checks with data reads incrementally and on-demand, eliminating startup delays and reducing both query latency and memory usage, especially for LIMIT queries on large tables.
What You'll Learn
1
How ClickHouse streaming secondary indices eliminate upfront index scanning by interleaving index checks with data reads
2
Why sequential index evaluation causes startup delays and wastes work for LIMIT queries on large tables
3
How to enable or disable streaming secondary indices using the use_skip_indexes_on_data_read setting
4
When streaming secondary indices provide the greatest performance benefit (early-exit queries on large tables)
Prerequisites & Requirements
- Understanding of ClickHouse granules as the smallest processing units (typically 8,192 rows each)
- Familiarity with secondary index types in ClickHouse (minmax, set, bloom filter, vector, text)
- Basic understanding of ClickHouse primary index analysis and MergeTree engine
- ClickHouse 25.9 or later for streaming secondary indices support
Key Questions Answered
What are streaming secondary indices in ClickHouse 25.9?
Streaming secondary indices are a new evaluation strategy in ClickHouse 25.9 that interleaves index checks with data reads instead of scanning the entire index upfront. When ClickHouse is about to read a granule, it first checks the corresponding secondary index entry. If the index indicates the granule can be skipped, it's never read. Otherwise, the granule is processed while scanning continues on subsequent granules concurrently.
How did ClickHouse evaluate secondary indices before version 25.9?
Before ClickHouse 25.9, secondary indices were evaluated sequentially in two phases: first, ClickHouse fully scanned all index entries upfront to determine which granules to read, then the selected granules were streamed into the query engine. This caused startup delays, heavy index scans that could cost more than processing the data itself, and inefficiency with LIMIT queries since the entire index was scanned regardless.
How to enable streaming secondary indices in ClickHouse?
Streaming secondary indices are controlled by the setting use_skip_indexes_on_data_read. Set it to 1 to enable streaming evaluation (use_skip_indexes_on_data_read = 1) or 0 to use the traditional upfront scanning approach. This can be specified as a query-level SETTINGS parameter.
How much faster are streaming secondary indices compared to sequential index scans?
In the article's benchmark with a billion-row table and a 2.21 GiB bloom filter index, a LIMIT 1 query took approximately 10.173 seconds without streaming indices versus 2.471 seconds with streaming indices enabled — over 4x faster. Memory usage also dropped from 8.90 MiB to 4.48 MiB peak.
Why are LIMIT queries inefficient with traditional ClickHouse secondary index evaluation?
With traditional sequential evaluation, ClickHouse scans the entire secondary index upfront before query execution begins, even for LIMIT queries that only need a small number of rows. This means the full index must be processed regardless of whether early results satisfy the LIMIT. Streaming indices fix this by halting index checks and granule reads as soon as the LIMIT condition is met.
What types of secondary indices benefit from streaming evaluation in ClickHouse?
All secondary index types in ClickHouse benefit from streaming evaluation, including minmax, set, bloom filter, vector, and text indices. The improvement is especially significant for queries with highly selective WHERE filters over huge tables, and for early-exit queries using LIMIT on large datasets where the index itself can be multiple terabytes in size.
What are the drawbacks of upfront secondary index evaluation in ClickHouse?
Upfront secondary index evaluation has three main drawbacks: startup delay because index analysis must complete before query execution begins, heavy index scans where scanning the index can cost more than processing the selected data (especially with highly selective filters on huge tables), and inefficiency with LIMIT queries since the entire index is scanned even when results are found early.
Key Statistics & Figures
Query time without streaming indices
10.173 seconds
LIMIT 1 query on billion-row table with bloom filter index, single-threaded
Query time with streaming indices
2.471 seconds
Same LIMIT 1 query with use_skip_indexes_on_data_read = 1
Speedup factor
Over 4x faster
Streaming vs non-streaming secondary index evaluation
Peak memory without streaming indices
8.90 MiB
LIMIT 1 query with upfront index scanning
Peak memory with streaming indices
4.48 MiB
Same query with streaming index evaluation
Bloom filter index size
2.21 GiB
bloom_filter(0.0001
Table row count
1 billion rows
Demo table used for benchmark
Default granule size
8,192 rows
Typical granule size in ClickHouse (demo used 1,024
Internal test index size
Over 6 TB compressed
Individual secondary indices on ClickHouse's internal tables with trillions of log records
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Core database system implementing streaming secondary indices in version 25.9
Data Structure
Bloom Filter
Secondary index type used in the demo to filter granules for string column lookups
Storage Engine
Mergetree
ClickHouse table engine used in the demo for the benchmark table
Cloud Infrastructure
AWS EC2
m6i.8xlarge instance (32 vCPUs, 128 GiB RAM) used for running the benchmark
Query Language
SQL
Used for all demo queries, table creation, and data insertion
Key Actionable Insights
1Enable streaming secondary indices for LIMIT queries on large tables by setting use_skip_indexes_on_data_read = 1. This eliminates the need to fully scan secondary indices upfront, allowing ClickHouse to stop immediately once the LIMIT condition is satisfied, dramatically reducing both query time and memory usage.This is most impactful for queries with highly selective WHERE filters over huge tables, particularly when the secondary index itself is large (the demo showed a 2.21 GiB bloom filter index on a billion-row table).
2Audit your existing LIMIT queries on large tables with secondary indices for potential performance improvements. Queries that previously suffered from long startup delays due to upfront index scanning will see the most dramatic speedups when streaming indices are enabled, as demonstrated by the 4x improvement in the article's benchmark.The improvement comes from two mechanisms: concurrent index scanning and data processing, plus early termination when LIMIT conditions are met.
3Consider the size of your secondary indices relative to your data when evaluating the impact of streaming indices. In scenarios where secondary indices alone exceed terabytes (as mentioned in ClickHouse's internal test runs with trillions of log records and 6 TB+ compressed indices), the startup delay from upfront scanning can be severe.Even at smaller scales, the benefit is measurable — the demo showed a 2.21 GiB bloom filter index causing a ~10 second delay that was reduced to ~2.4 seconds with streaming.
4When benchmarking streaming secondary indices, isolate the effect by setting max_threads = 1 and disabling the query condition cache (use_query_condition_cache = 0). Clear the OS page cache between runs to ensure fair comparisons. In production, multi-threaded execution will provide additional benefits beyond what single-threaded benchmarks show.The article notes that while the demo uses single-threaded execution for clarity, in reality multiple threads process many granules concurrently, so production improvements may vary.
5Use bloom_filter indices with fine granularity (granularity 1) for point lookups on string columns in large tables. Combined with streaming secondary indices, this allows ClickHouse to efficiently skip non-matching granules incrementally without paying the cost of scanning the entire index upfront.The article's demo used bloom_filter(0.0001) with granularity 1 and index_granularity = 1024, creating a 2.21 GiB index on a billion-row table that performed well with streaming evaluation.
Common Pitfalls
1
Assuming that adding more secondary indices always improves query performance without considering the cost of index scanning. On huge tables, the index itself can become so large (e.g., 6 TB+ compressed) that scanning it upfront costs more time than scanning and processing the selected data, especially for highly selective WHERE filters.
Streaming secondary indices mitigate this by making index evaluation incremental, but understanding this trade-off is important for index design decisions.
2
Not accounting for the inefficiency of LIMIT queries with traditional upfront index evaluation. Even if a query only needs 1 row (LIMIT 1), ClickHouse with sequential index scanning still processes the entire secondary index before returning results, leading to unnecessary delays of seconds or more.
Enable streaming indices (use_skip_indexes_on_data_read = 1) to allow early termination as soon as LIMIT conditions are met.
3
Benchmarking streaming indices without properly isolating variables can lead to misleading results. Factors like OS page caching, multi-threaded execution, and the query condition cache can all mask or amplify the true impact of streaming versus sequential index evaluation.
For accurate benchmarks, clear the OS page cache between runs, set max_threads = 1, and disable use_query_condition_cache as demonstrated in the article.
Related Concepts
Clickhouse Granules And Sparse Primary Indexes
Bloom Filter Data Structures
Query Parallelism In Clickhouse
Mergetree Engine Architecture
Data Skipping Indices
Query Condition Cache
Primary Index Analysis
Limit Query Optimization
Incremental And Demand-driven Evaluation
Concurrent Index Scanning And Data Processing