ClickHouse Release
26.5

ClickHouse release 26.5

1. (50 min) What's new in ClickHouse 26.5.

2. (10 min) Q&A.

Release 26.5

ClickHouse "Spring" Release.

— 38 new features 🌹

— 51 performance optimizations 🦋

— 224 bug fixes 🐞

Small And Nice Features

The filesystem table function

List and analyze a directory as a queryable table:

SELECT count(), sum(length(content)), sum(countMatches(content, '\n')) FROM filesystem('src') WHERE type = 'regular' AND match(name, '\.(cpp|h)$')

filesystem() - current path in clickhouse-local, user_files in clickhouse-server,
filesystem('path/') - a relative path.

Can list all files recursively, select and search across all files content.

Demo

Developers: Ilya Perstenev, Ilya Yatsishin, Alexey Milovidov.

tokenizeQuery and highlightQuery

Tokenize and classify any SQL string from inside SQL:

SELECT tokenizeQuery('SELECT a, b FROM t WHERE x > 1'); -- [(0,6,'BareWord'),(6,7,'Whitespace'),(7,8,'BareWord'), ...] SELECT highlightQuery('SELECT a, b FROM t WHERE x > 1'); -- [(0,6,'keyword'),(7,8,'identifier'), ...]

Returns ranges with token kinds: keyword, identifier, number, ...
— Analyze various query features in system.query_log.
— Build query renderers, analyzers, IDE-style hints — all in SQL.

Bonus: you can also use ClickHouse lexer from WebAssembly!

Developer: Alexey Milovidov.

regexpPosition

Find where a regexp first matches inside a string:

SELECT regexpPosition('connection refused on port 5432', '[0-9]+') -- 28 SELECT regexpPosition('foo bar baz', 'ba\w+') -- 5

— Returns 1-based position of the first match, 0 if none.
— Complements position / positionCaseInsensitive with regexp power.

Bonus: REGEXP_INSTR - an alias for PostgreSQL compatibility.

Developer: Abhinav Agarwal.

Bare function names in higher-order functions

Skip the lambda boilerplate for one-argument calls:

-- Before: SELECT arrayMap(x -> upper(x), ['ab', 'cd']); -- Now: SELECT arrayMap(upper, ['ab', 'cd']) -- ['AB', 'CD'] SELECT arrayMap(plus, [1, 2, 3], [10, 20, 30]) -- [11, 22, 33]

Works for any function with a matching arity:
arrayMap, arrayFilter, arrayCount, arrayFirst, etc.

Developer: Alexey Milovidov.

url_base for the url table function

Set a base URL once, then use relative paths everywhere:

SET url_base = 'https://datasets.clickhouse.com/'; SELECT count() FROM url('hits_compatible/hits.csv.gz', TSV, 'URL String'); -- resolved to https://datasets.clickhouse.com/hits_compatible/hits.csv.gz

Standard URL resolution per RFC 3986:
— data.csv → base + path
— /test/data.csv → host-relative
— //other.com/x.csv → scheme-relative

Supported for the URL table engine - the resolved URL is materialized at creation.

Developer: Alexey Milovidov.

Negative LIMIT BY

Pick rows from the end of each group, not the beginning:

SELECT * FROM (SELECT number, number % 3 AS g FROM numbers(15)) ORDER BY number LIMIT -2 BY g; -- last 2 rows per group g ORDER BY number LIMIT -1 OFFSET -1 BY g; -- skip last row of each group, then take 1 from the end ORDER BY number LIMIT -2 OFFSET 1 BY g; -- skip first row, then take last 2 of the remainder

— Negative limit, negative offset, and mixed signs all work.
— "Most recent N per id" without rewriting the query with window functions.

Demo

Developer: Nihal Z. Miaji.

Multi-path SQL/JSON

Pass a Tuple or Array of paths — get a Tuple/Array of results,
with the JSON parsed only once:

SELECT JSON_VALUE('{"a":1,"b":"x","c":3.14}', ('$.a', '$.b', '$.c')); -- ('1', 'x', '3.14') as Tuple(String, String, String) SELECT JSON_VALUE('{"a":1,"b":"x"}', ['$.a', '$.missing', '$.b']); -- ['1', '', 'x'] SELECT JSON_EXISTS('{"a":1,"b":"x"}', ('$.a', ['$.b', '$.c'])); -- (1, [1, 0]) SELECT JSON_QUERY('{"a":[1,2],"b":{"x":1}}', ('$.a', '$.b')); -- ('[[1,2]]', '[{"x":1}]')

Developers: Kevinyhzou, Alexey Milovidov.

system.zookeeper_watches

A new system table showing every active ZooKeeper watch:

SELECT path, watch_type, op_num, create_time FROM system.zookeeper_watches ORDER BY create_time DESC LIMIT 10;

— Columns: zookeeper_name, path, session_id, op_num,
  watch_type (Children / Exists / Data), create_time.
— Diagnose runaway watch counts, slow Keeper sessions.

Complements system.zookeeper_connection and system.zookeeper_log.

Developer: Den Kalantaevskii.

CREATE OR REPLACE MATERIALIZED VIEW

Atomically swap a materialized view definition:

CREATE OR REPLACE MATERIALIZED VIEW mv ENGINE = MergeTree ORDER BY date AS SELECT date, count() AS c FROM events GROUP BY date;

— Replaces the SELECT and the inner target table atomically.
— Works for refreshable and regular materialized views.
— No more "drop + recreate" race windows.

Developer: DQ.

Write Kafka metadata from columns

A new Kafka engine setting kafka_map_virtual_columns_on_write:
columns with special names map to Kafka message metadata on INSERT.

CREATE TABLE kafka_sink (payload String, _key String, _timestamp DateTime, _headers Array(Tuple(name String, value String))) ENGINE = Kafka(...) SETTINGS kafka_map_virtual_columns_on_write = 1; INSERT INTO kafka_sink VALUES ('hello', 'k1', now(), ['env'], ['prod']); -- _key, _timestamp, _headers go into Kafka message metadata, -- not into the payload.

Symmetric with the existing virtual columns on read.

Developer: Alexey Milovidov.

SYSTEM PAUSE VIEW

Temporarily stop a refreshable materialized view without dropping it:

SYSTEM PAUSE VIEW daily_rollup; -- Skip refreshes, keep schedule and last state. SYSTEM START VIEW daily_rollup; -- Resume on the next scheduled tick.

— Useful during maintenance windows.
— Pause persists across server restarts.
— Pair with SYSTEM REFRESH VIEW to trigger an immediate run.

Developer: Nikita Mikhaylov.

isPrime, isProbablePrime

Primality testing in SQL:

SELECT isPrime(7), isPrime(8), isPrime(101); -- 1, 0, 1 SELECT isProbablePrime(1000000007); -- 1 (Miller-Rabin; fast for huge numbers)

— isPrime: deterministic, slower for very large values.
— isProbablePrime: probabilistic, fast for cryptographic-size numbers.

Bonus: system.primes table in 26.4.

Developers: Nihal Z. Miaji.

prettyPrintJSON

Pretty-print a JSON string from inside SQL:

SELECT prettyPrintJSON('{"a":1,"b":[1,2,3],"c":{"x":"hi"}}') FORMAT Raw; { "a": 1, "b": [1, 2, 3], "c": {"x": "hi"} }

— Useful for log inspection, debugging, and report generation.

Developer: Dmitry Prokofyev.

SQL Compatibility

STRING_AGG

— Alias for the ClickHouse groupConcat aggregate.
— Drop-in compatibility with PostgreSQL, Snowflake, BigQuery,
  and other obscure databases...

STDDEV

— Alias for stddevSamp aggregate for compatibility with PostgreSQL.
— stddevSamp — explicit name (Bessel-corrected, divides by n−1).
— stddevPop — existing population variant (divides by n).

Developer: Alexey Milovidov.

array_to_string

— Alias for the existing arrayStringConcat function.
— Compatibility with PostgreSQL and friends.

unnest

An PostgreSQL compatible alias for arrayJoin:

SELECT unnest([1, 2, 3]); -- 1, 2, 3 as separate rows SELECT unnest([1, 2, 3]), unnest(['a', 'b']); -- cross product of array elements

