Inside ClickHouse: How MergeTree Tables Store Data on Disk

  

ClickHouse is a high-performance OLAP database designed for fast analytical queries. While developers and data engineers often interact with ClickHouse at a high level like creating tables, inserting data, running analytical queries, it's equally important to understand how ClickHouse organizes and stores that data on disk, especially for performance tuning and debugging.

 

In this post, we’ll explore how data is structured under the hood when using MergeTree tables, the most used table engine in ClickHouse. We’ll also look into the actual files and directories that ClickHouse creates inside its storage path (/var/lib/clickhouse/data).

 

Let’s assume the default ClickHouse data storage path is /var/lib/clickhouse/data. When you navigate to this directory, you’ll see subdirectories representing databases that have been created in your ClickHouse instance.

$ ls -lart /var/lib/clickhouse/data
total 0
drwxr-x---   2 krishna  test   64  4 May 19:05 system
drwxr-x---   3 krishna  test   96  7 May 15:43 archive_db
drwxr-x---   3 krishna  test   96  7 May 19:46 analytics_db
drwxr-x---   5 krishna  test  160 15 May 10:15 default
drwxr-xr-x@ 14 krishna  test  448 19 May 09:38 ..
drwxr-x---   7 krishna  test  224 19 May 17:08 .
drwxr-x---   3 krishna  test   96 19 May 17:08 demo_db

   

Here we can see five databases:

 

·      system (default internal system metadata)

·      archive_db

·      analytics_db

·      default

·      demo_db

 

Each database directory contains subdirectories for its tables. Let’s explore the demo_db database.

 

$ cd demo_db
$ ls
orders          orders_granule_example

   

In this example, the demo_db contains two tables:

·      orders and

·      orders_granule_example.

 

Let’s go one step deeper into orders_granule_example to see how ClickHouse organizes its data files.

 

$ tree orders_granule_example
orders_granule_example
├── all_1_1_0
│   ├── checksums.txt
│   ├── columns_substreams.txt
│   ├── columns.txt
│   ├── count.txt
│   ├── data.bin
│   ├── data.cmrk4
│   ├── default_compression_codec.txt
│   ├── metadata_version.txt
│   ├── primary.cidx
│   └── serialization.json
├── detached
└── format_version.txt

all_1_1_0/

This is a data part directory. In MergeTree, data is written in parts, and these parts are later merged. The name all_1_1_0 indicates:

 

all: the partition (if no partitioning is specified, it's all)

1_1_0: the min block, max block, and level of the part

 

Files inside all_1_1_0/:

·      columns.txt: Lists all columns in the data part.

·      columns_substreams.txt: Metadata about column substreams (used during compression).

·      count.txt: Number of rows in this part.

·      checksums.txt: Contains checksums for all files for integrity verification.

·      data.bin, data.cmrk4: Actual column data and mark files for fast seek. A mark file contains offset pointers (called marks) that allow ClickHouse to quickly locate data inside the corresponding .bin (binary data) files.

·      primary.cidx: Stores values for the primary key index.

·      serialization.json: Information about how columns are serialized.

·      default_compression_codec.txt: Indicates the compression method used.

·      metadata_version.txt: Metadata schema version.

 

detached/

This is a special folder used when parts are detached manually or by the system (for example, for backup or manual inspection). These parts are not currently active in the table but are preserved.

format_version.txt

Contains the format version for the MergeTree table directory.

 

Understanding this storage layout can help you with:

 

·      Debugging: Inspecting files when facing data corruption or merge issues.

·      Backups: Manually copying specific data parts for backup.

·      Performance Tuning: Knowing how many parts exist and how merges work can help optimize performance.

·      Low-Level Exploration: For developer building tools that work with ClickHouse.

 

In summary, ClickHouse’s MergeTree engine is optimized for performance, and part of that optimization comes from how it organizes and stores data on disk. Each table is composed of parts, each part has its own directory and set of files that manage data, indexes, checksums, and more.

 

Exploring the physical file layout of a table can give you insights that help with debugging, maintenance, and system optimization. Next time you're running a query in ClickHouse, remember that a lot is happening behind the scenes.

 

Previous                                                    Next                                                    Home