ClickHouse Blog

Another month goes by, which means it's time for another release!

The ClickHouse 26.5 release contains 38 new features 🌹 51 performance optimizations 🦋 224 bug fixes 🐞

This release sees a record number of performance optimizations, with highlights including ORDER BY … LIMIT pushdown through joins (up to 20× faster), a new GROUP BY … LIMIT shortcut that avoids building unnecessary groups, a new filesystem table function for running SQL directly against your local file system, and more!

New contributors

A special welcome to all the new contributors in 26.5! 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:

Abhinav Agarwal, Ahaan, Alex Kuleshov, Ashrith Bandla, Asish Kumar, Callum C, Felix Bernhard, Flavio Malavazi, Ian Rakhmatullin, Ilya Perstenev, JackFielding, Joe Redfern, Larry Snizek, Luc Leray, Rahul Nair, Roy Sindre Norangshol, Venkata Vineel, Vincent Voyer, Yue, Yue Ni, functioncrafter, ibrahim karimeddin, mohaidoss, perst20, peter15914, sayondeep, zhangzhibiao, zxuhan7

Hint: if you're curious how we generate this list… here.

You can also view the slides from the presentation.

Push ORDER BY … LIMIT through JOIN

Contributed by Alexey Milovidov

"We optimize ClickHouse in every version, we optimize it more, and there is no end in optimizations" – Alexey Milovidov during the ClickHouse release 26.5 webinar

Moving more work before joins

In recent releases, ClickHouse has been steadily moving more work before joins, so less data has to pass through them. For example, ClickHouse already pushes down complex OR conditions in JOIN queries to filter each table earlier, before the join happens. It also supports runtime filters, which are created from the right-hand side of a join and applied to the left-hand side before the join runs.

This release continues that theme, but pushes down a different kind of work: not a WHERE predicate, but the ORDER BY … LIMIT clause, a pattern that appears frequently in analytical workloads.

From "join then limit" to "limit then join"

If the outermost SELECT of a LEFT JOIN query ends with ORDER BY … LIMIT, and the sort key depends only on columns from the left table, ClickHouse can push that ORDER BY … LIMIT below the join.

The same applies to RIGHT JOIN queries when the sort key depends only on columns from the right table.

For example, this query running over TPC-H tables asks for the 100 most recent orders, enriched with customer information:

SELECT
    o_orderkey,
    o_orderdate,
    o_totalprice,
    c_name,
    c_mktsegment
FROM orders
LEFT JOIN customer ON o_custkey = c_custkey
ORDER BY
    o_orderdate DESC,
    o_orderkey DESC
LIMIT 100;

Here, the ORDER BY uses only columns from orders, the preserved side of the LEFT JOIN. That means ClickHouse does not need to join every order with its customer before applying the limit.

Without the optimization, the plan is forced to do the expensive join first:

With the new optimization, ClickHouse can flip the work around: it can first find the top 100 rows from orders, and then join only those few rows with customer.

You can also see the change in the query plan obtained via EXPLAIN. With the optimization enabled, the plan contains a Limit and Sorting step on the orders table side, before the join with the customer table:

Join

  ...

    Limit

      Sorting

        ReadFromMergeTree (sf100.orders)

  ...

ReadFromMergeTree (sf100.customer)

A nice side effect is that ClickHouse already treats the pushed-down ORDER BY … LIMIT part as a first-class query pattern. As covered in our dedicated Top-N optimization post, ClickHouse has accumulated several engine-level optimizations for this pattern.

This optimization is controlled by the new query_plan_top_k_through_join setting, which is enabled by default.

Benchmark: 20× faster and 175× less memory

To evaluate the impact, we created and loaded the TPC-H schema with a scale factor of 100 on an AWS EC2 m6i.8xlarge instance with 32 vCPUs and 128 GiB of RAM.

First, we ran the query with the new ORDER BY … LIMIT pushdown disabled by setting query_plan_top_k_through_join = 0. We executed the query three times and used the fastest run as the baseline:

Elapsed: 2.153 sec. Processed 165.00 million rows, 3.23 GB (76.65 million rows/s., 1.50 GB/s.)
Peak memory usage: 1.87 GiB.

Elapsed: 1.878 sec. Processed 165.00 million rows, 3.23 GB (87.87 million rows/s., 1.72 GB/s.)
Peak memory usage: 1.88 GiB.

Elapsed: 2.197 sec. Processed 165.00 million rows, 3.23 GB (75.10 million rows/s., 1.47 GB/s.)
Peak memory usage: 1.87 GiB.

Then we ran the same query with the optimization enabled by setting query_plan_top_k_through_join = 1:

Elapsed: 0.093 sec. Processed 165.22 million rows, 2.18 GB (1.78 billion rows/s., 23.45 GB/s.)
Peak memory usage: 11.46 MiB.

Elapsed: 0.092 sec. Processed 165.22 million rows, 2.18 GB (1.80 billion rows/s., 23.70 GB/s.)
Peak memory usage: 13.72 MiB.

Elapsed: 0.092 sec. Processed 165.22 million rows, 2.18 GB (1.79 billion rows/s., 23.53 GB/s.)
Peak memory usage: 10.98 MiB.

Using the fastest run from each configuration, the difference is significant:

This benchmark already shows a 20.4× runtime improvement and around 175× lower peak memory usage.

These numbers are not a fixed ceiling. The benefit depends on the size of the input tables, the width of the joined rows, the selected columns, and the LIMIT value.

GROUP BY … LIMIT with no ORDER BY

Contributed by Amos Bird

Extending Top-N optimizations to GROUP BY

ClickHouse already treats Top-N queries as a first-class query pattern. As covered in our dedicated Top-N optimization post, ClickHouse has accumulated several engine-level optimizations for queries with ORDER BY … LIMIT, including streaming execution, read-in-order, lazy reading, and data-skipping-based Top-N pruning.

This release extends the same idea to another shape: GROUP BY … LIMIT queries without ORDER BY.

Consider a query that groups by a key and then applies LIMIT, but has no ORDER BY, no HAVING clause, and no window function. In that case, the query does not ask for the smallest keys, the largest keys, the most frequent keys, or keys in any particular order. It only asks for any N distinct grouping keys.

For example, because we already had the TPC-H dataset loaded for the previous section's benchmark, we can reuse it here. This query asks for any 100 distinct order keys from the lineitem table:

SELECT l_orderkey
FROM lineitem
GROUP BY l_orderkey
LIMIT 100;

From "group everything, then limit" to "keep only N groups"

In TPC-H scale factor 100, lineitem contains 600 million rows and 150 million distinct l_orderkey values.

Without the new optimization, ClickHouse treats the query like a regular GROUP BY: as it scans the input, every new l_orderkey creates a new entry in the aggregation hash table. Only after the aggregation result has been built does LIMIT 100 reduce the output to 100 rows.

With this release, ClickHouse recognizes this special pattern and avoids building groups that cannot affect the result. The optimization is controlled by the new optimize_trivial_group_by_limit_query setting, which is enabled by default.

For eligible queries, ClickHouse internally sets the aggregation limit to LIMIT + OFFSET and uses group_by_overflow_mode = 'any'. In practice, this means that once the aggregation hash table contains the first 100 distinct l_orderkey values, new keys are ignored instead of being added as new groups.

The scan still processes the input, but the aggregation state in main memory stays tiny: 100 groups instead of growing toward 150 million.

Benchmark: 11.9× faster and 185× less memory

To evaluate the impact, we ran the query again on an AWS EC2 m6i.8xlarge instance with 32 vCPUs and 128 GiB RAM. First, we disabled the optimization by setting optimize_trivial_group_by_limit_query = 0 and used the fastest of three runs as the baseline:

Elapsed: 0.853 sec. Processed 600.04 million rows, 2.40 GB (703.29 million rows/s., 2.81 GB/s.)
Peak memory usage: 8.60 GiB.

Elapsed: 0.806 sec. Processed 600.04 million rows, 2.40 GB (744.07 million rows/s., 2.98 GB/s.)
Peak memory usage: 8.58 GiB.

Elapsed: 0.809 sec. Processed 600.04 million rows, 2.40 GB (742.06 million rows/s., 2.97 GB/s.)
Peak memory usage: 8.57 GiB.

Then we ran the same query with the optimization enabled by setting optimize_trivial_group_by_limit_query = 1:

Elapsed: 0.069 sec. Processed 600.04 million rows, 2.40 GB (8.76 billion rows/s., 35.03 GB/s.)
Peak memory usage: 47.54 MiB.

Elapsed: 0.070 sec. Processed 600.04 million rows, 2.40 GB (8.54 billion rows/s., 34.16 GB/s.)
Peak memory usage: 47.54 MiB.

Elapsed: 0.068 sec. Processed 600.04 million rows, 2.40 GB (8.79 billion rows/s., 35.17 GB/s.)
Peak memory usage: 47.55 MiB.

Using the fastest run from each configuration:

The optimized query is 11.9× faster and uses about 185× less peak memory.

The filesystem table function

Contributed by Ilya Perstenev, Ilya Yatsishin, Alexey Milovidov

ClickHouse 25.6 also introduces the filesystem table function, which lets us list and analyze a directory as a queryable table.

The full schema exposed by filesystem covers everything you'd expect for filesystem introspection:

DESCRIBE filesystem();