Developer: Alexey Milovidov.

Performance Improvements

Push ORDER BY … LIMIT through JOIN

A query plan optimization — query_plan_top_k_through_join:

SELECT * FROM big_left LEFT JOIN small_right USING (id) ORDER BY big_left.created_at DESC LIMIT 100;

If the sort key references only the preserved side, ClickHouse now applies
ORDER BY … LIMIT before the join — only the top N rows
are joined, not the whole table.

— Works for LEFT and RIGHT joins where the sorting is on the preserved side.
— Enabled by default.

Developer: Alexey Milovidov.

Speed up huge Merge table queries

Speed up huge Merge table queries

Queries over a Merge table that spans many underlying tables
used to spend a lot of CPU planning — each underlying table
was analyzed sequentially.

But if many tables are identical in the structure, we don't have to analyze them repeatedly!

A test: a 78 KB query on a Merge table over 500 tables:

26.4: 6.7 sec.
26.5: 0.25 sec.

Developer: Alexey Milovidov.

Lower memory usage for UNION ALL

A common pattern — UNION ALL across many tables (e.g., 1000)
— used to allocate buffers for every branch up front.

26.5 adds new settings with reasonable defaults:
— max_streams_for_union_step,
— max_streams_for_union_step_to_max_threads_ratio.

Queries with huge UNION ALL will keep high parallelism
but avoid running every branch at once.

Developer: Alexey Milovidov.

Memory-aware max_threads

Two new settings clamp parallelism under memory pressure:

max_threads_min_free_memory_per_thread -- default 1 GiB max_insert_threads_min_free_memory_per_thread -- default 4 GiB

— Reduces max_threads and max_insert_threads when free memory is low.
— With 32 GiB free and 1 GiB/thread - up to 32 threads.
— With 1 GiB free - falls back to 1 thread.

Big concurrent workloads no longer fail with "Memory limit exceeded"
just because the parallelism was too aggressive.

Developer: Alexey Milovidov.

Auto-tune for low-memory systems

If the server detects less than 4 GiB of memory available,
26.5 automatically dials back the defaults:

— input_format_parquet_memory_high_watermark,
— min_insert_block_size_bytes,
— zero jemalloc's dirty_decay_ms,
— ...

Run ClickHouse on a tiny VPS — without a stack of overrides.

Developer: Alexey Milovidov.

GROUP BY … LIMIT with no ORDER BY

The simplest possible distinct-keys query:

SELECT user_id FROM events GROUP BY user_id LIMIT 10;

— Aggregation now stops after collecting LIMIT + OFFSET distinct keys.
— Controlled by optimize_trivial_group_by_limit_query (on by default).

Developer: Amos Bird.

Prefetching in hash tables

Hash-based aggregations and joins probe random memory addresses —
and modern CPUs pay tens of cycles each time the cache misses.

26.5 adds precomputation of hashes and software prefetching to the hash table probes.

Developer: Amos Bird.

Faster MD5

MD5 - an obsolete hash function, that you should not use.

But often you have to use it for compatibility with external systems.

Now we have data-parallel implementation, hashing multiple values at once!

Developer: Joanna Hulboj.

JIT compilation on macOS

ClickHouse uses LLVM to JIT-compile hot expressions and aggregations.
For years, this only worked on Linux.

26.5 enables JIT on macOS too:

— compile_expressions, compile_aggregate_expressions,
  compile_sort_description — all functional.
— Same speedups you've been seeing on Linux for years.

Local development on a Mac now matches production performance characteristics.

Developer: Alexey Milovidov.

Something Interesting

Query cache for subqueries

You can control query caching on a per-subquery basis:

-- Enable query cache only for outmost query, as usual: SELECT * FROM (SELECT * FROM table) SETTINGS use_query_cache = 1; -- Enable query cache for subquery (new in 26.5): SELECT * FROM (SELECT * FROM table SETTINGS use_query_cache = 1); -- Enable propagation of query cache into all subqueries (new in 26.5): SELECT * FROM (SELECT * FROM table) SETTINGS use_query_cache_for_subqueries = 1; -- Enable propagation of query cache into all subqueries but disable in one: SELECT * FROM (SELECT * FROM table1) t1 NATURAL JOIN (SELECT * FROM table2 SETTINGS use_query_cache = 0) t2 SETTINGS use_query_cache_for_subqueries = 1;

