MergeTree tables settings

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 "Direct link to object_shared_data_buckets_for_wide_part")

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 "Direct link to object_shared_data_serialization_version")

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 "Direct link to object_shared_data_serialization_version_for_zero_level_parts")

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 "Direct link to 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[​](#optimize_row_order "Direct link to 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](/docs/sql-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, 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.

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

## part\_moves\_between\_shards\_delay\_seconds[​](#part_moves_between_shards_delay_seconds "Direct link to part_moves_between_shards_delay_seconds")

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

Time to wait before/after moving parts between shards.

## part\_moves\_between\_shards\_enable[​](#part_moves_between_shards_enable "Direct link to part_moves_between_shards_enable")

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

Experimental/Incomplete feature to move parts between shards. Does not take
into account sharding expressions.

## parts\_to\_delay\_insert[​](#parts_to_delay_insert "Direct link to parts_to_delay_insert")

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.

ClickHouse 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 "Direct link to parts_to_throw_insert")