ClickHouse® settings

Written by

Updated at April 25, 2025

Server-level settings
Query-level settings
- DBMS settings
- Custom settings
Quota settings

For Managed Service for ClickHouse® clusters, you can configure ClickHouse® 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 Yandex Cloud interfaces.
Query-level settings: Configured at the user, session, or query level.
Quota settings: Configured at the user level.

Note

ClickHouse® supports more settings than Yandex Cloud interfaces. You can use SQL queries to modify ClickHouse® settings, e.g., configure ClickHouse® settings at the query level or change the settings for MergeTree tables.

The label next to the setting name helps determine which interface is used to set the value of this setting: the management console, CLI, API, SQL, or Terraform. The All interfaces label means that all of the above interfaces are supported.

Depending on the interface you select, the same setting will be represented differently, 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

Note

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.

The following settings are available:

Asynchronous insert log enabled Management console CLI API

Determines whether information about asynchronous inserts will be logged. These logs are saved to the system.asynchronous_insert_log table.

Default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Asynchronous insert log retention size Management console CLI API

The size of the system.asynchronous_insert_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

Default value is 0 (old records will not be deleted as the table grows in size).
Asynchronous insert log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.asynchronous_insert_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Asynchronous metric log enabled Management console CLI API

Determines 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 cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Asynchronous metric log retention size Management console CLI API

The size of the system.asynchronous_metric_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 0 (old records will not be deleted as the table grows in size).
Asynchronous metric log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.asynchronous_metric_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Background buffer flush schedule pool size Management console

Number of threads for background data flushing in Buffer tables.

Minimum value: 1; default value: 16. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background common pool size Management console API

Number of threads for common background operations, such as file system clean up, in MergeTree tables.

Minimum value: 1; default value: 8. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background distributed schedule pool size Management console

Number of threads for background operations in Distributed tables.

The minimum value is 1; the default value is 16. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background fetches pool size Management console API

Number of threads for background jobs of copying data from a replica in ReplicatedMergeTree tables.

The minimum value is 1; the default value is 8. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background merges mutations concurrency ratio Management console CLI API

The number of background merges and mutations that can be concurrently executed by each thread.

Default value is 2. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background message broker schedule pool size Management console

Number of threads for background message translation operations. This setting is set to a new value when restarting the ClickHouse® server.

The minimum value is 1; the default value is 16. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background move pool size Management console

Number of threads for background moves of data parts in MergeTree tables.

The minimum value is 1; the default value is 8. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background pool size Management console API Terraform

Number of threads for background merges and mutations in MergeTree tables.

The minimum value is 1; the default value is 16. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Background schedule pool size Management console API Terraform

The number of threads for background jobs. Used for replicated tables, streams in Apache Kafka®, and updating a record's IP address in the internal DNS cache.

Minimum value: 1; default value: 128. Changing this setting will restart ClickHouse® servers on cluster hosts.
Compression Management console API Terraform

Data compression rules for MergeTree tables. For each rule, specify:
- Level: Compression level. Only available for the zstd compression method. Minimum value: 1; maximum value: 12; default: 9.
- Method: Compression method. Available methods: LZ4 and zstd.
- Min part size: Minimum size (in bytes) of a data part.
- Min part size ratio: Ratio between the smallest table part size and full table size. ClickHouse® will only apply the rule to 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 can be applied to the same table, ClickHouse® applies the first one. If none of the rules are applicable, ClickHouse® uses the LZ4 compression method.

Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the relevant ClickHouse® documentation.
Default database Management console API

Default database. To learn how to get a list of cluster databases, see Managing databases.

Changing this setting will restart ClickHouse® servers on cluster hosts.
Dictionaries lazy load Management console CLI Terraform API SQL

With this setting enabled, each dictionary is loaded the first time it is used. 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 be loaded before it starts processing connections.

This setting is on by default.

For more information, see the relevant ClickHouse® documentation.
Geobase enabled Management console CLI API

Enables/disables the built-in geobase dictionary.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.
Geobase uri Management console CLI API Terraform

Address of the archive containing the user geobase in Object Storage.

Changing this setting will restart ClickHouse® servers on cluster hosts.
Graphite rollup Management console API Terraform

