ClickHouse Release 26.8 LTS

ClickHouse release 26.8

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

2. (10 min) Q&A.

Release 26.8 LTS

— 98 new features 🍇

— 128 performance optimizations 🌠

— 556 bug fixes 🐝

A long-term support release.

Small And Nice Features

Run a query in the background

Fire and forget: the server accepts the query, returns immediately,
and runs it to completion — whatever happens to your connection:

INSERT INTO backup_table SELECT * FROM huge_table SETTINGS run_query_in_background = 1; -- returns instantly; the INSERT keeps running on the server SELECT query_id, elapsed FROM system.processes; -- track it

— For long INSERT … SELECT, CREATE TABLE … AS SELECT,
  CREATE MATERIALIZED VIEW … POPULATE — that must continue
  with a dropped connection.
— Track by query_id in system.processes and the query log.

Developer: Miсhael Stetsyuk.

CREATE USER ... VALID FOR

Time-limited credentials, without calculating dates by hand:

CREATE USER bot IDENTIFIED BY '…' VALID FOR INTERVAL 30 DAY; SHOW CREATE USER bot; -- CREATE USER bot ... VALID UNTIL '2026-09-25 12:34:56' ALTER USER bot VALID FOR INTERVAL 90 DAY; -- extend from now

— A shorthand for VALID UNTIL: the deadline is computed
  as the current time plus the interval, at execution time.

Developer: Alexey Milovidov.

default_session_user

Which user is a connection without a user name?
It used to be hardcoded to default. Now it is your choice:

$ cat config.d/default_session_user.yaml default_session_user: anonymous # instead of 'default' protocols: mysql_guests: type: mysql port: 9106 default_session_user: mysql_guest # override per port

— The native, HTTP, MySQL, and PostgreSQL protocols now accept
  an empty user name instead of rejecting it.
— Set it to an empty string to prohibit anonymous connections.

Developer: Alexey Milovidov.

system.user_query_log

Every user can see their own query history —
without access to the full query log:

-- as user 'analyst', with no special grants: SELECT user, query FROM system.user_query_log LIMIT 1; -- analyst │ SELECT count() FROM default.hits — own queries only SELECT count() FROM system.query_log; -- ACCESS_DENIED — as before

— debug your own slow queries without asking anyone.

Developers: Yue Ni, Alexey Milovidov.

The URL database engine

Since 26.7, the url table function supports multi-protocol operation,
and dispatches on file://, s3://, etc. to the specific implementation.

In 26.8, you can create an URL database:

CREATE DATABASE web ENGINE = URL('https://example.com/data/'); USE web; SELECT count() FROM "sales/2026.parquet";

In clickhouse-local, the default database is an URL Overlay:

SELECT * FROM 'hits.tsv'; -- select from local files SELECT * FROM 'https://example.com/hits.tsv'; -- select from URLs SELECT * FROM 's3://mybucket/hits.tsv'; -- select from S3 SELECT * FROM table; -- regular tables in the default database

Developer: Alexey Milovidov.

Atomic POPULATE

CREATE MATERIALIZED VIEW … POPULATE used to skip records
inserted while the view was being filled. Not anymore:

-- 30 concurrent INSERTs racing the CREATE: CREATE MATERIALIZED VIEW mv ENGINE = SummingMergeTree ORDER BY () POPULATE AS SELECT sum(v) AS total FROM events; SELECT (SELECT sum(v) FROM events) AS source, (SELECT sum(total) FROM mv) AS materialized; -- 20300000 = 20300000 — nothing lost, nothing duplicated

— POPULATE now also works with TO — to backfill the target.
— the subscription is atomic with respect to inserts on the same local server
  (distributed atomic subscription is to do).

Developer: Alexey Milovidov.

GROUPS frame for window functions

A rarely used SQL:2011 feature:

