[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Concepts](index.md) > ClickHouse® settings
# ClickHouse® settings
For Managed Service for ClickHouse® clusters, you can specify these ClickHouse® settings:
* [Server-level settings](#server-level-settings): Configured at the shard or cluster level. In a custom ClickHouse® installation, you can change these settings only via configuration files, while in Managed Service for ClickHouse® clusters, only via the Yandex Cloud interfaces.
* [Query-level settings](#user-level-settings): Configured at the user, session, or query level.
* [Quota settings](#quota-settings): Configured at the user level.
{% note info %}
ClickHouse® supports more settings than the Yandex Cloud interfaces. You can use SQL queries to change ClickHouse® settings, e.g., [specify ClickHouse® settings at the query level](../operations/change-query-level-settings.md) or [change the settings for MergeTree tables](../operations/change-server-level-settings.md#sql-queries).
{% endnote %}
The label next to the setting name identifies the interface used to specify this setting: the management console, CLI, API, SQL, or Terraform. The All interfaces label means you can use any of the above interfaces.
The name for a setting depends on the interface you use, e.g.:
- **Geobase uri** in the management console is the same as:
- `geobase_uri` in the gRPC API and Terraform.
- `geobaseUri` in the REST API.
- **Allow DDL** in the management console is the same as:
- `allow_ddl` in the CLI, gRPC API, and SQL.
- `allowDdl` in the REST API.
## Server-level settings {#server-level-settings}
{% note info %}
To reset to default values using Terraform, specify these values explicitly for the settings in the manifest. Deleting settings from the manifest will not reset them to their defaults.
{% endnote %}
The following settings are available:
* **Access control improvements**{#setting-access-control-improvements} Management console
Access control system settings:
* **Select from information schema requires grant**{#setting-select-from-information-schema-requires-grant}: Sets whether the `SELECT * FROM information_schema.` query requires any permissions and whether any user can run the query. The default value is `false`. When set to `true`, you need the `GRANT SELECT ON information_schema.` permission to run the query, same as for ordinary tables.
* **Select from system db requires grant**{#setting-select-from-system-db-requires-grant}: Sets whether the `SELECT * FROM system.` query requires any permissions and whether any user can run the query. The default value is `false`. When set to `true`, you need the `GRANT SELECT ON system.` permission to run the query, same as for ordinary tables.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#access_control_improvements).
* **Asynchronous insert log enabled**{#setting-asynchronous-insert-log-enabled} Management console CLI API
Sets whether to log information about asynchronous inserts. Such logs will be saved to the `system.asynchronous_insert_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/asynchronous_insert_log).
* **Asynchronous insert log retention size**{#setting-asynchronous-insert-log-retention-size} Management console CLI API
Size of the `system.asynchronous_insert_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Asynchronous insert log retention time**{#setting-asynchronous-insert-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.asynchronous_insert_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Asynchronous metric log enabled**{#setting-asynchronous-metric-log-enabled} Management console CLI API
Sets whether to log historical metric values from the `system.asynchronous_metrics` table to the `system.asynchronous_metric_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/asynchronous_metric_log).
* **Asynchronous metric log retention size**{#setting-asynchronous-metric-log-retention-size} Management console CLI API
Size of the `system.asynchronous_metric_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Asynchronous metric log retention time**{#setting-asynchronous-metric-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.asynchronous_metric_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Background buffer flush schedule pool size**{#setting-background-buffer-flush-schedule-pool-size} Management console
Number of threads for background data flushing in [Buffer](https://clickhouse.com/docs/enen/engines/table-engines/special/buffer) tables.
The minimum value is `1` and the default is `16`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_buffer_flush_schedule_pool_size).
* **Background common pool size**{#setting-background-common-pool-size} Management console API
Number of threads for common background operations, such as file system clean up, in [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables.
The minimum value is `1` and the default is `8`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_common_pool_size).
* **Background distributed schedule pool size**{#setting-background-distributed-schedule-pool-size} Management console
Number of threads for background operations in [Distributed](https://clickhouse.com/docs/enen/engines/table-engines/special/distributed) tables.
The minimum value is `1` and the default is `16`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_distributed_schedule_pool_size).
* **Background fetches pool size**{#setting-background-fetches-pool-size} Management console API
Number of threads for background data copying from a replica in [ReplicatedMergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/replication) tables.
The minimum value is `1`; for ClickHouse® 24.12 or lower, the default value is `16`; for 25.1 or higher, `32`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_fetches_pool_size).
* **Background merges mutations concurrency ratio**{#setting-background-merges-mutations-concurrency-ratio} Management console CLI API
Number of background merges and mutations that each thread can run concurrently.
The default value is `2`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_merges_mutations_concurrency_ratio).
* **Background message broker schedule pool size**{#setting-background-message-broker-schedule-pool-size} Management console
Number of threads for background message translation operations. This setting gets a new value when restarting the ClickHouse® server.
The minimum value is `1` and the default is `16`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_message_broker_schedule_pool_size).
* **Background move pool size**{#setting-background-move-pool-size} Management console
Number of threads for background moves of data parts in [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables.
The minimum value is `1` and the default is `8`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_move_pool_size).
* **Background pool size**{#setting-background-pool-size} Management console API Terraform
Number of threads for background merges and [mutations](https://clickhouse.com/docs/enen/sql-reference/statements/alter#mutations) in [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables.
The minimum value is `1` and the default is `16`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#background_pool_size).
* **Background schedule pool size**{#setting-background-schedule-pool-size} Management console API Terraform
Number of threads for background jobs. It is used for replicated tables, streams in Apache Kafka®, and updating IP addresses of records in the internal DNS cache.
The minimum value is `1` and the default is `128`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Compression**{#setting-compression} Management console API Terraform
Data compression rules for [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables. For each rule, you need to specify the following:
* **Level**: Compression level. It is only available for the [zstd](https://clickhouse.com/docs/enen/sql-reference/statements/create/table#zstd) compression method. The minimum value is `1`, the maximum value is `12`, and the default value is `9`.
* **Method**: Compression method. Available methods: [LZ4](https://lz4.github.io/lz4/) and [zstd](https://facebook.github.io/zstd/).
* **Min part size**: Minimum size of a [data part](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/custom-partitioning-key), in bytes.
* **Min part size ratio**: Ratio between the smallest table part size and full table size. ClickHouse® will only apply the rule to the tables where this ratio does not exceed **Min part size ratio**.
You can add multiple compression rules. ClickHouse® will check the **Min part size** and **Min part size ratio** conditions and apply the rules to those tables that meet both of them. If multiple rules may apply to the same table, ClickHouse® applies the first one. If none of the rules apply, ClickHouse® will use the [LZ4](https://lz4.github.io/lz4/) compression method.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings).
* **Default database**{#setting-default-database} Management console API
Default database. To learn how to get a list of cluster databases, see [Managing databases](../operations/databases.md#list-db).
Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Dictionaries lazy load**{#setting-dictionaries-lazy-load} Management console CLI Terraform API SQL
With this setting enabled, each dictionary is loaded when you first use it. If the loading fails, the function that uses the dictionary will return an exception.
If the setting is disabled, the dictionaries are loaded when the server starts. The server waits for all the dictionaries to load before it starts processing connections.
By default, the setting is disabled for ClickHouse® 24.12 or lower and enabled for 25.1 or higher.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#server_configuration_parameters-dictionaries_lazy_load).
* **Geobase enabled**{#setting-geobase-enabled} Management console CLI API
Enables/disables the [built-in geobase dictionary](dictionaries.md#internal-dicts).
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Geobase uri**{#setting-geobase-uri} Management console CLI API Terraform
Address of the archive containing the [custom geobase](dictionaries.md#internal-dicts) in Object Storage.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Graphite rollup**{#setting-graphite-rollup} Management console API Terraform
[GraphiteMergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/graphitemergetree) engine configurations for [Graphite](http://graphite.readthedocs.io/en/latest/index.html) data thinning and aggregation (rollup):
* **Name**: Configuration name.
* **Patterns**: Thinning rules. A rule applies if the metric name matches the **Regexp** parameter value and the age of data matches the **Retention** parameter group value.
* **Function**: Aggregation function name.
* **Regexp**: Regular expression the metric name must match.
* **Retention**: Retention parameters. The function applies to the data with the age interval of [Age, Age + Precision]. You can set multiple groups of such parameters.
* **Age**: Minimum data age, in seconds.
* **Precision**: Accuracy of determining the age of the data, in seconds. The value must be a multiple of `86400` (number of seconds in 24 hours).
You can set up multiple configurations and use them for different tables. Changing this setting will restart ClickHouse® servers on the cluster hosts.
To create `GraphiteMergeTree` tables, specify column names for each one of such tables:
* **Path column name**: Column with the metric name (`Graphite` sensor). The default value is `Path`.
* **Time column name**: Column with the time of measuring the metric. The default value is `Time`.
* **Value column name**: Column with the metric value. The default value is `Value`.
* **Version column name**: Column with the metric version. The default value is `Timestamp`.
To learn more about Graphite support, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/graphitemergetree).
* **Jdbc bridge**{#setting-jdbc-bridge} Management console CLI API Terraform
JDBC bridge for queries against external databases. Specify the host and, optionally, port for accessing the data source.
Changing these settings will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/integrations/jdbc/jdbc-with-clickhouse).
* **Kafka**{#setting-kafka} Management console CLI API Terraform
Global authentication settings for [integration with Apache Kafka®](https://clickhouse.com/docs/enen/engines/table-engines/integrations/kafka):
* **Auto offset reset**: Initial position from which a [consumer](../../managed-kafka/concepts/producers-consumers.md) starts reading messages. Starting from there, as the consumer reads incoming messages, it commits offsets for new ones. By default, no value is set (equivalent to `latest`). For more information, see [this Confluent guide](https://docs.confluent.io/platform/current/clients/consumer.html#offset-management).
* **Batch size**: Maximum total size of all messages in a batch (MessageSet), including protocol overhead.
The possible values range from `1` to `2147483647` bytes. The default value is `1000000`.
* **Debug**: Context for debugging purposes. By default, the value is not set. You can only specify one value for this setting. For the list of possible values, see the `librdkafka` library page on [GitHub](https://github.com/confluentinc/librdkafka/blob/master/CONFIGURATION.md).
* **Enable ssl certificate verification**: Sets whether to verify SSL certificates. The default value is `false`.
* **Max poll interval ms**: Maximum interval, in milliseconds, between polls to get messages for high-level consumers. If exceeded, the user is removed from the group and a rebalance starts. No value is set by default (equivalent to `300000`, 5 minutes).
* **Message max bytes**: Maximum size per message for Apache Kafka® protocol requests.
The possible values range from `1000` to `1000000000` bytes. The default value is `1000000`.
* **Sasl mechanism**: SASL authentication:
* `GSSAPI` (default): Authentication [using Kerberos](https://kafka.apache.org/42/security/authentication-using-sasl/#authentication-using-saslkerberos).
* `PLAIN`: Authentication [using an unencrypted username-password pair](https://kafka.apache.org/42/security/authentication-using-sasl/#authentication-using-saslplain).
* `SCRAM-SHA-256` and `SCRAM-SHA-512`: Authentication [using the SCRAM family mechanisms](https://kafka.apache.org/42/security/authentication-using-sasl/#authentication-using-saslscram).
By default, no value is set (equivalent to `GSSAPI`).
* **Sasl password**: Apache Kafka® account password.
* **Sasl username**: Apache Kafka® account name.
* **Security protocol**: Security protocol used for authentication:
* `PLAINTEXT`: Authentication credentials are sent as plain text.
* `SSL`: Authentication credentials are sent SSL encrypted.
* `SASL_PLAINTEXT`: Authentication credentials are sent as plain text using SASL.
* `SASL_SSL`: Authentication credentials are sent SSL encrypted using SASL.
By default, no value is set (equivalent to `PLAINTEXT`).
* **Session timeout ms**: Timeout, in milliseconds, for a periodic signal from a user to maintain a client group session. If exceeded, the broker removes the user from the group and runs a rebalance. For ClickHouse® 25.1 or lower, the default value is `45000` (45 s); for 25.2 or higher, `30000` (30 s).
Changing these settings will restart ClickHouse® servers on the cluster hosts.
{% note tip %}
If you want to specify authentication settings at the [topic](../../managed-kafka/concepts/topics.md) level, specify them when [creating the Kafka table](https://clickhouse.com/docs/enen/engines/table-engines/integrations/kafka#creating-a-table) or reuse them with [named collections](https://clickhouse.com/docs/enen/operations/named-collections#named-collections-for-accessing-kafka).
{% endnote %}
* **Keep alive timeout**{#setting-keep-alive-timeout} Management console CLI API Terraform
Time period, in seconds, from the moment ClickHouse® receives the last query until the connection is terminated. If a new query comes during this time interval, the connection is not terminated.
The default value is `3`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Log level**{#setting-log-level} Management console CLI API Terraform
Event logging level. At each next level, the log will contain complete information from the previous one:
1. `ERROR`: Information about cluster errors.
1. `WARNING`: Information about events that may result in cluster errors.
1. `INFORMATION`: Confirmations and information about events that do not result in cluster errors.
1. `DEBUG`: System information for debugging.
1. `TRACE`: All available information on the cluster operation.
For more information about logging levels, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#server_configuration_parameters-logger).
* **Mark cache size**{#setting-mark-cache-size} Management console CLI API Terraform
Approximate size, in bytes, of the mark cache used by [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table engines. The cache is shared by a cluster host. Memory is allocated as needed.
The selected setting value is not a strict limit. ClickHouse® can make this cache a little smaller or larger.
The default value is `5368709120`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Max concurrent queries**{#setting-max-concurrent-queries} Management console CLI API Terraform
Maximum number of concurrent queries.
The minimum value is `10` and the default is `500`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Max connections**{#setting-max-connections} Management console CLI API Terraform
Maximum number of inbound client connections. This setting does not account for connections established to run distributed subqueries.
The minimum value is `10` and the default is `4096`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Max partition size to drop**{#setting-max-partition-size-to-drop} Management console CLI API Terraform
Maximum [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table [partition](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/custom-partitioning-key) size, in bytes, at which you can delete the table using a `DROP TABLE` query. You can use this setting to avoid accidental deletion of tables with real data, as these tables tend to be larger than test ones.
The default value is `53687091200` (50 GB). When set to `0`, you can delete tables of any size.
* **Max table size to drop**{#setting-max-table-size-to-drop} Management console CLI API Terraform
Maximum size, in bytes, of a [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table you can delete using a `DROP TABLE` query. You can use this setting to avoid accidental deletion of tables with real data, as these tables tend to be larger than test ones.
The default value is `53687091200` (50 GB). When set to `0`, you can delete tables of any size.
* **Merge tree**{#setting-merge-tree} Management console CLI API Terraform
MergeTree engine configuration:
* **Allow remote fs zero copy replication**: Sets whether to allow remote zero copy replication for S3 and HDFS disks.
The default value is `true`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/integrations/s3#disable-zero-copy-replication).
* **Check sample column is correct**: When creating a table, checks that column data type is correct for sampling or sampling expressions.
By default, the check is enabled. You can disable it if you already have tables with incorrect sampling expressions. This will eliminate false negatives and exceptions when starting the server.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#check_sample_column_is_correct).
* **Cleanup delay period**: Time interval, in seconds, between running [distributed DDL queries](https://clickhouse.com/docs/enen/sql-reference/distributed-ddl) to clean up outdated data.
The default value is `30`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#server-settings-distributed_ddl).
* **Deduplicate merge projection mode** Management console CLI: Sets the background merge behavior in tables with projections:
* `ignore`: Ignore the parameter. Use this value only to ensure compatibility.
* `throw`: Generate an exception to prevent the projection parts from getting desynchronized.
* `drop`: Delete the affected part of the projection table.
* `rebuild`: Rebuild the affected part of the projection table.
By default, no value is set (equivalent to `throw`). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#deduplicate_merge_projection_mode).
* **Inactive parts to delay insert**: Number of inactive table data parts, exceeding which will trigger ClickHouse® to throttle the speed of table data inserts.
This setting is disabled by default (`0`). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#inactive-parts-to-delay-insert).
* **Inactive parts to throw insert**: Number of inactive table data parts, exceeding which will trigger ClickHouse® to throw the `Too many inactive parts ...` exception.
This setting is disabled by default (`0`). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#inactive-parts-to-throw-insert).
* **Lightweight mutation projection mode** Management console CLI: Sets the simplified deletion behavior in tables with projections:
* `throw`: Generate an exception to prevent the projection parts from getting desynchronized.
* `drop`: Delete the affected part of the projection table.
* `rebuild`: Rebuild the affected part of the projection table.
By default, no value is set (equivalent to `throw`). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#lightweight_mutation_projection_mode).
* **Materialize ttl recalculate only** Management console CLI: Enables applying modified TTL rules for tables or rows only when the query explicitly specifies `MATERIALIZE TTL`.
The default value is:
* For ClickHouse® versions 25.2 or higher: `true`. Recalculates only the TTL info, which simplifies TTL materialization.
* For ClickHouse® versions 25.1 or lower: `false`. Complete TTL materialization is performed.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#materialize_ttl_recalculate_only).
* **Max avg part size for too many parts**: Maximum average size of active data parts in a table, in bytes, that triggers the **Parts to delay insert** and **Parts to throw insert** checks. If exceeded, data inserts into the table will neither slow down nor get rejected.
The minimum value is `0` and the default is `1073741824` (1 GB). Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Max bytes to merge at max space in pool**: Maximum total size of data parts, in bytes, to merge when the background pool has available resources.
The default value is `161061273600` (150 GB). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse®](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#max-bytes-to-merge-at-max-space-in-pool) guide.
* **Max bytes to merge at min space in pool**: Maximum total size of data parts to merge when the background pool has minimum available resources.
The default value is `1048576` (1 MB). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#max-bytes-to-merge-at-min-space-in-pool).
* **Max cleanup delay period**: Maximum period, in seconds, to clean up outdated data, such as query logs, data block and data part hashes.
Specify a value not smaller than that of **Cleanup delay period**.
The default value is `300`.
* **Max merge selecting sleep ms**: Maximum timeout, in milliseconds, before trying to select parts to merge.
Specify a value not smaller than that of **Merge selecting sleep ms**.
The default value is `60000` (one minute). If you specify a smaller value, selecting tasks will frequently run in the background task pool, which will result in a large amount of requests to ZooKeeper in large-scale clusters.
* **Max number of merges with ttl in pool**: Maximum number of TTL-based merges in the background pool.
The default value is `2`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Max parts in total**: Number of active data parts in all table partitions, exceeding which will trigger ClickHouse® to throw the `Too many parts ...` exception.
For ClickHouse® 25.1 or lower, the default value is `100000`; for 25.2 or higher, `20000`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#max-parts-in-total).
* **Max replicated merges in queue**: Maximum number of concurrent merge jobs in a `ReplicatedMergeTree` queue.
The default value is `16`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Merge max block size**: Number of rows in the blocks forming the data parts for merging.
The database reads rows from these data parts, then merges those rows and writes them to a new data part. The rows being read are stored in RAM, so this setting affects its size required to merge rows. The smaller the value, the less RAM is available, which slows down row merging.
The default value is `8192`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#merge-max-block-size).
* **Merge selecting sleep ms**: Timeout, in milliseconds, before trying to select data parts to merge after no parts were selected.
The default value is `5000` (five seconds). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#merge_selecting_sleep_ms).
* **Merge with recompression TTL timeout**: Minimum timeout, in seconds, before merges with recompression of data with expired TTL.
The default value is `14400` (four hours). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/guides/developer/ttl#triggering-ttl-events).
* **Merge with TTL timeout**: Minimum timeout, in seconds, before merges to delete data with expired TTL.
The default value is `14400` (four hours). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/guides/developer/ttl#triggering-ttl-events).
* **Min age to force merge on partition only**: Sets whether to only merge [partitions](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/custom-partitioning-key) based on the **Min age to force merge seconds** setting value.
Forcing merges on partitions only is disabled by default. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#min_age_to_force_merge_on_partition_only).
* **Min age to force merge seconds**: Minimum time, in seconds, before a data part is merged.
The default value is `0` (merging is disabled). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#min_age_to_force_merge_seconds).
* **Min bytes for wide part**: Minimum number of bytes a data part must have to be stored in `Wide` format. You can set it together with **Min rows for wide part**.
If a data part has fewer bytes than the set value, it will be stored in `Compact` format.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage).
* **Min rows for wide part**: Minimum number of rows a data part must have to be stored in `Wide` format. You can set it together with **Min bytes for wide part**.
If the number of rows in a data part is fewer than the set value, it will be stored in `Compact` format.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage).
* **Number of free entries in pool to execute mutation**: Threshold for free entries in the pool. If the number of entries in the pool falls below this value, ClickHouse® stops running [mutations](https://clickhouse.com/docs/enen/sql-reference/statements/alter#mutations). Thus, you can have free threads for merges and avoid the `Too many parts ...` exception.
The default value is `20`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#number-of-free-entries-in-pool-to-execute-mutation).
* **Number of free entries in pool to lower max size of merge**: Threshold for free entries in the pool. If the number of entries in the pool falls below this value, ClickHouse® reduces the maximum size of a data part to merge. This helps handle small merges faster.
The default value is `8`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Parts to delay insert**: Number of active table data parts, exceeding which will trigger ClickHouse® to throttle the speed of table data inserts. An active data part is a new data part you get from a merge.
For ClickHouse® 24.12 or lower, the default value is `150`; for 25.1 or higher, `1000`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#parts-to-delay-insert).
* **Parts to throw insert**: Threshold for active table data parts, exceeding which will trigger ClickHouse® to throw the `Too many parts ...` exception.
For ClickHouse® 24.12 or lower, the default value is `300`; for 25.1 or higher, `3000`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#parts-to-throw-insert).
* **Replicated deduplication window**: Number of blocks for recent hash inserts that ZooKeeper will store. Deduplication only works for the most recently inserted data. Old blocks will be deleted.
The default value is `100`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#replicated-deduplication-window).
* **Replicated deduplication window seconds**: Time interval during which ZooKeeper stores blocks of recent hash inserts. Deduplication only works for the most recently inserted data. Old blocks will be deleted.
The default value is `604800`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/merge-tree-settings#replicated-deduplication-window-seconds).
* **TTL only drop parts**: Sets data drop mode based on TTL:
* `true`: Drop complete data parts.
* `false`: Drop data row by row with additional data merges. This mode uses much more resources than dropping complete data parts.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#ttl_only_drop_parts).
* **Metric log enabled**{#setting-metric-log-enabled} Management console CLI Terraform
Sets whether to log metric values from the `system.metrics` and `system.events` tables to the `system.metric_log` table.
Default value is `true`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Metric log retention size**{#setting-metric-log-retention-size} Management console CLI Terraform
Size of the `system.metric_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `536870912` (0.5 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Metric log retention time**{#setting-metric-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.metric_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Opentelemetry span log enabled**{#setting-opentelemetry-span-log-enabled} Management console
Sets whether to log trace and metric values from a distributed application. Such logs will be saved to the `system.opentelemetry_span_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/opentelemetry).
* **Opentelemetry span log retention size**{#setting-opentelemetry-span-log-retention-size} Management console CLI API
Size of the `system.opentelemetry_span_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Opentelemetry span log retention time**{#setting-opentelemetry-span-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.opentelemetry_span_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Part log retention size**{#setting-part-log-retention-size} Management console CLI Terraform
Size of the `system.part_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `536870912` (0.5 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Part log retention time**{#setting-part-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.part_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Processors profile log enabled**{#setting-processors-profile-log-enabled} Management console CLI
Sets whether to log information about processor-level profiling. Such logs will be saved to the `system.processors_profile_log` table.
The default value is:
* For ClickHouse® versions 25.2 or higher: `true`.
* For ClickHouse® versions 25.1 or lower: `false`.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Processors profile log retention size**{#setting-processors-profile-log-retention-size} Management console CLI
Maximum size of the `system.processors_profile_log` table, in bytes, exceeding which will trigger automatic deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Processors profile log retention time**{#setting-processors-profile-log-retention-time} Management console CLI
Time interval, in milliseconds, between adding a record to the `system.processors_profile_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Query cache**{#setting-query-cache} Management console CLI Terraform API
[Query cache](https://clickhouse.com/docs/enen/operations/query-cache) settings. The cache allows you to run `SELECT` queries only once and use the saved results in subsequent operations. This reduces latency and cluster resource consumption when processing queries.
The available query cache settings include:
* **Max entries**: Maximum number of `SELECT` query results that can be cached. The default value is `1024`.
* **Max entry size in bytes**: Maximum number of bytes to allocate in the cache for each `SELECT` query result. The default value is `1048576` (1 MB).
* **Max entry size in rows**: Maximum number of rows to allocate in the cache for each `SELECT` query result. The default value is `30000000`.
* **Max size in bytes**: Maximum cache size, in bytes. If set to `0`, the query cache will be disabled. The default value is `1073741824` (1 GB).
Read more about the settings in [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#server_configuration_parameters_query-cache).
* **Query log retention size**{#setting-query-log-retention-size} Management console CLI Terraform
Size of the `system.query_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `1073741824` (1 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Query log retention time**{#setting-query-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.query_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Query masking rules**{#setting-query-masking-rules} Management console CLI Terraform API
[Query masking](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#query-masking-rules) settings. Use these to apply regular expressions to queries and messages stored in logs. This makes data logging secure, as regular expressions help prevent leakage of sensitive data, such as names, emails, or bank card details.
The available settings for the query masking rules include:
* **Name**: Rule name. This is an optional setting.
* **Regexp**: RE2 regular expression.
* **Replace**: Expression to replace the sensitive data. Six asterisks are used by default.
* **Query thread log enabled**{#setting-query-thread-log-enabled} Management console CLI Terraform API
Activates the `system.query_thread_log` table to log query threads in it. The logging also requires enabling the [Log query threads](settings-list.md#setting-log-query-threads) setting.
The default value is `true`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/query_thread_log).
* **Query thread log retention size**{#setting-query-thread-log-retention-size} Management console CLI Terraform
Size of the `system.query_thread_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `536870912` (0.5 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Query thread log retention time**{#setting-query-thread-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.query_thread_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Query views log enabled**{#setting-query-views-log-enabled} Management console CLI API
Sets whether to log information about dependent views executed when running queries. Such logs will be saved to the `system.query_views_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/query_views_log).
* **Query views log retention size**{#setting-query-views-log-retention-size} Management console CLI API
Size of the `system.query_views_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Query views log retention time**{#setting-query-views-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.query_views_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Rabbitmq**{#setting-rabbitmq} Management console CLI API Terraform
Global authentication settings for [integration with RabbitMQ](https://clickhouse.com/docs/enen/engines/table-engines/integrations/rabbitmq):
* **Password**: RabbitMQ account password.
* **Username**: RabbitMQ account name.
* **Vhost**: RabbitMQ virtual host address.
Changing these settings will restart ClickHouse® servers on the cluster hosts.
* **Session log enabled**{#setting-session-log-enabled} Management console CLI API
Sets whether to log information about successful and failed login/logout events. Such logs will be saved to the `system.session_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/session_log).
* **Session log retention size**{#setting-session-log-retention-size} Management console CLI API
Size of the `system.session_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Session log retention time**{#setting-session-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.session_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Text log enabled**{#setting-text-log-enabled} Management console CLI Terraform
Sets whether to write system logs. Such logs will be saved to the `system.text_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Text log level**{#setting-text-log-level} Management console CLI Terraform
Event logging level in the [system.text_log](https://clickhouse.com/docs/enen/operations/system-tables/text_log) table. At each next level, the log will contain complete information from the previous one:
* `ERROR`: Information about DBMS errors.
* `WARNING`: Information about events that may result in DBMS errors.
* `INFORMATION`: Confirmations and information about events that do not result in DBMS errors.
* `DEBUG`: System information for debugging.
* `TRACE`: All available information on the DBMS operation.
The default value is `TRACE`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Text log retention size**{#setting-text-log-retention-size} Management console CLI Terraform
Size of the `system.text_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `536870912` (0.5 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Text log retention time**{#setting-text-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.text_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Timezone**{#setting-timezone} Management console CLI API Terraform
Server time zone. It is specified by the IANA identifier as the UTC time zone or geographical location, e.g., Africa/Abidjan.
Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#server_configuration_parameters-timezone).
* **Total memory profiler step**{#setting-total-memory-profiler-step} Management console API
RAM, in bytes, for a stack trace at each memory allocation step. Data is stored in the `system.trace_log` table. The `query_id` value is an empty string.
The default value is `4194304` (4 MB). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#total-memory-profiler-step).
* **Total memory tracker sample probability**{#setting-total-memory-tracker-sample-probability} Management console
Percentage probability of logging information about accidental memory allocation and release. Such log records are stored in the `system.trace_log` table. The `trace_type` parameter takes the value of the `MemorySample` parameter. The probability is valid for each memory allocation or release event, regardless of the allocation size. Information is only selected when the amount of untracked memory exceeds the [Total memory profiler step](#setting-total-memory-profiler-step) setting value.
By default, accidental memory allocations and releases are not logged (`0`). Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#total-memory-tracker-sample-probability).
* **Trace log enabled**{#setting-trace-log-enabled} Management console CLI Terraform
Sets whether to log stack traces collected by the query profiler. Stack traces are saved to the `system.trace_log` table.
The default value is `true`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Trace log retention size**{#setting-trace-log-retention-size} Management console CLI Terraform
Size of the `system.trace_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `536870912` (0.5 GB). When set to `0`, old records will not be deleted irrespective of the table size.
* **Trace log retention time**{#setting-trace-log-retention-time} Management console CLI Terraform
Time interval, in milliseconds, between adding a record to the `system.trace_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
* **Uncompressed cache size**{#setting-uncompressed-cache-size} Management console CLI API Terraform
Cache size, in bytes, for uncompressed data used by the [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table engines.
The default value is `8589934592` (8 GB). Changing this setting will restart ClickHouse® servers on the cluster hosts.
* **Zookeeper log enabled**{#setting-zookeeper-log-enabled} Management console CLI API
Sets whether to log ZooKeeper server request and response parameters. Such logs will be saved to the `system.zookeeper_log` table.
The default value is `false`. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/system-tables/zookeeper_log).
* **Zookeeper log retention size**{#setting-zookeeper-log-retention-size} Management console CLI API
Size of the `system.zookeeper_log` table, in bytes, exceeding which will trigger deletion of old records from that table.
The default value is `0`, i.e., old records will not be deleted irrespective of the table size.
* **Zookeeper log retention time**{#setting-zookeeper-log-retention-time} Management console CLI API
Time interval, in milliseconds, between adding a record to the `system.zookeeper_log` table and deleting the record. This value must be a multiple of 1,000.
The default value is `2592000000` (30 days). If `0`, the records will be stored indefinitely.
## Query-level settings {#user-level-settings}
### DBMS settings {#dbms-user-settings}
These settings apply at the individual user level.
{% note info %}
To reset to default values using Terraform, specify these values explicitly for the settings in the manifest. Deleting settings from the manifest will not reset them to their defaults.
{% endnote %}
The following settings are available:
* **Add HTTP CORS header**{#setting-add-http-cors-header} Management console CLI API SQL
Adds a CORS header to HTTP responses.
By default, no CORS header is included in HTTP responses.
* **Allow DDL**{#setting-allow-ddl} All interfaces
Sets whether to run DDL queries (`CREATE`, `ALTER`, `RENAME`, and others).
By default, DDL queries are allowed.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/permissions-for-queries#settings_allow_ddl).
Related to the [Readonly](#setting-readonly) setting.
* **Allow introspection functions**{#setting-allow-introspection-functions} Management console API SQL
Enables [introspection functions](https://clickhouse.com/docs/enen/sql-reference/functions/introspection) for query profiling.
The possible values are:
* `0`: Introspection functions are disabled.
* `1`: Introspection functions are enabled.
The default value is `0`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-allow_introspection_functions).
* **Allow suspicious low cardinality types**{#setting-allow-suspicious-low-cardinality-types} Management console API SQL
Allows using the [LowCardinality](https://clickhouse.com/docs/enen/sql-reference/data-types/lowcardinality) data type with fixed-size data types of up to 8 bytes.
The possible values are:
* `0`: Restricted use of `LowCardinality`.
* `1`: Unrestricted use of `LowCardinality`.
The default value is `0`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#allow_suspicious_low_cardinality_types).
* **Any join distinct right table keys**{#setting-any-join-distinct-right-table-keys} Management console SQL
Enables the deprecated behavior of the ClickHouse® server for `ANY INNER|LEFT JOIN`.
By default, the deprecated `JOIN` behavior is disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#any_join_distinct_right_table_keys).
* **Async insert**{#setting-async-insert} Management console API SQL
Enables/disables asynchronous inserts. It only works for inserts over HTTP, without deduplication.
If this setting is enabled, data is inserted into a table in batches. This allows making small and frequent inserts in ClickHouse® (up to 15,000 queries per second) without using intermediate tables.
The possible values are:
* `0`: Inserts are synchronous, one query after another.
* `1`: Multiple asynchronous inserts are enabled.
The default value is `0`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async-insert).
* **Async insert busy timeout**{#setting-async-insert-busy-timeout} Management console API SQL
Maximum data insert timeout, in milliseconds, after the first `INSERT` query.
The default value is `200`. If `0`, there is no timeout.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async-insert-busy-timeout-ms).
* **Async insert max data size**{#setting-async-insert-max-data-size} Management console API SQL
Maximum size of raw data, in bytes, collected per query before inserting it.
The default value is `1000000`. If `0`, asynchronous inserts are disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async-insert-max-data-size).
* **Async insert stale timeout**{#setting-async-insert-stale-timeout} Management console API SQL
Maximum data insert timeout, in milliseconds, after the last `INSERT` query. For non-zero values, [**Async insert busy timeout**](#setting-async-insert-busy-timeout) will increase with each `INSERT` query until the [**Async insert max data size**](#setting-async-insert-max-data-size) value is exceeded.
The default value is `0`. If `0`, there is no timeout.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async-insert-stale-timeout-ms).
* **Async insert threads**{#setting-async-insert-threads} Management console API SQL
Maximum number of threads for data background processing and inserts.
The default value is `16`. If `0`, asynchronous inserts are disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async-insert-threads).
* **Async insert use adaptive busy timeout**{#setting-async-insert-use-adaptive-busy-timeout} Management console CLI SQL
Sets whether to use an adaptive asynchronous insert, where ClickHouse® limits the number of inserts depending on the server load.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#async_insert_use_adaptive_busy_timeout).
* **Cancel HTTP readonly queries on client close**{#setting-cancel-http-readonly-queries-on-client-close} Management console API SQL
If enabled, the service cancels HTTP readonly queries, e.g., SELECT, when the client aborts a connection before receiving a response.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#cancel-http-readonly-queries-on-client-close).
* **Compile expressions**{#setting-compile-expressions} Management console CLI API SQL
Sets whether to compile expressions when running queries. With compilation enabled, queries that use identical expressions may run faster by using compiled expressions.
Use this setting in combination with [Min count to compile expression](#setting-min-count-to-compile-expression).
Expression compilation is disabled by default.
* **Connect timeout**{#setting-connect-timeout} Management console CLI API SQL
Connection timeout, in milliseconds.
The minimum value is `1`, the default value is `10000` (ten seconds).
* **Connect timeout with failover**{#setting-connect-timeout-with-failover} Management console API SQL
Remote server connection timeout, in milliseconds, for the `Distributed` table engine if a cluster uses sharding and replication.
If unable to connect to the server, the system will retry connecting to its replicas.
The default value is `50`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#connect-timeout-with-failover-ms).
* **Count distinct implementation**{#setting-count-distinct-implementation} Management console CLI API
Sets which `uniq*` function runs `COUNT(DISTINCT …)`:
* [uniq](https://clickhouse.com/docs/enen/sql-reference/aggregate-functions/reference/uniq#agg_function-uniq)
* [uniqCombined](https://clickhouse.com/docs/enen/sql-reference/aggregate-functions/reference/uniqcombined#agg_function-uniqcombined)
* [uniqCombined64](https://clickhouse.com/docs/enen/sql-reference/aggregate-functions/reference/uniqcombined64#agg_function-uniqcombined64)
* [uniqHLL12](https://clickhouse.com/docs/enen/sql-reference/aggregate-functions/reference/uniqhll12#agg_function-uniqhll12)
* [uniqExact](https://clickhouse.com/docs/enen/sql-reference/aggregate-functions/reference/uniqexact#agg_function-uniqexact)
By default, the `uniqExact` function is used.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#count_distinct_implementation).
* **Data type default nullable**{#setting-data-type-default-nullable} Management console CLI SQL
Sets whether to use the `Nullable` data type by default in column definition without explicit `NULL` or `NOT NULL` modifiers.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#data_type_default_nullable).
* **Date time input format**{#setting-date-time-input-format} Management console SQL
Sets which parser to use for a text representation of date and time when processing the input format:
* `best_effort`: Extended parser.
* `basic`: Basic parser.
By default, the `basic` parser is used.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-date_time_input_format).
* **Date time output format**{#setting-date-time-output-format} Management console SQL
Sets the output format for a text representation of date and time:
* `simple`: Simple format.
* `iso`: ISO format.
* `unix_timestamp`: Unix format.
The default format is `simple`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-date_time_output_format).
* **Deduplicate blocks in dependent materialized views**{#setting-deduplicate-blocks-in-dependent-materialized-views} Management console SQL
Enables checks for deduplication in materialized views that get data from replicated tables.
By default, the check is disabled (`0`).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-deduplicate-blocks-in-dependent-materialized-views).
* **Distinct overflow mode**{#setting-distinct-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if the amount of data when running a `SELECT DISTINCT` query [exceeds the limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Distributed aggregation memory efficient**{#setting-distributed-aggregation-memory-efficient} Management console CLI API SQL
Sets whether to enable memory saving mode for distributed aggregation.
For distributed query processing, remote servers perform [external aggregation](#setting-max-bytes-before-external-group-by). Enable this setting to reduce the RAM consumption on the server initiating the query.
By default, memory saving mode is disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/select/group-by#select-group-by-in-external-memory).
* **Distributed ddl task timeout**{#setting-distributed-ddl-task-timeout} All interfaces
Sets the timeout for responses to DDL queries from all cluster hosts. If a DDL query is not run on all hosts, the response will contain the timeout error and the query will be run in asynchronous mode. The possible values are:
* Positive integer: Timeout is equal to this integer, in seconds.
* `0`: Asynchronous mode.
* Negative number: Infinite timeout.
The default value is `180`.
* **Distributed product mode**{#setting-distributed-product-mode} Management console CLI API SQL
Changes the behavior of distributed subqueries when a query contains the product of distributed tables:
* `deny`: Blocks the use of such subqueries.
* `local`: Replaces the database and table in a subquery with the destination server (shard) local database and table leaving the regular `IN/JOIN`.
* `global`: Replaces the `IN/JOIN` query with the `GLOBAL IN/GLOBAL JOIN` one.
* `allow`: Allows the use of such subqueries.
By default, no value is set (equivalent to `deny`).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#distributed-product-mode).
* **Do not merge across partitions select final** {#setting-do-not-merge-across-partitions-select-final} Management console CLI SQL
Sets whether to process table sections independently for `SELECT` queries with the `FINAL` operator.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/guides/replacing-merge-tree#exploiting-partitions-with-replacingmergetree).
* **Empty result for aggregation by empty set**{#setting-empty-result-for-aggregation-by-empty-set} Management console CLI API SQL
Sets the result format when aggregating data without keys (without `GROUP BY`) for an empty set (e.g., `SELECT count(*) FROM table WHERE 0`):
* Disabled (default): ClickHouse® returns a single-line result with `NULL` values for aggregation functions, in accordance with the SQL standard.
* Enabled: ClickHouse® returns an empty result.
* **Enable HTTP compression**{#setting-enable-http-compression} Management console CLI API SQL
Sets whether to compress data in an HTTP response.
By default, ClickHouse® stores data in a compressed format. The request output is not compressed. For ClickHouse® to compress a request output when sending it over HTTP, enable this setting and provide your compression method in the `Accept-Encoding` request header:
* `gzip`
* `br`
* `deflate`
By default, data compression in HTTP responses is disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/interfaces/http).
* **Enable reads from query cache**{#setting-enable-reads-from-query-cache} Management console CLI SQL
Sets whether to extract the results of `SELECT` queries from the query cache.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#enable_reads_from_query_cache).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Enable writes to query cache**{#setting-enable-writes-to-query-cache} Management console CLI SQL
Sets whether to save the results of `SELECT` queries to the query cache.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#enable_writes_to_query_cache).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Fallback to stale replicas for distributed queries**{#setting-fallback-to-stale-replicas-for-distributed-queries} Management console CLI API SQL
Forces a query to a stale replica if up-to-date data is unavailable.
ClickHouse® will select the most up-to-date stale replica in the table. Use this setting when running `SELECT` queries from distributed tables pointing to replicated tables.
By default, query forcing is enabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-fallback_to_stale_replicas_for_distributed_queries).
Related to the [Max replica delay for distributed queries](#setting-max-replica-delay-for-distributed-queries) setting.
* **Flatten nested**{#setting-flatten-nested} Management console API SQL
Sets the data format for [nested columns](https://clickhouse.com/docs/enen/sql-reference/data-types/nested-data-structures/nested).
The possible values are:
* `0`: Nested column is converted into an array of tuples.
* `1`: Nested column is converted into individual arrays.
The default value is `1`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#flatten-nested).
* **Force index by date**{#setting-force-index-by-date} Management console CLI API SQL
Blocks queries if you cannot use an index by date. This setting applies to tables of the [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) family.
By default, it is disabled, i.e., you can run queries.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-force_index_by_date).
* **Force primary key**{#setting-force-primary-key} Management console CLI API SQL
Blocks queries if you cannot use an index by primary key. This setting applies to tables of the [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) family.
By default, it is disabled, i.e., you can run queries.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-force_primary_key).
* **Format avro schema registry url**{#setting-format-avro-schema-registry-url} Management console CLI SQL
URL of the Confluent schema registry for AvroConfluent format.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/formats#format_avro_schema_registry_url).
* **Format regexp**{#setting-format-regexp} Management console SQL
Sets a regular expression in [re2 format](https://github.com/google/re2/wiki/Syntax) to apply to each row of imported data. The number of subtemplates (parenthetical groups) in the expression must be equal to the number of columns in the table the data is imported to. Use the `\n` or `\r\n` line break characters as delimiters; line breaks cannot be escaped. If a row does not match the regular expression, it is skipped.
No value is set by default.
* **Format regexp escaping rule**{#setting-format-regexp_escaping_rule} Management console SQL
Sets the escaping rule for the regular expression specified in the [Format regexp](#setting-format-regexp) setting:
* `CSV`
* `Escaped`
* `JSON`
* `Quoted`
* `Raw`
* `XML`
The default value is `Raw`, i.e., no escaping is used.
* **Format regexp skip unmatched**{#setting-format-regexp-skip-unmatched} Management console SQL
Outputs an error message if a row in imported data cannot be split by the template specified in the [Format regexp](#setting-format-regexp) setting.
By default, no message is output (`0`).
* **Group by overflow mode**{#setting-group-by-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if, during aggregation, the number of unique keys [exceeds the limit](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
* `any`: Run `GROUP BY` in fuzzy mode. The quality of this computation depends on the statistical properties of the data.
By default, no value is set (equivalent to `throw`).
* **Group by two level threshold**{#setting-group-by-two-level-threshold} Management console CLI API SQL
Sets the number of keys, reaching which triggers two-level aggregation.
The minimum value is `0`, i.e., the number is not set. The default value is `100000`.
* **Group by two level threshold bytes**{#setting-group-by-two-level-threshold-bytes} Management console CLI API SQL
Sets the number of bytes in the aggregated output, reaching which triggers two-level aggregation.
The minimum value is `0`, i.e., the number is not set. The default value is `50000000`.
* **Hedged connection timeout ms**{#setting-hedged-connection-timeout-ms} Management console CLI Terraform API SQL
Timeout, in milliseconds, for connecting to the replica to work with hedged requests. Use this setting in combination with [Use hedged requests](#setting-use-hedged-requests).
The default value is `50`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#hedged_connection_timeout_ms).
* **HTTP connection timeout**{#setting-http-connection-timeout} Management console CLI API SQL
Sets the HTTP connection timeout, in milliseconds.
The minimum value is `1`. The default value is `1000` (one second).
* **HTTP headers progress interval**{#setting-http-headers-progress-interval} Management console CLI API SQL
Sets the minimum interval, in milliseconds, between progress notifications with the `X-ClickHouse-Progress` HTTP header.
The minimum value is `1` and the default is `100`.
* **HTTP max field name size**{#setting-http-max-field-name-size} Management console CLI SQL
Maximum field name length in an HTTP header.
The default value is `131072`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#http_max_field_name_size).
* **HTTP max field value size**{#setting-http-max-field-value-size} Management console CLI SQL
Maximum field value length in an HTTP header.
The default value is `131072`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#http_max_field_value_size).
* **HTTP receive timeout**{#setting-http-receive-timeout} Management console CLI API SQL
Sets the timeout for receiving data over HTTP, in milliseconds.
The minimum value is `1`; the default value is `1800000` (30 minutes).
* **HTTP send timeout**{#setting-http-send-timeout} Management console CLI API SQL
Sets the timeout for sending data over HTTP, in milliseconds.
The minimum value is `1`; the default value is `1800000` (30 minutes).
* **Idle connection timeout**{#setting-idle-connection-timeout} Management console CLI Terraform API SQL
Timeout for closing idle TCP connections, in milliseconds.
If set to `0`, all connections are closed immediately.
The default value is `360000` (six minutes).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#idle_connection_timeout).
* **Ignore materialized views with dropped target table**{#setting-ignore-materialized-views-with-dropped-target-table} Management console CLI SQL
Sets whether to ignore materialized views with a deleted target table when switching to views.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#ignore_materialized_views_with_dropped_target_table).
* **Input format defaults for omitted fields**{#setting-input-format-defaults-for-omitted-fields} Management console CLI API SQL
Sets whether to write default values into omitted fields based on a column data type when inserting data with `INSERT`.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#session_settings-input_format_defaults_for_omitted_fields).
* **Input format import nested json**{#setting-input-format-import-nested-json} Management console
Sets whether to insert JSON data with nested objects.
Such data inserts are disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-input_format_import_nested_json).
* **Input format null as default**{#setting-input-format-null-as-default} Management console SQL
Sets whether to write default values in `NULL` cells if the column data type does not allow storing the `NULL` value.
This setting is enabled by default, i.e., `NULL` cells get default values.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-input-format-null-as-default).
* **Input format parallel parsing**{#setting-input-format-parallel-parsing} Management console
Sets whether to split incoming data into parts and parse each of them concurrently while preserving the original sequence. This setting is only supported for [TSV](https://clickhouse.com/docs/enen/interfaces/formats#tabseparated), [TKSV](https://clickhouse.com/docs/enen/interfaces/formats#tskv), [CSV](https://clickhouse.com/docs/enen/interfaces/formats#csv), and [JSONEachRow](https://clickhouse.com/docs/enen/interfaces/formats#jsoneachrow) formats.
By default, such splitting of incoming data is enabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#input-format-parallel-parsing).
* **Input format values interpret expressions**{#setting-input-format-values-interpret-expressions} Management console CLI API SQL
Enables the SQL parser if the stream parser is unable to parse the data. Use this setting when inserting values containing SQL expressions into the table.
For instance, the stream parser cannot parse a value containing `now()` while the SQL parser, if enabled, will do it correctly and insert the output of the `now()` SQL function, i.e., current date and time.
This setting is only used for [Values](https://clickhouse.com/docs/enen/interfaces/formats#data-format-values) format when inserting data.
By default, the SQL parser is enabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-input_format_values_interpret_expressions).
* **Input format with names use header**{#setting-input-format-with-names-use-header} Management console SQL
Sets whether to check the order of columns when inserting data.
By default, the check is enabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-input-format-with-names-use-header).
* **Insert keeper max retries**{#setting-insert-keeper-max-retries} Management console CLI API
The maximum number of retries for ClickHouse® Keeper (or ZooKeeper) requests during inserts to replicated [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables.
The minimum value is `0`, i.e., retries are disabled, and the default is `20`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#insert_keeper_max_retries).
* **Insert null as default**{#setting-insert-null-as-default} Management console API SQL
Enables inserting [default values](https://clickhouse.com/docs/enen/sql-reference/statements/create/table#create-default-values) instead of [NULL](https://clickhouse.com/docs/enen/sql-reference/statements/create/table#null-modifiers) into columns that do not allow storing `NULL` values.
The possible values are:
* `0`: Inserting `NULL` into a column that does not allow `NULL` will throw an exception.
* `1`: Insert the default column value instead of `NULL`.
The default value is `1`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#insert_null_as_default).
* **Insert quorum**{#setting-insert-quorum} Management console CLI API SQL
Manages a quorum write in a ClickHouse® cluster:
* If the value is less than 2, quorum writes are disabled.
* If the value is greater than or equal to 2, quorum writes are enabled.
Quorum writes ensure that ClickHouse® writes data error-free to the `Insert quorum` replicas during within a time period not exceeding [Insert quorum timeout](#setting-insert-quorum-timeout) and that data is not lost if one or multiple replicas fail. All replicas in the quorum are consistent, i.e., they contain data from all the previous `INSERT` queries.
You can use the [Select sequential consistency](#setting-select-sequential-consistency) setting to read data written with `Insert quorum`.
By default, quorum writes are disabled (`0`).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-insert_quorum).
* **Insert quorum parallel**{#setting-insert-quorum-parallel} Management console SQL
If this setting is enabled, you can run multiple concurrent `INSERT` queries with quorum writes. If disabled, a single `INSERT` query with a quorum write will be made to the same table.
This setting is enabled by default.
* **Insert quorum timeout**{#setting-insert-quorum-timeout} Management console CLI API SQL
Sets the [quorum write](#setting-insert-quorum) timeout, in milliseconds. If the timeout ends without a write, ClickHouse® will abort the `INSERT` query and return an error.
The minimum value is `1000` (one second). The default value is `60000` (one minute).
* **Join algorithm**{#setting-join-algorithm} Management console SQL
Sets the `JOIN` algorithm:
* `auto`: Hash join. However, if the server is running out of memory, ClickHouse® will attempt a merge join.
* `direct`: Join by searching the right-hand table rows, which serve as keys in the left-hand table. Joins are only possible if these two conditions are met:
* Storage in the right-hand table supports key-value queries, e.g., [Dictionary](https://clickhouse.com/docs/enen/engines/table-engines/special/dictionary#dictionary) or [EmbeddedRocksDB](https://clickhouse.com/docs/enen/engines/table-engines/integrations/embedded-rocksdb).
* `LEFT` and `INNER JOIN` clauses are running.
* `hash`: Hash join.
* `parallel_hash`: Type of hash join where data is split into segments and multiple hash tables are created at the same time instead of a single one.
* `partial_merge`: Type of sort-merge join where only the right-hand side of the table is completely sorted.
* `prefer_partial_merge`: `partial_merge` algorithm. It is used whenever possible; otherwise, the `hash` algorithm is used.
* `sorting_merge`: Sort-merge join.
For ClickHouse® 24.11 or lower, the default value is `direct,auto`; for 24.12 or higher, `direct,parallel_hash,hash`. An available algorithm will be selected for a specific query depending on the table type and engine.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-join_algorithm).
* **Join overflow mode**{#setting-join-overflow-mode} All interfaces
Sets the action ClickHouse® will perform if any of the following `JOIN` limits is reached:
* `max_bytes_in_join`
* `max_rows_in_join`
The possible values are:
* `throw`: ClickHouse® throws an exception and terminates the operation.
* `break`: ClickHouse® terminates the operation without throwing an exception.
The default action is `throw`.
* **Join use nulls**{#setting-join-use-nulls} Management console CLI SQL
Controls the behavior of `JOIN` clauses. If the setting is enabled, empty cells resulting from a join will get `NULL` values. Otherwise, the cells will get the default values based on the specific field type.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#join_use_nulls).
* **Joined subquery requires alias**{#setting-joined-subquery-requires-alias} Management console CLI SQL
Requires aliases for subqueries when running the `JOIN` clause.
If the setting is enabled, such a query will not be run:
```sql
SELECT col1, col2 FROM table1 JOIN (SELECT col3 FROM table2)
```
The query with the specified alias will be successfully run:
```sql
SELECT col1, col2 FROM table1 JOIN (SELECT col3 FROM table2) AS MyQuery
```
This setting is enabled by default.
* **Load balancing**{#setting-load-balancing} Management console CLI Terraform API SQL
Sets the replica selection algorithm used to send and process distributed queries. ClickHouse® counts the number of errors per replica and selects the one with the lowest value. If multiple replicas have the same number of errors, the **Load balancing** setting determines the order to sort the replicas in and which replica to select.
The possible values are:
* `random`: Sends the query to a random replica. This is a default value.
Its disadvantage is that host proximity is ignored.
* `nearest_hostname`: Sends the query to the replica whose host name is the closest possible match to the server host name in the ClickHouse® configuration file. Let's assume you have a server named `example-05-05-5` and two replicas, `example-05-05-7` and `example-05-06-7`. The `example-05-05-7` replica will be selected, as its name is only one character different from the server name.
With this approach, distributed queries will be more likely sent to the same replicas located in proximity to each other. It eliminates the disadvantage of the `random` value.
* `hostname_levenshtein_distance`: To select a replica, the same principle applies as for `nearest_hostname`. However, the appropriate replica host name is determined by the [Levenshtein distance](https://en.wikipedia.org/wiki/Levenshtein_distance).
* `in_order`: Sends the query to the replica in the order set in the ClickHouse® configuration file. In a Managed Service for ClickHouse® cluster, the replica located in the same availability zone as the host initiating distributed subqueries is the first one in the order.
Its disadvantage is that if the first replica fails, its workload will be placed on the next replica, which will double that replica’s workload.
* `first_or_random`: Sends the query to the first replica similarly to `in_order`. However, it has the advantage over the `in_order` algorithm: if the first replica in the list fails, a random replica is selected.
* `round_robin`: Applies the [Round-Robin](https://en.wikipedia.org/wiki/Round-robin_scheduling) policy to select a replica. It is only used with queries supporting this policy.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-load_balancing).
* **Local filesystem read method**{#setting-local-filesystem-read-method} Management console
Sets how to read data from the local file system.
The possible values are:
* `mmap`
* `pread`
* `pread_threadpool`
* `read`
The default value is `pread`.
* **Log processors profiles**{#setting-log-processors-profiles} Management console CLI SQL
Sets whether to log information about processor-level profiling. Such logs will be saved to the `system.processors_profile_log` table.
This setting is disabled by default. Changing this setting will restart ClickHouse® servers on the cluster hosts.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#log_processors_profiles).
* **Log queries probability**{#setting-log-queries-probability} CLI SQL
Sets whether to write only randomly selected queries with a specified probability to the `system.query_log`, `system.query_thread_log`, and `system.query_views_log` tables. Random selection reduces the load in case of large number of queries per second.
This setting is a positive floating-point number within the [0..1] range. For example, if set to `0,5`, approximately half of the queries will be written to the system tables. If set to `0`, no queries will be written to the system tables. If set to `1` (by default), all queries will be written to the system tables.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#log_queries_probability).
* **Log query threads**{#setting-log-query-threads} Management console CLI Terraform API SQL
Enables logging of threads which run queries. Such logs will be saved to the [system.query_thread_log](https://clickhouse.com/docs/enen/operations/system-tables/query_thread_log) table.
This setting only applies when the [Query thread log enabled](settings-list.md#setting-query-thread-log-enabled) setting is enabled. The setting is disabled by default.
It only applies for some users or queries.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-log-query-threads).
* **Log query views**{#setting-log-query-views} Management console CLI SQL
Sets whether to log information about dependent views in queries. Such logs will be saved to the `system.query_views_log` table.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#log_query_views).
* **Low cardinality allow in native format**{#setting-low-cardinality-allow-in-native-format} Management console CLI API SQL
Sets whether to use [LowCardinality type](https://clickhouse.com/docs/enen/sql-reference/data-types/lowcardinality) in native format:
* If this setting is enabled (by default), use native format.
* If the setting is disabled, do not use native format:
* For `SELECT` queries, convert LowCardinality columns to regular ones.
* For `INSERT` queries, convert regular columns to LowCardinality.
Columns of this type enable you to store data more efficiently as hash tables. Wherever possible, ClickHouse® uses LowCardinality columns.
Some third-party clients for ClickHouse® do not support LowCardinality columns and cannot correctly interpret the output of a query that contains columns of this type. Disabling this setting enables such clients to process query outputs correctly.
The official ClickHouse® client supports LowCardinality columns.
This setting is enabled by default.
* **Max ast depth**{#setting-max-ast-depth} Management console CLI API SQL
Maximum nesting depth of a syntax tree.
For complex queries, the syntax tree may be too deep. This setting enables you to block unnecessarily complex or unoptimized queries for large tables.
For example, a `SELECT *` query will mostly result in a deeper and more complex syntax tree than a `SELECT ... WHERE ...` query with constraints and conditions.
The default value is `1000`. If you set the value too small, ClickHouse® may not be able to run most queries.
* **Max ast elements**{#setting-max-ast-elements} Management console CLI API SQL
Maximum size of the query syntax tree, i.e., the number of tree nodes.
For complex queries, the syntax tree may contain too many elements. This setting enables you to block unnecessarily complex or unoptimized queries for large tables.
The default value is `50000`. If you set the value too small, ClickHouse® may not be able to run most queries.
* **Max block size**{#setting-max-block-size} Management console CLI API SQL
Data in ClickHouse® is processed by blocks, i.e., sets of column parts. This setting specifies the recommended block size (number of rows) that will be loaded when processing tables. You are charged for processing each block, so setting a small value can slow down processing.
The minimum value is `1` and the default is `65536`.
* **Max bytes before external group by**{#setting-max-bytes-before-external-group-by} Management console CLI API SQL
Data accumulated during the `GROUP BY` aggregation is stored in RAM for a certain period of time. This parameter sets a threshold, in bytes, exceeding which will trigger data flush to disk to save RAM.
Use this setting if your queries terminate with an error because there is not enough RAM to aggregate a large amount of data. If this is the case, set this parameter to a non-zero value to get ClickHouse® to flush data to disk and perform successful aggregation.
The minimum value is `0` (`GROUP BY` is disabled in external memory). The default value is `0`.
When using aggregation in the external memory, we recommend setting the value twice as low as [Max memory usage](#setting-max-memory-usage). By default, the maximum memory usage is limited to 10 GB.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/select/group-by#select-group-by-in-external-memory).
Related to the [Distributed aggregation memory efficient](#setting-distributed-aggregation-memory-efficient) setting.
* **Max bytes before external sort**{#setting-max-bytes-before-external-sort} Management console CLI API SQL
The amount of RAM you can use for `ORDER BY`. When this value is exceeded, external sorting is used.
The minimum value is `0`, unlimited. The default value is `0`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#max_bytes_before_external_sort).
* **Max bytes in distinct**{#setting-max-bytes-in-distinct} Management console CLI API SQL
Maximum amount of uncompressed data, in bytes, occupied by a hash table when using `DISTINCT`.
The minimum and default value is `0`, i.e., there is no limit.
* **Max bytes in join**{#setting-max-bytes-in-join} All interfaces
Maximum amount of uncompressed data, in bytes, occupied by a hash table when using `JOIN`.
The minimum and default value is `0`, i.e., there is no limit.
* **Max bytes in set**{#setting-max-bytes-in-set} All interfaces
Maximum amount of uncompressed data, in bytes, occupied by a set created from a subquery in the `IN` section.
The minimum and default value is `0`, i.e., there is no limit.
* **Max bytes ratio before external group by**{#setting-max-bytes-ratio-before-external-group-by} Management console SQL
Query memory share available for `GROUP BY`. When this value is exceeded, external memory is used.
The possible values range from `0` (no limit) to `1`.
The default value depends on the ClickHouse® version:
* For versions below `25.1`: `0`.
* For versions `25.1` and higher: `0,5`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#max_bytes_ratio_before_external_group_by).
* **Max bytes ratio before external sort**{#setting-max-bytes-ratio-before-external-sort} Management console SQL
Query memory share available for `ORDER BY`.
This setting, along with the available memory limit set in [Max bytes before external sort](#setting-max-bytes-before-external-sort), restricts in-memory sorting. If the available query memory share is exceeded and the sort block size is greater than **Max bytes before external sort**, external sorting is used.
The possible values range from `0` (no limit) to `1`.
The default value depends on the ClickHouse® version:
* For versions below `25.1`: `0`.
* For versions `25.1` and higher: `0,5`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#max_bytes_ratio_before_external_sort).
* **Max bytes to read**{#setting-max-bytes-to-read} Management console CLI API SQL
Maximum amount of uncompressed data, in bytes, that can be read from a table when running a query.
The minimum and default value is `0`, i.e., there is no limit.
* **Max bytes to sort**{#setting-max-bytes-to-sort} Management console CLI API SQL
Maximum amount of uncompressed data, in bytes, that can be read from a table before sorting. This setting helps limit RAM usage for sorting.
The minimum and default value is `0`, i.e., there is no limit.
* **Max bytes to transfer**{#setting-max-bytes-to-transfer} Management console CLI API SQL
Maximum amount of uncompressed data, in bytes, that can be sent to a remote server or saved to a temporary table when using `GLOBAL IN`.
The minimum and default value is `0`, i.e., there is no limit.
* **Max columns to read**{#setting-max-columns-to-read} Management console CLI API SQL
Maximum number of columns that can be read from a table in a single query. Queries that require reading more columns will terminate with an error.
The minimum and default value is `0`, i.e., there is no limit.
* **Max concurrent queries for user**{#setting-max-concurrent-queries-for-user} Management console API SQL
Maximum number of concurrently processed user queries to a [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table.
The minimum value is `0`, i.e., there is no limit. The default value is `450`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/server-configuration-parameters/settings#max-concurrent-queries-for-user).
* **Max execution time**{#setting-max-execution-time} Management console CLI API SQL
Maximum query run time, in milliseconds.
During sorting or merging and finalizing aggregations, the limit on the maximum query run time will not be checked and may be exceeded.
The minimum value is `0`, i.e., there is no limit. The default value is `600000`.
* **Max expanded ast elements**{#setting-max-expanded-ast-elements} Management console CLI API SQL
Maximum query syntax tree size (number of tree nodes) after expansion of aliases and the asterisk.
For complex queries, the syntax tree may contain too many elements. This setting enables you to block unnecessarily complex or unoptimized queries for large tables.
The default value is `500000`. If you set the value too small, ClickHouse® may not be able to run most queries.
* **Max final threads**{#setting-max-final-threads} Management console
Maximum number of parallel threads for a `SELECT` query with the [FINAL](https://clickhouse.com/docs/enen/sql-reference/statements/select/from#select-from-final) modifier.
The default value is equal to [Max threads](#setting-max-threads).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#max-final-threads).
* **Max HTTP get redirects**{#setting-max-http-get-redirects} Management console API SQL
Sets the maximum number of redirect hops in [URL engine](https://clickhouse.com/docs/enen/engines/table-engines/special/url) tables for HTTP GET requests.
If `0`, redirect hops are not allowed.
The default value is `0`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#setting-max_http_get_redirects).
* **Max insert block size**{#setting-max-insert-block-size} Management console CLI API SQL
Sets the size, in bytes, for blocks when inserting data into a table. This setting will only apply if a server creates such blocks.
The default value is `1048576`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-max_insert_block_size).
* **Max insert threads**{#setting-max-insert-threads} Management console CLI Terraform API SQL
Maximum number of threads for the `INSERT SELECT` query.
If the value is greater than `1`, the threads will run in parallel. The default value is `0`.
You can run `INSERT SELECT` queries in parallel only if using the [Max threads](#setting-max-threads) setting to enable running `SELECT` queries in parallel.
The greater the **Max insert threads** value is, the more cluster RAM is used.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-max-insert-threads).
* **Max memory usage**{#setting-max-memory-usage} Management console CLI API SQL
Maximum amount of RAM, in bytes, to run a query on a single server. This setting does not account for the amount of free memory or the total amount of a machine's memory. It applies to a single query on a single server.
The minimum and default value is `0`, i.e., there is no limit. The maximum value is limited by **Max server memory usage**, which you cannot change directly. For more information, see [Memory management in Managed Service for ClickHouse®](memory-management.md). If the set value is greater than **Max server memory usage**, the latter is used.
If you are using [Max bytes before external `GROUP BY`](#setting-max-bytes-before-external-group-by) or [Max bytes before external sort](#setting-max-bytes-before-external-sort), we recommend setting their values to half of **Max memory usage**.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#settings_max_memory_usage).
* **Max memory usage for user**{#setting-max-memory-usage-for-user} Management console CLI API SQL
Maximum amount of RAM, in bytes, to run user queries on a single server. This setting does not account for the amount of free memory or the total amount of a machine's memory.
It applies to all user queries that run concurrently on the same server, as opposed to [Max memory usage](#setting-max-memory-usage).
The minimum and default value is `0`, i.e., there is no limit.
* **Max network bandwidth**{#setting-max-network-bandwidth} Management console CLI API SQL
Maximum network data transmission rate for a single query, in bytes per second.
The minimum and default value is `0`, i.e., there is no limit.
* **Max network bandwidth for user**{#setting-max-network-bandwidth-for-user} Management console CLI API SQL
Maximum network data transmission rate, in bytes per second. This setting applies to all concurrent user queries, as opposed to [Max network bandwidth](#setting-max-network-bandwidth).
The minimum and default value is `0`, i.e., there is no limit.
* **Max parser depth**{#setting-max-parser-depth} Management console CLI API
Maximum recursion depth in the recursive descent parser. It allows you to control the stack size.
The minimum value is `0`, i.e., the recursion depth is not limited. The default value is `1000`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#max_parser_depth).
* **Max partitions per insert block**{#setting-partitions-per-insert-block} Management console SQL
Limits the maximum number of partitions per insert block.
The minimum value is `0`, i.e., the number of partitions is not limited. The default value is `100`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#max-partitions-per-insert-block).
* **Max query size**{#setting-max-query-size} Management console CLI API SQL
Limits the size of the largest part of a query, in bytes, that can be transferred to RAM for parsing using the SQL parser.
The minimum value is `1` and the default is `262144`.
* **Max read buffer size**{#setting-max-read-buffer-size} Management console
Maximum buffer size, in bytes, to read data from the file system.
The default value is `1048576` (1 MB).
* **Max replica delay for distributed queries**{#setting-max-replica-delay-for-distributed-queries} Management console CLI API SQL
Maximum replica delay, in milliseconds. If a replica delay is greater than this value, the replica is no longer used.
The minimum value is `1000` (one second). The default value is `300000` (five minutes).
Related to the [Fallback to stale replicas for distributed queries](#setting-fallback-to-stale-replicas-for-distributed-queries) setting.
* **Max result bytes**{#setting-max-result-bytes} Management console CLI API SQL
Maximum size of an uncompressed data query output, in bytes. It also applies to subqueries and to parts of distributed queries that run on remote servers.
The minimum and default value is `0`, i.e., there is no limit.
* **Max result rows**{#setting-max-result-rows} Management console CLI API SQL
Maximum number of output rows. It also applies to subqueries and to parts of distributed queries that run on remote servers.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows in distinct**{#setting-max-rows-in-distinct} Management console CLI API SQL
Limits the maximum number of distinct rows when using `DISTINCT`.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows in join**{#setting-max-rows-in-join} All interfaces
Maximum number of rows in a hash table used when joining tables. It applies to `SELECT… JOIN` operations and the `Join` table engine.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#settings-max_rows_in_join).
* **Max rows in set**{#setting-max-rows-in-set} All interfaces
Maximum number of rows for a set created from a subquery in the `IN` section.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows to group by**{#setting-max-rows-to-group-by} Management console CLI API SQL
Limits the maximum number of unique keys received from the aggregation function. Use this setting to limit RAM usage during aggregation.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows to read**{#setting-max-rows-to-read} Management console CLI API SQL
Maximum number of rows that can be read from a table when running a query.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows to sort**{#setting-max-rows-to-sort} Management console CLI API SQL
Maximum number of rows to sort. Use this setting to limit RAM usage during sorting.
The minimum and default value is `0`, i.e., there is no limit.
* **Max rows to transfer**{#setting-max-rows-to-transfer} Management console CLI API SQL
Maximum number of rows that can be sent to a remote server or saved in a temporary table when using `GLOBAL IN`.
The minimum and default value is `0`, i.e., there is no limit.
* **Max temporary columns**{#setting-max-temporary-columns} Management console CLI API SQL
Maximum number of temporary columns concurrently kept in RAM when running a query, including constant columns.
The minimum and default value is `0`, i.e., there is no limit.
* **Max temporary data on disk size for query**{#setting-max-temporary-data-on-disk-size-for-query} Management console CLI API
Maximum amount of data, in bytes, consumed by temporary files on the disk for all concurrently running queries.
The minimum value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#settings_max_temporary_data_on_disk_size_for_query).
* **Max temporary data on disk size for user**{#setting-max-temporary-data-on-disk-size-for-user} Management console CLI API
Maximum amount of data, in bytes, consumed by temporary files on the disk for all concurrently running user queries.
The minimum value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#settings_max_temporary_data_on_disk_size_for_user).
* **Max temporary non const columns**{#setting-max-temporary-non-const-columns} Management console CLI API SQL
Maximum number of temporary columns concurrently kept in RAM when running a query, excluding constant columns.
The minimum and default value is `0`, i.e., there is no limit.
* **Max threads**{#setting-max-threads} Management console CLI API SQL
Maximum number of query processing threads, excluding threads for reading data from remote servers. This setting applies to threads that perform the same stages of the query processing pipeline in parallel.
The minimum and default value is `0`, i.e., the value is calculated automatically as the number of physical CPU cores excluding hyper-threading cores.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-max_threads).
* **Memory overcommit ratio denominator**{#setting-memory-overcommit-ratio-denominator} Management console CLI API
[Memory overcommit](https://clickhouse.com/docs/enen/operations/settings/memory-overcommit) limit, in GB, when the strict memory usage limit is reached at the user level.
The minimum value is `0`, i.e., there is no limit. The default value is `1`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#memory_overcommit_ratio_denominator).
* **Memory overcommit ratio denominator for user**{#setting-memory-overcommit-ratio-denominator-for-user} Management console CLI API
[Memory overcommit](https://clickhouse.com/docs/enen/operations/settings/memory-overcommit) limit, in GB, when the strict memory usage limit is reached globally.
The minimum value is `0`, i.e., there is no limit. The default value is `1`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#memory_overcommit_ratio_denominator_for_user).
* **Memory profiler sample probability**{#setting-memory-profiler-sample-probability} Management console API SQL
The system will log information about specific memory allocation and deallocation to the `system.trace_log` file of the `MemorySample` tracing type with the specified probability. The logging probability does not depend on the size of the allocated/deallocated memory.
You can specify values between `0` and `1`. The default value is `0`.
* **Memory profiler step**{#setting-memory-profiler-step} Management console API SQL
Memory profiler step, in bytes. Whenever query memory usage increases every next step greater than the number of bytes specified in this setting, the memory profiler will save the allocated stack trace. A value less than several MB slows down query processing.
The default value is `4194304` (4 MB). If `0`, the memory profiler is disabled.
* **Memory usage overcommit max wait microseconds**{#setting-memory-usage-overcommit-max-wait-microseconds} Management console CLI API
Timeout, in microseconds, for releasing memory in the event of user-level [memory overcommit](https://clickhouse.com/docs/enen/operations/settings/memory-overcommit).
The default value is `5000000` (five seconds).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#memory_usage_overcommit_max_wait_microseconds).
* **Merge tree max bytes to use cache**{#setting-merge-tree-max-bytes-to-use-cache} Management console CLI API SQL
Maximum size of a query, in bytes, that uses the uncompressed data cache. Queries larger than the specified value do not use cache.
Use this setting in combination with the [Use uncompressed cache](#setting-use-uncompressed-cache) setting.
The default value is `192x10x1024x1024`.
* **Merge tree max rows to use cache**{#setting-merge-tree-max-rows-to-use-cache} Management console CLI API SQL
Maximum size of a query, in rows, that uses the uncompressed data cache. Queries larger than the specified value do not use cache.
Use this setting in combination with the [Use uncompressed cache](#setting-use-uncompressed-cache) setting.
The default value is `128x8192`.
* **Merge tree min bytes for concurrent read**{#setting-merge-tree-min-bytes-for-concurrent-read} Management console CLI API SQL
If the number of bytes read from a file exceeds this value, ClickHouse® will try to use multiple threads for concurrent data reads from this file.
The minimum value is `1` and the default is `24x10x1024x1024`.
* **Merge tree min rows for concurrent read**{#setting-merge-tree-min-rows-for-concurrent-read} Management console CLI API SQL
If the number of rows read from a file exceeds this value, ClickHouse® will try to use multiple threads for concurrent data reads from this file.
The minimum value is `1` and the default is `20x8192`.
* **Min bytes to use direct io**{#setting-merge-tree-min-bytes-to-use-direct-io} Management console CLI API SQL
Amount of data, in bytes, required for direct reading (direct I/O) from disk.
By default, ClickHouse® does not read data directly from a disk, relying on the file system and its cache instead. This reading strategy is effective for small amounts of data. If you need to read large amounts of data, it is more efficient to do it directly from the disk bypassing the file system cache.
If the total amount of data stored for reading is greater than the value of this setting, ClickHouse® will read the data directly from the disk.
The minimum and default value is `0`, i.e., direct reads are disabled.
* **Min count to compile**{#setting-min-count-to-compile} CLI API SQL
This setting is deprecated.
It sets the minimum number of structurally identical queries that triggers [compilation](#setting-compile).
For a value of `0`, compilation is performed in synchronous mode: a query waits for the compilation to finish and then resumes. We recommend setting this value only for testing purposes.
For all other values, compilation is performed asynchronously in a separate thread: queries, currently running ones as well, will use the result as soon as it is available.
The minimum value is `0` and the default is `3`.
* **Min count to compile expression**{#setting-min-count-to-compile-expression} Management console CLI API SQL
Sets the minimum number of identical expressions that triggers [expression compilation](#setting-compile-expressions).
For a value of `0`, compilation is performed in synchronous mode: an expression waits for the compilation to finish, then the query resumes. We recommend setting this value only for testing purposes.
For all other values, compilation is performed asynchronously in a separate thread: queries, currently running ones as well, will use the result as soon as it is available.
The minimum value is `0` and the default is `3`.
* **Min execution speed**{#setting-min-execution-speed} All interfaces
Minimum query execution speed, in rows per second.
When running queries, ClickHouse® processes data in batches. If a batch is processed longer than the time set in [timeout_before_checking_execution_speed](https://clickhouse.com/docs/enen/operations/settings/query-complexity#timeout-before-checking-execution-speed), the query execution speed is checked. If the speed is lower than the setting value, ClickHouse® throws an exception.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#min-execution-speed).
* **Min execution speed bytes**{#setting-min-execution-speed-bytes} All interfaces
Minimum query execution speed, bytes per second.
When running queries, ClickHouse® processes data in batches. If a batch is processed longer than the time set in [timeout_before_checking_execution_speed](https://clickhouse.com/docs/enen/operations/settings/query-complexity#timeout-before-checking-execution-speed), the query execution speed is checked. If the speed is lower than the setting value, ClickHouse® throws an exception.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#min-execution-speed-bytes).
* **Min insert block size bytes**{#setting-min-insert-block-size-bytes} Management console CLI API SQL
Minimum block size, in bytes, that can be inserted into a table using `INSERT`. Smaller blocks are [squashed into bigger ones](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage).
The minimum value is `0`, i.e., block squashing is disabled. The default value is `268435456` (256 MB).
* **Min insert block size rows**{#setting-min-insert-block-size-rows} Management console CLI API SQL
Minimum block size, in rows, that can be inserted into a table using `INSERT`. Smaller blocks are [squashed into bigger ones](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage).
The minimum value is `0`, i.e., block squashing is disabled. The default value is `1048576`.
* **Output format json quote denormals**{#setting-output-format-json-quote-denormals} Management console CLI API SQL
Sets whether to output special values for floating-point numbers (`+nan`, `-nan`, `+inf`, and `-inf`) when using JSON format.
The default value is `false`, i.e., special values are not output.
* **Output format json quote_64bit integers**{#setting-output-format-json-quote-64bit-integers} Management console CLI API SQL
Sets the format of numbers in the JSON output. If this setting is enabled, 64-bit integers (`UInt64` and `Int64`) are enclosed in quotes in a JSON output to maintain compatibility with most JavaScript engines; otherwise, no quotes are used.
By default, enclosing 64-bit integers in quotes is disabled.
* **Prefer localhost replica**{#setting-prefer-localhost-replica} Management console CLI Terraform API SQL
Enables sending distributed queries to the `localhost` replica.
If the setting is enabled, Managed Service for ClickHouse® distributes queries based on the [Load balancing](#setting-load-balancing) value.
This setting is enabled by default.
{% note warning %}
If a cluster has a single shard and multiple replicas, disabling this setting may impair the health of the cluster.
{% endnote %}
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-prefer-localhost-replica).
* **Priority**{#setting-priority} Management console CLI API SQL
Sets the query priority:
* `0`: No priority.
* `1`: The highest priority.
* Other: The higher the number, the lower the query priority.
The value is set for each query individually.
If ClickHouse® receives a query with a higher priority, lower-priority queries are suspended until this query is completed.
The minimum and default value is `0`.
* **Query cache max entries**{#setting-query-cache-max-entries} Management console CLI SQL
Maximum number of query results the current user can save in the query cache.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_max_entries).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache max size in bytes**{#setting-query-cache-max-size-in-bytes} Management console CLI SQL
Maximum cache size, in bytes, for the user.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_max_size_in_bytes).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache min query duration**{#setting-query-cache-min-query-duration} Management console CLI SQL
Minimum `SELECT` query duration, in milliseconds, for saving the results to the query cache.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_min_query_duration).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache min query runs**{#setting-query-cache-min-query-runs} Management console CLI SQL
Minimum number of completed `SELECT` queries required for saving the results to the query cache.
The minimum and default value is `0`, i.e., there is no limit.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_min_query_runs).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache nondeterministic function handling**{#setting-query-cache-nondeterministic-function-handling} Management console CLI SQL
Sets how the query cache will process `SELECT` queries with non-determined functions, such as `rand()` or `now()`.
The possible values are:
* `throw`: To throw an exception without caching the query result.
* `save`: To cache the query result.
* `ignore`: Not to throw an exception and not to cache the query result.
By default, no value is set (equivalent to `throw`).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_nondeterministic_function_handling).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache share between users**{#setting-query-cache-share-between-users} Management console CLI SQL
Sets whether other users can read the results of the `SELECT` queries saved in the query cache. We do not recommend enabling this parameter for security reasons.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_share_between_users).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache tag**{#setting-query-cache-tag} Management console CLI SQL
String that serves as a tag for query cache entries. This setting allows you to cache multiple results of the same query. Queries with different tags are considered different.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_tag).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Query cache ttl**{#setting-query-cache-ttl} Management console CLI SQL
Time, in seconds, after which entries in the query cache become stale.
The default value is `60`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#query_cache_ttl).
Related to the [Use query cache](#setting-use-query-cache) setting.
* **Quota mode**{#setting-quota-mode} Management console CLI SQL
Mode used to track resources utilized when [quotas](https://console.yandex.cloud/cloud?section=quotas) are enabled:
* `default`: Keys are not used.
* `keyed`: `quota_key` is provided in a user query parameter, and quotas are calculated individually for each key value.
* `keyed_by_ip`: Similar to the previous one, but a user IP address acts as the key. We recommend using this mode only if a user cannot change their IP address quickly, e.g., to bypass the quota restrictions. This applies to both IPv4 and IPv6 addresses.
By default, no value is set (equivalent to `default`).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/quotas).
* **Read overflow mode**{#setting-read-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if the amount of read data [exceeds one of the limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Readonly**{#setting-readonly} Management console CLI API SQL
Permissions for configuration, read, and write queries:
* `0` (default): Allows queries of all types.
* `1`: Only data read queries are allowed.
* `2`: Allows queries to read data and edit settings.
This setting does not apply to DDL queries. To enable or disable DDL queries, use the [Allow DDL](#setting-allow-ddl) setting.
See the [example of creating a user with read-only access permissions](../operations/cluster-users.md#example-create-readonly-user).
* **Receive timeout**{#setting-receive-timeout} Management console CLI API SQL
Timeout for receiving data, in milliseconds.
The default value is `300000` (five minutes).
* **Remote filesystem read method**{#setting-remote-filesystem-read-method} Management console CLI API
Sets how to read data from a remote file system.
The possible values are:
* `read`
* `threadpool`
The default value is `threadpool`.
* **Replication alter partitions sync**{#setting-replication-alter-partitions-sync} Management console CLI API SQL
Sets the wait mode for asynchronous actions on replicas for `ALTER ... ATTACH DETACH DROP` queries:
* `0`: Do not wait.
* `1`: Only wait for own execution. This is a default value.
* `2`: Wait for everyone.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/statements/alter#synchronicity-of-alter-queries).
* **Result overflow mode**{#setting-result-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if the size of the result [exceeds one of the limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Select sequential consistency**{#setting-select-sequential-consistency} Management console CLI API SQL
Sets whether to enable sequential consistency for `SELECT` queries.
By default, sequential consistency is disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#settings-select_sequential_consistency).
* **Send progress in HTTP headers**{#setting-send-progress-in-http-headers} Management console CLI API SQL
Enables sending notifications regarding query execution status using `X-ClickHouse-Progress` headers.
By default, notifications are disabled.
* **Send timeout**{#setting-send-timeout} Management console CLI API SQL
Timeout for sending data, in milliseconds.
The default value is `300000` (five minutes).
* **Set overflow mode**{#setting-set-overflow-mode} All interfaces
Sets the ClickHouse® behavior if the amount of data [exceeds one of the query complexity limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Show Data Lake catalogs in system tables**{#setting-show-data-lake-catalogs-in-system_tables} Management console CLI API SQL
Enables display of Data Lake folders in system tables.
The default value depends on the ClickHouse® version:
* For versions below `25.10`: `true`.
* For versions `25.10` and higher: `false`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#show_data_lake_catalogs_in_system_tables).
* **Skip unavailable shards**{#setting-skip-unavailable-shards} Management console CLI API SQL
Enables silent skipping of unavailable shards. A shard is considered unavailable if none of its replicas are available.
By default, silent skipping is disabled.
* **Sort overflow mode**{#setting-sort-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if the number of rows received before sorting [exceeds one of the limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Timeout before checking execution speed**{#setting-timeout-before-checking-execution-speed} Management console API SQL
Timeout, in milliseconds, for a query execution speed check. ClickHouse® checks that the execution speed is not lower than [**Min execution speed**](#setting-min-execution-speed).
The default value is `60000` (one minute).
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/query-complexity#timeout-before-checking-execution-speed).
* **Timeout overflow mode**{#setting-timeout-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if a query takes longer than [max_execution_time](#setting-max-execution-time):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Transfer overflow mode**{#setting-transfer-overflow-mode} Management console CLI API SQL
Sets the ClickHouse® behavior if the amount of data to transfer to another server [exceeds one of the limits](https://clickhouse.com/docs/enen/operations/settings/query-complexity#restrictions-on-query-complexity):
* `throw`: Abort the query and return an error.
* `break`: Return a partial output.
By default, no value is set (equivalent to `throw`).
* **Transform null in**{#setting-transform-null-in} Management console CLI SQL
If this setting is enabled, `NULL = NULL` returns `true` in the `IN` operator.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#transform_null_in).
* **Use hedged requests**{#setting-use-hedged-requests} Management console CLI Terraform API SQL
Enables hedged requests.
This setting allows you to establish multiple connections to various replicas for queries. A new connection is established if the current connections to replicas were not successful within the [Hedged connection timeout ms](#setting-hedged-connection-timeout-ms) period.
The first connection that sent a non-empty query progress packet is used for this query. The other connections are canceled.
This setting is enabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#use_hedged_requests).
* **Use hive partitioning**{#setting-use-hive-partitioning} Management console CLI API SQL
If this setting is enabled, ClickHouse® will recognize Hive-style partitioning in `/name=value/` paths for `File/S3/URL/HDFS/AzureBlobStorage` table engines. This enables using partition columns as virtual columns in a query. Virtual columns use the same names as partition columns but prefixed with `_`.
The default value depends on the ClickHouse® version:
* For versions below `25.1`: `false`.
* For versions `25.1` and higher: `true`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#use_hive_partitioning).
* **Use query cache**{#setting-use-query-cache} Management console CLI SQL
Sets whether `SELECT` queries can use the query cache.
This setting is disabled by default.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#use_query_cache).
Related to these settings:
* [Enable reads from query cache](#setting-enable-reads-from-query-cache).
* [Enable writes to query cache](#setting-enable-writes-to-query-cache).
* **Use uncompressed cache**{#setting-use-uncompressed-cache} Management console CLI API SQL
Sets whether to use a cache of uncompressed blocks. Using this type of cache can help significantly reduce latency and improve throughput for a large number of short queries (only for the [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) family of tables). Enable this setting for users who frequently initiate small queries.
By default, the cache is disabled.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#setting-use_uncompressed_cache).
Related to these settings:
* [Merge tree max bytes to use cache](#setting-merge-tree-max-bytes-to-use-cache).
* [Merge tree max rows to use cache](#setting-merge-tree-max-rows-to-use-cache).
* **Wait for async insert**{#setting-wait-for-async-insert} Management console API SQL
Enables waiting for [asynchronous insert](#setting-async-insert) processing.
The possible values are:
* `0`: The server returns `OK` even if a data insert is not completed yet.
* `1`: The server returns `OK` only after data is inserted.
The default value is `1`.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#wait-for-async-insert).
* **Wait for async insert timeout**{#setting-wait-for-async-insert-timeout} Management console API SQL
Timeout for asynchronous insert processing, in seconds.
The default value is `120`. If `0`, there is no timeout.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/settings/settings#wait-for-async-insert-timeout).
### Custom settings {#custom-settings}
You can override [custom settings](https://clickhouse.com/docs/enen/operations/settings#custom_settings) as variables in the [settings profile](https://clickhouse.com/docs/enen/operations/access-rights#settings-profiles-management), session, or query. You can use settings profiles if the [user management via SQL](../operations/cluster-users.md#sql-user-management) is enabled for the cluster.
In Managed Service for ClickHouse®, the name of the custom setting must be prefixed by `custom_`. This prefix is non-editable, i.e., you cannot change it.
## Quota settings {#quota-settings}
Quotas allow you to limit the consumption of ClickHouse® resources for a specified time period. Quota settings apply to an individual user.
If one of the quota limits is exceeded, the user will not be able to run new queries until the set time period ends.
ClickHouse® will display a message about the quota being exceeded and when the next time period starts, and the query ban will be lifted with the limit counters reset.
ClickHouse® also uses quotas to calculate resource consumption.
For each user, there is a default quota that covers the consumption of all resources per hour but does not impose any limits.
The resource consumption level information is logged on the ClickHouse® server.
For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/quotas).
You can use the following quota settings:
* **Errors**{#setting-quota-errors} All interfaces
Limits the total number of failed queries.
The minimum value is `0`, i.e., there is no limit.
* **Execution time**{#setting-quota-execution-time} All interfaces
Limits the total query execution time in milliseconds.
The minimum value is `0`, i.e., there is no limit.
* **Interval duration**{#setting-quota-interval-duration} All interfaces
Quota interval, in milliseconds. This value must be a multiple of 1,000.
The minimum value is `1000` (one second).
* **Queries**{#setting-quota-queries} All interfaces
Limits the total number of queries.
The minimum value is `0`, i.e., there is no limit.
* **Read rows**{#setting-quota-read-rows} All interfaces
Limits the total number of source rows read from tables for running queries, including rows read from remote servers.
The minimum value is `0`, i.e., there is no limit.
* **Result rows**{#setting-quota-result-rows} All interfaces
Limits the total number of rows in query results.
The minimum value is `0`, i.e., there is no limit.
_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._