My Favorite ClickHouse Features
Photo by Eduard Gordeev, https://www.instagram.com/eduard_gordeev_
Author: Alexey Milovidov, ClickHouse User Council, Amsterdam, 2026.
Photo by Eduard Gordeev, https://www.instagram.com/eduard_gordeev_
From 25.8 to 26.8 — thirteen releases:
— 512 new features 🍇
— 703 performance optimizations 🌠
— 2825 bug fixes 🐝
Which one is your favorite?
The supported range grew from [1900, 2299]
to [0000-01-01, 9999-12-31]:
SELECT toDateTime64('9999-12-31 23:59:59.999', 3);
-- 9999-12-31 23:59:59.999 (was clamped to 2299-12-31)
SELECT age('year', toDateTime64('0079-08-24 13:00:00', 0), now());
-- 1946 years since the eruption of Vesuvius
SELECT toDateTime64('1789-07-14 12:00:00', 0) + INTERVAL 237 YEAR;
-- 2026-07-14 12:00:00
In 26.8, Date32 got the same range — parsing and conversions
accept it instead of silently clamping.
Uses the Proleptic Gregorian Calendar.
Developer: Alexey Milovidov.
— are subqueries that depend on the columns from the outer scope.
SELECT * FROM users AS u1 WHERE EXISTS (
SELECT * FROM users2 AS u2 WHERE u1.age = u2.age)
They can appear in many different contexts: EXISTS, IN, scalar...
In 25.4 we supported correlated subqueries inside the WHERE clause with the EXISTS operator.
In 25.5 we support scalar correlated subqueries inside the WHERE clause
and correlated subqueries inside SELECT!
Now it is enough to cover the TPC-H test suite without modifying queries.
Developer: Dmitry Novik.
CTEs (subqueries in the WITH clause) can now be evaluated only once
and stored in temporary tables:
SET enable_materialized_cte = 1;
WITH top_users AS MATERIALIZED (
SELECT user_id, count() AS cnt
FROM events GROUP BY user_id
ORDER BY cnt DESC LIMIT 1000)
SELECT * FROM top_users
INNER JOIN (SELECT user_id FROM top_users WHERE ...) ...
Developer: Dmitry Novik.
Reordering of the JOIN graph, based on the amount of data to read
and on the column-level statistics.
:) SET query_plan_optimize_join_order_limit = 10;
:) SET allow_statistics_optimize = 1;
Developer: Vladimir Cherkasov.
29% improvement across all the benchmarks.
4.5 times improvement on TPC-H
(an old synthetic benchmark for JOINs).
Since 25.12, JOIN reordering is enabled by default:
query_plan_optimize_join_order_limit = 10 -- was 1
allow_statistics_optimize = 1 -- was 0
— The optimizer reorders up to 10 tables using column-level statistics.
— In 26.4, statistics are collected by default on merges;
in 26.8, also on INSERT — so freshly loaded dimension tables
already have good estimates.
Developers: Vladimir Cherkasov, Alexander Gololobov, Nikita Taranov.
A new JOIN-order optimization algorithm —
dynamic programming over subsets:
SET query_plan_optimize_join_order_algorithm = 'dpsub';
— Considers all JOIN orders — finds the optimal plan.
— Lower optimization overhead than dpsize.
— Supports non-inner joins, unlike dpsize and dphyp.
— Algorithms can be chained: 'dpsub,greedy' — tried in order,
with fallback for queries an algorithm cannot handle.
Developer: Fisnik Kastrati.
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.
ClickHouse runs distributed queries:
— with Distributed tables;
— with Replicated tables, using "parallel replicas";
— on top of a bunch of external files, e.g., Parquet files on S3,
using the s3Cluster table function.
But what if there is only a single (or a few) large files?
Since 25.11, ClickHouse splits the work using ranges
(row groups) inside single files!
Many servers of the cluster will "eat" different portions of a file.
Developer: Konstantin Vedernikov.
25.12 introduces automatic_parallel_replicas_mode,
which collects statistics in the runtime and decides when
— a query is heavy enough and has to be run distributed;
— a query is quick or has a lot of network transfer;
Developer: Nikita Taranov.
The server can be configured with concurrent_threads_scheduler,
which gives better latency distribution on highly concurrent queries:
$ cat config.d/scheduler.yaml
concurrent_threads_scheduler: max_min_fair
Developer: Sergei Trifonov.
Starting from 26.3 LTS, async inserts are enabled by default.
— ClickHouse will batch all small inserts automatically.
— No configuration changes needed for most users.
— Reduces the number of parts created by frequent small inserts.
New: consistent mechanism of deduplication of both regular and asynchronous inserts, including inserts with materialized views.
Developer: Sema Checherinda.
Reading from data lakes is now tens of times faster on multi-core machines, when reading from a small number of files.
Applies to Iceberg, Delta Lake, Hudi, and all object storage reads.
Developer: Alexey Milovidov.
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.
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.
A new SLRU cache for Parquet metadata (footer):
— Up to 2x reduction of reads on regular queries.
— Especially beneficial for repeated queries over the same Parquet files.
SET use_parquet_metadata_cache;
Enabled by default.
Consistency is guaranteed by tracking file modifications by etag.
Developer: Grant Holly.
Iceberg tables now support asynchronous metadata prefetching:
CREATE TABLE my_iceberg (...)
ENGINE = IcebergS3(...)
SETTINGS iceberg_metadata_async_prefetch_period_ms = 60000;
SELECT ... FROM my_iceberg
SETTINGS iceberg_metadata_staleness_ms = 30000;
— Periodically pre-populates the metadata cache.
— SELECT queries use cached metadata if fresh enough,
eliminating calls to the Iceberg catalog.
Developer: Arsen Muk.
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.
SELECTs from Iceberg now use the PREWHERE optimization.
Iceberg tables support ALTER TABLE RENAME COLUMN.
INSERTs into Iceberg are production ready!
Developers: Konstantin Vedernikov, murphy-4o.
ClickHouse has database engines for:
— REST, Polaris catalog (since 24.12);
— Unity catalog (since 25.3);
— AWS Glue catalog (since 25.3);
— Hive Metastore (since 25.5);
— Microsoft Fabric OneLake (since 25.11);
— Google BigLake (since 26.2);
— AWS S3 Tables (since 26.8);
— Snowflake Horizon (since 26.8);
And table engines for Iceberg, Delta Lake, Apache Paimon, Hudi.
Developers: Konstantin Vedernikov, Melvyn Peignon, Daniil Ivanik, Arsen Muk, and others.
OneLake - Microsoft Fabric's unified data lake,
powered by the OneLake Tables APIs and Apache Iceberg.
Compatible with the Iceberg REST Catalog.
CREATE DATABASE onelake
ENGINE = DataLakeCatalog('https://onelake.table.fabric.microsoft.com/iceberg')
SETTINGS
catalog_type = 'onelake',
warehouse = 'warehouse_id/data_item_id',
onelake_tenant_id = '<tenant_id>',
oauth_server_uri = 'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token',
auth_scope = 'https://storage.azure.com/.default',
onelake_client_id = '<client_id>',
onelake_client_secret = '<client_secret>'
Developers: Konstantin Vedernikov.
CREATE DATABASE biglake
ENGINE = DataLakeCatalog(
'https://biglake.googleapis.com/iceberg/v1/restcatalog')
SETTINGS catalog_type = 'biglake',
google_adc_credentials_file =
'/home/ubuntu/.config/gcloud/application_default_credentials.json',
warehouse = 'gs://biglake-public-nyc-taxi-iceberg'
Demo
Developer: Konstantin Vedernikov.
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.
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.
A full-text search index in ClickHouse.
— In development since 2022
— first prototype in 2023 (by Harry Lee and Larry Luo)
— experimental in 25.9
— beta in 25.12
— production in 26.2
Developers: Anton Popov, Elmi Ahmadov, Jimmy Aguilar Mena.
CREATE TABLE text_log
(
message String,
...
INDEX inv_idx(message)
TYPE text(tokenizer = 'splitByNonAlpha')
GRANULARITY 128
)
ENGINE = SharedMergeTree ORDER BY id;
SELECT ... WHERE hasToken(message, 'DDLWorker');
SELECT ... WHERE hasAllTokens(message, ['peak', 'memory']);
SELECT ... WHERE hasAnyTokens(message, tokens('01442_merge_detach_attach'));
Developers: Anton Popov, Elmi Ahmadov, Jimmy Aguilar Mena.
Developers: Anton Popov, Elmi Ahmadov, Jimmy Aguilar Mena.
A new storage format, optimized for object storage.
Developers: Anton Popov.
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.
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 can now store token positions — for exact phrase search:
CREATE TABLE docs (…, INDEX idx body TYPE text(
tokenizer = 'splitByNonAlpha', support_phrase_search = 1))
… SETTINGS allow_experimental_text_index_phrase_search = 1;
SELECT count() FROM docs WHERE hasPhrase(body, 'quick brown fox');
-- 1 — but hasPhrase(body, 'brown quick fox') = 0: order matters
— The phrase test is answered directly from the index:
in our test, 124 granules → 1, and no column data was read at all.
Developer: Elmi Ahmadov.
We already had all the data types for GIS:
Point, LineString, MultiLineString, Ring, Polygon, MultiPolygon
Since 25.11, we also have one unified data type: Geometry
— it can contain any geometry!
New functions, readWkt, readWkb,
which read any type of geometry as a Geometry value.
ClickHouse also supports H3 and S2 indexes, Geohashes, optimized spherical and geo distances,
Polygonal dictionaries for reverse geocoding, SVG rendering...
Developer: Konstantin Vedernikov.
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.
Read a GeoJSON FeatureCollection — one row per feature:
SELECT id, properties.name, toTypeName(geometry)
FROM file('places.geojson', GeoJSON);
| id | properties.name | geometry |
|---|---|---|
| 1 | London | Geometry (Point) |
| 2 | square | Geometry (Polygon) |
— Columns: id String, geometry Geometry, properties Nullable(JSON).
— Point, LineString, MultiLineString, Polygon, MultiPolygon supported natively.
Developer: Mark Needham.
26.6 added GeoJSON input. 26.7 writes it, too:
SELECT id, (13.4050, 52.5200)::Point AS geometry, 'Berlin' AS name
FORMAT GeoJSON
{"type":"FeatureCollection","features":[
{"type":"Feature","id":1,
"geometry":{"type":"Point","coordinates":[13.405,52.52]},
"properties":{"name":"Berlin"}}, …]}
— The geo column → geometry, the id column → feature id,
everything else → properties.
— Feed query results straight to Leaflet, Mapbox, deck.gl, QGIS.
Developers: Mark Needham, Nihal Z. Miaji.
Geospatial predicates can now be answered by the primary key index:
CREATE TABLE points (p Point, ...) ENGINE = MergeTree ORDER BY p;
-- or: ORDER BY (lon, lat) -- a tuple of coordinates works too
SELECT count() FROM points
WHERE pointInPolygon(p, [(2.2, 48.8), (2.4, 48.8), (2.4, 49.0), (2.2, 49.0)]);
— The bounding box of the polygon is derived at index-analysis time
and turned into a range condition on the key — granules outside it
are never read.
— The same works for pointInEllipses, and for the first elements
of a tuple key.
Developer: Alexey Milovidov.
Convert between WGS84 coordinates and the UTM / MGRS systems
used in surveying, mapping, and military grids:
Map: NASA Visible Earth / Wikimedia Commons, public domain.
SELECT geoToUTM(4.89, 52.36) AS amsterdam;
-- (628700.24, 5802760.82, 31, 'U')
-- easting, northing, zone, band
SELECT UTMToGeo(628700.24, 5802760.82, 31, true);
-- (4.89, 52.36) — and back
SELECT geoToMGRS(4.89, 52.36);
-- '31UFU2870002760'
SELECT MGRSToGeo('31UFU2870002760');
-- (4.89, 52.36)
Developer: Alexey Milovidov.
Serve map tiles straight out of ClickHouse with the new MVT functions:
-- Project lon/lat into a tile's pixel space:
SELECT MVTEncodeGeom((13.37, 52.52)::Point, 10, 550, 335);
-- (124, 3384)
-- Aggregate a group's geometries into one binary tile:
SELECT MVTEncode(geom_in_tile_space) FROM ...;
-- Tile bounding box for the WHERE clause:
SELECT MVTBoundingBox(12, 1205, 2557);
— Point, line and polygon geometry.
— Also available as PostGIS aliases ST_AsMVTGeom and ST_AsMVT.
Developer: Saarthak Gupta.
You can already use projections to have different sort orders of a table:
CREATE TABLE pageviews (
CounterID UInt32,
UserID UInt64,
EventTime DateTime,
...
PROJECTION by_time (
SELECT * ORDER BY EventTime -- projection's order
)
) ORDER BY (CounterID, UserID, EventTime) -- table's main order
And the projection with accelerate range queries or queries with ORDER BY.
However, it requires duplicating the storage for the columns (or a subset).
Can we make it work without duplication?
Yes: since 25.11, a projection can store a single column, _part_offset
— so it acts as an index into the main table instead of a copy of the data.
Since 26.1, there is a dedicated syntax for exactly this:
CREATE TABLE pageviews (
CounterID UInt32,
UserID UInt64,
EventTime DateTime,
...
PROJECTION by_time INDEX EventTime TYPE basic
) ORDER BY (CounterID, UserID, EventTime)
ALTER TABLE pageviews ADD PROJECTION by_url INDEX URL TYPE basic;
Accelerates queries using much less extra storage, at the cost of worse
locality of reads: useful for point queries. Demo.
Developer: Amos Bird.
Ask "what if I had this skip index?" — without building it:
CREATE HYPOTHETICAL INDEX idx_level ON logs (level) TYPE set(2) GRANULARITY 4;
EXPLAIN WHATIF SELECT count() FROM logs WHERE level = 'error';
Baseline (PK + partition + existing indexes): marks: 612, 19.56 MiB
With idx_level (set, hypothetical):
status: applicable
marks: 200 (was 612)
est_bytes: 6.39 MiB
skip_ratio: 67.3%
Estimation: empirical, sampled 5/5 parts
— Session-scoped, visible in system.hypothetical_indexes.
Tune indexes data-driven.
Developer: Yarik Briukhovetskyi.
In 25.8: reading and analyzing the index happens before the query starts reading the data, which introduces a noticable delay when using large indices.
In 25.9: reading the indices happens together when scanning the data. Queries start faster and can finish earlier, e.g., when LIMIT is reached.
:) SET use_skip_indexes_on_data_read;
Demo
Developer: Amos Bird.
A JOIN can use the values of the right-hand side to prune granules
of the left table via its primary key or skip indexes:
SET enable_join_runtime_filters_index_analysis = 1;
SELECT … FROM facts JOIN lookup ON facts.id = lookup.id;
-- the ids from `lookup` become an index condition on `facts`
Measured — 100M-row table joined with a 100-row table:
granules read: 12216 → 100 (122x),
time: 0.177 s → 0.022 s (8x).
Developer: Shankar Iyer.
A SELECT that never ends — the first step toward streaming queries. Append STREAM and it keeps emitting new rows as they are inserted:
SET enable_streaming_queries = 1;
SELECT id, msg FROM live_events STREAM; -- blocks, keeps streaming
Applications: filtering and real-time alerting on a stream of events.
Advanced usage with cursors:
SELECT _block_number AS bn, _block_offset AS bo, id, msg
FROM events STREAM CURSOR {'all': {'block_number': 2, 'block_offset': 0}}
Developer: Mikhail Artemenko.
Call LLM endpoints (OpenAI, Anthropic) directly from SQL:
SELECT aiGenerate('Summarize this in one line: ' || review)
FROM product_reviews LIMIT 10;
Higher-level helpers built on top:
SELECT aiClassify(text, ['bug', 'feature', 'question']) FROM tickets;
SELECT aiExtract(article, 'people, organizations, dates') FROM news;
SELECT aiTranslate(message, 'English') FROM logs;
— Provider configured server-side; secrets never leak to clients.
Developer: George Larionov.
Bucketed serialization for Map columns:
CREATE TABLE tab (id UInt64, m Map(String, UInt64))
ENGINE = MergeTree ORDER BY id
SETTINGS
map_serialization_version = 'with_buckets',
max_buckets_in_map = 32;
— 2–49x faster single-key lookups depending on map size.
— Uses map_serialization_version_for_zero_level_parts = 'basic'
to keep insert speed close to baseline.
Developer: Pavel Kruglov.
MergeTree skip indices now work on the set of JSON paths
using JSONAllPaths:
ALTER TABLE events
ADD INDEX paths_idx JSONAllPaths(data)
TYPE bloom_filter GRANULARITY 1;
— Supported types: bloom_filter, tokenbf_v1, ngrambf_v1, and text (inverted).
— Skip granules where the JSON paths a query is filtering by are absent.
Developer: Pavel Kruglov.
A new function JSONAllValues returns every leaf value
of a JSON column as Array(String):
SELECT JSONAllValues(data) FROM events
-- ['42', 'click', '2026-04-30', ...]
Build a text index on it — and it kicks in automatically
for filters on JSON subcolumns:
ALTER TABLE events ADD INDEX vals
JSONAllValues(data) TYPE text(tokenizer='ngrams') GRANULARITY 1;
SELECT * FROM events WHERE data.event_type = 'click';
-- The text index is used to skip granules.
Developer: Anton Popov.
Add or modify JSON type hints instantly,
without rewriting data:
SET allow_experimental_json_lazy_type_hints = 1;
ALTER TABLE events
MODIFY COLUMN data JSON(metrics.count UInt64);
— Metadata-only operation — completes instantly.
— Type hints are applied at query time for old parts.
— Materialized during INSERTs and background merges.
Developer: tanner-bruce.
For the JSON data type:
Developer: Pavel Kruglov.
ClickHouse speaks PromQL — over a TimeSeries table:
CREATE TABLE metrics ENGINE = TimeSeries;
SELECT * FROM prometheusQuery('metrics',
'sum by (job) (rate(http_requests_total[5m]))');
-- or switch the whole session to the PromQL dialect:
SET dialect = 'promql', promql_table = 'metrics';
sum by (job) (rate(http_requests_total[5m]))
— The Prometheus remote-write and remote-read protocols,
and the Prometheus HTTP API (/api/v1/query, /api/v1/query_range)
— so Grafana can point at ClickHouse as if it were Prometheus.
— PromQL is transpiled to SQL and runs on the ClickHouse engine.
Developers: Vitaly Baranov, Nikita Mikhaylov, Valery Petrov, Minh Vu.
A year of work on making it fast and correct:
— Selectors compile into a continuous primary-key range on the samples
table instead of a huge id IN (...) set — up to 45% lower cold latency
on dashboard and alerting-rule queries over a 62-billion-sample table.
— topk / bottomk / limitk run streaming and parallel, in bounded memory.
— Shared subexpressions are evaluated once (~2x on topk over rate).
— A "recent samples" table: a TTL'd copy that short-range dashboards
and alerting rules read automatically — several times faster.
— A PromQL compliance suite runs against Prometheus itself in CI.
Developers: Nikita Mikhaylov, Vitaly Baranov, Valery Petrov, Minh Vu.
ClickHouse can now serve queries over Arrow Flight SQL
— a high-throughput, columnar gRPC protocol from Apache Arrow.
— Any ADBC client can connect — the standard Arrow database API.
Configured as a separate listener:
$ cat config.d/arrow.yaml
arrowflight_port: 9005
import pyarrow.flight as flight
client = flight.FlightClient("grpc://localhost:9005")
ticket = flight.Ticket(b"SELECT * FROM system.numbers LIMIT 10")
reader = client.do_get(ticket)
print(reader.read_all())
Developer: Yakov Olkhovskiy.
An in-browser clickhouse-client:
Point your browser at http://localhost:8123/webterminal
— an interactive client session over a WebSocket, no installation.
Experimental in 26.5; since 26.6 it is a production feature,
enabled by default:
$ cat config.d/webterminal.yaml
enable_webterminal: true -- the default; set to false to disable
Developer: Alexey Milovidov.
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.
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.
— 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.
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.
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.
22.9 — experimental version
Introduced by Arthur Filatenkov, Vladimir Makarov, Danila Mishin, Nikita Vasilenko, Alexander Piachonkin, Nikita Evsiukov, Hakob Sagatelyan.
23.8 — integration with the USearch library
Introduced by Davit Vardanyan.
25.1 — faster vector indices
By Robert Schulze, Michael Kolupaev.
25.5 — beta (prefiltering, postfiltering, rescoring)
Developer: Shankar Iyer, Robert Schulze.
25.8 — GA (index-only reading, fetch multiplier, binary quantization)
Developer: Shankar Iyer.
In development since 2021. Production-ready since 25.8.
ALTER TABLE dbpedia
ADD INDEX vector_index(vector)
TYPE vector_similarity(
'hnsw', 'cosineDistance', 1536, 'bf16', 64, 512);
ALTER TABLE dbpedia MATERIALIZE INDEX vector_index;
WITH ... AS reference_vector
SELECT ... FROM table WHERE ...
ORDER BY cosineDistance(vector, reference_vector)
LIMIT 10;
Developer: Shankar Iyer.
HNSW algorithm with quantization options (bf16, i8, b1).
Supports different filtering modes:
post-filtering (run ANN search, then apply other filters)
or pre-filtering (apply regular filters, then run ANN across filtered results).
Supports filtering multiplier
(e.g., find 100 nearest candidates to filter and return 10).
Avoids reading the data column if not necessarily.
Developer: Shankar Iyer.
A data type for vector embeddings,
that allows tuning the search precision at runtime.
CREATE TABLE vectors (
id UInt64, name String, ...
vec QBit(BFloat16, 1536)
) ORDER BY ();
SELECT id, name FROM vectors
ORDER BY L2DistanceTransposed(vector, target, 10)
LIMIT 10;
Developer: Raufs Dunamalijevs.
It uses a bit-sliced data layout:
every number is sliced by bits,
e.g., for 1536-dim vector of BFloat16,
we store 16 subcolumns with Nth (1..16th) bits from all dimensions.
At the query time, we specify, how many (most significant) bits to take.
For example, we can ask to read
only 10 out of 16 bits.
QBit now supports Int8, which allows anything from binary (1 bit) to full 8 bit quantization, chosen at runtime per query.
It can store any Int8 codes, and we also provide quantizeBFloat16ToInt8 and dequantizeInt8ToBFloat16 functions for optimal conversion from Float to Int8, minimizing the error on gaussian-distributed vector coordinates.
CREATE TABLE images (id UInt64, emb QBit(Int8, 2048)) …;
INSERT INTO images SELECT id, quantizeBFloat16ToInt8(embedding * sqrt(2048)) FROM src;
-- scale unit-normalized embeddings by sqrt(dim) and convert to Int8
SELECT id FROM images
ORDER BY cosineDistanceTransposedQuantized(emb, target, 4) LIMIT 10;
-- reads only the top 4 bit-planes — half the I/O of 8-bit
Developer: Alexey Milovidov.
The optional stride parameter slices dimensions into groups — each bit-plane of each group is its own stream on disk: QBit(Int8, 1024, 256):
| dims 1–256 | 257–512 | 513–768 | 769–1024 | |
| bit 1 (sign) | ||||
| bit 2 | ||||
| bit 3 | ||||
| bit 4 | ||||
| bit 5 … 8 |
… ORDER BY cosineDistanceTransposedQuantized(emb, target, 4, 256) …
-- 4 bits × first 256 dims: reads the 4 green streams of 32 — 3% of a Float32 column
You can request reading only first N dimensions - good for MRL (Matryoshka Representation Learning) embeddings to tune between the speed and recall.
Developer: Alexey Milovidov.
A scalar codec that compresses embedding components to 8bit and below:
SELECT quantizeBFloat16ToInt8(1.5::BFloat16);
-- 107
SELECT dequantizeInt8ToBFloat16(quantizeBFloat16ToInt8(1.5::BFloat16));
-- 1.5 (near-lossless round-trip)
— 256-level Gaussian Lloyd–Max quantizer; one byte per component.
— Int4 / Int2 / binary codes fall out by bit-truncation — trade size for recall.
— Shrinks vector indexes 4x (or more) for cheaper similarity search.
Developer: Alexey Milovidov.
To improve the quality of vector search, it makes sense to apply random rotation of N-dimensional space before quantization of vectors.
Rotation is a multiplication to an orthogonal matrix. There are many orthogonal matrices, and matrix multiplication is computationally expensive.
Thankfully, there is a family of orthogonal matrices, that are faster to multiply with - Hadamard matrices. And it is broad enough to represent pseudorandom rotations.
Now ClickHouse has a function for pseudorandom rotations:
SELECT randomHadamardTransform(vec);
Developer: Alexey Milovidov.
A building block for vector quantization pipelines:
SELECT randomHadamardTransform([1., 2., 3., 4.]::Array(Float32));
-- [0, 2, 1, -5] — an orthogonal, norm-preserving rotation
SELECT randomHadamardTransform(v, seed); -- different rotation
SELECT randomHadamardTransform(v, seed, 256); -- truncate to 256 dims
— Spreads the information evenly across dimensions,
so scalar quantization loses less — rotate, then quantize.
— Truncated form = Johnson–Lindenstrauss random projection.
— Deterministic: the same seed gives the same rotation.
Developer: Alexey Milovidov.
Store a compact quantized companion next to full-precision vectors:
CREATE TABLE vecs (id UInt32,
v Array(BFloat16) CODEC(Quantized('rabitq', 384))) …;
-- methods: int8, rabitq (1-bit), turboquant (2-bit), mrl, pq
SET vector_search_use_quantized_codes = 1;
SELECT id FROM vecs ORDER BY L2Distance(v, […]) LIMIT 10;
— Two-stage brute-force search without an index: scan the small
quantized stream, then rescore the candidates at full precision.
— Up to 8x less I/O in the first stage (1-bit codes: 16x smaller).
Developer: Shankar Iyer.
Contains 100 million photos, 50 million comments, and the Internet
— to explore as a vector embeddings in ClickHouse!
Demo.
Developer: Alexey Milovidov.
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).
In addition to SQL, chDB now implements Pandas API.
It builds a lazy pipeline from Pandas transformations,
and runs it with the ClickHouse speed!
import datastore as pd # That's it! Use pandas API as usual
Developer: Auxten.
Create user-defined functions in WebAssembly:
INSERT INTO system.webassembly_modules (name, code) SELECT 'collatz', '...';
CREATE FUNCTION collatz_steps LANGUAGE WASM
ARGUMENTS (n UInt32) RETURNS UInt32
FROM 'collatz' :: 'steps';
SELECT groupArray(collatz_steps(number::UInt32)) FROM numbers(1, 100);
— Write UDFs in any language that compiles to WASM:
Rust, C, C++, Go, Zig, ...
— Sandboxed execution with Wasmtime.
— Experimental.
Developers: Vladimir Cherkasov, Alexey Smirnov, Vasily Chekalkin.
Rust crate: clickhouse-wasm-udf:
use clickhouse_wasm_udf_bindgen::clickhouse_udf;
#[clickhouse_udf]
pub fn some_udf(data: String) -> HashMap<String, String> {
// Your implementation here
}
Control over runtime complexity:
— webassembly_udf_max_fuel
— webassembly_udf_max_memory
— webassembly_udf_max_input_block_size
— webassembly_udf_max_instances
Developers: Vladimir Cherkasov, Alexey Smirnov, Vasily Chekalkin.
A showcase:
chgeos: PostGIS-compatible spatial functions for ClickHouse,
delivered as a WebAssembly UDF module powered by GEOS 3.12+.
https://github.com/bacek/chgeos
Developer: Vasily Chekalkin.
Write a UDF in any programming language — right in SQL.
A "driver" compiles it at CREATE time into an executable UDF:
CREATE FUNCTION collatz_steps ARGUMENTS (n UInt64) RETURNS UInt64
ENGINE = GVisorC AS $$
uint64_t steps = 0;
while (n > 1) { n = (n % 2 == 0) ? n / 2 : 3 * n + 1; ++steps; }
return steps;
$$;
SELECT number, collatz_steps(number) FROM numbers(1, 10);
— The C body is compiled and runs in sandboxed Docker containers
the GVisorC, DockerC, and UnsafeC drivers are provided as examples.
— Drivers are declared by the operator; functions survive restarts.
Developers: Daniil Timižev, Alexey Milovidov.
The official ClickHouse Kubernetes operator:
https://github.com/ClickHouse/clickhouse-operator
Automated Cluster Provisioning: multi-node with sharding and replication.
ClickHouse Keeper Support: deploy and manage ClickHouse Keeper.
Vertical & Horizontal Scaling: adjust CPU / Memory resources or shards.
Configuration Management: in a single manifest change.
Seamless Upgrades: rolling updates without dropping queries.
Developer: Grigory Pervakov.
Photo by Kell Kell, 2013. CC-BY-SA-3.0.