SELECT ts, price, avg(price) OVER ( PARTITION BY symbol ORDER BY ts GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM trades;

— GROUPS means - sequences of rows with the same value of ORDER BY
— N PRECEDING = N peer groups before the current row's group,
  not N physical rows (ROWS) or an ORDER BY value distance (RANGE).

Developer: Nihal Z. Miaji.

An aggregate function to merge JSONs

Merges JSON values in the order of a sort key,
RFC 7396 style — the latest value of each path wins:

:) SELECT mergedJSONPatch(x, seq) FROM VALUES('seq UInt64, x JSON', (1, '{"database": "mongodb"}'), (2, '{"is_good": "true"}'), (3, '{"database": "ClickHouse"}')) {"database":"ClickHouse","is_good":"true"}

— The current state of an entity from a stream of partial updates.
— Works in AggregatingMergeTree — keep the state up to date on merges.

Developer: Larry Luo.

Array as an array subscript

The subscript operator accepts an array of indexes
and returns the elements at all of the given positions:

SELECT ['a', 'b', 'c', 'd', 'e'][[1, 3, 5]]; -- ['a', 'c', 'e'] SELECT scores[top_indexes] FROM results; -- same as arrayMap(i -> scores[i], top_indexes)

— Gather, reorder, or sample array elements in one expression —
  convenient together with arraySort and topK-style functions.

Developer: folly.

Interfaces

CREATE HANDLER

Define custom HTTP endpoints from SQL:

CREATE HANDLER top_pages URL '/top_pages' AS SELECT path, sum(hits) AS total FROM hits GROUP BY path ORDER BY total DESC LIMIT 5;

$ curl http://server:8123/top_pages /page19 254500 /page18 254000

— Parameterized: {p:String} is filled from URL params, form fields,
  headers, or named regexp capture groups on paths.
— INSERT handlers read the POST body.
— ALTER/DROP HANDLER, grants, system.handlers.

Developer: Alexey Milovidov.

API for query construction

New HTTP settings compose a query for you — and turn the HTTP
interface into a REST-style API over tables:

-- enable `http_allow_path_requests` in the server configuration -- and `http_allow_table_as_file` in settings profile GET /hits.tsv.gz -- requests a table, format, and compression GET /hits?format=RowBinary -- the format setting also works GET /hits.tsv?select=a,b GET /hits.tsv?query=SELECT+a,b -- enable `http_allow_database_as_path` GET /db/hits.parquet GET /db/hits.parquet?filter=x>1&order=c+DESC&limit=100&page=2 -- enable `http_allow_filters_as_unrecognized_url_parameters` GET /db/hits.parquet?x=1&y>2&sort=-c -- http_allow_filters_as_path GET /x=1/db/hits.parquet

Developer: Alexey Milovidov.

API for result modification

— new query settings: filter, select, sort, order, page, compression,
  and already existing limit, offset - for out-of-band result modification;

— and new settings format, input_format, output_format
  allow to explicitly override the input/output data format.

These settings wrap the query to alter its presentation for various tools.
They are safe and available in the readonly mode and can be restricted if needed.

— database and default_format became normal settings
  rather than being limited to the HTTP interface;

Developer: Alexey Milovidov.

Framing formats

One HTTP response stream can now carry everything: data, progress,
totals, profile events, server logs, and exceptions — multiplexed:

$ curl 'http://server:8123/?query=...&framing_output_format=EventStream' event: data data: {"number":"0"} event: progress data: {"read_rows":"3","elapsed_ns":"7496990",...}

— EventStream - HTTP Server-Sent Events, consumable
  by the browser's built-in EventSource API.
— JSONEachPacketString / JSONEachPacketBase64 - a JSON per packet.

Live progress bars and streaming results wrapping any data format.

Developer: Alexey Milovidov.

Query to JSON

The AST of a query, as JSON — in both directions:

SELECT parseQueryToJSON('SELECT number FROM numbers(10) WHERE number > 5'); {"type":"SelectWithUnionQuery", ... "where":{"type":"Function","name":"greater", "arguments":{...[{"type":"Identifier","name":"number"}, {"type":"Literal","value":{"field_type":"UInt64","value":5}}]}}...} SELECT formatQueryFromJSON(json); -- and back to SQL