┌─name──────────────┬─type───────────────────────────────────────────────┐
│ path              │ String                                             │
│ name              │ String                                             │
│ type              │ Enum8('none' = 0, 'not_found' = 1, 'regular' = 2, ⋯│
│ size              │ Nullable(UInt64)                                   │
│ depth             │ UInt16                                             │
│ modification_time │ Nullable(DateTime64(6))                            │
│ is_symlink        │ Bool                                               │
│ content           │ Nullable(String)                                   │
│ owner_read        │ Bool                                               │
│ owner_write       │ Bool                                               │
│ owner_exec        │ Bool                                               │
│ group_read        │ Bool                                               │
│ group_write       │ Bool                                               │
│ group_exec        │ Bool                                               │
│ others_read       │ Bool                                               │
│ others_write      │ Bool                                               │
│ others_exec       │ Bool                                               │
│ set_gid           │ Bool                                               │
│ set_uid           │ Bool                                               │
│ sticky_bit        │ Bool                                               │
│ file              │ String                                             │
└───────────────────┴────────────────────────────────────────────────────┘

If we call it with no arguments, using clickhouse-local, it will list files in the current directory:

SELECT path, name FROM filesystem();

┌─path──────────────────────────────────────────────┬─name──────────────────────┐
│ /Users/markhneedham/projects/release-posts/26.5   │ clickhouse                │
│ /Users/markhneedham/projects/release-posts/26.5   │ .claude                   │
└───────────────────────────────────────────────────┴───────────────────────────┘

It has access to the same parts of the file system as the user who launched ClickHouse. If you call it via ClickHouse Server, it will list the files in the user_files directory.

I have a lot of large video files on my machine, and I (or rather Claude!) usually have to run a bunch of Unix commands to find them. With this new function, it's as simple as the following query:

SELECT path, name, formatReadableSize(size), modification_time
FROM filesystem('/Users/markhneedham/projects/videos')
WHERE type = 'regular' AND name LIKE '%.braw'
ORDER BY size DESC
LIMIT 3
FORMAT Vertical;

Row 1:
──────
path:                     /Users/markhneedham/projects/videos/20260212-Sample
name:                     A001_10150625_C183 2.braw
formatReadableSize(size): 26.75 GiB
modification_time:        2025-10-15 06:25:08.529999

Row 2:
──────
path:                     /Users/markhneedham/projects/videos/20260217-AsyncInserts
name:                     A001_09290151_C176.braw
formatReadableSize(size): 21.70 GiB
modification_time:        2025-09-29 01:51:47.820000

Row 3:
──────
path:                     /Users/markhneedham/projects/videos/20260123-PGCHStack
name:                     A001_08021314_C119.braw
formatReadableSize(size): 21.54 GiB
modification_time:        2025-08-02 13:14:33.260000

And I've wrapped this query up into a skill that Claude can use to more quickly find files to delete to free up space.

url_base for the url table function

Contributed by Alexey Milovidov

If you use the url table function regularly, you've probably typed the same base URL dozens of times. The new url_base setting lets you set it once and use relative paths everywhere instead.

Working with the Amazon customer review dataset, we could set the URL base like this:

SET url_base = 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/';

We could then query the 2014 reviews like this:

SELECT
    count(),
    round(avg(star_rating), 2) AS stars,
    round(avg(helpful_votes), 2) AS votes
FROM url('amazon_reviews_2014.snappy.parquet')

┌──count()─┬─stars─┬─votes─┐
│ 44127569 │  4.23 │  0.96 │
└──────────┴───────┴───────┘

And if we want to query 2015:

SELECT
    count(),
    round(avg(star_rating), 2) AS stars,
    round(avg(helpful_votes), 2) AS votes
FROM url('amazon_reviews_2015.snappy.parquet')

┌──count()─┬─stars─┬─votes─┐
│ 41905631 │  4.25 │  0.74 │
└──────────┴───────┴───────┘

Negative LIMIT BY

Contributed by Nihal Z. Miaji

The 26.5 release also adds negative limit by, which lets us pick rows from the end of each group, rather than the beginning.

We'll use my favorite UK property prices dataset to demonstrate how it works, starting with the following query that finds the median price by district for all the counties that contain the term Yorkshire:

SELECT county, district, median(price)
FROM uk_price_paid
WHERE county ILIKE '%Yorkshire%'
GROUP BY ALL
ORDER BY median(price) DESC;

┌─county───────────────────┬─district─────────────────┬─median(price)─┐
│ NORTH YORKSHIRE          │ NORTH YORKSHIRE          │        263000 │
│ NORTH YORKSHIRE          │ HARROGATE                │        185000 │
│ NORTH YORKSHIRE          │ HAMBLETON                │        170000 │
│ NORTH YORKSHIRE          │ RYEDALE                  │        160000 │
│ NORTH YORKSHIRE          │ RICHMONDSHIRE            │        150000 │
│ NORTH YORKSHIRE          │ CRAVEN                   │        149250 │
│ NORTH YORKSHIRE          │ SELBY                    │        144995 │
│ EAST RIDING OF YORKSHIRE │ EAST RIDING OF YORKSHIRE │        132000 │
│ WEST YORKSHIRE           │ LEEDS                    │        129997 │
│ NORTH YORKSHIRE          │ SCARBOROUGH              │        120000 │
│ SOUTH YORKSHIRE          │ SHEFFIELD                │        115000 │
│ WEST YORKSHIRE           │ KIRKLEES                 │        114950 │
│ WEST YORKSHIRE           │ WAKEFIELD                │      112997.5 │
│ SOUTH YORKSHIRE          │ ROTHERHAM                │        102500 │
│ WEST YORKSHIRE           │ CALDERDALE               │        101000 │
│ WEST YORKSHIRE           │ BRADFORD                 │        100000 │
│ SOUTH YORKSHIRE          │ DONCASTER                │         98500 │
│ SOUTH YORKSHIRE          │ BARNSLEY                 │         95000 │
│ WEST YORKSHIRE           │ EAST YORKSHIRE           │         94950 │
└──────────────────────────┴──────────────────────────┴───────────────┘

We could already select the first two rows per county group, i.e., the two districts with the highest median price per county:

SELECT county, district, median(price)
FROM uk_price_paid
WHERE county ILIKE '%Yorkshire%'
GROUP BY ALL
ORDER BY median(price) DESC
LIMIT 2 BY county

┌─county───────────────────┬─district─────────────────┬─median(price)─┐
│ NORTH YORKSHIRE          │ NORTH YORKSHIRE          │        262000 │
│ NORTH YORKSHIRE          │ HARROGATE                │        185000 │
│ EAST RIDING OF YORKSHIRE │ EAST RIDING OF YORKSHIRE │      130972.5 │
│ WEST YORKSHIRE           │ LEEDS                    │        130000 │
│ WEST YORKSHIRE           │ KIRKLEES                 │        115000 │
│ SOUTH YORKSHIRE          │ SHEFFIELD                │        115000 │
│ SOUTH YORKSHIRE          │ ROTHERHAM                │        105000 │
└──────────────────────────┴──────────────────────────┴───────────────┘

But with negative limit by, we can also select the last two rows per county group, i.e., the two districts with the lowest median price per county.

SELECT county, district, median(price)
FROM uk_price_paid
WHERE county ILIKE '%Yorkshire%'
GROUP BY ALL
ORDER BY median(price) DESC
LIMIT -2 BY county;

┌─county───────────────────┬─district─────────────────┬─median(price)─┐
│ NORTH YORKSHIRE          │ SELBY                    │        145000 │
│ EAST RIDING OF YORKSHIRE │ EAST RIDING OF YORKSHIRE │        132500 │
│ NORTH YORKSHIRE          │ SCARBOROUGH              │        122000 │
│ SOUTH YORKSHIRE          │ DONCASTER                │         99000 │
│ WEST YORKSHIRE           │ BRADFORD                 │         97500 │
│ SOUTH YORKSHIRE          │ BARNSLEY                 │         94950 │
│ WEST YORKSHIRE           │ EAST YORKSHIRE           │         94950 │
└──────────────────────────┴──────────────────────────┴───────────────┘

Multi-path SQL/JSON

Contributed by Kevinyhzou, Alexey Milovidov

When using the JSON_VALUE and JSON_QUERY functions, we can now pass a tuple or array of paths and receive a tuple or array of strings, with JSON parsed only once.

We're going to work with a JSON string representing the Open House conference, printed out using the new prettyPrintJSON function:

WITH '{
  "name": "Open House 2026",
  "tagline": "The real-time database for AI conference",
  "dates": {
    "workshops": "2026-05-26",
    "conference": ["2026-05-27", "2026-05-28"]
  },
  "venue": {
    "name": "Convene 100 Stockton",
    "address": "40 O''Farrell St, San Francisco, CA 94108"
  }
}' AS conf
SELECT prettyPrintJSON(conf)FORMAT Raw;

{
    "name": "Open House 2026",
    "tagline": "The real-time database for AI conference",
    "dates": {
        "workshops": "2026-05-26",
        "conference": [
            "2026-05-27",
            "2026-05-28"
        ]
    },
    "venue": {
        "name": "Convene 100 Stockton",
        "address": "40 O'Farrell St, San Francisco, CA 94108"
    }
}

1 row in set. Elapsed: 0.003 sec.

To return strings, for example, if we want to return a tuple containing the name and venue, we use the JSON_VALUE function:

WITH '{
  "name": "Open House 2026",
  "tagline": "The real-time database for AI conference",
  "dates": {
    "workshops": "2026-05-26",
    "conference": ["2026-05-27", "2026-05-28"]
  },
  "venue": {
    "name": "Convene 100 Stockton",
    "address": "40 O''Farrell St, San Francisco, CA 94108"
  }
}' AS conf
SELECT JSON_VALUE(conf, ('$.name', '$.venue.name'));

┌─JSON_VALUE(conf, ('$.name', '$.venue.name'))─┐
│ ('Open House 2026','Convene 100 Stockton')   │
└──────────────────────────────────────────────┘

We can also pass in the JSON paths as an array rather than a tuple:

WITH '{
  "name": "Open House 2026",
  "tagline": "The real-time database for AI conference",
  "dates": {
    "workshops": "2026-05-26",
    "conference": ["2026-05-27", "2026-05-28"]
  },
  "venue": {
    "name": "Convene 100 Stockton",
    "address": "40 O''Farrell St, San Francisco, CA 94108"
  }
}' AS conf
SELECT JSON_VALUE(conf, ['$.name', '$.venue.name']);

┌─JSON_VALUE(conf, ['$.name', '$.venue.name'])─┐
│ ['Open House 2026','Convene 100 Stockton']   │
└──────────────────────────────────────────────┘

But dates.conference is an array, so if we try to retrieve that using JSON_VALUE, we'll return an empty string:

WITH '{
  "name": "Open House 2026",
  "tagline": "The real-time database for AI conference",
  "dates": {
    "workshops": "2026-05-26",
    "conference": ["2026-05-27", "2026-05-28"]
  },
  "venue": {
    "name": "Convene 100 Stockton",
    "address": "40 O''Farrell St, San Francisco, CA 94108"
  }
}' AS conf
SELECT JSON_VALUE(conf, ('$.name', '$.dates.conference'));

┌─JSON_VALUE(c⋯nference'))─┐
│ ('Open House 2026','')   │
└──────────────────────────┘

We can read the individual values from that array using zero-based array indices:

WITH '{
  "name": "Open House 2026",
  "tagline": "The real-time database for AI conference",
  "dates": {
    "workshops": "2026-05-26",
    "conference": ["2026-05-27", "2026-05-28"]
  },
  "venue": {
    "name": "Convene 100 Stockton",
    "address": "40 O''Farrell St, San Francisco, CA 94108"
  }
}' AS conf
SELECT JSON_QUERY(conf, ['$.name', '$.dates.conference[0]', '$.dates.conference[1]']);

┌─JSON_QUERY(conf, ['$.name', '$.dates.conference[0]', '$.dates.conference[1]'])─┐
│ ['Open House 2026','2026-05-27','2026-05-28']                                 │
└────────────────────────────────────────────────────────────────────────────────┘

