ClickHouse version 25.2 brings 12 new features 🐣, 15 performance optimizations 🥚, and 72 bug fixes 🌷.
This release brings improved parallel hash join performance, Parquet bloom filter writing, transitive condition inference for queries, a backup database engine, delta rust kernel integration, and more! ## New Contributors A special welcome to all the new contributors in 25.2! 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: *Artem Yurov, Gamezardashvili George, Garrett Thomas, Ivan Nesterov, Jesse Grodman, Jony Mohajan, Juan A. Pedreira, Julian Meyers, Kai Zhu, Manish Gill, Michael Anastasakis, Olli Draese, Pete Hampton, RinChanNOWWW, Sameer Tamsekar, Sante Allegrini, Sergey, Vladimir Zhirov, Yutong Xiao, heymind, jonymohajanGmail, mkalfon, ollidraese* Hint: if you’re curious how we generate this list… [here](https://gist.github.com/gingerwizard/5a9a87a39ba93b422d8640d811e269e9). ## Faster parallel hash join ### Contributed by Nikita Taranov As part of our [ongoing commitment](https://clickhouse.com/blog/clickhouse-release-24-05#cross-join-improvements) to improving join performance, each release brings [meticulous](https://clickhouse.com/docs/en/concepts/why-clickhouse-is-so-fast#meticulous-attention-to-detail) low-level optimizations. In the previous release, we optimized the **probe phase** of parallel hash joins. In this release, we revisited the **build phase** once [more](https://clickhouse.com/blog/clickhouse-release-24-12#automatic-join-reordering), eliminating unnecessary CPU thread contention for fully unthrottled performance. In our previous release post, we provided a [detailed explanation](https://clickhouse.com/blog/clickhouse-release-25-01#faster-parallel-hash-join) of how the build and probe phases of the parallel hash join algorithm. As a reminder, during the **build phase**, `N` threads concurrently insert data from the right table of the join into `N` hash tables in parallel, row-block by row-block. Once that phase is complete, the **probe phase** begins, where `N` concurrent threads process rows from the left table, performing lookups into the hash tables populated during the build phase. The value of `N` is determined by the [max_threads](https://clickhouse.com/docs/operations/settings/settings#max_threads) setting, which defaults to the number of available CPU cores on the ClickHouse server executing the join. Additionally, ClickHouse offers two settings to control the size of hash tables in hash-based joins: [max_rows_in_join](https://clickhouse.com/docs/operations/settings/query-complexity#settings-max_rows_in_join) and [max_bytes_in_join](https://clickhouse.com/docs/operations/settings/query-complexity#settings-max_bytes_in_join). By default, both are set to `0`, allowing unlimited hash table size. These limits help manage memory usage during joins, preventing excessive allocation. If a limit is reached, users can [configure](https://clickhouse.com/docs/operations/settings/query-complexity#settings-join_overflow_mode) ClickHouse to either abort with an exception (the default behavior) or return partial results based on the processed data. The following diagram refers to the [full diagram](https://clickhouse.com/uploads/25_01_02_cb04311596.png) from the [previous](https://clickhouse.com/blog/clickhouse-release-25-01#faster-parallel-hash-join) post and illustrates the parallel hash join’s **build phase** (with `max_threads` set to `4`), highlighting where these size limits are enforced to control memory usage:  Each processing thread runs a loop: ① Load the next block of rows from the input table, and ② insert its rows into the hash tables as [previously explained](https://clickhouse.com/blog/clickhouse-release-25-01#faster-parallel-hash-join). If ③ `max_rows_in_join` or `max_bytes_in_join` is set, the processing thread ④ retrieves the total row count and byte size from all hash table instances. To prevent concurrent modifications, the current processing thread [locks](https://en.wikipedia.org/wiki/Lock_(computer_science)) the mutex of each hash table sequentially, making other threads wait until the count is complete. If the row count or byte size exceeds the thresholds, the join is aborted. Until this version, the hash table size check in step ④ was always executed, even when no limit was set, causing threads to wait for each other and utilize only about 50% of CPU time. With the new check in step ③, this contention is eliminated, allowing the parallel hash join to fully utilize all CPU cores and complete faster. To better illustrate the improvement, we use a synthetic join query, which makes it easier to simulate a very large right-hand side table, focusing primarily on the build phase rather than the probe phase. Additionally, we prevent ClickHouse from automatically selecting the smaller table for the build phase by setting `query_plan_join_swap_table` to `0`:
SELECT *
FROM
numbers_mt(100_000) AS left_table
INNER JOIN
numbers_mt(1_000_000_000) AS right_table
USING number
FORMAT Null
SETTINGS query_plan_join_swap_table = 0;
Running this join query on ClickHouse 25.1 on our test machine with 32 CPU cores and 128 GB RAM, we observe via `htop` that all 32 cores are active, but none of the cores is fully utilized:
The query finishes in 12.275 seconds:
```shell
0 rows in set. Elapsed: 12.275 sec. Processed 1.00 billion rows, 8.00 GB (81.48 million rows/s., 651.80 MB/s.)
Peak memory usage: 64.25 GiB.
```
Running the same join query on ClickHouse 25.2 on the same machine shows that the parallel hash join’s processing threads fully utilize all available CPU time across all 32 cores:
Therefore the query finishes faster in 6.345 seconds:
```shell
0 rows in set. Elapsed: 6.345 sec. Processed 1.00 billion rows, 8.00 GB (157.61 million rows/s., 1.26 GB/s.)
Peak memory usage: 64.07 GiB.
```
Stay tuned for even more join performance improvements in the next release—and the ones after that!
## Writing Bloom filters for Parquet
### Contributed by Michael Kolupaev
This version sees the introduction of [Bloom filters when writing Parquet files](https://parquet.apache.org/docs/file-format/bloomfilter/). From the Parquet documentation:
> A Bloom filter is a compact data structure that overapproximates a set. It can respond to membership queries with either “definitely no” or “probably yes”, where the probability of false positives is configured when the filter is initialized. Bloom filters do not have false negatives.
Bloom filters work well when they can help filter out some row groups in the Parquet file that can’t be done by row group metadata.
Let’s see how it works using the `hits` dataset from the ClickBench benchmarking tool. We’ll first create a table using [the command in the ClickBench repository](https://github.com/ClickHouse/ClickBench/blob/main/clickhouse-cloud/create.sql).
Next, let’s download the following file:
wget --continue 'https://datasets.clickhouse.com/hits_compatible/hits.tsv.gz'
gzip -d hits.tsv.gz
INSERT INTO hits
SELECT *
FROM file('hits.tsv');
SELECT *
FROM hits
INTO OUTFILE 'hits.parquet'
SELECT *
FROM hits
INTO OUTFILE 'hits_old.parquet'
SETTINGS output_format_parquet_write_bloom_filter = 0;
du -h *.parquet
SELECT count()
FROM file('hits_old.parquet')
WHERE UserID = 8511964386843448775;
SELECT count()
FROM file('hits.parquet')
WHERE UserID = 8511964386843448775;
select uniq(UserID)
FROM hits
WHERE LocalEventTime > '2013-07-09'
AND EventDate > LocalEventTime;
storage_configuration:
disks:
backups:
type: local
path: /tmp/
backups:
allowed_disk: backups
allowed_path: /tmp/
BACKUP DATABASE default
TO File('backup1')
CREATE DATABASE backup
ENGINE = Backup('default', File('backup1'));
SHOW TABLES FROM backup;
SELECT count()
FROM backup.hits
WHERE UserID = 8511964386843448775;
CREATE TABLE t
ENGINE = DeltaLake(
s3,
filename = 'xyz/',
url = 'http://minio1:9001/test/'
)
SETTINGS allow_experimental_delta_kernel_rs = 1;
curl http://localhost:8123/ -d "SELECT CounterID, count() FROM hits GROUP BY ALL WITH TOTALS ORDER BY count() DESC LIMIT 10 FORMAT JSONCompactEachRowWithProgress"