Architecture Overview

Compatibility interfaces. Besides its native binary wire protocol and HTTP, clients can interact with ClickHouse over MySQL or PostgreSQL wire-protocol-compatible interfaces. This compatibility feature is useful to enable access from proprietary applications (e.g. certain business intelligence tools), where vendors haven't yet implemented native ClickHouse connectivity.

## 6 PERFORMANCE AS A FEATURE[​](#6-performance-as-a-feature "Direct link to 6 PERFORMANCE AS A FEATURE")

This section presents built-in tools for performance analysis and evaluates the performance using real-world and benchmark queries.

### 6.1 Built-in Performance Analysis Tools[​](#6-1-built-in-performance-analysis-tools "Direct link to 6.1 Built-in Performance Analysis Tools")

A wide range of tools is available to investigate performance bottlenecks in individual queries or background operations. Users interact with all tools through a uniform interface based on system tables.

**Server and query metrics**. Server-level statistics, such as the active part count, network throughput, and cache hit rates, are supplemented with per-query statistics, like the number of blocks read or index usage statistics. Metrics are calculated synchronously (upon request) or asynchronously at configurable intervals.

**Sampling profiler**. Callstacks of the server threads can be collected using a sampling profiler. The results can optionally be exported to external tools such as flamegraph visualizers.

**OpenTelemetry integration**. OpenTelemetry is an open standard for tracing data rows across multiple data processing systems [[8]](#page-12-26). ClickHouse can generate OpenTelemetry log spans with a configurable granularity for all query processing steps, as well as collect and analyze OpenTelemetry log spans from other systems.

**Explain query**. Like in other databases, SELECT queries can be preceded by EXPLAIN for detailed insights into a query's AST, logical and physical operator plans, and execution-time behavior.

### 6.2 Benchmarks[​](#6-2-benchmarks "Direct link to 6.2 Benchmarks")

While benchmarking has been criticized for being not realistic enough [[10,](#page-12-27) [52,](#page-13-22) [66,](#page-13-23) [74]](#page-13-24), it is still useful to identify the strengths and weaknesses of databases. In the following, we discuss how benchmarks are used to evaluate the performance of ClickHouse.

#### 6.2.1 Denormalized Tables[​](#6-2-1-denormalized-tables "Direct link to 6.2.1 Denormalized Tables")

Filter and aggregation queries on denormalized fact tables historically represent the primary use case of ClickHouse. We report runtimes of ClickBench, a typical workload of this kind that simulates ad-hoc and periodic reporting queries used in clickstream and traffic analysis. The benchmark consists of 43 queries against a table with 100 million anonymized page hits, sourced from one of the web's largest analytics platforms. An online dashboard [[17]](#page-12-28) shows measurements (cold/hot runtimes, data import time, on-disk size) for over 45 commercial and research databases as of June 2024. Results are submitted by independent contributors based on the publicly available data set and queries [[16]](#page-12-29). The queries test sequential and index scan access paths and routinely expose CPU-, IO-, or memory-bound relational operators.

[Figure 10](#page-10-0) shows the total relative cold and hot runtimes for sequentially executing all ClickBench queries in databases frequently used for analytics. The measurements were taken on a single-node AWS EC2 c6a.4xlarge instance with 16 vCPUs, 32 GB RAM, and 5000 IOPS / 1000 MiB/s disk. Comparable systems were used for Redshift ([ra3.4xlarge](https://clickhou.se/redshift-sizes), 12 vCPUs, 96 GB RAM) and Snowfake ([warehouse size S](https://clickhou.se/snowflake-sizes): 2x8 vCPUs, 2x16 GB RAM). The physical database design is tuned only lightly, for example, we specify primary keys, but don't change the compression of individual columns, create projections, or skipping indexes. We also flush the Linux page cache prior to each cold query run, but don't adjust database or operating system knobs. For every query, the fastest runtime across databases is used as a baseline. Relative query runtimes for other databases are calculated as ( + 10)/(\_ + 10). The total relative runtime for a database is the geometric mean of the per-query ratios. While the research database Umbra [[54]](#page-13-25) achieves the best overall hot runtime, ClickHouse outperforms all other production-grade databases for hot and cold runtimes.

Figure 10: Relative cold and hot runtimes of ClickBench.

To track the performance of SELECTs in more diverse workloads over time, we [use](https://clickhou.se/performance-over-years) a combination of four benchmarks called VersionsBench [[19]](#page-12-30). This benchmark is executed once per month when a new release is published to assess its performance [[20]](#page-12-31) and identify code changes that potentially degraded performance: Individual benchmarks include: 1. ClickBench (described above), 2. 15 MgBench [[21]](#page-12-32) queries, 3. 13 queries against a denormalized Star Schema Benchmark [[57]](#page-13-26) fact table with 600 million rows. 4. 4 queries against [NYC Taxi Rides](https://clickhou.se/nyc-taxi-rides-benchmark) with 3.4 billion rows [[70]](#page-13-27).

[Figure 11](#page-10-5) shows the development of the VersionsBench runtimes for 77 ClickHouse versions between March 2018 and March 2024. To compensate for differences in the relative runtime of individual queries, we normalize the runtimes using a geometric mean with the ratio to the minimum query runtime across all versions as weight. The performance of VersionBench improved by 1.72 × over the past six years. Dates for releases with long-term support (LTS) are marked on the x-axis. Although performance deteriorated temporarily in some periods, LTS releases generally have comparable or better performance than the previous LTS version. The significant improvement in August 2022 was caused by the column-by-column filter evaluation technique described in Section [4.4.](#page-7-0)

Figure 11: Relative hot runtimes of VersionsBench 2018-2024.

#### 6.2.2 Normalized tables[​](#6-2-2-normalized-tables "Direct link to 6.2.2 Normalized tables")

In classical warehousing, data is often modeled using star or snowfake schemas. We present runtimes of TPC-H queries (scale factor 100) but remark that normalized tables are an emerging use case for ClickHouse. [Figure 12](#page-10-6) shows the hot runtimes of the TPC-H queries based on the parallel hash join algorithm described in Section [4.4.](#page-7-0) The measurements were taken on a single-node AWS EC2 c6i.16xlarge instance with 64 vCPUs, 128 GB RAM, and 5000 IOPS / 1000 MiB/s disk. The fastest of fve runs was recorded. For reference, we performed the same measurements in a Snowfake system of comparable size (warehouse size L, 8x8 vCPUs, 8x16 GB RAM). The results of eleven queries are excluded from the table: Queries Q2, Q4, Q13, Q17, and Q20-22 include correlated subqueries which aren't supported as of ClickHouse v24.6. Queries Q7-Q9 and Q19 depend on extended plan-level optimizations for joins such as join reordering and join predicate pushdown (both missing as of ClickHouse v24.6.) to achieve viable runtimes. Automatic subquery decorrelation and better optimizer support for joins are planned for implementation in 2024 [[18]](#page-12-33). Out of the remaining 11 queries, 5 (6) queries executed faster in ClickHouse (Snowfake). As aforementioned optimizations are known to be critical for performance [[27]](#page-12-34), we expect them to improve runtimes of these queries further once implemented.

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

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