ClickHouse is a fast analytics performance on large-scale datasets. At the heart of this capability lies the MergeTree family of table engines. This blog post will explore the core principles, architecture, and strengths of the MergeTree engine, highlighting why it's the default choice for high-performance analytical workloads.
1. What Are Table Engines in ClickHouse?
Table engines, also known as storage engines, are responsible for how data is organized on disk and accessed during queries. Table engines are fundamental components in ClickHouse that determine how your data is:
· Stored on disk: The physical organization of data files
· Retrieved during queries: How data is accessed when reading
· Written during inserts: How new data is incorporated
· Compressed: The efficiency of storage utilization
· Indexed: The availability of data structures for fast lookups
The MergeTree engine family is the most commonly used and feature-rich group of engines in ClickHouse. It includes several variants like:
· MergeTree
· ReplacingMergeTree
· SummingMergeTree
· AggregatingMergeTree
· CollapsingMergeTree
· VersionedCollapsingMergeTree
· GraphiteMergeTree
These engines are designed for high ingestion rates and petabyte-scale datasets, and they power many production systems where speed and reliability are critical.
2. Why Is It Called the MergeTree Engine?
The MergeTree engine family gets its name from the internal way ClickHouse manages and organizes data by continuously merging smaller chunks of data into larger and more optimized ones in the background. This merge process not only improves performance but also significantly reduces storage fragmentation over time.
Let’s understand it with a practical example. Assume you've created a table using the MergeTree engine in ClickHouse. When you insert data into this table, it creates a new data part file, a small file that contains the newly inserted data and stores it on disk in the directory corresponding to the table.
To better understand this structure, it helps to explore how ClickHouse organizes its data at the filesystem level.
2.1 Exploring the ClickHouse Data Directory
By default, ClickHouse stores all data under the following directory on the server.
/var/lib/clickhouse
Inside this directory, there is a subdirectory named data which contains a folder for each database. Let’s check the contents of the data directory.
$ cd /var/lib/clickhouse/data $ ls analytics_db archive_db default system
As you can see, there are four directories corresponding to the four user-defined or system-defined databases in your ClickHouse instance. These are:
· analytics_db
· archive_db
· default
· system
You can confirm the same set of databases directly within ClickHouse using the SHOW DATABASES command:
krishna :) SHOW DATABASES; SHOW DATABASES Query id: f44b757e-6d35-4955-8877-b19246ffa282 ┌─name───────────────┐ │ INFORMATION_SCHEMA │ │ analytics_db │ │ archive_db │ │ default │ │ information_schema │ │ system │ └────────────────────┘ 6 rows in set. Elapsed: 0.002 sec.
Let's begin by creating a new database named demo_db. This command ensures the database is only created if it doesn’t already exist.
CREATE DATABASE IF NOT EXISTS demo_db;
Once the database is successfully created, ClickHouse will create a corresponding folder under the data directory, typically located at:
/var/lib/clickhouse/data/
To verify this, you can list the contents of the data directory:
$ ls /var/lib/clickhouse/data analytics_db archive_db default demo_db system
You should now see a new directory named demo_db, which stores the metadata and data files for this database.
Next, let’s create a table named orders inside the demo_db database using the MergeTree engine, one of the most commonly used and powerful engines in ClickHouse. It provides high-performance data insertion and querying, and supports features such as partitioning, order keys, and data skipping indexes.
CREATE TABLE demo_db.orders ( order_id UInt32, customer_id UInt32, order_date Date, order_amount Float32, status String ) ENGINE = MergeTree PARTITION BY toYYYYMM(order_date) ORDER BY (order_date, order_id);
Once the orders table is created, ClickHouse will generate a directory for it under the demo_db folder.
$ cd /var/lib/clickhouse/data/demo_db $ ls orders
You’ll see a new folder named orders, which corresponds to the table we just created. Navigate into the orders directory to inspect its contents.
$ cd orders $ ls detached format_version.txt
· detached/: A directory used for managing parts of the data that are temporarily detached from the active dataset.
· format_version.txt: A file that stores the format version of the table's storage structure.
To check the contents of format_version.txt:
$ cat format_version.txt 1
This indicates the storage format version being used for this table.
Now that we have created the orders table inside the demo_db database, let's insert some sample records using the INSERT INTO statement.
INSERT INTO demo_db.orders VALUES (1, 101, '2025-05-01', 250.50, 'Pending'), (2, 102, '2025-05-01', 120.00, 'Shipped'), (3, 103, '2025-05-02', 300.75, 'Delivered'), (4, 104, '2025-05-03', 450.00, 'Pending'), (5, 105, '2025-05-03', 75.25, 'Cancelled');
This command inserts five rows into the orders table with various values for order_id, customer_id, order_date, order_amount, and status.
ClickHouse is a column-oriented database, and under the hood, it stores data in files organized by parts and columns. After the insert operation, you can observe the internal data structure by listing the contents of the orders table directory.
$ tree orders orders ├── 202505_1_1_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── detached └── format_version.txt
A new folder named 202505_1_1_0 has been created. This is a data part, which is the basic unit of storage in MergeTree-based engines. The prefix 202505 corresponds to the partition key (i.e., toYYYYMM(order_date)), meaning this data part belongs to May 2025. The rest of the name _1_1_0 denotes the block range and mutation version metadata.
Each data part contains several files:
columns.txt: Lists the columns in the table along with their data types.
$ cat orders/202505_1_1_0/columns.txt columns format version: 1 5 columns: `order_id` UInt32 `customer_id` UInt32 `order_date` Date `order_amount` Float32 `status` String count.txt: Indicates the number of rows in this data part. $ cat orders/202505_1_1_0/count.txt 5
serialization.json: Describes how each column is serialized, including the number of rows and whether default values were used.
$ cat orders/202505_1_1_0/serialization.json
{
"columns": [
{"kind": "Default", "name": "customer_id", "num_defaults": 0, "num_rows": 5},
{"kind": "Default", "name": "order_amount", "num_defaults": 0, "num_rows": 5},
{"kind": "Default", "name": "order_date", "num_defaults": 0, "num_rows": 5},
{"kind": "Default", "name": "order_id", "num_defaults": 0, "num_rows": 5},
{"kind": "Default", "name": "status", "num_defaults": 0, "num_rows": 5}
],
"version": 0
}
Files like data.bin, data.cmrk4, primary.cidx, minmax_order_date.idx: These store the actual column data, indexing information, and metadata required for fast query execution.
In ClickHouse, each insert operation results in the creation of a new data part on disk. Each data part is stored as a folder that contains multiple files — each file represents a specific aspect of the table’s structure or data (column values, marks, indexes, etc.).
Let’s insert more records into the orders table and observe how ClickHouse reflects this change in its internal storage structure.
INSERT INTO demo_db.orders VALUES (6, 106, '2025-05-04', 199.99, 'Delivered'), (7, 107, '2025-05-04', 650.00, 'Pending'), (8, 108, '2025-05-05', 89.95, 'Shipped'), (9, 109, '2025-05-06', 1200.00, 'Delivered'), (10, 110, '2025-05-06', 430.45, 'Pending');
This batch inserts five additional records into the orders table, all belonging to the partition 202505 (May 2025), since the partition key is defined as toYYYYMM(order_date).
After the second insert operation, we can again inspect the orders table directory using the tree command:
$ tree orders orders ├── 202505_1_1_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── 202505_2_2_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── detached └── format_version.txt 4 directories, 25 files
What Changed?
· A new data part folder named 202505_2_2_0 has been created.
· Just like the earlier part 202505_1_1_0, this new part contains column data, metadata, index files, and compression details.
· Both parts belong to the same partition (202505), but they represent different insert batches.
· The naming pattern 202505_<min_block>_<max_block>_<level> tells us:
o This is the second data part (min_block = 2, max_block = 2),
o It hasn't undergone any merges yet (level = 0).
Let’s insert third set of records into orders table.
INSERT INTO demo_db.orders VALUES (11, 111, '2025-05-07', 220.30, 'Shipped'), (12, 112, '2025-05-08', 98.75, 'Delivered'), (13, 113, '2025-05-08', 75.00, 'Pending'), (14, 114, '2025-05-09', 499.99, 'Shipped'), (15, 115, '2025-05-09', 310.10, 'Delivered'), (16, 116, '2025-05-10', 845.25, 'Pending'), (17, 117, '2025-05-10', 65.50, 'Cancelled'), (18, 118, '2025-05-11', 155.75, 'Delivered'), (19, 119, '2025-05-11', 205.60, 'Pending'), (20, 120, '2025-05-12', 134.90, 'Shipped');
This inserts 10 more rows into the orders table. Now let’s take a look at what happens under the hood in the ClickHouse file system after these inserts.
$tree orders orders ├── 202505_1_1_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── 202505_2_2_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── 202505_3_3_0 │ ├── checksums.txt │ ├── columns_substreams.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.cmrk4 │ ├── default_compression_codec.txt │ ├── metadata_version.txt │ ├── minmax_order_date.idx │ ├── partition.dat │ ├── primary.cidx │ └── serialization.json ├── detached └── format_version.txt 5 directories, 37 files
To confirm the number of rows in the most recent part:
$ cat orders/202505_3_3_0/count.txt 10
This confirms that 10 rows from our most recent bulk insert are stored in the 202505_3_3_0 part.
2.2 Merging the Data Parts
One of the defining characteristics of the MergeTree engine in ClickHouse is how it manages and organizes data on disk. When you insert data into a MergeTree table, ClickHouse doesn't immediately write everything into a single large file. Instead, it creates individual data parts such as small, sorted subsets of the table data and stored on disk in the ClickHouse data directory.
Over time, as more inserts are made and new data parts are created, ClickHouse triggers a background merge process. This process:
· Identifies smaller data parts that can be merged.
· Combines them into a larger data part.
· Sorts the data again using the order key columns, maintaining order to optimize range queries and index usage.
· Writes the merged and sorted data back to disk as a new data part.
This merging process is automatic and happens asynchronously in the background, ensuring that insert and query operations remain fast and non-blocking.
By default, ClickHouse sets a maximum data part size of 150 GB. Once this size is reached, that part is no longer merged further. New inserts will create new parts, which in turn will go through the same merge cycle. This threshold can be adjusted via configuration settings like max_bytes_to_merge_at_max_space_in_pool.
The name MergeTree clearly describes how this engine works:
· "Merge" means that ClickHouse keeps combining smaller chunks of data into larger, more efficient ones in the background.
· "Tree" refers to the organized structure of the data, especially how it uses sorting and indexing (like the partitions) to make searching fast and efficient.
References
https://clickhouse.com/docs/best-practices/avoid-optimize-final#it-ignores-safety-limits
Previous Next Home
No comments:
Post a Comment