ClickHouse release 26.10

Another month goes by, which means it’s time for another release!

The ClickHouse 26.10 release contains 104 new features 🎃 225 performance optimizations 🍂 and 739 bug fixes 🦉.

This release brings function composition for more readable SQL, more flexible JOIN conditions, and new ways to work with text and SQLite files.

We also have faster GROUP BY queries, disk spilling for IN subqueries, and an experimental columns cache that saves repeated reads from decompressing and deserializing the same data.

New contributors #

A special welcome to all the new contributors in 26.10! 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:

ARON, Aarav, Abdullah Kaya, Akanshaak, Aleh Strakachuk, Aleksandr Efimov, Alexey Sitnikov, Andrea Debernardi, Andrew Wilkins, Angelica Ismailos, Armin Braun, Arvind Kumar, Ashish Choubey, Azarudeen Shariff A, Azhar Izzannada E, Bentsi Leviav, Chester, Craig Ringer, David Rolfe, Denis Voytyuk, Dimitri König, Frank Denis, Fábio Neves, GON, Gheorghe Pucea, Ilias, Ilias Aberkane, Javier G. Sogo, Jay Pokale, Jaysurya Ray, Jonathan Ackerman, Knut-Aage-Hofseth, Krithika Balagurunathan, LeeTeng2001, LyJacky, Maksim Andreev, Mike Lewis, Mishalski, Mohammed Sohail, Nick Akey, Oleg Milanov, Prince Maurya, RJ, Ray Winkelman, Rion Bora, Sam Wolfberg, Shahzeb Ali, Thomas Sileghem, Tom Alard, Unamed, Uwez Khan, Vadim Lopatin, Vector, Vitaly Pryakhin, Vlad, VrtxOmega, Zach Rippas, ZainnQureshii, ajay tomar, amogiska, antonioscaramuzzino, arc1999, artemkulyk, awss, azharizz, bentsileviav, bisht2050, clickgapai, comino, dhruvtaneja-ch, dosedaf, erezrokah, gonappuccino, jacky, linjiayu1025-collab, namtran1812, seva-potapov, sunliqiang, superdiaodiao, tianhui-fu, tridao, turbolytics, turbolytics.io, ultrabig, vector524

Hint: if you’re curious how we generate this list… here.

You can also view the slides from the presentation.

Function composition operator #

Contributed by Ivan Esipov and Alexey Milovidov #

If you’ve used ClickHouse’s higher-order functions, like arrayMap and arrayFilter, you’ll know that the lambda you pass in can quickly turn into a series of nested function calls. ClickHouse 26.10 adds a function composition operator, which means we can now create a pipeline of functions joined with the | operator, making these lambdas easier to read.

Let’s see how the two styles compare when extracting site names from some URLs. We're going to lowercase the URL, strip the www prefix, take the first significant subdomain, and then uppercase it.

1WITH [
2    'https://WWW.ClickHouse.com/docs',
3    'https://GitHub.com/ClickHouse/ClickHouse',
4    'https://www.anthropic.com/news'
5] AS urls
6SELECT
7    arrayMap(x -> upper(firstSignificantSubdomain(domainWithoutWWW(lower(x)))), urls) AS with_lambda,
8    arrayMap(lower | domainWithoutWWW | firstSignificantSubdomain | upper, urls) AS with_composition
9FORMAT Vertical;

1Row 1:
2──────
3with_lambda:      ['CLICKHOUSE','GITHUB','ANTHROPIC']
4with_composition: ['CLICKHOUSE','GITHUB','ANTHROPIC']

Both columns give the same result, but the composed version reads more like a list of steps. Each function here can be called with just the value passed along, so we didn’t need to say where it should go.

To pass additional arguments to a function, use _ to mark where the value from the pipeline goes. For example, substring(_, 1, 3) takes the first three characters of the string passed along:

1WITH ['ClickHouse', 'Anthropic', 'OpenAI'] AS companies
2SELECT
3    arrayMap(x -> substring(upper(x), 1, 3), companies) AS with_lambda,
4    arrayMap(upper | substring(_, 1, 3), companies) AS with_composition
5FORMAT Vertical;

1Row 1:
2──────
3with_lambda:      ['CLI','ANT','OPE']
4with_composition: ['CLI','ANT','OPE']

Higher-order functions can also take several arrays of the same length, which they process position by position. In that case, use _1 for the element from the first array, _2 for the matching element from the second, and so on. Let’s work out the line totals for an order by multiplying each price by its quantity, and then format the results as prices:

