MergeTree tables settings

Maximum size of data granules in bytes.

To restrict the granule size only by number of rows, set to `0` (not recommended).

## initialization\_retry\_period[​](#initialization_retry_period "Direct link to initialization_retry_period")

Retry period for table initialization, in seconds.

## kill\_delay\_period[​](#kill_delay_period "Direct link to kill_delay_period")

Obsolete setting, does nothing.

## kill\_delay\_period\_random\_add[​](#kill_delay_period_random_add "Direct link to kill_delay_period_random_add")

Obsolete setting, does nothing.

## kill\_threads[​](#kill_threads "Direct link to kill_threads")

Obsolete setting, does nothing.

## lightweight\_mutation\_projection\_mode[​](#lightweight_mutation_projection_mode "Direct link to lightweight_mutation_projection_mode")

By default, lightweight delete `DELETE` does not work for tables with
projections. This is because rows in a projection may be affected by a
`DELETE` operation. So the default value would be `throw`. However, this
option can change the behavior. With the value either `drop` or `rebuild`,
deletes will work with projections. `drop` would delete the projection so it
might be fast in the current query as projection gets deleted but slow in
future queries as no projection attached. `rebuild` would rebuild the
projection which might affect the performance of the current query, but
might speedup for future queries. A good thing is that these options would
only work in the part level, which means projections in the part that don't
get touched would stay intact instead of triggering any action like
drop or rebuild.

Possible values:

- `throw`
- `drop`
- `rebuild`

## load\_existing\_rows\_count\_for\_old\_parts[​](#load_existing_rows_count_for_old_parts "Direct link to load_existing_rows_count_for_old_parts")

