ClickHouse Decides Which Parts to Merge: A Cost Model for Write Amplification

TL;DR: ClickHouse picks merge candidates with a tiny, explicit cost model: score each contiguous range as sum_size / (count - 1.9), pick the cheapest admissible one, and only ever look at the newest 1000 parts so backlogs cannot snowball. The knobs that control it are merge_selector_base and merge_selector_window_size.

A MergeTree table is a set of immutable parts. Every INSERT adds one. Background merges take contiguous runs of parts and rewrite them as one bigger part, which cuts the number of parts a SELECT has to open — and costs a full rewrite of the data involved.

That trade-off has no free lunch, and the two directions pull against each other:

Merge eagerly and the part count stays low, but you rewrite the same bytes over and over. A part merged into a bigger part, which is merged again, is written twice.
Merge lazily and write amplification is low, but the part count climbs, and every query has to open every part.

ClickHouse’s answer is a small, explicit cost model. And — unusually — the model is documented in the source, together with its own limitations. Which means you can read the reasoning, not just guess at it.

Let’s take the score function, the “base of the logarithm” knob, the sliding window that stops merge backlogs from snowballing, and the background scheduler’s exponential backoff.

The design note

The most valuable thing about ClickHouse’s merge selector is that the authors wrote down what they were trying to do — and where they thought they were wrong. The header of SimpleMergeSelector.h is a design document:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.h:6
/**
We have a set of data parts that is dynamically changing - new data parts are added and there is background merging process.
Background merging process periodically selects continuous range of data parts to merge.

It tries to optimize the following metrics:
1. Write amplification: total amount of data written on disk (source data + merges) relative to the amount of source data.
It can be also considered as the total amount of work for merges.
2. The number of data parts in the set at the random moment of time (average + quantiles).

Also taking the following considerations:
1. Older data parts should be merged less frequently than newer data parts.
2. Larger data parts should be merged less frequently than smaller data parts.
3. If no new parts arrive, we should continue to merge existing data parts to eventually optimize the table.
4. Never allow too many parts, because it will slow down SELECT queries significantly.
5. Multiple background merges can run concurrently but not too many.

It is not possible to optimize both metrics, because they contradict to each other.
To lower the number of parts we can merge eagerly but write amplification will increase.
Then we need some balance between optimization of these two metrics.

But some optimizations may improve both metrics.

For example, we can look at the "merge tree" - the tree of data parts that were merged.
If the tree is perfectly balanced then its depth is proportional to the log(data size),
the total amount of work is proportional to data_size * log(data_size)
and the write amplification is proportional to log(data_size).
If it's not balanced (e.g. every new data part is always merged with existing data parts),
its depth is proportional to the data size, total amount of work is proportional to data_size^2.
*/

That last paragraph is the key insight of the whole design, so read it twice.

The merges form a tree: leaves are inserted parts, internal nodes are merge results. If the tree is balanced, its depth is log_base(total_size) and total write amplification is proportional to that depth. If the tree degenerates into a chain — every new part merged immediately into the current biggest part — the depth is proportional to the data size, and total write amplification is O(data_size). Every byte gets rewritten once per level.

So the selector’s real job is keeping the merge tree balanced. And the knob that controls balance is the arity of the tree: how many parts get merged at once.

The base of the logarithm

That arity is called base, and the header explains how it generalizes to parts of unequal size:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.h:35
We can also control the "base of the logarithm" - you can look it as the number of data parts
that are merged at once (the tree "arity"). But as the data parts are of different size, we should generalize it:
calculate the ratio between total size of merged parts to the size of the largest part participated in merge.
For example, if we merge 4 parts of size 5, 3, 1, 1 - then "base" will be 2 (10 / 5).

Base of the logarithm (simply called `base` in `SimpleMergeSelector`) is the main knob to control the write amplification.
The more it is, the less is write amplification but we will have more data parts on average.

To fit all the considerations, we also adjust `base` depending on total parts count,
parts size and parts age, with linear interpolation (then `base` is not a constant but a function of multiple variables,
looking like a section of hyperplane).

So the base of a candidate merge is sum_size / max_size: total size of the range divided by the size of its largest part. Merge four parts of sizes 5, 3, 1, 1 and you get a base of 10/5 = 2.

Now merge a huge part with a tiny one. Base is barely above 1 — and that is bad. That is the degenerate chain, the exact shape the selector exists to avoid.

base is exposed as a MergeTree setting, default 5:

