Introduction
One of the key reasons ClickHouse® delivers exceptional performance for analytical workloads is its ability to efficiently manage data in the background. Unlike traditional databases that update data in place, ClickHouse stores data as immutable parts. Every new insert creates a separate data part, and background merge processes continuously combine these smaller parts into larger, optimized ones.
This process, commonly referred to as compaction or background merging, improves query performance, reduces storage overhead, and enables several MergeTree engines to perform operations such as deduplication and aggregation automatically.
For many workloads, the default merge behavior is sufficient. However, environments with continuous streaming data, massive ingestion rates, or multi-tenant deployments often require additional tuning to keep merges from becoming a bottleneck.
In this article, we'll explore how compaction works in ClickHouse, why it matters, and the strategies you can use to optimize merge performance for large-scale deployments.
Understanding Compaction in ClickHouse
Whenever data is inserted into a MergeTree table, ClickHouse writes it into a new immutable data part instead of modifying existing files.
For example:
INSERT INTO events VALUES (...);
INSERT INTO events VALUES (...);
INSERT INTO events VALUES (...);
Instead of creating one large file, ClickHouse creates multiple independent parts.
Part A
Part B
Part C
Part D
Background merge threads later combine these parts into a larger, optimized part.
Part A
Part B
Part C
Part D
│
▼
Merged Part ABCD
This continuous merging process helps maintain efficient storage while minimizing the number of files that queries need to scan.
Why Background Merges Matter
Without background compaction, a ClickHouse table would accumulate thousands—or even millions—of tiny parts over time.
This creates several performance issues:
- Queries must open and scan many files.
- Metadata management becomes increasingly expensive.
- Disk seeks increase significantly.
- Compression efficiency decreases.
- Query latency gradually rises.
Regular background merges solve these problems by:
- Reducing the total number of data parts
- Improving compression ratios
- Lowering metadata overhead
- Speeding up analytical queries
- Optimizing storage utilization
Background merges are one of the primary reasons ClickHouse maintains excellent performance even under continuous ingestion workloads.
How ClickHouse Chooses Parts to Merge
ClickHouse continuously evaluates every partition and selects merge candidates using an internal scheduling algorithm.
Several factors influence merge selection:
- Number of active parts
- Size of each part
- Available disk space
- Estimated merge cost
- Background thread availability
- Current server workload
For example, consider a monthly partition:
Partition: 2026-07
Part1 40 MB
Part2 35 MB
Part3 38 MB
Part4 42 MB
│
▼
Merged Part
155 MB
Instead of creating one massive merge operation immediately, ClickHouse performs merges incrementally to balance resource utilization while minimizing impact on running queries.
When Default Compaction Isn't Enough
The default merge strategy works well for many deployments.
However, certain workloads generate data faster than background merges can process it.
Typical examples include:
| Workload | Challenge |
|---|---|
| Streaming ingestion | Thousands of tiny parts |
| IoT platforms | Continuous sensor updates |
| Log analytics | Extremely high write rates |
| Multi-tenant clusters | Uneven merge distribution |
| Large analytical clusters | Heavy background merge activity |
In these scenarios, custom tuning becomes essential to prevent merge backlogs.
Strategy 1: Increase Insert Batch Size
One of the easiest ways to improve merge performance is simply inserting larger batches.
Instead of repeatedly inserting:
1,000 rows
1,000 rows
1,000 rows
Insert:
100,000 rows
Larger batches create fewer data parts.
Benefits include:
- Reduced merge frequency
- Lower CPU usage
- Better compression
- Higher ingestion throughput
- Less metadata overhead
Batching inserts is often the single most effective optimization for write-heavy systems.
Strategy 2: Tune Background Merge Threads
ClickHouse performs merges using dedicated background worker threads.
Important settings include:
background_pool_size
background_merges_mutations_concurrency_ratio
Increasing these values allows more merge operations to execute simultaneously.
However, increasing merge concurrency should only be done if sufficient resources are available.
Consider:
- CPU capacity
- Disk throughput
- Memory availability
- Overall workload balance
Excessive concurrency may compete with user queries for system resources.
Strategy 3: Configure Maximum Merge Sizes
MergeTree tables expose several settings that control how aggressively parts are merged.
Common examples include:
max_bytes_to_merge_at_max_space_in_pool
max_bytes_to_merge_at_min_space_in_pool
These settings determine the largest merge operation allowed depending on available disk space.
Proper tuning helps:
- Avoid extremely large merge operations
- Prevent excessive temporary disk usage
- Improve merge scheduling efficiency
Strategy 4: Use OPTIMIZE TABLE Carefully
Manual compaction can be triggered using:
OPTIMIZE TABLE events FINAL;
This forces all eligible parts within a partition to merge into a single part.
Although useful, this command is resource intensive.
It consumes:
- CPU
- Memory
- Disk bandwidth
Good use cases include:
- Before exporting historical data
- After large backfill operations
- During scheduled maintenance windows
Avoid running OPTIMIZE FINAL repeatedly on actively written production tables, as it can interfere with normal background merge scheduling.
Strategy 5: Design Effective Partitions
Partitions define merge boundaries.
For example:
PARTITION BY toYYYYMM(event_time)
Each month is merged independently.
Instead of merging an entire 5 TB dataset:
5 TB
ClickHouse merges:
January
February
March
Smaller partitions result in:
- Faster merge operations
- Better resource utilization
- Reduced merge latency
- Improved maintenance
Choosing an appropriate partition key is one of the most important design decisions for large ClickHouse deployments.
Strategy 6: Avoid Tiny Inserts
Many applications insert one row at a time.
Instead, use buffered writes:
Application
│
Batch Buffer
│
ClickHouse
Batching inserts reduces:
- Number of data parts
- Merge pressure
- Metadata growth
At the same time, it improves:
- Compression
- Throughput
- Storage efficiency
Monitoring Merge Activity
ClickHouse exposes several system tables that make monitoring straightforward.
Current Merge Operations
SELECT *
FROM system.merges;
Useful information includes:
- Table name
- Partition
- Merge progress
- Elapsed time
- Source parts
- Resulting part
Active Parts
SELECT
partition,
count()
FROM system.parts
WHERE active
GROUP BY partition;
A consistently high number of active parts often indicates that merges cannot keep up with incoming data.
Merge Metrics
SELECT *
FROM system.metrics
WHERE metric LIKE '%Merge%';
These metrics help identify:
- Merge backlog
- Resource utilization
- Merge throughput
- Potential bottlenecks
Monitoring these tables regularly enables proactive performance tuning.
Compaction Across MergeTree Engines
Different MergeTree engines apply additional logic during background merges.
MergeTree
Standard background merges combine smaller parts into larger ones.
No automatic row deduplication occurs.
ReplacingMergeTree
During merges, duplicate rows are removed based on the primary key and version column.
Before merge:
| ID | Version |
|---|---|
| 1 | 1 |
| 1 | 2 |
After merge:
| ID | Version |
|---|---|
| 1 | 2 |
Only the newest version remains.
SummingMergeTree
Numeric values sharing the same primary key are automatically summed.
Before merge:
| User | Value |
|---|---|
| A | 10 |
| A | 20 |
After merge:
| User | Value |
|---|---|
| A | 30 |
AggregatingMergeTree
Aggregate states generated during inserts are combined into larger aggregate states during background merges.
This dramatically reduces storage requirements for analytical workloads.
CollapsingMergeTree
Rows with opposite sign values are eliminated during merges, simplifying datasets while reducing storage consumption.
Best Practices
For most production environments, the following recommendations provide excellent results:
- Insert data in large batches whenever possible.
- Avoid frequent
OPTIMIZE TABLE FINALoperations. - Monitor
system.mergesregularly. - Track active parts using
system.parts. - Design partitions carefully.
- Tune merge settings only after identifying real bottlenecks.
- Ensure adequate CPU, memory, and storage bandwidth.
- Monitor merge backlog before it impacts query performance.
Common Mistakes
| Mistake | Impact |
|---|---|
| Tiny inserts | Creates excessive data parts |
| Too many partitions | Higher metadata overhead |
Frequent OPTIMIZE FINAL
|
High CPU and disk usage |
| Ignoring merge backlog | Increasing query latency |
| Oversized partitions | Long-running merge operations |
Avoiding these mistakes helps ClickHouse maintain consistent performance as datasets grow.
Real-World Example
Consider a centralized logging platform ingesting millions of events every minute from thousands of servers.
Initially, the platform inserts small batches continuously throughout the day. As the workload increases, ClickHouse accumulates thousands of active parts per partition. Background merges struggle to keep pace with incoming data, resulting in slower queries and higher storage overhead.
To address this, the engineering team implements several optimizations:
- Batch inserts into larger groups before writing to ClickHouse.
- Partition log data by month using the event timestamp.
- Monitor
system.mergesandsystem.partsto detect merge backlogs. - Tune background merge thread settings based on available CPU and disk resources.
- Reserve
OPTIMIZE TABLE FINALfor maintenance windows after large historical imports.
These changes dramatically reduce the number of active parts, improve compression efficiency, increase ingestion throughput, and restore consistently low query latency—even as daily data volumes continue to grow.
Conclusion
Background compaction is one of the most important mechanisms that enables ClickHouse® to deliver exceptional analytical performance at scale. By continuously merging smaller data parts into larger, optimized ones, ClickHouse reduces storage overhead, improves compression, and minimizes the amount of data each query must scan.
While the default merge scheduler is suitable for most deployments, high-ingestion environments often benefit from additional tuning. Batching inserts, designing effective partitions, monitoring merge activity, and adjusting merge-related settings where necessary can significantly improve overall system efficiency.
Rather than relying on manual optimization, successful ClickHouse deployments focus on creating ingestion patterns and storage layouts that allow background merges to operate efficiently. As your datasets grow from millions to billions of rows, understanding and optimizing compaction becomes a critical part of maintaining predictable performance, scalable ingestion, and fast analytical queries.
Top comments (0)