At the end of every year, the ClickHouse engineering team works on some features that we call [Christmas gifts](https://github.com/ClickHouse/ClickHouse/pulls?q=is:pr+label:%22%F0%9F%8E%85+%F0%9F%8E%81+gift%F0%9F%8E%84%22+is:closed%20). These are features with a clear visible improvement, usability features, or long-awaited things.

In this blog post, we’re going to go through some of our favorites.

> And don’t forget, you can see release posts for every release in 2025 via the links below:  
[25.1](https://clickhouse.com/blog/clickhouse-release-25-01), [25.2](https://clickhouse.com/blog/clickhouse-release-25-02), [25.3 LTS](https://clickhouse.com/blog/clickhouse-release-25-03), [25.4](https://clickhouse.com/blog/clickhouse-release-25-04), [25.5](https://clickhouse.com/blog/clickhouse-release-25-05), [25.6](https://clickhouse.com/blog/clickhouse-release-25-06), [25.7](https://clickhouse.com/blog/clickhouse-release-25-07), [25.8 LTS](https://clickhouse.com/blog/clickhouse-release-25-08), [25.9](https://clickhouse.com/blog/clickhouse-release-25-09), [25.10](https://clickhouse.com/blog/clickhouse-release-25-10), [25.11](https://clickhouse.com/blog/clickhouse-release-25-11), [25.12](https://clickhouse.com/blog/clickhouse-release-25-12)

## Highlight digit groups inside numbers in the query prompt

### Contributed by Alexey Milovidov

Digit groups inside numbers in query prompts are now highlighted in a similar way that numbers are formatted in results.

You can see an example of how this works in the video below:


ChristmasGifts.mp4

## Numeric hint when last column and enough horizontal space

### Contributed by Cole Smith

Since [ClickHouse 24.2](https://clickhouse.com/blog/clickhouse-release-24-02#pretty-format-is-even-prettier), when you return a single numerical column, if the value in that column is bigger than 1 million, the readable quantity will be displayed as a comment alongside the value itself.

<pre><code type='click-ui' language='sql'>
SELECT 277282240574747
</code></pre>

```shell
┌─arrayJoin([277282240574747])─┐
│              277282240574747 │ -- 277.28 trillion
└──────────────────────────────┘
```

But if we returned multiple values, it wouldn’t show the numeric hint:

<pre><code type='click-ui' language='sql'>
SELECT arrayJoin([
 277282240574747, 
 2772822, 
 1543210
]);
</code></pre>

```shell
┌─arrayJoin([2⋯, 1543210])─┐
│          277282240574747 │
│                  2772822 │
│                  1543210 │
└──────────────────────────┘
```

Now we’ll get the numeric hint for every row as long as there’s enough horizontal space:

```shell
┌─arrayJoin([2⋯, 1543210])─┐
│          277282240574747 │ -- 277.28 trillion
│                  2772822 │ -- 2.77 million
│                  1543210 │ -- 1.54 million
└──────────────────────────┘
```

## Optimize distinctJSONPaths aggregate function

### Contributed by Pavel Kruglov

The [distinctJSONPaths](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/distinctjsonpaths) function returns the set of unique JSON paths present in a JSON column.

Starting with ClickHouse 25.12, this function is significantly optimized. Instead of scanning and decoding the [full internal JSON column representation](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse#true-columnar-json-storage), ClickHouse now relies on metadata that is already maintained at data-part level. 

Each data part tracks the set of JSON paths it contains. When distinctJSONPaths is executed, ClickHouse simply reads and merges this metadata across parts, without accessing the JSON column data itself.

Let’s see the performance impact using the BlueSky dataset. The following query ingests 10 million rows into a table:

<pre><code type='click-ui' language='sql'>
CREATE TABLE bluesky
ORDER BY ()
AS
SELECT *
FROM s3(
  'https://clickhouse-public-datasets.s3.amazonaws.com' ||
  '/bluesky/file_{0001..0010}.json.gz',
  'JSONAsObject'
)
SETTINGS
    input_format_allow_errors_num = 100,
    input_format_allow_errors_ratio = 1;
</code></pre>

We’re going to compute the distinct JSON paths in this dataset by running the following query:

<pre><code type='click-ui' language='sql'>
SELECT length(distinctJSONPaths(json)) 
FROM bluesky;
</code></pre>

If we run this query three times in ClickHouse 25.11:

```shell
┌─length(disti⋯aths(json))─┐
│                      224 │
└──────────────────────────┘

1 row in set. Elapsed: 4.978 sec. Processed 9.97 million rows, 21.21 GB (2.00 million rows/s., 4.26 GB/s.)
Peak memory usage: 1.24 GiB.

1 row in set. Elapsed: 4.563 sec. Processed 9.99 million rows, 21.26 GB (2.19 million rows/s., 4.66 GB/s.)
Peak memory usage: 1.26 GiB.

1 row in set. Elapsed: 4.926 sec. Processed 9.94 million rows, 21.14 GB (2.02 million rows/s., 4.29 GB/s.)
Peak memory usage: 1.24 GiB.
```

And then three times in ClickHouse 25.12:

```shell
┌─length(disti⋯aths(json))─┐
│                      224 │
└──────────────────────────┘

1 row in set. Elapsed: 0.082 sec.

1 row in set. Elapsed: 0.099 sec.

1 row in set. Elapsed: 0.100 sec.
```

Our fastest result goes from 4.563 seconds to 82 milliseconds, an improvement of more than 50 times.

## Optimize DISTINCT transform for LowCardinality Columns

### Contributed by Nihal Z. Miaji

[LowCardinality](https://clickhouse.com/docs/sql-reference/data-types/lowcardinality) columns store values using [**dictionary encoding**](https://en.wikipedia.org/wiki/Dictionary_coder): each distinct value is stored once in a dictionary, and rows reference it by a small integer ID.

Starting with ClickHouse 25.12, `DISTINCT` queries on LowCardinality columns are optimized to **exploit this dictionary representation**.

Instead of scanning all rows and comparing full values, ClickHouse operates directly on the **dictionary IDs**:

* It tracks which dictionary entries have already been seen

* Each dictionary value is returned at most once

* The scan can stop early once all dictionary entries are encountered

As a result, the cost of DISTINCT depends primarily on the **dictionary size**, not the number of rows.

Let’s have a look at the effect with the [UK property prices dataset](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid). This dataset has the following columns with the `LowCardinality` type:

```shell
postcode1 LowCardinality(String)
postcode2 LowCardinality(String)
street LowCardinality(String)
locality LowCardinality(String)
town LowCardinality(String)
district LowCardinality(String)
county LowCardinality(String)
```

We’re going to run the following query three times against each column:

<pre><code type='click-ui' language='sql'>
SELECT DISTINCT <field>
FROM uk.uk_price_paid
FORMAT NULL;
</code></pre>

For each of the fields on 25.11 and 25.12, record the fastest time. You can see the results in the table below:

| Field | Unique values | 25.11 | 25.12 |
| :---- | :---- | :---- | :---- |
| postcode1 | 2,392 | 0.017 sec | 0.033 sec |
| postcode2 | 4,005 | 0.121 sec | 0.043 sec |
| street | 334,642 | 0.186 sec | 0.084 sec |
| locality | 24,001 | 0.149 sec | 0.070 sec |
| town | 1,172 | 0.141 sec | 0.046 sec |
| district | 467 | 0.144 sec | 0.046 sec |
| county | 132 | 0.141 sec | 0.023 sec |

We can see that the optimization results in a faster query for every column except `postcode1`. 

The sorting key for this table is `(postcode1, postcode2, addr1, addr2)`, which explains why the optimization isn’t as effective on the `postcode1` column. 

The "early exit" optimization assumes you'll discover all unique values early. But when doing `DISTINCT` on `postcode1`, the unique values are spread from beginning to end by definition. We have to scan (almost) everything.

Therefore, for `postcode1`, we pay the overhead of tracking dictionary indices while still scanning nearly the full column, making it slower than the previous approach of scanning all rows.

## Use equivalent sets to push down filter for SEMI JOIN

### Contributed by Dmitry Novik

A semi join is a way of filtering a rowset based on the existence of its rows in another rowset.

A `LEFT SEMI JOIN` returns rows from the left table where matching rows exist in the right table. A `RIGHT SEMI JOIN` returns rows from the right table where matching rows exist in the left table. 

> You can read a more detailed explanation in [part 1 of our series on ClickHouse joins](https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1#left--right-semi-join).

Let’s have a look at an example of each type of semi join, starting with `LEFT SEMI JOIN`:

<pre><code type='click-ui' language='sql'>
-- Return users who have orders
SELECT * FROM users 
LEFT SEMI JOIN orders ON users.id = orders.user_id
</code></pre>

And `RIGHT SEMI JOIN`:

<pre><code type='click-ui' language='sql'>
-- Return orders that have matching users
SELECT * FROM users 
RIGHT SEMI JOIN orders ON users.id = orders.user_id
</code></pre>

ClickHouse 25.12 optimizes `SEMI JOIN` queries by pushing filters down earlier in the query execution. This means that when you have a SEMI JOIN with filter conditions, the optimizer can now push those filters down to the table scan level using "equivalent column sets" - columns that are known to be equal through the join condition.

Without filter pushdown, you might scan the entire right (or left) table before filtering. With this optimization, you filter first, then check existence against a smaller dataset.

Let’s have a look at how this optimization works with help from the UK property prices dataset. The following query finds 2023 sales where the property also sold in 2022

<pre><code type='click-ui' language='sql'>
 SELECT DISTINCT street, town, postcode1, date, price
  FROM uk_price_paid p1
  LEFT SEMI JOIN uk_price_paid p2
    ON p1.addr1 = p2.addr1
    AND p1.street = p2.street
    AND p1.postcode1 = p2.postcode1
    AND p2.date BETWEEN '2022-01-01' AND '2022-12-31'
  WHERE p1.date BETWEEN '2023-01-01' AND '2023-12-31'
  ORDER BY p1.price DESC
  LIMIT 10;
</code></pre>

```shell
┌─street────────────┬─town─────────┬─postcode1─┬───────date─┬────price─┐
│ MOULSECOOMB WAY   │ BRIGHTON     │ BN2       │ 2023-05-03 │ 97483948 │
│ GLEBE PLACE       │ LONDON       │ SW3       │ 2023-10-18 │ 68250000 │
│ THREE QUEENS LANE │ BRISTOL      │ BS1       │ 2023-12-28 │ 55859292 │
│ MARGATE ROAD      │ BROADSTAIRS  │ CT10      │ 2023-04-20 │ 54077847 │
│ BATH ROAD         │ WEST DRAYTON │ UB7       │ 2023-10-20 │ 54000000 │
│ PARK STREET       │ LONDON       │ W1K       │ 2023-09-07 │ 52625000 │
│ LINKS ROAD        │ MORPETH      │ NE65      │ 2023-02-28 │ 50090677 │
│ BILTON WAY        │ ENFIELD      │ EN3       │ 2023-08-18 │ 50017255 │
│ OLYMPIC WAY       │ WEMBLEY      │ HA9       │ 2023-08-04 │ 43096020 │
│ BLACKACRE ROAD    │ IPSWICH      │ IP6       │ 2023-11-14 │ 36990000 │
└───────────────────┴──────────────┴───────────┴────────────┴──────────┘
```

If we run this query against 25.11:

```shell
10 rows in set. Elapsed: 1.134 sec. Processed 59.22 million rows, 1.22 GB (52.22 million rows/s., 1.08 GB/s.)

10 rows in set. Elapsed: 1.295 sec. Processed 57.69 million rows, 1.18 GB (44.56 million rows/s., 913.69 MB/s.)

10 rows in set. Elapsed: 1.320 sec. Processed 60.90 million rows, 1.26 GB (46.13 million rows/s., 952.93 MB/s.)
```

And against 25.12:

```shell
10 rows in set. Elapsed: 0.818 sec. Processed 60.18 million rows, 940.56 MB (73.55 million rows/s., 1.15 GB/s.)

10 rows in set. Elapsed: 0.800 sec. Processed 60.90 million rows, 953.67 MB (76.12 million rows/s., 1.19 GB/s.)

10 rows in set. Elapsed: 0.741 sec. Processed 55.61 million rows, 856.55 MB (75.02 million rows/s., 1.16 GB/s.)
```

If we take the quickest runs for each, the query runs about 30% faster in ClickHouse 25.12. We can also see that about 30% less data is read from disk.

A yearly tradition: we tag a handful of features as gifts from our engineering team.

large_Christmas Gifts Blog Banner.jpg

medium_Christmas Gifts Blog Banner.jpg

small_Christmas Gifts Blog Banner.jpg

thumbnail_Christmas Gifts Blog Banner.jpg

Christmas Gifts Blog Banner.jpg

Christmas gifts from the ClickHouse engineering team

I enjoy reading blogs about performance optimizations, data structures and algorithms, database development, compilers, operating systems, programming languages, computer science and mathematics, hardware and networks, web development, computer graphics, and AI. I'll share some of my favorites with you, to help you survive the holidays.

## [Daniel Lemire](https://lemire.me/blog/)

Daniel is the author of many high-performance libraries and algorithms. His articles cover details of low-level performance optimizations, and he often finds opportunities to improve fundamental building blocks and computational kernels, used in every program, like parsing IP addresses, validating UTF-8, compressing sequences of numbers, converting numbers to strings, etc. His libraries and algorithms are adopted in ClickHouse too, for example, [simdjson](https://github.com/ClickHouse/ClickHouse/pull/5124) and [Roaring bitmap](https://github.com/ClickHouse/ClickHouse/pull/4207). We have also contributed numerous bug fixes back to these libraries.

## [Ash Vardanyan](https://ashvardanian.com/archives/)

Ash is one of the most visible enthusiasts of performance optimizations and hardware. He is the author of USearch (a library for vector search with HNSW) and StringZilla (a collection of computational kernels for string processing), and both of them are used in ClickHouse.

## [Wojciech Muła](http://0x80.pl/notesen.html)

Highly optimized computational kernels with and without SIMD instructions. For some reason, this blog does not use HTTPS, but that does not diminish its value.

## [Daniel Kutenin](https://danlark.org/)

Daniel is an expert in performance optimizations, algorithms, and compilers. He is also a [ClickHouse contributor](https://github.com/ClickHouse/ClickHouse/pulls?q=is%3Apr+author%3Adanlark1+is%3Aclosed), where he has implemented many string algorithms, such as an adaptation of Volnitsky's substring search for case-insensitive strings and for searching multiple strings at once (better than Aho-Corasick on a small number of strings), integrated Hyperscan (now Vectorscan) for multi and fuzzy search, and added string distances. Most importantly, he is the author of [Miniselect](https://github.com/danlark1/miniselect) which is a library for partial sorting, also integrated into ClickHouse.

## [Fangrui Song (MaskRay)](https://maskray.me/)

This is a blog about compilers, linkers, and executable binary formats. The amount of information in this blog will make you worry about yourself.

## [Yann Collet](https://fastcompression.blogspot.com/)

Yann is the author of lz4 and zstd, one of the most important libraries for databases. I [integrated LZ4](https://github.com/ClickHouse/ClickHouse/commit/58110f5cde5f3ececf2b439ab8cc36748559012d) in ClickHouse in 2012, and it was fantastic! I remember how I was amazed and eager to see his zhuff algorithm open-sourced, but he did even more by releasing zstd in 2015, which I immediately [integrated in ClickHouse](https://github.com/ClickHouse/ClickHouse/commit/7d11fc0931267ca4468c3379f358d7345a692664), replacing quicklz. We even found a few bugs in the first 0.x versions of zstd :) I spent a lot of time trying to understand the mechanics of LZ4, and ended up [optimizing it just a little](https://clickhouse.com/blog/lz4-compression-in-clickhouse).

Yann now works at Facebook, and his personal blog stopped being updated. For more resources about compression, you can check the [data compression forum](https://encode.su/) and read [Data Compression Explained](https://mattmahoney.net/dc/dce.html) from Matt Mahoney.

## [Dan Luu](https://danluu.com/)

Dan has many great articles about [CPU](https://danluu.com/hardware-unforgiving/) and [performance profiling](https://danluu.com/perf-tracing/).

## [Brendan Gregg](https://www.brendangregg.com/blog/index.html)

If you are interested in performance profiling, introspection, instrumentation, and tracing, read this blog.

## [Russ Cox](https://research.swtch.com/)

Russ is the author of the re2 library, he writes about algorithms amongst other topics. Re2 was a revolution for us - I still remember how colleagues tried to convince me that giving end users access to queries with arbitrary regular expressions would end up with DoS, but I knew it was going to be alright because [I use re2](https://github.com/ClickHouse/ClickHouse/commit/ea816297c2ae16b50a20ebd684471a535b59e6b0)! This was proven by practice - in 15 years, there was not a single case to lose trust in re2. There are faster libraries, like [vectorscan/hyperscan](https://github.com/ClickHouse/ClickHouse/pull/4816) and more versatile libraries, but re2 remains unbeaten. This article about [uninitialized memory](https://research.swtch.com/sparse) is especially interesting.

## [Bruce Dawson](https://randomascii.wordpress.com/)

Bruce has many articles about [floating point numbers](https://randomascii.wordpress.com/category/floating-point/).

## [Probably Dance](https://probablydance.com/)

This blog by Malte Skarupke is amazing. Scroll down to find the tech articles. He [didn't write the fastest hash table](https://probablydance.com/2017/02/26/i-wrote-the-fastest-hashtable/).

## [Alisa Sireneva](https://purplesyringa.moe/blog/)

Amazing blog about low-level optimizations, algorithms, and compilers. Don't worry, you can easily skip the articles about Rust if you want.

## [Kamila Szewczyk](https://iczelia.net/posts/)

Good articles about data compression, such as [this](https://iczelia.net/posts/lz-ent-detection/).

## [Salvatore Sanfilippo](https://antirez.com/)

A blog from the author of Redis. He writes rarely, and not only about AI. I really liked his smaz compression library and linenoise (an alternative to readline). He writes about experiments and observations from a first person perspective, and he is able to rethink some ideas and approaches without influence. It's worth noting that Redis lacks multithreading, it does not have on-disk data structures, and it does not implement distributed consensus. Finally, its development speed is very low, and the author uses a very conservative approach to software development. That's why there are many Redis clones, but none of them are comparable in terms of its reach.

## [Mitchell Hashimoto](https://mitchellh.com/writing)

This blog isn't about databases. It's great to read about the first-person perspective of the founder of Hashicorp, as well as the development of a terminal emulator in Zig.

## [Fabien Sanglard](https://fabiensanglard.net/)

This blog is also not about databases, but there is a lot of good stuff in it, such as the article about [pseudorandom permutations](https://fabiensanglard.net/fizzlefade/index.php).

## [Ben E. C. Boyter](https://boyter.org/)

Ben is the author of scc - a tool for counting lines of code. I especially liked his article about [analyzing GitHub](https://boyter.org/posts/an-informal-survey-of-10-million-github-bitbucket-gitlab-projects/).

## [Nikita Lapkov](https://laplab.me/posts/)

Nikita has many good articles about databases. He is also a ClickHouse contributor, where he [implemented](https://github.com/ClickHouse/ClickHouse/pull/4247) the first version of [always-on profiler](https://presentations.clickhouse.com/2019-yatalks-moscow/).

## [Simon Willison](https://simonwillison.net/)

Simon writes about AI. Even if you already get nauseous from this topic, don't run away, because Simon writes about first-person, hands-on experience with new models and tools. The articles are concise and unpretentious, which makes them pleasant to read.

## [Andrej Karpathy](https://karpathy.github.io/)

This is a great blog about AI, with articles like [this](https://karpathy.github.io/2015/05/21/rnn-effectiveness/) you should probably have read ten years ago. Today he is active on [YouTube](https://www.youtube.com/@AndrejKarpathy/videos).

## [Jay Mody](https://jaykmody.com/)

This is such a great blog about AI! Only five articles, though.

## [Kyle Kingsbury](https://jepsen.io/blog)

He tests the consistency of distributed databases, and there is hardly any database that survives his testing. Kyle is the author of the Jepsen framework, which is also [used in ClickHouse](https://github.com/ClickHouse/ClickHouse/tree/master/tests/jepsen.clickhouse), as well as in any self-respecting distributed DBMS.

## [Database Architects](https://databasearchitects.blogspot.com/)

A blog from developers of academic databases from TUM and CWI. The covered topics are often very similar to the development of ClickHouse.

## [DBMS Musings by Daniel Abadi](https://dbmsmusings.blogspot.com/)

A similar blog about databases, mostly column-oriented. Not updated since 2019.

## [Marc Brooker](https://brooker.co.za/blog/)

A blog about databases and distributed systems.

## [Mark Callaghan](https://smalldatum.blogspot.com/)

The last articles are all about how he compiled a certain version of MySQL or PostgreSQL and compared performance. It's very boring. I like this blog.

## [Raymond Chen](https://devblogs.microsoft.com/oldnewthing/)

A lot of interesting curiosities from the history of the development of Windows.

## [Bartosz Ciechanowski](https://ciechanow.ski/archives/)

This blog isn't about databases or AI, and the articles appear very rarely. This is the best blog I've ever seen.

## [Justine Tunney](https://justine.lol/)

She is the author of Cosmopolitan Libc, and the blog is great to read if you are interested in binary formats, linkers and loaders, machine instructions, operating systems, and hardware. Note that ClickHouse does not use Cosmopolitan Libc, as it is still hard to apply for large C++ applications. We have a build with Musl-libc, and I also hope that LLVM libc will eventually be suitable for ClickHouse.

## [Alex Kladov](https://matklad.github.io/)

Numerous articles discuss low-level code and performance. Sometimes about Rust and Zig, but should you worry?

## [LLVM blog](https://blog.llvm.org/)

If you are interested in compilers.

## [LWN](https://lwn.net/)

If you are interested in operating systems.

## [v8](https://v8.dev/blog)

If you are interested in web browsers.

## [Julia Evans](https://jvns.ca/)

A collection of brief notes on things worth knowing.

## [Rachel](https://rachelbythebay.com/w/)

Many small observations about tech.

## [Charles Bloom](https://cbloomrants.blogspot.com/)

Many good articles about data compression. From this blog, I learned the term ["cache table"](https://cbloomrants.blogspot.com/2010/11/11-19-10-hashes-and-cache-tables.html), and cache tables are widely used in ClickHouse, for cache dictionaries, for data compression, and for small caches.

## [Peter Kankowski](https://www.strchr.com/)

Low-level performance optimization tricks. For some reason, it is not updated anymore, but some of the old articles are golden.

## [Jeff Preshing](https://preshing.com/)

Also not updated, but there are many good articles, like this one about [pseudorandom permutations](https://preshing.com/20121224/how-to-generate-a-sequence-of-unique-random-integers/).

## [Marek](https://idea.popcount.org/)

It's been a long time since the last update, but there are many good articles, e.g., about [bitslicing](https://idea.popcount.org/2013-01-30-bitsliced-siphash/).

## Thoughts

While going through my favorite blogs, I've noticed that some of them have sadly stopped updating. Some blogs even disappeared, like a blog of Yury Lifshits about string and nearest neighbor algorithms or a blog of Eugene Kirpichev about functional data structures, such as [incremental regular expressions](http://jkff.info/articles/ire/). Sometimes there are rumors that the authors are still alive and continue to write somewhere on ~~darknet~~Facebook, but about therapy, philosophy, or parenting. What I really want is for them to continue writing great articles about technology!

A collection of my favorite technology blogs

large_perf.png

medium_perf.png

small_perf.png

thumbnail_perf.png

perf.png

Good technology blogs: a reading list for the holidays

o11y-fts.mp4

And the following chart shows the relative performance of the three approaches:

![FTS Presentation.png](/uploads/FTS_Presentation_74951aadb9.png)

## Vector index

Vector search in ClickHouse has also been years in the making. The first experimental version appeared back in 22.9, thanks to the work of Arthur Filatenkov, Vladimir Makarov, Danila Mishin, Nikita Vasilenko, Alexander Piachonkin, Nikita Evsiukov, and Hakob Sagatelyan. 

A year later, in 23.8, Davit Vardanyan integrated the USearch library, bringing more robust vector similarity capabilities to the platform.

The real momentum picked up this year. 25.1 introduced significant performance improvements to vector indices, courtesy of Shankar Iyer, Robert Schulze, and Michael Kolupaev. By 25.5, the feature hit beta status with the addition of prefiltering, postfiltering, and rescoring - critical features for production use cases - developed by Shankar Iyer and Robert Schulze. 

Finally, in 25.8, vector search reached general availability with index-only reading, fetch multiplier support, and binary quantization for even better performance and efficiency.

At the time of writing, the only supported approximate search method is HNSW, a popular and state-of-the-art technique for approximate vector search based on hierarchical proximity graphs. We can define a vector index on a column like this:

<pre><code type='click-ui' language='sql'>
CREATE TABLE wikiEmbeddings (
  id Int32,
  title String,
  text String,
  url String,
  wiki_id Int32,
  views Float32,
  paragraph_id Int32,
  langs Int32,
  emb Array(Float32),
  INDEX emb_hnsw emb TYPE vector_similarity('hnsw', 'L2Distance', 768)
)
ORDER BY id;
</code></pre>

We can then write a query that uses the index:

<pre><code type='click-ui' language='sql'>
WITH (SELECT emb FROM wikiEmbeddings WHERE id = 120356) AS lookup
SELECT id, title, text, url, L2Distance(emb, lookup) AS dist
FROM wikiEmbeddings
ORDER BY dist
LIMIT 3
FORMAT Vertical;
</code></pre>

Another interesting development in this area is the introduction of [QBit](https://clickhouse.com/blog/clickhouse-release-25-10#qbit-data-type) by Raufs Dunamalijevs, a data type for vector embeddings that enables runtime tuning of search precision.

We can create a column that uses this type:

<pre><code type='click-ui' language='sql'>
CREATE TABLE vectors (
    id UInt64, name String, ...
    vec QBit(BFloat16, 1536)
) ORDER BY ();
</code></pre>

And at query time, we specify how many (most significant) bits to take:

<pre><code type='click-ui' language='sql'>
SELECT id, name FROM vectors
ORDER BY L2DistanceTransposed(vector, target, 10)
LIMIT 10;
</code></pre>

## Query condition cache

Introduced in ClickHouse 25.3, the [query condition cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache) enables ClickHouse to remember which ranges of granules in data parts satisfy the condition in the WHERE clause and reuse this information as an ephemeral index for subsequent queries.

<iframe width="768" height="432" src="https://www.youtube.com/embed/MG15Ioq708E?si=aVN3Vu7vE-lfSytD" 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>

This makes it a valuable addition for real-world workloads, such as dashboards, alerts, or interactive analytics, which often run the same filters (WHERE conditions) repeatedly against the same data or against continuously growing data, as in observability scenarios. 

This cache is [enabled by default](https://clickhouse.com/docs/operations/settings/settings#use_query_condition_cache), and in our experiments, it results in an order of magnitude improvement in query performance. 

We ran the following query on a BlueSky dataset of 100 million rows with and without the query condition cache:

<pre><code type='click-ui' language='sql'>
SELECT count()
FROM bluesky
WHERE
    data.kind = 'commit'
    AND data.commit.operation = 'create'
    AND data.commit.collection = 'app.bsky.feed.post'
    AND data.commit.record.text LIKE '%????%'
</code></pre>

On the first run, the query takes 0.481 sec, but subsequent runs with the query condition cache enabled take around 0.037 sec, which is more than 10 times quicker.

## Join reordering

[ClickHouse 25.9](https://clickhouse.com/blog/clickhouse-release-25-09#join-reordering) introduced a long-awaited feature for many ClickHouse users - automatic global join reordering.

ClickHouse can now reorder complex join graphs spanning dozens of tables across the most common join types (inner, left outer, right outer, cross, semi, and anti). Automatic global join reordering uses the fact that joins between more than two tables are [associative](https://en.wikipedia.org/wiki/Associative_property).

Two new settings control the global join reordering:

* [query_plan_optimize_join_order_limit](https://clickhouse.com/docs/operations/settings/settings#query_plan_optimize_join_order_limit) - Value is the max number of tables to apply the reordering to.  
* [allow_statistics_optimize](https://clickhouse.com/docs/operations/settings/settings#allow_statistics_optimize) - Allows using statistics to optimize join order

We tested this functionality out on a query that joins six tables from the TPC-H join benchmark. The query was ~1,450 times faster than before and used ~25 times less memory.

You can read more about join reordering in the [25.9 release post](https://clickhouse.com/blog/clickhouse-release-25-09#join-reordering).


## Lazy reading/materialization

ClickHouse [25.4](https://clickhouse.com/blog/clickhouse-release-25-04#lazy-materialization) introduced [lazy materialization](https://clickhouse.com/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization). This means that instead of reading data for a column, we track the information about what data should be read, and then read the data only when needed.

The column values can be carried around, filtered, but not used in calculations before the latest stages of the query pipeline.

<iframe width="768" height="432" src="https://www.youtube.com/embed/xoYUHT_ISOU?si=JtcgE1TQHNynmxIc" 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>

We tried this functionality out on a query that finds the Amazon reviews with the highest number of helpful votes, returning the top 3 along with their title, headline, and full text.

<pre><code type='click-ui' language='sql'>
SELECT helpful_votes, product_title, review_headline, review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Null;
</code></pre>

Without lazy materialization, this query took 219 seconds. With lazy materialization, it took 0.14 seconds. That's a 1,576 times speedup, and it also used 40 times less I/O and 300 times less memory.

Looking back at Alexey's favorite ClickHouse features of the year.

large_Release Blog Cover (33).png

medium_Release Blog Cover (33).png

small_Release Blog Cover (33).png

thumbnail_Release Blog Cover (33).png

Release Blog Cover (33).png

Alexey's favorite features of 2025

An annotated version of Alexey's presentation at the P99 conference on the use of Rust in ClickHouse.

large_Clickhouse C++ Rust Journey Alexey Milovidov.jpg

medium_Clickhouse C++ Rust Journey Alexey Milovidov.jpg

small_Clickhouse C++ Rust Journey Alexey Milovidov.jpg

thumbnail_Clickhouse C++ Rust Journey Alexey Milovidov.jpg

Clickhouse C++ Rust Journey Alexey Milovidov.jpg

How to use Rust in ClickHouse: avoiding a full rewrite

Initially, I wanted to make a single report to incentivise our managers for looking at and resolving GitHub issues. But I'm greedy, so in the end it appeared to be a website where you can compare various metrics on team activities across GitHub repositories. Check it [here](https://velocity.clickhouse.com/).

[![screenshot2.png](https://clickhouse.com/uploads/screenshot2_a81b9da275.png)](https://velocity.clickhouse.com/)

## This is wrong

Developer productivity metrics are a problematic topic. I hope no one compares committed [lines of code](https://www.folklore.org/Negative_2000_Lines_Of_Code.html) today. However, if you track something like the number of commits and pull requests, you make the same mistake. Neither commits nor pull requests are the primary activities of the company, and neither the number of such items provides reliable information about the amount of work by particular individuals.

It could be nice to check the metrics out of curiosity, to find general trends on a large scale, but the metrics should not be a primary target for optimization. They could only be used as a secondary signal.

[![velocity2.png](https://clickhouse.com/uploads/velocity2_ddda0afb69.png)](https://velocity.clickhouse.com/#org=ClickHouse&metric=all_activity&range=all&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=godotengine)


## It makes sense anyway

The metrics of your own team are always wrong! So I added the possibility to calculate and compare any repositories and organizations on GitHub.

Examples:

[Rust vs. Zig](https://velocity.clickhouse.com/#org=rust-lang&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=ziglang&deleted=jcs090218%2Clivetdsa%2Cormabr%2CSuperAuguste%2Ctheballtv%2CJJJEJEJEJEJE%2Cgreensm0ke%2Cshizfs%2Capuarwa%2Ctadirnaksam) | [Polars vs. Datafusion](https://velocity.clickhouse.com/#org=pola-rs&metric=all_activity&range=12&grouping=auto&a[%E2%80%A6]yone=0&compare=1&compareOrg=apache%2Fdatafusion*%2C+apache%2Farrow-datafusion*) | [Superset vs. Metabase](https://velocity.clickhouse.com/#org=apache%2Fsuperset%2C+apache%2Fincubator-superset%2C+airbnb%2Fsuperset&metric=all_activity&range=all&grouping=quarter&alexey=0&everyone=0&compare=1&compareOrg=metabase&deleted=github-automation-metabase) | [Node vs. Deno](https://velocity.clickhouse.com/#org=nodejs%2C+joyent%2Fnode&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=denoland) | [Node vs. Bun](https://velocity.clickhouse.com/#org=nodejs%2C+joyent%2Fnode&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=oven-sh) | [SurrealDB vs ArangoDB](https://velocity.clickhouse.com/#org=surrealdb&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=arangodb) | [Elasticsearch vs Opensearch](https://velocity.clickhouse.com/#org=elastic%2C+elasticsearch&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=opensearch-project%2C+opendistro-for-elasticsearch&deleted=github-automation-metabase%2Celasticmachine%2Ckibanamachine%2Ckibana-ci%2Celasticsearchmachine%2Capmmachine%2Cprodsecmachine) | [Redis vs. Valkey](https://velocity.clickhouse.com/#org=valkey-io&metric=all_activity&range=6&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=antirez%2Fredis%2C+redis%2C+RedisLabs) | [Tensorflow vs. PyTorch](https://velocity.clickhouse.com/#org=tensorflow&metric=all_activity&range=all&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=pytorch&deleted=pytorchmergebot%2Ctensorflowbutler%2Ctensorflow-jenkins) | [QDrant vs. Weaviate](https://velocity.clickhouse.com/#org=qdrant&metric=all_activity&range=all&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=weaviate%2C+semi-technologies%2Fweaviate&deleted=pytorchmergebot%2Ctensorflowbutler%2Ctensorflow-jenkins) | [Supabase vs. Neon](https://velocity.clickhouse.com/#org=supabase&metric=all_activity&range=all&grouping=auto&alexey=0&everyone=0&compare=1&compareOrg=neondatabase)

We can see when particular people started and stopped contributing to the project, what the distribution of the community size is, and what the general trends are in a specific repository's activity. The popularity of a project can be estimated by the number of GitHub issues (meaning, feature requests, bug reports, and questions).

Keep in mind that direct comparison is not always possible and can be misleading. For example, [Spark](https://velocity.clickhouse.com/#org=apache%2Fspark*%2C+apache%2Fincubator-spark&metric=all_activity&range=all&grouping=quarter&alexey=0&everyone=0&deleted=github-automation-metabase) and [MongoDB](https://velocity.clickhouse.com/#org=mongodb%2C+10gen%2C+10gen-labs&metric=all_activity&range=all&grouping=quarter&alexey=0&everyone=0&deleted=github-automation-metabase) don't use GitHub issues, forcing their users to register in Jira. [Linux kernel](https://kernel.org/) uses neither issues nor pull requests. [LLVM](https://velocity.clickhouse.com/#org=llvm&metric=all_activity&range=12&grouping=auto&alexey=0&everyone=0&deleted=llvmbot%2Cllvm-ci) has recently migrated from Phabricator and Bugzilla to GitHub pull requests and issues, which made it way more accessible, while [GCC](https://gcc.gnu.org/) is only available on GitHub as a mirror. [Chromium](https://chromium.googlesource.com/chromium/src.git) also does not use issues or pull requests. For established open-source products, it's more an exception than the rule to use GitHub for issue tracking.


## Implementation

The website is implemented as a single-page HTML application, connecting directly to the ClickHouse database. It runs SQL queries from a read-only user, configured with limits and quotas. The database design and data collection are described in [this article from 2020](https://ghe.clickhouse.tech/).

It has a few neat details in the UX.

In the repository selector, you can type organizations (like `ClickHouse`), single repositories (like `ClickHouse/ClickHouse`), or use wildcards (like `apache/superset*`), as well as list multiple items. It will show suggestions by substring search across all GitHub repositories, sorted by the number of stars.

For the charts, we use the ["horizon chart"](https://en.wikipedia.org/wiki/Horizon_chart) type, which allows a high information density in a narrow space. We also highlight activity on different repositories with different colors, so you can see, for example, when I was contributing to ClickHouse and when I was contributing to [ClickBench](https://benchmark.clickhouse.com/).

In the list of authors, you can click on anyone to see the list of all activities in the ClickHouse SQL UI, and if you click on a single data point on the chart, you can dig into the events for the corresponding time. You can also remove any author from the report to clean it of automations or spam activity.

The source code is available [here](https://github.com/ClickHouse/velocity).

[![implementation.png](https://clickhouse.com/uploads/implementation_267d5599be.png)](https://github.com/ClickHouse/velocity/commits/master/)


## Results

It took only half of a weekend to implement this website, and I'm still impressed by how easy it was. Building analytical applications, data exploration tools, and dashboards - **it's such a joy with ClickHouse!** No other database comes even close in usability.



Initially, I wanted to make a single report to incentivise for looking at and resolving GitHub issues. But I'm greedy, so in the end it appeared to be a website where you can compare various metrics on team activities across GitHub repositories.

large_ClickHouse Blog Banner.jpg

medium_ClickHouse Blog Banner.jpg

small_ClickHouse Blog Banner.jpg

thumbnail_ClickHouse Blog Banner.jpg

ClickHouse Blog Banner.jpg

I've created a website to track the team's activity

Recently, I read the blog post by [Niklas Oberhuber](https://obrhubr.org/adsb-weather-model) showing that it is possible to derive weather data from airplane telemetry. Airplanes don't broadcast temperature or wind data, but it can be calculated on the fly from other parameters. I immediately decided to reproduce these calculations to show something beautiful!

[ADS-B Massive Visualizer](https://adsb.exposed/) collects telemetry from airplanes, such as their position, altitude, speed, and heading. This data is saved in the ClickHouse database. The service provides access to custom reports on top of over 150 billion data points and counting, accumulating them in real time.

You can read more about the service in the previous [blog post](https://clickhouse.com/blog/interactive-visualization-analytics-adsb-flight-data-with-clickhouse).

![title2.png](https://clickhouse.com/uploads/title2_f2f62c67b6.png)

## Sensors

Aircraft have many air pressure sensors, named "pitot tubes", which measure how the aircraft moves through the air. Static air pressure will show the altitude, and dynamic air pressure will show the airspeed relative to the air. These pressure sensors are the most important for flying dynamics.

Aircraft also have various navigation systems, which can show the altitude, speed, and heading, relative to the Earth. These measurements are less important to how the aircraft flies, but more important to navigation.

If we compare the values of these measurements to each other, we can derive more data. For example, comparing the airspeed with geometric speed will show us the speed of the tailwind (or headwind). Comparing the altitude from the pressure sensors with the geometric altitude could give some information about the local pressure changes and, maybe, temperature. Finally, comparing the heading relative to the air with the geometric heading will give us information about the wind direction.

## Visualizations

Let's suppose we want to visualize the angle (0..360 degrees) on the map. How to do it? We can use the color circle! So, different angles will be shown as different hues of color. For this purpose, ClickHouse provides functions for [conversions between color spaces](https://presentations.clickhouse.com/2025-release-25.7/#9): `colorOKLCHToSRGB` and `colorSRGBToOKLCH`.

The color space that most of the displays and graphic cards use is named sRGB, and contains three channels (Red, Green, and Blue) that are Gamma-corrected (they are non-linear to provide more resolution to darker colors, as they are easier to distinguish relatively to each other than the brighter colors).

There is another color space, named [OKLCH](https://oklch.com/). It contains three channels: Lightness, Chroma, and Hue. It is cylindrical, because the L and C channels range in the line (say, between 0 and 1; however, some of the values can be out of range of the display device), and the Hue channel is circular (represents an angle between 0 and 360). It is designed to be linear (which means, if we do a linear gradient in that color space, the gradient will look okay, hence the name) and intended to be perceptually uniform (which means, if you average between two colors in that space, the resulting color is also perceived as the average; if you fix the Lightness and Chroma and change the Hue, the result will be perceived as having the same brightness).

Let's map aircraft headings using this color space:

[![heading.png](https://clickhouse.com/uploads/heading_87061b8bfa.png)](https://adsb.exposed/?dataset=Planes&zoom=5&lat=36.0314&lng=-98.4165&query=ed99deb24cc4f2a2026034df3d7dd447)

## Pressure

Let's filter data for airplanes at least at 10,000 feet and map the difference between the `altitude` and `geometric_altitude` for a certain day:

[![pressure.png](https://clickhouse.com/uploads/pressure_4c97106659.png)](https://adsb.exposed/?dataset=Planes&zoom=5&lat=37.1785&lng=-95.2295&query=d095cd7b312291b682d3c9f1dafad188)

This looks surprisingly beautiful! Even more interesting, the picture changes if you select a different day.

<div style="height: 500px;"><video style="height: 100%;" src="https://clickhouse.com/uploads/pressure_cb1ce8d22f.mp4" autoplay loop muted playsinline></video></div>
<small style="color: gray;">Visualization of the air pressure difference across the US during 25 days starting from Apr 2, 2025. Each frame represents a 24-hour sliding window, and frames advance by 6 hours.</small>

I didn't derive the actual temperature from this data. The formula from Wikipedia looks complex, and I didn't want to apply it. Niklas Oberhuber also didn't use the formula but created a model instead. And I was just satisfied with nice pictures.

## Crabbing angle

When there is a strong but consistent crosswind, airplanes fly pointed into the wind. They can even land while pointed into the wind, which is called "crabbing". In the ADS-B data, there are two fields: one is `track_degrees`, which means the angle the plane points to, and another is `aircraft_true_heading`, which means the angle relative to the moving air around the airplane. If we subtract one angle from another, we will get the "crabbing" angle. Let's visualize it:

[![crabbing.png](https://clickhouse.com/uploads/crabbing_319dc835ee.png)](https://adsb.exposed/?dataset=Planes&zoom=5&lat=45.4119&lng=4.8697&query=6b7872ee6c34260ef470f5fd675f7d58)

Unfortunately, most of the data is not available in the US, but it is available in the EU. Probably, it is due to some local regulations.

## Wind

Now let's calculate the wind direction and speed. It will require some trigonometry, and trying to understand these formulas is difficult without a pen and paper.

Here is the "crabbing angle" (or name it "drift angle") we calculated in the previous step:
```(sql)
positiveModulo(track_degrees - aircraft_true_heading + 180, 360) - 180 AS drift_angle,
```

The wind speed can be calculated in the following way: draw a triangle with the `ground_speed` as one side and the `aircraft_tas` (airspeed) as the other side, with the angle `drift_angle` between these two sides. The length of the other side (the difference between the vectors of `ground_speed` and `aircraft_tas`) is the wind speed:

```(sql)
sqrt(
    pow(ground_speed, 2) + pow(aircraft_tas, 2) -
    2 * ground_speed * aircraft_tas * cos(radians(drift_angle))
) AS wind_speed,
```

Use a similar rule to calculate the absolute wind direction angle:

```(sql)
positiveModulo(track_degrees +
    if(drift_angle < 0, -1, 1) * degrees(acos(
        greatest(-1, least(1,
            (pow(ground_speed, 2) + pow(wind_speed, 2) - pow(aircraft_tas, 2)) /
            (2 * ground_speed * if(wind_speed > 0.1, wind_speed, 1))
        ))
    )) + 180, 360
) AS wind_direction,
```

We would like to show the wind direction on the map. But sometimes one pixel on the map will contain multiple data points, and we need to aggregate them to calculate the average. How to calculate the average of angles, e.g., what is the average between 10 degrees and 350 degrees? (The answer should be zero.) Actually, we need even more: calculate the weighted average of wind direction angles with the wind speed as the weight, so the stronger wind will have a bigger contribution.

To do this, let's say that the wind direction and wind speed are polar coordinates of the wind vector. And what we need in the result is the average wind vector. The average is a function in the linear coordinate space, which is why we can't apply it simply in the polar coordinates. We can do a coordinate transform of the function, but put it more simply, we should convert our (direction, speed) coordinates to linear (x, y), then do the average across both x and y coordinates, then convert it back to the polar space and take the angle back as the average:

```(sql)
positiveModulo(degrees(atan2(
    avg(sin(radians(wind_direction)) * wind_speed),
    avg(cos(radians(wind_direction)) * wind_speed)
)), 360) AS avg_wind_direction,
```

Now, given the wind angle and wind speed, it will be natural to map the angle to the color hue and the speed to the color lightness:

[![wind.png](https://clickhouse.com/uploads/wind_edc9f29b7d.png)](https://adsb.exposed/?dataset=Planes&zoom=5&lat=45.4119&lng=4.8697&query=ec1376e91336d70a77914a6ea2c22acc)

I compared the resulting picture to the actual weather data at https://earth.nullschool.net/ and it looked exactly as expected!

<div style="height: 550px;"><video style="height: 100%;" src="https://clickhouse.com/uploads/wind_f6f91a5794.mp4" autoplay loop muted playsinline></video></div>
<small style="color: gray;">Visualization of the wind across Europe during 25 days starting from Jan 1, 2025. Each frame represents a 24-hour sliding window, and frames advance by 6 hours.</small>

## Bottom line

ADS-B is a treasure trove of data, and it really shines when it is queryable in real-time with ClickHouse.

<small style="color: gray;">Bonus: how to do video visualizations. Prepare a query [as follows](https://pastila.nl/?03f73efc/6e0a10a5586f9a049a6747c36feb7373#oCbctgncESvUFSUc8eteyg==). Copy it to adsb.exposed. Open the browser console. Copy and run the following snippet: `frame = 0; const id = setInterval(() => { query_elem.value = query_elem.value.replace(/(\d+) AS frame/, `${frame} AS frame`); updateMap(); if (frame >= 100) { clearInterval(id) }; ++frame; }, 10000)`. The first run is needed to bring the images to the cache. After it finishes, replace the update interval to one second, start `recordmydesktop` and repeat the run. The generated video can be processed with ffmpeg as follows: `ffmpeg -y -i out.ogv -ss 4.5 -to 105 -filter:v "crop=2149:1218:893:666,thumbnail=15,setpts=0.1*PTS" -qscale:v 10 -an pressure.ogv`, where the time offsets are found manually by looking at the video, and crop frame is determined by taking a screenshot and using the selection tool in the graphic editor. The ffmpeg invocation is constructed by Claude.</small>

Alexey Milovidov

Blog posts by Alexey Milovidov

Christmas gifts from the ClickHouse engineering team

Good technology blogs: a reading list for the holidays

Alexey's favorite features of 2025

How to use Rust in ClickHouse: avoiding a full rewrite

I've created a website to track the team's activity

Global weather data from flying airplanes