cpp
// src/Storages/MergeTree/MergeTreeSettings.cpp:1090
    DECLARE(Float, merge_selector_base, 5.0, R"(Affects write amplification of
    assigned merges (expert level setting, don't change if you don't understand
    what it is doing). Works for Simple and StochasticSimple merge selectors
)", 0) \

The admissibility test: is this range allowed at all?

Before any candidate is scored, it has to pass an admissibility test. The test is allow(), and its central quantity is the lowered base — a base adjusted downward depending on how stale the parts are, how many parts the partition has, and how big the range is.

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:241
    /// Map size to 0..1 using logarithmic scale
    /// Use log(1 + x) instead of log1p(x) because our sum_size is always integer.
    /// Also log1p seems to be slow and significantly affect performance of merges assignment.
    double size_normalized = mapPiecewiseLinearToUnit(log(1 + sum_size), min_size_to_lower_base_log, max_size_to_lower_base_log);
    /// Calculate boundaries for age
    double min_age_to_lower_base = interpolateLinear(static_cast<double>(settings.min_age_to_lower_base_at_min_size), static_cast<double>(settings.min_age_to_lower_base_at_max_size), size_normalized);
    double max_age_to_lower_base = interpolateLinear(static_cast<double>(settings.max_age_to_lower_base_at_min_size), static_cast<double>(settings.max_age_to_lower_base_at_max_size), size_normalized);
    /// Map age to 0..1
    double age_normalized = mapPiecewiseLinearToUnit(min_age, min_age_to_lower_base, max_age_to_lower_base);
    /// Map partition_size to 0..1
    double num_parts_normalized = mapPiecewiseLinearToUnit(partition_size, static_cast<double>(settings.min_parts_to_lower_base), static_cast<double>(settings.max_parts_to_lower_base));
    /// The ratio should be within [0, 1]
    double combined_ratio = std::min(1.0, age_normalized + num_parts_normalized);

    double lowered_base = interpolateLinear(settings.base, 2.0, combined_ratio);

Three inputs, three effects:

Input	Effect on `lowered_base`
`age_normalized` — how old the youngest part in the range is	Older parts are cheaper to merge (they are not going to be queried as freshly-inserted data), so the base is lowered and the merge happens sooner.
`num_parts_normalized` — how many parts the partition has	A partition with too many parts is a problem for queries, so the base is lowered and merges get more aggressive.
`size_normalized` — how big the range is	Big ranges need to be older before they are merged, because merging them is expensive.

The mapping is deliberately non-linear in size (logarithmic), and the comment explains why log(1+x) rather than log1p(x): log1p is slow enough to matter, and the selector runs on every selection round.

Then the actual test:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:271
    return (sum_size + static_cast<double>(size) * static_cast<double>(settings.size_fixed_cost_to_add)) / (max_size + static_cast<double>(settings.size_fixed_cost_to_add)) >= lowered_base;

Read it as: “is sum_size / max_size at least lowered_base?” — modulo the fixed cost of merging (size_fixed_cost_to_add, default 5 MB) added to both numerator and denominator, which models the fact that merging even two tiny parts costs a fixed amount of work.

Two escape hatches come before it:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:215
    if (settings.min_age_to_force_merge && min_age >= static_cast<double>(settings.min_age_to_force_merge))
        return true;

    if (settings.min_partition_age_to_force_merge && min_partition_age > 0)
        if (min_partition_age >= static_cast<double>(settings.min_partition_age_to_force_merge))
            return true;

If the parts are old enough — or the whole partition is old enough — the range is merged no matter what the base says. This is the safety valve: the cost model can be wrong, but a part that has been sitting there for a day must eventually be merged.

The score function

Now, given that several ranges are admissible, which one do you pick? This is the score() function, and it is where the “cost model” really lives:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:122
    static double score(double count, double sum_size, double sum_size_fixed_cost)
    {
        /** Consider we have two alternative ranges of data parts to merge.
          * Assume time to merge a range is proportional to sum size of its parts.
          *
          * Cost of query execution is proportional to total number of data parts in a moment of time.
          * Let define our target: to minimize average (in time) total number of data parts.
          *
          * Let calculate integral of total number of parts, if we are going to do merge of one or another range.
          * It must be lower, and thus we decide, what range is better to merge.
          *
          * The integral is lower iff the following formula is lower:
          *
          *  sum_size / (count - 1)
          *
          * But we have some tunes to prefer longer ranges.
          */
        return (sum_size + sum_size_fixed_cost * count) / (count - 1.9);
    }