Developer: Nikita Barannik, Vincent Voyer.

Web Terminal 🧪

An in-browser clickhouse-client:

$ cat config.d/webterminal.yaml allow_experimental_webterminal: true

Then point your browser at http://localhost:8123/webterminal:

Demo

Developer: Alexey Milovidov.

Direct SELECT from Kafka 🧪

The Keeper-backed Kafka engine now supports direct SELECT:

CREATE TABLE my_kafka (raw String) ENGINE = Kafka(...) SETTINGS kafka_keeper_path = '/clickhouse/{database}/{uuid}', kafka_replica_name = '{replica}', allow_experimental_kafka_offsets_storage_in_keeper = 1; SELECT count() FROM my_kafka; -- Reads available messages without committing offsets by default.

Control it with the kafka_commit_on_select setting.

— Inspect a topic from the SQL prompt.
— Perform INSERT SELECT manually.

Developer: Alexey Milovidov.

Data Lakes

Iceberg: geometry and geography types 🧪

The Iceberg v3 spec introduces native geometry and geography field types.
26.5 reads them — mapped to ClickHouse's Geometry (Variant of Point,
Ring, Polygon, …) type:

SET allow_experimental_geo_types_in_iceberg = 1; SELECT WKT(geom) FROM iceberg('s3://bucket/places/');

Plays nicely with existing ClickHouse geo functions.

Developer: Konstantin Vedernikov.

Query condition cache for Iceberg

The query condition cache — the ephemeral index that remembers which
granules don't satisfy a WHERE clause — now works for Iceberg tables too:

SET use_query_condition_cache = 1; SELECT count() FROM iceberg('s3://bucket/events/') WHERE event_date = today() AND user_id = 42; -- Subsequent queries with the same predicate skip irrelevant data files.

— Especially useful for repeated dashboard queries.

Developer: Konstantin Vedernikov.

Apache Paimon: incremental reads 🧪

The Paimon, PaimonS3, PaimonAzure, PaimonHDFS, PaimonLocal
table engines now support incremental reads —
return only new rows since the last committed snapshot.

Progress tracking is stored in Keeper, so consumers don't lose place
across restarts:

-- Targeted snapshot delta: SELECT * FROM paimon_table SETTINGS paimon_target_snapshot_id = 42; -- Cap snapshots consumed in one query: SELECT * FROM paimon_table SETTINGS max_consume_snapshots = 10;

— Background metadata refresh: paimon_metadata_refresh_interval_sec.
— Gated by allow_experimental_paimon_storage_engine.

Developer: XiaoBinMu.

Bonus

Meetups

— 🇺🇸 New York: AI Demo Night, May 21
— 🇯🇵 Tokyo: Findy VPoE Summit, May 22
— 🇺🇸 San Francisco: Open House, May 26
— 🌐 Virtual: AI Agents with Langfuse, May 27
— 🇪🇸 Madrid: Google Summit, May 28
— 🇮🇳 Mumbai: AWS Summit, May 28
— 🇨🇦 Toronto Meetup, Jun 2
— 🇨🇴 Bogotá Meetup, Jun 2
— 🇺🇸 SF: ClickHouse Café @ Snowflake Summit, Jun 3
— 🇯🇵 Tokyo: AI Engineering Summit, Jun 8
— 🇸🇬 SuperAI Singapore, Jun 10
— 🇧🇷 São Paulo Meetup, Jun 11
— 🇳🇱 Amsterdam: The Agentic Data Stack, Jun 18

Reading Corner 📖

https://clickhouse.com/blog/

— ClickHouse vs. Prometheus for high cardinality
— How D. E. Shaw uses ClickHouse
— Do you still need Elasticsearch for log analytics?
— What's New in pg_clickhouse
— clickhousectl v0.2
— Postgres FDW: Pushdown is a negotiation
— SQL charting and alerting in ClickStack
— Agentic analytics starts with query-ready data
— Agentic coding at ClickHouse

Q&A