— Analyze, validate, rewrite, and generate queries programmatically —
  from any language with a JSON library.
— Round-trip safe.

Developers: Alexey Milovidov, Nikita Fomichev.

clickhouse_json dialect 🧪

Skip SQL entirely: send the query as a JSON AST:

SET enable_json_ast_dialect = 1, dialect = 'clickhouse_json'; {"type":"SelectWithUnionQuery","list_of_selects":{ "type":"ExpressionList","children":[{"type":"SelectQuery", "select":{"type":"ExpressionList","children":[ {"type":"Literal","value":{"field_type":"UInt64","value":42}}]}}]}} -- 42

— For machine-generated queries: no string concatenation,
  no escaping bugs, no injection surface.
— Build the tree structurally — the server executes it.

Developers: Alexey Milovidov, Nikita Fomichev.

Pipelined SQL

Write queries top-down - each step follows the previous:

FROM hits |> WHERE status = 404 |> AGGREGATE sum(hits) AS total GROUP BY path |> ORDER BY total DESC |> LIMIT 3;

Pipes can apply to any normal query:

SELECT region_id, user_id, sum(session_length) AS total_duration FROM hits GROUP BY user_id |> AGGREGATE avg(total_duration) GROUP BY region_id |> ORDER BY 1 DESC |> LIMIT 100;

Developer: Alexey Milovidov.

New chdig features

chdig — a TUI for cluster introspection.

Developer: Azat Khuzhin.

Performance Improvements

Adaptive aggregator

There are two ways of doing GROUP BY in parallel, opposite of each other:
— Merging aggregator - aggregate arbitrary subsets of data independently in different threads in parallel, then merge in parallel.
— Splitting aggregator - distributed data across the threads based on the hash of the aggregation key, so that different threads aggregate data for different keys, and the merge phase is unneeded.

Both of these methods were implemented early in ClickHouse, before 2016.
Parallel merge with two-level hash tables from ClickHouse was later adopted by DuckDB (in 2022) and Datafusion (in 2023).

Now we have a new algorithm for parallel GROUP BY, which adaptively combines both approaches, and works well on different data distributions!

Demo.

Developer: Nihal Z. Miaji.

Fused aggregation and topK

26.7 fused GROUP BY with ORDER BY LIMIT for tables sorted by the key.
26.8 does it for any read order: a bounded heap during aggregation
prunes the groups that cannot appear in the result:

SELECT k, count() FROM events GROUP BY k ORDER BY k LIMIT 10; -- only ~10 groups are kept per thread, not 500 million

Example: 500M rows, ~500M distinct keys:

26.7: 2.32 sec, 19.9 GB of memory.
26.8: 0.22 sec, 2.3 MB of memory.

Developers: Dmitriy Terenichev, Konstantin Bogdanov.

Faster aggregation by strings

GROUP BY with a single String key uses much smaller
hash-table cells now:

SELECT user_id, count() FROM events GROUP BY user_id; -- user_id String — the most common aggregation shape there is

Example: 200M rows, 10M distinct 16-byte keys:

26.7: 1.60 sec.   26.8: 0.88 sec. (1.8x)

— On by default (enable_packed_string_keys_in_aggregation);
  the legacy method can still win for very low-cardinality
  aggregation with keys longer than 11 bytes.

Developer: Harikrishnan Prabakaran.

Faster merging of aggregation states

In a parallel GROUP BY, every thread builds its own hash table —
and the final merge could become the single-threaded bottleneck:

— Parallel single-level merge: when the per-thread tables are small,
  the key space is split by hash and merged by all threads —
  no two threads ever touch the same key.

— The single-level -> two-level conversion of per-thread states
  is parallelized too — fixing a 2–3x regression for heavy states
  (e.g. COUNT(DISTINCT …)) on machines with very many cores.

