ClickHouse® settings

Written by

Updated at September 6, 2024

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 selected interface, the same setting may be represented in a different way, 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

You can use the following settings:

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.

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

For more information, see the 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 this table.

The 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 making an entry in the system.asynchronous_insert_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Asynchronous metric log enabled Management console CLI API

Determines whether historical metric values from the system.asynchronous_metrics table will be logged 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 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 this 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 making an entry in the system.asynchronous_metric_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Background buffer flush schedule pool size Management console

The number of threads for background data flushing in Buffer tables.

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

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

The number of threads for executing common background operations, such as cleaning up the file system, in MergeTree tables.

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

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

The number of threads for executing background operations in Distributed tables.

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

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

The number of threads for executing background operations to copy data from a replica in ReplicatedMergeTree tables.

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

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

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

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

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

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

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

The number of threads for background moves of data parts in MergeTree tables.

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

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

The number of threads for executing background merges and mutations in MergeTree tables.

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

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

The minimum value is 1, while the default one is 128. Changing this setting will restart ClickHouse® servers on cluster hosts.
Compression Management console API Terraform

Rules for compressing data in the MergeTree tables. For each rule, specify:
- Level: Compression level. Only available for the zstd compression method. The minimum value is 1, while the maximum one is 12. The default value is 9.
- Method: Compression method. There are two methods available: LZ4 and zstd.
- Min part size: Minimum size (in bytes) of a data part.
- Min part size ratio: Ratio of the smallest table chunk to the overall table size. ClickHouse® will only apply the rule to those tables where this ratio does not exceed Min part size ratio.
You can add multiple compression rules. ClickHouse® will check Min part size and Min part size ratio and apply the rules to tables that meet both conditions. 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 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.
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 learn more about Graphite support, see the documentation for ClickHouse®.
Kafka Management console CLI Terraform

Global authentication settings for integration with Apache Kafka®:
- 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. The default value is 300000 (5 minutes).
- Sasl mechanism: SASL authentication mechanism:
  - GSSAPI: 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 of mechanisms.
- 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 with SSL encryption.
  - SASL_PLAINTEXT: Authentication credentials are sent as plain text with SASL transport.
  - SASL_SSL: Authentication credentials are sent with SSL encryption and SASL as transport.
- 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. The default value is 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 integration with Apache Kafka®:
- 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 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.

The 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 errors in the cluster.
2. WARNING: Information about events that may cause errors in the cluster.
3. INFORMATION: Confirmations and information about events that do not lead to errors in the cluster.
4. DEBUG: System information for subsequent use in debugging.
5. TRACE: All available information on cluster operation.
For more information about log levels, see the ClickHouse® documentation.
Mark cache size Management console CLI API Terraform

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

The selected setting value is not a hard limit. ClickHouse® can use a little more or less memory for this cache.

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

The minimum value is 10, while the default one is 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.

The minimum value is 10, while the default one is 4096. Changing this setting will restart ClickHouse® servers on cluster hosts.
Max partition size to drop Management console CLI API Terraform

Maximum partition size (bytes) for the MergeTree family, at which a table can be deleted using the 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.

The default value is 53687091200 (50 GB). When the value is 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 table in the MergeTree family that you can delete using the 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.

The default value is 53687091200 (50 GB). When the value is 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.
- 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 data parts in a table. When exceeded, ClickHouse® will 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 data parts in a table. When exceeded, ClickHouse® throws 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.
  
  The minimum value is 0. The default value is 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.
  
  The 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.
  
  The default value is 1048576 (1 MB). Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the ClickHouse® documentation.
- 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. When exceeded, ClickHouse® throws the Too many parts ... exception.
  
  The 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 the ReplicatedMergeTree queue at the same time.
  
  The default value is 16. Changing this setting will restart ClickHouse® servers on cluster hosts.
