# MergeTree settings (/docs/reference/sql/settings/merge-tree)
MergeTree settings [#mergetree-settings]
This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).
adaptive_write_buffer_initial_size [#adaptive_write_buffer_initial_size]
**Type:** `NonZeroUInt64`\
**Default:** `16384`\
**Tier:** Production\
Initial size of an adaptive write buffer
add_implicit_sign_column_constraint_for_collapsing_engine [#add_implicit_sign_column_constraint_for_collapsing_engine]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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_block_number_column [#add_minmax_index_for_block_number_column]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, an implicit min-max (skipping) index is added for the persistent virtual column `_block_number`. Requires `enable_block_number_column = 1` to take effect. The index is built only during merges, not during inserts: at insert time the block number is provisional and would index a constant.
add_minmax_index_for_block_offset_column [#add_minmax_index_for_block_offset_column]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, an implicit min-max (skipping) index is added for the persistent virtual column `_block_offset`. Requires `enable_block_offset_column = 1` to take effect. The index is built only during merges, not during inserts.
add_minmax_index_for_numeric_columns [#add_minmax_index_for_numeric_columns]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
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_dimensions_outside_sorting_key [#allow_dimensions_outside_sorting_key]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
In `AggregatingMergeTree`, background merges collapse rows that share the same value of the sorting key, combining their aggregate states. A column that is neither part of the sorting key nor an aggregate-state *measure* (`AggregateFunction` or `SimpleAggregateFunction`) is a *dimension*: after a merge it keeps an arbitrary value of one of the collapsed rows, which silently produces wrong results for queries that `GROUP BY` or filter on it (see [https://github.com/ClickHouse/ClickHouse/issues/751](https://github.com/ClickHouse/ClickHouse/issues/751)). By default, such a schema is rejected when the table is created. Enable this setting if the column is functionally dependent on the sorting key (so all collapsed rows share the same value) and the behavior is intentional. The check applies only to tables that actually aggregate (those with at least one aggregate-state column) and is skipped when `allow_tuple_element_aggregation` is enabled.
allow_experimental_replacing_merge_with_cleanup [#allow_experimental_replacing_merge_with_cleanup]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
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_text_index_phrase_search [#allow_experimental_text_index_phrase_search]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
Allow creating text indexes with the experimental `support_phrase_search` argument which stores token positions to support exact phrase matching.
allow_floating_point_partition_key [#allow_floating_point_partition_key]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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_minmax_index_for_json [#allow_minmax_index_for_json]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Allow creating minmax skip indexes on JSON (Object) columns. Disabled by default because the minmax index serialization path cannot handle heterogeneous Field values that JSON columns may contain.
allow_nullable_key [#allow_nullable_key]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Allow Nullable types as primary keys.
allow_part_offset_column_in_projections [#allow_part_offset_column_in_projections]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Allow usage of '\_part\_offset' column in projections select query.
allow_reduce_blocking_parts_task [#allow_reduce_blocking_parts_task]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Background task which reduces blocking parts for shared merge tree tables. Only in RawTree Cloud
allow_remote_fs_zero_copy_replication [#allow_remote_fs_zero_copy_replication]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, allows summing columns in a SummingMergeTree table to be used in the partition or sorting key.
allow_suspicious_indices [#allow_suspicious_indices]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Reject primary/secondary indexes and sorting keys with identical expressions
allow_tuple_element_aggregation [#allow_tuple_element_aggregation]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, individual elements within Tuple columns participate in aggregation during merge in SummingMergeTree, AggregatingMergeTree, and CoalescingMergeTree. Nested Tuples are expanded recursively so that all leaf elements are aggregated independently. This setting is immutable and must be specified at table creation time.
allow_vertical_merges_from_compact_to_wide_parts [#allow_vertical_merges_from_compact_to_wide_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
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]
**Type:** `AlterColumnSecondaryIndexMode`\
**Default:** `rebuild`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If true, this replica never merges parts and always downloads merged parts from other replicas. Possible values: - true, false
always_fetch_mutated_part [#always_fetch_mutated_part]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If true, this replica never executes `MUTATE_PART` replication log entries (regular mutations such as `ALTER TABLE ... UPDATE/DELETE`) and always downloads the resulting mutated parts from other replicas. At least one replica must have this setting disabled; otherwise mutations cannot finish. This setting does not affect patch parts created by lightweight updates: they are still applied locally when parts are merged. Enable `always_fetch_merged_part` as well to also offload merges (including the application of patch parts) to other replicas. Because this replica does not execute mutations, and mutation failure status is local to each replica, a synchronous wait on this replica cannot observe mutation failures that happen on the replicas executing the mutation. Therefore synchronous mutations (`mutations_sync` = 1 or 2) and synchronous `ALTER` queries that mutate data (`alter_sync` = 1 or 2) are rejected on such a replica with a `SUPPORT_IS_DISABLED` error instead of a wait that would hang if the mutation fails. Use `mutations_sync` = 0 (`alter_sync` = 0), or issue these queries on a replica that executes mutations. Possible values: - true, false
always_use_copy_instead_of_hardlinks [#always_use_copy_instead_of_hardlinks]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Always copy data instead of hardlinking during mutations/replaces/detaches and so on.
apply_patches_on_merge [#apply_patches_on_merge]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true patch parts are applied on merges
assign_part_uuids [#assign_part_uuids]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, a unique part identifier will be assigned for every new part. Before enabling, check that all replicas support UUID version 4.
async_block_ids_cache_update_wait_ms [#async_block_ids_cache_update_wait_ms]
**Type:** `Milliseconds`\
**Default:** `100`\
**Tier:** Production\
Deprecated alias of `deduplication_hashes_cache_update_wait_ms`, kept for one release for backward compatibility. It is honored only when `deduplication_hashes_cache_update_wait_ms` is left at its default; this setting will be removed in a future release.
async_insert [#async_insert]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If true, data from INSERT query is stored in queue and later flushed to table in background.
auto_primary_key_columns_limit [#auto_primary_key_columns_limit]
**Type:** `UInt64`\
**Default:** `6`\
**Tier:** Production
The number of columns to use for the auto primary key generation during background merges for raw MergeTree tables.
auto_primary_key_max_avg_value_bytes [#auto_primary_key_max_avg_value_bytes]
**Type:** `UInt64`\
**Default:** `128`\
**Tier:** Production
The maximum average bytes per value of a JSON path for it to be used in the auto-generated primary key of raw MergeTree tables at INSERT and for statistics-derived keys during merges. 0 disables the limit.
auto_primary_key_max_bytes_resort_ratio [#auto_primary_key_max_bytes_resort_ratio]
**Type:** `Float`\
**Default:** `1`\
**Tier:** Production
The maximum fraction of the merged uncompressed bytes that the auto primary key may re-sort during background merges for raw MergeTree tables. 1 allows any re-sort.
auto_primary_key_max_bytes_to_resort [#auto_primary_key_max_bytes_to_resort]
**Type:** `UInt64`\
**Default:** `10737418240`\
**Tier:** Production
The maximum uncompressed bytes that the auto primary key may re-sort during background merges for raw MergeTree tables. 0 disables the limit.
auto_primary_key_max_rows_resort_ratio [#auto_primary_key_max_rows_resort_ratio]
**Type:** `Float`\
**Default:** `1`\
**Tier:** Production
The maximum fraction of the merged rows that the auto primary key may re-sort during background merges for raw MergeTree tables. 1 allows any re-sort.
auto_projection_aggregate_function_min_count [#auto_projection_aggregate_function_min_count]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Auto projection advisor merges different aggregate functions for the same GROUP BY columns, for example `SELECT id, COUNT() FROM table GROUP BY id` and `SELECT id, SUM(value) FROM table GROUP BY id`. In this queries there are COUNT() and SUM(value) aggregate functions. Auto projection advisor wants to create projection that will cover both those queries, and if the count of the aggregate function was used at least `auto_projection_aggregate_function_min_count` times it will be added in final projection.
auto_projection_analysis_interval_seconds [#auto_projection_analysis_interval_seconds]
**Type:** `UInt64`\
**Default:** `300`\
**Tier:** Production\
Interval in seconds between auto-projection analysis runs for raw tables.
auto_projection_materialize_after_create [#auto_projection_materialize_after_create]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Enable automatic materialization of auto-projections after creation.
auto_projection_max_aggregation_cardinality_ratio [#auto_projection_max_aggregation_cardinality_ratio]
**Type:** `Float`\
**Default:** `0.1`\
**Tier:** Production\
Maximum aggregation cardinality ratio to consider for auto-projection creation.
auto_projection_max_projections_count [#auto_projection_max_projections_count]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Maximum number of auto-projections to create.
auto_projection_min_query_execution_time_ms [#auto_projection_min_query_execution_time_ms]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
Minimum query execution time in milliseconds to consider for auto-projection analysis.
auto_projection_query_patterns_stats_queue_size [#auto_projection_query_patterns_stats_queue_size]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
Size of the queue of query patterns stats that satisfy both `auto_projection_min_query_execution_time_ms` and `auto_projection_max_aggregation_cardinality_ratio` thresholds. If the queue is full, the oldest query patterns stats are removed.
auto_projection_ttl_if_not_used_seconds [#auto_projection_ttl_if_not_used_seconds]
**Type:** `UInt64`\
**Default:** `172800`\
**Tier:** Production\
TTL in seconds for auto-projections that are not used for a given period of time.
auto_statistics_types [#auto_statistics_types]
**Type:** `String`\
**Default:** `basic, uniq_v2`\
**Tier:** Production\
Comma-separated list of statistics types to calculate automatically on all suitable columns. Supported statistics types: basic, tdigest, countmin, uniq, uniq\_v2. The `minmax` statistics type is deprecated: it is a subset of `basic`, which should be used instead.
autogenerate_primary_key [#autogenerate_primary_key]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
For raw MergeTree tables without an explicit primary key, automatically derive a per-part sorting key from the ingested data on INSERT and pick a winner sorting key during background merges. When disabled, new parts are written unsorted and merges keep the empty sorting key as the winner, so no part is ever re-sorted. Ignored when the table has an explicit PRIMARY KEY, which always wins.
background_task_preferred_step_execution_time_ms [#background_task_preferred_step_execution_time_ms]
**Type:** `Milliseconds`\
**Default:** `50`\
**Tier:** Production\
Target time to execution of one step of merge or mutation. Can be exceeded if one step takes longer time
cache_populated_by_fetch [#cache_populated_by_fetch]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
:::note This setting applies only to RawTree Cloud. ::: When `cache_populated_by_fetch` is disabled (the default setting), new data parts are loaded into the filesystem cache only when a query is run that requires those parts. If enabled, `cache_populated_by_fetch` will instead cause all nodes to load new data parts from storage into their filesystem cache without requiring a query to trigger such an action. **See Also** - [ignore\_cold\_parts\_seconds](/reference/settings/session-settings/ignore#ignore_cold_parts_seconds) - [prefer\_warmed\_unmerged\_parts\_seconds](/reference/settings/session-settings/prefer#prefer_warmed_unmerged_parts_seconds) - [cache\_warmer\_threads](/reference/settings/session-settings/other#cache_warmer_threads)
cache_populated_by_fetch_filename_regexp [#cache_populated_by_fetch_filename_regexp]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
:::note This setting applies only to RawTree Cloud. ::: If not empty, only files that match this regex will be prewarmed into the cache after fetch (if `cache_populated_by_fetch` is enabled).
cache_populated_by_fetch_max_part_bytes [#cache_populated_by_fetch_max_part_bytes]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Production\
Maximum size of a part (bytes on disk) to populate the filesystem cache after fetch. Parts larger than this threshold will not be prewarmed to avoid flooding the cache. Only has an effect when `cache_populated_by_fetch` is enabled. Default is 1 GiB.
check_sample_column_is_correct [#check_sample_column_is_correct]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables the check at table creation, that the data type of a column for s ampling or sampling expression is correct. The data type must be one of unsigned [integer types](/reference/data-types/int-uint): `UInt8`, `UInt16`, `UInt32`, `UInt64`. Possible values: - `true` — The check is enabled. - `false` — The check is disabled at table creation. Default value: `true`. By default, the RawTree server checks at table creation the data type of a column for sampling or sampling expression. If you already have tables with incorrect sampling expression and do not want the server to raise an exception during startup, set `check_sample_column_is_correct` to `false`.
cleanup_delay_period [#cleanup_delay_period]
**Type:** `UInt64`\
**Default:** `30`\
**Tier:** Production\
Minimum period to clean old queue logs, blocks hashes and parts.
cleanup_delay_period_random_add [#cleanup_delay_period_random_add]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Add uniformly distributed value from 0 to x seconds to cleanup\_delay\_period to avoid thundering herd effect and subsequent DoS of ZooKeeper in case of very large number of tables.
cleanup_thread_preferred_points_per_iteration [#cleanup_thread_preferred_points_per_iteration]
**Type:** `UInt64`\
**Default:** `150`\
**Tier:** Production\
Preferred batch size for background cleanup (points are abstract but 1 point is approximately equivalent to 1 inserted block).
clone_replica_zookeeper_create_get_part_batch_size [#clone_replica_zookeeper_create_get_part_batch_size]
**Type:** `NonZeroUInt64`\
**Default:** `100`\
**Tier:** Production\
Batch size for ZooKeeper multi-create get-part requests when cloning replica.
columns_and_secondary_indices_sizes_lazy_calculation [#columns_and_secondary_indices_sizes_lazy_calculation]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Calculate columns and secondary indices sizes lazily on first request instead of on table initialization.
columns_to_prewarm_mark_cache [#columns_to_prewarm_mark_cache]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
List of columns to prewarm mark cache for (if enabled). Empty means all columns
compact_parts_max_bytes_to_buffer [#compact_parts_max_bytes_to_buffer]
**Type:** `UInt64`\
**Default:** `134217728`\
**Tier:** Production
Cloud. Maximal number of bytes to write in a single stripe in compact parts
compact_parts_max_granules_to_buffer [#compact_parts_max_granules_to_buffer]
**Type:** `NonZeroUInt64`\
**Default:** `128`\
**Tier:** Production
Cloud. Maximal number of granules to write in a single stripe in compact parts
compact_parts_merge_max_bytes_to_prefetch_part [#compact_parts_merge_max_bytes_to_prefetch_part]
**Type:** `UInt64`\
**Default:** `16777216`\
**Tier:** Production
Cloud. Maximal size of compact part to read it in a whole to memory during merge.
compatibility_allow_sampling_expression_not_in_primary_key [#compatibility_allow_sampling_expression_not_in_primary_key]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Allow to create a table with sampling expression not in primary key. This is needed only to temporarily allow to run the server with wrong tables for backward compatibility.
compress_marks [#compress_marks]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Marks support compression, reduce mark file size and speed up network transmission.
compress_per_column_in_compact_parts [#compress_per_column_in_compact_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Controls the physical layout of Compact parts. If true (default), each column in a granule starts a new compressed block, allowing ClickHouse to skip reading unnecessary columns from disk. If false, all columns within a granule are packed into the same compressed block, improving compression ratio but requiring more data to be decompressed during reads. This is beneficial for workloads that always read all columns (e.g. projections).
compress_primary_key [#compress_primary_key]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Primary key support compression, reduce primary key file size and speed up network transmission.
compute_exact_num_defaults_for_sparse_columns [#compute_exact_num_defaults_for_sparse_columns]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Beta\
Compute the exact count of default values per column during inserts and merges, instead of the cheaper sampling estimate used to decide on sparse serialization. Required by `optimize_trivial_count_with_sparsity_filter`, which consumes the persisted `num_defaults` counter (Nullable columns additionally need `nullable_serialization_version = 'allow_sparse'`). Leaving it disabled keeps inserts/merges as fast as before; enabling it adds an O(rows) pass per sparse-eligible column.
concurrent_part_removal_threshold [#concurrent_part_removal_threshold]
**Type:** `UInt64`\
**Default:** `100`\
**Tier:** Production\
Activate concurrent part removal (see 'max\_part\_removal\_threads') only if the number of inactive data parts is at least this.
concurrent_part_removal_threshold_for_remote_disk [#concurrent_part_removal_threshold_for_remote_disk]
**Type:** `UInt64`\
**Default:** `16`\
**Tier:** Production\
Same as `concurrent_part_removal_threshold`, but used when at least one part being removed is stored on a remote disk. The default is lower because each part removal on remote storage typically requires a network round-trip (e.g. one HTTP `DELETE` per part on object storage), so a serial removal of even 100 parts can stall a `DROP TABLE` for tens of seconds.
dead_blobs_to_delay_insert [#dead_blobs_to_delay_insert]
**Type:** `UInt64`\
**Default:** `100000`\
**Tier:** Production\
If the number of blobs pending removal in the dead blobs queues of the table's disks exceeds the `dead_blobs_to_delay_insert` value, an `INSERT` is artificially slowed down (up to `max_delay_to_insert`). The dead blobs queue belongs to the disk and is shared by all tables on it (including blobs of already dropped tables), so size the threshold for the whole disk rather than a single table. :::tip It is useful when a server fails to clean up blobs quickly enough. ::: Possible values: - Any positive integer. - 0 — disabled.
dead_blobs_to_throw_insert [#dead_blobs_to_throw_insert]
**Type:** `UInt64`\
**Default:** `1000000`\
**Tier:** Production\
If the number of blobs pending removal in the dead blobs queues of the table's disks is more than the `dead_blobs_to_throw_insert` value, `INSERT` is interrupted with the following error: > "Too many dead blobs in queue (N) on disks of table. Blobs cleanup is processing significantly slower than inserts" The dead blobs queue belongs to the disk and is shared by all tables on it (including blobs of already dropped tables), so size the threshold for the whole disk rather than a single table. Possible values: - Any positive integer. - 0 — disabled.
deduplicate_merge_projection_mode [#deduplicate_merge_projection_mode]
**Type:** `DeduplicateMergeProjectionMode`\
**Default:** `throw`\
**Tier:** Production\
Whether to allow create projection for the table with non-classic MergeTree, that is not (Replicated, Shared) MergeTree. Ignore option is purely for compatibility which might result in incorrect answer. Otherwise, if allowed, what is the action when merge projections, either drop or rebuild. So classic MergeTree would ignore this setting. It also controls `OPTIMIZE DEDUPLICATE` as well, but has effect on all MergeTree family members. Similar to the option `lightweight_mutation_projection_mode`, it is also part level. Possible values: - `ignore` - `throw` - `drop` - `rebuild`
deduplication_hashes_cache_update_wait_ms [#deduplication_hashes_cache_update_wait_ms]
**Type:** `Milliseconds`\
**Default:** `100`\
**Tier:** Production\
How long each insert iteration waits for the in-memory `deduplication_hashes` cache to refresh to a newer version before re-checking it for already-inserted blocks. The cache mirrors the `deduplication_hashes` directory in ClickHouse Keeper so inserts can detect duplicates without a Keeper round-trip.
default_compression_codec [#default_compression_codec]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
Specifies the default compression codec to be used if none is defined for a particular column in the table declaration. Compression codec selecting order for a column: 1. Compression codec defined for the column in the table declaration 2. Compression codec defined in `default_compression_codec` (this setting) 3. Default compression codec defined in `compression` settings Default value: an empty string (not defined).
detach_not_byte_identical_parts [#detach_not_byte_identical_parts]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Enables or disables detaching a data part on a replica after a merge or a mutation, if it is not byte-identical to data parts on other replicas. If disabled, the data part is removed. Activate this setting if you want to analyze such parts later. The setting is applicable to `MergeTree` tables with enabled [data replication](/reference/engines/table-engines/mergetree-family/replacingmergetree). Possible values: - `0` — Parts are removed. - `1` — Parts are detached.
detach_old_local_parts_when_cloning_replica [#detach_old_local_parts_when_cloning_replica]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Do not remove old local parts when repairing lost replica. Possible values: - `true` - `false`
disable_detach_partition_for_zero_copy_replication [#disable_detach_partition_for_zero_copy_replication]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Disable DETACH PARTITION query for zero copy replication.
disable_fetch_partition_for_zero_copy_replication [#disable_fetch_partition_for_zero_copy_replication]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Disable FETCH PARTITION query for zero copy replication.
disable_freeze_partition_for_zero_copy_replication [#disable_freeze_partition_for_zero_copy_replication]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Disable FREEZE PARTITION query for zero copy replication.
disk [#disk]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
Name of storage disk. Can be specified instead of storage policy.
distributed_index_analysis_min_indexes_bytes_to_activate [#distributed_index_analysis_min_indexes_bytes_to_activate]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Experimental\
Minimal index sizes (data skipping and primary key) on disk (but uncompressed) to activated distributed index analysis
distributed_index_analysis_min_parts_to_activate [#distributed_index_analysis_min_parts_to_activate]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Experimental\
Minimal number of parts to activated distributed index analysis
dynamic_serialization_version [#dynamic_serialization_version]
**Type:** `MergeTreeDynamicSerializationVersion`\
**Default:** `v3`\
**Tier:** Production\
Serialization version for Dynamic data type. Required for compatibility. Possible values: - `v1` - `v2` - `v3`
enable_adaptive_codec_selection [#enable_adaptive_codec_selection]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
When enabled, merges and mutations choose a codec per block for columns that use the default codec (no `CODEC` clause, or `CODEC(Default)`). The candidates are the table's default codec (see the `default_compression_codec` setting), `NONE`, and specialized codecs suited to the column type. Only integer-like types are currently adaptive. The smallest output wins. Compression is therefore never worse than the default, and incompressible blocks are stored raw. A column whose default codec includes encryption (e.g. `AES_128_GCM_SIV`) is never selected adaptively, so encryption is always applied. Per-block codecs are reported by the [`mergeTreeCodecBlockCounts`](/reference/functions/table-functions) table function.
enable_auto_projections [#enable_auto_projections]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Enable automatic projection creation by projection advisor.
enable_block_number_column [#enable_block_number_column]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Enable persisting column \_block\_number for each row.
enable_block_offset_column [#enable_block_offset_column]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Persists virtual column `_block_offset` on merges.
enable_index_granularity_compression [#enable_index_granularity_compression]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Compress in memory values of index granularity if it is possible
enable_max_bytes_limit_for_min_age_to_force_merge [#enable_max_bytes_limit_for_min_age_to_force_merge]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If settings `min_age_to_force_merge_seconds` and `min_age_to_force_merge_on_partition_only` should respect setting `max_bytes_to_merge_at_max_space_in_pool`. Possible values: - `true` - `false`
enable_mixed_granularity_parts [#enable_mixed_granularity_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables or disables transitioning to control the granule size with the `index_granularity_bytes` setting. Before version 19.11, there was only the `index_granularity` setting for restricting granule size. The `index_granularity_bytes` setting improves RawTree performance when selecting data from tables with big rows (tens and hundreds of megabytes). If you have tables with big rows, you can enable this setting for the tables to improve the efficiency of `SELECT` queries.
enable_replacing_merge_with_cleanup_for_min_age_to_force_merge [#enable_replacing_merge_with_cleanup_for_min_age_to_force_merge]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
Whether to use CLEANUP merges for ReplacingMergeTree when merging partitions down to a single part. Requires `allow_experimental_replacing_merge_with_cleanup`, `min_age_to_force_merge_seconds` and `min_age_to_force_merge_on_partition_only` to be enabled. Possible values: - `true` - `false`
enable_row_mask_update_coalescing [#enable_row_mask_update_coalescing]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Coalesce adjacent singleton `UPDATE _row_exists = 0` commands while mutating a Wide part with Full storage. The persisted mutation commands and their versions are unchanged. Set to false to execute each command separately.
enable_subvertical_merge_for_object_columns [#enable_subvertical_merge_for_object_columns]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true, vertical merges of Object (JSON) columns are processed one path at a time ("sub-vertical merge") to reduce peak memory. Each path is read as its own subcolumn from every source part, gathered, and written as a substream of the merged Object column, in the order: typed paths first, then dynamic paths, then a final wrap-up batch that writes shared\_data and the structure header. Disable to process Object columns in a single vertical pass (older behavior).
enable_the_endpoint_id_with_zookeeper_name_prefix [#enable_the_endpoint_id_with_zookeeper_name_prefix]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Enable the endpoint id with zookeeper name prefix for the replicated merge tree table.
enable_vertical_merge_algorithm [#enable_vertical_merge_algorithm]
**Type:** `UInt64`\
**Default:** `1`\
**Tier:** Production\
Enable usage of Vertical merge algorithm.
enforce_index_structure_match_on_partition_manipulation [#enforce_index_structure_match_on_partition_manipulation]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If this setting is enabled for destination table of a partition manipulation query (`ATTACH/MOVE/REPLACE PARTITION`), the indices and projections must be identical between the source and destination tables. Otherwise, the destination table can have a superset of the source table's indices and projections.
escape_index_filenames [#escape_index_filenames]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Prior to 26.1 we didn't escape special symbols in filenames created for secondary indices, which could lead to issues with some characters in index names producing broken parts. This is added purely for compatibility reasons. It should not be changed unless you are reading old parts with indices using non-ascii characters in their names.
escape_variant_subcolumn_filenames [#escape_variant_subcolumn_filenames]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Escape special symbols in filenames created for subcolumns of Variant data type in Wide parts of MergeTree table. Needed for compatibility.
exclude_deleted_rows_for_part_size_in_merge [#exclude_deleted_rows_for_part_size_in_merge]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If enabled, estimated actual size of data parts (i.e., excluding those rows that have been deleted through `DELETE FROM`) will be used when selecting parts to merge. Note that this behavior is only triggered for data parts affected by `DELETE FROM` executed after this setting is enabled. Possible values: - `true` - `false` **See Also** - [load\_existing\_rows\_count\_for\_old\_parts](#load_existing_rows_count_for_old_parts) setting
exclude_materialize_skip_indexes_on_merge [#exclude_materialize_skip_indexes_on_merge]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
Excludes provided comma delimited list of skip indexes from being built and stored during merges. Has no effect if [materialize\_skip\_indexes\_on\_merge](#materialize_skip_indexes_on_merge) is false. The excluded skip indexes will still be built and stored by an explicit [MATERIALIZE INDEX](/reference/statements/alter/skipping-index#materialize-index) query or during INSERTs depending on the [materialize\_skip\_indexes\_on\_insert](/reference/settings/session-settings/materialize#materialize_skip_indexes_on_insert) session setting. Example: `sql CREATE TABLE tab ( a UInt64, b UInt64, INDEX idx_a a TYPE minmax, INDEX idx_b b TYPE set(3) ) ENGINE = MergeTree ORDER BY tuple() SETTINGS exclude_materialize_skip_indexes_on_merge = 'idx_a'; INSERT INTO tab SELECT number, number / 50 FROM numbers(100); -- setting has no effect on INSERTs -- idx_a will be excluded from update during background or explicit merge via OPTIMIZE TABLE FINAL -- can exclude multiple indexes by providing a list ALTER TABLE tab MODIFY SETTING exclude_materialize_skip_indexes_on_merge = 'idx_a, idx_b'; -- default setting, no indexes excluded from being updated during merge ALTER TABLE tab MODIFY SETTING exclude_materialize_skip_indexes_on_merge = ''; `
execute_merges_on_single_replica_time_threshold [#execute_merges_on_single_replica_time_threshold]
**Type:** `Seconds`\
**Default:** `0`\
**Tier:** Production\
When this setting has a value greater than zero, only a single replica starts the merge immediately, and other replicas wait up to that amount of time to download the result instead of doing merges locally. If the chosen replica doesn't finish the merge during that amount of time, fallback to standard behavior happens. Possible values: - Any positive integer.
fault_probability_after_part_commit [#fault_probability_after_part_commit]
**Type:** `Float`\
**Default:** `0`\
**Tier:** Production\
For testing. Do not change it.
fault_probability_before_part_commit [#fault_probability_before_part_commit]
**Type:** `Float`\
**Default:** `0`\
**Tier:** Production\
For testing. Do not change it.
finished_mutations_to_keep [#finished_mutations_to_keep]
**Type:** `UInt64`\
**Default:** `100`\
**Tier:** Production\
How many records about mutations that are done to keep. If zero, then keep all of them.
force_read_through_cache_for_merges [#force_read_through_cache_for_merges]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
Force read-through filesystem cache for merges
fsync_after_insert [#fsync_after_insert]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Do fsync for every inserted part. Significantly decreases performance of inserts, not recommended to use with wide parts.
fsync_part_directory [#fsync_part_directory]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Do fsync for part directory after all part operations (writes, renames, etc.).
inactive_parts_to_delay_insert [#inactive_parts_to_delay_insert]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
If the number of inactive parts in a single partition in the table exceeds the `inactive_parts_to_delay_insert` value, an `INSERT` is artificially slowed down. :::tip It is useful when a server fails to clean up parts quickly enough. ::: Possible values: - Any positive integer.
inactive_parts_to_throw_insert [#inactive_parts_to_throw_insert]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
If the number of inactive parts in a single partition more than the `inactive_parts_to_throw_insert` value, `INSERT` is interrupted with the following error: > "Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts" exception." Possible values: - Any positive integer.
index_granularity [#index_granularity]
**Type:** `UInt64`\
**Default:** `8192`\
**Tier:** Production\
Maximum number of data rows between the marks of an index. I.e how many rows correspond to one primary key value.
index_granularity_bytes [#index_granularity_bytes]
**Type:** `UInt64`\
**Default:** `10485760`\
**Tier:** Production\
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]
**Type:** `Seconds`\
**Default:** `60`\
**Tier:** Production\
Retry period for table initialization, in seconds.
is_raw [#is_raw]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production
If specified makes table a raw MergeTree table.
lightweight_mutation_projection_mode [#lightweight_mutation_projection_mode]
**Type:** `LightweightMutationProjectionMode`\
**Default:** `throw`\
**Tier:** Production\
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]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
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]
**Type:** `Seconds`\
**Default:** `120`\
**Tier:** Production\
For background operations like merges, mutations etc. How many seconds before failing to acquire table locks.
map_buckets_coefficient [#map_buckets_coefficient]
**Type:** `Float`\
**Default:** `1`\
**Tier:** Production\
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]
**Type:** `UInt64`\
**Default:** `32`\
**Tier:** Production\
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]
**Type:** `MergeTreeMapBucketsStrategy`\
**Default:** `sqrt`\
**Tier:** Production\
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]
**Type:** `MergeTreeMapSerializationVersion`\
**Default:** `basic`\
**Tier:** Production\
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]
**Type:** `MergeTreeMapSerializationVersion`\
**Default:** `basic`\
**Tier:** Production\
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]
**Type:** `NonZeroUInt64`\
**Default:** `65536`\
**Tier:** Production\
Mark compress block size, the actual size of the block to compress.
marks_compression_codec [#marks_compression_codec]
**Type:** `String`\
**Default:** `ZSTD(3)`\
**Tier:** Production\
Compression encoding used by marks, marks are small enough and cached, so the default compression is ZSTD(3).
materialize_projections_on_insert [#materialize_projections_on_insert]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled, INSERTs create new parts with projections. Otherwise, they can be created by explicit [MATERIALIZE PROJECTION](/reference/statements/alter/projection#materialize-projection) or during merges with [materialize\_projections\_on\_merge](/reference/settings/merge-tree-settings/materialize-projections#materialize_projections_on_merge).
materialize_projections_on_merge [#materialize_projections_on_merge]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
When enabled, a merge rebuilds a projection that is missing from all of its source parts (for example because they were inserted with `materialize_projections_on_insert = 0`), so the merged part has the projection. Merges still only combine parts that share the same set of projections. To backfill a projection to all existing parts, use an explicit [MATERIALIZE PROJECTION](/reference/statements/alter/projection#materialize-projection). Projections are also created during INSERTs with [materialize\_projections\_on\_insert](/reference/settings/merge-tree-settings/materialize-projections#materialize_projections_on_insert).
materialize_skip_indexes_on_merge [#materialize_skip_indexes_on_merge]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled, merges build and store skip indices for new parts. Otherwise they can be created/stored by explicit [MATERIALIZE INDEX](/reference/statements/alter/skipping-index#materialize-index) or [during INSERTs](/reference/settings/session-settings/materialize#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]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled, merges will build and store statistics for new parts. Otherwise they can be created/stored by explicit [MATERIALIZE STATISTICS](/docs/reference/sql/statements) or [during INSERTs](/reference/settings/session-settings/materialize-statistics-on-insert#materialize_statistics_on_insert)
materialize_ttl_recalculate_only [#materialize_ttl_recalculate_only]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Only recalculate ttl info when MATERIALIZE TTL
max_avg_part_size_for_too_many_parts [#max_avg_part_size_for_too_many_parts]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Production\
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]
**Type:** `NonZeroUInt64`\
**Default:** `32`\
**Tier:** Production\
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]
**Type:** `UInt64`\
**Default:** `161061273600`\
**Tier:** Production\
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. The merge scheduler periodically analyzes the sizes and number of parts in partitions, and if there are enough free resources in the pool, it starts background merges. Merges occur until the total size of the source parts is larger than `max_bytes_to_merge_at_max_space_in_pool`. Merges initiated by [OPTIMIZE FINAL](/reference/statements/optimize) ignore `max_bytes_to_merge_at_max_space_in_pool` (only the free disk space is taken into account).
max_bytes_to_merge_at_min_space_in_pool [#max_bytes_to_merge_at_min_space_in_pool]
**Type:** `UInt64`\
**Default:** `1048576`\
**Tier:** Production\
The maximum total part size (in bytes) to be merged into one part, with the minimum available resources in the background pool. Possible values: - Any positive integer. `max_bytes_to_merge_at_min_space_in_pool` defines the maximum total size of parts which can be merged despite the lack of available disk space (in pool). This is necessary to reduce the number of small parts and the chance of `Too many parts` errors. Merges book disk space by doubling the total merged parts sizes. Thus, with a small amount of free disk space, a situation may occur in which there is free space, but this space is already booked by ongoing large merges, so other merges are unable to start, and the number of small parts grows with every insert.
max_cleanup_delay_period [#max_cleanup_delay_period]
**Type:** `UInt64`\
**Default:** `300`\
**Tier:** Production\
Maximum period to clean old queue logs, blocks hashes and parts.
max_compress_block_size [#max_compress_block_size]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
The maximum size of blocks of uncompressed data before compressing for writing to a table. You can also specify this setting in the global settings (see [max\_compress\_block\_size](/reference/settings/merge-tree-settings/max#max_compress_block_size) setting). The value specified when the table is created overrides the global value for this setting.
max_concurrent_queries [#max_concurrent_queries]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Max number of concurrently executed queries related to the MergeTree table. Queries will still be limited by other `max_concurrent_queries` settings. Possible values: - Positive integer. - `0` — No limit. Default value: `0` (no limit). **Example** `xml 50 `
max_delay_to_insert [#max_delay_to_insert]
**Type:** `UInt64`\
**Default:** `1`\
**Tier:** Production\
The value in seconds, which is used to calculate the `INSERT` delay, if the number of active parts in a single partition exceeds the [parts\_to\_delay\_insert](#parts_to_delay_insert) value. Possible values: - Any positive integer. The delay (in milliseconds) for `INSERT` is calculated by the formula: `code max_k = parts_to_throw_insert - parts_to_delay_insert k = 1 + parts_count_in_partition - parts_to_delay_insert delay_milliseconds = pow(max_delay_to_insert * 1000, k / max_k) ` For example, if a partition has 299 active parts and parts\_to\_throw\_insert = 300, parts\_to\_delay\_insert = 150, max\_delay\_to\_insert = 1, `INSERT` is delayed for `pow( 1 * 1000, (1 + 299 - 150) / (300 - 150) ) = 1000` milliseconds. Starting from version 23.1 formula has been changed to: `code allowed_parts_over_threshold = parts_to_throw_insert - parts_to_delay_insert parts_over_threshold = parts_count_in_partition - parts_to_delay_insert + 1 delay_milliseconds = max(min_delay_to_insert_ms, (max_delay_to_insert * 1000) * parts_over_threshold / allowed_parts_over_threshold) ` For example, if a partition has 224 active parts and parts\_to\_throw\_insert = 300, parts\_to\_delay\_insert = 150, max\_delay\_to\_insert = 1, min\_delay\_to\_insert\_ms = 10, `INSERT` is delayed for `max( 10, 1 * 1000 * (224 - 150 + 1) / (300 - 150) ) = 500` milliseconds.
max_delay_to_mutate_ms [#max_delay_to_mutate_ms]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
Max delay of mutating MergeTree table in milliseconds, if there are a lot of unfinished mutations
max_file_name_length [#max_file_name_length]
**Type:** `UInt64`\
**Default:** `127`\
**Tier:** Production\
The maximal length of the file name to keep it as is without hashing. Takes effect only if setting `replace_long_file_name_to_hash` is enabled. The value of this setting does not include the length of file extension. So, it is recommended to set it below the maximum filename length (usually 255 bytes) with some gap to avoid filesystem errors.
max_files_to_modify_in_alter_columns [#max_files_to_modify_in_alter_columns]
**Type:** `UInt64`\
**Default:** `75`\
**Tier:** Production\
Do not apply ALTER if number of files for modification(deletion, addition) is greater than this setting. Possible values: - Any positive integer. Default value: 75
max_files_to_remove_in_alter_columns [#max_files_to_remove_in_alter_columns]
**Type:** `UInt64`\
**Default:** `50`\
**Tier:** Production\
Do not apply ALTER, if the number of files for deletion is greater than this setting. Possible values: - Any positive integer.
max_merge_delayed_streams_for_parallel_write [#max_merge_delayed_streams_for_parallel_write]
**Type:** `UInt64`\
**Default:** `40`\
**Tier:** Production\
The maximum number of streams (columns) that can be flushed in parallel (analog of max\_insert\_delayed\_streams\_for\_parallel\_write for merges). Works only for Vertical merges.
max_merge_selecting_sleep_ms [#max_merge_selecting_sleep_ms]
**Type:** `UInt64`\
**Default:** `60000`\
**Tier:** Production\
Maximum time to wait before trying to select parts to merge again after no parts were selected. A lower setting will trigger selecting tasks in background\_schedule\_pool frequently which result in large amount of requests to zookeeper in large-scale clusters
max_merged_part_offsets_rows [#max_merged_part_offsets_rows]
**Type:** `UInt64`\
**Default:** `100000000`\
**Tier:** Production\
The maximum number of merged rows for which text indexes and `_part_offset` projections are merged from the source parts during the merge, using auxiliary in-memory state that grows with the row count. Above this many rows they are rebuilt from scratch instead, trading extra CPU for bounded memory. 0 disables the limit.
max_number_of_merges_with_ttl_in_pool [#max_number_of_merges_with_ttl_in_pool]
**Type:** `UInt64`\
**Default:** `2`\
**Tier:** Production\
When there is more than specified number of merges with TTL entries in pool, do not assign new merge with TTL. This is to leave free threads for regular merges and avoid "Too many parts"
max_number_of_mutations_for_replica [#max_number_of_mutations_for_replica]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Limit the number of part mutations per replica to the specified amount. Zero means no limit on the number of mutations per replica (the execution can still be constrained by other settings).
max_partitions_to_read [#max_partitions_to_read]
**Type:** `Int64`\
**Default:** `-1`\
**Tier:** Production\
Limits the maximum number of partitions that can be accessed in one query. The setting value specified when the table is created can be overridden via query-level setting. Possible values: - Any positive integer. You can also specify a query complexity setting [max\_partitions\_to\_read](/reference/settings/session-settings/max-partitions#max_partitions_to_read) at a query / session / profile level.
max_parts_in_total [#max_parts_in_total]
**Type:** `UInt64`\
**Default:** `100000`\
**Tier:** Production\
If the total number of active parts in all partitions of a table exceeds the `max_parts_in_total` value `INSERT` is interrupted with the `Too many parts (N)` exception. Possible values: - Any positive integer. A large number of parts in a table reduces performance of RawTree queries and increases RawTree boot time. Most often this is a consequence of an incorrect design (mistakes when choosing a partitioning strategy - too small partitions).
max_parts_to_merge_at_once [#max_parts_to_merge_at_once]
**Type:** `UInt64`\
**Default:** `100`\
**Tier:** Production\
Max amount of parts which can be merged at once (0 - disabled). Doesn't affect OPTIMIZE FINAL query.
max_postpone_time_for_failed_mutations_ms [#max_postpone_time_for_failed_mutations_ms]
**Type:** `UInt64`\
**Default:** `300000`\
**Tier:** Production\
The maximum postpone time for failed mutations.
max_postpone_time_for_failed_replicated_fetches_ms [#max_postpone_time_for_failed_replicated_fetches_ms]
**Type:** `UInt64`\
**Default:** `60000`\
**Tier:** Production\
The maximum postpone time for failed replicated fetches.
max_postpone_time_for_failed_replicated_merges_ms [#max_postpone_time_for_failed_replicated_merges_ms]
**Type:** `UInt64`\
**Default:** `60000`\
**Tier:** Production\
The maximum postpone time for failed replicated merges.
max_postpone_time_for_failed_replicated_tasks_ms [#max_postpone_time_for_failed_replicated_tasks_ms]
**Type:** `UInt64`\
**Default:** `300000`\
**Tier:** Production\
The maximum postpone time for failed replicated task. The value is used if the task is not a fetch, merge or mutation.
max_projections [#max_projections]
**Type:** `UInt64`\
**Default:** `25`\
**Tier:** Production\
The maximum number of merge tree projections.
max_replicated_fetches_network_bandwidth [#max_replicated_fetches_network_bandwidth]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Limits the maximum speed of data exchange over the network in bytes per second for [replicated](/reference/engines/table-engines/mergetree-family/replication) fetches. This setting is applied to a particular table, unlike the [`max_replicated_fetches_network_bandwidth_for_server`](/reference/settings/server-settings/settings/max-replicated#max_replicated_fetches_network_bandwidth_for_server) setting, which is applied to the server. You can limit both server network and network for a particular table, but for this the value of the table-level setting should be less than server-level one. Otherwise the server considers only the `max_replicated_fetches_network_bandwidth_for_server` setting. The setting isn't followed perfectly accurately. Possible values: - Positive integer. - `0` — Unlimited. Default value: `0`. **Usage** Could be used for throttling speed when replicating data to add or replace new nodes.
max_replicated_logs_to_keep [#max_replicated_logs_to_keep]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
How many records may be in the RawTree Keeper log if there is inactive replica. An inactive replica becomes lost when when this number exceed. Possible values: - Any positive integer.
max_replicated_merges_in_queue [#max_replicated_merges_in_queue]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
How many tasks of merging and mutating parts are allowed simultaneously in ReplicatedMergeTree queue.
max_replicated_merges_with_ttl_in_queue [#max_replicated_merges_with_ttl_in_queue]
**Type:** `UInt64`\
**Default:** `1`\
**Tier:** Production\
How many tasks of merging parts with TTL are allowed simultaneously in ReplicatedMergeTree queue.
max_replicated_mutations_in_queue [#max_replicated_mutations_in_queue]
**Type:** `UInt64`\
**Default:** `8`\
**Tier:** Production\
How many tasks of mutating parts are allowed simultaneously in ReplicatedMergeTree queue.
max_replicated_sends_network_bandwidth [#max_replicated_sends_network_bandwidth]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Limits the maximum speed of data exchange over the network in bytes per second for [replicated](/reference/engines/table-engines/mergetree-family/replacingmergetree) sends. This setting is applied to a particular table, unlike the [`max_replicated_sends_network_bandwidth_for_server`](/reference/settings/server-settings/settings/max-replicated#max_replicated_sends_network_bandwidth_for_server) setting, which is applied to the server. You can limit both server network and network for a particular table, but for this the value of the table-level setting should be less than server-level one. Otherwise the server considers only the `max_replicated_sends_network_bandwidth_for_server` setting. The setting isn't followed perfectly accurately. Possible values: - Positive integer. - `0` — Unlimited. **Usage** Could be used for throttling speed when replicating data to add or replace new nodes.
max_row_mask_update_coalescing_ast_bytes [#max_row_mask_update_coalescing_ast_bytes]
**Type:** `UInt64`\
**Default:** `1048576`\
**Tier:** Production\
Maximum sum of the original commands' `ast_text` bytes eligible for row-mask update coalescing. The command-count limit alone cannot bound this copy when commands contain large literals. Set to 0 to disable coalescing.
max_row_mask_update_coalescing_commands [#max_row_mask_update_coalescing_commands]
**Type:** `UInt64`\
**Default:** `4096`\
**Tier:** Production\
Maximum number of part-local mutation commands eligible for row-mask update coalescing. This bounds the extra command-list copy made by the optimization; the value is a resource guard, not a semantic limit. Values below 2 disable coalescing.
max_row_mask_update_coalescing_key_bytes [#max_row_mask_update_coalescing_key_bytes]
**Type:** `UInt64`\
**Default:** `65536`\
**Tier:** Production\
Maximum total number of key-literal bytes in one coalesced row-mask update. This bounds the size of the generated `IN` predicate. Set to 0 to disable coalescing.
max_row_mask_update_coalescing_keys [#max_row_mask_update_coalescing_keys]
**Type:** `UInt64`\
**Default:** `256`\
**Tier:** Production\
Maximum number of keys in one coalesced row-mask update. The effective limit also respects `max_rows_in_set` in the background query settings. Values below 2 disable coalescing.
max_suspicious_broken_parts [#max_suspicious_broken_parts]
**Type:** `UInt64`\
**Default:** `100`\
**Tier:** Production\
If the number of broken parts in a single partition exceeds the `max_suspicious_broken_parts` value, automatic deletion is denied. Possible values: - Any positive integer.
max_suspicious_broken_parts_bytes [#max_suspicious_broken_parts_bytes]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Production\
Max size of all broken parts, if more - deny automatic deletion. Possible values: - Any positive integer.
max_uncompressed_bytes_in_patches [#max_uncompressed_bytes_in_patches]
**Type:** `UInt64`\
**Default:** `32212254720`\
**Tier:** Production\
The maximum uncompressed size of data in all patch parts in bytes. If amount of data in all patch parts exceeds this value, lightweight updates will be rejected. 0 - unlimited.
merge_max_block_size [#merge_max_block_size]
**Type:** `NonZeroUInt64`\
**Default:** `8192`\
**Tier:** Production\
The number of rows that are read from the merged parts into memory. Possible values: - Any positive integer. Merge reads rows from parts in blocks of `merge_max_block_size` rows, then merges and writes the result into a new part. The read block is placed in RAM, so `merge_max_block_size` affects the size of the RAM required for the merge. Thus, merges can consume a large amount of RAM for tables with very wide rows (if the average row size is 100kb, then when merging 10 parts, (100kb \* 10 \* 8192) = \~ 8GB of RAM). By decreasing `merge_max_block_size`, you can reduce the amount of RAM required for a merge but slow down a merge.
merge_max_block_size_bytes [#merge_max_block_size_bytes]
**Type:** `UInt64`\
**Default:** `10485760`\
**Tier:** Production\
How many bytes in blocks should be formed for merge operations. By default has the same value as `index_granularity_bytes`.
merge_max_bytes_to_prewarm_cache [#merge_max_bytes_to_prewarm_cache]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Production
Cloud. Maximal size of part (compact or packed) to prewarm cache during merge.
merge_max_dynamic_subcolumns_in_compact_part [#merge_max_dynamic_subcolumns_in_compact_part]
**Type:** `UInt64Auto`\
**Default:** `auto`\
**Tier:** Production\
The maximum number of dynamic subcolumns that can be created in every column in the Compact data part after merge. It allows to control the number of dynamic subcolumns in Compact part regardless of dynamic parameters specified in the data type. For example, if the table has a column with the JSON(max\_dynamic\_paths=1024) type and the setting merge\_max\_dynamic\_subcolumns\_in\_compact\_part is set to 128, after merge into the Compact data part number of dynamic paths will be decreased to 128 in this part and only 128 paths will be written as dynamic subcolumns.
merge_max_dynamic_subcolumns_in_wide_part [#merge_max_dynamic_subcolumns_in_wide_part]
**Type:** `UInt64Auto`\
**Default:** `auto`\
**Tier:** Production\
The maximum number of dynamic subcolumns that can be created in every column in the Wide data part after merge. It allows to reduce number of files created in Wide data part regardless of dynamic parameters specified in the data type. For example, if the table has a column with the JSON(max\_dynamic\_paths=1024) type and the setting merge\_max\_dynamic\_subcolumns\_in\_wide\_part is set to 128, after merge into the Wide data part number of dynamic paths will be decreased to 128 in this part and only 128 paths will be written as dynamic subcolumns.
merge_selecting_sleep_ms [#merge_selecting_sleep_ms]
**Type:** `UInt64`\
**Default:** `5000`\
**Tier:** Production\
Minimum time to wait before trying to select parts to merge again after no parts were selected. A lower setting will trigger selecting tasks in background\_schedule\_pool frequently which result in large amount of requests to zookeeper in large-scale clusters
merge_selecting_sleep_slowdown_factor [#merge_selecting_sleep_slowdown_factor]
**Type:** `Float`\
**Default:** `1.2`\
**Tier:** Production\
The sleep time for merge selecting task is multiplied by this factor when there's nothing to merge and divided when a merge was assigned
merge_selector_algorithm [#merge_selector_algorithm]
**Type:** `MergeSelectorAlgorithm`\
**Default:** `Simple`\
**Tier:** Experimental\
The algorithm to select parts for merges assignment
merge_selector_base [#merge_selector_base]
**Type:** `Float`\
**Default:** `5`\
**Tier:** Production\
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
merge_selector_blurry_base_scale_factor [#merge_selector_blurry_base_scale_factor]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Controls when the logic kicks in relatively to the number of parts in partition. The bigger the factor the more belated reaction will be.
merge_selector_enable_heuristic_to_lower_max_parts_to_merge_at_once [#merge_selector_enable_heuristic_to_lower_max_parts_to_merge_at_once]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enable heuristic for simple merge selector which will lower maximum limit for merge choice. By doing so number of concurrent merges will increase which can help with TOO\_MANY\_PARTS errors but at the same time this will increase the write amplification.
merge_selector_enable_heuristic_to_remove_small_parts_at_right [#merge_selector_enable_heuristic_to_remove_small_parts_at_right]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enable heuristic for selecting parts for merge which removes parts from right side of range, if their size is less than specified ratio (0.01) of sum\_size. Works for Simple and StochasticSimple merge selectors
merge_selector_heuristic_to_lower_max_parts_to_merge_at_once_exponent [#merge_selector_heuristic_to_lower_max_parts_to_merge_at_once_exponent]
**Type:** `UInt64`\
**Default:** `5`\
**Tier:** Experimental\
Controls the exponent value used in formulae building lowering curve. Lowering exponent will lower merge widths which will trigger increase in write amplification. The reverse is also true.
merge_selector_window_size [#merge_selector_window_size]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
How many parts to look at once.
merge_total_max_bytes_to_prewarm_cache [#merge_total_max_bytes_to_prewarm_cache]
**Type:** `UInt64`\
**Default:** `16106127360`\
**Tier:** Production
Cloud. Maximal size of parts in total to prewarm cache during merge.
merge_tree_clear_old_parts_interval_seconds [#merge_tree_clear_old_parts_interval_seconds]
**Type:** `UInt64`\
**Default:** `1`\
**Tier:** Production\
Sets the interval in seconds for RawTree to execute the cleanup of old parts, WALs, and mutations. Possible values: - Any positive integer.
merge_tree_clear_old_temporary_directories_interval_seconds [#merge_tree_clear_old_temporary_directories_interval_seconds]
**Type:** `UInt64`\
**Default:** `60`\
**Tier:** Production\
Sets the interval in seconds for RawTree to execute the cleanup of old temporary directories. Possible values: - Any positive integer.
merge_use_batch_sorting_queue [#merge_use_batch_sorting_queue]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Use the batch sorting queue to reduce per-row queue overhead when merging sorted streams. Only applies to merges that do not change rows, i.e. plain `MergeTree` (`Ordinary` merge mode). It has no effect on engines with merge-time semantics such as `ReplacingMergeTree`, `CollapsingMergeTree`, `SummingMergeTree`, `AggregatingMergeTree`, `CoalescingMergeTree`, `GraphiteMergeTree` and `VersionedCollapsingMergeTree`, which keep using the default queue regardless of this setting.
merge_with_recompression_ttl_timeout [#merge_with_recompression_ttl_timeout]
**Type:** `Int64`\
**Default:** `14400`\
**Tier:** Production\
Minimum delay in seconds before repeating a merge with recompression TTL.
merge_with_ttl_timeout [#merge_with_ttl_timeout]
**Type:** `Int64`\
**Default:** `14400`\
**Tier:** Production\
Minimum delay in seconds before repeating a merge with delete TTL.
merge_workload [#merge_workload]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
Used to regulate how resources are utilized and shared between merges and other workloads. Specified value is used as `workload` setting value for background merges of this table. If not specified (empty string), then server setting `merge_workload` is used instead. **See Also** - [Workload Scheduling](/concepts/features/configuration/server-config/workload-scheduling)
min_absolute_delay_to_close [#min_absolute_delay_to_close]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal absolute delay to close, stop serving requests and not return Ok during status check.
min_age_to_force_merge_on_partition_only [#min_age_to_force_merge_on_partition_only]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Whether `min_age_to_force_merge_seconds` should be applied only on the entire partition and not on subset. Possible values: - true, false
min_age_to_force_merge_seconds [#min_age_to_force_merge_seconds]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Merge parts if every part in the range is older than the value of `min_age_to_force_merge_seconds`. Possible values: - Positive integer.
min_bytes_for_full_part_storage [#min_bytes_for_full_part_storage]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal uncompressed size in bytes to use full type of storage for data part instead of packed
min_bytes_for_wide_part [#min_bytes_for_wide_part]
**Type:** `UInt64`\
**Default:** `10485760`\
**Tier:** Production\
Minimum number of bytes/rows in a data part that can be stored in `Wide` format. You can set one, both or none of these settings.
min_bytes_to_prewarm_caches [#min_bytes_to_prewarm_caches]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal size (uncompressed bytes) to prewarm mark cache and primary index cache for new parts
min_bytes_to_rebalance_partition_over_jbod [#min_bytes_to_rebalance_partition_over_jbod]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Sets minimal amount of bytes to enable balancing when distributing new big parts over volume disks [JBOD](https://en.wikipedia.org/wiki/Non-RAID_drive_architectures). Possible values: - Positive integer. - `0` — Balancing is disabled. **Usage** The value of the `min_bytes_to_rebalance_partition_over_jbod` setting should not be less than the value of the [max\_bytes\_to\_merge\_at\_max\_space\_in\_pool](/reference/settings/merge-tree-settings/max-bytes#max_bytes_to_merge_at_max_space_in_pool) / 1024. Otherwise, RawTree throws an exception.
min_columns_to_activate_adaptive_write_buffer [#min_columns_to_activate_adaptive_write_buffer]
**Type:** `UInt64`\
**Default:** `500`\
**Tier:** Production\
Allow to reduce memory usage for tables with lots of columns by using adaptive writer buffers. Possible values: - 0 - unlimited - 1 - always enabled
min_compress_block_size [#min_compress_block_size]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimum size of blocks of uncompressed data required for compression when writing the next mark. You can also specify this setting in the global settings (see [min\_compress\_block\_size](/reference/settings/merge-tree-settings/min#min_compress_block_size) setting). The value specified when the table is created overrides the global value for this setting.
min_compressed_bytes_to_fsync_after_fetch [#min_compressed_bytes_to_fsync_after_fetch]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal number of compressed bytes to do fsync for part after fetch (0 - disabled)
min_compressed_bytes_to_fsync_after_merge [#min_compressed_bytes_to_fsync_after_merge]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal number of compressed bytes to do fsync for part after merge (0 - disabled)
min_delay_to_insert_ms [#min_delay_to_insert_ms]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Min delay of inserting data into MergeTree table in milliseconds, if there are a lot of unmerged parts in single partition.
min_delay_to_mutate_ms [#min_delay_to_mutate_ms]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Min delay of mutating MergeTree table in milliseconds, if there are a lot of unfinished mutations
min_free_disk_bytes_to_perform_insert [#min_free_disk_bytes_to_perform_insert]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
The minimum number of bytes that should be free in disk space in order to insert data. If the number of available free bytes is less than `min_free_disk_bytes_to_perform_insert` then an exception is thrown and the insert is not executed. Note that this setting: - takes into account the `keep_free_space_bytes` setting. - does not take into account the amount of data that will be written by the `INSERT` operation. - is only checked if a positive (non-zero) number of bytes is specified Possible values: - Any positive integer. :::note If both `min_free_disk_bytes_to_perform_insert` and `min_free_disk_ratio_to_perform_insert` are specified, RawTree will count on the value that will allow to perform inserts on a bigger amount of free memory. :::
min_free_disk_ratio_to_perform_insert [#min_free_disk_ratio_to_perform_insert]
**Type:** `Float`\
**Default:** `0`\
**Tier:** Production\
The minimum free to total disk space ratio to perform an `INSERT`. Must be a floating point value between 0 and 1. Note that this setting: - takes into account the `keep_free_space_bytes` setting. - does not take into account the amount of data that will be written by the `INSERT` operation. - is only checked if a positive (non-zero) ratio is specified Possible values: - Float, 0.0 - 1.0 Note that if both `min_free_disk_ratio_to_perform_insert` and `min_free_disk_bytes_to_perform_insert` are specified, RawTree will count on the value that will allow to perform inserts on a bigger amount of free memory.
min_index_granularity_bytes [#min_index_granularity_bytes]
**Type:** `UInt64`\
**Default:** `1024`\
**Tier:** Production\
Min allowed size of data granules in bytes. To provide a safeguard against accidentally creating tables with very low `index_granularity_bytes`.
min_level_for_full_part_storage [#min_level_for_full_part_storage]
**Type:** `UInt32`\
**Default:** `0`\
**Tier:** Production\
Minimal part level to use full type of storage for data part instead of packed
min_level_for_wide_part [#min_level_for_wide_part]
**Type:** `UInt32`\
**Default:** `0`\
**Tier:** Production\
Minimal part level to create a data part in `Wide` format instead of `Compact`.
min_marks_to_honor_max_concurrent_queries [#min_marks_to_honor_max_concurrent_queries]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
The minimal number of marks read by the query for applying the [max\_concurrent\_queries](#max_concurrent_queries) setting. :::note Queries will still be limited by other `max_concurrent_queries` settings. ::: Possible values: - Positive integer. - `0` — Disabled (`max_concurrent_queries` limit applied to no queries). **Example** `xml 10 `
min_merge_bytes_to_use_direct_io [#min_merge_bytes_to_use_direct_io]
**Type:** `UInt64`\
**Default:** `10737418240`\
**Tier:** Production\
The minimum data volume for merge operation that is required for using direct I/O access to the storage disk. When merging data parts, RawTree calculates the total storage volume of all the data to be merged. If the volume exceeds `min_merge_bytes_to_use_direct_io` bytes, RawTree reads and writes the data to the storage disk using the direct I/O interface (`O_DIRECT` option). If `min_merge_bytes_to_use_direct_io = 0`, then direct I/O is disabled.
min_parts_to_merge_at_once [#min_parts_to_merge_at_once]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal amount of data parts which merge selector can pick to merge at once (expert level setting, don't change if you don't understand what it is doing). 0 - disabled. Works for Simple and StochasticSimple merge selectors.
min_relative_delay_to_close [#min_relative_delay_to_close]
**Type:** `UInt64`\
**Default:** `300`\
**Tier:** Production\
Minimal delay from other replicas to close, stop serving requests and not return Ok during status check.
min_relative_delay_to_measure [#min_relative_delay_to_measure]
**Type:** `UInt64`\
**Default:** `120`\
**Tier:** Production\
Calculate relative replica delay only if absolute delay is not less that this value.
min_replicated_logs_to_keep [#min_replicated_logs_to_keep]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production\
Keep about this number of last records in ZooKeeper log, even if they are obsolete. It doesn't affect work of tables: used only to diagnose ZooKeeper log before cleaning. Possible values: - Any positive integer.
min_rows_for_full_part_storage [#min_rows_for_full_part_storage]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal number of rows to use full type of storage for data part instead of packed
min_rows_for_wide_part [#min_rows_for_wide_part]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal number of rows to create a data part in `Wide` format instead of `Compact`.
min_rows_to_fsync_after_merge [#min_rows_to_fsync_after_merge]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal number of rows to do fsync for part after merge (0 - disabled)
mutation_workload [#mutation_workload]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production\
Used to regulate how resources are utilized and shared between mutations and other workloads. Specified value is used as `workload` setting value for background mutations of this table. If not specified (empty string), then server setting `mutation_workload` is used instead. **See Also** - [Workload Scheduling](/concepts/features/configuration/server-config/workload-scheduling)
non_replicated_deduplication_window [#non_replicated_deduplication_window]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
The number of the most recently inserted blocks in the non-replicated [MergeTree](/reference/engines/table-engines/mergetree-family/mergetree) table for which hash sums are stored to check for duplicates. Possible values: - Any positive integer. - `0` (disable deduplication). A deduplication mechanism is used, similar to replicated tables (see [replicated\_deduplication\_window](#replicated_deduplication_window) setting): the deduplication hash sum covers the whole inserted block. The hash sums are written to a local file on a disk rather than to ClickHouse Keeper.
notify_newest_block_number [#notify_newest_block_number]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
Notify newest block number to SharedJoin or SharedSet. Only in RawTree Cloud.
nullable_serialization_version [#nullable_serialization_version]
**Type:** `MergeTreeNullableSerializationVersion`\
**Default:** `basic`\
**Tier:** Production\
Controls the serialization method used for `Nullable(T)` columns. Possible values: - basic — Use the standard serialization for `Nullable(T)`. - allow\_sparse — Permit `Nullable(T)` to use sparse encoding.
number_of_free_entries_in_pool_to_execute_mutation [#number_of_free_entries_in_pool_to_execute_mutation]
**Type:** `UInt64`\
**Default:** `20`\
**Tier:** Production\
When there is less than specified number of free entries in pool, do not execute part mutations. This is to leave free threads for regular merges and to avoid "Too many parts" errors. Possible values: - Any positive integer. **Usage** The value of the `number_of_free_entries_in_pool_to_execute_mutation` setting must not exceed the product of [`background_pool_size`](/reference/settings/server-settings/settings/background#background_pool_size) and [`background_merges_mutations_concurrency_ratio`](/reference/settings/server-settings/settings/background-merges#background_merges_mutations_concurrency_ratio). Otherwise, RawTree will throw an exception.
number_of_free_entries_in_pool_to_execute_optimize_entire_partition [#number_of_free_entries_in_pool_to_execute_optimize_entire_partition]
**Type:** `UInt64`\
**Default:** `25`\
**Tier:** Production\
When there is less than specified number of free entries in pool, do not execute optimizing entire partition in the background (this task generated when set `min_age_to_force_merge_seconds` and enable `min_age_to_force_merge_on_partition_only`). This is to leave free threads for regular merges and avoid "Too many parts". Possible values: - Positive integer. The value of the `number_of_free_entries_in_pool_to_execute_optimize_entire_partition` setting should be less than the value of the [background\_pool\_size](/reference/settings/server-settings/settings/background#background_pool_size) \* [background\_merges\_mutations\_concurrency\_ratio](/reference/settings/server-settings/settings/background-merges#background_merges_mutations_concurrency_ratio). Otherwise, RawTree throws an exception.
number_of_free_entries_in_pool_to_lower_max_size_of_merge [#number_of_free_entries_in_pool_to_lower_max_size_of_merge]
**Type:** `UInt64`\
**Default:** `8`\
**Tier:** Production\
When there is less than the specified number of free entries in pool (or replicated queue), start to lower maximum size of merge to process (or to put in queue). This is to allow small merges to process - not filling the pool with long running merges. Possible values: - Any positive integer.
number_of_mutations_to_delay [#number_of_mutations_to_delay]
**Type:** `UInt64`\
**Default:** `500`\
**Tier:** Production\
If table has at least that many unfinished mutations, artificially slow down mutations of table. Disabled if set to 0
number_of_mutations_to_throw [#number_of_mutations_to_throw]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
If table has at least that many unfinished mutations, throw 'Too many mutations' exception. Disabled if set to 0
number_of_partitions_to_consider_for_merge [#number_of_partitions_to_consider_for_merge]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production
Cloud. Up to top N partitions which we will consider for merge. Partitions picked in a random weighted way where weight is amount of data parts which can be merged in this partition.
object_serialization_version [#object_serialization_version]
**Type:** `MergeTreeObjectSerializationVersion`\
**Default:** `v3`\
**Tier:** Production\
Serialization version for JSON data type. Required for compatibility. Possible values: - `v1` - `v2` - `v3` Only version `v3` supports changing the shared data serialization version.
object_shared_data_buckets_for_compact_part [#object_shared_data_buckets_for_compact_part]
**Type:** `NonZeroUInt64`\
**Default:** `8`\
**Tier:** Production\
The number of buckets for JSON shared data serialization in Compact parts. Works with `map_with_buckets` and `advanced` shared data serializations. The maximum allowed value is 256.
object_shared_data_buckets_for_wide_part [#object_shared_data_buckets_for_wide_part]
**Type:** `NonZeroUInt64`\
**Default:** `32`\
**Tier:** Production\
The number of buckets for JSON shared data serialization in Wide parts. Works with `map_with_buckets` and `advanced` shared data serializations. The maximum allowed value is 256.
object_shared_data_serialization_version [#object_shared_data_serialization_version]
**Type:** `MergeTreeObjectSharedDataSerializationVersion`\
**Default:** `advanced`\
**Tier:** Production\
Serialization version for shared data inside JSON data type. Possible values: - `map` - store shared data as `Map(String, String)` - `map_with_buckets` - store shared data as several separate `Map(String, String)` columns. Using buckets improves reading individual paths from shared data. - `advanced` - special serialization of shared data designed to significantly improve reading of individual paths from shared data. Note that this serialization increases the shared data storage size on disk because we store a lot of additional information. The number of buckets for `map_with_buckets` and `advanced` serializations is determined by settings [object\_shared\_data\_buckets\_for\_compact\_part](#object_shared_data_buckets_for_compact_part)/[object\_shared\_data\_buckets\_for\_wide\_part](#object_shared_data_buckets_for_wide_part).
object_shared_data_serialization_version_for_zero_level_parts [#object_shared_data_serialization_version_for_zero_level_parts]
**Type:** `MergeTreeObjectSharedDataSerializationVersion`\
**Default:** `map_with_buckets`\
**Tier:** Production\
This setting allows to specify different serialization version of the shared data inside JSON type for zero level parts that are created during inserts. It's recommended not to use `advanced` shared data serialization for zero level parts because it can increase the insertion time significantly.
old_parts_lifetime [#old_parts_lifetime]
**Type:** `Seconds`\
**Default:** `480`\
**Tier:** Production\
The time (in seconds) of storing inactive parts to protect against data loss during spontaneous server reboots. Possible values: - Any positive integer. After merging several parts into a new part, RawTree marks the original parts as inactive and deletes them only after `old_parts_lifetime` seconds. Inactive parts are removed if they are not used by current queries, i.e. if the `refcount` of the part is 1. `fsync` is not called for new parts, so for some time new parts exist only in the server's RAM (OS cache). If the server is rebooted spontaneously, new parts can be lost or damaged. To protect data inactive parts are not deleted immediately. During startup RawTree checks the integrity of the parts. If the merged part is damaged RawTree returns the inactive parts to the active list, and later merges them again. Then the damaged part is renamed (the `broken_` prefix is added) and moved to the `detached` folder. If the merged part is not damaged, then the original inactive parts are renamed (the `ignored_` prefix is added) and moved to the `detached` folder. The default `dirty_expire_centisecs` value (a Linux kernel setting) is 30 seconds (the maximum time that written data is stored only in RAM), but under heavy loads on the disk system data can be written much later. Experimentally, a value of 480 seconds was chosen for `old_parts_lifetime`, during which a new part is guaranteed to be written to disk.
optimize_row_order [#optimize_row_order]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Controls if the row order should be optimized during inserts to improve the compressability of the newly inserted table part. Only has an effect for ordinary MergeTree-engine tables. Does nothing for specialized MergeTree engine tables (e.g. CollapsingMergeTree). MergeTree tables are (optionally) compressed using [compression codecs](/reference/statements/create/table#column_compression_codec). Generic compression codecs such as LZ4 and ZSTD achieve maximum compression rates if the data exposes patterns. Long runs of the same value typically compress very well. If this setting is enabled, RawTree attempts to store the data in newly inserted parts in a row order that minimizes the number of equal-value runs across the columns of the new table part. In other words, a small number of equal-value runs mean that individual runs are long and compress well. Finding the optimal row order is computationally infeasible (NP hard). Therefore, RawTree uses a heuristics to quickly find a row order which still improves compression rates over the original row order. Heuristics for finding a row order
It is generally possible to shuffle the rows of a table (or table part) freely as SQL considers the same table (table part) in different row order equivalent. This freedom of shuffling rows is restricted when a primary key is defined for the table. In RawTree, a primary key `C1, C2, ..., CN` enforces that the table rows are sorted by columns `C1`, `C2`, ... `Cn` ([clustered index](https://en.wikipedia.org/wiki/Database_index#Clustered)). As a result, rows can only be shuffled within "equivalence classes" of row, i.e. rows which have the same values in their primary key columns. The intuition is that primary keys with high-cardinality, e.g. primary keys involving a `DateTime64` timestamp column, lead to many small equivalence classes. Likewise, tables with a low-cardinality primary key, create few and large equivalence classes. A table with no primary key represents the extreme case of a single equivalence class which spans all rows. The fewer and the larger the equivalence classes are, the higher the degree of freedom when re-shuffling rows. The heuristics applied to find the best row order within each equivalence class is suggested by D. Lemire, O. Kaser in [Reordering columns for smaller indexes](https://doi.org/10.1016/j.ins.2011.02.002) and based on sorting the rows within each equivalence class by ascending cardinality of the non-primary key columns. It performs three steps: 1. Find all equivalence classes based on the row values in primary key columns. 2. For each equivalence class, calculate (usually estimate) the cardinalities of the non-primary-key columns. 3. For each equivalence class, sort the rows in order of ascending non-primary-key column cardinality. If enabled, insert operations incur additional CPU costs to analyze and optimize the row order of the new data. INSERTs are expected to take 30-50% longer depending on the data characteristics. Compression rates of LZ4 or ZSTD improve on average by 20-40%. This setting works best for tables with no primary key or a low-cardinality primary key, i.e. a table with only few distinct primary key values. High-cardinality primary keys, e.g. involving timestamp columns of type `DateTime64`, are not expected to benefit from this setting.
pack_object_columns_on_insert [#pack_object_columns_on_insert]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled, a freshly INSERTED Wide part with adaptive granularity writes every `Object`/`JSON` column directly in packed layout — one bundle pair (`.raw_packed.bin` + `.raw_packed.cmrk4`) grouping the column's typed-path, dynamic-path, `shared_data` and `ObjectStructure` units — instead of the default per-substream `.bin`/`.mrk2` files. This makes inserted parts "born packed", avoiding the unpacked-to-packed conversion on the first vertical merge. Unlike `subvertical_merge_pack_object_columns`, which is merge-only, this setting affects the insert write path. Only applies to Wide parts with adaptive granularity; other parts keep the per-substream layout.
packed_skip_index_max_bytes [#packed_skip_index_max_bytes]
**Type:** `UInt64`\
**Default:** `1048576`\
**Tier:** Beta\
Threshold (serialized on-disk bytes, i.e. after the substream's compression and hashing chain) below which a skip-index substream is bundled into a single `skp_idx.packed` archive per part instead of being written as a separate `skp_idx_.idx2` / `.mrk2` file. Substreams larger than this stay in the legacy per-file layout. The decision is made independently per substream at write time, so a single part can have small indices (e.g. `minmax`) packed and large ones (e.g. a heavy `bloom_filter`) per-file. Set to 0 to disable packing entirely. Defaults to 1 MiB, which bundles the typically small skip indices into one archive per part and cuts the object count (and read requests) on object storage, while leaving genuinely large substreams in the per-file layout. Each skip-index substream actually consists of a data file and a marks file; both buffer in memory up to the threshold before the spill decision is made. So peak memory while writing scales with `2 * packed_skip_index_max_bytes * (number of substreams that stay below the threshold)`. Full-text indices are not supported by this setting and are never packed. Packing reduces inode pressure when many skip indices are defined on a table (for example with `add_minmax_index_for_numeric_columns`). The on-disk format is self-describing: readers detect `skp_idx.packed` and serve packed substreams from inside it transparently. Changing this setting affects newly written parts only; existing parts retain whatever layout they had at write time.
part_minmax_index_columns [#part_minmax_index_columns]
**Type:** `MergeTreePartMinMaxIndexColumns`\
**Default:** `partition_key_only`\
**Tier:** Production\
Selects which columns the per-part min-max index covers. Each value enables an additional group of columns on top of the previous one. Possible values: - `partition_key_only` — only the partition-key columns are tracked. - `with_block_number_offset` — partition-key columns plus the persisted `_block_number` and `_block_offset` virtual columns. Enables part-level pruning by these columns.
part_moves_between_shards_delay_seconds [#part_moves_between_shards_delay_seconds]
**Type:** `UInt64`\
**Default:** `30`\
**Tier:** Experimental\
Time to wait before/after moving parts between shards.
part_moves_between_shards_enable [#part_moves_between_shards_enable]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Experimental\
Experimental/Incomplete feature to move parts between shards. Does not take into account sharding expressions.
parts_to_delay_insert [#parts_to_delay_insert]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production\
If the number of active parts in a single partition exceeds the `parts_to_delay_insert` value, an `INSERT` is artificially slowed down. Possible values: - Any positive integer. RawTree artificially executes `INSERT` longer (adds 'sleep') so that the background merge process can merge parts faster than they are added.
parts_to_throw_insert [#parts_to_throw_insert]
**Type:** `UInt64`\
**Default:** `3000`\
**Tier:** Production\
If the number of active parts in a single partition exceeds the `parts_to_throw_insert` value, `INSERT` is interrupted with the `Too many parts (N). Merges are processing significantly slower than inserts` exception. Possible values: - Any positive integer. To achieve maximum performance of `SELECT` queries, it is necessary to minimize the number of parts processed, see [Merge Tree](/resources/develop-contribute/introduction/architecture#merge-tree). Prior to version 23.6 this setting was set to 300. You can set a higher different value, it will reduce the probability of the `Too many parts` error, but at the same time `SELECT` performance might degrade. Also in case of a merge issue (for example, due to insufficient disk space) you will notice it later than you would with the original 300.
patch_parts_version [#patch_parts_version]
**Type:** `MergeTreePatchPartsVersion`\
**Default:** `v2`\
**Tier:** Production\
On-disk serialization version for patch parts produced by lightweight UPDATE queries. Possible values: - `v1` - legacy format: patch parts contain `_part, _part_offset` system columns and are sorted by `(_part, _part_offset)`. In the worst case, memory usage during apply is bounded by the size of the whole patch part. - `v2` - patch parts carry the main table's sort-key columns and are sorted by `(sorting_key_columns..., _block_number, _block_offset)`. Memory usage during apply is bounded by the largest equal-sort-key run. Old-format patches on disk remain readable regardless of this setting.
prefer_fetch_merged_part_size_threshold [#prefer_fetch_merged_part_size_threshold]
**Type:** `UInt64`\
**Default:** `10737418240`\
**Tier:** Production\
If the sum of the size of parts exceeds this threshold and the time since a replication log entry creation is greater than `prefer_fetch_merged_part_time_threshold`, then prefer fetching merged part from a replica instead of doing merge locally. This is to speed up very long merges. Possible values: - Any positive integer.
prefer_fetch_merged_part_time_threshold [#prefer_fetch_merged_part_time_threshold]
**Type:** `Seconds`\
**Default:** `3600`\
**Tier:** Production\
If the time passed since a replication log (RawTree Keeper or ZooKeeper) entry creation exceeds this threshold, and the sum of the size of parts is greater than `prefer_fetch_merged_part_size_threshold`, then prefer fetching merged part from a replica instead of doing merge locally. This is to speed up very long merges. Possible values: - Any positive integer.
prewarm_mark_cache [#prewarm_mark_cache]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If true mark cache will be prewarmed by saving marks to mark cache on inserts, merges, fetches and on startup of server
prewarm_primary_key_cache [#prewarm_primary_key_cache]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If true primary index cache will be prewarmed by saving marks to mark cache on inserts, merges, fetches and on startup of server
primary_key_compress_block_size [#primary_key_compress_block_size]
**Type:** `NonZeroUInt64`\
**Default:** `65536`\
**Tier:** Production\
Primary compress block size, the actual size of the block to compress.
primary_key_compression_codec [#primary_key_compression_codec]
**Type:** `String`\
**Default:** `ZSTD(3)`\
**Tier:** Production\
Compression encoding used by primary, primary key is small enough and cached, so the default compression is ZSTD(3).
primary_key_lazy_load [#primary_key_lazy_load]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Load primary key in memory on first use instead of on table initialization. This can save memory in the presence of a large number of tables.
primary_key_ratio_of_unique_prefix_values_to_skip_suffix_columns [#primary_key_ratio_of_unique_prefix_values_to_skip_suffix_columns]
**Type:** `Float`\
**Default:** `0.9`\
**Tier:** Production\
If the value of a column of the primary key in data part changes at least in this ratio of times, skip loading next columns in memory. This allows to save memory usage by not loading useless columns of the primary key.
propagate_types_serialization_versions_to_nested_types [#propagate_types_serialization_versions_to_nested_types]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true, serialization versions like string\_serialization\_version will be propagated inside nested types like Array/Map/Nullable/JSON/etc. If disabled, the serialization version will take affect only to top-level columns of this type and Tuple el
ratio_of_defaults_for_sparse_serialization [#ratio_of_defaults_for_sparse_serialization]
**Type:** `Float`\
**Default:** `0.9375`\
**Tier:** Production\
Minimal ratio of the number of *default* values to the number of *all* values in a column. Setting this value causes the column to be stored using sparse serializations. If a column is sparse (contains mostly zeros), RawTree can encode it in a sparse format and automatically optimize calculations - the data does not require full decompression during queries. To enable this sparse serialization, define the `ratio_of_defaults_for_sparse_serialization` setting to be less than 1.0. If the value is greater than or equal to 1.0, then the columns will be always written using the normal full serialization. Possible values: - Float between `0` and `1` to enable sparse serialization - `1.0` (or greater) if you do not want to use sparse serialization **Example** Notice the `s` column in the following table is an empty string for 95% of the rows. In `my_regular_table` we do not use sparse serialization, and in `my_sparse_table` we set `ratio_of_defaults_for_sparse_serialization` to 0.95: ``sql CREATE TABLE my_regular_table ( `id` UInt64, `s` String ) ENGINE = MergeTree ORDER BY id; INSERT INTO my_regular_table SELECT number AS id, number % 20 = 0 ? toString(number): '' AS s FROM numbers(10000000); CREATE TABLE my_sparse_table ( `id` UInt64, `s` String ) ENGINE = MergeTree ORDER BY id SETTINGS ratio_of_defaults_for_sparse_serialization = 0.95; INSERT INTO my_sparse_table SELECT number, number % 20 = 0 ? toString(number): '' FROM numbers(10000000); `` Notice the `s` column in `my_sparse_table` uses less storage space on disk: `sql SELECT table, name, data_compressed_bytes, data_uncompressed_bytes FROM system.columns WHERE table LIKE 'my_%_table'; ` `response ┌─table────────────┬─name─┬─data_compressed_bytes─┬─data_uncompressed_bytes─┐ │ my_regular_table │ id │ 37790741 │ 75488328 │ │ my_regular_table │ s │ 2451377 │ 12683106 │ │ my_sparse_table │ id │ 37790741 │ 75488328 │ │ my_sparse_table │ s │ 2283454 │ 9855751 │ └──────────────────┴──────┴───────────────────────┴─────────────────────────┘ ` You can verify if a column is using the sparse encoding by viewing the `serialization_kind` column of the `system.parts_columns` table: `sql SELECT column, serialization_kind FROM system.parts_columns WHERE table LIKE 'my_sparse_table'; ` You can see which parts of `s` were stored using the sparse serialization: `response ┌─column─┬─serialization_kind─┐ │ id │ Default │ │ s │ Default │ │ id │ Default │ │ s │ Default │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ │ id │ Default │ │ s │ Sparse │ └────────┴────────────────────┘ `
reduce_blocking_parts_sleep_ms [#reduce_blocking_parts_sleep_ms]
**Type:** `UInt64`\
**Default:** `5000`\
**Tier:** Production
Cloud. Minimum time to wait before trying to reduce blocking parts again after no ranges were dropped/replaced. A lower setting will trigger tasks in background\_schedule\_pool frequently which results in large amount of requests to zookeeper in large-scale clusters
refresh_parts_interval [#refresh_parts_interval]
**Type:** `Seconds`\
**Default:** `0`\
**Tier:** Production\
If it is greater than zero - refresh the list of data parts from the underlying filesystem to check if the data was updated under the hood. It can be set only if the table is located on readonly disks (which means that this is a readonly replica, while data is being written by another replica).
refresh_statistics_interval [#refresh_statistics_interval]
**Type:** `Seconds`\
**Default:** `300`\
**Tier:** Production\
The interval of refreshing statistics cache in seconds. If it is set to zero, the refreshing will be disabled.
remote_fs_execute_merges_on_single_replica_time_threshold [#remote_fs_execute_merges_on_single_replica_time_threshold]
**Type:** `Seconds`\
**Default:** `10800`\
**Tier:** Production\
When this setting has a value greater than zero only a single replica starts the merge immediately if merged part on shared storage. :::note Zero-copy replication is not ready for production Zero-copy replication is disabled by default in RawTree version 22.8 and higher. This feature is not recommended for production use. ::: Possible values: - Any positive integer.
remote_fs_zero_copy_path_compatible_mode [#remote_fs_zero_copy_path_compatible_mode]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Experimental\
Run zero-copy in compatible mode during conversion process.
remote_fs_zero_copy_zookeeper_path [#remote_fs_zero_copy_zookeeper_path]
**Type:** `String`\
**Default:** `/rawtree/zero_copy`\
**Tier:** Experimental\
ZooKeeper path for zero-copy table-independent info.
remove_empty_parts [#remove_empty_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Remove empty parts after they were pruned by TTL, mutation, or collapsing merge algorithm.
remove_rolled_back_parts_immediately [#remove_rolled_back_parts_immediately]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Experimental\
Setting for an incomplete experimental feature.
remove_unused_patch_parts [#remove_unused_patch_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Remove in background patch parts which are applied for all active parts.
replace_long_file_name_to_hash [#replace_long_file_name_to_hash]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If the file name for column is too long (more than 'max\_file\_name\_length' bytes) replace it to SipHash128
replacing_enable [#replacing_enable]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production
For raw MergeTree tables with an explicit sorting key, remove rows with the same sorting key, like `ReplacingMergeTree`: duplicates are removed on INSERT within one inserted block (with `optimize_on_insert`), by background merges and by `SELECT ... FINAL`. Without `replacing_version_column`, the last inserted row is kept; of the rows with the same key in one part, the last one in the part. Each part records whether its rows are unique by its sorting key (`is_unique_by_sorting_key` in `system.parts`): a part written before this setting was enabled is deduplicated when it is merged, and `OPTIMIZE ... FINAL` and `min_age_to_force_merge_seconds` merge it even if it is the only part of its partition. Rows with a NULL value in the sorting key, e.g. the rows that do not have a JSON path of the key, have the same key and are removed as duplicates of each other. Has no effect while the table has no explicit sorting key.
replacing_is_deleted_column [#replacing_is_deleted_column]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production
For raw MergeTree tables with `replacing_enable`, the name of a `UInt8` column: if the kept row has the value 1, the row is deleted, like the `is_deleted` parameter of `ReplacingMergeTree`. Requires `replacing_version_column`.
replacing_version_column [#replacing_version_column]
**Type:** `String`\
**Default:** \`\`\
**Tier:** Production
For raw MergeTree tables with `replacing_enable`, the name of a column of type `UInt*`, `Date`, `DateTime` or `DateTime64`: of the rows with the same sorting key, the row with the maximum value is kept, like the `ver` parameter of `ReplacingMergeTree`.
replicated_can_become_leader [#replicated_can_become_leader]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true, replicated tables replicas on this node will try to acquire leadership. Possible values: - `true` - `false`
replicated_deduplication_window [#replicated_deduplication_window]
**Type:** `UInt64`\
**Default:** `10000`\
**Tier:** Production\
The number of most recently inserted blocks for which RawTree Keeper stores hash sums to check for duplicates. Possible values: - Any positive integer. - 0 (disable deduplication) For [insert deduplication](/reference/engines/table-engines/mergetree-family/replication), when writing into replicated tables, RawTree writes deduplication hash sums into RawTree Keeper. Hash sums are stored only for the most recent `replicated_deduplication_window` blocks. The oldest hash sums are removed from RawTree Keeper. The hash sum covers the whole inserted block, so an insert is deduplicated only when its entire data matches a previous insert (a retry), not per individual part. A large number for `replicated_deduplication_window` slows down `Inserts` because more entries need to be compared.
replicated_deduplication_window_for_async_inserts [#replicated_deduplication_window_for_async_inserts]
**Type:** `UInt64`\
**Default:** `10000`\
**Tier:** Production\
Legacy setting retained for mixed-version rolling upgrades. New inserts deduplicate with the unified hash governed by `replicated_deduplication_window`; this setting now only bounds how many legacy async-insert hashes are kept in the `async_blocks` directory in ClickHouse Keeper, which is still written by older replicas during a rolling upgrade and cleaned up by the current leader. A value of `0` retains none of them — the next cleanup pass removes every entry — so keep the default (or a larger value) to preserve the legacy async deduplication window during the upgrade.
replicated_deduplication_window_seconds [#replicated_deduplication_window_seconds]
**Type:** `UInt64`\
**Default:** `3600`\
**Tier:** Production\
The number of seconds after which the hash sums of the inserted blocks are removed from ClickHouse Keeper. Possible values: - Any positive integer. Similar to [replicated\_deduplication\_window](#replicated_deduplication_window), `replicated_deduplication_window_seconds` specifies how long to store hash sums of blocks for insert deduplication. Hash sums older than `replicated_deduplication_window_seconds` are removed from ClickHouse Keeper, even if they are less than ` replicated_deduplication_window`. The time is relative to the time of the most recent record, not to the wall time. If it's the only record it will be stored forever.
replicated_deduplication_window_seconds_for_async_inserts [#replicated_deduplication_window_seconds_for_async_inserts]
**Type:** `UInt64`\
**Default:** `604800`\
**Tier:** Production\
Legacy setting retained for mixed-version rolling upgrades. Together with `replicated_deduplication_window_for_async_inserts` it bounds how long legacy async-insert hashes are kept in the `async_blocks` directory in ClickHouse Keeper (written by older replicas during a rolling upgrade and cleaned up by the current leader). New inserts use `replicated_deduplication_window_seconds`.
replicated_fetches_min_part_level [#replicated_fetches_min_part_level]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimum part level to fetch from other replicas. Parts with level below this threshold are postponed (kept in the replication queue and re-evaluated each scheduling cycle, not permanently skipped). Use 1 to postpone fetching level-0 (unmerged) parts, reducing replication overhead during heavy ingestion. Default: 0 (fetch all parts regardless of level).
replicated_fetches_min_part_level_timeout_seconds [#replicated_fetches_min_part_level_timeout_seconds]
**Type:** `UInt64`\
**Default:** `300`\
**Tier:** Production\
Timeout in seconds after which a part below replicated\_fetches\_min\_part\_level will be fetched anyway. Use 0 to disable the timeout (parts below the minimum level are postponed indefinitely until merged). Default: 300 (force fetch after 5 minutes).
replicated_max_mutations_in_one_entry [#replicated_max_mutations_in_one_entry]
**Type:** `UInt64`\
**Default:** `10000`\
**Tier:** Production\
Max number of mutation commands that can be merged together and executed in one MUTATE\_PART entry (0 means unlimited)
replicated_max_ratio_of_wrong_parts [#replicated_max_ratio_of_wrong_parts]
**Type:** `Float`\
**Default:** `0.5`\
**Tier:** Production\
If the ratio of wrong parts to total number of parts is less than this - allow to start. Possible values: - Float, 0.0 - 1.0
search_orphaned_parts_disks [#search_orphaned_parts_disks]
**Type:** `SearchOrphanedPartsDisks`\
**Default:** `any`\
**Tier:** Production\
RawTree scans all disks for orphaned parts upon any ATTACH or CREATE table in order to not allow to miss data parts at undefined (not included in policy) disks. Orphaned parts originates from potentially unsafe storage reconfiguration, e.g. if a disk was excluded from storage policy. This setting limits scope of disks to search by traits of the disks. Possible values: - any - scope is not limited. - local - scope is limited by local disks . - none - empty scope, do not search
serialization_info_version [#serialization_info_version]
**Type:** `MergeTreeSerializationInfoVersion`\
**Default:** `with_types`\
**Tier:** Production\
Serialization info version used when writing `serialization.json`. This setting is required for compatibility during cluster upgrades. Possible values: - `basic` - Basic format. - `with_types` - Format with additional `types_serialization_versions` field, allowing per-type serialization versions. This makes settings like `string_serialization_version` effective. During rolling upgrades, set this to `basic` so that new servers produce data parts compatible with old servers. After the upgrade completes, switch to `WITH_TYPES` to enable per-type serialization versions.
share_nested_offsets [#share_nested_offsets]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled (default), Array columns with dotted names that share a common prefix (e.g. n.a and n.b) are treated as part of a Nested structure: they share a single offsets file on disk (e.g. n.size0), and their array sizes are validated to be equal during INSERT. When disabled, each Array column gets its own independent offset file, dotted names carry no special semantics, and a scalar column may coexist with dotted Array columns sharing the same prefix (e.g. n UInt32 alongside n.a Array(String)). This setting is immutable after table creation.
shared_merge_tree_activate_coordinated_merges_tasks [#shared_merge_tree_activate_coordinated_merges_tasks]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Activates rescheduling of coordinated merges tasks. It can be useful even when shared\_merge\_tree\_enable\_coordinated\_merges=0 because this will populate merge coordinator statistics and help with cold start.
shared_merge_tree_blobs_list_inline_file_max_bytes [#shared_merge_tree_blobs_list_inline_file_max_bytes]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Store files of a blob-list part that are at most this many bytes long (e.g. count.txt, metadata\_version.txt, minmax indexes) directly inside the consolidated blobs.list file instead of a separate object-storage blob, saving one blob write and read per small file. 0 disables inlining. Takes effect only for parts using the blob-list storage (see shared\_merge\_tree\_use\_blobs\_list\_for\_parts). Only available in ClickHouse Cloud.
shared_merge_tree_create_per_replica_metadata_nodes [#shared_merge_tree_create_per_replica_metadata_nodes]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production
Enables creation of per-replica /metadata and /columns nodes in ZooKeeper. Cloud
shared_merge_tree_disable_merges_and_mutations_assignment [#shared_merge_tree_disable_merges_and_mutations_assignment]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production
Stop merges assignment for shared merge tree. Cloud
shared_merge_tree_empty_partition_lifetime [#shared_merge_tree_empty_partition_lifetime]
**Type:** `Seconds`\
**Default:** `86400`\
**Tier:** Production\
How many seconds partition will be stored in keeper if it has no parts.
shared_merge_tree_enable_automatic_empty_partitions_cleanup [#shared_merge_tree_enable_automatic_empty_partitions_cleanup]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enabled cleanup of Keeper entries of empty partition.
shared_merge_tree_enable_coordinated_merges [#shared_merge_tree_enable_coordinated_merges]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables coordinated merges strategy
shared_merge_tree_enable_keeper_parts_extra_data [#shared_merge_tree_enable_keeper_parts_extra_data]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables writing attributes into virtual parts and committing blocks in keeper
shared_merge_tree_enable_outdated_parts_check [#shared_merge_tree_enable_outdated_parts_check]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
Enable outdated parts check. Cloud
shared_merge_tree_idle_parts_update_seconds [#shared_merge_tree_idle_parts_update_seconds]
**Type:** `UInt64`\
**Default:** `3600`\
**Tier:** Production
Interval in seconds for parts update without being triggered by ZooKeeper watch in the shared merge tree. Cloud
shared_merge_tree_inactive_replica_cutoff_seconds [#shared_merge_tree_inactive_replica_cutoff_seconds]
**Type:** `Seconds`\
**Default:** `0`\
**Tier:** Production\
For how long an inactive replica is still taken into account by the background cleanup (`OutdatedPartsQuorumThread`): while a replica has been inactive for less than this many seconds, it still holds back removal of outdated parts and truncation of finished mutations. A value of 0 means use the default of two ZooKeeper session timeouts. Only in ClickHouse Cloud
shared_merge_tree_initial_parts_update_backoff_ms [#shared_merge_tree_initial_parts_update_backoff_ms]
**Type:** `UInt64`\
**Default:** `50`\
**Tier:** Production
Initial backoff for parts update. Cloud
shared_merge_tree_interserver_http_connection_timeout_ms [#shared_merge_tree_interserver_http_connection_timeout_ms]
**Type:** `UInt64`\
**Default:** `100`\
**Tier:** Production
Timeouts for interserver HTTP connection. Cloud
shared_merge_tree_interserver_http_timeout_ms [#shared_merge_tree_interserver_http_timeout_ms]
**Type:** `UInt64`\
**Default:** `10000`\
**Tier:** Production
Timeouts for interserver HTTP communication. Cloud
shared_merge_tree_leader_update_period_random_add_seconds [#shared_merge_tree_leader_update_period_random_add_seconds]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production
Add uniformly distributed value from 0 to x seconds to shared\_merge\_tree\_leader\_update\_period to avoid thundering herd effect. Cloud
shared_merge_tree_leader_update_period_seconds [#shared_merge_tree_leader_update_period_seconds]
**Type:** `UInt64`\
**Default:** `30`\
**Tier:** Production
Maximum period to recheck leadership for parts update. Cloud
shared_merge_tree_max_outdated_parts_to_process_at_once [#shared_merge_tree_max_outdated_parts_to_process_at_once]
**Type:** `UInt64`\
**Default:** `1000`\
**Tier:** Production
Maximum amount of outdated parts leader will try to confirm for removal at one HTTP request. Cloud.
shared_merge_tree_max_parts_update_backoff_ms [#shared_merge_tree_max_parts_update_backoff_ms]
**Type:** `UInt64`\
**Default:** `5000`\
**Tier:** Production
Max backoff for parts update. Cloud
shared_merge_tree_max_parts_update_leaders_in_total [#shared_merge_tree_max_parts_update_leaders_in_total]
**Type:** `UInt64`\
**Default:** `6`\
**Tier:** Production
Maximum number of parts update leaders. Cloud
shared_merge_tree_max_parts_update_leaders_per_az [#shared_merge_tree_max_parts_update_leaders_per_az]
**Type:** `UInt64`\
**Default:** `2`\
**Tier:** Production
Maximum number of parts update leaders. Cloud
shared_merge_tree_max_replicas_for_parts_deletion [#shared_merge_tree_max_replicas_for_parts_deletion]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production
Max replicas which will participate in parts deletion (killer thread). Cloud
shared_merge_tree_max_replicas_to_merge_parts_for_each_parts_range [#shared_merge_tree_max_replicas_to_merge_parts_for_each_parts_range]
**Type:** `UInt64`\
**Default:** `5`\
**Tier:** Production
Max replicas which will try to assign potentially conflicting merges (allow to avoid redundant conflicts in merges assignment). 0 means disabled. Cloud
shared_merge_tree_max_suspicious_broken_parts [#shared_merge_tree_max_suspicious_broken_parts]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Max broken parts for SMT, if more - deny automatic detach.
shared_merge_tree_max_suspicious_broken_parts_bytes [#shared_merge_tree_max_suspicious_broken_parts_bytes]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Max size of all broken parts for SMT, if more - deny automatic detach.
shared_merge_tree_memo_ids_remove_timeout_seconds [#shared_merge_tree_memo_ids_remove_timeout_seconds]
**Type:** `Int64`\
**Default:** `1800`\
**Tier:** Production
How long we store insert memoization ids to avoid wrong actions during insert retries. Cloud
shared_merge_tree_merge_coordinator_distribution_algorithm [#shared_merge_tree_merge_coordinator_distribution_algorithm]
**Type:** `MergeCoordinatorDistributionAlgorithm`\
**Default:** `sainte_lague`\
**Tier:** Production\
The algorithm used by the merge coordinator thread to distribute merges between replicas. Possible values: - `water_filling` - `sainte_lague`
shared_merge_tree_merge_coordinator_election_check_period_ms [#shared_merge_tree_merge_coordinator_election_check_period_ms]
**Type:** `Milliseconds`\
**Default:** `30000`\
**Tier:** Production\
Time between runs of merge coordinator election thread
shared_merge_tree_merge_coordinator_factor [#shared_merge_tree_merge_coordinator_factor]
**Type:** `Float`\
**Default:** `1.1`\
**Tier:** Production\
Time changing factor for delay of coordinator thread
shared_merge_tree_merge_coordinator_fetch_fresh_metadata_period_ms [#shared_merge_tree_merge_coordinator_fetch_fresh_metadata_period_ms]
**Type:** `Milliseconds`\
**Default:** `10000`\
**Tier:** Production\
How often merge coordinator should sync with zookeeper to take fresh metadata
shared_merge_tree_merge_coordinator_max_merge_request_size [#shared_merge_tree_merge_coordinator_max_merge_request_size]
**Type:** `UInt64`\
**Default:** `20`\
**Tier:** Production\
Number of merges that coordinator can request from MergerMutator at once
shared_merge_tree_merge_coordinator_max_period_ms [#shared_merge_tree_merge_coordinator_max_period_ms]
**Type:** `Milliseconds`\
**Default:** `10000`\
**Tier:** Production\
Maximum time between runs of merge coordinator thread
shared_merge_tree_merge_coordinator_merges_prepare_count [#shared_merge_tree_merge_coordinator_merges_prepare_count]
**Type:** `UInt64Auto`\
**Default:** `auto`\
**Tier:** Production\
Number of merge entries that coordinator should prepare and distribute across workers. When set to 'auto', equals the max number of merge tasks allowed on a single replica multiplied by the number of active replicas.
shared_merge_tree_merge_coordinator_min_period_ms [#shared_merge_tree_merge_coordinator_min_period_ms]
**Type:** `Milliseconds`\
**Default:** `1`\
**Tier:** Production\
Minimum time between runs of merge coordinator thread
shared_merge_tree_merge_worker_fast_timeout_ms [#shared_merge_tree_merge_worker_fast_timeout_ms]
**Type:** `Milliseconds`\
**Default:** `100`\
**Tier:** Production\
Timeout that merge worker thread will use if it is needed to update it's state after immediate action
shared_merge_tree_merge_worker_regular_timeout_ms [#shared_merge_tree_merge_worker_regular_timeout_ms]
**Type:** `Milliseconds`\
**Default:** `10000`\
**Tier:** Production\
Time between runs of merge worker thread
shared_merge_tree_outdated_parts_group_size [#shared_merge_tree_outdated_parts_group_size]
**Type:** `UInt64`\
**Default:** `2`\
**Tier:** Production
How many replicas will be in the same rendezvous hash group for outdated parts cleanup. Cloud.
shared_merge_tree_partitions_hint_ratio_to_reload_merge_pred_for_mutations [#shared_merge_tree_partitions_hint_ratio_to_reload_merge_pred_for_mutations]
**Type:** `Float`\
**Default:** `0.5`\
**Tier:** Production
Will reload merge predicate in merge/mutate selecting task when `/` ratio is higher than the setting. Cloud
shared_merge_tree_parts_load_batch_size [#shared_merge_tree_parts_load_batch_size]
**Type:** `UInt64`\
**Default:** `32`\
**Tier:** Production
Amount of fetch parts metadata jobs to schedule at once. Cloud
shared_merge_tree_postpone_next_merge_for_locally_merged_parts_ms [#shared_merge_tree_postpone_next_merge_for_locally_merged_parts_ms]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production
Time to keep a locally merged part without starting a new merge containing this part. Gives other replicas a chance fetch the part and start this merge. Cloud.
shared_merge_tree_postpone_next_merge_for_locally_merged_parts_rows_threshold [#shared_merge_tree_postpone_next_merge_for_locally_merged_parts_rows_threshold]
**Type:** `UInt64`\
**Default:** `1000000`\
**Tier:** Production
Minimum size of part (in rows) to postpone assigning a next merge just after merging it locally. Cloud.
shared_merge_tree_range_for_merge_window_size [#shared_merge_tree_range_for_merge_window_size]
**Type:** `UInt64`\
**Default:** `10`\
**Tier:** Production
Window size (in block numbers) used to distribute merge assignment across replicas. Parts within the same window are assigned to the same replicas. Cloud
shared_merge_tree_read_virtual_parts_from_leader [#shared_merge_tree_read_virtual_parts_from_leader]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
Read virtual parts from leader when possible. Cloud
shared_merge_tree_replica_set_max_lifetime_seconds [#shared_merge_tree_replica_set_max_lifetime_seconds]
**Type:** `Seconds`\
**Default:** `1800`\
**Tier:** Production\
How often replicas will try to update replica set in background. Next run is jittered uniformly in \[0, value] seconds. Exception: value = 0 does not follow that contract; the implementation applies a minimum of 200 ms, so the next run is jittered in \[0, 200] ms.
shared_merge_tree_try_fetch_part_in_memory_data_from_replicas [#shared_merge_tree_try_fetch_part_in_memory_data_from_replicas]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If enabled all the replicas try to fetch part in memory data (like primary key, partition info and so on) from other replicas where it already exists.
shared_merge_tree_try_fetch_part_in_memory_data_from_replicas_on_startup [#shared_merge_tree_try_fetch_part_in_memory_data_from_replicas_on_startup]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If enabled all the replicas try to fetch part in memory data (like primary key, partition info and so on) on startup from other replicas where it already exists.
shared_merge_tree_update_replica_flags_delay_ms [#shared_merge_tree_update_replica_flags_delay_ms]
**Type:** `Milliseconds`\
**Default:** `30000`\
**Tier:** Production\
How often replica will try to reload it's flags according to background schedule.
shared_merge_tree_use_blobs_list_for_parts [#shared_merge_tree_use_blobs_list_for_parts]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Store parts of a SharedMergeTree table (both Wide and Compact) as a single consolidated blob-list metadata node instead of one Keeper node per file. Collapses per-part Keeper metadata from O(files) to a small constant while keeping one object-storage blob per file. Only available in ClickHouse Cloud.
shared_merge_tree_use_metadata_hints_cache [#shared_merge_tree_use_metadata_hints_cache]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
Enables requesting FS cache hints from in-memory cache on other replicas. Cloud
shared_merge_tree_use_outdated_parts_compact_format [#shared_merge_tree_use_outdated_parts_compact_format]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
Use compact format for outdated parts: reduces load to Keeper, improves outdated parts processing. Cloud
shared_merge_tree_use_too_many_parts_count_from_virtual_parts [#shared_merge_tree_use_too_many_parts_count_from_virtual_parts]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production
If enabled too many parts counter will rely on shared data in Keeper, not on local replica state. Cloud
shared_merge_tree_use_zookeeper_connection_pool [#shared_merge_tree_use_zookeeper_connection_pool]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If enabled, SharedMergeTree uses one of server-level pooled ZooKeeper sessions.
shared_merge_tree_virtual_parts_discovery_batch [#shared_merge_tree_virtual_parts_discovery_batch]
**Type:** `UInt64`\
**Default:** `1`\
**Tier:** Production\
How many partition discoveries should be packed into batch
simultaneous_parts_removal_limit [#simultaneous_parts_removal_limit]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
If there are a lot of outdated parts cleanup thread will try to delete up to `simultaneous_parts_removal_limit` parts during one iteration. `simultaneous_parts_removal_limit` set to `0` means unlimited.
sleep_before_commit_local_part_in_replicated_table_ms [#sleep_before_commit_local_part_in_replicated_table_ms]
**Type:** `Milliseconds`\
**Default:** `0`\
**Tier:** Production\
For testing. Do not change it.
sleep_before_loading_outdated_parts_ms [#sleep_before_loading_outdated_parts_ms]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
For testing. Do not change it.
storage_policy [#storage_policy]
**Type:** `String`\
**Default:** `default`\
**Tier:** Production\
Name of storage disk policy
string_serialization_version [#string_serialization_version]
**Type:** `MergeTreeStringSerializationVersion`\
**Default:** `with_size_stream`\
**Tier:** Production\
Controls the serialization format for top-level `String` columns. This setting is only effective when `serialization_info_version` is set to "with\_types". When set to `with_size_stream`, top-level `String` columns are serialized with a separate `.size` subcolumn storing string lengths, rather than inline. This allows real `.size` subcolumns and can improve compression efficiency. Nested `String` types (e.g., inside `Nullable`, `LowCardinality`, `Array`, or `Map`) are not affected, except when they appear in a `Tuple`. Possible values: - `single_stream` — Use the standard serialization format with inline sizes. - `with_size_stream` — Use a separate size stream for top-level `String` columns.
subvertical_merge_pack_object_columns [#subvertical_merge_pack_object_columns]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
When enabled, sub-vertical merge of an `Object`/`JSON` column packs every substream of one parent column — typed paths, dynamic paths, plus the wrap-up `shared_data` and `ObjectStructure` substreams — into a single bundle pair (`.raw_packed.bin` + `.raw_packed.cmrk4`) instead of writing one `.bin`/`.mrk2` pair per substream. Only applies to vertical-merge output; inserts and horizontal merges keep the per-substream layout. A part becomes packed after its first vertical merge; mutating a packed column produces a per-substream result until the next vertical merge re-packs it.
table_disk [#table_disk]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
This is table disk: the path/endpoint points to the table data, not the database data. Supported for object-storage disks whose metadata lives on the object storage itself (s3\_plain, s3\_plain\_rewritable, web, web\_index) and their cached variants. Encrypted variants are supported only over the writable s3\_plain / s3\_plain\_rewritable disks, not over the read-only web / web\_index disks.
table_readonly [#table_readonly]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
If set to true, the table is in read-only mode and performs no modifications on disk. All foreground operations that would modify the table are rejected: inserts, mutations, `OPTIMIZE`, and the data-mutating partition commands (`ATTACH`/`MOVE`/`DROP`/`DROP DETACHED`/`FETCH`/`REPLACE PARTITION`, as well as `MOVE PARTITION ... TO TABLE` targeting this table). Operations that do not modify the table's data, such as `FREEZE`/`UNFREEZE` and `FORGET PARTITION`, remain allowed. No background work is scheduled either: regular merges, TTL merges (`DELETE`/`MOVE`/recompression), recompression merges, background mutations, and background part moves are all suppressed. As a consequence, a table with a TTL no longer reclaims or moves its expired data while this setting is enabled. The setting can always be toggled back with `ALTER TABLE ... MODIFY SETTING table_readonly = 0` (or `RESET SETTING`). It is not supported for `ReplicatedMergeTree`.
temporary_directories_lifetime [#temporary_directories_lifetime]
**Type:** `Seconds`\
**Default:** `86400`\
**Tier:** Production\
How many seconds to keep tmp\_-directories. You should not lower this value because merges and mutations may not be able to work with low value of this setting.
text_index_dictionary_block_frontcoding_compression [#text_index_dictionary_block_frontcoding_compression]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Default front-coding compression for text index dictionary blocks. Can be overridden by explicit `dictionary_block_frontcoding_compression` index argument.
text_index_dictionary_block_size [#text_index_dictionary_block_size]
**Type:** `NonZeroUInt64`\
**Default:** `512`\
**Tier:** Production\
Default dictionary block size for text indexes. Can be overridden by explicit `dictionary_block_size` index argument.
text_index_max_memory_usage_before_flush [#text_index_max_memory_usage_before_flush]
**Type:** `NonZeroUInt64`\
**Default:** `1073741824`\
**Tier:** Production\
Maximum estimated memory retained by a text index builder before flushing a temporary segment.
text_index_max_processed_tokens_before_flush [#text_index_max_processed_tokens_before_flush]
**Type:** `NonZeroUInt64`\
**Default:** `100000000`\
**Tier:** Production\
Maximum number of processed tokens accumulated by a text index builder before flushing a temporary segment.
text_index_posting_list_block_size [#text_index_posting_list_block_size]
**Type:** `NonZeroUInt64`\
**Default:** `1048576`\
**Tier:** Production\
Default posting list block size for text indexes (rows). Can be overridden by explicit `posting_list_block_size` index argument.
text_index_posting_list_codec [#text_index_posting_list_codec]
**Type:** `TextIndexPostingListCodec`\
**Default:** `none`\
**Tier:** Production\
Default posting list codec for text indexes. Can be overridden by explicit `posting_list_codec` index argument.
text_index_serialization_version [#text_index_serialization_version]
**Type:** `MergeTreeTextIndexSerializationVersion`\
**Default:** `v2_with_positions`\
**Tier:** Production\
The preferred on-disk serialization format version for writing text indexes. The setting is a preference rather than a hard constraint: if the configured version cannot represent an index, a newer version that can represent it is chosen automatically, and writing a text index never fails because of this setting. During a rolling upgrade, pin the format with the profile-level `compatibility` setting on the already upgraded servers, so that they keep writing the format that older servers can still read. Possible values: - `v0_initial` — The original format. Does not persist the posting list codec type. - `v1_with_codec` — Persists the posting list codec type in the text index header. - `v2_with_positions` — Persists token positions for indexes with `support_phrase_search`.
try_fetch_recompressed_part_timeout [#try_fetch_recompressed_part_timeout]
**Type:** `Seconds`\
**Default:** `7200`\
**Tier:** Production\
Timeout (in seconds) before starting merge with recompression. During this time RawTree tries to fetch recompressed part from replica which assigned this merge with recompression. Recompression works slow in most cases, so we don't start merge with recompression until this timeout and trying to fetch recompressed part from replica which assigned this merge with recompression. Possible values: - Any positive integer.
ttl_only_drop_parts [#ttl_only_drop_parts]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Controls whether data parts are fully dropped in MergeTree tables when all rows in that part have expired according to their `TTL` settings. When `ttl_only_drop_parts` is disabled (by default), only the rows that have expired based on their TTL settings are removed. When `ttl_only_drop_parts` is enabled, the entire part is dropped if all rows in that part have expired according to their `TTL` settings.
use_adaptive_write_buffer_for_dynamic_subcolumns [#use_adaptive_write_buffer_for_dynamic_subcolumns]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Allow to use adaptive writer buffers during writing dynamic subcolumns to reduce memory usage
use_compact_variant_discriminators_serialization [#use_compact_variant_discriminators_serialization]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables compact mode for binary serialization of discriminators in Variant data type. This mode allows to use significantly less memory for storing discriminators in parts when there is mostly one variant or a lot of NULL values.
use_const_adaptive_granularity [#use_const_adaptive_granularity]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Non-const adaptive granularity (the default, `index_granularity_bytes` is not `0`) keeps granules at a constant size in bytes, so their row count varies: for every written block, rows per granule is `index_granularity_bytes` divided by the average size of a row in that block, capped at `index_granularity`. Because it differs from granule to granule, the row count of every granule in the part has to be kept in memory, 8 bytes each, which on large tables adds up to tens of gigabytes. Enabling constant adaptive granularity (this setting) keeps granules at a constant number of rows instead, so their size in bytes varies. The number is computed once from the average size of a row in the part being written, and a part stores that single value instead of one value per granule. Because all granules contain the same number of rows, no additional row count per granule needs to be stored. The amount of memory currently spent on these values is reported by the `TotalIndexGranularityBytesInMemory` metric in `system.asynchronous_metrics`, and per part in `system.parts.index_granularity_bytes_in_memory`. The setting is applied only to parts written after it was changed. Use [ALTER TABLE ... REWRITE PARTS](/reference/statements/alter/partition#rewrite-parts) to apply it to existing parts. `Compact` parts always use adaptive granularity.
use_minimalistic_checksums_in_zookeeper [#use_minimalistic_checksums_in_zookeeper]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Use small format (dozens bytes) for part checksums in ZooKeeper instead of ordinary ones (dozens KB). Before enabling check that all replicas support new format.
use_minimalistic_part_header_in_zookeeper [#use_minimalistic_part_header_in_zookeeper]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Storage method of the data parts headers in ZooKeeper. If enabled, ZooKeeper stores less data. For details, see [here](/reference/settings/server-settings/settings/use#use_minimalistic_part_header_in_zookeeper).
use_packed_metadata_part_storage [#use_packed_metadata_part_storage]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production
Use packed metadata part storage.
use_primary_key_cache [#use_primary_key_cache]
**Type:** `Bool`\
**Default:** `0`\
**Tier:** Production\
Use cache for primary index instead of saving all indexes in memory. Can be useful for very large tables
vertical_merge_algorithm_min_bytes_to_activate [#vertical_merge_algorithm_min_bytes_to_activate]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Minimal (approximate) uncompressed size in bytes in merging parts to activate Vertical merge algorithm.
vertical_merge_algorithm_min_columns_to_activate [#vertical_merge_algorithm_min_columns_to_activate]
**Type:** `UInt64`\
**Default:** `11`\
**Tier:** Production\
Minimal amount of non-PK columns to activate Vertical merge algorithm.
vertical_merge_algorithm_min_rows_to_activate [#vertical_merge_algorithm_min_rows_to_activate]
**Type:** `UInt64`\
**Default:** `131072`\
**Tier:** Production\
Minimal (approximate) sum of rows in merging parts to activate Vertical merge algorithm.
vertical_merge_optimize_lightweight_delete [#vertical_merge_optimize_lightweight_delete]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true, lightweight delete is optimized on vertical merge.
vertical_merge_optimize_ttl_delete [#vertical_merge_optimize_ttl_delete]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true, rows TTL delete is optimized on vertical merge. Instead of forcing horizontal merge, the TTL filter is evaluated and passed to the merging algorithm which sets skip flags in row sources.
vertical_merge_parallelize [#vertical_merge_parallelize]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Parallelize the read, gather, write for columns during vertical merge across worker threads. Currently only a sub-vertical `Object`/`JSON` merge into a packed bundle (`subvertical_merge_pack_object_columns`) is parallelized.
vertical_merge_parallelize_max_pending_bytes [#vertical_merge_parallelize_max_pending_bytes]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Bound, in bytes, on the memory held by the column units of a parallel vertical merge that are produced but not yet absorbed into the result part (see `vertical_merge_parallelize`): their in-memory output plus their marks. Units are absorbed in order on the merge's main thread, so a slow unit makes the ones after it wait; once the waiting units hold this many bytes, idle workers get no new unit until the absorber catches up. The bound is checked when a unit is handed to a worker, so the units already being produced can push the waiting set past it, by at most `vertical_merge_parallelize_workers` times `vertical_merge_parallelize_worker_buffer_size`. A unit that spilled to a temporary file counts only the part it kept in memory. `0` means `vertical_merge_parallelize_workers` times `vertical_merge_parallelize_worker_buffer_size`.
vertical_merge_parallelize_worker_buffer_size [#vertical_merge_parallelize_worker_buffer_size]
**Type:** `UInt64`\
**Default:** `16777216`\
**Tier:** Production\
Per-worker in-memory budget, in bytes, for holding produced-but-not-yet-absorbed column units during a parallel vertical merge (see `vertical_merge_parallelize`). A column is buffered in memory up to the worker's remaining budget; the rest spills to a temporary file. The budget is dynamic -- it shrinks while finished units wait to be written into the result part in order, and is returned once they are, so a worker whose budget is momentarily used up spills the next column to disk. `0` means no in-memory buffer at all: every unit is written straight to a temporary file.
vertical_merge_parallelize_workers [#vertical_merge_parallelize_workers]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
Maximum number of worker threads a single vertical merge may use to produce columns in parallel (see `vertical_merge_parallelize`). `0` means the number of CPU cores. This only caps how many workers one merge uses at once — they are still drawn from the shared vertical-merge thread pool — so lowering it reduces the CPU concurrency of an individual merge.
vertical_merge_remote_filesystem_prefetch [#vertical_merge_remote_filesystem_prefetch]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
If true prefetching of data from remote filesystem is used for the next column during merge
vertical_merge_resort_max_bytes_before_external_sort [#vertical_merge_resort_max_bytes_before_external_sort]
**Type:** `UInt64`\
**Default:** `268435456`\
**Tier:** Production\
Per-sort in-memory budget, in bytes, for the sorts a vertical merge runs to restore the order of re-sorted parts (inverting the re-sort permutation and re-applying it to every gathering column of every re-sorted part). Once a sort buffers more than this, it spills to a temporary file, regardless of `max_bytes_ratio_before_external_sort`. Many such sorts run at once (one per re-sorted part, per column unit, per worker), so this bounds each of them independently of the query-level external sort settings. `0` means inherit `max_bytes_before_external_sort`/`max_bytes_ratio_before_external_sort`.
wait_for_unique_parts_send_before_shutdown_ms [#wait_for_unique_parts_send_before_shutdown_ms]
**Type:** `Milliseconds`\
**Default:** `0`\
**Tier:** Production\
Before shutdown table will wait for required amount time for unique parts (exist only on current replica) to be fetched by other replicas (0 means disabled).
write_marks_for_substreams_in_compact_parts [#write_marks_for_substreams_in_compact_parts]
**Type:** `Bool`\
**Default:** `1`\
**Tier:** Production\
Enables writing marks per each substream instead of per each column in Compact parts. It allows to read individual subcolumns from the data part efficiently. For example, column `t Tuple(a String, b UInt32, c Array(Nullable(UInt32)))` is serialized in the next substreams: - `t.a` for String data of tuple element `a` - `t.b` for UInt32 data of tuple element `b` - `t.c.size0` for array sizes of tuple element `c` - `t.c.null` for null map of nested array elements of tuple element `c` - `t.c` for UInt32 data pf nested array elements of tuple element `c` When this setting is enabled, we will write a mark for each of these 5 substreams, which means that we will be able to read the data of each individual substream from the granule separately if needed. For example, if we want to read the subcolumn `t.c` we will read only data of substreams `t.c.size0`, `t.c.null` and `t.c` and won't read data from substreams `t.a` and `t.b`. When this setting is disabled, we will write a mark only for top-level column `t`, which means that we will always read the whole column data from the granule, even if we need only data of some substreams.
zero_copy_concurrent_part_removal_max_postpone_ratio [#zero_copy_concurrent_part_removal_max_postpone_ratio]
**Type:** `Float`\
**Default:** `0.05`\
**Tier:** Production\
Max percentage of top level parts to postpone removal in order to get smaller independent ranges. Recommended not to change.
zero_copy_concurrent_part_removal_max_split_times [#zero_copy_concurrent_part_removal_max_split_times]
**Type:** `UInt64`\
**Default:** `5`\
**Tier:** Production\
Max recursion depth for splitting independent Outdated parts ranges into smaller subranges. Recommended not to change.
zero_copy_merge_mutation_min_parts_size_sleep_before_lock [#zero_copy_merge_mutation_min_parts_size_sleep_before_lock]
**Type:** `UInt64`\
**Default:** `1073741824`\
**Tier:** Production\
If zero copy replication is enabled sleep random amount of time before trying to lock depending on parts size for merge or mutation
zero_copy_merge_mutation_min_parts_size_sleep_no_scale_before_lock [#zero_copy_merge_mutation_min_parts_size_sleep_no_scale_before_lock]
**Type:** `UInt64`\
**Default:** `0`\
**Tier:** Production\
If zero copy replication is enabled sleep random amount of time up to 500ms before trying to lock for merge or mutation.
zookeeper_session_expiration_check_period [#zookeeper_session_expiration_check_period]
**Type:** `Seconds`\
**Default:** `60`\
**Tier:** Production\
ZooKeeper session expiration check period, in seconds. Possible values: - Any positive integer.