Developers: Nihal Z. Miaji.

Faster uniq / uniqExact without a key

Plain uniq, uniqExact, and uniqHLL12 over a whole table
got a batch of micro-optimizations:

— A last-value cache: consecutive equal values
  (sorted or clustered inputs) skip the hash lookup entirely.
— Better devirtualization and inlining of batch inserts.
— Memory prefetch for the uniqExact hash set.

Example: uniqExact over 500M rows with runs of equal values:

26.7: 1.82 sec.   26.8: 1.22 sec. (1.5x, single thread)

Developer: Anton Popov.

Adaptive codecs 🧪

Merges can now pick the best codec per block for columns
that use the default codec:

CREATE TABLE t (ts DateTime, id UInt64, price Float64, msg String) ENGINE = MergeTree ORDER BY ts SETTINGS allow_experimental_adaptive_codec_selection = 1; -- inspect what the merge chose, with another 26.8 feature: SELECT column, sumMap(codec_block_counts) FROM mergeTreeCodecBlockCounts(currentDatabase(), 't') GROUP BY column; -- ts: {'T64':611}, id: {'T64':1221}, price: {'LZ4':1221}, msg: {'LZ4':1832}

Good for tables with a lot of numeric data.

Developer: Raufs Dunamalijevs.

Lazy reading in Parquet

Read the ORDER BY columns first,
then read only records that survive the LIMIT for the rest:

SELECT URL, Title FROM s3('s3://clickhouse-public-datasets/hits_compatible/hits.parquet') ORDER BY EventTime DESC LIMIT 10; -- 100 million rows, 14 GB

26.7: 4.9 sec, 5.85 GB read.
26.8: 1.7 sec, 0.53 GB read (11 times less).

Developer: Alexey Milovidov.

Dictionary-based filtering in Parquet

A new way to skip whole row groups, for equality and IN conditions:

SELECT count() FROM s3('s3://clickhouse-public-datasets/hits_compatible/hits.parquet') WHERE MobilePhoneModel = 'GT-C3262'; -- 200 rows out of 100 million; the value is read from the dictionary page: -- if it is not in the dictionary, the whole row group is skipped

Example: row groups read: 226 → 12 — only the ones with this value.

— Works where min/max statistics cannot help (every row group
  spans the value) and bloom filters are absent — most Parquet
  writers, including the one that wrote hits.parquet, do not write them.
— Applies when a column chunk is fully dictionary-encoded;
  controlled by input_format_parquet_dictionary_filter_push_down.

Developer: Alexey Milovidov.

Spatial pruning for GeoParquet

Geospatial queries over GeoParquet files skip the data
that cannot match:

— Row-group pruning: row groups whose bounding box does not
  overlap the query geometry are skipped entirely.
— Page-level pruning: the covering.bbox column index
  skips irrelevant pages within the surviving row groups.
— Spatial predicate push-down also applies during row reading.

Load city-scale slices out of planet-scale files —
without reading the planet.

Developer: Vasily Chekalkin.

Lower memory usage for many parts

Every data part used to keep its own copy of schema-derived metadata:
the column list, descriptions, serializations, substreams —
all essentially identical across the parts of a table.

Now the parts of a table share one immutable, reference-counted
copy, interned per column — parts that differ only in some columns
(e.g. sparse vs dense) still share the rest.

Example (from the PR): 231 columns × 3000 small parts:

live server memory: ~840 MiB → ~240 MiB (3.5x)

— The effect is largest for wide tables with many small parts.

Developer: Raúl Marín.

Faster DISTINCT and window functions

When the partition key is a function of the DISTINCT
(or window PARTITION BY) columns, each partition is now processed
in its own stream — no cross-thread reshuffle, no final merge:

SELECT DISTINCT uid, ev FROM pt; -- PARTITION BY uid % 64 SELECT row_number() OVER (PARTITION BY uid ORDER BY ts) FROM pt;

Example: 200M rows, 64 partitions:

DISTINCT: 16.6 s, 13.3 GB → 0.49 s, 2.5 GB (34x!)
Window function: 2.2 s → 0.46 s (4.8x)

— Also applies to building sets for IN (subquery).

Developer: Nihal Z. Miaji.

JOINs

Column statistics, enabled on INSERT

Column statistics provide information about the distribution of values
and this information is used by the query optimizer
to drive optimizations, such as JOIN reordering.

23.11: first implementation of statistics with manual selection
25.9: an option for JOIN reordering with statistics
25.10: automatic choice of statistics for columns
25.12: the usage of statistics for optimization by default
26.2: efficient and compact storage format and cache for statistics
26.3: the on-disk format and the feature is GA
26.4: building statistics on background merges by default
26.8: building statistics on INSERT by default for small tables

Developers: Han Fei, Anton Popov, Rober Schulze, Alexey Milovidov, ...

Column statistics by default

29% improvement across all the benchmarks.

4.5 times improvement on TPC-H
(an old synthetic benchmark for JOINs).

IEJoin: fast inequality joins

A JOIN whose condition is two inequalities — interval overlaps,
point-in-range, as-of windows — was executed as a filtered CROSS JOIN.
Now it uses the sort-based IEJoin algorithm, by default:

SELECT count() FROM trades t JOIN quotes q ON t.time > q.window_start AND t.time < q.window_end;

Example: 200K × 200K rows:

26.7: 112 sec. (cross join + filter)
26.8: 1.06 sec. (105x faster!)

— INNER, LEFT, RIGHT, FULL, SEMI, and ANTI joins are supported.

Developer: Vladimir Cherkasov.

parallel_full_sorting_merge JOIN

A merge join that runs on all cores: the input is sharded by the hash
of the keys into independent per-shard merge joins:

SET join_algorithm = 'parallel_full_sorting_merge';

Example: 100M × 150M INNER JOIN, 32 threads:

The streaming, low-memory nature of a merge join at full speed.

Developer: Alexey Milovidov.

Cascades optimizer 🧪

A cost-based optimizer for distributed query plans:

SET make_distributed_plan = 1, enable_cascades_optimizer = 1; EXPLAIN SELECT … FROM hits h JOIN dim d ON h.path = d.path GROUP BY … └──GatherExchange └──JoinLogical (Broadcast HashJoin) │ Join: h[10000] ⋈ d[20] -- cardinality estimates ├──ReadFromMergeTree (ParallelRead default.hits) └──BuildRuntimeFilter (Build runtime join filter on path) └──ReadFromMergeTree (ReplicatedRead default.dim)

— Chooses by estimated cost: shuffle / broadcast / replicated / local joins,
  one- or two-phase aggregation, two-stage top-N, parallel reads —
  inserting exchange operators as needed.

Developer: Alexander Gololobov.

Data Lakes

S3 Tables

A catalog for AWS S3 Tables — Amazon's managed Iceberg buckets:

CREATE DATABASE tables ENGINE = DataLakeCatalog( 'https://s3tables.us-east-1.amazonaws.com/iceberg') SETTINGS catalog_type = 's3tables', region = 'us-east-1', warehouse = 'arn:aws:s3tables:us-east-1:1234567890:bucket/analytics'; SHOW TABLES FROM tables; SELECT … FROM tables.`ns.events`; INSERT INTO tables.`ns.events` VALUES (…); -- writes work now

— Reading arrived earlier; 26.8 brings a working INSERT —
  and you can create your own tables.
— Requests are SigV4-signed; IAM role support included.

Developer: Konstantin Vedernikov.

Snowflake Horizon

Query — and write — Iceberg tables behind the
Snowflake Horizon catalog:

CREATE DATABASE horizon ENGINE = DataLakeCatalog( 'https://<org>-<account>.snowflakecomputing.com/polaris/api/catalog') SETTINGS catalog_type = 'horizon', warehouse = 'ICEBERG_DB', catalog_credential = '<PAT>', vended_credentials = 1; SELECT … FROM horizon.`schema.trades`; -- reads Iceberg directly INSERT INTO horizon.`schema.trades` …; -- commits via the catalog