Today we're excited to announce that executable UDFs are now available in public beta on ClickHouse Cloud. You can write a function in Python, upload it as a zip to your cluster, and call it from SQL like any built-in. ClickHouse manages a pool of long-lived sandboxed processes and routes rows through them at query speed. The function is callable anywhere SQL is: ad-hoc queries, joins, even materialized views that fire on every insert.

This isn't a brand-new idea. We've shipped executable UDFs in self-hosted ClickHouse for a while. Our 2023 post on calling OpenAI from SQL walked through the same mechanism. What's new today is that you don't need to run your own server to use it. The model code lives where the data is, runs in a managed sandbox, and the deployment surface is one upload screen in the Cloud console.

To show what this unlocks, we built a demo. A small PyTorch autoencoder scores ~6 billion equity trade ticks for anomalousness, inline with ingest. A Next.js front-end consumes the embeddings. Full source for the notebook, UDF bundle, SQL, and webapp is in this repo.

The problem this solves #

You have a trained model. You have a stream of data in ClickHouse. Getting them into the same room used to mean one of three options.

1. Stand up a separate scoring service. Now you maintain a model server, an ingest pipeline that routes rows to it, and a way to write the scores back into ClickHouse. The model is no longer near the data in any meaningful sense.

2. Translate the model into pure SQL. Workable for some tree-based models. Painful for anything with embeddings. Every retrain means regenerating thousands of lines of SQL by hand.

3. Batch score offline and join later. Loses freshness. The "anomaly" on a trade that just hit is only useful if you can react to it now.

Executable UDFs collapse all three into one. Write the inference code as a normal Python file. Point ClickHouse at it. Call it from SQL. The function runs inline with whatever query needs it, including inside a materialized view, which is exactly what we do here.

What we built #

Last year we wrote "Building StockHouse", showing how ClickHouse handles a continuous firehose of stock trade ticks in real time. That post stopped at the ingest and query layer. The natural next question is: what if you wanted to apply a learned model to every trade as it lands?

We picked an unsupervised anomaly-detection setup because it shows off the shape of the problem cleanly.

• A small autoencoder (~270K parameters) is trained on 50M historical trade ticks. Its inputs: a hashed ticker, 7 numeric features (price, size, exchange, etc.), and 6 cyclical-encoded temporal features.
• For each trade, the model produces a 32-dim embedding and a reconstruction error. High error means the model wasn't trained on patterns like this trade. It's anomalous in shape compared to what's normal for that symbol's history.
• The UDF that wraps this model is embed_trade. It's the only ML-specific piece in the system. Everything else is plain SQL: the score aggregation, the per-symbol baselines, the views.

Here's the data flow:

            ┌───────────────────────────┐
            │  default.trades           │     ← upstream feed (e.g. Polygon)
            └──────────────┬────────────┘
                           │ INSERT
                           ▼
            ┌───────────────────────────┐
            │  trades_embeddings_mv     │     ← fires on every INSERT
            │  (calls embed_trade UDF)  │
            └──────────────┬────────────┘
                           │
                           ▼
            ┌───────────────────────────┐
            │  default.trades_embeddings│     ← same trade + 32-dim
            │                           │       embedding + recon_score
            └──────────────┬────────────┘
              ▲            │
              │            │ refresh hourly
              │            ▼
              │  ┌──────────────────────┐
              │  │ trades_baselines     │     ← per-symbol score
              │  │ trades_dim_baselines │       distribution stats
              │  └──────────────────────┘
              │
              └──── consumed by webapp queries
                    (anomalies are defined relative
                     to each symbol's own baselines)

Every INSERT INTO trades flows through the materialized view, gets scored, and lands in trades_embeddings. The webapp never re-runs the model. It only reads trades_embeddings and two cheap baseline tables. The expensive inference happens exactly once per trade, inline with ingest, and every downstream query is a normal aggregation.

Training the autoencoder #

The model itself is small and unremarkable as ML goes, but the training pipeline is worth a quick look because it has to produce artifacts the UDF can load at runtime. The full walkthrough lives in notebook/train_and_deploy_udf.ipynb. A summary:

1. Stream training data into Parquet chunks. A SELECT against default.trades derives the 14 input features server-side (price, size, exchange, condition-code count, hashed ticker, and cyclical encodings of hour and day of week). The notebook pulls the result via query_arrow_stream and writes 5M-row Parquet chunks to local disk. Nothing is held in memory.

2. Fit a StandardScaler incrementally. Welford's algorithm via partial_fit gives the same mean and variance as a single scaler.fit() over the full dataset, with bounded memory. We fit on the 7 base numeric features only. The hashed ticker is an integer key and the cyclical features are already on a sensible scale.

3. Train the autoencoder. TradeAutoencoderV2 is a 4-layer encoder into a 32-dim latent, with a symmetric decoder back to the numeric feature space. The sym embedding lookup happens at the input layer, sym_idx = xxHash32(sym) % NUM_HASH_BUCKETS. Loss is MSE on the reconstructed numeric features. Training streams rows out of the Parquet chunks via an IterableDataset and stops when a 200-batch moving-average loss fails to improve for 5 windows.

4. Save two artifacts. scaler_params.pt holds mean_ and scale_ as Float32 tensors. trade_autoencoder_v2.pt holds the model state_dict plus a config dict with the constructor kwargs. The UDF's main.py reads these at startup and reconstructs the model.

5. Package the bundle. A final notebook cell zips main.py, requirements.txt, and the two .pt files into embed_trade.zip, ready to upload.

Deploying the UDF on Cloud #

The deployment surface is a single upload screen in the Cloud console. You give it a name, a zip containing your code and model files, and a few runtime parameters.

For embed_trade we use:

• Type: executable_pool. Long-lived processes, hot model in memory.
• Pool size: 10 per replica. Each process loads the 2MB model at startup (~1.5s) and reuses it for every subsequent call.
• Runtime: python3.11. Dependencies (torch==2.4.1, numpy==1.26.4) come from the requirements.txt in the zip.
• Format: TabSeparated. The UDF reads one TSV line per input row on stdin and prints (embedding, recon_score) on stdout.
• 14 arguments, each with an explicit ClickHouse type. The signature matches the autoencoder's training schema exactly. See udf/cloud-deployment.md for the full table.

The function is then callable from SQL like any built-in:

WITH
    fromUnixTimestamp64Milli(t, 'America/New_York') AS ts,
    embed_trade(
        xxHash32(sym), p, s, x, z, toUInt64(length(c)), trfi, trft,
        toUInt8(toHour(ts)), toUInt8(toDayOfWeek(ts, 1)),
        sin((toHour(ts) * 2 * pi()) / 24),
        cos((toHour(ts) * 2 * pi()) / 24),
        sin((toDayOfWeek(ts, 1) * 2 * pi()) / 7),
        cos((toDayOfWeek(ts, 1) * 2 * pi()) / 7)
    ) AS result
SELECT
    sym, i, x, p, s, c, t, q, z, trfi, trft, inserted_at,
    result.2 AS recon_score,
    result.1 AS embedding
FROM stockhouse.trades limit 10;

The interesting part isn't that you can do this. It's where you can put the call.

Scoring every trade, inline with ingest #

We wire embed_trade into a materialized view:

CREATE MATERIALIZED VIEW trades_embeddings_mv
TO trades_embeddings
AS
WITH
    fromUnixTimestamp64Milli(t, 'America/New_York') AS ts,
    embed_trade(
        xxHash32(sym), p, s, x, z, toUInt64(length(c)), trfi, trft,
        toUInt8(toHour(ts)), toUInt8(toDayOfWeek(ts, 1)),
        sin((toHour(ts) * 2 * pi()) / 24),
        cos((toHour(ts) * 2 * pi()) / 24),
        sin((toDayOfWeek(ts, 1) * 2 * pi()) / 7),
        cos((toDayOfWeek(ts, 1) * 2 * pi()) / 7)
    ) AS result
SELECT
    sym, i, x, p, s, c, t, q, z, trfi, trft, inserted_at,
    result.2 AS recon_score,
    result.1 AS embedding
FROM trades;

Every INSERT INTO trades fires this MV. The Python pool scores the batch and lands the result in trades_embeddings. There's no other mover, no other service, no separate scheduler. Just SQL.

This is the part that wasn't possible before executable UDFs landed in Cloud. The equivalent service architecture would be a Kafka consumer reading from trades, batching rows, posting to a model server, writing the results back. Same end state, several more moving parts. Here it's one DDL statement.

The performance shape is unsurprising. Cost per row is the model forward pass (a few milliseconds on a warm pool) plus the TSV serialization. ClickHouse batches rows into the UDF in chunks. The pool runs a handful of in-flight invocations in parallel. We backfilled ~6B historical rows at ~35K rows/sec sustained over several hours on a 3-replica cluster with no manual scaling. Same UDF, same MV, same SQL.

Making "anomalous" mean something #

The autoencoder gives us a raw recon_score per trade. That's a number between roughly 0.00002 and 1,000,000+ across the dataset. A naive "trades above 0.062 are anomalous" filter (using the global 99th percentile from the model's training distribution) sounds reasonable until you actually look at the data.

A handful of symbols, like BRK.A and LLY, score every single trade above that threshold because their share prices are unusually high. Their entire distribution sits in the right tail of the global one. A "100% anomalous" stat for those symbols is technically correct and practically useless.

So we redefine "anomaly" relative to each symbol's own history. For every symbol, we maintain its lifetime p95 of recon_score. A trade is anomalous for that symbol if it exceeds the symbol's own p95. About 5% of trades qualify in a typical window, by construction. When that fraction spikes well above 5%, the symbol is having a genuinely unusual window.

The per-symbol baseline lives in another ClickHouse table:

CREATE TABLE trades_baselines (
    sym         LowCardinality(String),
    p50         Float32,
    p95         Float32,
    p99         Float32,
    -- ...
    computed_at DateTime
)
ENGINE = MergeTree
ORDER BY sym;

A refreshable materialized view repopulates it every hour:

CREATE MATERIALIZED VIEW trades_baselines_mv
REFRESH EVERY 1 HOUR
TO trades_baselines
AS
SELECT
    sym,
    quantiles(0.5, 0.95, 0.99)(recon_score) AS qs,
    qs[1] AS p50, qs[2] AS p95, qs[3] AS p99,
    -- ...
FROM trades_embeddings
WHERE NOT has(c, 15) AND NOT has(c, 12)   -- exclude auction prints
GROUP BY sym;

Refreshable MVs atomically truncate and replace the target table on each refresh. Plain MergeTree is the right engine: no FINAL, no dedup logic, no read-time overhead.