- Merge selecting sleep ms: Timeout (in milliseconds) before merging a selection if no data part is selected.
  
  The default value is 5000 (5 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.
  
  The 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.
  
  The 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: Determines whether to only merge partitions 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 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.
  
  The default value is 0 (merges are 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 in a data part that can be stored in Wide format. You can set it along with the Min rows for wide part parameter.
  
  If the number of bytes in a data part is less than the set value, it is 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 in a data part that can be stored in Wide format. You can set it along with the Min bytes for wide part parameter.
  
  If the number of rows in a data part is less than the set value, it is 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 having free threads available for merges and avoiding the Too many parts ... exception.
  
  The 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.
  
  The default value is 8. Changing this setting will restart ClickHouse® servers on cluster hosts.
- Parts to delay insert: Number of active data chunks in a table. When it is exceeded, ClickHouse® will throttle the speed of table data inserts. An active chunk is a new chunk of data resulting from a merge.
  
  The 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 data parts in a table. When exceeded, ClickHouse® throws the Too many parts ... exception.
  
  The default value is 300. Changing this setting will restart ClickHouse® servers on cluster hosts.
  
  For more information, see the 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.
  
  The 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.
  
  The 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 metric values from the system.metrics and the system.events tables will be logged to system.metric_log.

The 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 will cause old records to be deleted from this table when exceeded.

The default value is 536870912 (0.5 GB). When the value is 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 making an entry in the system.metric_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
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 this 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 the making of an entry in the system.opentelemetry_span_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Part log retention size Management console CLI Terraform

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

The default value is 536870912 (0.5 GB). When the value is 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 the making of an entry in the system.part_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Query log retention size Management console CLI Terraform

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

The default value is 1073741824 (1 GB). When the value is 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 the making of an entry in the system.query_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Query thread log enabled Management console CLI Terraform

Determines whether information about the threads used to run queries will be logged. Logs are saved to the system.query_thread_log table.

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 will cause old records to be deleted from this table when exceeded.

The default value is 536870912 (0.5 GB). When the value is 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 the making of an entry in the system.query_thread_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
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 exceede, will cause old records to be deleted from this 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 making an entry in the system.query_views_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
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 this 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 making an entry in the system.session_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
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

The level of event logging in the system.text_log table. At each next level, the log will contain complete information from the previous one:
- ERROR: Information about errors in the DBMS.
- WARNING: Information about events that may cause errors in the DBMS.
- INFORMATION: Confirmation and information about events that do not lead to errors in the DBMS.
- DEBUG: System information for subsequent use in debugging.
- TRACE: All available information on the DBMS operation.
TRACE is the default. 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 will cause old records to be deleted from this table when exceeded.

The default value is 536870912 (0.5 GB). When the value is 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 the making of an entry in the system.text_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
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. Data is stored in the system.trace_log housekeeping table. The query_id value is an empty string.

The 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 (%). The log records are stored in the system.trace_log system view. The trace_type parameter takes the MemorySample parameter value. The probability refers to each memory allocation or release event, regardless of the amount of the allocated memory. Information is only selected when the amount of untracked memory exceeds the Total memory profiler step setting value.

By default, logging of accidental memory allocation and release is disabled (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 will cause old records to be deleted from this table when exceeded.

The default value is 536870912 (0.5 GB). When the value is 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 the making of an entry in the system.trace_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.
Uncompressed cache size Management console CLI API Terraform

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

The 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 this 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 making an entry in the system.zookeeper_log table and its deletion. The value must be a multiple of 1000.

The default value is 2592000000 (30 days). When the value is 0, the records will be stored for an unlimited period of time.

Query-level settings

DBMS settings

These settings apply at the individual user level.

You can use the following settings:

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

The possible values include:
- 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 along with types of data with a fixed size of up to 8B.

The possible values include:
- 0: Limited use of LowCardinality.
- 1: Unlimited 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 outdated behavior of the ClickHouse® server for ANY INNER|LEFT JOIN operations.

By default, the outdated behavior for JOINs 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 include:
- 0: Synchronous inserts are made, 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 timeout (ms) before inserting data 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 timeout (ms) before inserting data after the last INSERT query. If the value is different from zero, the Async insert busy timeout is extended 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, while the default one is 10000 (10 seconds).
Connect timeout with failover Management console API SQL

Remote server connect timeout (ms) for Distributed table engines 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

Determines the uniq* function to be used when performing a 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 value 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

Determines ClickHouse® behavior when the amount of data when running a SELECT DISTINCT query exceeds the limits:
- throw: Abort query execution and return an error.
- break: Return a partial result.
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 include:
- 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: Bans the use of such subqueries.
- local: Replaces the database and table in the subquery with local ones for the destination server (shard), leaving the normal IN/JOIN.
- global: Replaces the IN/JOIN query with the GLOBAL IN/GLOBAL JOIN one.
- allow: Allows the use of such subqueries.
By default, the value is not 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 output 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 consisting of NULL values for aggregation functions, in accordance with 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 request outputs when sending them 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 include:
- 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 the MergeTree family of tables.

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 the MergeTree family of tables.

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: Abort query execution and return an error.
- break: Return a partial result.
- 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); default: 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)< while the default one is 50000000.
HTTP connection timeout Management console CLI API SQL

Sets the HTTP connection timeout in milliseconds.

The minimum value is 1, while the default one 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.

The minimum value is 1; default: 100.
HTTP receive timeout Management console CLI API SQL

Sets the HTTP receive timeout (in milliseconds).

The minimum value is 1, while the default one is 1800000 (30 minutes).
HTTP send timeout Management console CLI API SQL

Sets the HTTP send timeout (in milliseconds).

The minimum value is 1; default: 1800000 (30 minutes).
Input format defaults for omitted fields Management console CLI API SQL

Determines whether default values will be entered into omitted fields for a column data type when inserting data using 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

Defines if NULL cells should be filled in with the default values if the cell data type does not allow storing NULL.

Enabled by default (NULL cells are filled in with the defaults).

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 causing the output of the now() SQL function (current date and time) to be inserted.

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 insert retries for ClickHouse® Keeper (or ZooKeeper) queries in replicated MergeTree tables.

The minimum value is 0 (no retries). The default value is 20.

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

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

The possible values include:
- 0: Inserting NULL in a column that does not allow NULL will throw an exception.
- 1: The default column value is inserted 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 in the consistent state, meaning that they contain linearized data from 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, multiple INSERT queries with quorum write can be run in parallel. If disabled, only one INSERT query with quorum write 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 is complete and a write has not occurred, ClickHouse® will stop the INSERT and return an error.

The minimum value is 1000 (1 second). The default is 60000 (1 minute).
Join algorithm Management console SQL

Defines the JOIN algorithm:
- auto: Hash join is used but, if the server is running out of memory, ClickHouse® tries to use merge join.
- direct: Join via a search by rows in the right-hand table, which are 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, such as Dictionary or EmbeddedRocksDB.
  - LEFT and INNER JOIN queries are executed.
- hash: Hash join.
- parallel_hash: Type of hash join with data split into segments and multiple hash tables created at the same time instead of a single hash table.
- partial_merge: Type of join by merging sorted lists (sort-merge join) with only the right-hand part of the table sorted completely.
- prefer_partial_merge: Partial_merge algorithm. Used whenever possible; otherwise, hash is used.
- sorting_merge: Join by merging sorted lists (sort-merge join).
The default algorithm is hash.

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

Defines the action to be performed by ClickHouse® if any of the following JOIN limits is reached:
- max_bytes_in_join
- max_rows_in_join
The possible values include:
- throw: ClickHouse® throws an exception and breaks the operation.
- break: ClickHouse® breaks the operation without throwing an exception.
By default, throw is used.
Join use nulls Management console CLI SQL

Controls the behavior of JOIN clauses. If the setting is enabled, empty cells resulting from a join are filled with NULL values; otherwise, the cells are filled with the defaults for 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.
Local filesystem read method Management console

Determines how to read data from the local file system.

The possible values include:
- nmap
- pread
- pread_threadpool
- read
The default value is pread.
Low cardinality allow in native format Management console CLI API SQL

Determines whether to use LowCardinality 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 containing constraints and conditions.

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

The minimum value is 1, while the default one is 65536.
Max bytes before external group by Management console CLI API SQL

Data accumulated during the GROUP BY aggregation are 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 in external memory disabled); default: 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 limitation is set).
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 limitation is set).
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 limitation is set).
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 limitation is set).
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 limitation is set).
Max bytes to transfer Management console CLI API SQL

