ClickHouse version 25.10 contains 20 new features 👻 30 performance optimizations 🔮 103 bug fixes 🎃
This release introduces a collection of join improvements, a new data type for vector search, late materialization of secondary indices, and more! ## New contributors A special welcome to all the new contributors in 25.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: *0xgouda, Ahmed Gouda, Albert Chae, Austin Bonander, ChaiAndCode, David E. Wheeler, DeanNeaht, Dylan, Frank Rosner, GEFFARD Quentin, Giampaolo Capelli, Grant Holly, Guang, Guang Zhao, Isak Ellmer, Jan Rada, Kunal Gupta, Lonny Kapelushnik, Manuel Raimann, Michal Simon, Narasimha Pakeer, Neerav, Raphaël Thériault, Rui Zhang, Sadra Barikbin, copilot-swe-agent[bot], dollaransh17, flozdra, jitendra1411, neeravsalaria, pranav mehta, zlareb1, |2ustam, Андрей Курганский, Артем Юров* Hint: if you’re curious how we generate this list… [here](https://gist.github.com/gingerwizard/5a9a87a39ba93b422d8640d811e269e9). You can also [view the slides from the presentation](https://presentations.clickhouse.com/2025-release-25.10/). ## Lazy columns replication in JOINs ### Contributed by Pavel Kruglov > "When will you stop optimizing join performance?" We will never stop! This release once again brings JOIN performance optimizations. The first join improvement in 25.10 is **lazy columns replication**, a new optimization that reduces CPU and memory usage when JOINs produce many duplicate values. When running JOIN queries (including those using the [arrayJoin](https://clickhouse.com/docs/sql-reference/functions/array-join) function), values from the input tables are often replicated in the result, especially when there are many matches for a given key. As an example, consider a hits table containing [anonymized web analytics data](https://clickhouse.com/docs/getting-started/example-datasets/metrica), sketched below with two columns, `ClientIP` and `URL`:  When we run a self-join:
SELECT ...
FROM
hits AS t1 INNER JOIN hits AS t2
ON t1.ClientIP = t2.ClientIP;
SELECT sum(cityHash64(URL))
FROM
hits AS t1 INNER JOIN hits AS t2
ON t1.ClientIP = t2.ClientIP
SETTINGS
enable_lazy_columns_replication = 0,
allow_special_serialization_kinds_in_output_formats = 0;
┌─sum(cityHash64(URL))─┐
│ 8580639250520379278 │
└──────────────────────┘
1 row in set. Elapsed: 83.396 sec. Processed 199.99 million rows, 10.64 GB (2.40 million rows/s., 127.57 MB/s.)
Peak memory usage: 4.88 GiB.
SELECT sum(cityHash64(URL))
FROM
hits AS t1 INNER JOIN hits AS t2
ON t1.ClientIP = t2.ClientIP
SETTINGS
enable_lazy_columns_replication = 1,
allow_special_serialization_kinds_in_output_formats = 1;
┌─sum(cityHash64(URL))─┐
│ 8580639250520379278 │
└──────────────────────┘
1 row in set. Elapsed: 4.078 sec. Processed 199.99 million rows, 10.64 GB (49.04 million rows/s., 2.61 GB/s.)
Peak memory usage: 4.57 GiB.
EXPLAIN plan
SELECT *
FROM orders, customer
WHERE o_custkey = c_custkey
SETTINGS enable_join_runtime_filters = 0;
...
Join
...
ReadFromMergeTree (default.orders)
ReadFromMergeTree (default.customer)
EXPLAIN plan
SELECT *
FROM orders, customer
WHERE o_custkey = c_custkey
SETTINGS enable_join_runtime_filters = 1;
...
Join
...
Prewhere filter column: __filterContains(_runtime_filter_14211390369232515712_0, __table1.o_custkey)
...
BuildRuntimeFilter (Build runtime join filter on __table2.c_custkey (_runtime_filter_14211390369232515712_0))
...
SELECT avg(o_totalprice)
FROM orders, customer, nation
WHERE (c_custkey = o_custkey) AND (c_nationkey = n_nationkey) AND (n_name = 'FRANCE')
SETTINGS enable_join_runtime_filters = 0;
┌──avg(o_totalprice)─┐
│ 151149.41468432106 │
└────────────────────┘
1 row in set. Elapsed: 1.005 sec. Processed 165.00 million rows, 1.92 GB (164.25 million rows/s., 1.91 GB/s.)
Peak memory usage: 1.24 GiB.
SELECT avg(o_totalprice)
FROM orders, customer, nation
WHERE (c_custkey = o_custkey) AND (c_nationkey = n_nationkey) AND (n_name = 'FRANCE')
SETTINGS enable_join_runtime_filters = 1;
┌──avg(o_totalprice)─┐
│ 151149.41468432106 │
└────────────────────┘
1 row in set. Elapsed: 0.471 sec. Processed 165.00 million rows, 1.92 GB (350.64 million rows/s., 4.08 GB/s.)
Peak memory usage: 185.18 MiB.
(t1.k IN (1,2) AND t2.x = 100)
OR
(t1.k IN (3,4) AND t2.x = 200)
EXPLAIN plan
SELECT *
FROM customer AS c
INNER JOIN nation AS n
ON c.c_nationkey = n.n_nationkey
WHERE (c.c_name LIKE 'Customer#00000%' AND n.n_name = 'GERMANY')
OR (c.c_name LIKE 'Customer#00001%' AND n.n_name = 'FRANCE')
SETTINGS use_join_disjunctions_push_down = 0;
Join
...
ReadFromMergeTree (default.customer)
ReadFromMergeTree (default.nation)
EXPLAIN plan
SELECT *
FROM customer AS c
INNER JOIN nation AS n
ON c.c_nationkey = n.n_nationkey
WHERE (c.c_name LIKE 'Customer#00000%' AND n.n_name = 'GERMANY')
OR (c.c_name LIKE 'Customer#00001%' AND n.n_name = 'FRANCE')
SETTINGS use_join_disjunctions_push_down = 1;
Join
...
Filter
ReadFromMergeTree (default.customer)
...
Filter
ReadFromMergeTree (default.nation)
SELECT *
FROM customer AS c
INNER JOIN nation AS n
ON c.c_nationkey = n.n_nationkey
WHERE (c.c_name LIKE 'Customer#00000%' AND n.n_name = 'GERMANY')
OR (c.c_name LIKE 'Customer#00001%' AND n.n_name = 'FRANCE')
SETTINGS use_join_disjunctions_push_down = 0;
788 rows in set. Elapsed: 0.240 sec. Processed 15.00 million rows, 2.93 GB (62.56 million rows/s., 12.21 GB/s.)
Peak memory usage: 261.30 MiB.
SELECT *
FROM customer AS c
INNER JOIN nation AS n
ON c.c_nationkey = n.n_nationkey
WHERE (c.c_name LIKE 'Customer#00000%' AND n.n_name = 'GERMANY')
OR (c.c_name LIKE 'Customer#00001%' AND n.n_name = 'FRANCE')
SETTINGS use_join_disjunctions_push_down = 0;
788 rows in set. Elapsed: 0.010 sec. Processed 24.60 thousand rows, 4.81 MB (2.47 million rows/s., 482.53 MB/s.)
Peak memory usage: 4.30 MiB.
CREATE TABLE tpch.orders (...) ORDER BY (o_orderkey)
SETTINGS auto_statistics_types = 'minmax, uniq, countmin';
$ cat /etc/config.d/merge_tree.yaml
merge_tree:
auto_statistics_types: 'minmax, uniq, countmin'
CREATE TABLE vectors (
id UInt64, name String, ...
vec QBit(BFloat16, 1536)
) ORDER BY ();
SELECT id, name FROM vectors
ORDER BY L2DistanceTransposed(vector, target, 10)
LIMIT 10;
SELECT NULL <=> NULL, NULL = NULL;
SELECT date, price, county, district
FROM uk.uk_price_paid
WHERE date >= '2024-01-01' AND price > 10_000_000
ORDER BY date DESC LIMIT 10;
SELECT date, price, county, district
FROM uk.uk_price_paid
WHERE date >= '2024-01-01' AND price > 10_000_000
ORDER BY date LIMIT -10;
SELECT date, price, county, district
FROM uk.uk_price_paid
WHERE date >= '2024-01-01' AND price > 10_000_000
ORDER BY date LIMIT -10 OFFSET -10;
SELECT town, district, type
FROM uk.uk_price_paid
WHERE county = 'GREATER LONDON' AND price > 10_000_000 AND type <> 'other'
ORDER BY price DESC
LIMIT 10;
SELECT town, district, type
FROM uk.uk_price_paid
WHERE county = 'GREATER LONDON' AND price > 10_000_000 AND type <> 'other'
ORDER BY price DESC
LIMIT 1 BY town, district, type
LIMIT 10;
SELECT town, district, type
FROM uk.uk_price_paid
WHERE county = 'GREATER LONDON' AND price > 10_000_000 AND type <> 'other'
ORDER BY price DESC
LIMIT 1 BY ALL
LIMIT 10;
arrowflight_port: 6379
arrowflight:
enable_ssl: false
auth_required: false
CREATE TABLE uk_price_paid
ENGINE = Alias(uk, uk_price_paid);
SELECT max(price), count()
FROM arrowflight('localhost:6379', 'uk_price_paid', 'default', '');
CREATE OR REPLACE TABLE dbpedia
(
id String,
title String,
text String,
vector Array(Float32) CODEC(NONE),
INDEX vector_idx vector TYPE vector_similarity('hnsw', 'L2Distance', 1536)
) ENGINE = MergeTree
ORDER BY (id);
wget https://huggingface.co/api/datasets/Qdrant/dbpedia-entities-openai3-text-embedding-3-large-1536-1M/parquet/default/train/0.parquet
INSERT INTO dbpedia
SELECT `_id` AS id, title, text,
`text-embedding-3-large-1536-embedding` AS vector
FROM file('0.parquet');
create table dbpedia2 as dbpedia;
SET exclude_materialize_skip_indexes_on_insert='vector_idx';
INSERT INTO dbpedia2
SELECT `_id` AS id, title, text,
`text-embedding-3-large-1536-embedding` AS vector
FROM file('0.parquet');
SELECT table, data_compressed_bytes, data_uncompressed_bytes, marks_bytes FROM system.data_skipping_indices
WHERE name = 'vector_idx';
ALTER TABLE dbpedia2 MATERIALIZE INDEX vector_idx
SETTINGS mutations_sync = 2;
SELECT name, table, secondary_indices_compressed_bytes, secondary_indices_uncompressed_bytes, secondary_indices_marks_bytes
FROM system.parts;
CREATE TABLE t (...)
SETTINGS materialize_skip_indexes_on_merge = false;
CREATE TABLE t (...)
SETTINGS exclude_materialize_skip_indexes_on_merge = 'vector_idx';