Why ClickHouse Dominates OLAP: Features, Configurations, Table Engines and Real‑World Use Cases

Introduction

ClickHouse is a high‑performance, open‑source column‑oriented DBMS designed for online analytical processing (OLAP). It stores data in columns, supports SQL queries, indexes, distributed queries, and approximate calculations, making it popular in large‑scale internet companies.

Why ClickHouse for OLAP

Typical OLAP workloads have the following characteristics:

Read‑heavy workloads

Bulk inserts (usually > 1000 rows per batch)

Rare updates or deletions

Queries read many rows but only a few columns

Wide tables with many columns

Moderate query concurrency (hundreds of queries per second per server)

Low latency requirements (≈ 50 ms for simple queries)

Small per‑column data (numeric or short strings)

High throughput (billions of rows per server per second)

No strict transaction guarantees

Relaxed consistency requirements

Result sets fit in a single server’s memory after filtering/aggregation

Columnar storage excels in this scenario because it can read only the required columns, achieve high compression ratios, keep more data in cache, and fully utilize CPU vector instructions.

Key ClickHouse Features

Automatic data compression

Disk‑based storage with low cost

Multi‑core parallel processing

Distributed queries across shards

SQL‑compatible query language

Vector engine for efficient CPU usage

Real‑time data ingestion with MergeTree engines

Primary‑key sparse indexes

Support for approximate calculations

Configuration Files

01. Main configuration (config.xml)

The main file resides at /etc/clickhouse-server/config.xml. Additional files are placed in /etc/clickhouse-server/config.d and merged recursively. Settings can be overridden with replace or remove attributes.

02. Data path configuration <path>/path/to/clickhouse/</path> Data and metadata are stored under the specified directory. Symbolic links can be used to spread data across multiple physical disks.

03. Log configuration

<logger>
    <level>trace</level>
    <log>/path/to/clickhouse-server/clickhouse-server.log</log>
    <errorlog>/path/to/clickhouse-server/clickhouse-server.err.log</errorlog>
    <size>1000M</size>
    <count>10</count>
</logger>

Log levels include trace, debug, information, warning, error, etc. Logs are written concurrently by multiple threads, so entries may be interleaved; each event has a unique ID for tracing.

04. Cluster configuration

<yandex>
    <remote_servers>
        <cluster_name>
            <shard>
                <weight>1</weight>
                <internal_replication>false</internal_replication>
                <replica>
                    <host>hostname1</host>
                    <port>9000</port>
                </replica>
            </shard>
            ...
        </cluster_name>
    </remote_servers>
</yandex>

Shards represent ClickHouse nodes; weight influences load balancing. Replication can be enabled with internal_replication=true.

05. Dictionary configuration

