
> **TL;DR**<br/><br/>ClickHouse treats Top-N as a first-class query pattern. By using min/max metadata from data-skipping indexes, it can skip entire granules before reading any data. In our examples this yields 5–10× faster Top-N queries and 10–100× less data read, especially effective on large tables and cold cache.

**Prefer a quick walkthrough?**<br/>
Watch Mark explain how ClickHouse optimizes Top-N queries:

<iframe width="768" height="432" src="https://www.youtube.com/embed/SyayPSrsAhI?si=6xrsWPFtFlOaz5q7" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

<br/>

## Optimizing common query patterns

ClickHouse’s performance [leadership](https://benchmark.clickhouse.com/) comes from systematically identifying **common analytical query patterns** and aggressively optimizing **each of them** at the engine level.

Top-N queries are one of the most important of these patterns.

They appear frequently in real analytical workloads, dashboards, monitoring queries, ranking reports, and exploratory analysis. 

“Show me the latest events.”

“Who are the top customers by revenue?”

“What are the most expensive orders today?”

<pre>
<code type='click-ui' language='sql' show_line_numbers='false'>
SELECT *
FROM orders
ORDER BY total_amount DESC
LIMIT 3;
</code>
</pre>
Over time, the engine has accumulated a large number of low-level optimizations specifically designed to make Top-N queries fast: streaming execution, read-in-order, lazy reading, and more.

This blog focuses on one additional optimization in that toolbox: a new way to **use data skipping indexes to eliminate work before any data is read**, further accelerating Top-N queries as data sizes grow.


## How ClickHouse already optimizes Top-N queries

ClickHouse already applies several optimizations to run Top-N queries efficiently and fast:

1. [Streaming](https://clickhouse.com/docs/sql-reference/statements/select#implementation-details) Top-N (bounded memory):   
   ClickHouse doesn’t sort all rows at once. Instead, it streams data and keeps only the current Top-N candidates, so memory usage stays proportional to N rather than to the table size.

2. [Read in order](https://clickhouse.com/blog/clickhouse-faster-queries-with-projections-and-primary-indexes#utilize-indexes-for-preventing-resorting-and-enabling-short-circuiting) (avoid sorting completely):  
   If the data on disk is already ordered by the ORDER BY column(s), or can be read in that order via a [projection](https://clickhouse.com/docs/sql-reference/statements/alter/projection), ClickHouse can avoid sorting and simply read the first rows in order.

3. [Lazy reading](https://clickhouse.com/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization) (defer reading non-order columns):  
   Even when the query selects many columns, ClickHouse can first determine the Top-N rows using only the columns needed for ordering, and only then read the remaining columns for those rows. This reduces I/O dramatically.

These optimizations are **orthogonal** to each other: they address different aspects of Top-N query execution and can be applied independently or in combination within a single query.

Despite these optimizations, Top-N queries still require scanning all relevant [granules](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes#data-is-organized-into-granules-for-parallel-data-processing), even when only a handful of rows are ultimately returned.

## The missing piece: skipping data before it is ever read

ClickHouse applies additional, orthogonal optimizations for Top-N queries by using [data skipping indexes](https://clickhouse.com/docs/optimize/skipping-indexes/examples) for static or dynamic Top-N filtering, **significantly reducing the number of rows that need to be processed**.

The rest of this post shows how this works in practice, starting with the simplest case.


## Static Top-N filtering (no predicates) 

Let’s start with the simplest case: a Top-N query without any predicates or filters.

<pre><code type='click-ui' language='sql'>
SELECT * FROM T ORDER BY c ASC LIMIT 3;
</code></pre>

Even without a WHERE clause, ClickHouse can reduce the amount of data read if a [minmax data skipping index](https://clickhouse.com/docs/optimize/skipping-indexes/examples) exists on the ORDER BY column `c`. 

To illustrate this, assume table T consists of five [granules](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes#data-is-organized-into-granules-for-parallel-data-processing) (the smallest processing units in ClickHouse, each covering 8,192 rows by default). For each granule, the min and max values of column `c` are stored in a minmax data skipping index, as shown below:

| Granule | min(c) | max(c) |
|---------|--------|--------|
| 1       | 40     | 90     |
| 2       | 10     | 30     |
| 3       | 5      | 25     |
| 4       | 60     | 110    |
| 5       | 1      | 15     |

This is what ClickHouse does for `ORDER BY c LIMIT 3`:

* Look only at min(c) values from the minmax index.  
  Smallest mins are:  
  * Granule 5 → min = 1  
  * Granule 3 → min = 5  
  * Granule 2 → min = 10

* Read only granules 5, 3, and 2 from disk  
* Merge their rows → return top 3 values

All remaining granules are skipped entirely.

*(Note that in practice, more than three granules may be read because data is processed in blocks, which can span multiple adjacent granules, and [multiple threads process data in parallel](https://clickhouse.com/docs/optimize/query-parallelism#distributing-work-across-processing-lanes).)*

### Benchmark: static Top-N

To illustrate the effect, we use the [anonymized web analytics example data](https://clickhouse.com/docs/getting-started/example-datasets/metrica). We [created the table and loaded the data](https://pastila.nl/?000d9e4d/048b6bdf283322bef6dc9bba561215c6#VWIpCGN9UO1RrWi2ilL5eA==GCM) on an AWS m6i.8xlarge EC2 instance (32 cores, 128 GB RAM) with a gp3 EBS volume (16k IOPS, 1000 MiB/s max throughput).

This is our example Top-N query without any predicates:

<pre><code type='click-ui' language='sql'>
SELECT URL, EventTime 
FROM hits 
ORDER BY EventTime
LIMIT 10;
</code></pre>

On 25.12, *without* the new data-skipping-index-based optimization, the fastest of three runs finished in 0.044 seconds:

```shell
10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.27 billion rows/s., 27.26 GB/s.)
Peak memory usage: 2.23 MiB.

10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.29 billion rows/s., 27.56 GB/s.)
Peak memory usage: 2.25 MiB.

10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.27 billion rows/s., 27.33 GB/s.)
Peak memory usage: 2.24 MiB.

```

Note that all ~100 million rows of the hits table got processed.

Now we run the same query *with* the new data skipping-indexes-based optimization by enabling the new [use_skip_indexes_for_top_k](https://clickhouse.com/docs/operations/settings/settings#use_skip_indexes_for_top_k) setting. Note that the table has a [minmax data skipping index on the `EventTime` column](https://pastila.nl/?000d9e4d/048b6bdf283322bef6dc9bba561215c6#VWIpCGN9UO1RrWi2ilL5eA==GCM).

<pre><code type='click-ui' language='sql'>
SELECT URL, EventTime 
FROM hits 
ORDER BY EventTime
LIMIT 10
SETTINGS use_skip_indexes_for_top_k = 1;
</code></pre>

Now the fastest of three runs finished in 0.009 seconds:

```shell
10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (17.48 million rows/s., 528.35 MB/s.)
Peak memory usage: 917.51 KiB.

10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (18.03 million rows/s., 544.98 MB/s.)
Peak memory usage: 198.47 KiB.

10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (18.19 million rows/s., 549.68 MB/s.)
Peak memory usage: 198.47 KiB.
```

This is roughly **5 times faster than before**. Instead of processing the table’s full **~100 million rows**, ClickHouse processed only about **163 thousand rows**, which reduced the amount of data read from roughly **1.2 GB** to just **4.95 MB**.

This I/O benefit will grow with table size: when tables run into billions or trillions of rows, and especially when the cache is cold, avoiding unnecessary reads at the granule level becomes increasingly impactful.

> Importantly, this reduction happens **before** any rows are read, purely based on granule-level metadata.

## Dynamic Top-N threshold filtering (with predicates)

So far, we’ve looked at Top-N queries without predicates, where granules can be preselected using static min/max metadata on the ORDER BY column.

When a Top-N query also includes predicates, the data skipping becomes *dynamic*.

As the query executes, ClickHouse continuously maintains the current Top-N result set. **The current N-th value effectively acts as a dynamic threshold.**

This mechanism builds on [streaming for secondary indices](https://clickhouse.com/blog/clickhouse-release-25-09#streaming-for-secondary-indices), introduced in ClickHouse 25.9: Instead of evaluating data skipping indexes upfront, ClickHouse interleaves data skipping index checks with data reads. As soon as a granule becomes eligible for reading (after [primary index analysis](https://clickhouse.com/docs/primary-indexes) for the predicate evaluation), its corresponding minmax data skipping index entry is consulted.

At that point, the current Top-N threshold is compared against the granule’s min/max metadata. If the granule cannot possibly contain values that would improve the Top-N result set, it is skipped immediately and never read.

As execution progresses and better Top-N candidates are found, the threshold tightens further, allowing ClickHouse to prune an increasing number of granules dynamically during query execution. Because the query stops once the Top-N result is complete, tighter thresholds allow ClickHouse to stop reading and skipping granules earlier.

### Benchmark: dynamic Top-N

We demonstrate the effectiveness of these mechanics with another example Top-N query, this time with a predicate:

<pre><code type='click-ui' language='sql'>
SELECT URL,EventTime 
FROM hits 
WHERE URL LIKE '%google%'
ORDER BY EventTime
LIMIT 10;
</code></pre>

On 25.12, *without* the new data-skipping-index-based dynamic top-N threshold filtering, the fastest of three runs finished in 0.325 seconds. We disabled the [query condition cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache) for all runs to get the raw data processing behavior each time.

```shell
10 rows in set. Elapsed: 0.333 sec. Processed 100.00 million rows, 9.42 GB (299.96 million rows/s., 28.26 GB/s.)
Peak memory usage: 143.92 MiB.

10 rows in set. Elapsed: 0.325 sec. Processed 100.00 million rows, 9.42 GB (307.37 million rows/s., 28.95 GB/s.)
Peak memory usage: 138.46 MiB.

10 rows in set. Elapsed: 0.334 sec. Processed 100.00 million rows, 9.42 GB (299.55 million rows/s., 28.22 GB/s.)
Peak memory usage: 147.47 MiB.
```

Note that all 100 million rows of the hits table got processed.

Now we run the same query *with* the new data-skipping-index-based dynamic top-N threshold filtering by enabling [streaming for secondary indices](https://clickhouse.com/blog/clickhouse-release-25-09#streaming-for-secondary-indices) and by enabling the new [use_top_k_dynamic_filtering](https://clickhouse.com/docs/operations/settings/settings#use_top_k_dynamic_filtering) setting. Note that the table has a [minmax data skipping index on the `EventTime` column](https://pastila.nl/?000d9e4d/048b6bdf283322bef6dc9bba561215c6#VWIpCGN9UO1RrWi2ilL5eA==GCM) and we disabled the [query condition cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache) for all runs to fully isolate the dynamic top-N threshold filtering.  

<pre><code type='click-ui' language='sql'>
SELECT URL,EventTime 
FROM hits 
WHERE URL LIKE '%google%'
ORDER BY EventTime
LIMIT 10
SETTINGS
  use_query_condition_cache = 0,
  use_skip_indexes_on_data_read = 1,
  use_skip_indexes_for_top_k = 1,
  use_top_k_dynamic_filtering = 1;
</code></pre>

Now the fastest of three runs finished in 0.033 seconds:

```shell
10 rows in set. Elapsed: 0.034 sec. Processed 7.66 million rows, 515.98 MB (227.36 million rows/s., 15.32 GB/s.)
Peak memory usage: 51.30 MiB.

10 rows in set. Elapsed: 0.034 sec. Processed 7.59 million rows, 509.67 MB (224.45 million rows/s., 15.08 GB/s.)
Peak memory usage: 51.29 MiB.

10 rows in set. Elapsed: 0.033 sec. Processed 7.67 million rows, 520.58 MB (234.95 million rows/s., 15.96 GB/s.)
Peak memory usage: 47.28 MiB.
```

This is roughly **10× faster than before**. Instead of processing the table’s full **~100 million rows**, ClickHouse processed only about **7 million rows**, which reduced the amount of data read from roughly **9.42 GB** to about **520.58 MB**.

As in the previous example, this I/O benefit grows with table size: when tables run into billions or trillions of rows (and especially when the cache is cold), dynamically skipping granules that cannot improve the current Top-N result becomes increasingly impactful.

As explained above, ClickHouse achieves this by continuously maintaining the current Top-N threshold *during* query execution and using the minmax data skipping index to dynamically skip granules whose values cannot improve the Top-10 result.

## Production-scale validation

These mechanics have also been validated on very large production tables.

In [early testing](https://github.com/ClickHouse/ClickHouse/pull/89835#issuecomment-3566807610) on a table with **50 billion rows**, Top-N queries using skip index filtering completed in under **0.2 seconds**, confirming that granule-level pruning remains effective even at extreme scale. Further improvements are expected to make this even faster.


## Turning Top-N into a metadata problem

With data skipping–based Top-N filtering, ClickHouse turns Top-N queries into a **metadata-driven pruning problem**.

By comparing the current Top-N threshold against granule-level min/max metadata, ClickHouse can skip large portions of a table without reading a single row. For simple Top-N queries, this pruning happens up front. For queries with predicates, it happens dynamically as execution progresses and the threshold tightens.

This kind of metadata-driven pruning is **especially powerful in modern architectures with object storage or disaggregated compute**, where avoiding unnecessary reads saves not just CPU, but also network I/O and latency.

In our examples, this approach sped up Top-N queries by **5× to 10×** and reduced the amount of data read by **one to two orders of magnitude**, purely by skipping granules that could not improve the result.

Crucially, this optimization composes with existing Top-N techniques like streaming execution, read-in-order, and lazy reading. Each optimization addresses a different bottleneck, and together they allow ClickHouse to scale Top-N queries from millions to billions of rows efficiently.

For users, this means a familiar query pattern `ORDER BY … LIMIT N` now benefits from increasingly aggressive pruning as data grows. By treating common query patterns as problems that can often be solved *before* touching the data at all.



A deep dive into how ClickHouse uses data-skipping metadata to turn Top-N queries into a pruning problem, cutting I/O and speeding up queries at scale.

large_Release Highlight  Template 1200 x 630.png

medium_Release Highlight  Template 1200 x 630.png

small_Release Highlight  Template 1200 x 630.png

thumbnail_Release Highlight  Template 1200 x 630.png

Release Highlight  Template 1200 x 630.png

How ClickHouse makes Top-N queries faster with granule-level data skipping


> **TL;DR**<br/><br/>ClickHouse tables once had only *one* primary index.<br/>Now they can have **many**, implemented as lightweight projections that behave like primary indexes, without duplicating data.

**Prefer a quick walkthrough?**<br/>
Watch Mark explain how projections act as secondary indexes in ClickHouse:

<iframe width="768" height="432" src="https://www.youtube.com/embed/Fe6DqnWBs1I?si=DSwxb9BlvSwHjibA" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

<br/>

## Why projections matter

[Primary indexes](https://clickhouse.com/docs/primary-indexes) are the most important mechanism ClickHouse uses to speed up filtered queries. By storing rows on disk in the order of the table’s sorting key, the engine maintains a sparse index that can quickly locate the relevant ranges of data. But because this index depends on the physical sort order of the table, **each table can have only one primary index**.

To accelerate queries whose filters do not align with that single index, ClickHouse offers [**projections**](https://clickhouse.com/docs/sql-reference/statements/alter/projection) - automatically maintained, hidden table copies stored in a different sort order, and therefore with a different primary index. These alternative layouts can speed up queries that benefit from those orderings. The downside, historically, was storage cost: **projections duplicated the base table’s data on disk**.

## Lightweight projections as secondary indexes

Since release **25.6**, however, ClickHouse can create much more lightweight [projections that behave like a **secondary index**](https://clickhouse.com/blog/clickhouse-release-25-06#filtering-by-multiple-projections) without duplicating full rows. Instead of storing complete data copies, they store only their sorting key plus a [_part_offset](https://clickhouse.com/docs/data-modeling/projections#smarter_storage_with_part_offset) pointer back into the base table, greatly reducing storage overhead.

When applicable, [ClickHouse uses such a projection’s primary index like a secondary index to locate matching rows](https://clickhouse.com/blog/clickhouse-release-25-06#smarter-storage-with-_part_offset), while still reading the actual row data from the base table. Multiple lightweight projections can work together, so a query with several filters can take advantage of every applicable projection, and if one of the filters also matches the base table’s primary index, **that index participates as well**.


## From part-level to granule-level pruning

Until now, this mechanism could only prune entire parts; **granule-level pruning** was not supported.

With this release, _part_offset-based projections now behave like true secondary indexes with **granule-level pruning**, enabling much finer filtering and dramatically faster queries.


## Example: combining multiple projection indexes

To demonstrate this, we’ll again use [the UK price paid dataset](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid), this time defining the table with two lightweight _part_offset-based projections: by_time and by_town:

<pre><code type='click-ui' language='sql'>
CREATE OR REPLACE TABLE uk.uk_price_paid_with_proj
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8(
      'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String),
    PROJECTION by_time (
        SELECT _part_offset ORDER BY date
    ),
    PROJECTION by_town (
        SELECT _part_offset ORDER BY town
    )
)
ENGINE = MergeTree
ORDER BY (postcode1, postcode2, addr1, addr2);
</code></pre>

Then we load the data using the instructions [here](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid).

The diagram below sketches the base table and its two lightweight _part_offset-based projections:

![image6.png](/uploads/image6_c12f1d6103.png)

① The base table is sorted by (postcode1, postcode2, addr1, addr2). This defines its primary index, making queries that filter by those columns fast and efficient.

② The by_time and ③ by_town projections store only their sorting key plus _part_offset, pointing back into the base table and greatly reducing data duplication. Their primary indexes act as secondary indexes for the base table, speeding up queries that filter on date and/or town.

*We benchmarked this on an AWS m6i.8xlarge EC2 instance (32 cores, 128 GB RAM) with a gp3 EBS volume (16k IOPS, 1000 MiB/s max throughput).*

We will run a query filtering on the date and town columns. Note that these columns are not part of the base table’s primary key. 

First, we run the query with projection support disabled to get a baseline performance. Note that we disabled the [query condition cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache) and [PREWHERE](https://clickhouse.com/docs/optimize/prewhere) to fully isolate index-based data pruning:

<pre><code type='click-ui' language='sql'>
SELECT *
FROM uk.uk_price_paid_with_proj
WHERE (date = '2008-09-26') AND (town = 'BARNARD CASTLE')
FORMAT Null
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 0;
</code></pre>

The fastest of three runs finished in **0.077 seconds**:

```shell 
0 rows in set. Elapsed: 0.084 sec. Processed 30.73 million rows, 1.29 GB (363.92 million rows/s., 15.26 GB/s.)
Peak memory usage: 129.07 MiB.

0 rows in set. Elapsed: 0.076 sec. Processed 30.73 million rows, 1.29 GB (406.96 million rows/s., 17.07 GB/s.)
Peak memory usage: 129.29 MiB.

0 rows in set. Elapsed: 0.077 sec. Processed 30.73 million rows, 1.29 GB (398.51 million rows/s., 16.71 GB/s.)
Peak memory usage: 129.27 MiB.
```

Note that it was a full table scan, reading the whole table (~30 million rows)

Now we run the query with enabled projection support:

<pre><code type='click-ui' language='sql'>
SELECT *
FROM uk.uk_price_paid_with_proj
WHERE (date = '2008-09-26') AND (town = 'BARNARD CASTLE')
FORMAT Null
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 1; -- default value
</code></pre>

The fastest of three runs finished in **0.010 seconds**:

```shell 
0 rows in set. Elapsed: 0.010 sec. Processed 16.38 thousand rows, 644.86 KB (1.60 million rows/s., 63.06 MB/s.)
Peak memory usage: 4.89 MiB.

0 rows in set. Elapsed: 0.010 sec. Processed 16.38 thousand rows, 644.86 KB (1.69 million rows/s., 66.36 MB/s.)
Peak memory usage: 4.88 MiB.

0 rows in set. Elapsed: 0.011 sec. Processed 16.38 thousand rows, 644.86 KB (1.54 million rows/s., 60.57 MB/s.)
Peak memory usage: 4.89 MiB.
```

The result: **0.077 s vs. 0.010 s — roughly a 90% speedup.**

Also note that this time only ~16k rows instead of all ~30 million rows got scanned.

Via EXPLAIN we can see that ClickHouse is using the primary indexes of *both* projections as secondary indexes to prune base table granules:

<pre><code type='click-ui' language='sql'>
EXPLAIN projections = 1
SELECT *
FROM uk.uk_price_paid_with_proj
WHERE (date = '2008-09-26') AND (town = 'BARNARD CASTLE')
SETTINGS
    use_query_condition_cache = 0,
    optimize_move_to_prewhere = 0,
    optimize_use_projections= 1; -- default value
</code></pre>   

```shell
    ┌─explain────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                          │
 2. │   Filter ((WHERE + Change column names to column identifiers))     │
 3. │     ReadFromMergeTree (uk.uk_price_paid_with_proj)                 │
 4. │     Projections:                                                   │
 5. │       Name: by_time                                                │
 6. │         Description: Projection has been analyzed...               │
 7. │         Condition: (date in [14148, 14148])                        │
 8. │         Search Algorithm: binary search                            │
 9. │         Parts: 5                                                   │
10. │         Marks: 7                                                   │
11. │         Ranges: 5                                                  │
12. │         Rows: 57344                                                │
13. │         Filtered Parts: 0                                          │
14. │       Name: by_town                                                │
15. │         Description: Projection has been analyzed...               │
16. │         Condition: (town in ['BARNARD CASTLE', 'BARNARD CASTLE'])  │
17. │         Search Algorithm: binary search                            │
18. │         Parts: 5                                                   │
19. │         Marks: 5                                                   │
20. │         Ranges: 5                                                  │
21. │         Rows: 40960                                                │
22. │         Filtered Parts: 0                                          │
    └────────────────────────────────────────────────────────────────────┘                     
```


Row 10 of the EXPLAIN output shows that the **by_time** projection (specifically its primary index) first narrows the search to **7 granules** (“Marks”). Since each granule contains 8,192 rows, this corresponds to **7 × 8192 = 57,344 rows** to scan (as shown in row 12). Those 7 granules lie across **5 data parts** (row 9), so the engine would need to read **5 corresponding data ranges (row 11)**.

Then, starting at row 14, the **by_town** projection’s primary index is applied. It filters out 2 of the 7 granules previously selected by the by_time projection. The final result: the engine needs to scan **5 granules**, located in **5 data ranges** across **5 parts** of the base table, because those granules may contain rows matching the query’s time and town predicate.

Two new settings are introduced to control this optimization:

* max_projection_rows_to_use_projection_index: If the estimated number of rows to read from the projection is <= this value, projection index can be applied.

* min_table_rows_to_use_projection_index: If the estimated number of rows to read from the table is >= this value, projection index will be considered.

## Key takeaway

ClickHouse tables once had only *one* primary index.

Now they can have **many**, each behaving like a primary index, and ClickHouse will use *all of them* when your query has multiple filters.

ClickHouse now supports granule-level pruning for lightweight projections, enabling them to act as true secondary indexes that work together to dramatically speed up multi-filter queries without duplicating full table data.

large_3.png

medium_3.png

small_3.png

thumbnail_3.png

3.png

ClickHouse projections now behave like true secondary indexes


> **TL;DR**<br/><br/>ClickHouse used to fully scan secondary indexes before query execution.<br/>Streaming secondary indices make index evaluation incremental and demand-driven, eliminating unnecessary upfront work and improving latency and memory usage.

## Before: Sequential index scans

Before ClickHouse 25.9, **secondary indices** (e.g., minmax, set, bloom filter, vector, text) were evaluated *before* reading the underlying table data. 

The index entries are scanned *upfront* to decide which [granules](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes#data-is-organized-into-granules-for-parallel-data-processing) (the smallest processing units in ClickHouse, typically covering 8,192 rows each) may contain matching rows for a query’s WHERE filter.

The following animation illustrates this process:

![Blog-release-25.9-animation-01.gif](/uploads/Blog_release_25_9_animation_01_5495b32bde.gif)

① **Index scan and granule selection** - ClickHouse checks each index entry to determine which granules to read. Only matching granules are chosen; the others are skipped.

② **Query execution** - The selected granules are [streamed](https://clickhouse.com/docs/optimize/query-parallelism) into the query engine and processed into the final result.

This upfront index evaluation has several drawbacks:

* **Startup delay:** Index analysis happens before the actual query execution begins.

* **Heavy index scans:** In some cases (e.g., queries with highly selective WHERE filters over huge tables), scanning the index can cost more than scanning and processing the selected data itself.

* **Inefficient with LIMIT:** Even if a query stops early due to a LIMIT, ClickHouse still has to scan the entire index upfront (and may select more data than necessary).

## How streaming secondary indices work

ClickHouse 25.9 eliminates these drawbacks by interleaving data reads with index checks, as shown in the animation below:

![Blog-release-25.9-animation-02.gif](/uploads/Blog_release_25_9_animation_02_1e370aca28.gif)

① **Index scan, granule selection, and** ② **query execution (concurrent)** – When ClickHouse is about to read a table data granule (e.g. because it was selected by the [primary index analysis](https://clickhouse.com/docs/primary-indexes)), it first checks the corresponding secondary index entry (if one exists). If the secondary index indicates the table granule can be skipped, it’s never read. Otherwise, the granule is read and processed by the query engine, while scanning continues on subsequent granules.

This incremental, two-stream process—reading table granules alongside checking their secondary index entries—is why we call the feature *streaming secondary indices* (controlled by the setting [use_skip_indexes_on_data_read](https://clickhouse.com/docs/operations/settings/settings#use_skip_indexes_on_data_read)) .

*(Note: For simplicity, the animation shows a single-threaded query engine, but in reality multiple threads process many granules concurrently.)*

**This concurrent execution eliminates the startup delay and avoids wasted work.** For example, with queries that stop early due to a LIMIT, ClickHouse halts index checks and granule reads as soon as the result is complete.


## Demo: Faster results with streaming secondary indices

In the release webinar, Alexey [demonstrated](https://www.youtube.com/live/LCHEPNXo8kQ?si=uyYeTXZpDioXHbHN&t=1081) *streaming secondary indices* on massive ClickHouse tables containing trillions of log records from test runs applied to pull requests and commits. On these datasets, some individual secondary indices alone exceed **6 TB compressed**.

Since reproducing that scale isn’t practical, we’ll use a simplified artificial example that you can run yourself.

We ran the experiment on an AWS EC2 m6i.8xlarge instance (32 vCPUs, 128 GiB RAM). 

First, we created a table with a String column and added a Bloom filter index on that column:

<pre><code type='click-ui' language='sql'>
CREATE TABLE test (
  s String, 
  index s_idx s type bloom_filter(0.0001) granularity 1
)
ENGINE = MergeTree
ORDER BY () 
SETTINGS index_granularity = 1024;
</code></pre>

Next, we inserted one billion rows:

<pre><code type='click-ui' language='sql'>
INSERT INTO test 
SELECT if(number % 1024 == 0, 'needle', randomPrintableASCII(64)) 
FROM numbers_mt(1_000_000_000);
</code></pre>

We can check that the bloom filter index has a size of over 2 GiB now:

<pre><code type='click-ui' language='sql'>
SELECT 
  name, 
  type_full, 
  formatReadableSize(data_uncompressed_bytes) AS size
FROM system.data_skipping_indices
WHERE database = 'default' AND table = 'test';
</code></pre>

```text
   ┌─name──┬─type_full────────────┬─size─────┐
1. │ s_idx │ bloom_filter(0.0001) │ 2.21 GiB │
   └───────┴──────────────────────┴──────────┘
```

To make the comparison fair, we cleared the OS page cache before each of the two test query runs below.

<pre><code type='click-ui' language='bash'>
echo 3 | sudo tee /proc/sys/vm/drop_caches >/dev/null
</code></pre>

**Without streaming indices** (use_skip_indexes_on_data_read = 0), finding a single row with LIMIT 1 took ~10 seconds. 

*Note that we set max_threads = 1 and disabled the [query condition cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache) to isolate and highlight the effect of secondary index processing.*

<pre><code type='click-ui' language='sql'>
SELECT * FROM test WHERE s = 'needle' LIMIT 1 
SETTINGS 
  max_threads = 1, 
  use_query_condition_cache = 0,  use_skip_indexes_on_data_read = 0;
</code></pre>

```text
   ┌─s──────┐
1. │ needle │
   └────────┘

1 row in set. Elapsed: 10.173 sec. Processed 29.70 thousand rows, 2.14 MB (2.92 thousand rows/s., 210.00 KB/s.)
Peak memory usage: 8.90 MiB.
```

**With streaming indices** (use_skip_indexes_on_data_read = 1), the same query returned in ~2.4 seconds — over 4× faster with less memory used.

<pre><code type='click-ui' language='sql'>
SELECT * FROM test WHERE s = 'needle' LIMIT 1 
SETTINGS 
  max_threads = 1, 
  use_query_condition_cache = 0,  use_skip_indexes_on_data_read = 1;
</code></pre>

```text
   ┌─s──────┐
1. │ needle │
   └────────┘

1 row in set. Elapsed: 2.471 sec. Processed 29.70 thousand rows, 2.14 MB (12.02 thousand rows/s., 864.57 KB/s.)
Peak memory usage: 4.48 MiB.
```

## Why this is faster in practice

As a reminder, the observed speedup comes from two (① and ②) mechanisms:

* **Without streaming indices**: Before query processing even begins, ClickHouse must fully scan and process the index to identify all matching granules.

* **With streaming indices**:   
  ① ClickHouse concurrently scans the index and processes matching granules in the query engine.   
  ② And as soon as the first matching row (LIMIT 1) is found, it immediately stops scanning further index entries and granules, eliminating wasted work.

## Key takeaway

By checking indexes and reading data concurrently instead of sequentially, ClickHouse eliminates startup delays and stops immediately when LIMIT conditions are met, reducing both query time and memory usage.
This is especially useful for early-exit queries on large tables.

ClickHouse now streams secondary index evaluation alongside data reads instead of scanning indexes upfront, making index usage incremental and demand-driven, reducing startup latency, unnecessary work, and memory usage.

large_2.png

medium_2.png

small_2.png

thumbnail_2.png

2.png

Streaming secondary indices: incremental, demand-driven index evaluation

## Why benchmarks keep showing up in my work

As the year winds down, I like to zoom out and look at the work I’m proudest of.

I wrote (and helped ship) a lot of ClickHouse content this year, but a bunch of the posts I care about most share the same backbone: **each one has a benchmark behind it**.

![Blog-benchmarks-of-2025.gif](https://cms.clickhouse-dev.com:1337/uploads/Blog_benchmarks_of_2025_a8bcb6547e.gif)

Benchmarks show up again and again in my work because they force clarity. They correct intuition, surface trade-offs, and sometimes overturn narratives that have stuck around for too long.

Here are a few of my favorites from this year, the ones that surprised me, changed how people think about ClickHouse, or directly impacted how real systems were built. Taken together, they form a loose narrative, from data layout and ingestion, through storage and execution, and finally to cost.

And reading back through them, I kept noticing the same thing: performance comes down to how efficiently an engine moves data end-to-end.

## The billion-docs JSON benchmark

This benchmark started with a foundational question: *what does JSON look like when you treat it as columnar data instead of a document blob?*

ClickHouse completely reimagined JSON storage on top of column-oriented storage, resulting in unmatched speed, compression, and user experience, [far beyond any existing JSON data store](https://clickhouse.com/blog/json-bench-clickhouse-vs-mongodb-elasticsearch-duckdb-postgresql). 

To prove the speed, we created [JSONBench](https://jsonbench.com/): a reproducible benchmark that loads the same real-world dataset (up to 1B Bluesky events) into multiple systems, then measures storage size, cold/hot query runtimes, and data quality (how many documents were successfully ingested).  

In the original billion-docs run, ClickHouse was orders of magnitude faster while also being smaller on disk (including 20% more compact than storing the same JSON as compressed files on disk).

![JSON_Benchmarks_018_4572dccd94.png](https://cms.clickhouse-dev.com:1337/uploads/JSON_Benchmarks_018_4572dccd94_723f9e64db.png)

![JSON_Benchmarks_017_1e36137288.png](https://cms.clickhouse-dev.com:1337/uploads/JSON_Benchmarks_017_1e36137288_d973fb27eb.png)

We also built a [live online dashboard](https://jsonbench.com/) where you can slice results by dataset size, cold vs. hot, storage, and data quality, plus a toggle for systems that flatten JSON. 

![Blog-benchmarks-of-2025.002.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_benchmarks_of_2025_002_b6a31eb399.png)

JSONBench didn’t stop at a blog post. It’s now actively used in the community and regularly receives external pull requests, adding new or updating results for exiting systems.

> **This is one of my favorite benchmarks this year because the results genuinely surprised me.**

ClickHouse was thousands of times faster than traditional JSON data stores like MongoDB, while also using significantly less storage. It completely changed my intuition for what’s possible with analytical queries on JSON.


![JSON.gif](https://cms.clickhouse-dev.com:1337/uploads/JSON_af5ee459cb.gif)

**Side story**: ClickHouse already started from a strong lead in the original JSONBench results. We continued improving the JSON implementation independently, and later shipped [new shared-data serializations](https://clickhouse.com/blog/json-data-type-gets-even-better) that dramatically improved performance and memory usage for complex JSON workloads. As a result, ClickHouse now leads even further across both cold and hot runs at all dataset sizes, with a significantly larger margin than before.


## The input formats benchmark

This benchmark came from a deceptively simple question: *if ClickHouse supports [70+](https://sql.clickhouse.com/?query=c2VsZWN0ICogZnJvbSBzeXN0ZW0uZm9ybWF0cyBXSEVSRSBpc19pbnB1dCBPUkRFUiBCWSBuYW1lOw&chart=eyJ0eXBlIjoibGluZSIsImNvbmZpZyI6eyJ4YXhpcyI6InllYXIiLCJ5YXhpcyI6Ijk5dGhfYXZnX3RlbXAiLCJzZXJpZXMiOiJjb3VudHJ5IiwidGl0bGUiOiJUZW1wZXJhdHVyZSBieSBjb3VudHJ5IGFuZCB5ZWFyIn19&tab=results&run_query=true) input formats, which ones actually matter for high-throughput ingestion?*

To [answer that properly](https://clickhouse.com/blog/clickhouse-input-format-matchup-which-is-fastest-most-efficient), we built [FastFormats](https://fastformats.clickhouse.com/), a systematic benchmark that isolates **server-side ingestion cost** and measures **time, CPU usage, memory usage, and file size** across dozens of formats, batch sizes, compression schemes, and pre-sorting variants.  
 
The headline result was clear and surprisingly consistent:


* **Native format wins** across essentially all scenarios 

* **Compression (LZ4 first, ZSTD when bandwidth matters)** is a no-brainer 

* **Pre-sorting helps**, but much less than people expect 

* **Batch size matters more than almost anything else**

![Blog_Formats_003_93c66d9a7e.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_Formats_003_93c66d9a7e_02f027921c.png)

And, like with JSONBench, we also built a [live FastFormats dashboard](https://fastformats.clickhouse.com/) where you can slice results by format, batch size, compression, pre-sorting, and metric.

![Blog-benchmarks-of-2025.003.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_benchmarks_of_2025_003_4e01fc06f2.png)

> **This is one of my favorite benchmarks this year because it had direct production impact beyond ClickHouse itself.**

Netflix [describes](https://clickhouse.com/blog/netflix-petabyte-scale-logging) how one of their biggest ingestion wins came *directly* from reading this post and looking at the FastFormats results. After seeing that the **native ClickHouse protocol consistently outperformed RowBinary**, they reverse-engineered the Go client and implemented **native-protocol encoding with LZ4** in their Java pipeline. The result: lower CPU usage, better memory efficiency,  and a system that now ingests **~5 PB of logs per day** into ClickHouse. 


## The object storage cache benchmark

This benchmark started from a practical cloud question: why do analytics systems still feel slow even when throughput looks great on paper?

The answer turned out not to be bandwidth, but **latency**. Object storage can deliver decent throughput, but every read still costs hundreds of milliseconds, and for many analytical queries (small, selective, scattered reads), that latency dominates everything else. 

To tackle this, we [built](https://clickhouse.com/blog/building-a-distributed-cache-for-s3) and [benchmarked](https://github.com/ClickHouse/examples/tree/main/blog-examples/caches/hot-data/cloud-distributed-cache) a **distributed cache for ClickHouse Cloud**: a shared, low-latency caching layer that sits between stateless compute nodes and object storage. Instead of each node warming its own isolated cache, hot table data is cached **once** and reused instantly by all nodes. 

The benchmarks compare three setups using the same dataset and queries:



* a self-managed server with local SSDs, 

* ClickHouse Cloud with a traditional per-node filesystem cache, 

* ClickHouse Cloud with the new **distributed cache**. 


The results:

* For **throughput-bound scans**, cold queries ran up to **4× faster than a local-SSD setup**, thanks to shared caching and parallel fetch across cache nodes.

* For **latency-sensitive scattered reads**, cold queries matched SSD performance, and hot queries consistently hit **sub-60 ms memory-speed latency**—*without any local disks at all*. 

<br/>

![Blog_caches_002_4142f575eb.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_caches_002_4142f575eb_3945c50438.png)

<br/>

![Blog_caches_003_5834a2afa1.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_caches_003_5834a2afa1_94db65a5a7.png)

The key insight is that **shared caching + elastic, stateless compute beats “fast disks”**, even on cold starts. You get faster warm-ups, no repeated downloads from object storage, and the ability to scale compute up or down without throwing away hot data. 

> **This is one of my favorite benchmarks this year because it shows a real architectural shift**. 

Cloud-native analytics doesn’t have to trade elasticity for performance anymore. You can have both.


## The UPDATEs benchmark

For a long time, one sentence followed ClickHouse around: *“Great for analytics, but you can’t really do frequent fast updates.”*

This [benchmark](https://github.com/ClickHouse/examples/tree/main/blog-examples/clickhouse-fast-updates) is where that narrative finally broke.

Accompanied by a [post](https://clickhouse.com/blog/updates-in-clickhouse-3-benchmarks), we benchmarked **every UPDATE mechanism ClickHouse supports** - classic mutations, on-the-fly mutations, ReplacingMergeTree inserts, and the new [standard SQL UPDATEs, implemented as lightweight updates with patch parts](https://clickhouse.com/blog/updates-in-clickhouse-2-sql-style-updates) - side by side, on the same dataset, hardware, and queries. 

The headline result surprised a lot of people:



* **Standard SQL UPDATEs** in ClickHouse are now **up to 1,000× faster** than classic mutations. 

* Bulk updates that used to take **minutes now finish in milliseconds**. 

* Single-row updates land in **tens of milliseconds**, comparable to inserts. 

![Blog_updates_Part_3_001_9bb4cf35e5.png](https://cms.clickhouse-dev.com:1337/uploads/Blog_updates_Part_3_001_9bb4cf35e5_eadad60b03.png)

> **This is one of my favorite benchmarks this year because it changes how people think about ClickHouse**. 

You can now do **frequent, fast updates using standard SQL**, without giving up analytical performance, and without resorting to engine-specific workarounds.


## The distributed GROUP BY benchmark

This benchmark answers the question: *how far can you scale [the same GROUP BY execution model](https://clickhouse.com/blog/clickhouse-parallel-replicas#how-clickhouse-makes-group-by-fast)?*

GROUP BY sits at the core of nearly every analytical query: powering observability dashboards, fueling conversation-speed AI, agent-facing analytics, and everything in between.

ClickHouse Cloud’s [parallel replicas](https://clickhouse.com/blog/clickhouse-parallel-replicas) **can scale a single GROUP BY query across all CPU cores of all available compute nodes.**

To evaluate the performance impact,  we [benchmarked](https://github.com/ClickHouse/examples/tree/3b6da4b0d0607d4debe5783021ffd2019058637e/blog-examples/ParallelReplicasBench) raw GROUP BY queries over datasets ranging from **tens of millions to 100 billion and even 1 trillion rows**, running the *same query* while progressively scaling from one node to **hundreds of nodes** in ClickHouse Cloud. 

The result feels almost fake the first time you see it:

<img src="https://cms.clickhouse-dev.com:1337/uploads/t1_414ms_socials_3df382020f.gif" 
     alt="t1_414ms.gif" 
     style="border: 3px solid #888; border-radius: 6px;" />

* **100 billion rows aggregated in ~0.4 seconds**

* **~240 billion rows per second**, over **1.4 TB/s** of throughput 

* No pre-aggregation or data reshuffling

![Parallel_Replicas_004_2e1de3a30e.png](https://cms.clickhouse-dev.com:1337/uploads/Parallel_Replicas_004_2e1de3a30e_352e81c02d.png)

> **This is one of my favorite benchmarks this year because it shows how far technical elegance can scale.** 

Like a fountain pen or a well-designed Unix tool, [ClickHouse does one thing exceptionally well: GROUP BY](https://clickhouse.com/blog/clickhouse-parallel-replicas#why-group-by-is-the-beating-heart-of-analytics). The same execution model runs unchanged from a laptop to the cloud, and this benchmark shows the impact. You can take the operation that defines analytical speed and scale it across a fleet of machines without changing the query or the data model.


## Honourable mention: The cloud warehouse cost–performance benchmark

This benchmark zooms out from features entirely and asks a harder question: how does performance actually turn into cost in the cloud?

It gets an honourable mention because its scope is much broader than the others.

The [benchmark](https://github.com/ClickHouse/examples/tree/main/blog-examples/Bench2Cost) started from a simple observation: headline prices are a terrible proxy for real-world cost. Different systems bill in different units, mapped to very different compute models, which makes price lists hard to compare, and easy to misinterpret.

To make this concrete, [we compared five major cloud warehouses](https://clickhouse.com/blog/cloud-data-warehouses-cost-performance-comparison) using the same analytical workload at multiple scales, then translated raw runtimes into real costs based on each system’s billing model.

<iframe width="768" height="432" src="https://www.youtube.com/embed/cEhFUefOOfA?si=lJlMZkVJPnrrOeFe" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

> **This benchmark is one of my favorites this year because it reframes the conversation.**
 
Instead of arguing about list prices or pricing tiers, it shows that efficiency is the real currency. When you measure cost the same way systems actually bill for it, ClickHouse Cloud consistently delivers the most performance per dollar.

![Blog_Costs_animation01_small_de9ac301cc.gif](https://cms.clickhouse-dev.com:1337/uploads/Blog_Costs_animation01_small_de9ac301cc_603f8a4845.gif)

## What a year of benchmarks taught me

Looking back, the common thread across all this work is the role benchmarks play in cutting through assumptions.

They corrected narratives (“ClickHouse can’t do updates”), exposed trade-offs (latency vs. throughput, speed vs. cost), and validated architectural bets (columnar JSON, stateless cloud compute). In a few cases, they even escaped the blog and changed how real systems were built.

Over the past year, these benchmarks all pointed to the same conclusion: performance is about how efficiently an engine moves and processes data *end-to-end*. In practice, that showed up repeatedly:

- Fancy formats don’t matter without batching.
- Raw bandwidth doesn’t matter without latency control.
- Isolated caches don’t matter on elastic compute.
- The storage model doesn’t matter if updates avoid rewriting large amounts of data.
- More compute nodes don’t matter without a scalable execution model.
- Low list prices don’t matter without execution efficiency.

That’s why benchmarks keep showing up in my work: they’re how these end-to-end trade-offs become visible.

Next year, we’ll ship new features, new architectures, and new claims. And we’ll keep doing the same thing we did here: measure first, publish the results, and use the numbers to guide the conversation.





A look back at some of my favorite benchmark-backed ClickHouse blog posts this year, and the lessons they revealed about performance in practice.

large_527657444-6c939522-a72b-4802-92e1-e5f3c8da60eb.jpg

medium_527657444-6c939522-a72b-4802-92e1-e5f3c8da60eb.jpg

small_527657444-6c939522-a72b-4802-92e1-e5f3c8da60eb.jpg

thumbnail_527657444-6c939522-a72b-4802-92e1-e5f3c8da60eb.jpg

527657444-6c939522-a72b-4802-92e1-e5f3c8da60eb.jpg

What really matters for performance: lessons from a year of benchmarks

We benchmarked the five major cloud data warehouses at 1B–100B rows using their real billing models to measure performance per dollar. Results show how cost-performance shifts as data grows.

large_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

medium_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

small_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

thumbnail_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

How the 5 major cloud data warehouses compare on cost-performance

ClickHouse Cloud can now query Iceberg and Delta Lake tables directly through the DataLakeCatalog engine. Connect to Glue or Unity catalogs, discover tables automatically, and query your Lakehouse data instantly, all at ClickHouse speed.

Tom Schreiber

Blog posts by Tom Schreiber

How ClickHouse makes Top-N queries faster with granule-level data skipping

ClickHouse projections now behave like true secondary indexes

Streaming secondary indices: incremental, demand-driven index evaluation

What really matters for performance: lessons from a year of benchmarks

How the 5 major cloud data warehouses compare on cost-performance

If it’s in your catalog, you can query it: The DataLakeCatalog engine in ClickHouse Cloud

Resources by Tom Schreiber

The definitive guide to ClickHouse query optimization (2026)