MergeTree tables settings

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

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](/docs/development/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.

## prefer\_fetch\_merged\_part\_size\_threshold[​](#prefer_fetch_merged_part_size_threshold "Direct link to prefer_fetch_merged_part_size_threshold")

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

If the time passed since a replication log (ClickHouse 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 "Direct link to prewarm_mark_cache")

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

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

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

## primary\_key\_compression\_codec[​](#primary_key_compression_codec "Direct link to primary_key_compression_codec")

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

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

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 "Direct link to 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[​](#ratio_of_defaults_for_sparse_serialization "Direct link to 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[​](#reduce_blocking_parts_sleep_ms "Direct link to reduce_blocking_parts_sleep_ms")