1WITH
2    [49.99, 189.00, 19.50] AS prices,
3    [2, 1, 4] AS quantities
4SELECT
5    arrayMap(multiply(_1, _2) | toString | concat('$', _), prices, quantities) AS line_totals;

1┌─line_totals─────────────┐
2│ ['$99.98','$189','$78'] │
3└─────────────────────────┘

Placeholders also work on their own, without |, as a shorter way of writing a lambda. For example, _1 * _2 means the same thing as (price, quantity) -> price * quantity, as shown in the following example:

1WITH
2    ['Keyboard', 'Monitor', 'Mouse'] AS products,
3    [49.99, 189.00, 19.50] AS prices,
4    [2, 1, 4] AS quantities
5SELECT
6    arrayMap(_1 * _2, prices, quantities) AS line_totals,
7    arrayMap(concat(_1, ' x', toString(_2)), products, quantities) AS order_lines
8FORMAT Vertical;

1Row 1:
2──────
3line_totals: [99.98,189,78]
4order_lines: ['Keyboard x2','Monitor x1','Mouse x4']

Under the hood, ClickHouse turns the pipeline into an ordinary lambda during query analysis. We can see this by running EXPLAIN SYNTAX with run_query_tree_passes = 1, which shows the query after the analyzer has rewritten it:

1EXPLAIN SYNTAX run_query_tree_passes = 1
2SELECT arrayMap(
3    lower | domainWithoutWWW | firstSignificantSubdomain | upper,
4    ['https://WWW.ClickHouse.com/docs']
5) AS sites;

1SELECT arrayMap(lambda(tuple(__composed_arg_1), upper(firstSignificantSubdomain(domainWithoutWWW(lower(__composed_arg_1))))), _CAST(['https://WWW.ClickHouse.com/docs'], 'Array(String)')) AS sites
2FROM system.one AS __table1

The | chain has become a lambda with the same nested expression we started with, so the two styles run in exactly the same way and you can use whichever one you prefer.

Quotas on inserted rows #

Contributed by Alexey Milovidov #

Quotas let you limit how much a user can do in a given time interval. ClickHouse 26.10 adds a written_rows counter, so you can cap ingestion by the number of rows a user writes.

Let’s create a table and an ingest user who can write at most 100,000 rows, across no more than 250 inserts, per minute:

1CREATE TABLE events (id UInt64) 
2ORDER BY id;

1CREATE USER ingest IDENTIFIED WITH no_password;
2GRANT INSERT, SELECT ON default.events TO ingest;

1CREATE QUOTA ingest_limit
2FOR INTERVAL 1 minute MAX written_rows = 100000, query_inserts = 250
3TO ingest;

The first insert of 60,000 rows goes through without any problems:

1INSERT INTO events 
2SELECT number 
3FROM numbers(60000);

But if we run the same insert again within that minute, it takes us over the limit:

1Received exception:
2Code: 201. DB::Exception: Quota for user `ingest` for 60s has been exceeded: written_rows = 120000/100000. Interval will end at 2026-10-23 14:00:00. Name of quota template: `ingest_limit`. (QUOTA_EXCEEDED)

The rejected insert doesn’t write any data, so the table still contains 60,000 rows. As with other quotas, you can see how close each user is to their limits in system.quotas_usage:

1SELECT quota_name, written_rows, max_written_rows, query_inserts, max_query_inserts
2FROM system.quotas_usage
3WHERE quota_name = 'ingest_limit'
4FORMAT Vertical;

1Row 1:
2──────
3quota_name:        ingest_limit
4written_rows:      120000
5max_written_rows:  100000
6query_inserts:     2
7max_query_inserts: 250

New Array and Map functions #

Contributed by Minh Vu, Jimmy Aguilar Mena, UnamedRus, and Alexey Milovidov #

This 26.10 release also brings a handful of new functions for working with arrays and maps.

arrayMinIndex and arrayMaxIndex return the position of the smallest and largest element, optionally after applying a lambda to each one. arrayRemoveAt removes the element at a given position, and negative positions count back from the end.

Let’s see what each of them returns on some small arrays:

1WITH [2,5,10,9,7] AS values
2
3SELECT
4    arrayMinIndex(values) AS min_index,
5    arrayMaxIndex(values) AS max_index,
6    arrayMaxIndex(x -> length(x), ['a', 'abc', 'ab', 'abcd']) AS longest_index,
7    arrayRemoveAt(values, 2) AS remove_second,
8    arrayRemoveAt(values, -1) AS remove_last
9FORMAT Vertical;

