Day 69 - Anatomy of a ClickHouse® Vertical Merge

Anatomy of a ClickHouse® Vertical Merge

Introduction

ClickHouse is designed to process analytical workloads at exceptional speed. One of the key reasons behind this performance is its background merge mechanism, which continuously combines smaller data parts into larger ones. These merges improve query performance, reduce the number of files on disk, and optimize data storage without interrupting ongoing queries.

For most tables, ClickHouse performs a Horizontal Merge, where all columns are processed together. However, when dealing with wide tables containing hundreds of columns, this approach can consume a significant amount of memory. To address this challenge, ClickHouse introduces Vertical Merge, an optimization that processes columns in multiple stages instead of all at once.

In this article, we'll explore how Vertical Merge works, why it was introduced, how ClickHouse decides when to use it, and how it helps reduce memory consumption during background merge operations.

Prerequisites

Before exploring Vertical Merge, it's helpful to understand a few core ClickHouse concepts:

• MergeTree tables
• Data parts
• ORDER BY (sorting key)
• Background merge operations

---

Understanding Background Merges

ClickHouse stores data in immutable data parts. A data part is called immutable because once it is written to disk, ClickHouse never modifies it directly. Instead, when optimization is needed, ClickHouse creates a new merged part and removes the old ones after the merge completes successfully.

Since every INSERT operation creates a new data part, a busy table can quickly accumulate hundreds or even thousands of small parts. Having too many parts increases storage metadata, makes background maintenance more expensive, and can negatively impact query performance.

To keep storage efficient and maintain fast query execution, ClickHouse continuously performs background merges that combine smaller parts into larger ones.

For example, if you insert data five times into the same MergeTree table, ClickHouse creates five separate data parts. These parts are stored independently until the background merge process combines them into a larger part.

INSERTS
  │  Part A
  │  Part B
  │  Part C
  ▼
Background Merge
  │
  ▼
Merged Part

During a merge, ClickHouse:

1. Reads multiple source parts.
2. Combines and sorts rows according to the table's sorting key.
3. Applies engine-specific processing.
4. Writes a new merged part.
5. Removes the old parts after the merge completes successfully.

---

Horizontal Merge vs. Vertical Merge

ClickHouse supports two approaches for merging data parts:

Feature	Horizontal Merge	Vertical Merge
Column Processing	Processes all columns together	Processes key columns first, then remaining columns separately
Memory Usage	Higher memory usage	Lower memory usage
Best For	Narrow tables	Wide tables
Workflow Complexity	Simpler merge process	Multi-stage merge process
Scalability	Faster for smaller tables	Better scalability for large tables

Horizontal Merge remains the default merge strategy in ClickHouse because it performs efficiently for most workloads. Vertical Merge is used only when ClickHouse determines that processing all columns together would require excessive memory, making a staged merge more efficient.

---

Why Was Vertical Merge Introduced?

Horizontal Merge works efficiently for tables containing a relatively small number of columns. However, as analytical datasets grow wider—with hundreds of columns and millions or billions of rows—the amount of data that must be processed during a merge also increases. Processing every column simultaneously can consume a considerable amount of memory.

Imagine an analytics table containing:

• 250 columns
• Millions of rows
• Several large String, Array, or JSON columns
• Continuous data ingestion

A traditional merge must read data from every column at the same time. As the number of columns increases, memory consumption grows significantly.

Instead of loading every column simultaneously, Vertical Merge divides the work into smaller stages, allowing ClickHouse to process only a small portion of the data at a time. This dramatically reduces peak memory usage while still producing the same final merged part.

---

How Vertical Merge Works (Step-by-Step Example)

Before understanding the internal workflow, let's look at a simple example. We'll use two small data parts to demonstrate how ClickHouse performs a Vertical Merge. The same process is used internally for much larger datasets.

Let's assume we have two data parts that need to be merged:

Part 1

id	city	sales
1	Chennai	500
3	Delhi	700

Part 2

id	city	sales
2	Mumbai	600
4	Pune	900

Assume the table is created with:

ORDER BY id

This means the final merged data must be ordered by the id column.

Step 1: Read Only the Sorting Key

Every MergeTree table defines a sorting key using the ORDER BY clause. Instead of loading every column (id, city, and sales) into memory at once, ClickHouse first reads only the sorting key columns to determine the correct row order for the merged part.

• Part 1 Key: id (1, 3)
• Part 2 Key: id (2, 4)

At this stage, ClickHouse completely ignores the city and sales columns.

Step 2: Determine the Correct Row Order

Using the id values, ClickHouse determines how the rows should appear in the final merged part. The correct order is:

Final Position	id
1	1
2	2
3	3
4	4

Internally, ClickHouse remembers where every row belongs. This information is called a row mapping.

Once the row mapping has been created, ClickHouse no longer needs to recalculate the row order while merging the remaining columns, making the process both efficient and memory-friendly. You can think of it as a set of instructions saying:

• Row from Part 1 $\rightarrow$ Position 1
• Row from Part 2 $\rightarrow$ Position 2
• Row from Part 1 $\rightarrow$ Position 3
• Row from Part 2 $\rightarrow$ Position 4

This mapping is created only once and is reused while processing every remaining column. It exists only for the duration of the merge operation and is discarded after the final merged part has been written.

Step 3: Merge the Remaining Columns

