Inside ClickHouse INSERT: Data Parts, Background Merges, and Best Practices

Why Fast ClickHouse Clusters Sometimes Struggle

ClickHouse has earned its reputation as an ultra-fast analytical DBMS capable of processing billions of events per second. Teams choose it for analytics dashboards, financial reporting, and complex behavioral tracking. However, when software engineers treat ClickHouse like a traditional transactional database, unexpected bottlenecks quickly emerge: sluggish write operations, incorrect query results, or the dreaded Too many parts error.

These issues typically stem from a mismatch between expectations and reality. ClickHouse manages disk storage and incoming writes fundamentally differently from systems like PostgreSQL. Understanding how a single INSERT query travels through the storage engine is essential for building scalable backend architectures.

The Anatomy of an INSERT: Everything Is a Part

In ClickHouse tables powered by the MergeTree family, an incoming row is never simply appended to an existing file. Every single write query creates a brand-new, completely immutable storage entity called a data part.

Each part is a distinct directory on the filesystem containing compressed columnar data files, sparse primary indexes, mark files, and integrity metadata. Writing just a single record forces the engine to initialize this entire file structure. The disk overhead of the metadata can dwarf the actual payload by several orders of magnitude. Conversely, inserting a batch of 100,000 rows generates the same single part directory, but with optimal compression ratios and negligible overhead.

Understanding the Too Many Parts Error

Under the hood, ClickHouse continuously runs background merges, combining smaller parts into larger, consolidated blocks. However, disk I/O and CPU throughput have physical boundaries. If your applications generate new parts faster than background threads can merge them, ClickHouse throttles or rejects incoming writes to protect server integrity.

This bottleneck usually stems from two architectural mistakes:

• High-frequency micro-inserts: Treating ClickHouse like an OLTP database by inserting individual rows per API request.
• Excessive partition granularity: Defining partition keys down to the minute or hour creates dozens of tiny parts across multiple directories from a single multi-row batch.

The standard solution is client-side batching. Accumulate records in memory, in message brokers like Kafka, or configure ClickHouse's native async_insert feature, which buffers small writes on the server before writing consolidated parts to disk.

The Nuances of ReplacingMergeTree and Data Mutation

Another common source of confusion is deduplication. When developers use ReplacingMergeTree to maintain up-to-date entities like orders or customer profiles, they often expect immediate replacements. In reality, merges are asynchronous and lazy:

• Delayed deduplication: Duplicate rows coexist across different parts until a background merge combines them. If an application needs strict point-in-time consistency, queries must explicitly use the FINAL modifier or aggregate via specific window functions.
• Partition isolation: Merges never cross partition boundaries. If an updated record carries a timestamp that moves it to a different monthly partition than its predecessor, both records will remain indefinitely.

Guidelines for Resilient Production Setups

To maximize ClickHouse efficiency in web and data-heavy applications, design schemas with batch writes in mind, partition tables responsibly (typically by month or day), and monitor the system.parts table to track part counts before thresholds are breached.