Background merge operations are one of the most important components of ClickHouse®. Every insert creates immutable data parts, and the storage engine continuously merges these parts to keep query performance high and storage efficient. These merges also perform critical maintenance tasks such as applying TTL rules, removing expired records, recompressing data, and consolidating files.
For most workloads, this process happens quietly in the background. However, for organizations storing massive analytical datasets with hundreds of columns, background merges can become one of the largest consumers of memory.
ClickHouse® already introduced Vertical Merge, an optimization designed to reduce memory usage during merge operations by processing columns independently instead of entire rows. In ClickHouse 26.3, this optimization has been extended to TTL DELETE merges, significantly lowering memory consumption when expired rows are removed automatically.
This article explains why this enhancement matters, how Vertical Merge works internally, and why it improves the efficiency of large production deployments.
Why Merge Memory Matters
Unlike traditional databases that update data in place, ClickHouse stores data as immutable parts.
Every INSERT creates a new part, and background merge threads continuously combine smaller parts into larger ones.
During these merges, ClickHouse performs several maintenance operations simultaneously:
- Merging multiple parts into one
- Recompressing column files
- Applying TTL expressions
- Removing expired rows
- Rewriting data into optimized storage layouts
For small datasets, merge memory consumption is usually insignificant.
Production analytical systems, however, often store:
- Hundreds of columns
- Large String columns
- Nested structures
- Arrays
- Maps
- JSON data
During a merge, ClickHouse may need to read, decompress, merge, filter, and rewrite large amounts of column data.
As tables become wider, the peak memory required during merges grows substantially.
While query optimization often receives the most attention, reducing merge memory is equally important because merges execute continuously in the background.
Horizontal Merge vs Vertical Merge
Historically, ClickHouse performed merges using a horizontal merge algorithm.
In a horizontal merge:
- Entire rows are processed together.
- All required columns remain active throughout much of the merge.
- Memory usage increases as the number of columns grows.
This approach works well for narrow schemas but becomes increasingly expensive for wide analytical tables.
Vertical Merge takes a different approach.
Instead of processing complete rows, ClickHouse separates the merge into stages.
First, it determines the correct row ordering using the primary key. After that, each remaining column is processed independently.
The workflow looks like this:
- Merge primary key columns.
- Build the merged row mapping.
- Process one non-key column at a time.
- Write the merged output.
- Release memory before processing the next column.
Since only a small subset of columns is active at any moment, peak memory usage becomes dramatically lower.
Diagram 1: Horizontal Merge vs Vertical Merge
Horizontal Merge
Part A Part B
| |
| Read ALL Columns
|________________|
|
Merge Entire Rows
|
Write New Part
Vertical Merge
Part A Part B
| |
| Merge Primary Keys
|_________________|
|
Build Row Mapping
|
Column 1 -> Write
Column 2 -> Write
Column 3 -> Write
...
Column N -> Write
TTL DELETE Before ClickHouse 26.3
One of the most common maintenance tasks in ClickHouse is automatic data retention using TTL.
For example:
CREATE TABLE logs
(
id UInt64,
event_time DateTime,
message String
)
ENGINE = MergeTree
ORDER BY id
TTL event_time + INTERVAL 30 DAY DELETE;
Rows older than 30 days are automatically removed during background merges.
Before ClickHouse 26.3, TTL DELETE operations primarily relied on the horizontal merge algorithm.
Although this approach correctly removed expired records, it also meant that all participating columns needed to be processed together.
For tables with hundreds of columns, peak memory usage during TTL cleanup could become quite large.
This was rarely noticeable for small tables but became increasingly important for enterprise deployments storing billions of rows.
What's New in ClickHouse 26.3?
ClickHouse 26.3 extends Vertical Merge support to TTL DELETE merges.
Instead of evaluating TTL rules while processing every column simultaneously, ClickHouse now performs TTL cleanup using the same memory-efficient vertical workflow already used for standard merges.
The process now works like this:
- Read and merge primary key columns.
- Evaluate the TTL expression.
- Identify rows that should be deleted.
- Build the row mapping.
- Process each remaining column independently.
- Write only the surviving rows.
Because non-key columns are handled individually, ClickHouse no longer needs to keep every column in memory simultaneously.
The result is significantly lower peak memory usage during automatic data cleanup.
Before vs After ClickHouse 26.3
| Feature | Before 26.3 | ClickHouse 26.3 |
|---|---|---|
| TTL DELETE Merge | Horizontal Merge | Vertical Merge |
| Peak Memory Usage | Higher | Lower |
| Wide Table Performance | Memory intensive | More memory efficient |
| Background Cleanup | Higher resource consumption | Reduced memory pressure |
| Scalability | Limited by merge memory | Better scalability |
Why Vertical Merge Uses Less Memory
The biggest improvement is that memory usage becomes far less dependent on the number of columns.
Imagine a table containing 400 columns.
With a horizontal merge, many of those columns may be loaded and processed together.
With Vertical Merge, ClickHouse processes:
- one column,
- writes the filtered data,
- releases memory,
- moves to the next column.
Only a tiny portion of the table is active at any given time.
The total amount of data processed remains identical.
What changes is the maximum memory required at any instant, which can be significantly smaller.
For servers running multiple concurrent merges, this reduction can substantially improve overall system stability.
Practical Example
Suppose an event logging platform stores billions of events and automatically deletes records older than 90 days.
CREATE TABLE events
(
id UInt64,
timestamp DateTime,
user_id UInt64,
event_type String,
payload String
)
ENGINE = MergeTree
ORDER BY id
TTL timestamp + INTERVAL 90 DAY DELETE;
Every day, background merges remove expired records.
On previous versions, cleaning up a very wide table could temporarily consume a large amount of memory because every column participated in the merge simultaneously.
With ClickHouse 26.3, the same cleanup benefits from Vertical Merge processing.
The result is smoother background maintenance with lower peak memory usage and less pressure on the operating system.
Where You'll Notice the Biggest Improvements
This optimization is especially valuable when:
- Tables contain hundreds of columns.
- Large MergeTree tables are merged frequently.
- TTL DELETE rules remove old data continuously.
- Memory resources are limited.
- Multiple background merges execute concurrently.
- Analytical workloads generate large numbers of data parts.
Smaller tables may not show dramatic improvements because merge memory requirements are already relatively low.
Benefits of Vertical Merge for TTL DELETE
Extending Vertical Merge to TTL DELETE operations provides several practical advantages:
- Lower peak memory consumption during background merges
- Better scalability for very wide schemas
- More stable merge execution
- Reduced memory pressure across the server
- Improved efficiency of automatic data retention
- Better utilization of available system resources
- More predictable performance under heavy workloads
Although users may never interact with this feature directly, its impact can be significant for production environments processing terabytes or petabytes of data.
Conclusion
Background merges are essential to how ClickHouse maintains fast analytical performance. As datasets become larger and schemas become wider, however, merge operations can consume substantial amounts of memory.
ClickHouse 26.3 addresses this challenge by extending the Vertical Merge algorithm to TTL DELETE operations. Instead of processing every column simultaneously, the storage engine now handles non-key columns independently, dramatically reducing peak memory requirements while preserving the correctness of TTL processing.
This enhancement operates entirely behind the scenes, requiring no application changes or configuration updates. Yet for production workloads that rely on automated data retention and large MergeTree tables, it can lead to more stable background merges, improved resource utilization, and better overall scalability.
It is another example of how ClickHouse continues refining its storage engine—not only to execute queries faster, but also to make the underlying maintenance operations increasingly efficient for modern analytical workloads.
Top comments (0)