Now ClickHouse starts processing the remaining columns one at a time.

1. Merge the city column

Using the row mapping, ClickHouse reads the city values from both parts and writes them in the correct position:

• Part 1: Chennai, Delhi
• Part 2: Mumbai, Pune

Resulting Output:

city
----
Chennai
Mumbai
Delhi
Pune

2. Merge the sales column

Next, ClickHouse clears the previous column from memory and processes the sales column using the exact same row mapping:

• Part 1: 500, 700
• Part 2: 600, 900

Resulting Output:

sales
-----
500
600
700
900

Notice that ClickHouse never loads every column into memory simultaneously. Instead, it processes one column (or a small group of columns) at a time using the previously created row mapping.

Step 4: Build the Final Merged Part

Finally, ClickHouse combines all processed columns into a single, comprehensive merged part on disk:

id	city	sales
1	Chennai	500
2	Mumbai	600
3	Delhi	700
4	Pune	900

The old data parts are then unlinked and removed automatically.

---

Why Does This Save Memory?

Imagine a table with 500 columns:

Horizontal Merge

ClickHouse tries to process all 500 columns together.

[ Memory Buffer ] ──► Contains: id, city, sales, price, quantity, ... (All 500 columns simultaneously)

Result: Requires a massive chunk of RAM.

Vertical Merge

ClickHouse processes columns sequentially, using the mapping as a guide.

[ Memory Buffer ] ──► id ──► (Done)
[ Memory Buffer ] ──► city ──► (Done)
[ Memory Buffer ] ──► sales ──► (Done)
...

At any given moment, only a small portion of the overall row data is in memory, which drastically minimizes peak memory usage.

---

When Does ClickHouse Use Vertical Merge?

ClickHouse automatically determines whether a Vertical Merge is more efficient than a Horizontal Merge. The decision is based on several internal factors, including:

• Number of columns in the table
• Number of rows being merged
• Size of the source data parts
• Estimated memory required for the merge
• MergeTree configuration thresholds (e.g., vertical_merge_algorithm_min_columns, vertical_merge_algorithm_min_rows)

If ClickHouse estimates that a Horizontal Merge would consume excessive memory, it automatically switches to a Vertical Merge. In most production environments, users do not manually choose between Horizontal Merge and Vertical Merge; ClickHouse handles this choice completely under the hood.

⚠️ Note: Vertical Merge optimizes the merge process itself to prevent Out-Of-Memory (OOM) errors. It does not directly improve SELECT query performance. However, by reducing memory pressure during background operations and keeping parts organized, it contributes to overall system stability.

---

Advantages & Limitations

Advantages

• Reduces Peak Memory: Slashing RAM footprint during heavy background activity.
• Improves Wide Table Scalability: Perfect for massive schemas with hundreds of attributes.
• Handles Complex Types Efficiently: Prevents large String, Array, and JSON columns from hogging memory.
• Maintains Server Stability: Minimizes the resource impact of background operations on running production queries.

Limitations

• Processing Overhead: Additional management stages (like generating and reading row mappings) introduce a minor amount of compute overhead.
• Inefficient for Narrow Tables: Small schemas or narrow tables generally perform better with a fast, direct Horizontal Merge.
• Workload Dependent: Overall gains rely heavily on proper table design and data characteristics.

---

Monitoring Merge Activity

ClickHouse provides several system tables to help administrators track and audit merge strategies:

1. View Active Merges

To monitor what the background merge pool is working on right now:

SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts
FROM system.merges;

2. View Active Parts

To view how parts are currently organized on disk after being merged:

SELECT
    partition,
    name,
    rows,
    bytes_on_disk
FROM system.parts
WHERE active;

3. View Merge History

To evaluate how frequently ClickHouse utilizes vertical vs. horizontal merge tracks, look into the part log:

SELECT 
    event_time,
    table,
    event_type,
    rows_read,
    memory_usage
FROM system.part_log
WHERE event_type = 'MergeParts'
ORDER BY event_time DESC;

---

Real-World Use Cases

Vertical Merge is the quiet hero behind several high-scale database architectures:

Workload	Why Vertical Merge Helps
IoT Sensor Data	Multi-metric schemas frequently span hundreds of specialized metric columns.
Log Analytics	Massive text fields (String) would otherwise trigger memory spikes if loaded concurrently.
Clickstream Analytics	Event tables tracking granular user interactions scale widely.
Data Warehouses	Denormalized tables containing wide schemas with embedded JSON objects merge cleanly.

---

Key Takeaways

• Every INSERT creates a brand-new, immutable data part.
• Background merges continuously combine small parts into larger, sorted parts to keep query performance fast.
• Horizontal Merge processes all columns at the same time.
• Vertical Merge maps out the row layout using the sorting key columns first, then handles remaining columns piece by piece.
• ClickHouse intelligently and automatically picks the right merge strategy based on your schema size and system configurations.

Conclusion

Vertical Merge is an intelligent optimization within ClickHouse that improves the efficiency of background merge operations for wide tables. By separating the processing of sorting key columns from the remaining columns, ClickHouse significantly reduces memory consumption without affecting the correctness of the final merged data.

Although Vertical Merge works automatically behind the scenes, understanding its workflow helps database administrators and data engineers interpret merge behavior, troubleshoot performance issues, and design schemas that scale efficiently as datasets continue to grow.