The leaderboard query then joins live trades against the baselines table to count anomalies per symbol relative to their own baseline:

SELECT
    e.sym,
    countIf(e.recon_score > b.p95) AS anomaly_count,
    round(sumIf(e.s, e.recon_score > b.p95) * 100.0 / sum(e.s), 2) AS pct_of_volume
FROM stockhouse.trades_embeddings AS e
INNER JOIN stockhouse.trades_baselines AS b ON e.sym = b.sym
WHERE e.t = now() - INTERVAL 1 HOUR
GROUP BY e.sym
ORDER BY pct_of_volume DESC
LIMIT 50;

This query goes from ~1.7s (recomputing baselines inline as a CTE) to ~0.27s (joining against the pre-computed table). Same answer, roughly 6x faster. The expensive part is materialized exactly once an hour instead of on every page load.

The webapp #

The webapp is a Next.js + Click UI + Highcharts demo. It consumes trades_embeddings and the baseline tables.

The anomaly dashboard ranks S&P 500 symbols by share of trading volume that exceeds their own baseline.

The packed-bubble chart sizes and colors each symbol by pct_of_volume, the share of total trading volume in the window that came from trades above the symbol's lifetime p95. Symbols with redder, larger bubbles had unusually anomaly-heavy windows. The table on the left carries the same sort, with OHLC, max score, and the per-symbol baseline alongside.

The symbol drilldown zooms in on one ticker.

A candlestick and volume pane sits on top. Both axes overlap a single plot area, with the price axis stretched downward to push candles into the top 65% and volume bars into the bottom 30%. Hover any row in the anomalous-trades table and the corresponding candle's volume bar fills yellow, sized to that trade's share of the bucket's total volume. Crosshairs snap to the candle center.

The similarity search opens as a modal over the drilldown when you click a trade.

The radar chart plots each trade's 13 input dimensions, normalized against the symbol's lifetime min, max, and avg per dim. Because the avg always maps to 0.5, the baseline series renders as a perfect 13-sided polygon at the chart's midpoint. Easy to spot deviations from. Hover a similar-trade row to overlay it. The 50 most-similar trades come from cosineDistance(embedding, target_embedding) over the same symbol's embedding column.

The model drift monitor tracks the score distribution over time.

Weekly p50, p95, p99, and max of recon_score, with horizontal reference lines at the static thresholds the model was originally calibrated against. If the p99 line starts climbing week over week, the market has drifted from the model's training distribution and it's time to retrain.

The auction print monitor is the home for the extreme tail. Opening (c=12) and closing (c=15) auction prints score in the thousands to millions because of their massive share sizes.

They'd dominate every other view if we didn't filter them out everywhere else. Here they get their own page.

One more thing: network-access UDFs (private beta) #

Everything you've seen so far runs on the deterministic path. embed_trade scores rows at ingest, baselines refresh hourly, the webapp reads pre-computed tables. No external calls anywhere on the read path. That's the shape you want for the load-bearing pieces: cheap, predictable, no upstream that can disappear on you.

But once a trade has been flagged as anomalous, the obvious next question is why. That answer lives outside ClickHouse — in news APIs, SEC filings, halt notices, social signals. To pull those in we need network access from the UDF.

Network-access executable UDFs are in private beta on ClickHouse Cloud. Once enabled, the UDF runtime can make outbound HTTPS calls to any allowed host. We added two new UDFs in this repo to use it:

nearby_events #

Given (sym, t, window_min), calls two external sources and returns a JSON array of events near that trade time:

1. Massive News API (Polygon recently rebranded as Massive; api.polygon.io endpoints still respond as before).
2. SEC EDGAR (free, public, no API key).

SELECT
    sym,
    JSONLength(nearby_events(sym, t, 120)) AS n_events
FROM stockhouse.trades_embeddings
WHERE recon_score > 1.0
LIMIT 5;

You could almost do this with url(). The differences that make it a UDF:

• In-process composition. Polygon's results and EDGAR's filings get deduped, sorted, and capped in a single Python call. Chaining two url() calls in SQL would force the same logic into a UNION ALL with downstream arrayJoin/groupArray plumbing — workable, but uglier.
• Auth in env. The Polygon API key is read from POLYGON_API_KEY at pool-process startup. It never appears in SQL.
• Per-process LRU cache. Each pool worker keeps recent results keyed by (sym, minute, window). The same trade hovered twice in the UI costs one API call, not two.
• Connection reuse. A long-lived requests.Session() per process keeps HTTP connections alive for the duration of that worker, which is hours.

classify_trade #

Given (sym, t), fetches context via nearby_events's internals, then asks Anthropic Claude to classify the most likely cause of the anomalous trade. Returns a typed tuple:

WITH classify_trade('LLY', 1778777944818) AS c
SELECT c.1 AS cause, c.2 AS confidence, c.3 AS summary;

The cause is constrained to a fixed taxonomy: earnings, m_and_a, halt, rumor, sector_move, block_trade, no_news_found. We enforce this via Anthropic's tool-use mechanism. The model is required to call a tool whose input_schema includes an enum on the cause field, so the response is guaranteed to be parseable and the cause is guaranteed to be one of the known values. No regex parsing of free-form prose, no "the model returned something close to 'earnings' but with extra words" follow-up logic.

Remember the similarity modal from the webapp? classify_trade and nearby_events drive a "Why anomalous?" panel pinned to the top of that modal. When you open a trade, the panel hits both UDFs in parallel and shows:

• A badge with the classified cause and a confidence number
• A 1–2 sentence summary written by the model
• A short list of the news headlines and filings that drove the call

Why this matters #

url() has been in ClickHouse for years and it's good for ad-hoc fetches. What network-access UDFs add is the rest of the picture: stateful clients, auth lifecycle, multi-step pipelines, structured LLM output, and per-process caching. The boundary between "code that needs to run" and "data that needs to be queried" gets thinner.

You can put a 200-line Python function with three API calls and an LLM prompt into a SELECT. Nobody else has to learn it exists.

Want to try it on your cluster? Network-access UDFs are in private beta — reach out to ClickHouse Cloud support to get it enabled!

What's interesting about this #

Most ML-on-streaming-data architectures pay an integration tax. The model lives somewhere. The data lives somewhere else. The glue between them is its own system. The setup in this repo flattens that. There's a ClickHouse Cloud cluster, a 2MB Python file, and one DDL statement that binds them together.

Every piece of UI logic in the webapp is a SQL query. Anomaly detection is the only ML in the system, and even that's not "ML in the webapp", it's a column in a table. The "how anomalous is this symbol's last hour" calculation, the "find me similar trades by cosine distance" query, the per-symbol p95 baseline, the materialized views that keep it all fresh: standard SQL features, running against standard ClickHouse tables.

Executable UDFs in Cloud don't add new abstractions on top of ClickHouse. They give you a way to make Python part of your SQL.

Reproduce it #

The full project is at https://github.com/clickhouse/stock-anomaly-udf.

stock-anomaly-udf/
├── notebook/   # Train the autoencoder, export weights, package the UDF zip
├── udf/        # The deployable bundle: main.py, model weights, deployment notes
├── sql/        # Source schema, the auto-embed MV, two refreshable baseline MVs
└── web/        # Next.js demo app

Quickstart #

1. Get the UDF onto your cluster.

   • Zip the contents of udf/embed_trade/:

     cd udf/embed_trade && zip embed_trade.zip main.py requirements.txt *.pt

   • Upload via the Cloud UDF deployment UI. Configure per udf/cloud-deployment.md.

2. Run the SQL files in order:

   :run sql/01_source_schema.sql
   :run sql/02_embeddings_mv.sql
   :run sql/03_score_baselines.sql
   :run sql/04_dim_baselines.sql

3. Backfill historical data (optional). Bulk INSERT into trades_embeddings using the same SELECT pattern as the MV, scoped to any time range. The MV in step 2 will catch every subsequent INSERT into default.trades automatically.

4. Start the webapp:

   cd web
   cp .env.example .env.local   # fill in CH_HOST/PORT/USER/PASS/DB
   npm install
   npm run dev

   Open http://localhost:3000.

The notebook in notebook/ walks through training your own autoencoder end to end. It streams training data from default.trades into Parquet chunks, fits a StandardScaler incrementally, trains with early stopping, and zips the artifacts into a deployable bundle.

Try executable UDFs #

Public beta is live in ClickHouse Cloud today. Drop us a note if you put something interesting together with it!

If you missed Day 1, catch up on all the ClickHouse Cloud and ClickStack announcements in our Day 1 roundup blog post.

Last year was our first annual Open House conference, and we covered a lot in a single day. This year, we had so much content and announcements to make that we decided to extend by another day. Day 2 delved deeper into what the open-source community, our partners, and our customers are building on top of ClickHouse. We focused not just on the features being built, but also on why the world is betting on this data platform.

This year, ClickHouse turns 10 as an open source project. Over that decade, the ecosystem has grown to 200+ integrations built by us, by partners, and by a community that just keeps shipping. The pace has accelerated recently. MCP support and our skills framework are seeing explosive adoption, and we are watching entirely new categories of embedded and bespoke integrations emerge organically. People and agents alike are finding ways to wire ClickHouse into their stacks that we would not have thought to build ourselves.

10 years of Open Source #

The opening keynote looked back at 10 years of ClickHouse as an open-source project, including contributor growth, community milestones, and the launch of the Community Champions Program. Alexey Milovidov took the stage and walked us through his thought process in building and designing ClickHouse and what he has learned over the years in a session entitled 'How to build a great database.' It hasn't just been Alexey working on ClickHouse all this time. With over 2.6k contributors to ClickHouse and nearly 48k stars in GitHub, the support we've seen from our community over the last decade has been amazing, and we can't thank you enough for all of your contributions.

House Mates: the inaugural cohort #

To support the momentum behind the ClickHouse ecosystem, we announced House Mates, ClickHouse's official partner program. House Mates was officially launched with 25+ ISV & technology partners and 35+ services & channel partners. You can't ask for more out of our first cohort; they shipped.

The first wave of House Mates covers the integrations our customers have asked about most. On the data and transformation side, Fivetran is now generally available as a ClickHouse Cloud destination, bringing 500+ sources with schema migration support and an enterprise SLA. Sigma Computing is moving its official connector to public beta. Notion is adding first-party support for the ClickHouse MCP server for custom agents. And ClickHouse is now the first partner to build a community dbt Fusion adapter, taking our first step towards natively running on the dbt platform. More on each of these below.

