Analyzing Memory Efficiency with Vertical Merges in 26.3

Introduction

Background merge operations are fundamental to ClickHouse. They combine smaller data parts into larger ones, apply TTL rules, remove expired data, and maintain efficient storage for analytical workloads. However, when working with wide tables containing hundreds of columns, these background merges can become memory-intensive.

To address this, ClickHouse introduced Vertical Merge, an optimization that processes columns independently to reduce memory consumption during merge operations. In ClickHouse 26.3, this optimization has been extended to TTL DELETE merges, allowing expired rows to be removed with significantly lower memory usage.

This article explores why this improvement matters, how Vertical Merge works, and how ClickHouse 26.3 makes TTL cleanup more memory efficient.

Why Memory Usage During Merges Matters

Every INSERT in ClickHouse creates a new immutable data part. To prevent thousands of small parts from accumulating, ClickHouse continuously performs background merges that combine these parts into larger ones.

During a merge, ClickHouse doesn't simply combine data. It also performs several storage maintenance tasks, including:

• Merge multiple parts into one
• Apply compression
• Evaluate TTL expressions
• Removing expired rows
• Rewriting column files

For narrow tables, these operations typically require only a small amount of memory. However, analytical workloads often contain hundreds of columns, nested data types, and large string values.

During a horizontal merge, many columns need to be read, decompressed, processed, and rewritten at the same time. As the number of columns increases, more column data must remain in memory simultaneously, causing peak memory usage to grow significantly.

This becomes especially noticeable in production environments that manage large MergeTree tables with frequent background merges and automated TTL cleanup.

Reducing memory usage during merges is therefore just as important as improving query performance, especially for large-scale analytical workloads.

Horizontal Merge vs Vertical Merge

Traditionally, ClickHouse performs merges using a horizontal approach. During a horizontal merge, every column of a row is processed together before writing the merged result.

For tables with many columns, this means a large amount of column data must remain in memory throughout the merge.

Vertical Merge approaches the problem differently.

Instead of processing entire rows, ClickHouse first determines the correct row order and then processes each non-key column independently. Since only a small number of columns are active at any given time, the overall memory footprint is much lower.

Figure 1: Horizontal Merge processes all columns simultaneously, while Vertical Merge processes non-key columns independently, reducing peak memory usage.

TTL DELETE Before ClickHouse 26.3

TTL rules allow ClickHouse to automatically remove expired data.

Consider a table where logs older than 30 days should be deleted automatically.

Example

CREATE TABLE logs
(
    id UInt64,
    event_time DateTime,
    message String
)
ENGINE = MergeTree
ORDER BY id
TTL event_time + INTERVAL 30 DAY DELETE;

When background merges occur, ClickHouse evaluates the TTL expression and removes rows that have expired.

Before ClickHouse 26.3, these TTL DELETE merges primarily relied on the horizontal merge algorithm. While effective, this approach required processing every column together, which could consume a substantial amount of memory for wide tables.

This wasn't usually noticeable for small datasets, but in production systems with hundreds of columns and billions of rows, merge memory became an important resource consideration.

What's New in ClickHouse 26.3?

Vertical Merge was already available for standard background merges. ClickHouse 26.3 extends this optimization to TTL DELETE merges.

Instead of loading all columns simultaneously while evaluating TTL expressions, ClickHouse now performs the merge using the Vertical Merge workflow.

The process can be summarized as follows:

1. Read and merge the primary key columns.
2. Determine which rows satisfy the TTL condition.
3. Build the row mapping for the merged part.
4. Process each remaining column independently.
5. Write only the rows that should remain.

This allows ClickHouse to avoid keeping every column in memory simultaneously, significantly reducing peak memory usage during TTL cleanup.

Because non-key columns are processed one at a time, the merge requires significantly less peak memory than the previous implementation.

Figure 2: Vertical Merge workflow for TTL DELETE in ClickHouse 26.3. The merge reads primary key columns, evaluates the TTL condition, builds a row mapping, processes non-key columns independently, and writes only the remaining rows, significantly reducing peak memory usage.

Before vs After ClickHouse 26.3

Why This Improves Memory Efficiency

The key advantage is that memory usage becomes much less dependent on the total number of columns.

Imagine a table containing 400 columns.

With a horizontal merge, many of these columns may need to be processed together during the merge.

With a vertical merge, ClickHouse processes one column (or a small group of columns) at a time, writes the filtered data, releases the memory, and then continues with the next column.

This dramatically reduces the maximum amount of memory required during the merge process.

Although the total amount of data processed remains the same, the peak memory footprint becomes much smaller.

Practical Example

Consider an event logging table containing billions of records where data older than 90 days must be removed automatically.

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;

As data ages, background merges periodically remove expired records.

In previous versions, deleting expired rows from a table containing hundreds of columns could temporarily require a large amount of memory.

With ClickHouse 26.3, the same TTL cleanup benefits from Vertical Merge processing, allowing expired rows to be removed much more efficiently without increasing peak memory usage.

When Does This Matter?

This improvement provides the greatest benefit in environments where:

• Tables contain hundreds of columns
• Large background merges occur regularly
• TTL DELETE is heavily used
• Memory resources are limited
• Data retention policies continuously remove old records

Smaller tables may not show a noticeable difference, but production-scale analytical workloads can benefit from improved merge stability and lower resource consumption.

Benefits of Vertical Merge for TTL DELETE

The enhancement introduced in ClickHouse 26.3 offers several practical advantages:

• Lower peak memory usage during TTL merges
• Better scalability for wide tables
• More stable background merge operations
• Reduced memory pressure on busy servers
• Improved efficiency when cleaning up expired data
• Better overall resource utilization

When Will You Notice This Improvement?

You are most likely to benefit if:

• Your tables contain hundreds of columns.
• TTL DELETE runs frequently.
• Background merges consume noticeable memory.
• Your ClickHouse deployment manages large analytical workloads.

Smaller tables may not show a significant difference because merge memory usage is already relatively low.

Conclusion

Background merges are essential to maintaining ClickHouse performance, but they can become memory-intensive as tables grow wider and datasets become larger.

By extending the Vertical Merge algorithm to TTL DELETE operations, ClickHouse 26.3 significantly improves the memory efficiency of background cleanup tasks. Instead of processing every column simultaneously, columns are handled independently, reducing peak memory usage while preserving the correctness of TTL processing.

Although this enhancement operates entirely in the background, it can make a meaningful difference for production workloads that rely on large MergeTree tables and automated data retention policies. It is another example of ClickHouse continuing to optimize its storage engine for high-performance analytics at scale.

References

1. ClickHouse Documentation – MergeTree Table Engine
   https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree

2. ClickHouse Documentation – TTL (Time To Live)
   https://clickhouse.com/docs/guides/developer/ttl