Another month goes by, which means it's time for another release!
The ClickHouse 26.6 release contains 56 new features 🌞 79 performance optimizations 🏖️ 366 bug fixes 🍦
This release introduces hypothetical skip indexes, cascading refreshable materialized views, experimental support for continuous queries, and more!
New contributors
A special welcome to all the new contributors in 26.6! The growth of ClickHouse's community is humbling, and we are always grateful for the contributions that have made ClickHouse so popular.
Below are the names of the new contributors:
Aditya Chopra, Alasdair Brown, Almaz Kunpeissov, Andriy Yakovlev, Antonio Filipovic, Asya Shneerson, Avenir Voronov, Dmitriy Borisenko, Elian Gidoni, Hanzi Jiang, Harikrishnan Prabakaran, Joe Smith, Joey Yu, Le Zhang, Lefteris Gilmaz, Maksim Dergousov, Maksim Moisiuk, Mathuranath Metivier, Minh Vu, Mohamed Abdelhalim, Mohamed Hussain, MunMunMiao, Patrick Pichler, Ramarajusairajesh, Rory Shanks, SKULLFIRE07, Saarthak Gupta, Sacheendra Talluri, Sergey Kuznetsov, Thomas Cabral, Valerii Mordovskii, Valerii Petrov, Varoon Pazhyanur, Venkata Vineel, Vinayak Joshi, Walt Ribeiro, Youssef Kadry, abdelhalim, abduldjafar, alexbakharew, andyzzhao, bernardlim, daxzel, harikrishnan94, leonard9893, linjiayu, mzitnik, ofeliacode, siwakorn.r, sugaf1204, thewisenerd, uber, uwezkhan, valerypetrov, yousefQadry, zhiqiang-hhhh
Hint: if you're curious how we generate this list… here.
You can also view the slides from the presentation.
Hypothetical skip indexes
Contributed by Yarik Briukhovetskyi
Starting from ClickHouse 26.6, it's possible to ask "what if I had this skip index?" without having to build it.
Hypothetical indexes live only in the current session and are invisible to other sessions and discarded when the session ends.
We tried them out on the UK properties dataset, having duplicated the partitions a few times so that we had more rows to work with:
ALTER TABLE uk_price_paid
ATTACH PARTITION ID 'all'
FROM uk_price_paid;We have almost 500 million rows:
SELECT count() FROM uk_price_paid;┌───count()─┐
│ 487239408 │ -- 487.24 million
└───────────┘
1 row in set. Elapsed: 0.006 sec.We run the following query to find the districts in London with the most sales:
SELECT district, count(), round(avg(price)) AS avgPrice
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10;┌─district────────────┬─count()─┬─avgPrice─┐
│ WANDSWORTH │ 3258048 │ 496367 │
│ LAMBETH │ 2354352 │ 402424 │
│ CITY OF WESTMINSTER │ 2164304 │ 1215976 │ -- 1.22 million
│ TOWER HAMLETS │ 2098864 │ 473783 │
│ LEWISHAM │ 2022208 │ 291688 │
│ SOUTHWARK │ 1998816 │ 462604 │
│ BARNET │ 1942384 │ 449124 │
│ GREENWICH │ 1874464 │ 316369 │
│ WALTHAM FOREST │ 1813360 │ 270709 │
│ NEWHAM │ 1706352 │ 284768 │
└─────────────────────┴─────────┴──────────┘
10 rows in set. Elapsed: 0.317 sec. Processed 487.24 million rows, 1.20 GB (1.54 billion rows/s., 3.80 GB/s.)
Peak memory usage: 894.97 KiB.We can probably improve the performance of this query by adding a set skip index on the town column.
A set skip index would store the unique values for that column for the provided number of granules. At query time, ClickHouse could refer to this skip-index set to determine whether it needs to scan a particular granule/granules.
Before 26.6, we'd need to create that skip index and test it out, but now we can create a hypothetical index instead. And, in fact, we're going to create two hypothetical indexes so that we can see the difference between creating a skip index per granule compared to one for every 128 granules:
CREATE HYPOTHETICAL INDEX town_set_10_granularity_1
ON uk_price_paid (town)
TYPE set(10)
GRANULARITY 1;
CREATE HYPOTHETICAL INDEX town_set_10_granularity_128
ON uk_price_paid (town)
TYPE set(10)
GRANULARITY 128;Once we've done that, we can prefix our district query with EXPLAIN WHATIF:
EXPLAIN WHATIF
SELECT district, count(), round(avg(price)) AS avgPrice
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10;When we run that query, ClickHouse will read table data to build the candidate index in memory, and scan counts against the session's read limits and quotas. The output of running the query is shown below:
┌─explain───────────────────────────────────────────────┐
│ Baseline (after PK + partition + existing indexes): │
│ table: default.uk_price_paid │
│ parts: 3 │
│ marks: 59479 │
│ est_bytes: 831.90 MiB │
│ │
│ With town_set_10_granularity_128 (set, hypothetical): │
│ status: applicable │
│ marks: 13702 │
│ est_bytes: 191.64 MiB │
│ skip_ratio: 77.0% │
│ │
│ Estimation: │
│ source: empirical │
│ empirical_status: ok │
│ sampled_parts: 3 / 3 │
│ sampled_marks: 59479 / 118964 │
│ elapsed_us: 3826267 │
│ │
│ With town_set_10_granularity_1 (set, hypothetical): │
│ status: applicable │
│ marks: 4663 │
│ est_bytes: 65.22 MiB │
│ skip_ratio: 92.2% │
│ │
│ Estimation: │
│ source: empirical │
│ empirical_status: ok │
│ sampled_parts: 3 / 3 │
│ sampled_marks: 59479 / 118964 │
│ elapsed_us: 4283731 │
│ │
└───────────────────────────────────────────────────────┘
32 rows in set. Elapsed: 8.115 sec. Processed 974.48 million rows, 1.95 GB (120.08 million rows/s., 240.11 MB/s.)
Peak memory usage: 269.35 KiB.If we look at row 12, we can see that the index for 128 granules will skip 77% of granules for this query, whereas on row 25, the index per granule would instead skip 92% of granules.
This gives us some useful information before deciding whether to create a skip index and what settings to use.
Cascading refreshable materialized views
Contributed by Michael Kolupaev
ClickHouse 26.6 introduces an overhaul of how dependencies work for refreshable materialized views.
Before this release, it was possible to create dependencies between refreshable materialized views, but the dependent views still ran on their own independent timers. This meant that latency could build up between stages if the schedules drifted, and views could skip or lag by a full refresh cycle.
Let's have a look at how to set things up with the following set of tables that represent IMDB data:
CREATE TABLE actor_summary
(
`id` UInt32,
`name` String,
`movies` UInt16,
`avg_rank` Float32,
`genres` UInt16,
`directors` UInt16,
`updated_at` DateTime
)
ENGINE = MergeTree
ORDER BY movies;CREATE TABLE actor_rank
(
`id` UInt32,
`name` String,
`movies` UInt16,
`avg_rank` Float32,
`genres` UInt16,
`directors` UInt16,
`updated_at` DateTime
)
ENGINE = MergeTree
ORDER BY movies;CREATE TABLE actor_rank_over_time
(
`id` UInt32,
`name` String,
`avg_rank` Float32,
`as_of` DateTime
)
ENGINE = MergeTree
ORDER BY as_of;We previously populated these tables like this:
CREATE MATERIALIZED VIEW actor_summary_mv
REFRESH EVERY 2 MINUTES TO actor_summary AS
...CREATE MATERIALIZED VIEW actor_rank_mv
REFRESH EVERY 1 MINUTE DEPENDS ON actor_summary_mv
TO imdb.actor_rank AS
SELECT *
FROM actor_summary
WHERE movies > 10
ORDER BY avg_rank DESC
LIMIT 5;CREATE MATERIALIZED VIEW actor_rank_over_time_mv
REFRESH EVERY 1 MINUTE DEPENDS ON actor_rank_mv
APPEND
TO imdb.actor_rank_over_time AS
SELECT id, name, avg_rank, now() AS as_of
FROM actor_rank
ORDER BY avg_rank DESCFetched July 1, 2026