<dictionaries_config>dictionaries/*_dictionary.xml</dictionaries_config>

Dictionary files are loaded from the dictionaries directory and define key‑value mappings for fast lookups.

06. User configuration

<users_config>users.xml</users_config>

Users are defined in users.xml (or files under users.d). The default user is default with an empty password. Passwords can be stored as SHA‑256 hashes: echo -n "$PASSWORD" | sha256sum | tr -d '-' Profiles control permissions (e.g., read‑only) and resource limits such as max_threads and max_memory_usage.

07. ZooKeeper configuration

<yandex>
    <zookeeper replace="true">
        <node index="1">
            <host>hostname1</host>
            <port>2181</port>
        </node>
        ...
    </zookeeper>
</yandex>

ZooKeeper stores metadata for replicated tables; it can be omitted when replication is not used.

08. Macros configuration

<yandex>
    <macros replace="true">
        <shard>01</shard>
        <replica>hostname/ip</replica>
    </macros>
</yandex>

Macros are used to substitute parameters in replicated table definitions.

09. Prometheus configuration

<prometheus>
    <endpoint>/metrics</endpoint>
    <port>9363</port>
    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
</prometheus>

Exposes ClickHouse metrics at http://<host>:9363/metrics.

10. MergeTree settings

<merge_tree>
    <parts_to_throw_insert>300</parts_to_throw_insert>
</merge_tree>

Controls the maximum number of active parts per partition before an exception is thrown.

11. Miscellaneous settings

Timezone: <timezone>Asia/Shanghai</timezone> Max connections: <max_connections>4096</max_connections> Max concurrent queries:

<max_concurrent_queries>200</max_concurrent_queries>

Server memory limit:

<max_server_memory_usage>0</max_server_memory_usage>

(0 = unlimited)

Maximum table size for DROP:

<max_table_size_to_drop>0</max_table_size_to_drop>

(0 = no limit)

Table Engines Overview

ClickHouse provides a rich set of table engines, each governing storage layout, indexing, replication, and query capabilities.

MergeTree family (core engine)

Data is written in immutable parts; a background thread merges parts within the same partition. Primary key creates a sparse index for fast range pruning.

CREATE TABLE [IF NOT EXISTS] db.table_name (
    col1 UInt16 COMMENT 'id',
    col2 String COMMENT 'name',
    ...
) ENGINE = MergeTree()
ORDER BY col1
PARTITION BY work_place;

Key clauses: ORDER BY – sorting key (mandatory) PARTITION BY – optional partition expression PRIMARY KEY – optional, defaults to

ORDER BY

SAMPLE BY

– optional sampling key TTL – data expiration policy SETTINGS – engine‑specific parameters

ReplacingMergeTree

Provides deduplication based on the ORDER BY key (not the primary key). Optional version column determines which row wins.

CREATE TABLE db.emp_replacing (
    emp_id UInt16,
    name String,
    work_place String,
    age UInt8,
    depart String,
    salary Decimal32(2)
) ENGINE = ReplacingMergeTree()
ORDER BY emp_id
PARTITION BY work_place;

During OPTIMIZE FINAL, rows with the same ORDER BY values are collapsed; if a version column is defined, the row with the highest version is kept.

SummingMergeTree

Aggregates numeric columns for rows sharing the same ORDER BY key during merges, similar to a GROUP BY operation.

CREATE TABLE db.emp_summing (
    emp_id UInt16,
    name String,
    work_place String,
    age UInt8,
    depart String,
    salary Decimal32(2)
) ENGINE = SummingMergeTree(salary)
ORDER BY (emp_id, name)
PARTITION BY work_place;

AggregatingMergeTree

Allows custom aggregate functions (e.g., uniqExact) to be stored and merged.

CollapsingMergeTree & VersionedCollapsingMergeTree

Implements logical deletes via a sign column (1 = insert, -1 = delete). The versioned variant adds a version column to tolerate out‑of‑order inserts.

Log series (TinyLog, StripeLog, Log)

Simple engines for small, write‑once tables. TinyLog stores each column in a separate file; StripeLog stores all columns in a single file with a shared index; Log combines the advantages of both and offers parallel reads.

External integration engines

ClickHouse can read from external systems without storing data locally:

ODBC / JDBC – connect to relational databases

MySQL – read MySQL tables (read‑only)

HDFS – read files directly from Hadoop

Kafka – consume streaming data (requires a materialized view to materialize the stream)

RabbitMQ – similar to Kafka

Example Kafka engine definition:

CREATE TABLE kafka_events (
    event_id UInt64,
    payload String
) ENGINE = Kafka()
SETTINGS kafka_broker_list = 'kafka:9092',
        kafka_topic_list = 'events',
        kafka_group_name = 'clickhouse_group',
        kafka_format = 'JSONEachRow';

A materialized view then inserts data into a MergeTree table for persistent storage.

Memory and Distributed engines

Memory

stores data only in RAM (lost on restart). Distributed routes inserts to underlying local tables across a cluster and performs query routing based on sharding keys.

Common Issues and Troubleshooting

Long startup time – caused by many parts; wait for background merges.

"too many parts" exception – insert rate too high; increase batch_size and reduce insert frequency.

Replica becomes read‑only – ZooKeeper connectivity loss; restore network or convert to non‑replicated table.

JOIN memory overflow – add filters or increase memory limits in max_memory_usage.

General debugging steps:

Check ClickHouse service status.

Inspect clickhouse-server.log and error.log.

Review system logs (/var/log/messages) for OS‑level issues.

Open a GitHub issue with detailed logs for unknown bugs.

Real‑World Use Cases

Recommendation System Real‑Time Metrics

ByteDance needed sub‑second visibility into AB experiment results. ClickHouse was chosen over Druid and Elasticsearch because it supports:

Fine‑grained detail queries

Dynamic dimensions via Map columns

Bloom filter indexes for fast experiment‑ID filtering

Built‑in statistical functions (e.g., AUC)

Two deployment options were evaluated:

Flink → JDBC → ClickHouse (clear component boundaries but adds latency and operational overhead)

ClickHouse native Kafka engine (simpler, lower latency, but requires careful handling of consumer concurrency and fault tolerance)

The native Kafka engine was adopted. Improvements included asynchronous index building (≈ 20 % higher write throughput) and multi‑threaded consumption to achieve near‑linear scaling.

Advertising Real‑Time Delivery Metrics

Initial solution used Druid, which struggled with high partition counts and many dimensions. ClickHouse’s columnar storage and ability to ingest raw detail data eliminated the need for pre‑aggregation.

Key enhancements:

Buffer engine integrated into the Kafka pipeline to reduce part churn while preserving consistency for replicated tables.

Leader election via ZooKeeper ensures only one replica consumes from Kafka, avoiding duplicate consumption and guaranteeing query correctness during node failures.

Transactional offset‑part binding guarantees exactly‑once semantics even after crashes.

Performance Insights

ClickHouse stores each column separately, enabling it to read only the required columns. Combined with LZ4 compression (≈ 8:1 ratio), vectorized execution, sparse indexes, and a distributed multi‑master architecture, it can outperform traditional row‑store databases by orders of magnitude (official benchmarks report > 800× speedup over MySQL for typical analytical queries).

Key performance techniques include:

Reading only needed columns reduces I/O.

Vector engine leverages SIMD for parallel computation.

Sparse indexes skip irrelevant data blocks.

Distributed query execution balances load across nodes.

Conclusion

ClickHouse’s columnar architecture, rich set of table engines, and flexible configuration make it a powerful platform for high‑throughput OLAP workloads, real‑time analytics, and large‑scale data pipelines. Proper tuning of configuration parameters, understanding of engine semantics, and careful handling of ingestion pipelines (especially with Kafka) are essential to unlock its full performance potential.