1Row 1:
2──────
3min_index:     1
4max_index:     3
5longest_index: 4
6remove_second: [2,10,9,7]
7remove_last:   [2,5,10,9]

Array indexes in ClickHouse start at 1, so the smallest value, 2, which is in first position in the array, has an index of 1

We could then can pass the results straight into another array to look up a matching value. In the following example, we find the coldest and warmest days of the week, and then drop Friday from the list:

1WITH
2    ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'] AS days,
3    [18.5, 21.2, 16.9, 24.3, 22.0] AS temps
4SELECT
5    days[arrayMinIndex(temps)] AS coldest,
6    days[arrayMaxIndex(temps)] AS warmest,
7    arrayRemoveAt(days, -1) AS without_friday;

1┌─coldest─┬─warmest─┬─without_friday────────────┐
2│ Wed     │ Thu     │ ['Mon','Tue','Wed','Thu'] │
3└─────────┴─────────┴───────────────────────────┘

For maps, mapRemove drops a key, mapEntries returns the key-value pairs as an array of tuples, and mapContainsKeyValue checks whether a map contains a specific key-value pair:

1WITH map('env', 'prod', 'region', 'eu-west-1', 'team', 'payments') AS labels
2SELECT
3    mapRemove(labels, 'team') AS without_team,
4    mapEntries(labels) AS entries,
5    mapContainsKeyValue(labels, 'env', 'prod') AS is_prod
6FORMAT Vertical;

1Row 1:
2──────
3without_team: {'env':'prod','region':'eu-west-1'}
4entries:      [('env','prod'),('region','eu-west-1'),('team','payments')]
5is_prod:      1

mapContainsKeyValue can also use text indexes, which makes filtering on labels a lot quicker when you have plenty of rows.

Finally, the -Map combinator no longer needs a Map column. You can pass the key as the last argument instead, which makes it easy to aggregate per key directly from rows:

1SELECT
2    sumMap(views, country) AS views_by_country,
3    argMaxMap(page, views, country) AS top_page_by_country
4FROM VALUES('country String, page String, views UInt32',
5    ('UK', '/docs', 120), ('UK', '/blog', 340),
6    ('US', '/docs', 910), ('US', '/pricing', 450),
7    ('DE', '/blog', 75));

1┌─views_by_country─────────────┬─top_page_by_country──────────────────────┐
2│ {'DE':75,'UK':460,'US':1360} │ {'DE':'/blog','UK':'/blog','US':'/docs'} │
3└──────────────────────────────┴──────────────────────────────────────────┘

SQL-compatible integer division #

Contributed by Ilya Yatsishin #

In ClickHouse, dividing one integer by another has always returned a floating-point number, so 7 / 2 gives you 3.5. PostgreSQL, SQL Server, and SQLite all truncate that to 3 instead.

Alexey’s take on the release call was that ClickHouse is right and everyone else is wrong, but if you’re porting queries from another database and need them to be wrong in the same way, there’s now a setting for that. Here’s the default behaviour:

1SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);

1┌─divide(7, 2)─┬─divide(-7, 2)─┬─divide(7, 2.)─┬─toTypeName(divide(7, 2))─┐
2│          3.5 │          -3.5 │           3.5 │ Float64                  │
3└──────────────┴───────────────┴───────────────┴──────────────────────────┘

And if you prefer the PostgreSQL way, you can enable sql_compatible_division_for_integers:

1SET sql_compatible_division_for_integers = 1;
2
3SELECT 7 / 2, -7 / 2, 7 / 2.0, toTypeName(7 / 2);

1┌─divide(7, 2)─┬─divide(-7, 2)─┬─divide(7, 2.)─┬─toTypeName(divide(7, 2))─┐
2│            3 │            -3 │           3.5 │ UInt8                    │
3└──────────────┴───────────────┴───────────────┴──────────────────────────┘

The result is truncated towards zero and keeps an integer type, while dividing by a floating-point number still gives you a float. With the setting enabled, dividing by zero throws an error rather than returning inf:

1SET sql_compatible_division_for_integers = 1;
2
3SELECT 1 / 0;

1Received exception:
2Code: 153. DB::Exception: Division by zero: In scope SELECT divideExact(1, 0) AS `divide(1, 0)`. (ILLEGAL_DIVISION)

The setting is disabled by default. Views and other stored definitions keep the division behaviour they were created with, so enabling it won’t change the results of your existing views.

IN subqueries can spill to disk #

Contributed by Nihal Z. Miaji #