— ClickHouse speaks to the Snowflake-hosted Iceberg REST endpoint —
  the data files are read from object storage directly,
  without spending Snowflake compute credits.

Developer: Melvyn Peignon.

Puffin

Support for the Puffin file format — Iceberg's sidecar files
for statistics and deletion vectors:

SELECT * FROM s3('…/data/delete-0001.puffin'); ┌─referenced_data_file──────────┬─deleted_rows──────┐ │ s3://bucket/data/f1.parquet │ [4, 17, 23, 1042] │ └───────────────────────────────┴───────────────────┘

— See exactly which rows are deleted from which data file —
  the physical truth behind Iceberg v3 deletion vectors.
— Works with file, url, s3, and the other table functions;
  a metadata mode lists the blobs: types, offsets, properties.

Developer: Konstantin Vedernikov.

Prefetching manifest files in Iceberg

Query planning over Iceberg walks a chain of manifest files —
previously one at a time, paying the object-storage latency for each:

— The next manifest is now prefetched while the current one
  is parsed — I/O overlaps with CPU work.

— Delete manifests are read and decoded concurrently
  (iceberg_delete_manifest_decode_concurrency, default 4) —
  tables with many delete files start much faster.

— Also: the S3 bucket-region cache now works for data lake catalogs,
  saving a region-resolution round trip per request.

Developers: Asya Shneerson, Konstantin Vedernikov.

Table function bigquery

A table function and a table engine for Google BigQuery:

SELECT corpus, count() FROM bigquery( 'my-project', 'samples', 'shakespeare', service_account_key = '{"type": "service_account", ...}') GROUP BY corpus; CREATE TABLE bq_events ENGINE = BigQuery('my-project', 'events', 'log'); INSERT INTO bq_events VALUES (...); -- writes work, too

— The structure is inferred from the BigQuery table schema.
— Includes public datasets.
— Auth: OAuth access token, service account key (JSON),
  or an OAuth client with a refresh token.

Developer: Alexey Milovidov.

Text Search

Speaks new languages

Four new tokenizers for the tokens function and text indexes:

SELECT tokens('ClickHouse是一个快速的开源数据库', 'chinese'); -- ['ClickHouse','是','一个','快速','的','开源','数据库'] — dictionary + HMM SELECT tokens('日本語の形態素解析エンジン', 'japanese'); -- ['日本語','の','形態','素','解析','エンジン'] — the MeCab morphological analyzer SELECT tokens('こんにちは世界。データベースです。', 'icu', 'ja'); -- ['こんにちは','世界','データベース','です'] — locale-aware Unicode segmentation SELECT tokens('C++ and C# are languages', 'splitByRegexp', '[^\p{L}\p{N}#+]+'); -- ['C++','and','C#','are','languages'] — tokens other tokenizers would break

Developers: Robert Schulze, Amos Bird, Jimmy Aguilar Mena.

Text indexes: faster answers

count() is answered directly from the index cardinality metadata:

SELECT count() FROM docs WHERE hasAnyTokens(body, ['fox']); -- 1 million rows matched; rows actually read: 1

— Posting lists are now decoded lazily, at packed-block granularity,
  instead of being eagerly materialized into bitmaps.

— Optional cache for tokens missing from the index.

— The v2 on-disk format (with positions for phrase search) is the default;
  text_index_version controls it for rolling upgrades.

Developers: Elmi Ahmadov, Anton Popov, Rory Shanks.

Bonus

Animated PNG

The PNG output format learned to make animations:
add a t column — and the result becomes the frames:

WITH 480 AS W, 360 AS H, 60 AS T, 48 AS ITER, number % W AS px, intDiv(number, W) % H AS py, intDiv(number, W * H) AS t, 2 * pi() * t / T AS theta, 0.7885 * cos(theta) AS cx, 0.7885 * sin(theta) AS cy, -- c orbits the circle (px - W / 2) / (H / 2.6) AS x0, (py - H / 2) / (H / 2.6) AS y0, arrayFold((acc, i) -> if(acc.3 >= 0, acc, -- z ← z² + c if(acc.1 * acc.1 + acc.2 * acc.2 > 4.0, (acc.1, acc.2, i), (acc.1 * acc.1 - acc.2 * acc.2 + cx, 2 * acc.1 * acc.2 + cy, -1))), range(ITER), (x0, y0, -1)::Tuple(Float64, Float64, Int64)) AS res, if(res.3 < 0, ITER, res.3) / ITER AS v, sqrt(v) AS w SELECT px AS x, py AS y, t, least(1.0, 2.2 * w * w) AS r, least(1.0, greatest(0., 1.8 * w - 0.35)) AS g, least(1.0, greatest(0., w < 0.6 ? 0.4 + 1.2 * w : 2.2 - 2.4 * w)) AS b FROM numbers_mt(480 * 360 * 60) SETTINGS output_format_image_width = 480, output_format_image_height = 360 FORMAT PNG

Developer: Alexey Milovidov.

Animated PNG, in practice

One year of air traffic over Guess it International Airport — 24 million
ADS-B traces from adsb.exposed, one frame per week, colored by airline:

WITH 1280 AS W, 960 AS H, 650 AS px, -- a ~7 km viewport toUInt32(4294967295 * ((55.361 + 180) / 360)) - intDiv(W, 2) * px AS x0, toUInt32(4294967295 * (1/2 - log(tan(((25.253 + 90) / 360) * pi())) / (2 * pi()))) - intDiv(H, 2) * px AS y0, -- the frame number: one per week, 0..51 dateDiff('week', toStartOfWeek(subtractWeeks(now(), 51)), toStartOfWeek(time)) AS t, cityHash64(substring(aircraft_flight, 1, 3)) AS airline, -- color by airline code pow(least(count() / 12, 1), 0.1) AS alpha, avg(airline % 256) AS r0, avg(intDiv(airline, 256) % 256) AS g0, avg(intDiv(airline, 65536) % 256) AS b0, greatest(r0, g0, b0, 1) AS m SELECT intDiv(mercator_x - x0, px) AS x, intDiv(mercator_y - y0, px) AS y, t, r0 / m * alpha AS r, g0 / m * alpha AS g, b0 / m * alpha AS b FROM planes_mercator WHERE mercator_x >= x0 AND mercator_x < x0 + W * px AND mercator_y >= y0 AND mercator_y < y0 + H * px AND time >= toStartOfWeek(subtractWeeks(now(), 51)) AND aircraft_flight != '' GROUP BY x, y, t SETTINGS output_format_image_width = 1280, output_format_image_height = 960, output_format_image_time_divisor_seconds = 10 -- 10 frames per second FORMAT PNG

Data: adsb.exposed — © adsb.lol (ODbL v1.0), © airplanes.live, © adsbexchange.com.

On-disk storage for Keeper 🧪

ClickHouse Keeper always kept the whole coordination state in memory.
Now it can store it on disk, in a custom LSM tree:

$ cat keeper_config.d/on_disk_storage.yaml keeper_server: coordination_settings: storage_memory_only: false data_storage_path: /var/lib/keeper-data

— Similar performance to the in-memory storage.
— State no longer bounded by RAM — and it can even live on S3
  (point data_storage_disk to an s3_plain disk).

Developer: Michael Kolupaev.

PostgreSQL protocol improvements

psql now feels at home when connected to ClickHouse:

$ psql "host=… port=9005 user=pg dbname=default" default=> \dt -- lists ClickHouse tables default=> SELECT count() FROM hits WHERE path ~ 'page1[0-9]'; -- 5000

— The \d, \dt, \dv commands of psql work now.
— PostgreSQL-style regexp operators — in any query,
  over any protocol: ~, ~*, !~, !~*.