Beyond those announcements, the cohort spans: Artie for sub-minute CDC replication from Postgres, MySQL, SQL Server, Oracle, and MongoDB; Dreambase as an AI-native analytics platform connecting ClickHouse and Supabase; Laravel for a full-featured ClickHouse driver with Eloquent models, Query Builder, Schema Builder, and native migration support via artisan; Odigos for zero-code OpenTelemetry tracing with ClickHouse as the analytics backend; Tavily for AI search with ClickHouse Agents as a fast retrieval backend; Hightouch for reverse ETL from ClickHouse to CRMs, ad platforms, and SaaS destinations; and DataHub as an open source data catalog with lineage and discovery for ClickHouse tables and pipelines. The list goes on.

We're humbled to have such an amazing group of partners building for tomorrow with us. During our conference, we had some exciting announcements across the ClickHouse ecosystem.

Fivetran: generally available #

The Fivetran destination connector for ClickHouse Cloud is now generally available. This is one of the most-requested integrations we have heard from customers over the past two years.

Teams can now replicate data from 500+ sources into ClickHouse without writing or maintaining pipeline code. Fivetran handles schema migrations, column additions, and retries automatically. The GA release includes schema migration support and an enterprise SLA, making this a production-ready path for teams running enterprise SaaS data into ClickHouse from Salesforce, HubSpot, Google Ads, Stripe, SAP, NetSuite, Snowflake, Databricks, and hundreds more.

"ClickHouse has been a fast-growing data destination on Fivetran, and going GA reflects how seriously both teams have invested in this partnership. Enterprise data teams run their businesses on sources such as Salesforce, Google Analytics, SAP, Databricks, Snowflake, Workday, and NetSuite. Getting that data into ClickHouse reliably, with automated schema management and an enterprise SLA, is what Fivetran was built for. We are proud to give customers a production-grade path from every major enterprise source into ClickHouse Cloud."

-- Shiva Mogili, Director of Product Management, Connectors & Extensibility

Google Cloud Dataflow: Pub/Sub template generally available

Adding to our BigQuery template, the ClickHouse template for Pub/Sub support is now in the Google Cloud Dataflow gallery.

Previously, moving Pub/Sub data into ClickHouse required custom Beam pipelines with no official support. Now teams can configure a managed pipeline to ClickHouse directly from the Dataflow console. For teams on ClickHouse Cloud, the same integration is also available as a native ClickPipes connector, so you can set it up from whichever side makes more sense for your workflow.

Learn more about how to get started using Pub/Sub on Dataflow here.

Sigma Computing: connector in public beta #

The Sigma Computing connector for ClickHouse is moving from private beta to public beta after successfully being used in production across a variety of customers. The connector is now available to any Sigma customer who wants to connect to ClickHouse.

In addition to the connector going to public beta, Sigma also selected ClickHouse as the first database target for its new ETL Cache Layer, now in private preview. When Sigma queries hit the primary warehouse at every dashboard load, teams pay full compute cost regardless of how often the data underneath is actually changing. The ETL Cache Layer pre-materializes hot datasets and serves them sub-second at any concurrency level, with intelligent routing that falls back to the source warehouse on a cache miss. This has not only major performance benefits, but cost benefits as well.

"ClickHouse is one of the fastest-growing databases in the market, and our customers are building on it because it delivers disruptive cost-performance," said Mike Palmer, CEO, Sigma. "Entering the House Mates partner program is our commitment to making Sigma the best AI runtime experience on top of ClickHouse."

dbt: ClickHouse Fusion adapter #

ClickHouse is the first partner to co-develop a dbt Fusion engine adapter with dbt Labs. Fusion is a true SQL compiler built in Rust: the engine underneath dbt that makes it faster at any project scale, gives developers real feedback before anything hits the warehouse, and generates the structured metadata that makes AI-assisted data work reliably. It's the future of dbt, and we are excited to partner with dbt Labs to bring the adapter to our joint community.

Our growing community of dbt users, combined with the dbt vision, was the catalyst for this work. As a result, the adapter is now available in alpha via the CLI. Analytics engineers can run dbt models against ClickHouse using Fusion today. This is the first milestone toward a full dbt platform (fka Cloud) integration. We're excited not only to address one of our most common integration requests but also to help shape the new adapter program at dbt Labs. As Fusion becomes the default, we're committed to delivering a first-class experience for our shared customers.

"ClickHouse has become one of the fastest-growing databases in the dbt community, and it is easy to see why. The combination of speed at scale and a genuine commitment to the open ecosystem makes it a natural fit for the composable data infrastructure the market is moving toward. Fusion is how dbt will power the platform going forward, and getting ClickHouse there early means our shared customers can build a fully open stack today. We are excited about what we are building together, and there is a lot more to come."

-- Hope Watson, Product Manager, dbt Labs

Apache Airflow: native provider #

A native Apache Airflow provider for ClickHouse will be available from the Airflow registry soon. We've seen a lot of interest in native orchestration connectors and want to thank Ivan Klimenko (klimenkoIv), Anton Bryzgalov (bryzgaloff), and the Airflow community for supporting this effort to date. Our native provider adds a ClickHouse hook and operator to the Airflow ecosystem, so teams can schedule queries, table refreshes, and data loads directly from DAGs without writing custom operator wrappers. The new provider is built on the new Apache Airflow standards, will be maintained by ClickHouse, and will work out of the box with Astronomer Astro connections once it clears the registry. We're excited to see how this project evolves and can't wait to get this out to our community.

Vercel AI SDK v7 + Langfuse #

Vercel AI SDK v7 ships with a new Telemetry system for more granular agent observability. Langfuse offers a native integration with this new telemetry system via OpenTelemetry.

AI SDK v7 is available as a canary build today and will be generally available in June.

Langfuse integrates with the AI SDK v7 with a single registerTelemetry() call, giving full hierarchical trace visibility across every tool call, sub-agent span, and LLM invocation in a production AI agent. No extra boilerplate, no custom instrumentation wiring.

Notion: Native ClickHouse connector for custom agents #

Notion is adding ClickHouse as a native connector for custom agents, so teams can query their ClickHouse data directly from inside Notion without any external tooling.

What makes this integration interesting is the architecture. There's no separate semantic layer sitting between your data and your questions. Instead, Notion pages written in plain English describe your table structures, how data is tagged, and what different fields mean. The agent reads those docs, connects to ClickHouse via MCP, and returns answers in natural language. Your existing Notion workspace becomes the context layer. ClickHouse does the querying. This will be rolling out to Notion custom agents soon.

"Every day, ClickHouse users rely on a service that handles some of the most demanding data workloads out there. But getting those insights into the tools where teams actually make decisions has historically required a lot of glue. We're thrilled that Notion custom agents can now query ClickHouse directly: our shared customers can ask questions in plain language and get real, data-rich answers back, no SQL required. That means scheduled reports, automated workflows, and live data pulled straight into docs and dashboards. ClickHouse crunches the numbers, Notion makes them actionable."

-- David Rosenberg, Ecosystem Lead, Notion

Python Client is now v1! #

clickhouse-connect v1 is now generally available on PyPI. This is a huge milestone and the first major release of the client. The headline for the initial 1.0 release is a native async client built from the ground up on top of aiohttp. This replaces the old executor-based wrapper and has full feature parity with the sync client, including streaming.

You'll also find massive speedups around performance for DateTime types, fixed-width numerics, Maps, Decimals, and BigDecimals. Along with support for Variant columns that can now be serialized client-side using their native ClickHouse types.

For projects that have numpy, pandas, pyarrow, or polars installed, cold-start time improves by 4x. That improvement comes from lazy-loading optional dependencies rather than importing everything up front.

We have introduced first-class support for the SQLAlchemy dialect, deepened support for ClickHouse-specific SQL, and paved the way for Alembic schema migrations, which ship in v1.1.0.

On the compatibility front, this release also adds compatibility for Pandas 3.x in addition to experimental support for Python 3.14free-threading, giving existing clickhouse-connect users compelling reasons to upgrade

.NET Stack: generally available on NuGet #

With the recent stable release 1.2.0, clickhouse-cs now ships a full .NET development stack with four packages on NuGet.

ClickHouse.EntityFrameworkCore adds ORM support, including JOINs, UNIONs, subqueries, DDL generation, and schema migrations. Serilog.Sinks.ClickHouse ships with column and cluster fluent configuration for structured logging into ClickHouse. ClickHouse.Aspire rounds out the stack for teams building cloud-native .NET applications.ClickHouse.SemanticKernel connects ClickHouse to the Microsoft AI orchestration ecosystem. Each package is available today for users building in the .NET ecosystem.

ClickStack announcements #

Following up on yesterday's ClickStack Cloud announcement, we also launched two major additions to ClickStack: AI Notebooks and the ClickStack MCP server. Both are built around a pretty simple idea: observability workflows shouldn't feel isolated from the way engineers actually debug systems today.

AI Notebooks, now public beta in Managed ClickStack, give teams a persistent workspace for investigations. Instead of losing context across dashboards, chats, terminals, and ad hoc queries, engineers can keep queries, charts, notes, and findings together in one place while an investigation evolves.

Most production incidents aren't linear. You follow one lead, hit a dead end, revisit an earlier assumption, compare traces against logs, then try something else. Notebooks are designed to support that kind of workflow without forcing everything into a single chat session or rigid sequence of steps.

At the same time, we're seeing more teams build their own internal agents and automation around observability data using tools like Claude Code, Cursor, and custom SDK-based systems. The ClickStack MCP server is designed for that world and built around the philosophy of "bring your own agents".

Rather than introducing yet another standalone AI interface, the MCP server exposes the same observability tools used internally by Notebooks, but makes them available to external agents. Under the hood, the MCP translates those operations into optimized ClickHouse queries, allowing agents to work with higher-level semantic endpoints instead of repeatedly rebuilding investigation logic and generating complex SQL from scratch.

Compared to the more generic ClickHouse MCP implementation, our internal testing shows the ClickStack MCP server improves efficiency, performance, and investigation accuracy. In our internal testing, investigations were completed up to 25% fewer tool calls, a 2.5x increase in consistency, and an improvement in evaluation of almost 20%.

For more details, including where we're heading with tighter integration between Notebooks and MCP-driven workflows over time, check out the main announcement post.

What this adds up to #

Ten years in, the ecosystem around ClickHouse is healthier than it has ever been and expanding rapidly. The integrations we announced today span ELT, BI, orchestration, language clients, MCP, and AI observability. They close gaps that have been on our list for a long time and open up entirely new categories of things people (and their agents) can build.

The community, our partners, and our customers are all pulling in the same direction. That is what Day 2 was about, and we can't wait to see what you build.

Session recordings will be available shortly. For everything announced on Day 1, start here: Day 1 ClickHouse Cloud recap.

TL;DR
Multi-stage distributed execution gives ClickHouse Cloud a new way to scale one query across many nodes. It repartitions intermediate data between stages, removing key bottlenecks in large joins and high-cardinality aggregations.