The maximum amount of uncompressed data (in bytes) that can be passed to a remote server or saved to a temporary table when using GLOBAL IN.

The minimum and default value is 0 (no limitation is set).
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 limitation is set).
Max concurrent queries for user Management console API SQL

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

The minimum value is 0 (no limit), while the default one is 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.

The minimum value is 0 (no limit); default: 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 redirects in tables with the URL engine for HTTP GET requests.

If 0, redirects 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 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 limitation is set). 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 the 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 limitation is set).
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 limitation is set).
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 limitation is set).
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), while the default one 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 (no limit), while the default one 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.

The minimum value is 1; default: 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 one is 300000 (5 minutes).

See also the Fallback to stale replicas for distributed queries setting.
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 limitation is set).
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 limitation is set).
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 limitation is set).
Max rows in join All interfaces

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

The minimum and default value is 0 (no limitation is set).

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 limitation is set).
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 limitation is set).
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 limitation is set).
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 limitation is set).
Max rows to transfer Management console CLI API SQL

Maximum number of rows that can be passed to a remote server or saved in a temporary table when using GLOBAL IN.

The minimum and default value is 0 (no limitation is set).
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 limitation is set).
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 (unlimited).

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

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 limitation is set).
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.

Minimum and default values are 0 (compute the value automatically as the number of processor cores without accounting for Hyper-Threading).

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 (no limit); default: 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 (no limit); default: 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.