When we use IN with a subquery, ClickHouse builds a set of the values returned by that subquery and checks the outer query’s rows against it. A large set can use a lot of memory. ClickHouse 26.10 adds the ability to spill this set to disk, allowing the query to complete with a smaller memory limit.

Let’s try it with the UK property prices dataset, which contains property sales in England and Wales since 1995. We want to count sales in 2024 at addresses that also have a recorded sale before 2024. We’ll match on the postcode and the two address fields, addr1 and addr2, which identify the building and, where applicable, the flat. This is an address-based match rather than a permanent property identifier, and we’re counting sales rather than distinct properties.

First, let’s give the query a 4 GiB memory limit and leave spilling disabled:

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '4Gi',
13         max_bytes_before_external_set = 0;

1┌─repeat_address_sales_2024─┐
2│                    558458 │
3└───────────────────────────┘
4
51 row in set. Elapsed: 5.777 sec. Processed 60.90 million rows, 966.34 MB (10.54 million rows/s., 167.27 MB/s.)
6Peak memory usage: 1.96 GiB.

The subquery reads 29.3 million earlier sales to build its set of addresses. On ClickHouse 26.10, this query used 1.96 GiB of peak memory. Let's see what happens if we reduce the memory limit to 512 MiB:

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '512Mi',
13         max_bytes_before_external_set = 0;

1Elapsed: 0.978 sec. Processed 7.93 million rows, 122.04 MB (8.10 million rows/s., 124.75 MB/s.)
2
3Received exception:
4Code: 241. DB::Exception: Query memory limit exceeded: would use 537.51 MiB (attempt to allocate chunk of 63.96 MiB), maximum: 512.00 MiB: While executing CreatingSetsTransform. (MEMORY_LIMIT_EXCEEDED)

ClickHouse runs out of memory. We can now enable spilling by setting max_bytes_before_external_set. Let’s use a threshold of 128 MiB, keeping the query’s overall memory limit at 512 MiB:

1SELECT count() AS repeat_address_sales_2024
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4  AND date < '2025-01-01'
5  AND (postcode1, postcode2, addr1, addr2) IN
6  (
7      SELECT postcode1, postcode2, addr1, addr2
8      FROM uk_price_paid
9      WHERE date < '2024-01-01'
10  )
11SETTINGS max_threads = 2,
12         max_memory_usage = '512Mi',
13         max_bytes_before_external_set = '128Mi';

1┌─repeat_address_sales_2024─┐
2│                    558458 │
3└───────────────────────────┘
4
51 row in set. Elapsed: 3.185 sec. Processed 60.90 million rows, 966.48 MB (19.12 million rows/s., 303.44 MB/s.)
6Peak memory usage: 203.11 MiB.

This time we have a peak memory usage of 203 MiB. That’s above our 128 MiB spill threshold, which tells ClickHouse when to start spilling rather than imposing a hard memory limit. The query also needs memory to read and process data, so we set the spill threshold below the overall 512 MiB limit.

If we look at profile events, by pressing the space bar while the query's running, we'll see that ClickHouse spilled one set to disk and wrote four parts:

1SetsSpilledToDisk:          1
2ExternalSetWritePart:       4
3ExternalSetCompressedBytes: 470342352

The following table summarizes the various configurations we used:

Faster GROUP BY with LowCardinality columns #

Contributed by artemkulyk, Samuel Krempaský, and Alexey Milovidov #

ClickHouse 26.10 improves grouping by multiple LowCardinality columns. It can serialize the grouping keys directly from their dictionaries and cache aggregate-state lookups for repeated combinations of keys.

Let’s reuse our uk_price_paid table to count sales and calculate the average price for each combination of county, district, town, and outward postcode. All four grouping columns have a column type of LowCardinality(String):

1SELECT
2    county,
3    district,
4    town,
5    postcode1,
6    count() AS sales,
7    avg(price) AS average_price
8FROM uk_price_paid
9GROUP BY county, district, town, postcode1
10SETTINGS use_query_cache = 0
11FORMAT Null;

We use FORMAT Null to run the query without printing its 15,409 result rows, so terminal output doesn’t affect the comparison.

Let’s run the query three times on ClickHouse 26.9:

10 rows in set. Elapsed: 0.289 sec. Processed 30.45 million rows, 331.39 MB (105.20 million rows/s., 1.14 GB/s.)
2Peak memory usage: 170.67 MiB.
3
40 rows in set. Elapsed: 0.269 sec. Processed 30.45 million rows, 331.39 MB (113.29 million rows/s., 1.23 GB/s.)
5Peak memory usage: 171.81 MiB.
6
70 rows in set. Elapsed: 0.255 sec. Processed 30.45 million rows, 331.39 MB (119.19 million rows/s., 1.30 GB/s.)
8Peak memory usage: 177.98 MiB.