— A failed query no longer terminates the connection.
— Anonymous connections follow default_session_user.

Developer: Alexey Milovidov.

chDB in WASM

The clickhouse binary now builds for WebAssembly
(wasm64, via Emscripten) — and chDB brings it to your browser:

https://wasm.chdb.io -- a full SQL shell, no server, no cloud chdb :) CREATE TABLE t (x UInt64) ENGINE = MergeTree ORDER BY x; chdb :) INSERT INTO t SELECT number FROM numbers(1000000); chdb :) SELECT sum(x) FROM t; -- everything runs in the browser tab

— clickhouse local runs under Node.js ≥ 24 and in the browser,
  including real MergeTree tables; a CI job pins the build.
— The SQL parser also builds standalone (utils/wasm-parser) —
  it powers the syntax highlighting of the Web UI and the new Fiddle.

Developers: Auxten Wang (chDB), Alexey Milovidov (parser).

ClickBench Playground

ClickBench now has a playground: run ad-hoc SQL
against 110+ database systems, right from the browser:

benchmark.clickhouse.com/playground

— Every system comes with the 100M-row ClickBench dataset preloaded —
  and you can create tables, insert data, and run anything.
— Competition mode: select several systems and race them
  on the same query, comparing results for correctness.
— Runs on Firecracker microVMs — strong isolation, fast startup.

Demo →

Developer: Alexey Milovidov.

New ClickHouse Fiddle

The community favorite for sharing runnable SQL snippets
got a major upgrade — and moved to the ClickHouse organization:

fiddle.clickhouse.com

— A new UI built with the Click-UI design system, with syntax
  highlighting by the same WASM lexer as the Web UI.
— Run a query in any ClickHouse version — now including
  debug and sanitizer builds (great for bug reports!).
— Share a link to the query and its result.
— A hex viewer for binary output, box-drawing-capable fonts.

Demo →

Created by Igor Baliuk; continued by Nikita Mikhaylov and Alexey Milovidov.

Embeddings.info

Contains 100 million photos, 50 million comments, and the Internet
— to explore as a vector embeddings in ClickHouse!

Demo.

Developer: Alexey Milovidov.

Meetups

— 🇺🇸 San Francisco: Better Days Hackathon, Aug 28
— 🇺🇸 Boston: Data Party Trivia Night, Aug 31
— 🇩🇪 Berlin: The Agentic Data Stack, Sep 2
— 🇬🇧 London: Agentic Data Stack Breakfast, Sep 11
— 🇳🇱 Amsterdam Meetup @ Adyen, Sep 15
— 🇿🇦 Cape Town: AI Builders & Databases, Sep 15
— 🇫🇷 Paris + 🇨🇭 Zurich: The Agentic Data Stack, Sep 17
— 🇦🇺 Sydney: Build Better LLM Apps, Sep 17
— 🇸🇪 Stockholm + 🇩🇰 Copenhagen: Trainings, Sep 23–24
— 🇫🇷 Paris: AI Builders & Databases, Sep 29
— 🇬🇧 London: Query Optimization Workshop, Sep 29

Open House Roadshow

🇳🇱 Amsterdam, Sep 1  ·  🇺🇸 New York, Sep 10  ·  🇮🇳 Bangalore, Sep 22
🇬🇧 London, Sep 30  ·  🇩🇪 Munich, Oct 6

Reading Corner 📖

https://clickhouse.com/blog/

— I created a playground for 110 database systems
— ClickGap: autonomous QA for ClickHouse
— ClickHouse Labs, led by Andy Pavlo
— How Jump Trading uses ClickHouse with Iceberg
— Shopify: observability for global-scale commerce
— Mercado Libre: 50x faster trace queries
— Musinsa scaled its audience engine and cut TCO by 71%
— Sony LIV: live streaming analytics at billion-row scale
— Ensuring reliable OpenTelemetry ingestion at scale

Q&A