Understanding ClickHouse MergeTree Engine: Principles, Table Creation, and Settings

ClickHouse is a column‑oriented analytical DBMS, and the MergeTree family of engines is its most important and powerful table engine.

MergeTree writes large amounts of data into parts, then merges them efficiently, storing data sorted by primary key, supporting partitioning, replication, sampling, and TTL.

Key features include primary‑key sorted storage, optional partitioning, data replication via ReplicatedMergeTree, data sampling, and TTL‑based data expiration.

1. Creating a Table

Typical CREATE TABLE syntax for a MergeTree table is shown below, with optional clauses such as PARTITION BY, ORDER BY, SAMPLE BY, and SETTINGS.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

Important clauses: ENGINE: specifies the engine; for ReplicatedMergeTree additional parameters are required. ORDER BY: defines sorting key; if PRIMARY KEY is omitted, ORDER BY is used as primary key. PARTITION BY: optional partition field, often a Date or DateTime expression. PRIMARY KEY: usually identical to ORDER BY; can differ if needed. SAMPLE BY: sampling expression, must be part of the sorting key. TTL: rules for data expiration, deletion, or moving to disk/volume. SETTINGS: engine‑specific parameters such as index_granularity, index_granularity_bytes, and others that control granule size, merge behavior, and storage policy.

2. Data Storage

Tables consist of parts sorted by primary key. Insertion creates new parts, which are later merged. Parts can be stored in Wide or Compact format depending on min_bytes_for_wide_part and min_rows_for_wide_part settings.

Each part is divided into granules, the smallest indivisible unit read by ClickHouse. Granule size is limited by index_granularity (row count) and index_granularity_bytes (byte size). Index files store marks for fast data location.