MergeTree tables settings

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/merge-tree-settings.md)

System table `system.merge_tree_settings` shows the globally set MergeTree settings.

MergeTree settings can be set in the `merge_tree` section of the server config file, or specified for each `MergeTree` table individually in
the `SETTINGS` clause of the `CREATE TABLE` statement.

Example for customizing setting `max_suspicious_broken_parts`:

Configure the default for all `MergeTree` tables in the server configuration file:

```
<merge_tree>
    <max_suspicious_broken_parts>5</max_suspicious_broken_parts>
</merge_tree>
```

Set for a particular table:

```
CREATE TABLE tab
(
    `A` Int64
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS max_suspicious_broken_parts = 500;
```

Change the settings for a particular table using `ALTER TABLE ... MODIFY SETTING`:

```
ALTER TABLE tab MODIFY SETTING max_suspicious_broken_parts = 100;

-- reset to global default (value from system.merge_tree_settings)
ALTER TABLE tab RESET SETTING max_suspicious_broken_parts;
```

## MergeTree settings[​](#mergetree-settings "Direct link to MergeTree settings")

## adaptive\_write\_buffer\_initial\_size[​](#adaptive_write_buffer_initial_size "Direct link to adaptive_write_buffer_initial_size")

Initial size of an adaptive write buffer

## add\_implicit\_sign\_column\_constraint\_for\_collapsing\_engine[​](#add_implicit_sign_column_constraint_for_collapsing_engine "Direct link to add_implicit_sign_column_constraint_for_collapsing_engine")

If true, adds an implicit constraint for the `sign` column of a CollapsingMergeTree
or VersionedCollapsingMergeTree table to allow only valid values (`1` and `-1`).

## add\_minmax\_index\_for\_numeric\_columns[​](#add_minmax_index_for_numeric_columns "Direct link to add_minmax_index_for_numeric_columns")

When enabled, min-max (skipping) indices are added for all numeric columns
of the table.

## add\_minmax\_index\_for\_string\_columns[​](#add_minmax_index_for_string_columns "Direct link to add_minmax_index_for_string_columns")

When enabled, min-max (skipping) indices are added for all string columns of the table.

## add\_minmax\_index\_for\_temporal\_columns[​](#add_minmax_index_for_temporal_columns "Direct link to add_minmax_index_for_temporal_columns")

When enabled, min-max (skipping) indices are added for all Date, Date32, Time, Time64, DateTime and DateTime64 columns of the table

## allow\_coalescing\_columns\_in\_partition\_or\_order\_key[​](#allow_coalescing_columns_in_partition_or_order_key "Direct link to allow_coalescing_columns_in_partition_or_order_key")

When enabled, allows coalescing columns in a CoalescingMergeTree table to be used in
the partition or sorting key.

## allow\_commit\_order\_projection[​](#allow_commit_order_projection "Direct link to allow_commit_order_projection")

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

Enables commit-order projections that store `_block_number` and `_block_offset` virtual columns, preserving original insertion order through merges.
Requires `enable_block_number_column` and `enable_block_offset_column` to be enabled.

## allow\_experimental\_replacing\_merge\_with\_cleanup[​](#allow_experimental_replacing_merge_with_cleanup "Direct link to allow_experimental_replacing_merge_with_cleanup")

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

Allow experimental CLEANUP merges for ReplacingMergeTree with `is_deleted`
column. When enabled, allows using `OPTIMIZE ... FINAL CLEANUP` to manually
merge all parts in a partition down to a single part and removing any
deleted rows.

Also allows enabling such merges to happen automatically in the background
with settings `min_age_to_force_merge_seconds`,
`min_age_to_force_merge_on_partition_only` and
`enable_replacing_merge_with_cleanup_for_min_age_to_force_merge`.

## allow\_experimental\_reverse\_key[​](#allow_experimental_reverse_key "Direct link to allow_experimental_reverse_key")

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