Early TPC-H results show up to 3.4× speedups for join-heavy queries while retaining near-linear aggregation scaling: 7.4× faster on 8 nodes than on 1 node.

Scaling one query across many nodes

ClickHouse has always been able to scale a single query across multiple nodes. In shared-nothing deployments, users do this with physical sharding and the Distributed table engine. In ClickHouse Cloud, parallel replicas brought intra-query scaling to shared storage.

These mechanisms work well for many analytical queries, but they were not the final answer for modern PB-scale workloads. They could fan out work across nodes, but they could not freely repartition intermediate results between execution stages. That limited how far ClickHouse could scale high-cardinality aggregations, and especially large joins.

Multi-stage distributed query execution is the next step. It gives ClickHouse Cloud a new way to parallelize a single query across the CPU and memory of all available nodes, without the bottlenecks of the previous execution models.

In this post, we introduce the new extension of ClickHouse's query execution model and walk through how it works. We use a multi-table join as the running example because joins are among the hardest analytical workloads to scale, but the mechanism is much broader: it is a new foundation for distributed query execution in ClickHouse Cloud.

Before we look at the new mechanics, let's review what came before and why those approaches weren't enough for modern PB-scale workloads.

Why existing distributed execution was not enough

The existing distributed execution was useful but not elastic enough for PB-scale workloads.

In shared-nothing open source deployments, ClickHouse scales a query by physically sharding data across nodes and querying those shards through a Distributed table. Each node processes its local slice, and the requester merges the results.

That works, but capacity is tied to the shard layout.

Bottleneck: capacity is tied to shard layout
Adding compute does not automatically make one query faster. Large tables must first be redistributed across more shards.

Large joins across physically sharded tables expose a second limitation. A join only works when matching rows meet on the same machine. With a distributed JOIN, each node keeps its local left side, fetches the missing right-side shards from the other nodes, builds a full right-side hash table, and returns its local join result to the requester.

GLOBAL JOIN reduces the many-to-many network round-trip by computing the right side once and broadcasting it to every node.

But the core problem remains: large right sides still have to be copied across the cluster.

Bottleneck: large right sides are copied everywhere
Distributed JOIN and GLOBAL JOIN handle network traffic in different ways, but both still make every shard join against a full right side.

ClickHouse Cloud removed the physical sharding problem by moving to shared storage. Any node can access the same table data, and parallel replicas allow multiple nodes to participate in a single query. Nodes can be added or removed instantly, with no data copying or reshuffling.

That made intra-query scaling in ClickHouse Cloud much more elastic. But parallel replicas still had a structural limitation: they could split work across replicas, but they could not freely repartition intermediate data between execution stages.

This shows up in two places.

First, joins. On a single node, ClickHouse can parallelize both sides of its default hash join strategy: it partitions rows by the join key into multiple hash tables, so both build and probe work can run across CPU cores. The same remains true inside each node when parallel replicas are used.

The limitation is one level higher. Across multiple nodes, dividing the build side itself would require a shuffle stage that repartitions both inputs by join key between nodes. Parallel replicas do not have that mechanism. The next best option is to distribute the left-side read ranges after primary-index pruning. That parallelizes probe-side work across nodes, but those ranges are not partitioned by the join key. A row in one left-side range can match rows anywhere in the right-side table, so each node still needs the full right side to build its local hash table(s) before probing its local slice.

Bottleneck: the build side does not scale out
The left-side probe is divided across nodes, but the build side is not. Every node still builds the same hash table from the full right side, so the build step is repeated instead of divided across the cluster.

Second, aggregations. Nodes can scan and aggregate locally in parallel. But without a shuffle by the GROUP BY key, ClickHouse cannot guarantee that all rows for the same GROUP BY key end up on the same node.

Bottleneck: final aggregation is still single-node
Partial groups must be merged by one coordinator. For high-cardinality GROUP BY, that final merge is bounded by one node's CPU and memory, not the cluster.

Both problems have the same root cause: there is no general way to repartition intermediate data between execution stages. That is what multi-stage distributed execution adds.

Introducing multi-stage distributed execution

Multi-stage distributed execution adds the missing primitive: it lets ClickHouse Cloud move intermediate data between nodes while a query is running.

Instead of executing a query as one distributed fan-out plus a final merge, ClickHouse splits the query plan into stages running in parallel across worker nodes. Between stages, exchange operators move the intermediate results into the shape required by the next stage.

For example, data can be shuffled by a join key so each worker receives the matching slice of both join inputs. It can be shuffled by a GROUP BY key so each worker owns complete groups. Small inputs can be broadcast to all workers. Final results can be gathered by the coordinator.

Prior bottlenecks removed: data can move between stages
Large joins no longer need every node to build the full right-side hash table. High-cardinality aggregations no longer need one coordinator to merge all partial groups.

The core abstraction is the exchange operator, a well-known building block in parallel query execution, introduced for the Volcano system and used by MPP databases like Teradata and Greenplum, as well as in SQL Server.

The exchange operator redistributes data between plan stages. Multi-stage distributed execution uses three main exchange types:

1. GatherExchange (N-to-1): sends worker output to the coordinator, usually at the top of the plan to produce the final result.

2. ShuffleExchange (M-to-N): repartitions rows by a key, such as a join key or GROUP BY key. This is what lets each worker own a complete, disjoint slice of the next operation.

3. BroadcastExchange (1-to-N): copies a small input to every worker, useful when one side of a join is small enough to replicate cheaply.

There is also ScatterExchange, which spreads rows randomly amongst workers.

Those are the mechanics in the abstract. The easiest way to see why they matter is to follow one query through the stages.

How one analytical join query avoids the old bottlenecks

Let's make this concrete with a TPC-H-like query that hits both bottlenecks from the previous section: a large join side that should not be copied to every worker node, and an aggregation that should not collapse into a single-node final merge.

The query computes total shipment revenue per nation: it joins lineitem to supplier, joins the result to the small nation table, groups by n_name, and sorts by revenue.

SELECT n_name, sum(l_extendedprice) AS revenue
FROM lineitem
JOIN supplier ON l_suppkey = s_suppkey
JOIN nation ON s_nationkey = n_nationkey
WHERE l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01'
GROUP BY n_name
ORDER BY revenue DESC;

The distributed plan (inspected via EXPLAIN) contains one BroadcastExchange, two ShuffleExchanges, and one GatherExchange:

┌─explain──────────────────────────────────────────────────┐
│ Output: n_name, sum(l_extendedprice)                     │
│                                                          │
│ GatherExchange (sorted by (sum(l_extendedprice) DESC))   │
│ └──Sorting (Sorting for ORDER BY)                        │
│    └──Aggregating                                        │
│       └──ShuffleExchange (by hash([n_name]))             │
│          └──JoinLogical                                  │
│             ├──ShuffleExchange (by hash([l_suppkey]))    │
│             │  └──ReadFromMergeTree (sf100.lineitem)     │
│             └──ShuffleExchange (by hash([s_suppkey]))    │
│                └──JoinLogical                            │
│                   ├──ReadFromMergeTree (sf100.supplier)  │
│                   └──BroadcastExchange                   │
│                      └──ReadFromMergeTree (sf100.nation) │
└──────────────────────────────────────────────────────────┘

Read from the bottom up, the plan first builds the small supplier ⋈ nation join: nation is broadcast, supplier is read, and each worker produces an enriched supplier ⋈ nation side. That enriched side is then repartitioned by s_suppkey, while lineitem is read and repartitioned by l_suppkey, so matching rows meet on the same worker. The joined rows are then shuffled by n_name for aggregation, and the sorted final result is gathered by the coordinator.

Let's walk through those steps.

Step 1: Join supplier with nation

ClickHouse first broadcasts the tiny nation table to every worker and builds a small local nation hash table.

Each worker then reads its slice of supplier …

…and probes that local hash table.

The result is an enriched supplier ⋈ nation side.

Nothing has been shuffled yet: each worker still keeps the rows from its original supplier slice.

Step 2: Co-locate lineitem with enriched supplier rows

Next, ClickHouse prepares the larger join lineitem ⋈ (supplier ⋈ nation).

Workers first read slices of lineitem…

…and both join sides are repartitioned by supplier key: lineitem by l_suppkey, and the enriched supplier ⋈ nation rows by s_suppkey.

After the shuffle, each worker owns a disjoint supplier-key bucket containing the matching rows from both sides, and the enriched supplier ⋈ nation rows from Step 1 become the build side.

Prior bottleneck removed: no full build-side copy
Previously, each node needed the full right side of the join in memory. After the shuffle, each worker node owns only a disjoint supplier-key bucket, so it builds only its share of the hash table.

Step 3: Join locally within each supplier-key bucket

After the shuffle, each worker owns one supplier-key bucket. For that bucket, it has both sides of the join: matching lineitem rows and the enriched supplier ⋈ nation rows.

Each worker can now join locally by probing its bucket-local hash table. No worker needs the full build side, and no additional network exchange is needed for this join.

Step 4: Shuffle by GROUP BY key for final aggregation

The join output is still partitioned by supplier key, not by n_name. So the same nation can appear on multiple workers. ClickHouse reshuffles the joined rows by the GROUP BY key, n_name, so each worker owns complete groups and can compute sum(l_extendedprice) independently.

Prior bottleneck removed: no single-node final aggregate merge
Previously, nodes could produce partial groups locally, but the same GROUP BY key could appear on multiple nodes, so one coordinator had to merge all partial states. After the shuffle by the GROUP BY key, each worker owns complete groups and can compute the final aggregate for its keys locally.

Here, n_name has only 25 distinct values, so the final merge would be small. But for high-cardinality GROUP BY, shuffling by the grouping key avoids the single-coordinator merge bottleneck. We'll come back to this planning tradeoff at the end.

Step 5: Sort locally and gather the final result

Each worker sorts its aggregated results by revenue. The GatherExchange (line 3) combines the sorted results from all workers at the coordinator to produce the final output.

Prior bottleneck avoided: the coordinator only gathers final rows
The coordinator still receives the query result, but the expensive work has already happened across the workers. It gathers sorted, already-aggregated rows; it does not merge large partial groups or build a large join hash table.

The example above shows the logical data movement: shuffle here, broadcast there, gather at the end. Under the hood, ClickHouse Cloud needs a transport layer that can move those intermediate blocks efficiently between workers.

How does data move between stages?

Exchanges can move data in two ways.

The default is streaming exchange. Workers send blocks directly to other workers over TCP using a custom binary protocol. Data starts moving as soon as it is produced: a worker reading lineitem can begin sending blocks into a ShuffleExchange immediately, while the receiving workers start consuming them without waiting for the full input. In other words, exchanges are pipelined rather than "write everything, then read everything."

