Architecture Overview

**Joins**. As ClickHouse originally supported joins only rudimentarily, many use cases historically resorted to denormalized tables. Today, the database [offers](https://clickhou.se/joins) all join types available in SQL (inner, left- /right/full outer, cross, as-of), as well as different join algorithms such as hash join (naïve, grace), sort-merge join, and index join for table engines with fast key-value lookup (usually dictionaries).

Since joins are among the most expensive database operations, it is important to provide parallel variants of the classic join algorithms, ideally with configurable space/time trade-ofs. For hash joins, ClickHouse implements the non-blocking, shared partition algorithm from [[7]](#page-12-23). For example, the query in [Figure 9](#page-8-3) computes how users move between URLs via a self-join on a page hit statistics table. The build phase of the join is split into three lanes, covering three disjoint ranges of the source table. Instead of a global hash table, a partitioned hash table is used. The (typically three) worker threads determine the target partition for each input row of the build side by computing the modulo of a hash function. Access to the hash table partitions is synchronized using Gather exchange operators. The probe phase finds the target partition of its input tuples similarly. While this algorithm introduces two additional hash calculations per tuple, it greatly reduces latch contention in the build phase, depending on the number of hash table partitions.

Figure 9: Parallel hash join with three hash table partitions.

### 4.5 Workload Isolation[​](#4-5-workload-isolation "Direct link to 4-5-workload-isolation")

ClickHouse offers concurrency control, memory usage limits, and I/O scheduling, enabling users to isolate queries into workload classes. By setting limits on shared resources (CPU cores, DRAM, disk and network I/O) for specific workload classes, it ensures these queries don't affect other critical business queries.

Concurrency control prevents thread oversubscription in scenarios with a high number of concurrent queries. More specifically, the number of worker threads per query are adjusted dynamically based on a specified ratio to the number of available CPU cores.

ClickHouse tracks byte sizes of memory allocations at the server, user, and query level, and thereby allows to set flexible memory usage limits. Memory overcommit enables queries to use additional free memory beyond the guaranteed memory, while assuring memory limits for other queries. Furthermore, memory usage for aggregation, sort, and join clauses can be limited, causing fallbacks to external algorithms when the memory limit is exceeded.

Lastly, I/O scheduling allows users to restrict local and remote disk accesses for workload classes based on a maximum bandwidth, in-fight requests, and policy (e.g. FIFO, SFC [[32]](#page-12-24)).

### 5 INTEGRATION LAYER[​](#5-integration-layer "Direct link to 5-integration-layer")

Real-time decision-making applications often depend on efficient and low-latency access to data in multiple locations. Two approaches exist to make external data available in an OLAP database. With push-based data access, a third-party component bridges the database with external data stores. One example of this are specialized extract-transform-load (ETL) tools which push remote data to the destination system. In the pull-based model, the database itself connects to remote data sources and pulls data for querying into local tables or exports data to remote systems. While push-based approaches are more versatile and common, they entail a larger architectural footprint and scalability bottleneck. In contrast, remote connectivity directly in the database offers interesting capabilities, such as joins between local and remote data, while keeping the overall architecture simple and reducing the time to insight.

The rest of the section explores pull-based data integration methods in ClickHouse, aimed to access data in remote locations. We note that the idea of remote connectivity in SQL databases isn't new. For example, the SQL/MED standard [[35]](#page-12-25), introduced in 2001 and implemented by PostgreSQL since 2011 [[65]](#page-13-21), proposes foreign data wrappers as a unified interface for managing external data. Maximum interoperability with other data stores and storage formats is one of ClickHouse's design goals. As of March 2024, ClickHouse offers to the best of our knowledge the most built-in data integration options across all analytical databases.

External Connectivity. ClickHouse provides [50+](https://clickhou.se/query-integrations) integration table functions and engines for connectivity with external systems and storage locations, including ODBC, MySQL, PostgreSQL, SQLite, Kafka, Hive, MongoDB, Redis, S3/GCP/Azure object stores and various data lakes. We break them further down into categories shown by the following bonus figure (not part of the original vldb paper).

Bonus Figure: Interoperability options of ClickBench.

Temporary access with Integration **Table Functions**. Table functions can be invoked in the FROM clause of SELECT queries to read remote data for exploratory ad-hoc queries. Alternatively, they can be used to write data to remote stores using INSERT INTO TABLE FUNCTION statements.

Persisted access. Three methods exist to create permanent connections with remote data stores and processing systems.

First, integration **table engines** represent a remote data source, such as a MySQL table, as a persistent local table. Users store the table definition using CREATE TABLE AS syntax, combined with a SELECT query and the table function. It is possible to specify a custom schema, for example, to reference only a subset of the remote columns, or use schema inference to determine the column names and equivalent ClickHouse types automatically. We further distinguish passive and active runtime behavior: Passive table engines forward queries to the remote system and populate a local proxy table with the result. In contrast, active table engines periodically pull data from the remote system or subscribe to remote changes, for example, through PostgreSQL's logical replication protocol. As a result, the local table contains a full copy of the remote table.

Second, integration **database engines** map all tables of a table schema in a remote data store into ClickHouse. Unlike the former, they generally require the remote data store to be a relational database and additionally provide limited support for DDL statements.

Third, **dictionaries** can be populated using arbitrary queries against almost all possible data sources with a corresponding integration table function or engine. The runtime behavior is active since data is pulled in constant intervals from remote storage.

Data Formats. To interact with 3rd party systems, modern analytical databases must also be able to process data in any format. Besides its native format, ClickHouse supports [90+](https://clickhou.se/query-formats) formats, including CSV, JSON, Parquet, Avro, ORC, Arrow, and Protobuf. Each format can be an input format (which ClickHouse can read), an output format (which ClickHouse can export), or both. Some analytics-oriented formats like Parquet are also integrated with query processing, i.e, the optimizer can exploit embedded statistics, and filters are evaluated directly on compressed data.

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.