That comment is a genuine, if informal, calculus argument. Merge a range of count parts and the part count drops by count - 1 (you replace count parts with one). The merge itself takes time proportional to sum_size. So the “average number of parts over time” is sum_size / (count - 1) — and lower is better. The range with the smallest score is the one to merge.

Now the - 1.9. With a plain - 1 the denominator vanishes for a two-part merge, giving an infinite score. The authors replaced - 1 with - 1.9, which softens the singularity: merging two parts is penalized less than the pure model would suggest, and merging many parts is penalized more. The comment’s own summary is “we have some tunes to prefer longer ranges”.

size_fixed_cost_to_add (5 MB by default) enters the numerator as sum_size + fixed * count — every part in the range carries the fixed cost of being merged, so a range of ten tiny parts is far more expensive than its byte sum suggests.

The candidate enumeration walks every contiguous sub-range of the partition and scores each one:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:108
        double current_score = score(static_cast<double>(end - begin), static_cast<double>(sum_size), static_cast<double>(settings.size_fixed_cost_to_add));

        if (settings.enable_heuristic_to_align_parts
            && static_cast<double>(size_prev_at_left) > static_cast<double>(sum_size) * settings.heuristic_to_align_parts_min_ratio_of_sum_size_to_prev_part)
        {
            double difference = std::abs(log2(static_cast<double>(sum_size) / static_cast<double>(size_prev_at_left)));
            if (difference < settings.heuristic_to_align_parts_max_absolute_difference_in_powers_of_two)
                current_score *= interpolateLinear(settings.heuristic_to_align_parts_max_score_adjustment, 1,
                    difference / settings.heuristic_to_align_parts_max_absolute_difference_in_powers_of_two);
        }

The “align parts” heuristic prefers ranges whose total size is roughly a power of two times the size of the part to their left. Why? Because a balanced merge tree wants each level to double the size. If the part on the left is 100 MB and the candidate range is 100 MB, the range “fits” the tree; at 30 MB or 300 MB the resulting tree is lopsided and the next merge has to rewrite more. The score is scaled down by up to 25% (heuristic_to_align_parts_max_score_adjustment = 0.75) when the sizes sit close to a power of two apart.

The right-tail heuristic

One more adjustment, applied before scoring, and it is the most “heuristic” of them all:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:51
        const bool should_remove_small_parts_at_right = settings.enable_heuristic_to_remove_small_parts_at_right
            && (!settings.merge_selector_min_age_to_disable_right_tail_heuristic
                || min_age < settings.merge_selector_min_age_to_disable_right_tail_heuristic);
        if (should_remove_small_parts_at_right)
        {
            size_t size_delta = 0;
            size_t rows_delta = 0;
            while (end >= begin + 3 && static_cast<double>((end - 1)->size) < settings.heuristic_to_remove_small_parts_at_right_max_ratio * static_cast<double>(sum_size))
            {
                size_delta += (end - 1)->size;
                rows_delta += (end - 1)->rows;
                --end;
            }

            sum_size -= size_delta;
            sum_rows -= rows_delta;
        }

The idea: a range that ends with a couple of very small parts is a bad merge. Those small parts are probably new parts that just arrived, and folding them into a large merge means they sit in the merge queue for a long time. Worse, it means freshly-arrived data is not queryable quickly. So the heuristic trims the right tail of the range — the small parts at the newest end — and scores what remains.

The default ratio is 1%: a part at the right end smaller than 1% of the range’s total gets trimmed, as long as at least three parts remain. And there is an age-based escape hatch (merge_selector_min_age_to_disable_right_tail_heuristic): once the range is old enough the tail is kept, because at that point staleness is worse than freshness.

The sliding window: anti-snowball protection

The single most valuable piece of engineering in the selector is this:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:307
    /// If the parts in the parts vector are sorted by block number,
    /// it may not be ideal to only select parts for merging from the first N ones.
    /// This is because if there are more than N parts in the partition,
    /// we will not be able to assign a merge for newly created parts.
    /// As a result, the total number of parts within the partition could
    /// grow uncontrollably, similar to a snowball effect.
    /// To address this we will try to assign a merge taking into consideration
    /// only last N parts.
    const size_t parts_threshold = settings.window_size;
    size_t begin = 0;
    if (parts_count >= parts_threshold)
    {
        if (settings.enable_stochastic_sliding)
            begin = calculateRangeWithStochasticSliding(parts_count, parts_threshold);
        else
            begin = parts_count - parts_threshold;
    }