The second mode is persisted exchange. Instead of sending intermediate blocks directly between workers, ClickHouse writes them to shared object storage. This is useful for future fault recovery and for spilling intermediate results when a query exceeds cluster memory.

Streaming exchange is optimized for fast interactive queries and is the default. If a worker fails, the query fails and the client retries it. For these workloads, rerunning the query is usually cheaper than checkpointing every exchange.

Why ClickHouse Cloud makes this possible

Multi-stage distributed execution depends on workers being interchangeable. A stage can run on any worker only if that worker can access the data and metadata it needs.

Shared storage makes workers interchangeable

ClickHouse Cloud already has that foundation. Table data lives in shared object storage, and every node has access to the metadata needed to read it. The coordinator can therefore assign stages dynamically across the cluster: any worker can scan table data, receive shuffled rows, build its share of a hash table, aggregate its assigned groups, or sort its local result.

Shuffle improves memory utilization

This also improves memory utilization. When a ShuffleExchange partitions a large join across 8 workers, each worker receives roughly 1/8 of the rows and builds roughly 1/8 of the hash table. A join that would require 16 GiB of memory on one node can instead use about 2 GiB per worker across 8 nodes.

Shared storage can avoid some broadcasts

Shared storage also opens up future optimizations. For small tables, a worker may not need to receive a broadcast over the network at all; it can read the table directly from object storage and keep it in the local SSD cache for future reads. That is useful for dimension tables like nation or supplier, where local cached reads may be cheaper than broadcasting the table through the exchange layer.

Toward stateless workers

The longer-term direction is fully stateless workers: nodes that can appear on demand, pick up work for a query, read the data they need from shared storage, and disappear again when the work is done. Without fixed ownership or manual data placement. Multi-stage distributed execution is a step toward that model.

What about single-node queries?

ClickHouse's single-node execution path is unchanged. Columnar MergeTree storage, vectorized execution, and aggressive pipeline parallelism are still the foundation of query performance.

Multi-stage distributed execution is an additional, opt-in path for queries that benefit from scaling across multiple nodes. It extends ClickHouse's execution model and does not replace the single-node engine.

TPC-H benchmark results for multi-stage distributed query execution

TPC-H is an industry-standard benchmark for analytical query processing. It consists of 22 queries across 8 tables, ranging from simple scans to complex multi-table joins, designed to simulate real-world decision-support workloads.

We ran it at scale factor 100 (~100GB of data), where the various tables have the following row counts:

• lineitem (600M rows)
• orders (150M)
• partsupp (80M)
• part (20M)
• supplier (1M)
• nation (25)

We ran the benchmark on ClickHouse Cloud staging machines with ARM (Graviton), 8 cores, and 32 GB RAM. We're using ClickHouse's SharedMergeTree and server version 26.2.1.261

The table below shows the results from running each query on 1 node (our baseline) and 8 nodes using multi-stage distributed query execution. We run each query three times and take the best time.

Why is Q02 slower?

Some single-node optimizations are not yet fully supported in distributed mode, e.g., runtime filters (Bloom filter pushdowns across joins). Q02 shows a regression because of this.

What scales well?

Near-linear scaling for scan-dominated queries.
Q01 (full scan + aggregation of 600M rows) achieves 7.4x on 8 nodes.

The work is almost entirely reading and aggregating, which splits evenly across workers with minimal exchange overhead.

Good scaling (2-5x) for multi-join queries.
Q19 (4.5x), Q04 (4.2x), Q06 (4.1x), Q09 (3.4x), Q22 (3.4x), Q13 (3.3x), Q12 (3.0x), Q03 (2.9x).

For these queries, there is significant shuffle overhead, as every exchange involves serializing data, network transfer, and deserialization, but it's proportionally small compared to the parallelized join computation.

Where is there room for smarter plans?

The rule-based strategy works well for most queries, but some plans are suboptimal.

Q08 shuffles both sides of a join where one side has only 134K rows after filtering - a broadcast would save reshuffling 600M rows.

Q18's EXISTS subquery limits parallelism. Small tables like supplier (1M rows) are shuffled over the network even though every worker could read them directly from shared object storage.

These limitations are not fundamental to the execution engine. The engine can execute any plan it's given, the question is which plan to give it.

What's next?

We are working on a cost-based optimizer for multi-stage distributed query execution, which we expect will further improve query performance.

One important area is choosing the right aggregation strategy automatically. Some queries benefit from shuffling by the GROUP BY key so each worker owns complete groups; others are better served by local partial aggregation followed by a final merge. A cost-based optimizer can choose between these strategies based on cardinality, data size, and cluster shape.

Stay tuned for a future post.

How can I use multi-stage distributed query execution?

At the time of writing (May 2026), multi-stage distributed execution is experimental and only available in ClickHouse Cloud as part of a private preview program.

To request access, reach out to your ClickHouse account team or contact us at [email protected]

Summary

• ClickHouse Open House 2026 Day 1 brought announcements across ClickHouse Cloud, Postgres, distributed query execution, AI agents, observability, and lakehouse interoperability.
• ClickHouse Postgres moves to public beta delivering over 5x more transactions per second than AWS RDS; multi-stage distributed queries cut TPC-H SF100 runtime from 117.6 seconds to 54.7 seconds; Langfuse V4 delivers 200x query performance improvements.
• ClickHouse Agents powered by Claude entered public beta with a native chat experience and no-code agent builder; write support landed for Microsoft OneLake and Unity Catalog, enabling bi-directional workflows between ClickHouse and external lakehouse systems.

Today at Open House 2026 in San Francisco, we shared a broad set of announcements across ClickHouse Cloud, AI agents, observability, Postgres, and lakehouse interoperability.

Open House has always been a chance for our users to come together and exchange ideas about where real-time data systems are heading, and this year's launches reflect many of the conversations we've been having with users, contributors, customers, and partners over the last year. This post rounds up the major announcements from Day 1, and tomorrow we'll be back with another set of announcements from Day 2.

Improving the UX for humans and agents

ClickHouse Cloud is now serving two audiences: the teams that operate it in production and the AI agents that query it programmatically. These product updates bring improvements across resilience, observability, schema management, and developer tooling to make ClickHouse Cloud easier to operate and easier to build on.

Cross-Region Replication brings an active-passive failover architecture to ClickHouse Cloud. Data is synchronously replicated to a secondary region, with recovery time measured in minutes and recovery point measured in seconds. We will begin a limited private preview later this year.

Monitoring v2 overhauls observability with opinionated service health signals, schema exploration, query insights, and mutation tracking all in one place.

A Materialized View Pipeline Visualization will give teams a live visual map of data flowing across materialized views, one-click debugging for unhealthy refreshes, and a drag-and-drop pipeline builder for creating new pipelines.

Coming soon Schema Management and Optimization introduces a fully guided, end-to-end AI-assisted schema management experience. It includes a workload-aware recommendation engine, a dedicated sandboxing environment, automated impact analysis, and a guided blue-green deployment flow for zero-downtime schema changes.

On the developer and agent side, the ClickHouse CLI is built for agentic workflows, with official ClickHouse skills installable directly into Cursor, Claude Code, and other coding agents. The same CLI manages both local development and production Cloud services.

Query API Endpoints gets a unified management pane, enterprise-grade RBAC, native IDE integrations, and MCP tooling support. The AI-Enhanced Query Builder UI lets analysts build complex queries visually and move fluidly between the visual builder and ClickHouse Assistant.

And MCP-as-a-Service enables fully managed, domain-specific remote MCP servers to be spun up directly from the ClickHouse Cloud UI, with fine-grained access control and support for custom context.

Together, these updates make ClickHouse Cloud a more complete platform for running production workloads, whether the queries come from a dashboard, a notebook, or an agent.

Postgres announcements

AI workloads are collapsing the divide between transactional and analytical databases. Applications that once ran predictable, hard-coded queries now generate unpredictable bursts of agent-driven requests that need answers from both sides of the stack. Best-of-breed matters more than ever: Postgres for OLTP, ClickHouse for OLAP. But composing them has traditionally meant standing up each database independently and bridging the two with custom CDC pipelines, message brokers, and orchestration logic.

ClickHouse Postgres, now moving from private preview to public beta, is built to close that gap. The service runs on local NVMe storage, eliminating the primary bottleneck in transactional workloads by colocating storage with compute instead of relying on network-attached volumes.

In early benchmarks, this delivers over 5x more transactions per second than AWS RDS and 2.4x more than the next closest alternative. High availability is supported with up to two standby replicas across availability zones, synchronous commit to the fastest standby, and automatic failover. Continuous WAL archiving to S3 provides point-in-time recovery, branching, and region-survivable durability.

On the integration side, a Postgres-native CDC pipeline streams inserts, updates, and deletes directly into ClickHouse with no intermediate infrastructure, handling both parallel initial snapshots and continuous replication.

To make the experience even simpler for developers already building on Postgres, a new open-source extension, pg_clickhouse, makes ClickHouse-backed tables queryable directly from within a standard Postgres session, transparently pushing projections, filters, and aggregations down to ClickHouse for fast analytical queries. Developers continue using familiar Postgres SQL and tooling while the right engine handles the right workload behind the scenes.

For a deeper look at the architecture, benchmarks, and roadmap, read the dedicated blog post.

Distributed queries in ClickHouse

ClickHouse Cloud now supports multi-stage distributed query execution in private preview. The feature scales large joins and high-cardinality aggregations across many nodes by repartitioning intermediate data between execution stages.

Parallel replicas already distribute probe-side work, but every node still rebuilds the full hash table from the right side of the join. High-cardinality aggregations hit a similar wall, with final aggregation collapsing onto a single coordinator. Multi-stage execution removes both bottlenecks.

Workers exchange intermediate results, re-partition by join or GROUP BY keys, and operate on independent slices of the problem.

The feature builds on ClickHouse Cloud's shared-storage architecture, where any worker can access any data without fixed shard ownership. That makes it possible to distribute query stages dynamically across available compute, pointing toward more elastic execution with interchangeable worker pools.

In early TPC-H testing at scale factor 100, the full benchmark suite ran in 54.7 seconds on eight nodes, down from 117.6 seconds on a single node. Roughly a 2x improvement, with further gains expected as the cost-based optimizer matures.

For more technical details, we recommend users read the dedicated blog post.

Join performance and other core improvements

We presented how JOIN performance in ClickHouse improved by over 6x in the last year through support for correlated subqueries, lazy materialization, runtime filters, and automatic join reordering. These optimizations and features now put ClickHouse on par with established data warehouses across TPC-H and other standard JOIN benchmarks, often matching or exceeding the performance of systems such as Snowflake, BigQuery, and Databricks.

