ReplacingMergeTree

Computing the sum of answers per year with `FINAL`:

```
SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_no_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │        371480 │
...
│ 2024 │        127765 │
└──────┴───────────────┘

17 rows in set. Elapsed: 2.338 sec. Processed 122.94 million rows, 1.84 GB (52.57 million rows/s., 788.58 MB/s.)
Peak memory usage: 2.09 GiB.
```

Repeating these same steps for a table partitioning by year, and repeating the above query with `do_not_merge_across_partitions_select_final=1`.

```
CREATE TABLE stackoverflow.posts_with_part
(
        `Version` UInt32,
        `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        ...
)
ENGINE = ReplacingMergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

// populate & update omitted

SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_with_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │       387832  │
│ 2009 │       1165506 │
│ 2010 │       1755437 │
...
│ 2023 │       787032  │
│ 2024 │       127765  │
└──────┴───────────────┘

17 rows in set. Elapsed: 0.994 sec. Processed 64.65 million rows, 983.64 MB (65.02 million rows/s., 989.23 MB/s.)
```

As shown, partitioning has significantly improved query performance in this case by allowing the deduplication process to occur at a partition level in parallel.

## Merge behavior considerations[​](#merge-behavior-considerations "Direct link to Merge behavior considerations")

ClickHouse's merge selection mechanism goes beyond simple merging of parts. Below, we examine this behavior in the context of ReplacingMergeTree, including configuration options for enabling more aggressive merging of older data and considerations for larger parts.

### Merge selection logic[​](#merge-selection-logic "Direct link to Merge selection logic")

While merging aims to minimize the number of parts, it also balances this goal against the cost of write amplification. Consequently, some ranges of parts are excluded from merging if they would lead to excessive write amplification, based on internal calculations. This behavior helps prevent unnecessary resource usage and extends the lifespan of storage components.

### Merging behavior on large parts[​](#merging-behavior-on-large-parts "Direct link to Merging behavior on large parts")

The ReplacingMergeTree engine in ClickHouse is optimized for managing duplicate rows by merging data parts, keeping only the latest version of each row based on a specified unique key. However, when a merged part reaches the max\_bytes\_to\_merge\_at\_max\_space\_in\_pool threshold, it will no longer be selected for further merging, even if min\_age\_to\_force\_merge\_seconds is set. As a result, automatic merges can no longer be relied upon to remove duplicates that may accumulate with ongoing data insertion.

To address this, you can invoke `OPTIMIZE FINAL` to manually merge parts and remove duplicates. Unlike automatic merges, `OPTIMIZE FINAL` bypasses the `max_bytes_to_merge_at_max_space_in_pool` threshold, merging parts based solely on available resources, particularly disk space, until a single part remains in each partition. However, this approach can be memory-intensive on large tables and may require repeated execution as new data is added.

For a more sustainable solution that maintains performance, partitioning the table is recommended. This can help prevent data parts from reaching the maximum merge size and reduces the need for ongoing manual optimizations.

### Partitioning and merging across partitions[​](#partitioning-and-merging-across-partitions "Direct link to Partitioning and merging across partitions")

As discussed in Exploiting Partitions with ReplacingMergeTree, we recommend partitioning tables as a best practice. Partitioning isolates data for more efficient merges and avoids merging across partitions, particularly during query execution. This behavior is enhanced in versions from 23.12 onward: if the partition key is a prefix of the sorting key, merging across partitions isn't performed at query time, leading to faster query performance.

### Tuning merges for better query performance[​](#tuning-merges-for-better-query-performance "Direct link to Tuning merges for better query performance")

By default, min\_age\_to\_force\_merge\_seconds and min\_age\_to\_force\_merge\_on\_partition\_only are set to 0 and false, respectively, disabling these features. In this configuration, ClickHouse will apply standard merging behavior without forcing merges based on partition age.

If a value for min\_age\_to\_force\_merge\_seconds is specified, ClickHouse will ignore normal merging heuristics for parts older than the specified period. While this is generally only effective if the goal is to minimize the total number of parts, it can improve query performance in ReplacingMergeTree by reducing the number of parts needing merging at query time.

This behavior can be further tuned by setting min\_age\_to\_force\_merge\_on\_partition\_only=true, requiring all parts in the partition to be older than min\_age\_to\_force\_merge\_seconds for aggressive merging. This configuration allows older partitions to merge down to a single part over time, which consolidates data and maintains query performance.

### Recommended settings[​](#recommended-settings "Direct link to Recommended settings")

Note

Tuning merge behavior is an advanced operation. We recommend consulting with ClickHouse support before enabling these settings in production workloads.

In most cases, setting min\_age\_to\_force\_merge\_seconds to a low value—significantly less than the partition period—is preferred. This minimizes the number of parts and prevents unnecessary merging at query time with the FINAL operator.

For example, consider a monthly partition that has already been merged into a single part. If a small, stray insert creates a new part within this partition, query performance can suffer because ClickHouse must read multiple parts until the merge completes. Setting min\_age\_to\_force\_merge\_seconds can ensure these parts are merged aggressively, preventing a degradation in query performance.

- [Automatic upserts of inserted rows](#automatic-upserts-of-inserted-rows)- [Choosing a primary/deduplication key](#choosing-a-primarydeduplication-key)- [Querying ReplacingMergeTree](#querying-replacingmergetree)- [FINAL performance](#final-performance)- [Exploiting partitions with ReplacingMergeTree](#exploiting-partitions-with-replacingmergetree)- [Merge behavior considerations](#merge-behavior-considerations)
            - [Merge selection logic](#merge-selection-logic)- [Merging behavior on large parts](#merging-behavior-on-large-parts)- [Partitioning and merging across partitions](#partitioning-and-merging-across-partitions)- [Tuning merges for better query performance](#tuning-merges-for-better-query-performance)- [Recommended settings](#recommended-settings)

Was this page helpful?