Possible values are 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 (5 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 selected 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 is 1. Default is 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 is 1. Default is 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.

Minimum value and default value are 0 (direct reads 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 continues running. 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.

The minimum value is 0, while the default one is 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, after which the query continues running. 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.

The minimum value is 0, while the default one is 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 limitation is set).

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 limitation is set).

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

The minimum value is 0 (block merges are disabled), while the default one 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 merged.

Minimum value is 0 (block squashing disabled). Default 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, preventing special values from being 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 put in quotes when output in JSON to maintain compatibility with most JavaScript engines; otherwise, no quotes are used.

By default, using quotes around 64-bit integers is disabled.
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 passed 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 data read exceeds one of the limits:
- throw: Abort query execution and return an error.
- break: Return a partial result.
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 (5 minutes).
Remote filesystem read method Management console CLI API

Determines how to read data from a remote file system.

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

Sets wait mode for asynchronous actions in ALTER ... ATTACH DETACH DROP queries on replicated tables:
- 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: Abort query execution and return an error.
- break: Return a partial result.
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 (5 minutes).
Set overflow mode All interfaces

Determines ClickHouse® behavior if the amount of data exceeds one of the query complexity restrictions:
- throw: Abort query execution and return an error.
- break: Return a partial result.
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: Abort query execution and return an error.
- break: Return a partial result.
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: Abort query execution and return an error.
- break: Return a partial result.
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: Abort query execution and return an error.
- break: Return a partial result.
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 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 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 include:
- 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.

The name of the user setting in Managed Service for ClickHouse® 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 the user exceeds one of the quota limits, they will not be able to execute new queries until the interval expires.
ClickHouse® will display a message about exceeding the quota and inform the user when the next interval starts. At the beginning of the new interval, the user will be able to run queries again 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