And now three times on ClickHouse 26.10, using the same data and table layout. Both versions use the default 12 threads on my machine:

10 rows in set. Elapsed: 0.242 sec. Processed 30.45 million rows, 331.39 MB (125.90 million rows/s., 1.37 GB/s.)
2Peak memory usage: 128.49 MiB.
3
40 rows in set. Elapsed: 0.181 sec. Processed 30.45 million rows, 331.39 MB (168.49 million rows/s., 1.83 GB/s.)
5Peak memory usage: 124.16 MiB.
6
70 rows in set. Elapsed: 0.171 sec. Processed 30.45 million rows, 331.39 MB (178.37 million rows/s., 1.94 GB/s.)
8Peak memory usage: 127.35 MiB.

If we take the best time from each version, the query took 255 milliseconds on 26.9 and 171 milliseconds on 26.10, meaning 26.10 is just over 30% quicker.

Top-K dynamic filtering for GROUP BY #

Contributed by Alexey Milovidov and Konstantin Bogdanov #

ClickHouse 25.12 introduced dynamic filtering for ORDER BY … LIMIT queries, using the current Top-K boundary to avoid unnecessary column reads and skip granules through minmax indexes. ClickHouse 26.10 extends this approach to GROUP BY queries and adds granule skipping through the primary index.

Let’s reuse our UK property data to find the ten most recent dates with recorded sales, along with the number of sales and their average price. We’ll create a separate table containing the two columns used by the query, sorted by date. This layout lets ClickHouse use both mechanisms: filter older sales before reading their prices, and use the primary index to skip granules whose dates are all older than the cutoff.

1CREATE TABLE uk_price_paid_by_date
2ENGINE = MergeTree
3ORDER BY date
4AS SELECT date, price FROM uk_price_paid;
5
6OPTIMIZE TABLE uk_price_paid_by_date FINAL;

This copies all 30m sales and merges the new table into a single part. We’ll run the query on ClickHouse 26.10 so we can see what the output looks like:

1SELECT
2    date,
3    count() AS sales,
4    round(avg(price)) AS average_price
5FROM uk_price_paid_by_date
6GROUP BY date
7ORDER BY date DESC
8LIMIT 10
9SETTINGS
10    enable_group_by_top_k_dynamic_filtering = 0,
11    use_query_condition_cache = 0,
12    use_query_cache = 0;

The query returns the following ten dates:

1┌───────date─┬─sales─┬─average_price─┐
2│ 2025-07-31 │    26 │        300021 │
3│ 2025-07-30 │    39 │        316864 │
4│ 2025-07-29 │    92 │        344986 │
5│ 2025-07-28 │   220 │        317645 │
6│ 2025-07-27 │     4 │        242998 │
7│ 2025-07-25 │   926 │        316243 │
8│ 2025-07-24 │   388 │        358814 │
9│ 2025-07-23 │   437 │        347647 │
10│ 2025-07-22 │   373 │        332035 │
11│ 2025-07-21 │   662 │        337260 │
12└────────────┴───────┴───────────────┘

Time to test it with and without the optimization using clickhouse-local. The timings come from the CLI’s --time output, and the read counts/peak memory come from --print-profile-events:

Changing enable_group_by_top_k_dynamic_filtering to 1 gives us:

From the table of results, we can see that with filtering enabled, ClickHouse reads around 94% fewer rows. The runs with the optimization skipped 3,529, 3,521, and 3,529 granules respectively, whereas no granules were skipped without it. The exact amount of work varies as the parallel aggregation streams discover and publish their cutoffs.

We can confirm that whole granules were skipped using the TopKGranulesSkippedByPrimaryKey profile event. With --print-profile-events --profile-events-delay-ms=-1, the CLI reports the final event totals. For the first enabled run, the relevant values were:

1SelectedRows:                     1728263
2SelectedBytes:                    10369578
3TopKGranulesSkippedByPrimaryKey:  3529

Columns cache #

Contributed by Alexey Milovidov #

ClickHouse 26.10 introduces an experimental columns cache, which keeps deserialized column data in memory, saving subsequent queries from decompressing and deserializing it again.

Let’s reuse our UK property dataset to count sales where the first address field contains HOUSE. We'll create a copy containing the price and address columns, keeping the original sort order:

1CREATE TABLE uk_price_paid_columns_cache
2ENGINE = MergeTree
3ORDER BY (postcode1, postcode2, addr1, addr2)
4SETTINGS min_bytes_for_wide_part = 0,
5         min_rows_for_wide_part = 0
6AS SELECT postcode1, postcode2, addr1, addr2, price FROM uk_price_paid;

The cache currently supports wide MergeTree parts. Setting both size thresholds to 0 forces this format.

First, let's run the query with the columns cache disabled:

1SELECT count() AS house_sales
2FROM uk_price_paid_columns_cache
3WHERE addr1 LIKE '%HOUSE%'
4SETTINGS use_columns_cache = 0,
5         use_query_cache = 0,
6         use_query_condition_cache = 0,
7         log_queries = 1,
8         log_profile_events = 1,
9         log_comment = 'sorted-columns-cache:off-1';

1┌─house_sales─┐
2│      904871 │
3└─────────────┘

After one unmeasured run to warm the filesystem cache, we'll run this query three times, using sorted-columns-cache:off-1, sorted-columns-cache:off-2, and sorted-columns-cache:off-3 as the log comments.

Then we'll change use_columns_cache to 1 and run it three more times, using comments sorted-columns-cache:on-1, sorted-columns-cache:on-2, and sorted-columns-cache:on-3. The first enabled run populates the columns cache, and the next two reuse it.

With caching enabled, the query caches addr1. It doesn't read price, for example, so that column isn't cached. But, if we added a condition on price, any data read from that column would also be cached.

With query logging enabled, we can retrieve the timings and cache counters from system.query_log, using the following query:

1SYSTEM FLUSH LOGS;
2
3SELECT
4    log_comment AS run,
5    query_duration_ms AS queryTime,
6    ProfileEvents['ColumnsCacheHits'] AS cacheHits,
7    ProfileEvents['ColumnsCacheMisses'] AS cacheMisses,
8    ProfileEvents['SelectedRows'] AS selectedRows
9FROM
10(
11    SELECT *
12    FROM system.query_log
13    WHERE type = 'QueryFinish'
14      AND startsWith(log_comment, 'sorted-columns-cache:')
15      AND log_comment != 'sorted-columns-cache:warmup'
16    ORDER BY query_start_time_microseconds DESC
17    LIMIT 6
18)
19ORDER BY query_start_time_microseconds
20FORMAT Markdown;

With the columns cache disabled, the query takes 32–34 milliseconds. cacheHits is 0 because ClickHouse isn't looking in the cache.

The run in which the cache gets populated takes 37 milliseconds, and the next two runs that use the cache each take 12 milliseconds, with all 3,718 granules of addr1 read from the columns cache.

SelectedRows stays the same: ClickHouse still evaluates the filter against all 30.45 million rows, but it can reuse the decoded address data. The cache stores column data rather than the query result, so other queries reading the same data can benefit too.

LATERAL JOIN #

Contributed by Dimitri König and Alexey Milovidov #

ClickHouse 26.10 adds experimental support for LATERAL JOIN, which lets a subquery reference columns from a table that appears before it. We can enable it by setting allow_experimental_lateral_join = 1.

We'll use the IMDb dataset from our joins examples to find the three highest-rated films for each of three actors. The actors table contains their names, movies contains film details and ratings, and roles connects actors to the films they appeared in.

1SELECT
2    concat(a.first_name, ' ', a.last_name) AS actor,
3    top_titles.name AS title,
4    top_titles.year AS year,
5    round(top_titles.rank, 1) AS rating
6FROM actors AS a
7JOIN LATERAL
8(
9    SELECT m.id, m.name, m.year, m.rank
10    FROM roles AS r
11    INNER JOIN movies AS m ON r.movie_id = m.id
12    WHERE r.actor_id = a.id
13      AND m.rank IS NOT NULL
14    GROUP BY m.id, m.name, m.year, m.rank
15    ORDER BY m.rank DESC, m.year DESC, m.id
16    LIMIT 3
17) AS top_titles ON true
18WHERE (a.first_name, a.last_name) IN
19    (('Tom', 'Hanks'), ('Meryl', 'Streep'), ('Denzel', 'Washington'))
20ORDER BY actor, rating DESC, year DESC, top_titles.id
21SETTINGS allow_experimental_lateral_join = 1;

Line 12 is the important one: WHERE r.actor_id = a.id. The a.id value comes from actors AS a in the outer query. The LATERAL lets our inner query use that value, which means we find the top three films for this actor. LIMIT 3 applies per actor, rather than selecting three films across the whole dataset.

