ClickHouse: It's fast

In an era of substantial data generation, conventional database systems frequently encounter challenges with the scale and demand for rapid query responses. ClickHouse is an open-source, column-oriented Database Management System (DBMS) for Online Analytical Processing (OLAP), facilitating real-time analytical reports from SQL queries. Initially developed by Yandex, it was engineered for exceptionally fast processing of large-scale analytical queries.3 ClickHouse’s high performance is attributed to an innovative storage design that makes processing massive datasets more manageable.

The Core Pillars of ClickHouse’s Speed

ClickHouse’s performance is due to several key architectural decisions.

A. Columnar Storage

ClickHouse employs columnar storage, where data for each column is stored independently of other columns. This contrasts with row-oriented databases, which store all data for a single record together.

The advantages for analytical query performance include:

1. Reduced I/O: Analytical queries frequently require data from only a limited subset of columns. ClickHouse reads only the data for the columns specified in the query, thereby dramatically reducing the volume of data read from disk compared to row-oriented systems.
2. Enhanced Data Compression: The homogeneity of data within a single column renders it highly compressible. Superior compression ratios result in less data being read from disk, which accelerates query execution.

Table: Data Read Comparison - Analytical Query (“Calculate Average Order Value”)

The reduction in I/O achieved through columnar storage is a foundational element of ClickHouse’s performance.

B. Data Compression

The homogeneity of columnar data makes it highly suitable for effective compression. Compression reduces the volume of data transferred from disk to memory, thereby accelerating query execution. ClickHouse provides a range of compression algorithms (codecs), which can often be specified on a per-column basis.

Prominent codecs include:

• LZ4: Characterised by rapid compression and decompression, LZ4 is advantageous when processing speed is a primary concern.
• ZSTD: The default codec in ClickHouse, ZSTD offers an effective balance between compression ratio and computational performance.

ClickHouse also employs specialized encoding techniques such as Delta, Dictionary, and Run-Length Encoding (RLE) to further leverage specific data characteristics. The columnar storage architecture creates optimal conditions for these compression methodologies.

C. Vectorized Query Execution

ClickHouse utilises vectorized query execution, processing data in batches—referred to as vectors or blocks—instead of on a row-by-row basis. Query operators perform operations on these entire vectors of values simultaneously.

This approach yields several performance benefits:

1. Reduced Overhead: Executing an operation on a vector of values in a single call is considerably more efficient than invoking that operation multiple times for individual values.
2. Enhanced CPU Cache Efficiency: Processing data in contiguous vectors, as facilitated by columnar storage, significantly improves CPU cache locality, thereby reducing cache misses.
3. SIMD (Single Instruction, Multiple Data) Parallelism: Modern CPUs are equipped with SIMD instruction sets that allow the same operation to be performed on multiple data points concurrently. Vectorized execution is inherently suited to leverage SIMD, and ClickHouse is engineered to exploit these hardware capabilities.

The synergy between columnar storage, vectorized execution, and SIMD instructions maximises CPU processing efficiency.

D. The MergeTree Engine

The MergeTree family of storage engines is fundamental to ClickHouse’s data management, engineered for high data ingestion rates and the efficient storage of petabyte-scale datasets.10 Data is initially written in segments, termed “parts,” which are subsequently merged in background processes.

A distinguishing feature is its use of sparse primary indexes:

• Data within MergeTree parts is logically divided into “granules,” with a default size of 8,192 rows.
• The primary index stores an entry, known as a “mark,” exclusively for the first row of each granule. This design results in a compact index, often small enough to reside entirely in RAM.
• The efficacy of this approach is contingent upon the physical sorting of data within each part, according to the ORDER BY key specified during table creation (which typically also serves as the primary key).

This architecture enables efficient data skipping. ClickHouse leverages the sparse primary index to rapidly identify only those granules that could potentially contain rows matching the query’s criteria. Consequently, only these selected granules are read from disk, while all others are bypassed.10 This mechanism significantly reduces disk I/O. The selection of an appropriate ORDER BY key is therefore critical for maximising the effectiveness of data skipping. Furthermore, the MergeTree engine’s design, which involves writing data in immutable parts and merging them asynchronously, supports high data ingestion rates.

E. Parallel Processing

ClickHouse is engineered to exploit parallelism at multiple levels to utilise available hardware resources effectively:

• Intra-Server Parallelism (Vertical Scalability): On a single server, ClickHouse is designed to utilise all available CPU cores. It can decompose query workloads to enable parallel processing of different data segments or query stages.
• Inter-Server Parallelism (Horizontal Scalability via Sharding): For datasets exceeding single-server capacity or for workloads demanding greater concurrent processing power, ClickHouse supports horizontal scalability through sharding. This involves partitioning table data across multiple servers (nodes) within a cluster.3 Distributed queries are routed to relevant shards, processed in parallel, and the intermediate results are subsequently aggregated.

This multi-level parallelism is fundamental to ClickHouse’s capacity for rapidly processing massive datasets and complex queries.

Synergistic Architecture for High Performance

ClickHouse’s notable performance is not attributable to a single feature, but rather to the synergistic interplay of the architectural components previously discussed. Each element is designed to complement and enhance the others:

• Columnar Storage: Facilitates efficient data access patterns for analytical queries, enables superior data compression, and provides data in a format conducive to vectorized execution.
• Data Compression: Significantly reduces data volume, thereby decreasing I/O operations and memory footprint.
• Vectorized Query Execution: Maximises CPU throughput by processing data in batches and leveraging SIMD instructions.
• MergeTree Engine with Sparse Primary Indexes: Minimises the amount of data read from disk by intelligently skipping irrelevant data blocks.
• Parallel Processing: Leverages multiple CPU cores and distributed server architectures to amplify overall processing speed.

When all these components are integrated and optimised, specifically for analytical workloads, ClickHouse exhibits exceptional performance.

Optimal Use Cases and Limitations

Optimal Scenarios for ClickHouse Utilisation:

• Online Analytical Processing (OLAP): Suited for complex analytical queries, aggregations, multi-dimensional analysis, and report generation over extensive historical datasets.
• Real-time Analytics and Interactive Dashboards: Its low-latency query response on large datasets makes it ideal for powering interactive dashboards and applications requiring near real-time insights.
• Large-Scale Data Processing: Engineered to efficiently manage and query datasets at terabyte and petabyte scales.
• Common Applications: Includes log and event data analysis, time-series data analysis, business intelligence platforms, data warehousing, and application performance monitoring.

Scenarios Where ClickHouse May Be Less Suitable:

• Online Transaction Processing (OLTP): Generally not recommended for workloads characterised by frequent, small, single-row updates, inserts, or deletes, or for systems requiring high-concurrency transactional operations with stringent ACID guarantees across multiple tables.
• Small to Medium-Sized Datasets (with caveats): For datasets where analytical demands are modest, the architectural overheads of ClickHouse may not be justified unless significant data growth or future requirements for complex analytics are anticipated.
• Requirement for Strong, Granular Transactional Guarantees: ClickHouse provides limited transactional support, typically at the level of block insertion into a specific table, rather than comprehensive ACID transactions across multiple operations or tables.

Conclusion

ClickHouse’s high performance is the result of a sophisticated combination of several architectural features:

• Columnar Storage
• Data Compression
• Vectorized Query Execution
• The MergeTree Engine with Sparse Primary Indexes
• Parallel Processing

These architectural principles operate in concert to enable efficient querying of extensive datasets. A comprehension of these fundamental aspects is crucial for effectively leveraging ClickHouse’s analytical capabilities. Further exploration of its comprehensive feature set is recommended, with detailed information available in the official documentation and various online tutorials.