window_size defaults to 1000. The comment describes the failure mode it prevents, and it is a real one:

If the parts in the parts vector are sorted by block number, it may not be ideal to only select parts for merging from the first N ones. This is because if there are more than N parts in the partition, we will not be able to assign a merge for newly created parts. As a result, the total number of parts within the partition could grow uncontrollably, similar to a snowball effect.

Picture it: if the selector always preferred the oldest parts, then in a partition with 10 000 parts it would forever be merging parts 1..100 — ancient, useless candidates — while parts 9000..10000, the ones that arrived in the last minute, never get merged. The part count never goes down. The merge queue is permanently busy with work that does not help.

By only ever considering the last N parts, every merge consumes recent parts, so recent parts get compacted and the partition’s part count actually falls. One line of code, and it kills a self-reinforcing failure mode.

The stochastic variant (StochasticSimpleMergeSelector) randomizes the window position, so different replicas do not all merge the same parts:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:275
size_t calculateRangeWithStochasticSliding(size_t parts_count, size_t parts_threshold)
{
    auto mean = static_cast<double>(parts_count);
    std::normal_distribution<double> distribution{mean, mean / 4};

    size_t right_boundary = static_cast<size_t>(distribution(thread_local_rng));
    if (right_boundary > parts_count)
        right_boundary = 2 * parts_count - right_boundary;

    right_boundary = std::max(right_boundary, parts_threshold);

    return right_boundary - parts_threshold;
}

A normal distribution with mean parts_count and standard deviation parts_count / 4, folded back into range by mirroring at parts_count. And note the std::max(right_boundary, parts_threshold) at the end: the window never shrinks below window_size. The anti-snowball guarantee is never traded away for randomization.

The blurry base

StochasticSimpleMergeSelector adds one more trick: instead of a sharp threshold on the base, it uses a probability distribution around the lowered base.

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:256
    if (settings.use_blurry_base)
    {
        double partition_fill_factor = std::max(0., 1 - partition_size / static_cast<double>(settings.parts_to_throw_insert));
        /// Scale factor controls when (relativelty to the number of parts in partition)
        /// do we activate our special algorithm.
        /// With standard parameters the logic kicks in starting from 80% empty factor.
        /// The division by 2 is due to the fact that for normal distribution nearly 95.4%
        /// of all observations fall within two standard deviations.
        double scaling_factor = std::pow(partition_fill_factor, settings.blurry_base_scale_factor) / 2;
        /// The base lower than 1 doesn't make sense, so we try to avoid it.
        std::normal_distribution<double> distribution{lowered_base, (lowered_base - 1) * scaling_factor};
        /// The threshold should be strictly bigger than 1, because we don't allow to merge the part with itself.
        lowered_base = std::min(distribution(thread_local_rng), std::max(1.01, lowered_base));
    }

Instead of “merge if base ≥ 5.0”, it says “merge if base ≥ a randomly-drawn threshold around 5.0”. A merge whose base is 4.9 might happen this round and not the next. The result: the distribution of merges is smoother, no sharp cliff at a particular base value, and — critically — the merge decisions of different replicas do not line up.

The width of the distribution grows with the emptiness of the partition: partition_fill_factor is 0 when the partition is full (parts_to_throw_insert parts) and approaches 1 when it is empty. blurry_base_scale_factor (default 42) makes the effect very nonlinear, so the blur only kicks in when the partition is nearly empty — exactly when you want merges to be infrequent and gentle.

The final std::min(distribution(...), std::max(1.01, lowered_base)) is a careful clamp: the drawn threshold can be lower than the base, making the merge more eager, but never lower than 1.01, because a base ≤ 1 would mean merging a part with itself.

Picking the winner