For each selected actor, the inner query finds their roles, joins them to the film details, and excludes films without a rating. The GROUP BY removes duplicate films where an actor has multiple role records. It then sorts by rating, using the year and film ID to break ties, and returns the top three.

1┌─actor─────────────┬─title──────────────────┬─year─┬─rating─┐
2│ Denzel Washington │ Glory                  │ 1989 │    8.1 │
3│ Denzel Washington │ Much Ado About Nothing │ 1993 │    7.5 │
4│ Denzel Washington │ Antwone Fisher         │ 2002 │    7.4 │
5│ Meryl Streep      │ Manhattan              │ 1979 │    8.2 │
6│ Meryl Streep      │ Deer Hunter, The       │ 1978 │    8.1 │
7│ Meryl Streep      │ Adaptation.            │ 2002 │    7.9 │
8│ Tom Hanks         │ Saving Private Ryan    │ 1998 │    8.3 │
9│ Tom Hanks         │ Green Mile, The        │ 1999 │    8.1 │
10│ Tom Hanks         │ Toy Story 2            │ 1999 │    8.1 │
11└───────────────────┴────────────────────────┴──────┴────────┘

We get nine rows: three films for each actor.

JOINs with arbitrary conditions #

Contributed by Vladimir Cherkasov and Alexey Milovidov #

ClickHouse 26.10 also supports JOINs with arbitrary ON conditions, including those without an equality condition between the tables. Let's use this to categorize website traffic using URL patterns.

First, we'll create a table containing page view counts:

1CREATE TABLE page_views
2(
3    path String,
4    views UInt64
5);
6
7INSERT INTO page_views VALUES
8    ('/docs/getting-started', 1200),
9    ('/docs/sql-reference/functions', 850),
10    ('/blog/clickhouse-release-26-10', 2400),
11    ('/pricing', 600),
12    ('/about', 300);

We'll store the categorization rules in a separate table. The % wildcard matches any sequence of characters:

1CREATE TABLE page_categories
2(
3    pattern String,
4    category String
5);
6
7INSERT INTO page_categories VALUES
8    ('/docs/%', 'Documentation'),
9    ('/blog/%', 'Blog'),
10    ('/pricing', 'Pricing');

We can now join the tables using LIKE to match each path to a category:

1SELECT p.path, p.views, c.category
2FROM page_views AS p
3LEFT JOIN page_categories AS c ON p.path LIKE c.pattern
4ORDER BY p.views DESC;

1┌─path───────────────────────────┬─views─┬─category──────┐
2│ /blog/clickhouse-release-26-10 │  2400 │ Blog          │
3│ /docs/getting-started          │  1200 │ Documentation │
4│ /docs/sql-reference/functions  │   850 │ Documentation │
5│ /pricing                       │   600 │ Pricing       │
6│ /about                         │   300 │               │
7└────────────────────────────────┴───────┴───────────────┘

Keep in mine that the flexibility of arbitrary joins can be expensive: the block nested loop join checks every pair of rows, so it can be as slow as a cross join and is used as a last resort by the query planner.

Freeform input format #

Contributed by Tri Dao and Alexey Milovidov #

ClickHouse 26.10 also adds the Freeform input format, which infers columns and their types from consistently structured text.

Let's see how it works by piping the output of ls -l into clickhouse-local. The piped input will be available as table and can be queried the same way as any other table. The following query desribes the output of ls -l:

1ls -l **/*.parquet | 
2./clickhouse --input-format Freeform --query "DESCRIBE TABLE table"

1c0	String
2c1	Int64
3c2	String
4c3	String
5c4	Int64
6c5	Int64
7c6	String
8c7	Int64
9c8	Int64
10c9	String

We can see from this output that ls -l outputs 10 columns, which are named c0 to c9. We can query table to return all columns with the following query;

1ls -l **/*.parquet | 
2./clickhouse --input-format Freeform --query "FROM table FORMAT Vertical"

1Row 1:
2──────
3c0: -rw-r--r--@
4c1: 1
5c2: markhneedham
6c3: staff
7c4: 2048074919 -- 2.05 billion
8c5: 30
9c6: Oct
10c7: 16
11c8: 23
12c9: benchmarks/amazon-columns-cache/data/amazon_reviews_2010.snappy.parquet
13
14Row 2:
15──────
16c0: -rw-r--r--@
17c1: 1
18c2: markhneedham
19c3: staff
20c4: 3013406813 -- 3.01 billion
21c5: 30
22c6: Oct
23c7: 16
24c8: 27
25c9: benchmarks/amazon-columns-cache/data/amazon_reviews_2011.snappy.parquet