Enables support for descending sort order in MergeTree sorting keys. This
setting is particularly useful for time series analysis and Top-N queries,
allowing data to be stored in reverse chronological order to optimize query
performance.

With `allow_experimental_reverse_key` enabled, you can define descending sort
orders within the `ORDER BY` clause of a MergeTree table. This enables the
use of more efficient `ReadInOrder` optimizations instead of `ReadInReverseOrder`
for descending queries.

**Example**

```
CREATE TABLE example
(
time DateTime,
key Int32,
value String
) ENGINE = MergeTree
ORDER BY (time DESC, key)  -- Descending order on 'time' field
SETTINGS allow_experimental_reverse_key = 1;

SELECT * FROM example WHERE key = 'xxx' ORDER BY time DESC LIMIT 10;
```

By using `ORDER BY time DESC` in the query, `ReadInOrder` is applied.

**Default Value:** false

## allow\_floating\_point\_partition\_key[​](#allow_floating_point_partition_key "Direct link to allow_floating_point_partition_key")

Enables to allow floating-point number as a partition key.

Possible values:

- `0` — Floating-point partition key not allowed.
- `1` — Floating-point partition key allowed.

## allow\_nullable\_key[​](#allow_nullable_key "Direct link to allow_nullable_key")

Allow Nullable types as primary keys.

## allow\_part\_offset\_column\_in\_projections[​](#allow_part_offset_column_in_projections "Direct link to allow_part_offset_column_in_projections")

Allow usage of '\_part\_offset' column in projections select query.

## allow\_reduce\_blocking\_parts\_task[​](#allow_reduce_blocking_parts_task "Direct link to allow_reduce_blocking_parts_task")

Background task which reduces blocking parts for shared merge tree tables.
Only in ClickHouse Cloud

## allow\_remote\_fs\_zero\_copy\_replication[​](#allow_remote_fs_zero_copy_replication "Direct link to allow_remote_fs_zero_copy_replication")

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

Don't use this setting in production, because it is not ready.

## allow\_summing\_columns\_in\_partition\_or\_order\_key[​](#allow_summing_columns_in_partition_or_order_key "Direct link to allow_summing_columns_in_partition_or_order_key")

When enabled, allows summing columns in a SummingMergeTree table to be used in
the partition or sorting key.

## allow\_suspicious\_indices[​](#allow_suspicious_indices "Direct link to allow_suspicious_indices")

Reject primary/secondary indexes and sorting keys with identical expressions

## allow\_vertical\_merges\_from\_compact\_to\_wide\_parts[​](#allow_vertical_merges_from_compact_to_wide_parts "Direct link to allow_vertical_merges_from_compact_to_wide_parts")

Allows vertical merges from compact to wide parts. This settings must have
the same value on all replicas.

## alter\_column\_secondary\_index\_mode[​](#alter_column_secondary_index_mode "Direct link to alter_column_secondary_index_mode")

Configures whether to allow `ALTER` commands that modify columns covered by secondary indices, and what action to take if
they are allowed. By default, such `ALTER` commands are allowed and the indices are rebuilt.

Possible values:

- `rebuild` (default): Rebuilds any secondary indices affected by the column in the `ALTER` command.
- `throw`: Prevents any `ALTER` of columns covered by **explicit** secondary indices by throwing an exception. Implicit indices are excluded from this restriction and will be rebuilt.
- `drop`: Drop the dependent secondary indices. The new parts won't have the indices, requiring `MATERIALIZE INDEX` to recreate them.
- `compatibility`: Matches the original behaviour: `throw` on `ALTER ... MODIFY COLUMN` and `rebuild` on `ALTER ... UPDATE/DELETE`.
- `ignore`: Intended for expert usage. It will leave the indices in an inconsistent state, allowing incorrect query results.

## always\_fetch\_merged\_part[​](#always_fetch_merged_part "Direct link to always_fetch_merged_part")