Architecture Overview

Figure 12: Hot runtimes (in seconds) for TPC-H queries.

## 7 RELATED WORK[​](#7-related-work "Direct link to 7 RELATED WORK")

Analytical databases have been of great academic and commercial interest in recent decades [[1]](#page-12-35). Early systems like Sybase IQ [[48]](#page-13-28), Teradata [[72]](#page-13-29), Vertica [[42]](#page-12-36), and Greenplum [[47]](#page-13-30) were characterized by expensive batch ETL jobs and limited elasticity due to their on-premise nature. In the early 2010s, the advent of cloud-native data warehouses and database-as-a-service offerings (DBaaS) such as Snowfake [[22]](#page-12-37), BigQuery [[49]](#page-13-31), and Redshift [[4]](#page-12-38) dramatically reduced the cost and complexity of analytics for organizations, while benefiting from high availability and automatic resource scaling. More recently, analytical execution kernels (e.g. Photon [[5]](#page-12-39) and Velox [[62]](#page-13-32)) offer co-modified data processing for use in different analytical, streaming, and machine learning applications.

The most similar databases to ClickHouse, in terms of goals and design principles, are Druid [[78]](#page-13-33) and Pinot [[34]](#page-12-40). Both systems target real-time analytics with high data ingestion rates. Like ClickHouse, tables are split into horizontal parts called segments. While ClickHouse continuously merges smaller parts and optionally reduces data volumes using the techniques in Section [3.3,](#page-4-3) parts remain forever immutable in Druid and Pinot. Also, Druid and Pinot require specialized nodes to create, mutate, and search tables, whereas ClickHouse uses a monolithic binary for these tasks.

Snowfake [[22]](#page-12-37) is a popular proprietary cloud data warehouse based on a shared-disk architecture. Its approach of dividing tables into micro-partitions is similar to the concept of parts in ClickHouse. Snowfake uses hybrid PAX pages [[3]](#page-12-41) for persistence, whereas ClickHouse's storage format is strictly columnar. Snowfake also emphasizes local caching and data pruning using automatically created lightweight indexes [[31,](#page-12-13) [51]](#page-13-14) as a source for good performance. Similar to primary keys in ClickHouse, users may optionally create clustered indexes to co-locate data with the same values.

Photon [[5]](#page-12-39) and Velox [[62]](#page-13-32) are query execution engines designed to be used as components in complex data management systems. Both systems are passed query plans as input, which are then executed on the local node over Parquet (Photon) or Arrow (Velox) files [[46]](#page-13-34). ClickHouse is able to consume and generate data in these generic formats but prefers its native file format for storage. While Velox and Photon don't optimize the query plan (Velox performs basic expression optimizations), they utilize runtime adaptivity techniques, such as dynamically switching compute kernels depending on the data characteristics. Similarly, plan operators in ClickHouse

can create other operators at runtime, primarily to switch to external aggregation or join operators, based on the query memory consumption. The Photon paper notes that code-generating designs [[38,](#page-12-22) [41,](#page-12-42) [53]](#page-13-0) are harder to develop and debug than interpreted vectorized designs [[11]](#page-12-0). The (experimental) support for code generation in Velox builds and links a shared library produced from runtime-generated C++ code, whereas ClickHouse interacts directly with LLVM's on-request compilation API.

DuckDB [[67]](#page-13-6) is also meant to be embedded by a host process, but additionally provides query optimization and transactions. It was designed for OLAP queries mixed with occasional OLTP statements. DuckDB accordingly chose the DataBlocks [[43]](#page-12-43) storage format, which employs light-weight compression methods such as order-preserving dictionaries or frame-of-reference [[2]](#page-12-10) to achieve good performance in hybrid workloads. In contrast, ClickHouse is optimized for append-only use cases, i.e. no or rare updates and deletes. Blocks are compressed using heavy-weight techniques like LZ4, assuming that users make liberal use of data pruning to speed up frequent queries and that I/O costs dwarf decompression costs for the remaining queries. DuckDB also provides serializable transactions based on Hyper's MVCC scheme [[55]](#page-13-35), whereas ClickHouse only offers snapshot isolation.

## 8 CONCLUSION AND OUTLOOK[​](#8-conclusion-and-outlook "Direct link to 8 CONCLUSION AND OUTLOOK")

We presented the architecture of ClickHouse, an open-source, highperformance OLAP database. With a write-optimized storage layer and a state-of-the-art vectorized query engine at its foundation, ClickHouse enables real-time analytics over petabyte-scale data sets with high ingestion rates. By merging and transforming data asynchronously in the background, ClickHouse efficiently decouples data maintenance and parallel inserts. Its storage layer enables aggressive data pruning using sparse primary indexes, skipping indexes, and projection tables. We described ClickHouse's implementation of updates and deletes, idempotent inserts, and data replication across nodes for high availability. The query processing layer optimizes queries using a wealth of techniques, and parallelizes execution across all server and cluster resources. Integration table engines and functions provide a convenient way to interact with other data management systems and data formats seamlessly. Through benchmarks, we demonstrate that ClickHouse is amongst the fastest analytical databases on the market, and we showed significant improvements in the performance of typical queries in real-world deployments of ClickHouse throughout the years.

All features and enhancements planned for 2024 can be found on the public roadmap [[18]](#page-12-33). Planned improvements include support for user transactions, PromQL [[69]](#page-13-36) as an alternative query language, a new datatype for semi-structured data (e.g. JSON), better plan-level optimizations of joins, as well as an implementation of light-weight updates to complement light-weight deletes.

## ACKNOWLEDGMENTS[​](#acknowledgements "Direct link to ACKNOWLEDGMENTS")

As per version 24.6, SELECT \* FROM system.contributors returns 1994 individuals who contributed to ClickHouse. We would like to thank the entire engineering team at ClickHouse Inc. and ClickHouse's amazing open-source community for their hard work and dedication in building this database together.

## REFERENCES[​](#references "Direct link to REFERENCES")