These settings are available in system.merge_tree_settings and are autogenerated from ClickHouse source.
adaptive_write_buffer_initial_size
Initial size of an adaptive write buffer
add_implicit_sign_column_constraint_for_collapsing_engine
If true, adds an implicit constraint for the sign column of a CollapsingMergeTree
or VersionedCollapsingMergeTree table to allow only valid values (1 and -1).
alter_column_secondary_index_mode
Configures whether to allow ALTER commands that modify columns covered by secondary indices, and what action to take if
they are allowed. By default, such ALTER commands are allowed and the indices are rebuilt.
Possible values:
rebuild (default): Rebuilds any secondary indices affected by the column in the ALTER command.throw: Prevents any ALTER of columns covered by explicit secondary indices by throwing an exception. Implicit indices are excluded from this restriction and will be rebuilt.drop: Drop the dependent secondary indices. The new parts won’t have the indices, requiring MATERIALIZE INDEX to recreate them.compatibility: Matches the original behaviour: throw on ALTER ... MODIFY COLUMN and rebuild on ALTER ... UPDATE/DELETE.ignore: Intended for expert usage. It will leave the indices in an inconsistent state, allowing incorrect query results.
apply_patches_on_merge
If true patch parts are applied on merges
assign_part_uuids
When enabled, a unique part identifier will be assigned for every new part.
Before enabling, check that all replicas support UUID version 4.
auto_statistics_types
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.
background_task_preferred_step_execution_time_ms
Target time to execution of one step of merge or mutation. Can be exceeded if
one step takes longer time
clean_deleted_rows
Obsolete setting, does nothing.
clone_replica_zookeeper_create_get_part_batch_size
Batch size for ZooKeeper multi-create get-part requests when cloning replica.
compatibility_allow_sampling_expression_not_in_primary_key
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.
compute_exact_num_defaults_for_sparse_columns
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.
deduplicate_merge_projection_mode
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:
ignorethrowdroprebuild
deduplication_hashes_cache_update_wait_ms
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
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:
- Compression codec defined for the column in the table declaration
- Compression codec defined in
default_compression_codec (this setting)
- Default compression codec defined in
compression settings
Default value: an empty string (not defined).
disk
Name of storage disk. Can be specified instead of storage policy.
dynamic_serialization_version
Serialization version for Dynamic data type. Required for compatibility.
Possible values:
enforce_index_structure_match_on_partition_manipulation
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.
execute_merges_on_single_replica_time_threshold
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:
finished_mutations_to_keep
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 filesystem cache for merges
initialization_retry_period
Retry period for table initialization, in seconds.
kill_threads
Obsolete setting, does nothing.
lightweight_mutation_projection_mode
By default, lightweight delete DELETE does not work for tables with
projections. This is because rows in a projection may be affected by a
DELETE operation. So the default value would be throw. However, this
option can change the behavior. With the value either drop or rebuild,
deletes will work with projections. drop would delete the projection so it
might be fast in the current query as projection gets deleted but slow in
future queries as no projection attached. rebuild would rebuild the
projection which might affect the performance of the current query, but
might speedup for future queries. A good thing is that these options would
only work in the part level, which means projections in the part that don’t
get touched would stay intact instead of triggering any action like
drop or rebuild.
Possible values:
load_existing_rows_count_for_old_parts
If enabled along with 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:
See Also
lock_acquire_timeout_for_background_operations
For background operations like merges, mutations etc. How many seconds before
failing to acquire table locks.
mutation_workload
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
non_replicated_deduplication_window
The number of the most recently inserted blocks in the non-replicated
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 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 to SharedJoin or SharedSet. Only in ClickHouse Cloud.
nullable_serialization_version
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.
object_serialization_version
Serialization version for JSON data type. Required for compatibility.
Possible values:
Only version v3 supports changing the shared data serialization version.
old_parts_lifetime
The time (in seconds) of storing inactive parts to protect against data loss
during spontaneous server reboots.
Possible values:
After merging several parts into a new part, ClickHouse 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 ClickHouse checks the integrity of the parts. If the merged
part is damaged ClickHouse 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
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.
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, ClickHouse 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, ClickHouse uses a heuristics to quickly find a row order which
still improves compression rates over the original row order.
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.
packed_skip_index_max_bytes
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_<name>.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 (default).
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
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.
propagate_types_serialization_versions_to_nested_types
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
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), ClickHouse 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:
Notice the s column in my_sparse_table uses less storage space on disk:
You can verify if a column is using the sparse encoding by viewing the
serialization_kind column of the system.parts_columns table:
You can see which parts of s were stored using the sparse serialization:
reduce_blocking_parts_sleep_ms
Only available in ClickHouse 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
replace_long_file_name_to_hash
If the file name for column is too long (more than ‘max_file_name_length’
bytes) replace it to SipHash128
replicated_can_become_leader
If true, replicated tables replicas on this node will try to acquire
leadership.
Possible values:
search_orphaned_parts_disks
ClickHouse 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 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
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.
simultaneous_parts_removal_limit
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.
storage_policy
Name of storage disk policy
string_serialization_version
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.
temporary_directories_lifetime
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.
try_fetch_recompressed_part_timeout
Timeout (in seconds) before starting merge with recompression. During this
time ClickHouse 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:
ttl_only_drop_parts
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.
wait_for_unique_parts_send_before_shutdown_ms
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).
zookeeper_session_expiration_check_period
ZooKeeper session expiration check period, in seconds.
Possible values:
Last modified on July 24, 2026