We also recapped some of the most recent improvements in the core database, including Full-Text Search being made generally available, addressing one of the most common requirements for observability and AI workloads.

Write support for Microsoft OneLake and Unity catalogs

Following our recent announcement that ClickHouse is now Data Lake Ready, we've continued expanding support for open data lake ecosystems and catalog integrations. ClickHouse now supports write operations to Iceberg tables via Unity Catalog. Furthermore, Microsoft users can now also write directly to Iceberg tables managed by Microsoft OneLake.

This makes it possible to support fully bi-directional workflows between ClickHouse and external lakehouse systems. As organizations increasingly adopt open table formats and shared catalog layers, it is important not only for ClickHouse to act as a fast analytical engine on top of these systems, but also to allow users to write data back in open formats to ensure data remains interoperable. Write support for additional catalogs is planned over the coming months.

ClickPipes

We're bringing ClickPipes natively to GCP! Starting today, new ClickHouse Cloud services on GCP run ClickPipes in the same region for lower-latency ingestion, data locality guarantees, and tighter integration with GCP-native features.

This includes Private Service Connect (PSC) support for secure, direct private connections to GCP-managed services behind a VPC; before, you had to tunnel connections via an SSH bastion host.

Note: If you're using ClickPipes with existing GCP services, please reach out to your account executive to discuss a migration plan.

To deepen our integration with the GCP ecosystem even further, we're also announcing a new ClickPipes connector for Google Cloud Pub/Sub, in Private Preview. If Pub/Sub is part of your stack, you can now subscribe to topics and stream data directly into ClickHouse Cloud with no additional infrastructure. The connector supports all common formats (JSON, Avro, Protobuf) and schema registry integration, with attribute-based message filtering, flexible seek options, and per-key ordered delivery. Like all connectors, it can also be managed programmatically via OpenAPI and the ClickHouse Terraform provider.

Bring any agent to your real-time data, with zero lock-in

AI agents are a new kind of workload, and they're breaking the old playbook. They fire ten to a hundred times the volume of queries a human analyst would, in bursts, all at once, and most analytical engines simply weren't built for it. Closed data platforms make it worse, locking teams into limited and proprietary models, a single agent, and one rigid way of working, while costs spiral. The agentic era needs a real-time substrate that's fast, affordable, and refuses to lock anyone in.

Today, we're introducing ClickHouse Agents, a fully managed agentic analytics service on ClickHouse Cloud, powered by Claude and now in public beta.

Every ClickHouse Cloud user gets a native chat experience and a no-code agent builder for shipping agents grounded in their own data, with zero setup. Under the hood is a sandboxed code interpreter, shareable artifacts, skills, memory, and multi-agent workflows, all built on LibreChat - the open-source platform that joined ClickHouse last November.

Because it speaks MCP natively, context can be pulled from any MCP-compatible system. ClickHouse is purpose-fit for this agentic era, where sub-second queries on billions of rows, petabyte scale, and cost efficiency are key to running agentic analytics in production successfully. Customers don't have to choose between us and the tools they already love. Use our agents out of the box, build or bring your own, get speed, transparency, and full control with your analytics AI stack.

Langfuse announcements

As we discussed in the previous section, AI agents are introducing a fundamentally different workload pattern for data systems, and observability platforms are feeling that shift just as strongly. A single agent run can produce hundreds of observations across LLM calls and tool calls, and the interesting signal is rarely at the top of the trace but requires deep evaluation workflows in production and in development. Langfuse solves for this problem, and the team joined ClickHouse in January.

Today, we're announcing Langfuse V4, in beta on Langfuse Cloud and soon to be available for self-hosted deployments.

This represents a "simplify for scalability" rewrite that collapses the data model into a single immutable observations table with no joins and no deduplication. The result is millisecond dashboard loads, 200x query performance across many important routes, and a single project that comfortably handles billions of events, all benefiting from ClickHouse's performance. Learn more about the details of this change in this technical blog post.

On top of V4, we're shipping a tighter improvement loop: Experiments now ship with an improved UI, code-based evaluators, and CI/CD workflows. This means prompt and agent changes can be scored before they reach production, with LLM-as-a-judge supporting categorical, boolean, and free-text scores and alerting, ensuring teams can react to score and latency regressions at agent scale.

The platform itself is becoming agent-native, with v2 of the MCP server exposing most API routes to agents, a rewritten Langfuse CLI, and skills that wrap every Langfuse capability so the same building blocks work for humans and agents. Soon, these APIs will include fast full-text search, allowing agents to explore traces in a more free-form/semantic way.

Finally, Langfuse Cloud users can now sign in using their ClickHouse Cloud identity, making adoption seamless and removing the need to manage a separate login. Langfuse itself remains open source, OpenTelemetry-native, self-hostable, and built to scale with the realities of agent workloads.

ClickStack announcements

ClickStack Cloud

Today, we announced the private preview of ClickStack Cloud, a fully managed, serverless observability platform built on ClickHouse. Teams can send OpenTelemetry data to a managed OTLP endpoint, then investigate logs, metrics, and traces through the ClickStack UI without operating collectors, ingestion pipelines, or ClickHouse clusters themselves. ClickStack Cloud enters private preview with managed ingestion, a serverless query experience, and integrated observability workflows built directly on ClickHouse Cloud.

During the private preview, we're focused on two areas that matter for large-scale observability workloads: independently scaling ingestion and query infrastructure, and automatically tuning the underlying datastore based on how teams actually use their telemetry data.

This means improving systems that learn from common query patterns and automatically optimize telemetry data over time. Planned areas of automatic tuning include materializing frequently queried fields, adjusting primary keys around common filters, and adding materialized views and indexes for common dashboard and investigation workflows. These capabilities will be refined alongside early adopters and design partners throughout the private preview period.

Private preview spots are limited. If you are interested in using ClickStack Cloud, sign up for the preview program and tell us about your observability workload.

For more details, see our dedicated blog post.

Managed ClickStack

In addition to ClickStack Cloud entering private preview, our existing Managed ClickStack offering is now generally available.

Managed ClickStack is designed for teams that want direct operational control over their observability stack, including ingestion pipelines, compute sizing, workload isolation, schema design, and datastore tuning. Users manage their own OpenTelemetry collectors and ingestion architecture while using ClickHouse Cloud as the underlying observability datastore. For many large-scale deployments, that control is essential for optimizing performance and achieving market-leading cost efficiency.

PromQL support

While we believe SQL is the lingua franca of data analysis, some workloads benefit from a domain-specific language. PromQL for Prometheus-style metrics is one example.

To close out our observability announcements, we wanted to share an early preview of a major area of investment for 2026: PromQL support in ClickHouse and ClickStack.

During the demo, we showed PromQL queries running against ClickHouse through the ClickStack UI.

This is still very early work. There are dragons here. The implementation is incomplete, behavior will change, and there is still a large gap between the current state and something we would consider production-ready.

For anyone curious enough to experiment, the underlying functionality is already available experimentally in ClickHouse, with integration also available in open-source ClickStack.

Most of the recent work has focused on language coverage and compatibility. The goal is straightforward: existing PromQL queries should behave the way users expect when pointed at ClickHouse. Performance work is happening alongside this, so PromQL queries can still benefit from the scale and execution speed of the ClickHouse engine.

Partner program announcement

ClickHouse also introduced House Mates, its first formal partner community and program. It launches with a founding cohort of more than 25 technology partners and over 35 services, consulting, and channel partners across six continents. The program is organized across three tracks: Technology, Services, and Reseller, each with three tiers: Ignite, Accelerate, and Prime. Benefits scale with tier and include joint go-to-market motions, co-innovation and integration support, enablement and certifications, incentives, and a dedicated partner portal. Read the announcement blog for more details.

Wrap-up

Today's announcements span distributed query execution, observability, lakehouse interoperability, agentic analytics, and transactional workloads, all while keeping teams in control of the tools and architectures they use. Whether that means bringing your own agent, your own catalog, your own collectors, or continuing to work from familiar Postgres workflows, the focus remains the same: fast real-time systems without lock-in.

The performance improvements behind these launches were equally significant, including over 5x higher throughput than AWS RDS for Postgres workloads, roughly 2x faster distributed query execution at TPC-H scale factor 100, and up to 200x query performance improvements in Langfuse V4. We'll be back tomorrow with another round of announcements and deeper technical sessions for Day 2.

At Open House 2026, we announced ClickStack Cloud in private preview, AI Notebooks in beta, and the ClickStack MCP server: three updates that make observability on ClickHouse faster to get started with, easier to investigate in, and more composable with y

Introducing ClickStack Cloud: a fully managed, serverless observability platform built on ClickHouse where you send OpenTelemetry data to a managed endpoint and immediately explore logs, metrics, and traces without operating any infrastructure.

We are launching House Mates, a partner community and program bringing together 60+ ISV, consulting, and channel partners to help customers build, scale, and buy ClickHouse solutions with confidence.

Postgres managed by ClickHouse is now in public beta - a fully managed, NVMe-backed Postgres service with native CDC into ClickHouse and a unified query layer via pg_clickhouse.

Introducing CostBench, an open benchmark that turns cloud data warehouse runtime and billing models into comparable performance-per-dollar results.

Today we welcome over 500 data engineers to our annual user conference in San Francisco and announce some exciting product launches and company milestones.

5 Countries, 12 Days, One ClickHouse: Inside Alexey's APJ AI Tour

Learn how to use ClickHouse's SAMPLE BY clause to run fast, approximate queries on large datasets with minimal accuracy trade-off.

How we integrated the Rust Delta Kernel to replace our native Delta Lake implementation, reducing maintenance overhead while unlocking writes, schema evolution, time travel, and partition pruning.

We're releasing clickhousectl v0.2.0. This release adds Postgres, ClickPipes, SQL over HTTP, a few agent-friendly output tweaks, and a standalone Rust client library for the ClickHouse Cloud API.

Learn how ClickStack’s new SQL-powered charting and alerting unlock anomaly detection, rolling baselines, and advanced observability workflows directly on top of ClickHouse, without relying on external tooling.

Learn how ClickHouse Cloud's Join table engine enables fast, updatable in-memory lookups for dimensional modeling — with automatic upserts, deduplication, and data compaction powered by ReplacingMergeTree under the hood.

ClickHouse 26.4 is here! In this release, more features become SQL compatible, COUNT DISTINCT gets faster, EXPLAIN gets even prettier, and more

Query Insights is now in preview for ClickHouse Cloud Managed Postgres: every query pattern your database runs, ranked by impact, with the diagnostic picture of why each one is slow.

How ClickStack and Odigos eliminate observability gaps with zero-code eBPF instrumentation and full-fidelity distributed tracing at scale.