Other MergeTree table settings

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.

always_use_copy_instead_of_hardlinks

Always copy data instead of hardlinking during mutations/replaces/detaches and so on.

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:

• ignore
• throw
• drop
• rebuild

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:

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).

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:

• v1
• v2
• v3

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:

• Any positive integer.

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:

• throw
• drop
• rebuild

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:

• true
• false

See Also

• exclude_deleted_rows_for_part_size_in_merge setting

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

• Workload Scheduling

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:

• v1
• v2
• v3

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:

• Any positive integer.

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. 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

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.

patch_parts_version

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.

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:

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:

SELECT table, name, data_compressed_bytes, data_uncompressed_bytes FROM system.columns
WHERE table LIKE 'my_%_table';

┌─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:

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:

┌─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

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:

• true
• false

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.
• with_missing_columns - Everything with_types records, plus a missing_columns field listing omitted columns and the type whose default represents their values. Required to enable skip_empty_columns_on_insert.

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 (or with_missing_columns) to enable the corresponding features.

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.

skip_empty_columns_on_insert

If enabled, columns whose values are entirely type-defaults in a given INSERT block are not written to the data part on disk. When the part is later read, missing columns are filled with the default of their recorded type. This saves disk space for sparse-update workloads where most columns in each INSERT are left at their type’s default value. Columns with DEFAULT, MATERIALIZED, or ALIAS expressions are never skipped, because the read path would evaluate the expression instead of returning the type-default that was explicitly inserted. Patch parts (used by lightweight UPDATE) are also excluded. This optimization records the missing columns in the part’s serialization.json using the with_missing_columns format version, so it only takes effect when serialization_info_version is set to with_missing_columns. With a lower version (for example pinned to a lower value for a rolling upgrade so older servers can read freshly written parts) no columns are skipped.

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” or newer. 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:

• Any positive integer.

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:

• Any positive integer.