Understanding MergeTree Internals in ClickHouse

Most people learn ClickHouse from the outside in. Point it at a big table. Run a query. Marvel at how fast it comes back. But the speed isn’t magic. It comes straight from how the MergeTree engine organizes data on disk. Every INSERT, every background merge, every index lookup- they all follow a specific, observable set of rules.

Once you see those rules in action, ClickHouse stops feeling mysterious. It starts feeling predictable. This post is a hands-on walkthrough of exactly that. We’ll watch MergeTree build, merge, and query real data, using ClickHouse’s own system tables as proof, not just taking the docs’ word for it.

ClickHouse is widely known for its blazing-fast analytical query performance. However, to truly get the best out of it, understanding how its core engine MergeTree manages data under the hood is critical.

While working with ClickHouse, I wanted to understand how data moves from a simple INSERT statement into physical storage, how it handles indexing, and what actually happens during background merges. Instead of just reading the docs, I spun up a local instance and analyzed the internal system tables to see the mechanics in action.

Concepts Explored

Before diving into the hands-on tests, here is the quick, engineering-focused context of the core components we are dealing with:

Practical Hands-on

To see how ClickHouse structures data, I created a standard test_analytics table using the MergeTree engine, ordered by a timestamp and event_id composite key.

CREATE TABLE default.test_analytics
(
event_id UInt64,
timestamp DateTime,
event_name String,
metrics Float64
)
ENGINE = MergeTree
ORDER BY (timestamp, event_id);

Instead of inserting all data at once, I intentionally simulated three separate batch inserts with a few seconds of delay between them.

To see what ClickHouse did under the hood, I queried the system.parts table immediately after the inserts:

SELECT
name,
active,
rows,
bytes_on_disk
FROM system.parts
WHERE table = 'test_analytics';

Query Output & Visual Proof

Here is the exact state of the parts inside ClickHouse right after the inserts, and after a few minutes when the background merge kicked in:

Problems/Observations

The Row-by-Row Trap

Initially, I tried running a script that inserted 100 rows individually instead of batching. The system.parts table exploded with 100 active parts within seconds. My CPU usage spiked because the background merge thread had to aggressively work to clean up these tiny parts. ClickHouse literally screamed at me with a “Too many parts” warning.

This kind of part explosion is a common early-stage mistake covered in Ksolves’ broader look at data engineering services for big data pipelines.

Sparse Index Behavior

Unlike traditional databases that map an index pointer to every single row, ClickHouse created an index entry only for every 8192nd row (the default index granularity). This explains why its memory footprint is so incredibly low even with billions of rows.

Querying Unmerged Data

If I have duplicate rows across different parts, they stay duplicate until a merge happens. To force ClickHouse to merge them during query execution, I had to use the FINAL keyword:

SELECT * FROM default.test_analytics FINAL WHERE event_id = 101;

Observation: While FINAL gives accurate, deduplicated results, the query response time was noticeably slower because it does the heavy lifting of merging data on the fly in memory.

Key Learnings

• Batching is Non-Negotiable: ClickHouse is built for bulk inserts. Always aim for batches of 10,000 to 100,000+ rows at a time to prevent part explosion.
• Parts are Immutable: ClickHouse never modifies an existing part on disk. It always writes a new one and merges old ones asynchronously.
• Use FINAL Judiciously: The FINAL modifier is powerful for real-time deduplication but should be used carefully, as it bypasses parallel processing optimizations in older ClickHouse versions and increases query latency.

This immutability is also why lifecycle design matters at scale – see how Ksolves built a ClickHouse and MinIO hot-cold storage architecture to move aging parts off expensive hot storage automatically for a telecom operator.

Future Scope

In the next deep dive, I plan to set up a Kafka Engine table to stream data directly into ClickHouse to observe how it handles real-time micro-batching without breaking the MergeTree boundaries.

How Ksolves Can Help

Everything covered above, a part explosion, merge backlogs, the FINAL trade-off, is exactly the kind of thing that turns from a local experiment into a production incident once real traffic hits a table. Ksolves ClickHouse support services are built around that gap: sorting key and primary key redesign aligned with actual production query patterns, MergeTree engine audits to confirm the right engine variant is in use, and partition and merge-setting reviews that catch “too many parts” situations before they become outages.