ClickHouse version 25.6 contains 25 new features 🌸 32 performance optimizations 🦋 105 bug fixes 🐝.
This release brings a single consistent snapshot across all subqueries and table references within a query, filtering by multiple projections, JSON in Parquet, and more! ## New Contributors A special welcome to all the new contributors in 25.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: *Alexander Grueneberg, Delyan Kratunov, Denis K, Engel Danila, George Larionov, Hans Krutzer, Himanshu Pandey, Joel Höner, Kenny Sun, Krishna Mannem, Lucas Pelecq, Lucas Ricoy, Nihal Z., Nikolay Govorov, Philip Dubé, Q3Master, Roy Kim, San Tran, Sav, Shakhaev Kyamran, Tanner Bruce, abashkeev, clickhouse-gh[bot], fhw12345, haowenfeng, haoyangqian, mekpro, nihalzp, roykim98, sdairs, sinfillo* 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.6/). ## Single snapshot for SELECT ### Contributed by Amos Bird ClickHouse ensures that **SELECT** queries run on a consistent snapshot of the data. This means that for the entire duration of the query, it will see the same data, even if new rows are inserted or existing rows are updated or deleted in parallel. At the core of this is the immutability of [table data parts](https://clickhouse.com/docs/parts): * **Inserts** always create new parts; existing data is never modified in place. * **Deletes** and **updates** are eventually applied via *mutations* or *part merges*, which generate new parts with the changes applied. Because of this immutability, **SELECT queries** can safely work with a consistent snapshot of the relevant data parts at the time the query starts. The animation below illustrates this snapshot mechanism:  ① When ClickHouse receives a SELECT query `Q1` on table `t`, it identifies the relevant data parts during query planning and index analysis. ② These parts are **marked as referenced** by the query. While Q1 is running, these changes can safely occur **without affecting the result**: ③ A part is deleted
## Filtering by multiple projections
### Contributed by Amos Bird
ClickHouse supports several mechanisms to accelerate real-time analytics at scale. One particularly powerful feature is **projections**, automatically maintained, hidden tables that optimize query performance.
A projection can have a different sort order (and thus a different [primary index](https://clickhouse.com/docs/primary-indexes)) than the base table, and can even pre-aggregate data. When a query runs, ClickHouse automatically chooses the most efficient data path, whether from the base table or a projection, to minimize the amount of data read.
Let’s walk through a simple example with a base table and two projections:
CREATE TABLE page_views
(
id UInt64,
event_date Date,
user_id UInt32,
url String,
region String,
PROJECTION region_proj
(
SELECT * ORDER BY region
),
PROJECTION user_id_proj
(
SELECT * ORDER BY user_id
)
)
ENGINE = MergeTree
ORDER BY (event_date, id);
CREATE TABLE page_views
(
id UInt64,
event_date Date,
user_id UInt32,
url String,
region String,
PROJECTION region_proj
(
SELECT _part_offset ORDER BY region
),
PROJECTION user_id_proj
(
SELECT _part_offset ORDER BY user_id
)
)
ENGINE = MergeTree
ORDER BY (event_date, id);
SETTINGS
index_granularity = 1, -- one row per granule
max_bytes_to_merge_at_max_space_in_pool = 1; -- disable merge
INSERT INTO page_views VALUES (
1, '2025-07-01', 101, 'https://example.com/page1', 'europe');
INSERT INTO page_views VALUES (
2, '2025-07-01', 102, 'https://example.com/page2', 'us_west');
INSERT INTO page_views VALUES (
3, '2025-07-02', 106, 'https://example.com/page3', 'us_west');
INSERT INTO page_views VALUES (
4, '2025-07-02', 107, 'https://example.com/page4', 'us_west');
INSERT INTO page_views VALUES (
5, '2025-07-03', 104, 'https://example.com/page5', 'asia');
EXPLAIN projections=1
SELECT * FROM page_views WHERE region = 'us_west' AND user_id = 107;
Expression ((Project names + Projection))
Expression
① ReadFromMergeTree (default.page_views)
Projections:
② Name: region_proj
Description: Projection has been analyzed and is used for part-level filtering
Condition: (region in ['us_west', 'us_west'])
Search Algorithm: binary search
Parts: 3
Marks: 3
Ranges: 3
Rows: 3
Filtered Parts: 2
③ Name: user_id_proj
Description: Projection has been analyzed and is used for part-level filtering
Condition: (user_id in [107, 107])
Search Algorithm: binary search
Parts: 1
Marks: 1
Ranges: 1
Rows: 1
Filtered Parts: 2
SELECT '{"database": "ClickHouse", "is_it_good": true}'::JSON AS data
INTO OUTFILE 'test.parquet';
SELECT *
FROM file('test.parquet', ParquetMetadata)
FORMAT PrettyJSONEachRow;
{
"num_columns": "1",
"num_rows": "1",
"num_row_groups": "1",
"format_version": "2",
"metadata_size": "232",
"total_uncompressed_size": "174",
"total_compressed_size": "206",
"columns": [
{
"name": "data",
"path": "data",
"max_definition_level": "0",
"max_repetition_level": "0",
"physical_type": "BYTE_ARRAY",
"logical_type": "JSON",
"compression": "ZSTD",
"total_uncompressed_size": "174",
"total_compressed_size": "206",
"space_saved": "-18.39%",
"encodings": [
"PLAIN",
"RLE_DICTIONARY"
]
}
],
"row_groups": [
{
"file_offset": "4",
"num_columns": "1",
"num_rows": "1",
"total_uncompressed_size": "174",
"total_compressed_size": "206",
"columns": [
{
"name": "data",
"path": "data",
"total_compressed_size": "206",
"total_uncompressed_size": "174",
"have_statistics": true,
"statistics": {
"num_values": "1",
"null_count": null,
"distinct_count": null,
"min": "{\"database\":\"ClickHouse\",\"is_it_good\":true}",
"max": "{\"database\":\"ClickHouse\",\"is_it_good\":true}"
},
"bloom_filter_bytes": "47"
}
]
}
]
}
select *, * APPLY(toTypeName)
FROM file('test.parquet');
┌─data────────────────────────────────────────┬─toTypeName(data)─┐
│ {"database":"ClickHouse","is_it_good":true} │ Nullable(String) │
└─────────────────────────────────────────────┴──────────────────┘
┌─data────────────────────────────────────────┬─toTypeName(data)─┐
│ {"database":"ClickHouse","is_it_good":true} │ JSON │
└─────────────────────────────────────────────┴──────────────────┘
SET enable_time_time64_type=1;
SELECT now()::Time;
┌─CAST(now(), 'Time')─┐
│ 13:38:25 │
└─────────────────────┘
CREATE TABLE runningTimes(
time Time64(3)
)
ORDER BY time;
INSERT INTO runningTimes VALUES ('00:07:45.143') ('00:08:02.001') ('00:07:42.001');
select avg(toUInt32(time))::Time AS avg
FROM runningTimes;
┌──────avg─┐
│ 00:07:49 │
└──────────┘
DESCRIBE system.codecs;
┌─name───────────────────┬─type───┐
│ name │ String │
│ method_byte │ UInt8 │
│ is_compression │ UInt8 │
│ is_generic_compression │ UInt8 │
│ is_encryption │ UInt8 │
│ is_timeseries_codec │ UInt8 │
│ is_experimental │ UInt8 │
│ description │ String │
└────────────────────────┴────────┘
SELECT name, description
FROM system.codecs
LIMIT 3
FORMAT Vertical;
Row 1:
──────
name: GCD
description: Preprocessor. Greatest common divisor compression; divides values by a common divisor; effective for divisible integer sequences.
Row 2:
──────
name: AES_128_GCM_SIV
description: Encrypts and decrypts blocks with AES-128 in GCM-SIV mode (RFC-8452).
Row 3:
──────
name: FPC
description: High Throughput Compression of Double-Precision Floating-Point Data
DESCRIBE TABLE system.iceberg_history
┌─name────────────────┬─type────────────────────┐
│ database_name │ String │
│ table_name │ String │
│ made_current_at │ Nullable(DateTime64(6)) │
│ snapshot_id │ UInt64 │
│ parent_id │ UInt64 │
│ is_current_ancestor │ UInt8 │
└─────────────────────┴─────────────────────────┘
Thanks again to Delyan and the OpenAI team for upstreaming the fix! 🌸
## Bonus: Dig into ClickHouse with chdig ### Contributed by Azat Khuzhin Last but not least, every ClickHouse installation now comes bundled with a powerful new command-line tool for monitoring and diagnostics: **[chdig](https://github.com/azat/chdig)**. You can launch it like any other ClickHouse tool: `clickhouse-chdig`, `clickhouse chdig`, or simply `chdig`. It’s a **top-like TUI interface** designed specifically for ClickHouse, offering deep insights into how your queries and servers behave in real time. Here are just a few things it can do: * **Flamegraphs, everywhere**
## Summer bonus: CoalescingMergeTree table engine
### Contributed by Konstantin Vedernikov
This post was getting a little long, so we gave [CoalescingMergeTree](/docs/engines/table-engines/mergetree-family/coalescingmergetree) its own spotlight. It’s a new MergeTree engine that physically consolidates sparse updates during background merges, ideal for IoT state, user profiles, and more.
[Read the full summer bonus release blog post](/blog/clickhouse-25-6-coalescingmergetree).