If enabled along with [exclude\_deleted\_rows\_for\_part\_size\_in\_merge](#exclude_deleted_rows_for_part_size_in_merge),
deleted rows count for existing data parts will be calculated during table
starting up. Note that it may slow down start up table loading.

Possible values:

- `true`
- `false`

**See Also**

- [exclude\_deleted\_rows\_for\_part\_size\_in\_merge](#exclude_deleted_rows_for_part_size_in_merge) setting

## lock\_acquire\_timeout\_for\_background\_operations[​](#lock_acquire_timeout_for_background_operations "Direct link to lock_acquire_timeout_for_background_operations")

For background operations like merges, mutations etc. How many seconds before
failing to acquire table locks.

## map\_buckets\_coefficient[​](#map_buckets_coefficient "Direct link to map_buckets_coefficient")

The coefficient used in `sqrt` and `linear` [map\_buckets\_strategy](#map_buckets_strategy) to calculate the number of buckets from the average map size.
For `sqrt` strategy: `round(map_buckets_coefficient * sqrt(avg_map_size))`.
For `linear` strategy: `round(map_buckets_coefficient * avg_map_size)`.
Ignored when `map_buckets_strategy` is `constant`.

## map\_buckets\_min\_avg\_size[​](#map_buckets_min_avg_size "Direct link to map_buckets_min_avg_size")

The minimum average map size (number of keys per row) required to apply `with_buckets` serialization.
If the average map size is less than this value, a single bucket is used regardless of other bucket settings.
A value of `0` disables the threshold and always applies the bucketing strategy.
This setting is useful to avoid the overhead of bucketed serialization for small maps where the benefit is negligible.

## map\_buckets\_strategy[​](#map_buckets_strategy "Direct link to map_buckets_strategy")

Controls the strategy for choosing the number of buckets in `with_buckets` `Map` serialization based on the average map size.

Possible values:

- constant — Always use [max\_buckets\_in\_map](#max_buckets_in_map) as the number of buckets, regardless of the average map size.
- sqrt — Use `round(map_buckets_coefficient * sqrt(avg_map_size))` as the number of buckets, clamped to `[1, max_buckets_in_map]`.
- linear — Use `round(map_buckets_coefficient * avg_map_size)` as the number of buckets, clamped to `[1, max_buckets_in_map]`.

## map\_serialization\_version[​](#map_serialization_version "Direct link to map_serialization_version")

Controls the serialization method used for `Map` columns.

Possible values:

- basic — Use the standard serialization for `Map`.
- with\_buckets — Split keys into buckets during serialization. Using buckets improves reading individual keys from the Map.

The number of buckets in `with_buckets` serialization is determined by [max\_buckets\_in\_map](#max_buckets_in_map) and [map\_buckets\_strategy](#map_buckets_strategy).

## map\_serialization\_version\_for\_zero\_level\_parts[​](#map_serialization_version_for_zero_level_parts "Direct link to map_serialization_version_for_zero_level_parts")

This setting allows to specify a different serialization version of
`Map` columns for zero level parts that are created during inserts.
It can be useful to keep `basic` serialization for zero level parts to avoid
performance degradation during inserts, while using `with_buckets` for merged parts.

## marks\_compress\_block\_size[​](#marks_compress_block_size "Direct link to marks_compress_block_size")

Mark compress block size, the actual size of the block to compress.

## marks\_compression\_codec[​](#marks_compression_codec "Direct link to marks_compression_codec")

Compression encoding used by marks, marks are small enough and cached, so
the default compression is ZSTD(3).

## materialize\_skip\_indexes\_on\_merge[​](#materialize_skip_indexes_on_merge "Direct link to materialize_skip_indexes_on_merge")

When enabled, merges build and store skip indices for new parts.
Otherwise they can be created/stored by explicit [MATERIALIZE INDEX](/docs/sql-reference/statements/alter/skipping-index#materialize-index)
or [during INSERTs](/docs/operations/settings/settings#materialize_skip_indexes_on_insert).

See also [exclude\_materialize\_skip\_indexes\_on\_merge](#exclude_materialize_skip_indexes_on_merge) for more fine-grained control.

## materialize\_statistics\_on\_merge[​](#materialize_statistics_on_merge "Direct link to materialize_statistics_on_merge")

When enabled, merges will build and store statistics for new parts.
Otherwise they can be created/stored by explicit [MATERIALIZE STATISTICS](/docs/sql-reference/statements/alter/statistics)
or [during INSERTs](/docs/operations/settings/settings#materialize_statistics_on_insert)

## materialize\_ttl\_recalculate\_only[​](#materialize_ttl_recalculate_only "Direct link to materialize_ttl_recalculate_only")

Only recalculate ttl info when MATERIALIZE TTL

## max\_avg\_part\_size\_for\_too\_many\_parts[​](#max_avg_part_size_for_too_many_parts "Direct link to max_avg_part_size_for_too_many_parts")

The 'too many parts' check according to 'parts\_to\_delay\_insert' and
'parts\_to\_throw\_insert' will be active only if the average part size (in the
relevant partition) is not larger than the specified threshold. If it is
larger than the specified threshold, the INSERTs will be neither delayed or
rejected. This allows to have hundreds of terabytes in a single table on a
single server if the parts are successfully merged to larger parts. This
does not affect the thresholds on inactive parts or total parts.

## max\_buckets\_in\_map[​](#max_buckets_in_map "Direct link to max_buckets_in_map")

The maximum number of buckets for `Map` serialization. Works with `with_buckets` `Map` serialization.
The actual number of buckets is determined by [map\_buckets\_strategy](#map_buckets_strategy).
The maximum allowed value is 256.

## max\_bytes\_to\_merge\_at\_max\_space\_in\_pool[​](#max_bytes_to_merge_at_max_space_in_pool "Direct link to max_bytes_to_merge_at_max_space_in_pool")

The maximum total parts size (in bytes) to be merged into one part, if there
are enough resources available. Corresponds roughly to the maximum possible
part size created by an automatic background merge. (0 means merges will be disabled)

Possible values:

- Any non-negative integer.