Let's give the columns meaningful names, format the file sizes, and combine the date fields into a timestamp. This listing doesn't include a year, so we'll use the current year for these recent files:

1ls -l **/*.parquet |
2./clickhouse --input-format Freeform --query "
3    SELECT
4        c9 AS file,
5        formatReadableSize(c4) AS size,
6        parseDateTimeBestEffort(
7            format('{} {} {} {}:{}', c5, c6, toYear(today()), c7, c8)
8        ) AS modified
9    FROM table
10    ORDER BY c4 DESC
11    FORMAT Vertical"

1Row 1:
2──────
3file:     benchmarks/amazon-columns-cache/data/amazon_reviews_2011.snappy.parquet
4size:     2.81 GiB
5modified: 2026-10-30 16:27:00
6
7Row 2:
8──────
9file:     benchmarks/amazon-columns-cache/data/amazon_reviews_2010.snappy.parquet
10size:     1.91 GiB
11modified: 2026-10-30 16:23:00

SQLite import and export #

Contributed by Yue Ni and Alexey Milovidov #

ClickHouse already supports querying SQLite databases through a database engine, table engine, and table function. Version 26.10 adds SQLite as an input and output format, letting us export query results directly into a SQLite database file, ready to open with SQLite tools or share with someone else.

Let's reuse our IMDb movies table to count films and calculate their average rating for each year of the 1990s:

1SELECT
2    year,
3    toInt64(count()) AS films,
4    round(avg(rank), 2) AS average_rating
5FROM movies
6WHERE year BETWEEN 1990 AND 1999
7GROUP BY year
8ORDER BY year
9INTO OUTFILE 'films.sqlite'
10SETTINGS output_format_sqlite_table_name = 'films_by_year';

ClickHouse uses the SQLite format becaues of the .sqlite extension of the output file, but we can also specify the output explicitly by specifying FORMAT SQLite after the file name. We write ten results in a table called films_by_year.

We can open the file with the sqlite3 command-line tool and query it:

1sqlite3 -header -column films.sqlite \
2  'SELECT * FROM films_by_year ORDER BY year LIMIT 5;'

1year  films  average_rating
2----  -----  --------------
31990  6098   5.33
41991  6068   5.69
51992  6580   5.53
61993  6900   5.55
71994  7502   5.43

ClickHouse can also read the file back, inferring the column names and types:

1SELECT *
2FROM file('films.sqlite')
3ORDER BY year
4LIMIT 5;

1┌─year─┬─films─┬─average_rating─┐
2│ 1990 │  6098 │           5.33 │
3│ 1991 │  6068 │           5.69 │
4│ 1992 │  6580 │           5.53 │
5│ 1993 │  6900 │           5.55 │
6│ 1994 │  7502 │           5.43 │
7└──────┴───────┴────────────────┘

String filtering during the scan #

Contributed by Alexey Milovidov #

ClickHouse 26.10 can check string filters such as LIKE, startsWith, and endsWith while deserializing a column, avoiding copying non-matching strings into memory. The optimization is enabled by default through apply_string_filters_during_scan.

Let's try it with the Amazon reviews dataset. We'll load the 2010 and 2011 Parquet files from our Freeform example into a MergeTree table, giving us 9.98 million reviews:

1CREATE TABLE amazon_reviews
2ENGINE = MergeTree
3ORDER BY tuple()
4AS SELECT *
5FROM file('benchmarks/amazon-columns-cache/data/amazon_reviews_{2010,2011}.snappy.parquet');

We want to count reviews containing the word disappointed. First, let's disable the optimization, along with the columns cache, query cache, and query condition cache:

1SELECT count() AS matching_reviews
2FROM amazon_reviews
3WHERE review_body LIKE '%disappointed%'
4SETTINGS apply_string_filters_during_scan = 0,
5         use_columns_cache = 0,
6         use_query_cache = 0,
7         use_query_condition_cache = 0;

1┌─matching_reviews─┐
2│           293115 │
3└──────────────────┘

We ran this on ClickHouse 26.10 with the default 12 threads, after merging the table into one Wide part. After one warm-up with each setting, we alternated three measured runs with the optimization disabled and enabled. The timings and peak memory figures below come from system.query_log.

With apply_string_filters_during_scan = 0:

Changing apply_string_filters_during_scan to 1 gives us:

Every run returns the same 293,115 matches. Execution time is similar, but peak memory drops from around 129 MiB to 2.54 MiB, a reduction of about 98%.