Why is ClickHouse so fast?

## State-of-the-art query processing layer[​](#state-of-the-art-query-processing-layer "Direct link to State-of-the-art query processing layer")

Finally, ClickHouse uses a vectorized query processing layer that parallelizes query execution as much as possible to utilize all resources for maximum speed and efficiency.

"Vectorization" means that query plan operators pass intermediate result rows in batches instead of single rows. This leads to better utilization of CPU caches and allows operators to apply SIMD instructions to process multiple values at once. In fact, many operators come in multiple versions - one per SIMD instruction set generation. ClickHouse will automatically select the most recent and fastest version based on the capabilities of the hardware it runs on.

Modern systems have dozens of CPU cores. To utilize all cores, ClickHouse unfolds the query plan into multiple lanes, typically one per core. Each lane processes a disjoint range of the table data. That way, the performance of the database scales "vertically" with the number of available cores.

If a single node becomes too small to hold the table data, further nodes can be added to form a cluster. Tables can be split ("sharded") and distributed across the nodes. ClickHouse will run queries on all nodes that store table data and thereby scale "horizontally" with the number of available nodes.

🤿 Deep dive into this in the [Query Processing Layer](/docs/academic_overview#4-query-processing-layer) section of the web version of our VLDB 2024 paper.

## Meticulous attention to detail[​](#meticulous-attention-to-detail "Direct link to Meticulous attention to detail")

> **"ClickHouse is a freak system - you guys have 20 versions of a hash table. You guys have all these amazing things where most systems will have one hash table** **...** **ClickHouse has this amazing performance because it has all these specialized components"** [Andy Pavlo, Database Professor at CMU](https://www.youtube.com/watch?v=Vy2t_wZx4Is&t=3579s)

What sets ClickHouse [apart](https://www.youtube.com/watch?v=CAS2otEoerM) is its meticulous attention to low-level optimization. Building a database that simply works is one thing, but engineering it to deliver speed across diverse query types, data structures, distributions, and index configurations is where the "[freak system](https://youtu.be/Vy2t_wZx4Is?si=K7MyzsBBxgmGcuGU&t=3579)" artistry shines.

**Hash Tables.** Let's take a hash table as an example. Hash tables are central data structures used by joins and aggregations. As a programmer, one needs to consider these design decisions:

- The hash function to choose,
- The collision resolution: [open addressing](https://en.wikipedia.org/wiki/Open_addressing) or [chaining](https://en.wikipedia.org/wiki/Hash_table#Separate_chaining),
- The memory layout: one array for keys and values or separate arrays?
- The fill factor: When and how to resize? How to move values during resize?
- Deletions: Should the hash table allow evicting entries?

A standard hash table provided by a third-party library would functionally work, but it wouldn't be fast. Great performance requires meticulous benchmarking and experimentation.

The [hash table implementation in ClickHouse](https://clickhouse.com/blog/hash-tables-in-clickhouse-and-zero-cost-abstractions) chooses one of **30+ precompiled hash table variants based** on the specifics of the query and the data.

**Algorithms.** The same goes for algorithms. For example, in sorting, you might consider:

- What will be sorted: numbers, tuples, strings, or structures?
- Is the data in RAM?
- Is the sort required to be stable?
- Should all data be sorted or will a partial sort suffice?

Algorithms that rely on data characteristics often perform better than their generic counterparts. If the data characteristics aren't known in advance, the system can try various implementations and choose the one that works best at runtime. For an example, see the [article on how LZ4 decompression is implemented in ClickHouse](https://habr.com/en/company/yandex/blog/457612/).

🤿 Deep dive into this in the [Holistic Performance Optimization](/docs/academic_overview#4-4-holistic-performance-optimization) section of the web version of our VLDB 2024 paper.

## VLDB 2024 paper[​](#vldb-2024-paper "Direct link to VLDB 2024 paper")

In August 2024, we had our first research paper accepted and published at VLDB.
VLDB is an international conference on very large databases, and is widely regarded as one of the leading conferences in the field of data management.
Among the hundreds of submissions, VLDB generally has an acceptance rate of ~20%.

You can read a [PDF of the paper](https://www.vldb.org/pvldb/vol17/p3731-schulze.pdf) or our [web version](/docs/academic_overview) of it, which gives a concise description of ClickHouse's most interesting architectural and system design components that make it so fast.

Alexey Milovidov, our CTO and the creator of ClickHouse, presented the paper (slides [here](https://raw.githubusercontent.com/ClickHouse/clickhouse-presentations/master/2024-vldb/VLDB_2024_presentation.pdf)), followed by a Q&A (that quickly ran out of time!).
You can catch the recorded presentation here:

- [Storage layer: concurrent inserts are isolated from each other](#storage-layer-concurrent-inserts-are-isolated-from-each-other)- [Storage layer: concurrent inserts and selects are isolated](#storage-layer-concurrent-inserts-and-selects-are-isolated)- [Storage layer: merge-time computation](#storage-layer-merge-time-computation)- [Storage layer: data pruning](#storage-layer-data-pruning)- [Storage layer: data compression](#storage-layer-data-compression)- [State-of-the-art query processing layer](#state-of-the-art-query-processing-layer)- [Meticulous attention to detail](#meticulous-attention-to-detail)- [VLDB 2024 paper](#vldb-2024-paper)

Was this page helpful?