Last month we [released clickhousectl](https://clickhouse.com/blog/introducing-clickhousectl-official-cli-for-clickhouse-local-and-cloud), a CLI for ClickHouse that manages local installations, runs local servers, and operates ClickHouse Cloud.

This is exciting for me as every month my colleague Tom Schreiber and I write the ClickHouse release post and we often compare query performances between versions. Before clickhousectl, we'd either have to use Docker or look through GitHub releases to find old binaries in order to do this.

Now we can just use clickhousectl and in this blog post we'll have a look at how to compare query performances between different versions for improvements made in recent versions. 

> <small>If you just want to see the query comparisons, you can skip forward to [DISTINCT over low cardinality columns](/blog/clickhousectl-compare-versions#distinct-low-cardinality) or [Parquet metadata cache](/blog/clickhousectl-compare-versions#parquet-metadata-cache).</small>

<iframe width="768" height="432" src="https://www.youtube.com/embed/chxxBswIuYw?si=OaFJWVYQLXXHh77w" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Installing clickhousectl {#installing-clickhousectl}

But first things first, we need to get clickhousectl installed on our machine.
We can do this using the following command:

<pre><code type='click-ui' language='bash'>
curl https://clickhouse.com/cli | sh
</code></pre>

You should see something like the following output:

```shell
Detected platform: aarch64-apple-darwin
Fetching latest release...
Latest release: v0.1.18
Downloading https://github.com/ClickHouse/clickhousectl/releases/download/v0.1.18/clickhousectl-aarch64-apple-darwin...
Installed clickhousectl to /Users/markhneedham/.local/bin/clickhousectl
Created alias: chctl -> clickhousectl
```

We should now have a `clickhousectl` and `chctl` command, and if we run that we'll see the following output (trimmed for brevity):

```shell
The official CLI for ClickHouse: local and cloud

Usage: chctl <COMMAND>

Commands:
  local   Work with local ClickHouse installations
  cloud   Work with serverless ClickHouse in ClickHouse Cloud
  skills  Install ClickHouse agent skills into supported coding agents
  update  Update clickhousectl to the latest version
  help    Print this message or the help of the given subcommand(s)

Options:
  -h, --help     Print help
  -V, --version  Print version
```

## Installing local servers {#installing-local-servers}

We're going to install two ClickHouse versions on my machine - 25.12 and 26.3.
To have operations run locally, we use the `local` sub command.

We can download a version of ClickHouse by running this command:

<pre><code type='click-ui' language='bash'>
chctl local install 26.3
</code></pre>

I've actually got this version installed already, so I'll see the following output:

```shell
Resolving 26.3...
ClickHouse 26.3 is already installed as 26.3.10.16
Use --force to re-download the latest build
Installed version 26.3.10.16
```
If we use the `--force` flag that the output suggested, it will download the latest build of 26.3:

```shell
Resolving 26.3...
Downloading ClickHouse 26.3...
  [00:00:16] [###################################] 143.05 MiB/143.05 MiB (0s)
Detecting version...
Installed ClickHouse 26.3.10.30
Installed version 26.3.10.30
```

## Starting local servers {#starting-local-servers}

Next, let's have a look at how to start local servers.
First up, let's start a server running ClickHouse 25.12:

<pre><code type='click-ui' language='bash'>
chctl local server start --version 25.12 --name old
</code></pre>

We haven't actually installed this version yet, but that's ok - the CLI will automatically download it for us:

```shell
Resolving 25.12...
Downloading ClickHouse 25.12...
  [00:00:13] [#####################################] 111.51 MiB/111.51 MiB (0s)
Detecting version...
Installed ClickHouse 25.12.10.7
Server 'old' started in background (PID: 58145)
  HTTP port: 8123
  TCP port:  9000
  Version:   25.12.10.7
```

This server uses the default HTTP and TCP ports.

We can start a server running ClickHouse 26.3 as well:

<pre><code type='click-ui' language='bash'>
chctl local server start --version 26.3 --name new
</code></pre>

```shell
Resolving 26.3...
Note: 1 server already running (use `clickhousectl local server list` to see them)
Note: default ports in use, auto-assigned HTTP:8124 TCP:9001
Server 'new' started in background (PID: 58406)
  HTTP port: 8124
  TCP port:  9001
  Version:   26.3.10.30
```

This time it recognizes that there's already a server running and therefore uses different ports.

We can run the following command to check which servers are running:

<pre><code type='click-ui' language='bash'>
chctl local server list
</code></pre>

```shell
╭──────┬─────────┬───────┬────────────┬───────────┬──────────╮
│ Name │ Status  │ PID   │ Version    │ HTTP Port │ TCP Port │
├──────┼─────────┼───────┼────────────┼───────────┼──────────┤
│ new  │ running │ 58406 │ 26.3.10.30 │ 8124      │ 9001     │
│ old  │ running │ 58145 │ 25.12.10.7 │ 8123      │ 9000     │
╰──────┴─────────┴───────┴────────────┴───────────┴──────────╯
```

This shows the servers running for a given project, which means if we run the command from a different directory, we'll see the following output:

```shell
No servers
```

We can use the `--global` flag to show all servers running across our machine:

<pre><code type='click-ui' language='bash'>
chctl local server list --global
</code></pre>

```shell
╭──────┬─────────┬───────┬────────────┬───────────┬──────────┬──────────────╮
│ Name │ Status  │ PID   │ Version    │ HTTP Port │ TCP Port │ Project      │
├──────┼─────────┼───────┼────────────┼───────────┼──────────┼──────────────┤
│ old  │ running │ 58145 │ 25.12.10.7 │ 8123      │ 9000     │ .../ch-test  │
│ new  │ running │ 58406 │ 26.3.10.30 │ 8124      │ 9001     │ .../ch-test  │
╰──────┴─────────┴───────┴────────────┴───────────┴──────────┴──────────────╯
```

## Loading data into ClickHouse {#loading-data}

Now that we've got our servers running, it's time to load some data.
We'll first connect to the 25.12 server:

<pre><code type='click-ui' language='bash'>
chctl local client --name old -mn
</code></pre>

And then run the following query to create the [UK price paid](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid) table:

<pre><code type='click-ui' language='sql'>
CREATE OR REPLACE TABLE uk_price_paid
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3,
               'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (date, postcode1, postcode2, addr1, addr2)
SETTINGS add_minmax_index_for_numeric_columns=1,
         add_minmax_index_for_string_columns;
</code></pre>

For simplicity's sake, I've got this data in Parquet format and I'm serving it locally via a HTTP server.
We can import it like this:

<pre><code type='click-ui' language='sql'>
INSERT INTO uk_price_paid 
SELECT * FROM url('http://127.0.0.1:8000/uk_all.parquet');
</code></pre>

```shell
30452463 rows in set. Elapsed: 8.990 sec. Processed 30.45 million rows, 170.44 MB (3.39 million rows/s., 18.96 MB/s.)
Peak memory usage: 2.00 GiB.
```

30 million rows imported! Now we need to get the same data into the 26.3 server.

One of the handy things about `clickhousectl` is that you can copy a table schema from one local server to another entirely from the CLI, without touching any files. First, fetch the create table statement from the old server:

<pre><code type='click-ui' language='bash'>
chctl local client --name old \
  --query "SHOW CREATE TABLE uk_price_paid" \
  --output-format LineAsString
</code></pre>

```shell
CREATE TABLE default.uk_price_paid
(
    `price` UInt32,
    `date` Date,
    `postcode1` LowCardinality(String),
    `postcode2` LowCardinality(String),
    `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4),
    `is_new` UInt8,
    `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2),
    `addr1` String,
    `addr2` String,
    `street` LowCardinality(String),
    `locality` LowCardinality(String),
    `town` LowCardinality(String),
    `district` LowCardinality(String),
    `county` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (date, postcode1, postcode2, addr1, addr2)
SETTINGS index_granularity = 8192
```

By default, `SHOW CREATE TABLE` returns its output wrapped in a table with a header and borders. `LineAsString` treats each row of the result as a plain string and prints it as-is, with no decorators — just the raw SQL. We can then pipe that straight into the 26.3 server in one command:

<pre><code type='click-ui' language='bash'>
chctl local client --name old \
  --query "SHOW CREATE TABLE uk_price_paid" \
  --output-format LineAsString |
chctl local client --name new
</code></pre>

The table now exists on the new server. To copy the data across, we connect to the 26.3 server and use the [`remote`](https://clickhouse.com/docs/sql-reference/table-functions/remote) table function to read directly from the 25.12 server:

<pre><code type='click-ui' language='bash'>
chctl local client --name new -mn
</code></pre>

<pre><code type='click-ui' language='sql'>
INSERT INTO uk_price_paid
SELECT * 
FROM remote('localhost:9000', 'default', 'uk_price_paid');
</code></pre>

```shell
30452463 rows in set. Elapsed: 15.169 sec. Processed 30.45 million rows, 1.33 GB (2.01 million rows/s., 87.96 MB/s.)
Peak memory usage: 339.23 MiB.
```

This is a useful pattern any time you want to copy data from one local server to another.

## DISTINCT over low cardinality columns (added in 26.1) {#distinct-low-cardinality}

Now it's time to test a performance improvement made to DISTINCT over low cardinality columns in ClickHouse 26.1.
Below is a slide from Alexey's 26.1 release call slide deck:

[![slide-26.1-16.png](/uploads/slide_26_1_16_2669b51bf6.png)](https://presentations.clickhouse.com/2026-release-26.1/?full#16)

We'll first run the query 5 times against 25.12:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do 
  chctl local client \
    --name old \
    --query 'SELECT distinct(county) FROM uk_price_paid' \
    --time \
    -- --output-format Null;
done
</code></pre>

```shell
0.076
0.082
0.075
0.074
0.073
```

The `--time` flag outputs times in seconds, so the best time here is 0.076 seconds (76ms).

Now let's do the same against 26.3:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do 
  chctl local client \
    --name new \
    --query 'SELECT distinct(county) FROM uk_price_paid' \
    --time \
    -- --output-format Null;
done
</code></pre>

```shell
0.015
0.016
0.014
0.017
0.014
```

The best time here is 0.014 seconds (14ms), which is a little more than 5 times faster than on ClickHouse 25.12.
Success!

## Parquet metadata cache (added in 26.3) {#parquet-metadata-cache}

In the 26.3 release last month, we introduced a metadata cache that stores Parquet file footer metadata (structure, row group layout, column statistics), keyed by ETag for consistency.

[![slide-31.png](/uploads/slide_31_711f184015.png)](https://presentations.clickhouse.com/2026-release-26.3/?full#31)

This will be especially useful when running repeat queries on Parquet files in S3.

I asked Claude Code to recommend a dataset containing Parquet files and it suggested the [AWS public blockchain dataset](https://registry.opendata.aws/aws-public-blockchain/).

The following query, which we'll save to `parquet.sql`, returns the number of rows and average gas price for block numbers between 19500000 and 19501000.

<pre><code type='click-ui' language='sql'>
SELECT count(), avg(gas_price)
FROM s3(
  's3://aws-public-blockchain/v1.0/eth/transactions/date=2024-*/*.parquet',
  NOSIGN,
  Parquet
)
WHERE block_number BETWEEN 19500000 AND 19501000
SETTINGS use_query_condition_cache=0;
</code></pre>

We'll first run the query five times against 25.12 by passing the file name in using the `--queries-file` flag:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do
  chctl local client --name old --queries-file parquet.sql --time;
done
</code></pre>

```shell
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
11.480
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.473
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.102
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
9.604
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
8.957
```

And now the same query against 26.3:

<pre><code type='click-ui' language='bash'>
for i in {1..5}; do
  chctl local client --name new --queries-file parquet.sql --time;
done
</code></pre>

```shell
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
12.463
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
2.224
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.696
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.639
   ┌─count()─┬────avg(gas_price)─┐
1. │  168399 │ 20033038997.74929 │
   └─────────┴───────────────────┘
1.687
```

The initial run takes around 11-12 seconds on both versions. Subsequent runs take around 8-9 seconds on ClickHouse 25.12, which is possibly due to file system caching.

With 26.3, we see a huge reduction in the query time of subsequent runs down to around 1-2 seconds, which is around 5 times faster than it was on the first run.

## Inspecting the Parquet metadata cache {#inspecting-parquet-metadata-cache}

There are a couple of system tables that we can query to learn more about the Parquet metadata cache.

First up, there are some settings that we can query via `system.server_settings`:

<pre><code type='click-ui' language='sql'>
SELECT name, value
FROM system.server_settings
WHERE name ILIKE '%parquet_metadata_cache%';
</code></pre>

```shell
   ┌─name───────────────────────────────┬─value─────┐
1. │ parquet_metadata_cache_policy      │ SLRU      │
2. │ parquet_metadata_cache_size        │ 536870912 │
3. │ parquet_metadata_cache_max_entries │ 5000      │
4. │ parquet_metadata_cache_size_ratio  │ 0.5       │
   └────────────────────────────────────┴───────────┘
```

The cache size is 512MB or 5,000 entries, whichever value's reached first!

We can also query `system.metrics` to see how much data we've populated in the cache:

<pre><code type='click-ui' language='sql'>
SELECT name, value
FROM system.metrics
WHERE name ILIKE '%parquet%metadata%';
</code></pre>

```shell
   ┌─name──────────────────────┬────value─┐
1. │ ParquetMetadataCacheBytes │ 46067740 │
2. │ ParquetMetadataCacheFiles │      366 │
   └───────────────────────────┴──────────┘
```

We have the metadata cached for 366 files and it's taking up 46MB of space.

## Conclusion {#conclusion}

In this post we've seen how `clickhousectl` makes it easy to spin up multiple ClickHouse versions side by side and compare query performance between them.

We demonstrated two improvements that shipped in recent releases: 

* A 5x speedup for DISTINCT queries over low cardinality columns (introduced in 26.1)
* A Parquet metadata cache (introduced in 26.3) that reduces repeat S3 query times from ~9 seconds down to ~1-2 seconds.

If you want to try this yourself, install `clickhousectl` with:

<pre><code type='click-ui' language='bash'>
curl https://clickhouse.com/cli | sh
</code></pre>

And check the [clickhousectl documentation](https://clickhouse.com/docs/interfaces/cli) for more on what you can do with it.


Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Get started today

We use clickhousectl to spin up multiple ClickHouse versions side by side and benchmark two recent performance improvements.

large_Comparing ClickHouse Versions.jpg

medium_Comparing ClickHouse Versions.jpg

small_Comparing ClickHouse Versions.jpg

thumbnail_Comparing ClickHouse Versions.jpg

Comparing ClickHouse Versions.jpg

Comparing ClickHouse versions with clickhousectl

Hello, and welcome to the April 2026 ClickHouse newsletter!

This month, we have a new CLI for ClickHouse local and cloud, agentic coding at ClickHouse, materialized CTEs in our latest release, building a SIEM, and more!

## Featured community member: Nazarii Piontko {#featured-community-member}

This month's featured community member is Nazarii Piontko, Solutions Architect at Future Processing.  

![](/uploads/newsletter_apr2026_image2_c4a00ae7d0.png)

He leads technical presales and architecture, translating complex requirements into solutions for clients across trading, mobility, and logistics. He stays hands-on through open-source contributions to distributed data systems, mentors engineers, and has taught at universities.

Nazarii contributed two features to ClickHouse 26.3 LTS: the [`naturalSortKey`](https://github.com/ClickHouse/ClickHouse/pull/90322) function, which enables intuitive sorting of strings containing numbers (so "file10" sorts after "file9", not "file1"), and support for the [ALP compression codec](https://github.com/ClickHouse/ClickHouse/pull/91362), which brings efficient lossless compression for floating-point data.

➡️ [Connect with Nazarii on LinkedIn](https://www.linkedin.com/in/piontko/)

## Open House 2026 {#open-house}

It's just over a month until the second edition of [Open House](https://clickhouse.com/openhouse/san-francisco), a free three-day ClickHouse user conference running May 26–28 in San Francisco.

Kick things off on May 26 with hands-on workshops covering real-time analytics, observability, database administration, and data warehousing, then head into two days of keynotes, technical sessions, and networking.

Hear from ClickHouse CEO Aaron Katz and CTO Alexey Milovidov, plus industry guests including Bret Taylor (Sierra) and Guillermo Rauch (Vercel). Admission is free!

➡️ [Register now](https://clickhouse.com/openhouse/san-francisco)

## 26.3 release {#26-3-release}

![](/uploads/newsletter_apr2026_image1_ce952c56f3.png)

26.3 sees the experimental introduction of materialized Common Table Expressions (CTEs) and WebAssembly User-defined functions (UDFs).

This release also makes asynchronous inserts the default, enables JOIN reordering for ANTI, SEMI, and FULL, and improves the internal storage of the ClickHouse Map data type.

➡️ [Read the release post](https://clickhouse.com/blog/clickhouse-release-26-03)

## Introducing clickhousectl: the CLI for ClickHouse local and cloud (beta) {#introducing-clickhousectl}

![april_2026_clickhouse-ctl.png](/uploads/april_2026_clickhouse_ctl_e3d28f3583.png)

Alasdair Brown announced clickhousectl, a CLI for managing both local ClickHouse installations and cloud deployments, built with AI agents in mind alongside human developers.

Designed for agentic development, it handles local version management, project scaffolding, and server lifecycle, while also supporting cloud infrastructure commands with authentication safeguards like read-only OAuth and permissioned API keys.

➡️ [Read the blog post](https://clickhouse.com/blog/introducing-clickhousectl-official-cli-for-clickhouse-local-and-cloud)

## How Goldsky made historical blockchain data backfills 12x faster {#how-goldsky-made-historical-blockchain-data-backfills-12x-faster}

![](/uploads/newsletter_apr2026_image6_7949daf91e.png)

Goldsky sped up historical blockchain backfills by 12x by replacing their Kafka/Avro pipeline with direct ClickHouse reads using the Apache Arrow format.

Eliminating deserialization overhead by reading data in the format closest to its physical storage proved extremely efficient, boosting throughput from ~50K to ~600K rows/second.

➡️ [Read the blog post](https://goldsky.com/blog/making-historical-blockchain-backfills-faster)

## Agentic coding at ClickHouse {#agentic-coding-at-clickhouse}

![](/uploads/newsletter_apr2026_image7_ffb09aaaf1.png)

Our CTO, Alexey Milovidov, argues that agentic coding has crossed the threshold into genuine usefulness for professional software development, driven by recent model improvements such as Claude Opus 4.5.

He walks through how we've achieved productivity gains by using AI agents across a wide range of tasks - fixing flaky tests, investigating bugs, code reviews, security research, and more.

The key takeaway: As of today, agents work best as pair programmers under an engineer's direction, not as autonomous replacements.

➡️ [Read the blog post](https://clickhouse.com/blog/agentic-coding)

## Building a SIEM with ClickHouse and Clickdetect {#building-a-siem-with-clickhouse-and-clickdetect}

![](/uploads/newsletter_apr2026_image3_f7c3e467cd.png)

Vinicius Morais walks through building a SIEM architecture using ClickHouse and Clickdetect, an open-source detection engine that runs SQL-based security rules against your log data.

He uses Wazuh (an open-source security platform) solely as a log collector and decoder, forwarding parsed alerts to a ClickHouse table with compression codecs, TTLs, bloom filter indexes, and S3 storage support.

From there, Clickdetect schedules detections and fires alerts to a webhook — giving you a lightweight, cost-effective SIEM that scales from a single node to a distributed ClickHouse cluster.

➡️ [Read the blog post](https://medium.com/@souzo/building-a-powerful-siem-with-clickhouse-and-clickdetect-ae68a4495a76)

## Building high-performance full-text search for object storage {#building-high-performance-full-text-search-for-object-storage}

![](/uploads/newsletter_apr2026_image8_39f30f112c.png)

Last month, we announced that Full-text search was generally available. We now have a blog post written by the engineering team explaining how they redesigned the FTS index to work efficiently with object storage, where random reads are expensive.

The new index uses front-coding compression, a sparse lookup index, and adaptive posting lists, allowing many queries to be answered from the index alone without touching the underlying text columns.

➡️ [Read the blog post](https://hookdeck.com/blog/how-we-made-payload-search-60x-faster-in-clickhouse)

## Quick reads {#quick-reads}

* Marcin Kulakowski explains [why ClickHouse is becoming the data control plane](https://mkulakowski2-73849.medium.com/the-rise-of-real-time-data-why-clickhouse-is-becoming-the-control-plane-77b59bf85399).  
* Divyanshu shares what he [learned about observability (and ClickHouse) in 10 days](https://medium.com/@shekdivyanshu/what-i-learned-about-observability-in-10-days-a370c143d2ee).  
* Alexandra Petra Berger [draws parallels](https://medium.com/towards-data-engineering/poyekhali-what-1961-soviet-space-tech-teaches-us-about-modern-data-engineering-1549445a054f) between Sergei Korolev's space engineering principles - precision, redundancy, and military-grade quality checks - and modern data engineering practices, using ClickHouse compression codecs like Gorilla and DoubleDelta to illustrate how the same rigor applies to building reliable data systems.

## Upcoming events {#upcoming-events}

### Global virtual events

* [Combining Postgres & ClickHouse to Build a Unified Data Stack](https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres) - Apr 22, 2026  
* [CDC ClickPipes Webinar](https://clickhouse.com/company/events/202604-EMEA-Webinar-CDC-ClickPipes) - Apr 23, 2026  
* [Analisando bilhões de registros em tempo real](https://clickhouse.com/company/events/202604-LATAM-Real-Time-Analytics) - Apr 16, 2026  
* [Analizando miles de millones de registros en tiempo real](https://clickhouse.com/company/events/202605-LATAM-Real-Time-Analytics-ES) - May 14, 2026

### Virtual training

* [ClickHouse Fundamentals](https://clickhouse.com/company/events/202605-AMER-ClickHouse-Fundamentals) - May 5, 2026  
* [Data Warehousing with ClickHouse: Level 1](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level1) - May 13, 2026  
* [Data Warehousing with ClickHouse: Level 2](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level2) - May 14, 2026  
* [Observability with ClickStack: Level 1](https://clickhouse.com/company/events/202605-AMER-EMEA-Observability-with-ClickStack-Level1) - May 19, 2026  
* [Data Warehousing with ClickHouse: Level 3](https://clickhouse.com/company/events/202605-AMER-EMEA-data-warehousing-Level3) - May 20, 2026  
* [ClickHouse Fundamentals](https://clickhouse.com/company/events/202605-EMEA-ClickHouse-Fundamentals) - May 21, 2026

### Events in AMER

* [Meetup in Sunnyvale](https://clickhouse.com/company/events/202504164) - Sunnyvale - Apr 16, 2026  
* [Google Cloud Next 2026](https://clickhouse.com/company/events/google-cloud-next-2026) - Las Vegas - Apr 22, 2026  
* [House Party, Google Cloud Next](https://clickhouse.com/company/events/2026-houseparty-google-next) - Las Vegas - Apr 22, 2026  
* [Sao Paulo 2-day In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202605-LATAM-SaoPaulo-Real-time-Analytics-w-ClickHouse) - Sao Paulo - May 12, 2026  
* [Open House](https://clickhouse.com/company/events/202605-global-open-house) - San Francisco - May 26, 2026

### Events in EMEA

* [Paris In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-EMEA-Paris-Real-time-Analytics-w-ClickHouse) - Paris - Apr 15, 2026  
* [GrafanaCON](https://grafana.com/events/grafanacon/) - Barcelona - Apr 20-22, 2026  
* [Barcelona In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-EMEA-Barcelona-Real-time-Analytics-w-ClickHouse) - Barcelona - Apr 20, 2026  
* [AWS Summit London](https://aws.amazon.com/events/summits/london/) - London - Apr 22 - Booth G18  
* [Rise of AI Berlin](https://riseof.ai/conference-2026/) - Berlin - May 5-6, 2026  
* [AWS Summit Tel Aviv](https://aws.amazon.com/events/summits/tel-aviv/) - Tel Aviv - May 6, 2026  
* [Data Innovation Summit](https://datainnovationsummit.com/) - Stockholm - May 6-8, 2026  
* [Gartner Data & Analytics](http://gartner.com/en/data-analytics) - London - May 11-13, 2026  
* [Revolution Banking](https://www.revolutionbanking.es/) - Madrid - May 12, 2026  
* [London 2-day In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202605-EMEA-London-Real-time-Analytics-w-ClickHouse) - London - May 19, 2026  
* [ClickHouse Meetup London](https://www.meetup.com/clickhouse-london-user-group/events/313759007/) - London - May 19, 2026  
* [Platforma 2026](https://www.platfor-ma.com/) - Tel Aviv - May 20, 2026  
* [AWS Summit Hamburg](https://aws.amazon.com/events/summits/hamburg/) - Hamburg - May 20, 2026  
* [Google Summit Madrid](https://cloudonair.withgoogle.com/events/cloud-ai-live-madrid-2026) - Madrid - May 28, 2026

### Events in APAC

* [Data Streaming World Tour - Mumbai](https://clickhouse.com/company/events/202604-APJ-3P-Mumbai-ConfluentStreamingTour) - Apr 13, 2026  
* [Taiwan In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-APJ-Taiwan-Real-time-Analytics-with-ClickHouse) - Taiwan - Apr 16, 2026  
* [Taipei Open Source Meetup](https://clickhouse.com/company/events/taipei-open-source-meetup) - Taipei - Apr 16, 2026  
* [Data Streaming World Tour - Bangalore](https://clickhouse.com/company/events/202604-APJ-3P-Bangalore-ConfluentStreamingTour) - Apr 16, 2026  
* [PagerDuty on Tour Tokyo](https://clickhouse.com/company/events/202604-APJ-3P-Tokyo-PagerDutyTour) - Tokyo - Apr 16, 2026  
* [Bangalore Meetup with Alexey Milovidov](https://www.meetup.com/clickhouse-bangalore-user-group/events/313739871/) - Apr 18, 2026  
* [Data Streaming World Tour - Jakarta](https://clickhouse.com/company/events/202604-APJ-3P-Jakarta-ConfluentStreamingTour) - Apr 21, 2026  
* [Ho Chi Minh In-Person Training: Real-time Analytics with ClickHouse](https://clickhouse.com/company/events/202604-APJ-HoChiMinh-Real-time-Analytics-with-ClickHouse) - Ho Chi Minh - Apr 22, 2026  
* [AWS Summit Bengaluru](https://aws.amazon.com/events/summits/bengaluru/) - Apr 22-23, 2026  
* [Microsoft AI Tour - Sydney](https://clickhouse.com/company/events/202604-APJ-3P-Sydney-MicrosoftAITour) - Apr 23, 2026  
* [AWS Summit Singapore](https://clickhouse.com/company/events/202605-APJ-3P-Singapore-AWSSummit) - Singapore - May 6, 2026  
* [AWS Summit Sydney](https://clickhouse.com/company/events/202605-APJ-AWSSummit-Sydney) - Sydney - May 13, 2026  
* [Data Engineering Summit](https://clickhouse.com/company/events/202605-APJ-3P-Bangalore-DataEngineeringSummit) - Bangalore - May 14, 2026  
* [AWS Summit Seoul](https://clickhouse.com/company/events/202605-APJ-3P-Seoul-AWSSummit) - Seoul - May 20, 2026  
* [Findy VPoE Summit](https://clickhouse.com/company/events/202605-APJ-3P-Tokyo-FindyVPoESummit) - Tokyo - May 22, 2026


Welcome to the April 2026 ClickHouse newsletter, which will round up what’s happened in real-time data warehouses over the last month.

large_newsletter_apr2026_image4.png

medium_newsletter_apr2026_image4.png

small_newsletter_apr2026_image4.png

thumbnail_newsletter_apr2026_image4.png

newsletter_apr2026_image4.png

April 2026 newsletter

Learn how ClickHouse uses primary indexes, lightweight projections, and skip indexes to prune data before reading it. Demonstrated  on a 243 million row UK property sales dataset.

large_Image 577391624 1200x630.jpg

medium_Image 577391624 1200x630.jpg

small_Image 577391624 1200x630.jpg

thumbnail_Image 577391624 1200x630.jpg

Image 577391624 1200x630.jpg

Index-based pruning in ClickHouse

In this post we're going to look at some of the most useful ClickHouse functions for querying and filtering by dates and datetimes - things like rounding to the nearest hour or 15-minute window, filtering by time of day, and computing durations between two timestamps.

If you're working with raw date strings or timestamps that need converting first, check out another post that I wrote about [parsing dates and datetimes in ClickHouse](https://clickhouse.com/blog/parsing-dates-datetimes). This post picks up from there and focuses on what to do once your data is already in a `DateTime` column.


<iframe width="768" height="432" src="https://www.youtube.com/embed/odWa7rAtI5k?si=PkUSvscWY19bmDcu" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Importing the New York City taxi dataset {#importing_the_new_york_city_taxi_dataset}

Our dataset of choice is the [New York City taxi dataset](https://clickhouse.com/docs/getting-started/example-datasets/nyc-taxi), so let's get that set up in ClickHouse. First we'll create a database:

<pre><code type='click-ui' language='sql'>
CREATE DATABASE nyc_taxi;
</code></pre>

And a table:

<pre><code type='click-ui' language='sql'>
CREATE TABLE nyc_taxi.trips_small (
    trip_id             UInt32,
    pickup_datetime     DateTime,
    dropoff_datetime    DateTime,
    pickup_longitude    Nullable(Float64),
    pickup_latitude     Nullable(Float64),
    dropoff_longitude   Nullable(Float64),
    dropoff_latitude    Nullable(Float64),
    passenger_count     UInt8,
    trip_distance       Float32,
    fare_amount         Float32,
    extra               Float32,
    tip_amount          Float32,
    tolls_amount        Float32,
    total_amount        Float32,
    payment_type        Enum('CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4, 'UNK' = 5),
    pickup_ntaname      LowCardinality(String),
    dropoff_ntaname     LowCardinality(String)
)
ENGINE = MergeTree
PRIMARY KEY (pickup_datetime, dropoff_datetime);
</code></pre>

We can then run the following command to import the data:

<pre><code type='click-ui' language='sql'>
INSERT INTO nyc_taxi.trips_small
SELECT
    trip_id,
    pickup_datetime,
    dropoff_datetime,
    pickup_longitude,
    pickup_latitude,
    dropoff_longitude,
    dropoff_latitude,
    passenger_count,
    trip_distance,
    fare_amount,
    extra,
    tip_amount,
    tolls_amount,
    total_amount,
    payment_type,
    pickup_ntaname,
    dropoff_ntaname
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{0..2}.gz',
    'TabSeparatedWithNames'
);
</code></pre>

This query imports just over 3 million records, as we can see in the output below:

```shell
3000317 rows in set. Elapsed: 32.077 sec. Processed 3.00 million rows, 256.38 MB (93.53 thousand rows/s., 7.99 MB/s.)
Peak memory usage: 536.51 MiB.
```

We can adjust the `{0..2}` in the URI to include more files if we want to import more data.

Now that we're loaded the data, it's time to write some queries. 
We'll be working with the `pickup_datetime` and `dropoff_datetime` columns:

## Trips by hour {#trips_by_hour}

Let's start by exploring taxi journeys taken on July 1st, 2015. We'll use [`toStartOfHour`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tostartofhour) to round datetimes down to the nearest hour, [`toDate`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todate) to filter to a single day, and [`toHour`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tohour) to restrict to morning hours:

<pre><code type='click-ui' language='sql'>
SELECT
  toStartOfHour(pickup_datetime) as hour,
  count() as trips,
  round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toDate(pickup_datetime) = '2015-07-01'
AND toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
</code></pre>

```shell
┌────────────────hour─┬─trips─┬─avg_passengers─┐
│ 2015-07-01 00:00:00 │   663 │            1.7 │
│ 2015-07-01 01:00:00 │   381 │            1.6 │
│ 2015-07-01 02:00:00 │   249 │            1.8 │
│ 2015-07-01 03:00:00 │   155 │            1.6 │
│ 2015-07-01 04:00:00 │   159 │            1.5 │
│ 2015-07-01 05:00:00 │   197 │            1.5 │
│ 2015-07-01 06:00:00 │   530 │            1.6 │
│ 2015-07-01 07:00:00 │   849 │            1.6 │
│ 2015-07-01 08:00:00 │  1034 │            1.6 │
│ 2015-07-01 09:00:00 │  1033 │            1.7 │
│ 2015-07-01 10:00:00 │   898 │            1.7 │
│ 2015-07-01 11:00:00 │   900 │            1.6 │
│ 2015-07-01 12:00:00 │   961 │            1.7 │
└─────────────────────┴───────┴────────────────┘
```

It was quiet overnight, trips start to pick up around 6am, and peak around 8–9am. But was July 1st typical? Let's remove the date filter and look across all days. To do that we cast `toStartOfHour` down to a `Time` type using [`::Time`](https://clickhouse.com/docs/en/sql-reference/data-types/time) - that strips out the date and leaves just the time, so all days are grouped together:

<pre><code type='click-ui' language='sql'>
SELECT
  toStartOfHour(pickup_datetime)::Time as hour,
  count() as trips,
  round(avg(passenger_count), 1) as avg_passengers
FROM nyc_taxi.trips_small
WHERE toHour(pickup_datetime) < 13
GROUP BY hour
ORDER BY hour;
</code></pre>

```shell
┌─────hour─┬──trips─┬─avg_passengers─┐
│ 00:00:00 │ 118268 │            1.7 │
│ 01:00:00 │  86495 │            1.7 │
│ 02:00:00 │  65246 │            1.7 │
│ 03:00:00 │  47377 │            1.7 │
│ 04:00:00 │  34840 │            1.7 │
│ 05:00:00 │  32328 │            1.6 │
│ 06:00:00 │  68644 │            1.6 │
│ 07:00:00 │ 107494 │            1.6 │
│ 08:00:00 │ 132596 │            1.6 │
│ 09:00:00 │ 136228 │            1.6 │
│ 10:00:00 │ 134286 │            1.7 │
│ 11:00:00 │ 137561 │            1.7 │
│ 12:00:00 │ 145282 │            1.7 │
└──────────┴────────┴────────────────┘
```

When we look across all days we still see a reduction in journeys over night, but the increase starts a couple of hours earlier, from 6am to 7am. The number of trips then stays reasonably stable for the next four hours.

## Rush hour in 15-minute windows {#rush_hour}

When exactly does the morning rush start? Let's zoom in using [`toStartOfFifteenMinutes`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#tostartoffifteenminutes) to bucket by 15-minute intervals, and [`formatDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#formatdatetime) to make the output readable. In the `WHERE` clause we cast `pickup_datetime` to `Time` to filter just by time of day - no date needed:

<pre><code type='click-ui' language='sql'>
SELECT
  formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
  count() as trips,
  round(avg(trip_distance), 2) as avgDistance
FROM nyc_taxi.trips_small
WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
AND trip_distance > 0
GROUP BY timeWindow
ORDER BY timeWindow;
</code></pre>

```shell
┌─timeWindow─┬─trips─┬─avgDistance─┐
│ 06:00 AM   │ 11601 │        4.47 │
│ 06:15 AM   │ 14645 │        3.97 │
│ 06:30 AM   │ 19033 │        3.67 │
│ 06:45 AM   │ 22795 │         3.2 │
│ 07:00 AM   │ 23179 │        3.27 │
│ 07:15 AM   │ 25465 │        3.12 │
│ 07:30 AM   │ 28350 │        3.04 │
│ 07:45 AM   │ 29914 │        2.89 │
│ 08:00 AM   │ 30444 │           3 │
│ 08:15 AM   │ 32063 │        2.91 │
│ 08:30 AM   │ 34293 │         2.8 │
│ 08:45 AM   │ 35116 │        2.63 │
│ 09:00 AM   │ 33776 │        2.74 │
│ 09:15 AM   │ 33800 │        2.72 │
│ 09:30 AM   │ 33694 │        2.73 │
│ 09:45 AM   │ 34235 │        2.63 │
└────────────┴───────┴─────────────┘
```

The surge begins at 6:30, accelerates until 7:30, and peaks at 8:45.

## What does rush hour feel like? {#rush_hour_speed}

We know when rush hour starts - but what does it feel like if you're in a taxi? We can use [`dateDiff`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#datediff) to compute journey duration in minutes, which lets us calculate average speed. We also use `dateDiff` in the `WHERE` clause to filter out zero-duration trips (bad data):

<pre><code type='click-ui' language='sql'>
WITH buckets AS (
  SELECT
    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
    count() as trips,
    round(avg(trip_distance), 2) as avgDist,
    round(avg(dateDiff('minute', pickup_datetime, dropoff_datetime)), 1) AS avgDuration,
    round(avg(
      trip_distance /
      (dateDiff('minute', pickup_datetime, dropoff_datetime) / 60)
    ), 1) AS avgSpeed
  FROM nyc_taxi.trips_small
  WHERE pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
  AND trip_distance > 0
  AND dateDiff('minute', pickup_datetime, dropoff_datetime) > 0
  GROUP BY timeWindow
  ORDER BY timeWindow
)
SELECT timeWindow, trips, avgDuration, avgDist, avgSpeed,
       bar(avgSpeed, 0, (SELECT max(avgSpeed) FROM buckets), 20) AS speedBar
FROM buckets
ORDER BY timeWindow ASC;
</code></pre>

```shell
┌─timeWindow─┬─trips─┬─avgDuration─┬─avgDist─┬─avgSpeed─┬─speedBar─────────────┐
│ 06:00 AM   │ 11562 │        13.2 │    4.48 │     19.3 │ ████████████████████ │
│ 06:15 AM   │ 14609 │        13.1 │    3.98 │     18.2 │ ██████████████████▊  │
│ 06:30 AM   │ 18993 │        12.5 │    3.67 │     17.3 │ █████████████████▉   │
│ 06:45 AM   │ 22754 │        11.5 │    3.21 │     16.1 │ ████████████████▋    │
│ 07:00 AM   │ 23139 │        12.3 │    3.27 │     15.4 │ ███████████████▉     │
│ 07:15 AM   │ 25428 │        12.7 │    3.12 │     14.3 │ ██████████████▊      │
│ 07:30 AM   │ 28313 │        13.9 │    3.04 │     13.5 │ █████████████▉       │
│ 07:45 AM   │ 29873 │        13.8 │    2.89 │     12.7 │ █████████████▏       │
│ 08:00 AM   │ 30411 │        14.2 │       3 │       12 │ ████████████▍        │
│ 08:15 AM   │ 32017 │        15.2 │    2.91 │     11.5 │ ███████████▉         │
│ 08:30 AM   │ 34258 │        15.4 │     2.8 │       11 │ ███████████▍         │
│ 08:45 AM   │ 35071 │        14.9 │    2.64 │     10.8 │ ███████████▏         │
│ 09:00 AM   │ 33718 │        15.3 │    2.74 │     10.9 │ ███████████▎         │
│ 09:15 AM   │ 33754 │        15.3 │    2.72 │     10.8 │ ███████████▏         │
│ 09:30 AM   │ 33657 │        15.4 │    2.73 │     10.8 │ ███████████▏         │
│ 09:45 AM   │ 34188 │        14.8 │    2.63 │     10.9 │ ███████████▎         │
└────────────┴───────┴─────────────┴─────────┴──────────┴──────────────────────┘
```

The [`bar`](https://clickhouse.com/docs/en/sql-reference/functions/other-functions#bar) function draws an ASCII bar chart scaled to the max value - a handy way to visualize relative values inline.

At 6am taxis are moving at just over 19 mph. By 8am that's down to around 12 mph - slow, but pretty standard for a big city during rush hour. It keeps getting slower as the morning wears on.

## Weekdays vs weekends {#weekdays_vs_weekends}

We've confirmed rush hour exists - but does it happen on weekends too? We can split weekday and weekend trips using [`countIf`](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-if) with [`toDayOfWeek`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#todayofweek): values 1–5 are weekdays, 6–7 are weekends. We then use the [`lag`](https://clickhouse.com/docs/en/sql-reference/window-functions) window function to compute the percentage change in trips between each 15-minute window:

<pre><code type='click-ui' language='sql'>
WITH trips AS (
  SELECT
    formatDateTime(toStartOfFifteenMinutes(pickup_datetime), '%r') AS timeWindow,
    countIf(toDayOfWeek(pickup_datetime) <= 5) as wdTrips,
    countIf(toDayOfWeek(pickup_datetime) > 5) as weTrips
  FROM nyc_taxi.trips_small
  WHERE trip_distance > 0
  AND pickup_datetime::Time BETWEEN '06:00:00'::Time AND '09:59:59'::Time
  GROUP BY timeWindow
  ORDER BY timeWindow
)
SELECT timeWindow, wdTrips,
  round((
    (wdTrips - lag(wdTrips) OVER (ORDER BY timeWindow)) /
    lag(wdTrips) OVER (ORDER BY timeWindow)) * 100,
  1) as wdPctChange,
  weTrips,
  round(
    ((weTrips - lag(weTrips) OVER (ORDER BY timeWindow)) /
    lag(weTrips) OVER (ORDER BY timeWindow)) * 100,
  1) as wePctChange
FROM trips
ORDER BY timeWindow;
</code></pre>

```shell
┌─timeWindow─┬─wdTrips─┬─wdPctChange─┬─weTrips─┬─wePctChange─┐
│ 06:00 AM   │    9398 │         inf │    2203 │         inf │
│ 06:15 AM   │   12254 │        30.4 │    2391 │         8.5 │
│ 06:30 AM   │   16106 │        31.4 │    2927 │        22.4 │
│ 06:45 AM   │   19727 │        22.5 │    3068 │         4.8 │
│ 07:00 AM   │   20285 │         2.8 │    2894 │        -5.7 │
│ 07:15 AM   │   22129 │         9.1 │    3336 │        15.3 │
│ 07:30 AM   │   24494 │        10.7 │    3856 │        15.6 │
│ 07:45 AM   │   25681 │         4.8 │    4233 │         9.8 │
│ 08:00 AM   │   26259 │         2.3 │    4185 │        -1.1 │
│ 08:15 AM   │   27509 │         4.8 │    4554 │         8.8 │
│ 08:30 AM   │   28891 │           5 │    5402 │        18.6 │
│ 08:45 AM   │   29154 │         0.9 │    5962 │        10.4 │
│ 09:00 AM   │   27872 │        -4.4 │    5904 │          -1 │
│ 09:15 AM   │   27268 │        -2.2 │    6532 │        10.6 │
│ 09:30 AM   │   26426 │        -3.1 │    7268 │        11.3 │
│ 09:45 AM   │   26070 │        -1.3 │    8165 │        12.3 │
└────────────┴─────────┴─────────────┴─────────┴─────────────┘
```

Weekdays show a sharp spike at 6:15–6:30, a second (smaller) increase around 7:15–7:30, and peak at 8:45 before leveling off. Weekends are different: the increase at 6:30 is gentler, and trips keep growing steadily well into the morning.


How to query datetime columns in ClickHouse - from hourly bucketing to rush hour analysis with real taxi data.

large_ClickHouse Blog Banner (4).jpg

medium_ClickHouse Blog Banner (4).jpg

small_ClickHouse Blog Banner (4).jpg

thumbnail_ClickHouse Blog Banner (4).jpg

ClickHouse Blog Banner (4).jpg

Querying DateTimes in ClickHouse

Hello, and welcome to the March 2026 ClickHouse newsletter!

This month, we have an overview of Geospatial, the launch of chDB 4, how Hookdeck made payload search 60 times faster, The Agentic Data Stack, and more!

## Featured community member: Jamie Herre {#featured-community-member}

This month's featured community member is Jamie Herre, Sr. Director of Engineering at Cloudflare.

![](/uploads/mar2026_nl_image4_2c5abe927e.png)

Jamie leads engineering on Cloudflare's analytics infrastructure - a system that processes over 1.61 quadrillion events every day across more than 300 global data centers, built on ClickHouse.

At the <a href="https://clickhouse.com/blog/cloudflare" target="_blank">ClickHouse meetup in August 2025</a>, Jamie shared how his team designed for both explosive growth and catastrophic failure simultaneously. In one live demonstration, a single query scanned 96 trillion events in under 2 seconds - while a simulated North American outage caused European clusters to silently absorb the load without missing a beat.

➡️ <a href="https://www.linkedin.com/in/jherre/" target="_blank">Connect with Jamie on LinkedIn</a>

## Upcoming events {#upcoming-events}

### Global virtual events {#global-virtual-events}

* <a href="https://clickhouse.com/company/events/v26-3-community-release-call" target="_blank">v26.3 Community Call</a> - Mar 26, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Korea-Webinar-CDC-ClickPipes" target="_blank">CDC ClickPipes: 데이터베이스를 ClickHouse로 복제하는 가장 빠른 방법</a> - Apr 1, 2026
* <a href="https://clickhouse.com/company/events/202603-AMER-Webinar-MaterializedViews" target="_blank">Under-the-Hood: ClickHouse Incremental Materialized Views and Dictionaries</a> - Apr 9, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres" target="_blank">Combining Postgres & ClickHouse to Build a Unified Data Stack</a> - Apr 22, 2026

### Virtual training {#virtual-training}

* <a href="https://clickhouse.com/company/events/202603-AMER-EMEA-Observabiity-with-ClickStackLevel3" target="_blank">Observability with ClickStack: Level 3</a> - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/202604-AMER-EMEA-Observabiity-with-ClickStackLevel2" target="_blank">Observability with ClickStack: Level 2</a> - Apr 7, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-query-optimization" target="_blank">Query Optimization with ClickHouse Workshop</a> - Apr 7, 2026

### Events in AMER {#events-in-amer}

* <a href="https://clickhouse.com/company/events/20250325" target="_blank">SRECon</a> - Seattle - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/20260324RSA" target="_blank">Bay Area Iceberg Meetup: RSA Edition</a> - San Francisco - Mar 24, 2026
* <a href="https://clickhouse.com/company/events/20260326OBS" target="_blank">Seattle Observability Meetup</a> - Seattle - Mar 26, 2026
* <a href="https://clickhouse.com/company/events/20260331SF" target="_blank">Fireside Chat in San Francisco: Column Stores & the Evolution of Observability</a> - San Francisco - Mar 31, 2026
* <a href="https://clickhouse.com/company/events/20260401Start" target="_blank">Seattle Startup Summit</a> - Seattle - Apr 1, 2026
* <a href="https://clickhouse.com/company/events/2026048ICEY" target="_blank">Iceberg Summit</a> - San Francisco - Apr 8, 2026
* <a href="https://clickhouse.com/company/events/20260409SF" target="_blank">AI Demo Night</a> - San Francisco - Apr 9, 2026
* <a href="https://clickhouse.com/company/events/google-cloud-next-2026" target="_blank">Google Cloud Next 2026</a> - Las Vegas - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/2026-houseparty-google-next" target="_blank">House Party, Google Cloud Next</a> - Las Vegas - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/202605-global-open-house" target="_blank">Open House</a> - San Francisco - May 26, 2026

### Events in EMEA {#events-in-emea}

* <a href="https://clickhouse.com/company/events/202603-EMEA-Munich-meetup" target="_blank">ClickHouse Meetup Munich</a> - Munich - Mar 19, 2026
* <a href="https://talk.clickhouse.com/MjM4LUZQQy0zMTcAAAGgUzZGmrCsh9LwlOhfQ5S4NuJ8xeCq3mUb8G5BcC77kIWCSLiIIgxcRMEPPICfZJY68i42uok=" target="_blank">KubeCon EU</a> - Amsterdam, 23-26 March - Hall 2 stand 261
* <a href="https://talk.clickhouse.com/MjM4LUZQQy0zMTcAAAGgUzZGmmaesRMWlmOCJFDfP-dRMIF62KX7wSNwW7XuKDPuAZhovJEFU7vmLZKWfQhES_NXQvg=" target="_blank">DW&BI</a> - Utrecht, 24 March
* <a href="https://clickhouse.com/company/events/202603-EMEA-Milan-meetup" target="_blank">ClickHouse x Apache Kafka x AWS</a> - Milan - Mar 26, 2026
* <a href="https://ti.to/maltaawsusergroup/gen-a-i-day-malta/with/lpqxlepi9ww" target="_blank">ClickHouse x AWS x DoiT - Gaming and Betting AI Workshop</a> - Malta - March 26, 2026
* <a href="https://clickhouse.com/company/events/202603-EMEA-Benelux-Amsterdam-LunchandLearn" target="_blank">Lunch & Learn with ClickHouse</a> - Amsterdam - Mar 31, 2026
* <a href="https://aws.amazon.com/events/summits/paris/" target="_blank">AWS Summit Paris</a> - Paris, 1 April - Level 1 (nr. G3)
* <a href="https://clickhouse.com/company/events/202604-EMEA-Observability-with-ClickStack" target="_blank">Amsterdam In-Person Training: Observability with ClickStack</a> - Amsterdam - Apr 8, 2026
* <a href="https://events.confluent.io/awsstartupexchangevienna2026" target="_blank">CTO Networking Event with ClickHouse, AWS, Confluent & DoiT</a> - Vienna - Apr 9, 2026
* <a href="https://luma.com/wjv3v1tn" target="_blank">Scaling the Enterprise AI Operating Model</a> - Berlin - Apr 14, 2026
* <a href="https://clickhouse.com/company/events/202604-EMEA-Paris-Real-time-Analytics-w-ClickHouse" target="_blank">Paris In-Person Training: Real-time Analytics with ClickHouse</a> - Paris - Apr 15, 2026
* <a href="https://www.meetup.com/clickhouse-ireland-user-group/events/313793261" target="_blank">ClickHouse Meetup in Dublin</a> - Dublin - Apr 16, 2026
* <a href="https://grafana.com/events/grafanacon/" target="_blank">GrafanaCON</a> - Barcelona - Apr 20-22, 2026
* <a href="https://clickhouse.com/company/events/202604-EMEA-Barcelona-Real-time-Analytics-w-ClickHouse" target="_blank">Barcelona In-Person Training: Real-time Analytics with ClickHouse</a> - Barcelona - Apr 20, 2026
* <a href="https://aws.amazon.com/events/summits/london/" target="_blank">AWS Summit London</a> - London - Apr 22 - Booth G18
* <a href="https://riseof.ai/conference-2026/" target="_blank">Rise of AI Berlin</a> - Berlin - May 5-6, 2026
* <a href="https://aws.amazon.com/events/summits/tel-aviv/" target="_blank">AWS Summit Tel Aviv</a> - Tel Aviv - May 6, 2026
* <a href="https://datainnovationsummit.com/" target="_blank">Data Innovation Summit</a> - Stockholm - May 6-8, 2026
* <a href="http://gartner.com/en/data-analytics" target="_blank">Gartner Data & Analytics</a> - London - May 11-13, 2026
* <a href="https://www.revolutionbanking.es/" target="_blank">Revolution Banking</a> - Madrid - May 12, 2026
* <a href="https://www.platfor-ma.com/" target="_blank">Platforma 2026</a> - Tel Aviv - May 20, 2026
* <a href="https://aws.amazon.com/events/summits/hamburg/" target="_blank">AWS Summit Hamburg</a> - Hamburg - May 20, 2026
* <a href="https://clickhouse.com/company/events/202605-EMEA-London-Real-time-Analytics-w-ClickHouse" target="_blank">London 2-day In-Person Training: Real-time Analytics with ClickHouse</a> - London - May 19, 2026
* <a href="https://www.meetup.com/clickhouse-london-user-group/events/313759007/" target="_blank">ClickHouse Meetup London</a> - London - May 19, 2026
* <a href="https://cloudonair.withgoogle.com/events/cloud-ai-live-madrid-2026" target="_blank">Google Summit Madrid</a> - Madrid - May 28, 2026

### Events in APAC {#events-in-apac}

* <a href="https://2026.pythonasia.org/" target="_blank">Python Asia 2026</a> - Manila - Mar 21-22, 2026
* <a href="https://clickhouse.com/company/events/meetup-nz-23mar2026" target="_blank">Postgres + ClickHouse: Building a Real-Time Open-Source Data Stack</a> - Auckland - Mar 23, 2026
* <a href="https://dataengbytes.com/2026/auckland" target="_blank">DataEngBytes Auckland</a> - Mar 24, 2026
* <a href="https://clickhouse.com/company/events/meetup-nz-25mar2026" target="_blank">Wellington Data Eng Meetup</a> - Wellington - Mar 25, 2026
* <a href="https://clickhouse.com/company/events/202603-apj-shenzhen-meetup" target="_blank">ClickHouse Shenzhen Meetup</a> - Shenzhen - Mar 28, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Korea-Webinar-CDC-ClickPipes" target="_blank">Korean Webinar: CDC ClickPipes: 데이터베이스를 ClickHouse로 복제하는 가장 빠른 방법</a> - Apr 1, 2026
* <a href="https://events.confluent.io/dswt2026mumbai" target="_blank">Data Streaming World Mumbai</a> - Apr 13, 2026
* <a href="https://events.confluent.io/dswt2026bangalore" target="_blank">Data Streaming World Bangalore</a> - Apr 16, 2026
* <a href="https://clickhouse.com/company/events/taipei-open-source-meetup" target="_blank">Taipei Open Source Meetup</a> - Taipei - Apr 16, 2026
* <a href="https://www.meetup.com/clickhouse-bangalore-user-group/events/313739871/" target="_blank">Bangalore Meetup with Alexey Milovidov</a> - Apr 18, 2026
* <a href="https://events.confluent.io/dswt2026jakarta" target="_blank">Data Streaming World Jakarta</a> - Apr 21, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-Webinar-Unified-Data-Stack-ClickHouse-Postgres" target="_blank">APJ Webinar: Combining Postgres & ClickHouse to Build a Unified Data Stack</a> - Apr 22, 2026
* <a href="https://clickhouse.com/company/events/202604-APJ-HoChiMinh-Real-time-Analytics-with-ClickHouse" target="_blank">Ho Chi Minh In-Person Training: Real-time Analytics with ClickHouse</a> - Ho Chi Minh - Apr 22, 2026
* <a href="https://aws.amazon.com/events/summits/bengaluru/" target="_blank">AWS Summit Bengaluru</a> - Apr 22-23, 2026

## 26.2 release {#26-2-release}

![](/uploads/mar2026_nl_image9_00d00cbf75.png)

My favorite feature in the recent ClickHouse 26.2 release is time-based block flushing for streaming data. This lets you batch inserts by time interval rather than row count, which is useful for low-throughput feeds like Wikimedia recent changes.

The release also brings production-ready text index and QBit data types, 3.2x faster RIGHT/FULL JOINs, and embedded ClickStack for in-product observability.

➡️ <a href="https://clickhouse.com/blog/clickhouse-release-26-02" target="_blank">Read the release post</a>

## Building towards an enterprise-grade Postgres service in ClickHouse Cloud {#building-towards-an-enterprise-grade-postgres-service}

![](/uploads/mar2026_nl_image3_b4e4cebe10.jpg)

Sai Srirampur introduces the enterprise-grade Postgres service, coming soon to ClickHouse Cloud, bringing cross-AZ high availability, point-in-time recovery, automated backups, and failover-safe CDC slots for ClickHouse integration.

In other Postgres news, Kaushik Iska introduces <a href="https://clickhouse.com/blog/pg_stat_ch-postgres-extension-stats-to-clickhouse" target="_blank">pg_stat_ch</a>, an open-source Postgres extension that streams query metrics directly into ClickHouse for latency analysis and error tracking without impacting production performance.

➡️ <a href="https://clickhouse.com/blog/enterprise-postgres-service-in-clickhouse-cloud" target="_blank">Read the blog post</a>

## Announcing chDB 4: Write Pandas, Run ClickHouse, Now on Hex {#announcing-chdb-4}

![](/uploads/mar2026_nl_image10_d4f00e1d08.png)

Ryadh Dahimene and Auxten Wang introduce chDB 4, which adds a Pandas-compatible DataStore API that executes on ClickHouse's engine under the hood.

Operations run lazily as an optimized pipeline, with automatic routing between ClickHouse and Pandas engines, and it's now available natively in Hex notebooks.

➡️ <a href="https://clickhouse.com/blog/chdb.4-0-pandas-hex" target="_blank">Read the release post</a>

## How Trigger.dev built a custom SQL language on top of ClickHouse {#how-trigger-dev-built-a-custom-sql-language}

![](/uploads/mar2026_nl_image6_3d154dfd7b.png)

Matt Aitken, CEO of Trigger.dev, explains how they gave users SQL access to a shared multi-tenant ClickHouse cluster without risking data leaks.

Their solution is TRQL, a SQL-style DSL that compiles to tenant-isolated ClickHouse queries - dangerous operations are grammatically impossible, and tenant filters are injected at compile time.

➡️ <a href="https://trigger.dev/blog/how-trql-works" target="_blank">Read the blog post</a>

## Announcing General Availability of ClickHouse Full-text Search {#announcing-general-availability-of-full-text-search}

![](/uploads/mar2026_nl_image7_a3a43dd469.png)

Melvyn Peignon announces the general availability of full-text search in ClickHouse, which uses native inverted indexes to enable fast token-based filtering at scale.

The implementation supersedes Bloom filters for string matching, delivering deterministic results without false positives and reducing the number of granules scanned by up to 96%.

To see it in action, Lionel Palacin built <a href="https://clickhouse.com/blog/gittrends" target="_blank">GitTrends</a>, an open-source demo that searches and aggregates nearly 10 billion GitHub events in real time, with a live comparison tool showing the performance differences between full-text search, Bloom filters, and a full table scan.

➡️ <a href="https://clickhouse.com/blog/full-text-search-ga-release" target="_blank">Read the blog post</a>

## How we made payload search 60x faster in ClickHouse {#how-we-made-payload-search-60x-faster}

![](/uploads/mar2026_nl_image5_7eaefa6d9a.png)

Maurice Kherlakian at Hookdeck describes how webhook payload search across millions of semi-structured JSON records was timing out at 30+ seconds, making debugging nearly impossible.

The fix: hashing values into typed bucket columns so queries scan a single bucket instead of all, combined with iterative time-window scanning that stops once enough results are found, bringing latency down to under 400ms.

➡️ <a href="https://hookdeck.com/blog/how-we-made-payload-search-60x-faster-in-clickhouse" target="_blank">Read the blog post</a>

## The Agentic Data Stack {#the-agentic-data-stack}

![](/uploads/mar2026_nl_image1_9d8086f2d3.png)

Dustin Healy outlines an open-source agentic data stack that lets AI agents query ClickHouse directly via natural language, replacing dashboards and data tickets with real-time conversational access.

The architecture combines ClickHouse's MCP server with an open-source LLM interface and Langfuse for observability, keeping data and infrastructure under the user's control.

➡️ <a href="https://clickhouse.com/blog/the-agentic-data-stack" target="_blank">Read the blog post</a>

## ClickHouse TTL in production: A safe strategy for data retention and disk optimization {#clickhouse-ttl-in-production}

![](/uploads/mar2026_nl_image8_5f21853f75.png)

Aliakbar Hosseinzadeh shares a production runbook for implementing ClickHouse TTL policies after his cluster hit 97% disk utilization, covering the key mental model shift: TTL runs during background merges, not at insert time.

The winning combination is to align partitioning with your TTL time unit and `set ttl_only_drop_parts=1`, which lets ClickHouse drop whole parts cleanly rather than triggering expensive mutation-style rewrites.

➡️ <a href="https://medium.com/@aliakbarhosseinzadeh/clickhouse-ttl-in-production-a-safe-strategy-for-data-retention-and-disk-optimization-9f1546fe673f" target="_blank">Read the blog post</a>

## Quick reads {#quick-reads}

* Mark Needham <a href="https://clickhouse.com/blog/state-of-geospatial-march-2026" target="_blank">surveys everything ClickHouse can do with geospatial data in 2026</a> - from Geometry types and spatial operations to H3 grid-based analytics that run 12x faster than bounding-box queries on 10 million rows.
* Fiona J. Sylvester shows how to run <a href="https://medium.com/@fiona.j.sylvester/wait-you-can-do-anomaly-detection-directly-inside-a-database-ba6ec8dceb8c" target="_blank">STL-based anomaly detection directly in ClickHouse</a>, catching subtle deviations like a silent 4% transaction drop that would be missed by fixed thresholds.
* Parade suggests that <a href="https://medium.com/@parade4940/stop-calling-it-saas-why-you-should-dump-microsoft-fabric-and-use-clickhouse-for-data-analytics-62e73b3cffbe" target="_blank">Microsoft Fabric's shared capacity model creates unpredictable performance</a> and runaway costs, and that swapping in ClickHouse as the analytics engine keeps Power BI intact while cutting bills from tens to thousands of dollars a month.
* Tom Schreiber <a href="https://clickhouse.com/blog/table-cloning" target="_blank">explains how the CLONE AS command</a> creates instant copies of tables of any size by hard-linking immutable data parts rather than copying bytes, enabling safe experimentation on production-scale data with near-zero storage overhead.
* Shuva Jyoti Kar <a href="https://medium.com/google-cloud/agentic-threat-hunting-conversational-telemetry-with-clickhouse-the-new-mcp-java-sdk-and-google-bb3d98aff1f3" target="_blank">built a SecOps agent</a> that uses Google's MCP Toolbox and ClickHouse to let analysts query millions of rows of security telemetry in plain English, without exposing the schema or letting the LLM write raw SQL.
* Mohamed Hussain S explains how <a href="https://medium.com/@mohhddhassan/the-clickhouse-mental-model-most-engineers-miss-c1f39b18f46f" target="_blank">understanding two ClickHouse internals</a> - aggregation states and argMax - unlocks simpler, more powerful query design that would require subqueries and joins in traditional SQL databases.


Welcome to the March 2026 ClickHouse newsletter, which will round up what’s happened in real-time data warehouses over the last month.

large_Newsletter Cover Facebook Post (18).png

medium_Newsletter Cover Facebook Post (18).png

small_Newsletter Cover Facebook Post (18).png

thumbnail_Newsletter Cover Facebook Post (18).png

Newsletter Cover Facebook Post (18).png

March 2026 newsletter

Dates come in all shapes and sizes - Unix timestamps from event streams, weird looking numeric dates from legacy database exports, ISO 8601 strings from APIs, and more. Lucky for us, ClickHouse has a rich set of functions to handle all of them and that's what we're going to explore in this blog post.

We'll start with the most explicit approaches: converting Unix timestamps with `fromUnixTimestamp`, parsing packed numeric dates with `YYYYMMDDToDate`, and parsing known format strings with `parseDateTime`. Then we'll look at the `parseDateTimeBestEffort` family for when the format is unknown or mixed. 

Finally, we'll cover how casting dates with the `cast_string_to_date_time_mode` setting might be a better choice than explicit function calls for some use cases.

<iframe width="768" height="432" src="https://www.youtube.com/embed/hVEAzVz_xIY?si=-8QknStXsAO9iMjI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Unix timestamps {#unix_timestamps}

First up, Unix timestamps! Unix timestamps represent the number of seconds since January 1st, 1970. We can use the [`fromUnixTimestamp`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#fromUnixTimestamp) function to convert them:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp(1704067295) AS val1, toTypeName(val1);
</code></pre>

This returns a `DateTime` type. If you have milliseconds since January 1st, 1970, there's a different function — [`fromUnixTimestamp64Milli`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#fromUnixTimestamp64Milli) — and the type comes back as `DateTime64(3)`, where the `3` means precision up to milliseconds. 

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp64Milli(1704067295123) AS val2, toTypeName(val2);
</code></pre>

For microseconds, [`fromUnixTimestamp64Micro`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#fromUnixTimestamp64Micro) returns `DateTime64(6)`:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    fromUnixTimestamp64Micro(1704067295123456) AS val3, toTypeName(val3);
</code></pre>

## Numeric date formats {#numeric_date_formats}

Sometimes dates are represented as plain numbers encoding the year, month, and day — with no separators or formatting. This is common in legacy database exports or flat files from mainframes. The function [`YYYYMMDDToDate`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#YYYYMMDDToDate) handles this:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    YYYYMMDDToDate(20240115) AS val1, toTypeName(val1);
</code></pre>

If the number also includes time information, [`YYYYMMDDhhmmssToDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#YYYYMMDDhhmmssToDateTime) handles that too:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    YYYYMMDDhhmmssToDateTime(20240115143022) AS val2, toTypeName(val2);
</code></pre>

## Known format strings {#known_format_strings}

APIs often return dates as strings. If you know the format, you can use [`parseDateTime`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime) with a MySQL date format string:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    parseDateTime('15/01/2024 14:30:22', '%d/%m/%Y %H:%i:%s') AS val1,
    toTypeName(val1);
</code></pre>

This returns a `DateTime` including the timezone. 

If you prefer Joda date format strings, there's [`parseDateTimeInJodaSyntax`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTimeInJodaSyntax) which produces the same output:

<pre><code type='click-ui' language='sql' runnable='true'>
SELECT
    parseDateTimeInJodaSyntax('15/01/2024 14:30:22', 'dd/MM/yyyy HH:mm:ss') AS val2,
    toTypeName(val2);
</code></pre>

## Best effort parsing of DateTimes {#best_effort_parsing}

The previous three approaches all assumed we knew the exact date format. But what if we don't? That's where the [`parseDateTimeBestEffort`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTimeBestEffort) family of functions comes in. Imagine we have dates in a mix of different formats:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, parseDateTimeBestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

We can also convert to `DateTime64` using [`parseDateTimeBestEffort64`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffort), like the earlier functions:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

What happens if we include a completely invalid date? 

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffort(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

ClickHouse throws an exception! 

We can work around this with the [`parseDateTimeBestEffort64OrNull`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffortOrNull) variant, which returns `NULL` instead:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffortOrNull(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

Or if you'd rather get an actual datetime value, [`parseDateTimeBestEffort64OrZero`](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffortOrZero) falls back to January 1st, 1970 at midnight:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
    UNION ALL
    SELECT 'not a date' AS raw
)
SELECT raw, parseDateTime64BestEffortOrZero(raw) AS val, toTypeName(val)
FROM dates;
</code></pre>

## Casting {#casting}

If you'd rather avoid calling explicit parse functions throughout your queries, you can cast string values directly to date types using `::DateTime`. However, there's an important setting to be aware of: `cast_string_to_date_time_mode`.

By default it's set to `basic`, which handles standard formats like `YYYY-MM-DD` and `YYYY-MM-DD HH:MM:SS`, but anything else will fail. For broader format support, change it to `best_effort`. Note that this setting still throws an exception for completely invalid dates.

You can pass the setting inline per query:

<pre><code type='click-ui' language='sql' runnable='true'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM dates
SETTINGS cast_string_to_date_time_mode = 'best_effort';
</code></pre>

Or configure it at the session level so you don't need it in every query:

<pre><code type='click-ui' language='sql'>
SET cast_string_to_date_time_mode = 'best_effort';
</code></pre>

Then the same query works without the `SETTINGS` clause:

<pre><code type='click-ui' language='sql'>
WITH dates AS (
    SELECT '2024-01-15T14:30:22.000Z' AS raw
    UNION ALL
    SELECT '2024-01-15' AS raw
    UNION ALL
    SELECT '1704067295' AS raw
)
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM dates;
</code></pre>

Finally, imagine that we have the following file that contains a variety of dates:

*dates.csv*
```csv
raw
2024-01-15T14:30:22.000Z
2024-01-15
1704067295
```

We can parse the dates in that file using the same approach:

<pre><code type='click-ui' language='sql'>
SELECT raw, raw::DateTime AS val, toTypeName(val)
FROM file('dates.csv', CSVWithNames);
</code></pre>

```shell
┌─raw──────────────────────┬─────────────────val─┬─toTypeName(val)─┐
│ 2024-01-15T14:30:22.000Z │ 2024-01-15 14:30:22 │ DateTime        │
│ 2024-01-15               │ 2024-01-15 00:00:00 │ DateTime        │
│ 1704067295               │ 2024-01-01 00:01:35 │ DateTime        │
└──────────────────────────┴─────────────────────┴─────────────────┘
```

5 Ways to Parse Dates and DateTimes in ClickHouse

large_Image 1920x1080.jpg

medium_Image 1920x1080.jpg

small_Image 1920x1080.jpg

thumbnail_Image 1920x1080.jpg

Image 1920x1080.jpg

5 ways to parse Dates and DateTimes in ClickHouse

Linux, macOS, FreeBSD

Database compression turns analytical storage from an I/O-bound problem into a CPU-bound one. By stacking a column-aware encoding (dictionary, run-length, delta, Gorilla, frame-of-reference) under a general-purpose codec (LZ4 or ZSTD), columnar engines like ClickHouse hit 5-10× compression on typical analytical data and 30×+ on low-cardinality columns. On the ClickBench 100M-row benchmark, ClickHouse stores the dataset in 9.26 GiB compared with PostgreSQL's ~100 GiB, a 10× advantage driven by storage layout. This article covers how that compression works, when it backfires, and how to configure it.

## TL;DR

- Database compression has two regimes: row-based (page-level, 1.5-3× typical) and column-based (per-column encoding stacked under a codec, 5-10× typical, 30×+ on low-cardinality columns).
- Column-based compression is a two-stage stack: a column-aware encoding (dictionary, RLE, delta, delta-of-delta, Gorilla, frame-of-reference) below a general-purpose codec (LZ4, ZSTD, Snappy).
- ClickBench measures ClickHouse at 9.26 GiB vs PostgreSQL ~100 GiB on 100M rows. Customer-side, Character.AI reports averages of 15-20× across columns. Seemplicity reports 5-6× over Postgres.
- LZ4 decompresses 3-5× faster than ZSTD. ZSTD compresses ~30% smaller. Pick LZ4 for hot query paths, ZSTD for cold storage.
- Heavy codecs on high-cardinality strings make the query path CPU-bound, which is the worst-case failure mode.

## Why do databases compress data?

At the most fundamental level, compression reduces the number of bytes stored on disk. This means less data read during queries, which improves performance, and less storage space used overall, which reduces storage costs. 

A scan that reads 10 GiB instead of 100 GiB completes 10× faster on the same hardware, and costs 10× less to keep on disk or in object storage. The CPU spent decompressing is almost always cheaper than the I/O it saves. Storage volumes can easily run into terabytes or petabytes.  Without compression, those datasets quickly become expensive to manage and slow to query. 

| Benefit | Why it matters |
|---------|----------------|
| Lower storage costs | Disk space is not free. Whether you manage your own hardware or use cloud storage, fewer bytes means a smaller bill. |
| Faster queries | Reading less data from disk or network reduces I/O time. In analytical workloads, I/O is often the bottleneck. |
| Better cache utilization | More rows can fit into memory or CPU caches when compressed, leading to higher hit rates and faster repeated queries. |
| Cheaper data movement | Compression reduces the size of data replicated across nodes or shipped to backups. |
| CPU vs I/O trade-off | Decompression does use CPU, but in many real-world workloads the CPU cost is far outweighed by I/O savings. |

Compressed data not only saves money but also makes queries faster and enables use cases that wouldn’t be practical otherwise. For a system like ClickHouse, which thrives on scanning and aggregating massive datasets, getting compression right is one of the most important levers for both performance and cost efficiency.

## What types of compression exist in databases?

Database compression splits cleanly into two regimes by storage layout. Row-based engines compress at the page level on heterogeneous data. Columnar engines compress each column separately on homogeneous data. Compression ratios differ by an order of magnitude between the two because adjacent values in a column share a type and often a distribution.

### Row-based compression

Row-oriented databases like PostgreSQL, MySQL, and SQL Server group rows into fixed-size pages on disk. Compression happens at the page level. Each page is a block of mixed integers, strings, dates, and floats, and a general-purpose codec applied to that mix finds limited redundancy because adjacent bytes have nothing in common.

Row-based compression saves space, but ratios are usually modest. A typical Postgres TOAST + pg_lz pipeline achieves 1.5-3× on most analytical data. 

To see this in practice, [the ClickBench benchmark compared storage sizes across formats](https://benchmark.clickhouse.com/#system=+%E2%98%81w%7CgQ&type=-&machine=-a2l%7Cg4e%7C6ax%7Cae-l%7C6ale%7C3al%7Cg-l%7Co%E2%98%818&cluster_size=+1&opensource=-&tuned=+n&metric=size&queries=-). PostgreSQL, a row-based system, required far more space than ClickHouse for the same dataset of 100 million records:

![](/uploads/eng_resource_0_datacompression_7b602f8519.png)

### Column-based compression

Column-oriented databases store each column of a table separately on disk. Adjacent values in a column share a type (all UInt64, all DateTime, all Enum8) and often share a distribution (monotonic timestamps, low-cardinality enums, narrow numeric ranges). The same compressor sees orders of magnitude more redundancy and produces ratios 3-5× tighter than the same data compressed row-by-row, as Abadi, Madden, and Ferreira documented in [Integrating Compression and Execution in Column-Oriented Database Systems](http://www.cs.umd.edu/~abadi/papers/abadisigmod06.pdf) (SIGMOD 2006), the canonical reference on the subject.

Column-based engines layer two stages: a column-aware *encoding* that exploits per-column patterns, then a general-purpose *codec* that compresses the residual byte stream. ClickHouse, Parquet, ORC, and Arrow all follow this pattern with different vocabularies.

## How does column-based compression work?

Column-based compression works in two stages. An encoding (dictionary, run-length, delta, Gorilla, frame-of-reference) rewrites the column into a compact form that exploits its type and distribution. A general-purpose codec (LZ4, ZSTD, Snappy) then squeezes the residual bytes. Both stages are needed: encodings know patterns codecs cannot see, and codecs catch entropy encodings leave behind.

The encoding layer is type-aware. It knows that timestamps are likely monotonic, that boolean columns have two values, that an HTTP-status column probably has fewer than ten distinct values across a billion rows. The codec layer is type-blind: it sees a byte stream and looks for repetition. Stacking them compounds the ratio. A timestamp column might shrink 10× under delta-of-delta encoding, then a further 2× under LZ4, yielding 20× overall with each stage handling redundancy the other cannot see.

ClickHouse exposes this stack directly in DDL via the `CODEC` clause:

```sql
CREATE TABLE events (
  event_time DateTime CODEC(DoubleDelta, LZ4),
  user_id UInt64 CODEC(Delta, ZSTD(3)),
  country LowCardinality(String) CODEC(LZ4),
  metric Float64 CODEC(Gorilla, LZ4)
) ENGINE = MergeTree
ORDER BY (user_id, event_time);
```

In many cases, its not necessary to define encodings and codecs manually. Databases like ClickHouse ship with intelligent defaults that apply the right encodings and codecs for most cases, while offering the ability to optimise for specific cases.

The full set of available codecs is documented in [the ClickHouse compression docs](https://clickhouse.com/docs/sql-reference/statements/create/table).

### Dictionary encoding

Dictionary encoding stores the unique value set once, then represents each row as an index into that dictionary. A column with 200 distinct country names across a billion rows compresses by storing 200 strings plus a billion small integer indices. Every columnar engine implements some form of dictionary encoding: ClickHouse exposes it as the `LowCardinality(String)` type, Parquet uses `PLAIN_DICTIONARY`, Arrow uses `DictionaryArray`.

Dictionary encoding wins on cardinality below ~50k distinct values per block. Above that threshold the index width grows, the dictionary itself becomes large, and the technique loses to general-purpose codecs.

### Run-length encoding (RLE)

RLE encodes runs of identical adjacent values as `(value, count)` pairs. A sorted boolean column with 1M consecutive `true` values stored as `(true, 1000000)` is one of the highest compression ratios any technique can deliver. RLE wins on sorted, low-cardinality columns: partition keys, sorted boolean flags, sorted enum columns.

The catch is that RLE depends on physical sort order. The same column unsorted compresses by far less. ClickHouse exploits this by encouraging sort keys (`ORDER BY`) that put low-cardinality columns first, so the prefix gets an RLE-like benefit at no extra storage cost.

### Delta and delta-of-delta encoding

Delta encoding stores each value as the difference from the previous. A monotonic UInt64 ID column with values `1000, 1001, 1002, 1003` becomes `1000, 1, 1, 1`, and the residuals pack into a single byte each under bit-packing.

Delta-of-delta extends the idea to second-order differences. Timestamps sampled at a regular interval (`12:00:00.000, 12:00:00.500, 12:00:01.000`) have constant deltas, so the deltas of deltas are mostly zero. Facebook's [Gorilla paper](https://www.vldb.org/pvldb/vol8/p1816-teller.pdf) (VLDB 2015) reported delta-of-delta cutting timestamp storage by over 95% on production time-series workloads. ClickHouse exposes both as `CODEC(Delta)` and `CODEC(DoubleDelta)`, and they are correct defaults for any monotonic integer or timestamp column.

### Gorilla compression

Gorilla is a floating-point compression technique published by Facebook in the same 2015 VLDB paper. It XORs each value against the previous, then encodes the leading and trailing zero bits of the XOR. Consecutive values in time-series data tend to be close, so the XORs are small and most bits compress to zero. The original paper reported a 12× reduction on Facebook's production time-series database.

`CODEC(Gorilla)` in ClickHouse and the equivalent Chimp codec in Parquet 2.x both descend from this paper. Use Gorilla on `Float32` and `Float64` time-series metrics. Do not apply it to integer or string columns: the XOR pattern on those is irregular, and the encoding adds metadata overhead with no compression benefit.

### Frame-of-reference (FOR) and bit-packing

Frame-of-reference subtracts a base value from each entry, then bit-packs the residual into the smallest number of bits that fits the range. A column of integers between 1,000,000 and 1,000,255 has a range of 256, so eight bits per value suffices instead of 32 or 64. FOR + bit-packing underlies Parquet's `DELTA_BINARY_PACKED` encoding and Arrow's run-length and dictionary indices, and is one of the techniques BtrBlocks (Kuschewski et al., *BtrBlocks: Efficient Columnar Compression for Data Lakes*, SIGMOD 2023) used to beat ZSTD on numeric data while decompressing an order of magnitude faster. ClickHouse's `T64` codec applies the same idea to 64-bit integer columns.

### General-purpose codecs (LZ4, ZSTD, Snappy)

General-purpose codecs are the second stage. They compress the byte stream produced by the encoding layer. ClickHouse defaults to LZ4 for hot query paths and offers ZSTD with configurable levels (1-22) for cold storage. Parquet defaults to Snappy. ORC defaults to Zlib. The choice is a compression-ratio-versus-decompression-speed tradeoff, not a quality ranking.

| Codec | Typical ratio | Decompression (per core) | Use when |
|---|---|---|---|
| LZ4 | ~2× | 3-5 GB/s | Query path, hot columns |
| Snappy | ~2× | 2-4 GB/s | Lake defaults (Parquet) |
| ZSTD level 1-3 | ~2.5× | 1-2 GB/s | Mixed hot/cold |
| ZSTD level 9-19 | ~3× | 0.3-1 GB/s | Cold archival |
| Zlib | ~3× | 0.2-0.4 GB/s | ORC default, Hive ecosystem |

ZSTD compresses about 30% smaller than LZ4 at default levels but decompresses 2-3× slower. On a query that scans 100GB of LZ4-compressed data, the codec is invisible to wall-clock time. The same query against `ZSTD(19)` can become CPU-bound on the decoder.

## Does data compression affect data accuracy?

Database compression is almost always lossless. The compressed bytes decompress back to the exact original values. Dictionary, RLE, delta, Gorilla, and the general-purpose codecs (LZ4, ZSTD, Snappy, Zlib) all guarantee bit-for-bit fidelity.

What sometimes gets confused with compression is deliberate accuracy trade-offs: storing less precise versions of data to save space. Those are separate techniques, not lossy compression:

| Type | Meaning | Examples | Trade-offs |
|---|---|---|---|
| Lossless compression | Decompresses to the exact original bytes. | Dictionary, RLE, delta, LZ4, ZSTD, GZIP | Guarantees accuracy. Savings depend on redundancy. |
| Lossy reduction | Stores a reduced or approximate form. Some information is lost. | Sampling, aggregation states, reduced precision (seconds vs microseconds) | Larger savings. Cannot reconstruct raw rows. |

When teams use the word "compression" they almost always mean the lossless kind. Lossy reductions are usually exposed as separate features (sampled tables, materialised views aggregating to AggregateFunction state, lower-precision data types).

## What are best practices for database compression?

The compression ratio you see in production depends as much on schema decisions as on codec choice. Five practices move the needle:

1. **Choose the smallest data types that fit.** Int16 instead of Int64. Float32 instead of Float64 when full precision isn't needed. Smaller types give general-purpose codecs less work and shrink the column before any encoding runs.
2. **Use enums or `LowCardinality` for low-cardinality strings.** Status codes, country names, log levels, and HTTP methods all benefit. Dictionary encoding does the rest of the work automatically.
3. **Avoid unnecessary precision.** A timestamp truncated to seconds compresses better than the same timestamp at microsecond resolution. Latitude/longitude as Float32 instead of Float64 is usually indistinguishable in practice.
4. **Match codecs to data patterns.** Delta and DoubleDelta for monotonic columns. Gorilla for float time-series. RLE for sorted low-cardinality. Dictionary for unsorted low-cardinality strings. T64 or FOR + bit-packing for bounded numeric ranges.
5. **Measure per column.** ClickHouse's `system.parts_columns` shows compressed and uncompressed bytes per column. The columns furthest from a 5× ratio are usually where mis-matched codecs hide.

The decision matrix below maps the most common column shapes to the encoding + codec combination that wins them:

| Column type | Cardinality | Pattern | Encoding | Codec |
|---|---|---|---|---|
| String enum (status, country) | Low (<10k) | Mixed | Dictionary / `LowCardinality` | LZ4 |
| String free-text (logs, UA) | High | None | None | ZSTD |
| Integer ID, monotonic | High | Monotonic | `Delta` + T64 | LZ4 |
| Timestamp, regular interval | High | Constant deltas | `DoubleDelta` | LZ4 |
| Float metric, time-series | High | Slow-changing | `Gorilla` | LZ4 |
| Boolean, sorted | Binary | Sorted | RLE (via `ORDER BY`) | LZ4 |
| Bounded numeric range | Bounded | Any | T64 / FOR + bit-packing | LZ4 |

### Worked example: NOAA weather data

The NOAA Global Historical Climatology Network is a public dataset of decades of daily weather observations. Loaded into ClickHouse with naive types, the table compresses around 3-4×. After a series of schema-level optimisations:

- Narrowed integer types from Int64 to Int16 where the value range allowed.
- Converted weather-type strings to Enum8 (a fixed set of values).
- Dropped float precision: latitude/longitude from Float64 to Float32.
- Applied `Delta + ZSTD` codecs to time-series columns.

The compressed size dropped substantially without any change to query semantics. Detailed steps are in the [ClickHouse blog write-up on optimising codecs and compression schema](https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema).

### When does compression backfire?

Compression backfires when the CPU cost of the codec outpaces the I/O it saves. The two main failure modes are heavy codecs on high-cardinality data and over-aggressive level tuning on hot query paths. A third, subtler failure is using sort-order-dependent encodings on physically unsorted columns, where the metadata overhead exceeds any compression gain.

A free-text string column (log messages, user-agent strings, JSON blobs) has high entropy. Dictionary encoding fails because every value is unique. FOR fails because there is no numeric range. The general-purpose codec is doing all the work. Stacking `ZSTD(19)` on top reaches ~3× ratio at the cost of a query path 5-10× slower than LZ4. The right choice on such a column is often LZ4 alone, or moving the column into a separate, rarely-read part.

The second failure is treating the codec level as a tuning knob. Going from `ZSTD(3)` to `ZSTD(9)` typically buys 5-10% additional compression at 2-3× the decompression cost. Going from `ZSTD(9)` to `ZSTD(19)` buys another 3-5% at another 2× cost. The marginal compression flattens fast while the marginal decompression cost stays linear. For cold columns, `ZSTD(3)` is a common stopping point. Beyond that, the math rarely justifies itself.

The third failure is encodings that depend on sort order applied to unsorted columns. RLE on an unsorted boolean column adds metadata overhead with no compression benefit. Always check that the sort key matches the encoding assumption before adding `CODEC(...)` clauses.

## What are the best high data compression databases?

Not all databases are equally efficient when it comes to storage. To see the difference, we can look at results from the [ClickBench](https://benchmark.clickhouse.com/#system=+%E2%98%81w%7CDcD%7Cltrc%7CgQ%7CtR%7Cm%E2%98%81&type=-&machine=-a2l%7C6t%7Cg4e%7C6ax%7Cae-l%7C6ale%7C3al%7Cg-l%7Co%E2%98%818%7Ci4P%7CT6%20C&cluster_size=+1&opensource=-&tuned=+n&metric=size&queries=-) benchmark, which compares several popular systems on the same dataset.  

![ClickBench storage comparison](/uploads/eng_resource_1_datacompression_d06d271a91.png)

The results are striking. ClickHouse stores the dataset in just 9.26 GiB, while PostgreSQL requires almost 100 GiB - more than ten times larger. Other systems, including Elasticsearch and Timescale, fall somewhere in between but still lag significantly. Even highly optimized formats like Parquet or compressed CSV/TSV files can’t match ClickHouse.

This outcome isn’t surprising given what we discussed earlier: ClickHouse’s columnar layout, per-column codecs, and ordering-aware compression allow it to shrink data much more aggressively than row-based systems or general-purpose file formats.

And these gains aren’t limited to benchmarks. Real-world users see the same results in production:

Character.AI, which ingests massive streams of observability data from thousands of GPUs, relies on ClickHouse to keep storage under control:

> *“I was genuinely impressed by the compression we achieved with ClickHouse. Some columns gave us 10x, others 20x - even up to 50x in some cases. On average, we're seeing 15–20x compression!”*  
> — [Character.AI](https://clickhouse.com/blog/scaling-observabilty-for-thousands-of-gpus-at-character-ai)  

Seemplicity, a security analytics platform processing tens of billions of updates per month, found that compression was a key factor in scaling beyond Postgres:  

> *“Compression has also been a major win. Seemplicity’s ClickHouse footprint sits at around 10 terabytes - a fraction of their Postgres instance. I’d guess Postgres is 5 to 6 times bigger. That space efficiency, plus the ability to stream tens of billions of updates per month, gives them room to grow without worrying about bloat or system strain.”*  
> — [Seemplicity](https://clickhouse.com/blog/seemplicity-scaled-real-time-security-analytics-with-postgres-cdc-and-clickhouse)  

Together, the benchmark and these customer experiences make one thing clear: if you’re looking for the best high data compression database available today, ClickHouse stands out from the pack.

Frequently asked questions

Database compression: encodings, codecs and ratios

A data lakehouse is a data architecture that combines the low-cost, open storage of a data lake with the performance and governance of a data warehouse. It stores data in open formats on cloud object storage, so the same dataset can serve BI, large-scale analytics, and AI/ML workloads without duplication or movement.

The architecture emerged as organizations ran into the limits of running a data lake and a data warehouse in parallel. Lakes gave them cheap, flexible storage but poor query performance and weak governance. Warehouses gave them structure and speed but at a premium price, on proprietary formats, with tightly coupled compute. The lakehouse applies database principles — schema enforcement, ACID transactions, metadata management — directly to the open files in object storage, which removes the need to copy data between two systems to support both sets of workloads.

Mark Needham

Blog posts by Mark Needham

Comparing ClickHouse versions with clickhousectl

April 2026 newsletter

Index-based pruning in ClickHouse

Querying DateTimes in ClickHouse

March 2026 newsletter

5 ways to parse Dates and DateTimes in ClickHouse

Resources by Mark Needham

Database compression: encodings, codecs and ratios

What is a data lakehouse?

What is Real-Time Analytics? A Complete Guide (2026)

What is a columnar database?