The scored candidates go into a max-heap, and the best admissible, non-overlapping one wins:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.cpp:142
    std::optional<PartsRange> buildMergeRange(const MergeConstraint & constraint)
    {
        constexpr static auto range_compare = [](const ScoredRange & lhs, const ScoredRange & rhs)
        {
            /// If the ranges have the same score, use the range that is most likely to have the lower merge level -
            /// the right one, according to the block number sorting.
            return lhs.score > rhs.score || (lhs.score == rhs.score && lhs.range_begin < rhs.range_begin);
        };

Note the tie-break: “the right one”, i.e. the newer parts. That is the opposite of what a naive reading of “oldest first” would suggest, and it is consistent with the sliding window.

The disjoint_set.addRangeIfPossible call in the pop loop enforces that two concurrent merges never claim overlapping ranges of parts. The disjoint set is implemented as a std::set of range boundaries per independent range, with an upper_bound lookup:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/DisjointPartsRangesSet.cpp:113
bool DisjointPartsRangesSet::addRangeIfPossible(RangesIterator range_it, PartsIterator range_begin, PartsIterator range_end)
{
    chassert(sanityCheck(uncovered_ranges, range_it, range_begin, range_end));

    auto & sorted_ranges = disjoint[range_it.base()];
    PartsRangeBoundaries range_boundaries{std::move(range_begin), std::move(range_end)};

    if (!isDisjoint(range_boundaries, sorted_ranges))
        return false;

    sorted_ranges.insert(std::move(range_boundaries));
    return true;
}

From parts to a merge: the whole pipeline

The selector is one step of a longer pipeline. The entry point is MergeTreeDataMergerMutator::selectPartsToMerge:

cpp
// src/Storages/MergeTree/MergeTreeDataMergerMutator.cpp:328
std::expected<MergeSelectorChoices, SelectMergeFailure> MergeTreeDataMergerMutator::selectPartsToMerge(
    const PartsCollectorPtr & parts_collector,
    const MergePredicatePtr & merge_predicate,
    const MergeSelectorApplier & selector,
    const std::optional<PartitionIdsHint> & partitions_hint)
{
    const auto context = data.getContext();
    const auto settings = data.getSettings();
    const auto metadata_snapshot = data.getInMemoryMetadataPtr(context, false);
    const auto storage_policy = data.getStoragePolicy();
    const time_t current_time = std::time(nullptr);
    const bool can_use_ttl_merges = !ttl_merges_blocker.isCancelled();
    LogSeriesLimiter series_log(log, 1, /*interval_s_=*/60 * 30);

    auto collected = collectAllPossibleRanges(parts_collector, metadata_snapshot, storage_policy, current_time, partitions_hint, series_log);

Three phases:

collect — find the ranges of parts that could be merged (same partition, compatible schema, not already covered by a running merge);
split by merge predicate — cut those ranges wherever a merge is forbidden (a part that overlaps a running merge, a part with a different schema version, and so on);
choose — run the selector’s cost model over what is left.

The LogSeriesLimiter is a nice touch: the trace log of this function is rate-limited to one message per 30 minutes, because the function runs on every selection round and can produce a lot of output.

The background scheduler’s backoff

Selection is cheap; the merge is not. The background scheduler that runs the selection loop uses an exponential backoff when there is nothing to do:

cpp
// src/Storages/MergeTree/BackgroundJobsAssignee.cpp:62
void BackgroundJobsAssignee::postpone()
{
    std::lock_guard lock(holder_mutex);

    if (!holder)
        return;

    no_work_done_count += 1;
    double random_addition = std::uniform_real_distribution<double>(0, sleep_settings.task_sleep_seconds_when_no_work_random_part)(rng);

    size_t next_time_to_execute = static_cast<size_t>(
        1000 * (data.getBiasBackoffSeconds() + std::min(
            sleep_settings.task_sleep_seconds_when_no_work_max,
            sleep_settings.thread_sleep_seconds_if_nothing_to_do * std::pow(sleep_settings.task_sleep_seconds_when_no_work_multiplier, no_work_done_count))
        + random_addition));

    holder->scheduleAfter(next_time_to_execute, false);
}

with the settings:

cpp
// src/Storages/MergeTree/BackgroundJobsAssignee.h:18
struct BackgroundTaskSchedulingSettings
{
    double thread_sleep_seconds_random_part = 1.0;
    double thread_sleep_seconds_if_nothing_to_do = 0.1;
    double task_sleep_seconds_when_no_work_max = 600;
    /// For exponential backoff.
    double task_sleep_seconds_when_no_work_multiplier = 1.1;

    double task_sleep_seconds_when_no_work_random_part = 1.0;

So: start at 0.1 s, multiply by 1.1 per idle round, cap at 600 s, plus up to 1 s of random jitter. An idle table’s selection loop slows to one attempt every ten minutes after about 130 idle rounds, and the jitter stops a fleet of servers from synchronizing their wake-ups.

And when work does appear, the backoff is unwound geometrically rather than reset:

cpp
// src/Storages/MergeTree/BackgroundJobsAssignee.cpp:48
void BackgroundJobsAssignee::trigger()
{
    std::lock_guard lock(holder_mutex);

    if (!holder)
        return;

    /// Do not reset backoff factor if some task has appeared,
    /// but decrease it exponentially on every new task.
    no_work_done_count /= 2;
    /// We have background jobs, schedule task as soon as possible
    holder->schedule();
}

no_work_done_count /= 2 rather than = 0. Halving means that after one new task the sleep time is back to roughly its previous value, and it takes several rounds of activity to fully unwind. A table that receives one part per minute therefore does not flip between “poll every 0.1 s” and “poll every 600 s” — it settles at a rate proportional to the arrival rate.

Here’s the thing: that is genuinely nice control theory hiding inside what looks like three lines of code.

The whole picture

text
  parts in a partition (by block number)
  ┌────┬────┬────┬────┬────┬──────────────┬────┬────┐
  │ p1 │ p2 │ p3 │ p4 │ p5 │  ...         │pn-1│ pn │
  └────┴────┴────┴────┴────┴──────────────┴────┴────┘
   ↑                                          ↑
   └──────── sliding window (last N=1000) ─────┘
                  (only these are eligible)

  for each contiguous sub-range of the window:
      base = sum_size / max_size
      if base < lowered_base(age, partition_size, range_size):
          skip
      score = (sum_size + 5MB * count) / (count - 1.9)
      trim small parts off the right tail
      adjust score for power-of-two alignment with the part on the left

  pick the lowest-scoring admissible range that does not overlap
  an already-assigned merge

What this buys you, and what it costs

It buys you a merge tree that stays balanced, which is what keeps write amplification at O(log n) instead of O(n). The base knob is the lever, and the whole selector is a machine for keeping base near its target.

It buys you a system that self-corrects. When a partition fills up, num_parts_normalized rises, the base is lowered, and merges become more eager — without anybody tuning anything. When a partition sits idle, the opposite happens.

It buys you an anti-snowball guarantee. The sliding window is the difference between a table whose part count stabilizes and one that grows without bound.

It costs you a cost model that is admittedly approximate. The header comment says so:

cpp
// src/Storages/MergeTree/Compaction/MergeSelectors/SimpleMergeSelector.h:57
It's still unclear if this algorithm is good or optimal at all. It's unclear if this algorithm is using the optimal coefficients.

and it describes how the coefficients were tuned (an insert/merge simulator, replayed against production system.part_log data), and how the research found slightly better coefficients that were not used because the test data’s representativeness was in question.

It costs you a - 1.9. The score function’s singularity is papered over with a constant rather than derived. It is a fudge factor, honestly labelled as one.

It costs you a lot of tuning surface. merge_selector_base, merge_selector_window_size, parts_to_throw_insert, max_parts_to_merge_at_once, min_parts_to_merge_at_once, merge_selector_small_parts_*, the align-parts heuristic, the right-tail heuristic, the blurry base, the stochastic sliding window. Every one of these is a lever that was pulled for a reason, and the reason is usually written in a comment somewhere in SimpleMergeSelector.h.

Seeing it in action

The merge list shows what was selected and why:

sql
SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) AS size,
    formatReadableSize(memory_usage) AS mem,
    result_part_name
FROM system.merges
ORDER BY elapsed DESC;

And the parts-per-partition histogram is the metric the selector is ultimately optimizing:

sql
SELECT
    database,
    table,
    partition,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table, partition
ORDER BY parts DESC
LIMIT 20;

A partition with hundreds of parts means the selector is not keeping up — usually because the merge pool is saturated, or because the parts are too large to merge under parts_to_throw_insert pressure, or because merge_selector_base is set too high for the workload.

And if you want to watch the cost model work, the trace log has it:

sql
SET log_level = 'trace';
SELECT count() FROM hits WHERE URL LIKE '%very-selective-prefix%';

The MergeTreeDataMergerMutator::selectPartsToMerge trace messages show which ranges were considered and which one was chosen.

Wrapping up

The one thing to remember: the merge tree is a tree, and a balanced tree costs O(log n) to write while a chain costs O(n). merge_selector_base is the knob that decides which one you get.

The part people get wrong: the sliding window is not an optimization, it is a correctness guard. Without it, a busy partition can keep a permanent merge queue full of work that never reduces the part count.

What to go do about it: run the parts-per-partition query against your biggest tables. If a partition is crawling toward parts_to_throw_insert, the merge pool is the bottleneck, not the selector — and no amount of knob twiddling will fix that.