ClickHouse version 24.11 contains 9 new features 🦃 15 performance optimizations ⛸️ 68 bug fixes 🏕️
In this release, parallel hash join becomes the default join strategy, `WITH FILL` gets a `STALENESS` modifier, you can pre-warm the marks cache, and vector search gets faster with the `BFloat16` data type. ## New Contributors As always, we send a special welcome to all the new contributors in 24.11\! ClickHouse's popularity is, in large part, due to the efforts of the community that contributes. Seeing that community grow is always humbling. Below are the names of the new contributors: *0xMihalich, Max Vostrikov, Payam Qorbanpour, Plasmaion, Roman Antonov, Romeo58rus, Zoe Steinkamp, kellytoole, ortyomka, qhsong, udiz, yun, Örjan Fors, Андрей* 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/release_24.11/). ## Parallel hash join is the default join strategy ## Contributed by Nikita Taranov The parallel hash join algorithm is now the default join strategy, replacing hash join. The parallel hash join algorithm is a variation of a hash join that splits the input data to build several hash tables concurrently in order to speed up the join at the expense of higher memory overhead. You can see a diagram of the algorithm's query pipeline below:  You can learn more about parallel hash join in the [ClickHouse Joins Under the Hood - Hash Join, Parallel Hash Join, Grace Hash Join](https://clickhouse.com/blog/clickhouse-fully-supports-joins-hash-joins-part2) blog post. As well as becoming the default, a performance optimization was done to the algorithm where blocks scattered between threads for parallel processing now [use zero-copy](https://github.com/ClickHouse/ClickHouse/pull/67782/files) instead of copying block columns each time. ## STALENESS Modifier For ORDER BY WITH FILL ## Contributed by Mikhail Artemenko This release introduces the `STALENESS` clause to `WITH FILL`. Let’s look at how to use it with help from the [MidJourney dataset](https://huggingface.co/datasets/vivym/midjourney-messages). Assuming we’ve downloaded the Parquet files, we can populate a table using the following queries:
CREATE TABLE images (
id String,
timestamp DateTime64,
height Int64,
width Int64,
size Int64
)
ENGINE = MergeTree
ORDER BY (size, height, width);
INSERT INTO images WITH data AS (
SELECT
assumeNotNull(timestamp) AS ts,
assumeNotNull(id) AS id,
assumeNotNull(height) AS height,
assumeNotNull(width) AS width,
assumeNotNull(size) AS size,
parseDateTime64BestEffort(ts) AS ts2
FROM file('data/0000{00..55}.parquet')
)
SELECT id, ts2 AS timestamp, height, width, size
FROM data;
SET param_start = '2023-03-24 00:24:02',
param_end = '2023-03-24 00:24:03';
We can then write this query to compute the count per 100 milliseconds using the `WITH FILL` clause to populate empty buckets with a zero value:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count, 'original' as original
FROM MidJourney.images
WHERE (timestamp >= {start: String}) AND (timestamp <= {end: String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) STEP toIntervalMillisecond(100);
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count, 'original' as original
FROM MidJourney.images
WHERE (timestamp >= {start: String}) AND (timestamp <= {end: String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
TO toDateTime64({end:String}, 3) STEP toIntervalMillisecond(100);
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count, 'original' as original
FROM MidJourney.images
WHERE (timestamp >= {start: String}) AND (timestamp <= {end: String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
TO toDateTime64({end:String}, 3) STEP toIntervalMillisecond(100)
STALENESS toIntervalMillisecond(200);
curl http://localhost:8123/?output_format_parallel_formatting=0 -d "SELECT throwIf(number > 100000) FROM system.numbers FORMAT Values"