GraphiteMergeTree engine configurations for Graphite data thinning and aggregation (rollup):
- Name: Configuration name.
- Patterns: Set of thinning rules. A rule applies if the metric name matches the Regexp parameter value and the age of the data matches the Retention parameter group value.
  - Function: Aggregation function name.
  - Regexp: Regular expression that the metric name must match.
  - Retention: Retention parameters. The function is applied to the data 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 data age (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 cluster hosts.

To create GraphiteMergeTree tables, specify column names for each one of such tables:
- Path column name: Column with the name of the metric (Graphite sensor). The default value is Path.
- Time column name: Column with the time the metric was measured. 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 the ClickHouse® documentation.
Kafka Management console CLI Terraform

Global authentication settings for integration with Apache Kafka®:
- Auto offset reset: Initial position from which the consumer starts reading messages. Starting from there, the consumer adds shifts for new messages as it moves forward reading the incoming messages. By default, the value is not set (equivalent to latest). For more information, see the Confluent documentation.
- Debug: Context for debugging purposes. By default, the value is not set. You can only specify one value in the setting. For the list of possible values, see the librdkafka library documentation on GitHub.
- Enable ssl certificate verification: Determines whether to perform SSL certificate verification. 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 rebalancing starts. No value is set by default (equivalent to 300000, 5 minutes).
- Sasl mechanism: SASL authentication mechanism:
  - GSSAPI (default): Authentication using Kerberos.
  - PLAIN: Authentication using a username-password pair as plain text.
  - SCRAM-SHA-256 and SCRAM-SHA-512: Authentication using the SCRAM family methods.
  By default, the value is not set (equivalent to GSSAPI).
- Sasl password: Apache Kafka® account password.
- Sasl username: Apache Kafka® account username.
- 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 through SASL.
  - SASL_SSL: Authentication credentials are sent SSL encrypted through SASL.
  By default, the value is not 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 rebalancing. By default, the value is not set (equivalent to 45000, 45 seconds).
Changing these settings will restart ClickHouse® servers on the cluster hosts.
Kafka topics Management console CLI Terraform

Topic-level authentication settings for Apache Kafka® integration:
- Name: Apache Kafka® topic name.
- Settings: Topic-level authentication settings similar to the global authentication settings in the Kafka section.
  
  If topic-level authentication settings are not specified for a table using the Kafka engine, global settings from the Kafka section will be used.
  
  Changing these settings will restart ClickHouse® servers on the cluster hosts.
  
  For more information, see the relevant Apache Kafka® documentation.
Keep alive timeout Management console CLI API Terraform

The time (in seconds) since ClickHouse® received its last query before a connection was interrupted. If a new query comes in during this time, the connection does not terminate.

Default value is 3. Changing this setting will restart ClickHouse® servers on cluster hosts.
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.
2. WARNING: Information about events that may lead to cluster errors.
3. INFORMATION: Confirmations and information about events not leading to cluster errors.
4. DEBUG: System information for subsequent use in debugging.
5. TRACE: All available information on cluster operation.
For more information about logging levels, see the ClickHouse® documentation.
Mark cache size Management console CLI API Terraform

Approximate size (in bytes) of the mark cache used by MergeTree table engines. The cache is shared by a cluster host. Memory is allocated as needed.

The selected parameter value is not a hard and fast restriction. ClickHouse® can make this cache a little smaller or larger.

Default value is 5368709120. Changing this setting will restart ClickHouse® servers on cluster hosts.
Max concurrent queries Management console CLI API Terraform

Maximum number of simultaneously processed requests.

Minimum value: 10; default value: 500. Changing this setting will restart ClickHouse® servers on cluster hosts.
Max connections Management console CLI API Terraform

Maximum number of inbound client connections. This setting does not account for housekeeping connections established to run distributed subqueries.

Minimum value: 10; default value: 4096. Changing this setting will restart ClickHouse® servers on cluster hosts.
Max partition size to drop Management console CLI API Terraform

Maximum MergeTree table partition size (in bytes) at which the table can be deleted with a DROP TABLE query. You can use this setting to protect tables with real data from inadvertent deletion, as these tables will normally be larger than test ones.

Default value is 53687091200 (50 GB). When set to 0, you can delete tables of any size.
Max table size to drop Management console CLI API Terraform

Maximum size (in bytes) of a MergeTree table you can delete with a DROP TABLE query. You can use this setting to protect tables with real data from inadvertent deletion, as these tables will normally be larger than test ones.

Default value is 53687091200 (50 GB). When set to 0, you can delete tables of any size.
Merge tree Management console CLI API Terraform

MergeTree engine configuration:
- Allow remote fs zero copy replication: Determines whether to allow remote zero copy replication for S3 and HDFS disks.
  
  The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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 positives and exceptions when starting the server.
  
  For more information, see the ClickHouse® documentation.
- Cleanup delay period: Interval (in seconds) between running distributed DDL queries to clean up outdated data.
  
  The default value is 60 (one minute). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Inactive parts to delay insert: Number of inactive table data parts which, when exceeded, will cause ClickHouse® to throttle the speed of table data inserts.
  
  This setting is disabled by default (0). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Inactive parts to throw insert: Number of inactive table data parts which, when exceeded, will cause ClickHouse® to throw the Too many inactive parts ... exception.
  
  This setting is disabled by default (0). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Max avg part size for too many parts: Maximum average size of active data parts in a table (in bytes) that triggers Parts to delay insert and Parts to throw insert checks. If exceeded, data inserts into the table will neither slow down nor get rejected.
  
  Minimum value: 0; default value: 1073741824 (1 GB). Changing this setting will restart ClickHouse® servers on 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.
  
  Default value is 161061273600 (150 GB). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Max bytes to merge at min space in pool: Maximum total data chunk size to merge with the background pool at minimum available resources.
  
  Default value is 1048576 (1 MB). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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 merging a selection.
  
  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, selection jobs to often run in the background job pool. Thus, high capacity clusters will accumulate a large number of requests to ZooKeeper.
- 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 cluster hosts.
- Max parts in total: Number of active data parts in all table partitions which, when exceeded, will cause ClickHouse® to throw the Too many parts ... exception.
  
  Default value is 100000. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Max replicated merges in queue: Maximum number of merge tasks that can be in a ReplicatedMergeTree queue at the same time.
  
  Default value is 16. Changing this setting will restart ClickHouse® servers on 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 the 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 the ClickHouse® documentation.
- Merge selecting sleep ms: Timeout (in milliseconds) before merging a selection if no data part is selected.
  
  Default value is 5000 (five seconds). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Merge with recompression TTL timeout: Minimum timeout (in seconds) before merges with recompression of data with expired TTL.
  
  Default value is 14400 (four hours). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Merge with TTL timeout: Minimum timeout (in seconds) before merges to delete data with expired TTL.
  
  Default value is 14400 (four hours). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Min age to force merge on partition only: Whether to apply the merge from the Min age to force merge seconds setting only to partitions.
  
  Forcing merges on partitions only is disabled by default. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Min age to force merge seconds: Minimum age (in seconds) for a data part to be merged.
  
  Default value is 0 (merge disabled). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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 the Min rows for wide part parameter.
  
  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 cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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 the Min bytes for wide part parameter.
  
  If the number of rows in a data part is less than the set value, the data part will be stored in Compact format.
  
  Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Number of free entries in pool to execute mutation: Threshold value of free entries in the pool. If the number of entries in the pool falls below this value, ClickHouse® stops executing mutation operations. This allows to leave free threads for merges and to avoid the Too many parts ... exception.
  
  Default value is 20. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Number of free entries in pool to lower max size of merge: Threshold value of 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.
  
  Default value is 8. Changing this setting will restart ClickHouse® servers on cluster hosts.
- Parts to delay insert: Number of active table data parts which, when exceeded, will cause ClickHouse® to throttle the speed of table data inserts. An active chunk is a new chunk of data resulting from a merge.
  
  Default value is 150. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- Parts to throw insert: Threshold value of active table data parts which, when exceeded, will cause ClickHouse® to throw the Too many parts ... exception.
  
  Default value is 300. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the relevant ClickHouse® documentation.
- 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.
  
  Default value is 100. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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.
  
  Default value is 604800. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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 cluster hosts.
  
  For more information, see the ClickHouse® documentation.
Metric log enabled Management console CLI Terraform

Determines 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 cluster hosts.
Metric log retention size Management console CLI Terraform

The size of the system.metric_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

Default value is 536870912 (0.5 GB). When set to 0, old records will not be deleted as the table grows in size.
Metric log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.metric_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Opentelemetry span log enabled Management console

Determines whether to log trace and metric values from a distributed application. These logs are saved to the system.opentelemetry_span_log table.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Opentelemetry span log retention size Management console CLI API

The size of the system.opentelemetry_span_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 0 (old records will not be deleted as the table grows in size).
Opentelemetry span log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.opentelemetry_span_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Part log retention size Management console CLI Terraform

The size of the system.part_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 536870912 (0.5 GB). When set to 0, old records will not be deleted as the table grows in size.
Part log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.part_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Query cache Management console CLI Terraform API

Query cache settings. The cache allows you to execute SELECT queries only once and use the saved results in subsequent operations. This reduces latency and cluster resource consumption when processing queries.

Available query cache settings:
- 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 that can be allocated in the cache for each SELECT query result. The default value is 1048576 (1 MB).
- Max entry size in rows: Maximum number of rows that can be allocated 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 1048576 (1 GB).
For more information about the settings, see the ClickHouse® documentation.
Query log retention size Management console CLI Terraform

The size of the system.query_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

Default value is 1073741824 (1 GB). When set to 0, old records will not be deleted as the table grows in size.
Query log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.query_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Query masking rules Management console CLI Terraform API

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

Available settings for the query masking rules:
- Name: Rule name. This is an optional parameter.
- Regexp: RE2 regular expression.
- Replace: Expression to replace the sensitive data. Six asterisks are used by default.
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 that the Log query threads setting is enabled.

The default value is true. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Query thread log retention size Management console CLI Terraform

The size of the system.query_thread_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 536870912 (0.5 GB). When set to 0, old records will not be deleted as the table grows in size.
Query thread log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.query_thread_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Query views log enabled Management console CLI API

Determines whether information about dependent views executed when running queries will be logged. These logs are saved to the system.query_views_log table.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Query views log retention size Management console CLI API

The size of the system.query_views_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 0 (old records will not be deleted as the table grows in size).
Query views log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.query_views_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Rabbitmq Management console CLI API Terraform

Global authentication settings for integration with RabbitMQ:
- Password: RabbitMQ account password.
- Username: RabbitMQ account username.
- Vhost: RabbitMQ virtual host address.
Changing these settings will restart ClickHouse® servers on the cluster hosts.
Session log enabled Management console CLI API

Determines whether information about successful and failed login/logout events will be logged. These logs are saved to the system.session_log table.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Session log retention size Management console CLI API

The size of the system.session_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 0 (old records will not be deleted as the table grows in size).
Session log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.session_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Text log enabled Management console CLI Terraform

Determines whether system logs will be made. These logs are saved to the system.text_log table.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.
Text log level Management console CLI Terraform

Event logging level in the system.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 lead to DBMS errors.
- INFORMATION: Confirmation and information about events not leading to DBMS errors.
- DEBUG: System information for subsequent use in debugging.
- TRACE: All available information on DBMS operation.
Default value is TRACE. Changing this setting will restart ClickHouse® servers on cluster hosts.
Text log retention size Management console CLI Terraform

The size of the system.text_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 536870912 (0.5 GB). When set to 0, old records will not be deleted as the table grows in size.
Text log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.text_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Timezone Management console CLI API Terraform

Server time zone. Specified by the IANA identifier as the UTC time zone or geographical location (for example, Africa/Abidjan).

Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Total memory profiler step Management console API

RAM (in bytes) for a stack trace at each memory allocation step. The data is stored in the system.trace_log system table. The query_id value is an empty string.

Default value is 4194304 (4 MB). Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Total memory tracker sample probability Management console

Probability of logging information about accidental memory allocation and release (%). These log records are stored in the system.trace_log system 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 value.

By default, accidental memory allocations and releases are not logged (0). Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Trace log enabled Management console CLI Terraform

Determines whether stack traces collected by the query profiler will be logged. Stack traces are saved to the system.trace_log table.

The default value is true. Changing this setting will restart ClickHouse® servers on cluster hosts.
Trace log retention size Management console CLI Terraform

The size of the system.trace_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 536870912 (0.5 GB). When set to 0, old records will not be deleted as the table grows in size.
Trace log retention time Management console CLI Terraform

Time (in milliseconds) between adding an entry to the system.trace_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.
Uncompressed cache size Management console CLI API Terraform

Cache size (in bytes) for uncompressed data used by the MergeTree table engines.

Default value is 8589934592 (8 GB). Changing this setting will restart ClickHouse® servers on cluster hosts.
Zookeeper log enabled Management console CLI API

Determines whether ZooKeeper server request and response parameters will be logged. These logs are saved to the system.zookeeper_log table.

The default value is false. Changing this setting will restart ClickHouse® servers on cluster hosts.

For more information, see the ClickHouse® documentation.
Zookeeper log retention size Management console CLI API

The size of the system.zookeeper_log table (in bytes), which, when exceeded, will cause old records to be deleted from that table.

The default value is 0 (old records will not be deleted as the table grows in size).
Zookeeper log retention time Management console CLI API

Time (in milliseconds) between adding an entry to the system.zookeeper_log table and deleting the entry. The value must be a multiple of 1000.

Default value is 2592000000 (30 days). If 0, the records will be stored indefinitely.

Query-level settings

DBMS settings

These settings apply at the individual user level.

Note

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.

The following settings are available:

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 All interfaces

Defines whether DDL queries will be executed (CREATE, ALTER, RENAME, and others).

By default, DDL queries are allowed.

For more information, see the ClickHouse® documentation.

See also the Readonly setting.
Allow introspection functions Management console API SQL

Enables introspection functions 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 the ClickHouse® documentation.
Allow suspicious low cardinality types Management console API SQL

Allows using the 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 the ClickHouse® documentation.
Any join distinct right table keys Management console SQL

Enables the deprecated behavior of the ClickHouse® server for ANY INNER|LEFT JOIN operations.

By default, the deprecated JOIN behavior is disabled.

For more information, see the ClickHouse® documentation.
Async insert Management console API SQL

Enables/disables asynchronous inserts. Only works for inserts over HTTP. These inserts are made with no deduplication.

If enabled, data is grouped into batches before inserting it in a table. This allows making small and frequent inserts in ClickHouse® (up to 15000 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 the ClickHouse® documentation.
Async insert busy timeout Management console API SQL

Maximum data insert timeout (ms) after the first INSERT query.

The default value is 200. If 0, there is no timeout.

For more information, see the ClickHouse® documentation.
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 the ClickHouse® documentation.
Async insert stale timeout Management console API SQL

Maximum data insert timeout (ms) after the last INSERT query. For non-zero values, Async insert busy timeout will increase with each INSERT query until the Async insert max data size value is exceeded.

The default value is 0. If 0, there is no timeout.

For more information, see the ClickHouse® documentation.
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 the ClickHouse® documentation.
Cancel HTTP readonly queries on client close Management console API SQL

If enabled, the service cancels HTTP readonly queries (like a SELECT) when the client aborts a connection before a response is returned.

This setting is disabled by default.

For more information, see the ClickHouse® documentation.
Compile expressions Management console CLI API SQL

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

Expression compilation is disabled by default.
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 Management console API SQL

Remote server connection timeout (ms) for the Distributed table engine if a cluster uses sharding and replication.

If unable to connect to the server, the system will attempt to connect to its replicas.

The default value is 50.

For more information, see the ClickHouse® documentation.
Count distinct implementation Management console CLI API

Indicates the uniq* function to use when performing COUNT(DISTINCT …):
- uniq
- uniqCombined
- uniqCombined64
- uniqHLL12
- uniqExact
By default, the uniqExact function is used.

For more information, see the ClickHouse® documentation.
Date time input format Management console SQL

Determines the parser to be used 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 the ClickHouse® documentation.
Date time output format Management console SQL

Determines 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 the ClickHouse® documentation.
Deduplicate blocks in dependent materialized views Management console SQL

Enables checks for deduplication of materialized views that get data from replicated tables.

Disabled by default (0).

For more information, see the ClickHouse® documentation.
Distinct overflow mode Management console CLI API SQL

Defines ClickHouse® behavior if the amount of data when running a SELECT DISTINCT query exceeds the limits:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Distributed aggregation memory efficient Management console CLI API SQL

Defines whether to enable memory saving mode for distributed aggregation.

Under distributed query processing, remote servers perform external aggregation. Enable this setting to reduce the memory footprint on the server initiating the query.

By default, memory saving mode is disabled.

For more information, see the ClickHouse® documentation.
Distributed ddl task timeout All interfaces

Sets the waiting time 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 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 the ClickHouse® documentation.
Empty result for aggregation by empty set Management console CLI API SQL

Defines the result format when aggregating data without keys (without GROUP BY) for an empty set (for example, 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 Management console CLI API SQL

Defines whether the data in a response to an HTTP request will be compressed.

By default, ClickHouse® stores data in a compressed format. The request output is uncompressed. For ClickHouse® to compress a request output when sending it over HTTP, enable this option and include the selected compression method in the Accept-Encoding request header.
- gzip
- br
- deflate
By default, data compression in HTTP responses is disabled.

For more information, see the ClickHouse® documentation.
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® selects 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 the ClickHouse® documentation.

See also the Max replica delay for distributed queries setting.
Flatten nested Management console API SQL

Sets data format for nested columns.

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 the ClickHouse® documentation.
Force index by date Management console CLI API SQL

Disables queries if you cannot use an index by date. Works with tables of the MergeTree family.

By default, this setting is disabled, which means query execution is enabled.

For more information, see the ClickHouse® documentation.
Force primary key Management console CLI API SQL

Disables queries if you cannot use an index by primary key. Works with tables of the MergeTree family.

By default, this setting is disabled, which means query execution is enabled.

For more information, see the ClickHouse® documentation.
Format regexp Management console SQL

Sets a regular expression in re2 format to be applied 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 Management console SQL

Sets the escaping rule for the regular expression specified in the Format regexp setting:
- CSV
- Escaped
- JSON
- Quoted
- Raw
- XML
The default value is Raw (no escaping).
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.

By default, no message is output (0).
Group by overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if, during aggregation, the number of unique keys exceeds the limit:
- throw: Terminate 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 data's statistical properties.
By default, the value is not set (equivalent to throw).
Group by two level threshold Management console CLI API SQL

Defines the number of keys at which two-level aggregation begins.

The minimum value is 0 (not set). The default value is 100000.
Group by two level threshold bytes Management console CLI API SQL

Defines the number of bytes in the aggregated output at which two-level aggregation begins.

The minimum value is 0 (not set). The default value is 50000000.
Hedged connection timeout ms Management console CLI Terraform API SQL

Timeout (ms) for connecting to the replica to work with hedged requests. Use this setting in combination with Use hedged requests.

The default value is 50 milliseconds.

For more information, see the ClickHouse® documentation.
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 Management console CLI API SQL

Sets the minimum interval between progress notifications with the X-ClickHouse-Progress HTTP header, in milliseconds.

Minimum value: 1; default value: 100.
HTTP receive timeout Management console CLI API SQL

Sets the HTTP receive timeout (in milliseconds).

Minimum value: 1; default value: 1800000 (30 minutes).
HTTP send timeout Management console CLI API SQL

Sets the HTTP send timeout (in milliseconds).

Minimum value: 1; default value: 1800000 (30 minutes).
Idle connection timeout Management console CLI Terraform API SQL

Time before 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 the ClickHouse® documentation.
Input format defaults for omitted fields Management console CLI API SQL

Indicates whether to write default values into omitted fields based on a column data type when inserting data with INSERT.

By default, replacement is enabled.

For more information, see the ClickHouse® documentation.
Input format import nested json Management console

Determines whether to insert JSON data with nested objects.

By default, such data inserts are disabled.

For more information, see the ClickHouse® documentation.
Input format null as default Management console SQL

Indicates 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 (NULL cells get default values).

For more information, see the ClickHouse® documentation.
Input format parallel parsing Management console

Determines whether to split incoming data into parts and parse each of them concurrently while preserving the original sequence. Only supported for TSV, TKSV, CSV, and JSONEachRow formats.

By default, such splitting of incoming data is enabled.

For more information, see the ClickHouse® documentation.
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 does not recognize a value containing now() while the SQL parser, if enabled, will parse the value correctly insert the output of the now() SQL function (current date and time).

This setting is only used for the Values format when inserting data.

By default, the SQL parser is enabled.

For more information, see the ClickHouse® documentation.
Input format with names use header Management console SQL

Defines if the order of columns should be checked when inserting data.

By default, the check is enabled.

For more information, see the ClickHouse® documentation.
Insert keeper max retries Management console CLI API

The maximum number of retries for ClickHouse® Keeper (or ZooKeeper) requests during inserts to replicated MergeTree tables.

Minimum value: 0 (no retries); default value: 20.

For more information, see the ClickHouse® documentation.
Insert null as default Management console API SQL

Enables inserting default values instead of NULL in columns that do not allow NULL.

The possible values are:
- 0: Inserting NULL in 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 the ClickHouse® documentation.
Insert quorum Management console CLI API SQL

Manages a ClickHouse® cluster's quorum write:
- If the value is less than 2, quorum write is disabled.
- If the value is greater than or equal to 2, quorum write is enabled.
Quorum write ensures that ClickHouse® writes data error-free to the quorum from the replicas' Insert quorum during an interval that does not exceed Insert quorum timeout and that data is not lost if one or more 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 to read data written with Insert quorum.

For more information, see the ClickHouse® documentation.
Insert quorum parallel Management console SQL

If this setting is enabled, you can run multiple INSERT queries with a write quorum at once. If disabled, a single INSERT query with a write quorum is made to the same table.

This setting is enabled by default.
Insert quorum timeout Management console CLI API SQL

Sets the quorum write timeout in milliseconds. If the timeout ends without a write, ClickHouse® will terminate the INSERT query and return an error.

Minimum value: 1000 (one second); default value: 60000 (1 minute).
Join algorithm Management console SQL

Defines the JOIN algorithm:
- auto: Use a hash join. However, if the server is running out of memory, ClickHouse® will attempt a merge join.
- direct: Perform a join by searching the right-hand table rows, which serve as keys in the left-hand table. Joins are only possible if the following two conditions are met:
  - Storage in the right-hand table supports key-value queries, e.g., Dictionary or EmbeddedRocksDB.
  - LEFT and INNER JOIN queries are running.
- hash: Hash join.
- parallel_hash: Type of a 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.
The default algorithm is hash.

For more information, see the ClickHouse® documentation.
Join overflow mode All interfaces

Defines 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 Management console CLI SQL

Controls the behavior of JOIN queries. 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 the ClickHouse® documentation.
Joined subquery requires alias Management console CLI SQL

Requires aliases for subqueries when executing the JOIN command.

If the setting is enabled, this request is not executed:
```
SELECT col1, col2 FROM table1 JOIN (SELECT col3 FROM table2)
```
The request with the specified alias is successfully executed:
```
SELECT col1, col2 FROM table1 JOIN (SELECT col3 FROM table2) AS MyQuery
```
This setting is disabled by default.
Load balancing Management console CLI Terraform API SQL

Specifies 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 number of errors. 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 (default).
  
  Disadvantage: 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. This is an advantage over the random algorithm.
- hostname_levenshtein_distance: Applies the same replica selection principle as nearest_hostname. However, the appropriate replica host name is determined by the Levenshtein distance.
- in_order: Quieries are sent to replicas in the order specified in the ClickHouse® configuration file. In a Managed Service for ClickHouse® cluster, replicas are ranged so that the replica located in the same availability zone as the initiator host of distributed subqueries is the first one.
  
  Disadvantage: if the first replica fails, its workload will be moved to the next replica, which will double its workload.
- first_or_random: Sends the query to the first replica similarly to in_order. However, there is an 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 policy to select a replica for the query. It is ony used with queries supporting this policy.
For more information, see the ClickHouse® documentation.
Local filesystem read method Management console

Determines how to read data from the local file system.

The possible values are:
- nmap
- pread
- pread_threadpool
- read
The default value is pread.
Log query threads Management console CLI Terraform API SQL

Enables logging of threads which run queries. The logs will be saved to thesystem.query_thread_log table.

If this setting is enabled, you also need to enable Query thread log enabled. Both settings are enabled by default.

It is only suitable for some users or queries.

For more information, see the ClickHouse® documentation.
Low cardinality allow in native format Management console CLI API SQL

Determines whether to use LowCardinality type in native format.
- If this setting is enabled (by default), use native format.
- If the setting is off, do not use native format:
  - For SELECT queries, convert LowCardinality type 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-type 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 the setting enables such clients to process query output correctly.

The official ClickHouse® client supports working with LowCardinality columns.

This setting is enabled by default.
Max ast depth Management console CLI API SQL

Maximum nesting depth of the syntax tree.

For complex queries, the syntax tree may be too deep. This setting enables you to block the execution of 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 too small a value is set, it may render ClickHouse unable to execute even simple queries.
Max ast elements Management console CLI API SQL

Maximum size of the query syntax tree (number of tree nodes).

For complex queries, the syntax tree may contain too many elements. This setting enables you to block the execution of unnecessarily complex or unoptimized queries for large tables.

The default value is 50000. If too small a value is set, it may render ClickHouse unable to execute even simple queries.
Max block size Management console CLI API SQL

Data in ClickHouse® is processed by blocks (a block is a set of column parts). This parameter sets the recommended block size (number of rows) that will be loaded when processing tables. Processing each block entails overhead, so too small a setting can slow processing down.

Minimum value: 1; default value: 65536.
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) which, when exceeded, will cause data to be flushed to disk to save RAM.

The setting is useful if queries fail 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 of this setting twice as low as the Max memory usage setting value (by default, the maximum memory usage is limited to 10 GB).

For more information, see the ClickHouse® documentation.

See also the Distributed aggregation memory efficient setting.
Max bytes before external sort Management console CLI API SQL

This setting is similar to the previous setting, except that it is used for the sorting operation (ORDER BY).
Max bytes in distinct Management console CLI API SQL

The maximum amount of uncompressed data (in bytes) occupied by a hash table when using DISTINCT.

The minimum and default value is 0 (no limit).
Max bytes in join All interfaces

The maximum amount of uncompressed data (in bytes) occupied by a hash table when using JOIN.

The minimum and default value is 0 (no limit).
Max bytes in set All interfaces

The 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 (no limit).
Max bytes to read Management console CLI API SQL

The maximum amount of uncompressed data (in bytes) that can be read from a table when executing a query.

The minimum and default value is 0 (no limit).
Max bytes to sort Management console CLI API SQL

The maximum amount of uncompressed data (in bytes) that can be read from a table before sorting. This setting helps reduce RAM usage during a sort operation.

The minimum and default value is 0 (no limit).
Max bytes to transfer Management console CLI API SQL

The 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 (no limit).
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 fail.

The minimum and default value is 0 (no limit).
Max concurrent queries for user Management console API SQL

The maximum number of concurrently processed user queries to a MergeTree table.

Minimum value: 0 (no limit). Default value: 450.

For more information, see the ClickHouse® documentation.
Max execution time Management console CLI API SQL

Maximum query run time in milliseconds.

If query execution is at one of the stages of sorting or joining and finalizing aggregations, the limit on the maximum query run time will not be checked and may be exceeded.

Minimum value: 0 (no limit). Default value: 600000.
Max expanded ast elements Management console CLI API SQL

Maximum query syntax tree size (number of tree nodes) after alias and asterisk value expansion.

For complex queries, the syntax tree may contain too many elements. This setting enables you to block the execution of unnecessarily complex or unoptimized queries for large tables.

The default value is 500000. If too small a value is set, it may render ClickHouse unable to execute even simple queries.
Max final threads Management console

Maximum number of parallel threads for a SELECT query with the FINAL modifier.

The default value is equal to the Max threads setting value.

For more information, see the ClickHouse® documentation.
Max HTTP get redirects Management console API SQL

Sets the maximum number of redirect hops in URL engine tables for HTTP GET requests.

If 0, redirect hops are not allowed.

The default value is 0.

For more information, see the ClickHouse® documentation.
Max insert block size Management console CLI API SQL

Enables the creation of blocks of the specified size (in bytes) when inserting data into a table. This setting will work only if a server creates such blocks on its own.

The default value is 1048576.

For more information, see the ClickHouse® documentation.
Max insert threads Management console CLI Terraform API SQL

The 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 to enable running SELECT queries in parallel.

The greater the Max insert threads value is, the higher is cluster RAM usage.

For more information, see the ClickHouse® documentation.
Max memory usage Management console CLI API SQL

The maximum amount of RAM (in bytes) to execute 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 (no limit). The maximum value is limited to the Max server memory usage setting value, which cannot be changed directly. For more information, see Memory management in Managed Service for ClickHouse®. If the set value is greater than the Max server memory usage value, the latter is used.

If you are using Max bytes before external GROUP BY or Max bytes before external sort, we recommend setting their values to half of Max memory usage.

For more information, see the ClickHouse® documentation.
Max memory usage for user Management console CLI API SQL

The maximum amount of RAM (in bytes) to execute 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.

The restriction applies to all user queries that run concurrently within the same server, unlike Max memory usage.

The minimum and default value is 0 (no limit).
Max network bandwidth Management console CLI API SQL

The maximum network data transmission rate for running a single query (bytes per second).

The minimum and default value is 0 (no limit).
Max network bandwidth for user Management console CLI API SQL

The maximum network data transmission rate (bytes per second). This setting applies to all concurrent user queries, unlike Max network bandwidth.

The minimum and default value is 0 (no limit).
Max parser depth Management console CLI API

The maximum recursion depth in the recursive descent parser. It allows you to control the stack size.

The minimum value is 0 (unlimited recursion depth). The default value is 1000.

For more information, see the ClickHouse® documentation.
Max partitions per insert block Management console SQL

Limits the maximum number of partitions per insert block.

The minimum value is 0 (unlimited number of partitions). The default value is 100.

For more information, see the ClickHouse® documentation.
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.

Minimum value: 1; default value: 262144.
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 Management console CLI API SQL

Maximum replica delay (in milliseconds). If replica delay is greater than this setting, the replica is no longer used.

The minimum value is 1000 (1 second). The default value is 300000 (5 minutes).

See also the Fallback to stale replicas for distributed queries setting description.
Max result bytes Management console CLI API SQL

The maximum size of an uncompressed data query output (in bytes). This restriction also applies to subqueries and to parts of distributed queries that run on remote servers.

The minimum and default value is 0 (no limit).
Max result rows Management console CLI API SQL

The maximum number of output rows. This restriction also applies to subqueries and to parts of distributed queries that run on remote servers.

The minimum and default value is 0 (no limit).
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 (no limit).
Max rows in join All interfaces

The maximum number of rows in a hash table used when joining tables. Applies to SELECT… JOIN and the Join table engine.

The minimum and default value is 0 (no limit).

For more information, see the ClickHouse® documentation.
Max rows in set All interfaces

The maximum number of rows for a set created from a subquery in the IN section.

The minimum and default value is 0 (no limit).
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 (no limit).
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 (no limit).
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 (no limit).
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 (no limit).
Max temporary columns Management console CLI API SQL

The maximum number of temporary columns concurrently stored in RAM when executing a query (including permanent columns).

The minimum and default value is 0 (no limit).
Max temporary data on disk size for query Management console CLI API

The maximum amount of data (in bytes) consumed by temporary files on the disk for all concurrently running queries.

The minimum value is 0 (no limit).

For more information, see the ClickHouse® documentation.
Max temporary data on disk size for user Management console CLI API

The maximum amount of data (in bytes) consumed by temporary files on the disk for all concurrently running user queries.

The minimum value is 0 (no limit).

For more information, see the ClickHouse® documentation.
Max temporary non const columns Management console CLI API SQL

The maximum number of temporary columns concurrently stored in RAM when executing a query (excluding permanent columns).

The minimum and default value is 0 (no limit).
Max threads Management console CLI API SQL

The maximum number of query processing threads, excluding threads for reading data from remote servers. This setting applies to threads that are used for the parallel execution of stages of the query pipeline.

The minimum and default value is 0 (the value is calculated automatically based on the number of physical CPU cores excluding hyper-threading cores).

For more information, see the ClickHouse® documentation.
Memory overcommit ratio denominator Management console CLI API

Memory overcommit limit (in GB) when the hard memory usage limit is reached at the user level.

The minimum value is 0 (unlimited). The default value is 1.

For more information, see the ClickHouse® documentation.
Memory overcommit ratio denominator for user Management console CLI API

Memory overcommit limit (in GB) when the hard memory usage limit is reached globally.

The minimum value is 0 (unlimited). The default value is 1.

For more information, see the ClickHouse® documentation.
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 or released memory.

The values range from 0 to 1. The default value is 0.
Memory profiler step Management console API SQL

Memory profiler step in bytes. If, at the next query execution step, memory usage increases by the number of bytes specified in this setting, the profiler saves 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 Management console CLI API

Timeout (in microseconds) before releasing memory in the event of user-level memory overcommit.

The default value is 5000000 (five seconds).

For more information, see the ClickHouse® documentation.
Merge tree max bytes to use cache Management console CLI API SQL

The maximum size of a query (in bytes) that uses the uncompressed data cache. Queries larger than the specified value do not use the cache.

Use this setting in combination with the Use uncompressed cache setting.

The default value is 192x10x1024x1024.
Merge tree max rows to use cache Management console CLI API SQL

The maximum size of a query (in rows) that uses the uncompressed data cache. Queries larger than the specified value do not use the cache.

Use this setting in combination with the Use uncompressed cache setting.

The default value is 128x8192.
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 to read data from the file concurrently.

Minimum value: 1; default value: 24x10x1024x1024.
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 to read data from the file concurrently.

Minimum value: 1; default value: 20x8192.
Min bytes to use direct io Management console CLI API SQL

The 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 a reading operation involves large amounts of data, it is more efficient to read the data directly from the disk and bypassing the filesystem cache.

If the total amount of data stored for reading is greater than the value of this parameter, ClickHouse® will fetch the data directly from the disk.

The minimum and default value is 0 (direct reads are disabled).
Min count to compile CLI API SQL

This setting is deprecated.

Sets the minimum count of structurally identical queries to start compilation from.

For a value of 0, compilation is performed in synchronous mode: a query waits for the compilation to finish and then resumes. We recommended setting this value only for testing purposes.

For all other values, compilation is performed asynchronously in a separate thread: the result is used as soon as it is available, including by currently running queries.

Minimum value: 0; default value: 3.
Min count to compile expression Management console CLI API SQL

Sets the minimum count of identical expressions to start expression compilation with.

For a value of 0, compilation is performed in synchronous mode: an expression waits for the compilation to finish, then the query resumes. We recommended setting this value only for testing purposes.

For all other values, compilation is performed asynchronously in a separate thread: the result is used as soon as it is available, including by currently running queries.

Minimum value: 0; default value: 3.
Min execution speed All interfaces

The minimum query execution speed (rows per second).

When executing queries, ClickHouse® processes data in batches. If a batch is processed longer than the time set in timeout_before_checking_execution_speed, the query execution speed is checked. If the speed is lower than specified in the setting value, an exception is thrown.

The minimum and default value is 0 (no limit).

For more information, see the ClickHouse® documentation.
Min execution speed bytes All interfaces

The minimum query execution speed (bytes per second).

When executing queries, ClickHouse® processes data in batches. If a batch is processed longer than the time set in timeout_before_checking_execution_speed, the query execution speed is checked. If the speed is lower than specified in the setting value, an exception is thrown.

The minimum and default value is 0 (no limit).

For more information, see the ClickHouse® documentation.
Min insert block size bytes Management console CLI API SQL

The minimum block size (in bytes) that can be inserted into a table by an INSERT query. Smaller blocks are squashed.

The minimum value is 0 (block merges are disabled). The default value is 268435456 (256 MB).
Min insert block size rows Management console CLI API SQL

The minimum block size (in rows) that can be inserted into a table by an INSERT query. Smaller blocks are squashed.

The minimum value is 0 (block squashing is disabled). The default value is 1048576.
Output format json quote denormals Management console CLI API SQL

Determines whether to output special values for floating-point numbers (+nan, -nan, +inf, and -inf) when using JSON format for the output.

The default value is false, i.e., special values are not output.
Output format json quote_64bit integers Management console CLI API SQL

Defines 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, using quotes around 64-bit integers is disabled.
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 value.

This setting is enabled by default.

Warning

If a cluster has a single shard and multiple replicas, disabling this setting may damage the health of the cluster.

For more information, see the ClickHouse® documentation.
Priority Management console CLI API SQL

Sets the priority of a query.
- 0: No priority is used.
- 1: The highest priority.
- Other: The higher the number, the lower a query's priority.
The parameter is set for each query individually.

If ClickHouse® receives a query with a higher priority, execution of lower-priority queries is suspended until this incoming query is completed.

The minimum and default value is 0.
Quota mode Management console CLI SQL

The mode used to track resources utilized when quotas are enabled:
- default: Keys 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's IP address acts as the key. We recommended that you use this mode only if a user does not have a way to acquire a new IP address quickly to bypass quota restrictions, for example. This applies to both IPv4 and IPv6 addresses.
By default, no value is set (equivalent to default).

For more information, see the ClickHouse® documentation.
Read overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if the amount of read data exceeds one of the limits:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Readonly Management console CLI API SQL

Permissions for configuration, read, and write queries:
- 0 (default): Allows queries of all types.
- 1: Only read data queries are allowed.
- 2: Read data and edit settings queries are allowed.
This setting does not affect running DDL queries. To enable or disable DDL queries, use the Allow DDL setting.

See the example of creating a user with read-only access.
Receive timeout Management console CLI API SQL

Receive timeout (in milliseconds).

The default value is 300000 (five minutes).
Remote filesystem read method Management console CLI API

Determines how to read data from a remote file system.

The possible values are:
- read
- threadpool
The default value is threadpool.
Replication alter partitions sync Management console CLI API SQL

Sets wait mode for asynchronous actions on replicas for ALTER ... ATTACH DETACH DROP queries:
- 0: Do not wait.
- 1: Only wait for execution at its own (default).
- 2: Wait for every action to complete.
For more information, see the ClickHouse® documentation.
Result overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if the size of the result exceeds one of the limits:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Select sequential consistency Management console CLI API SQL

Determines whether to enable sequential consistency for SELECT queries.

By default, sequential consistency is disabled.

For more information, see the ClickHouse® documentation.
Send progress in HTTP headers Management console CLI API SQL

Enables the sending of notifications regarding query execution status using X-ClickHouse-Progress headers.

By default, notifications are disabled.
Send timeout Management console CLI API SQL

Send timeout (in milliseconds).

The default value is 300000 (five minutes).
Set overflow mode All interfaces

Determines ClickHouse® behavior if the amount of data exceeds one of the query complexity restrictions:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Skip unavailable shards Management console CLI API SQL

Enables silent skipping of inaccessible shards. A shard is considered unavailable if none of its replicas are available.

By default, silent skip is disabled.
Sort overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if the number of rows received before sorting exceeds one of the limits:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Timeout before checking execution speed Management console API SQL

Timeout (in seconds) before a query execution speed check. It is checked that the execution speed is not lower than that specified in the Min execution speed parameter.

The default value is 10.

For more information, see the ClickHouse® documentation.
Timeout overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if a query takes longer than max_execution_time:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Transfer overflow mode Management console CLI API SQL

Determines ClickHouse® behavior if the amount of data to be transferred to another server exceeds one of the limits:
- throw: Terminate the query and return an error.
- break: Return a partial output.
By default, the value is not set (equivalent to throw).
Transform null in Management console CLI SQL

When the compare option is enabled, NULL = NULL returns true in the IN operator.

This setting is disabled by default.

For more information, see the ClickHouse® documentation.
Use hedged requests Management console CLI Terraform API SQL

Enables hedged requests.

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 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 the ClickHouse® documentation.
Use uncompressed cache Management console CLI API SQL

Determines 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 family of tables). Enable this setting for users who initiate small queries frequently.

By default, the cache is disabled.

For more information, see the ClickHouse® documentation.

See also Merge tree max bytes to use cache and Merge tree max rows to use cache.
Wait for async insert Management console API SQL

Enables waiting for asynchronous insert handling.

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 the ClickHouse® documentation.
Wait for async insert timeout Management console API SQL

Asynchronous insert handling timeout (in seconds).

The default value is 120. If 0, there is no timeout.

For more information, see the ClickHouse® documentation.

Custom settings

You can override custom settings as variables in the settings profile, session, or query. Working with settings profiles is available if the user management via SQL is enabled for the cluster.

In Managed Service for ClickHouse®, the name of the custom setting must start with the custom_ predefined prefix. This prefix is non-editable, which means the users cannot change it.

Quota settings

Quotas allow you to limit the consumption of ClickHouse® resources for a specified time interval. Quota settings are set for an individual user.

If one of the quota limits is exceeded, the user will not be able to run new queries until the interval ends.
ClickHouse® will display a message about the quota being exceeded and give start time of the next interval, when the query ban will be lifted and the limit counters will be reset.

ClickHouse® also uses quotas to account for resource consumption.
For each user, there is a default quota that considers the consumption of all resources per hour but does not impose any restrictions.
The resource consumption level information is written to the ClickHouse® server log.

For more information, see the ClickHouse® documentation.

You can use the following quota settings:

Errors All interfaces

Limits the total number of failed queries.

The minimum value is 0 (no limit).
Execution time All interfaces

Limits the total query execution time in milliseconds.

The minimum value is 0 (no limit).
Interval duration All interfaces

Quota interval in milliseconds. The value must be a multiple of 1000.

The minimum value is 1000 (one second).
Queries All interfaces

Limits the total number of queries.

The minimum value is 0 (no limit).
Read rows All interfaces

Limits the total number of source rows read from tables for executing the query (including rows read from remote servers).

The minimum value is 0 (no limit).
Result rows All interfaces

Limits the total number of rows in query results.

The minimum value is 0 (no limit).

ClickHouse® is a registered trademark of ClickHouse, Inc.

ClickHouse® settings

Server-level settingsServer-level settings

Query-level settingsQuery-level settings

DBMS settingsDBMS settings

Custom settingsCustom settings

Quota settingsQuota settings

Was the article helpful?

Server-level settings

Query-level settings

DBMS settings

Custom settings

Quota settings