Skip to main content
Skip to main content
Edit this page

Session Settings

All below settings are also available in table system.settings. These settings are autogenerated from source.

add_http_cors_header​

Type
Default value
Type
Bool
Default value
0
Write add http CORS header.

additional_result_filter​

An additional filter expression to apply to the result of SELECT query. This setting is not applied to any subquery.

Example

additional_table_filters​

Type
Default value
Type
Map
Default value
{}
An additional filter expression that is applied after reading from the specified table.

Example

aggregate_functions_null_for_empty​

Type
Default value
Type
Bool
Default value
0
Enables or disables rewriting all aggregate functions in a query, adding -OrNull suffix to them. Enable it for SQL standard compatibility. It is implemented via query rewrite (similar to count_distinct_implementation setting) to get consistent results for distributed queries.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Example

Consider the following query with aggregate functions:

With aggregate_functions_null_for_empty = 0 it would produce:

With aggregate_functions_null_for_empty = 1 the result would be:

aggregation_in_order_max_block_bytes​

Type
Default value
Type
UInt64
Default value
50000000
Maximal size of block in bytes accumulated during aggregation in order of primary key. Lower block size allows to parallelize more final merge stage of aggregation.

aggregation_memory_efficient_merge_threads​

Type
Default value
Type
UInt64
Default value
0
Number of threads to use for merge intermediate aggregation results in memory efficient mode. When bigger, then more memory is consumed. 0 means - same as 'max_threads'.

allow_aggregate_partitions_independently​

Type
Default value
Type
Bool
Default value
0
Enable independent aggregation of partitions on separate threads when partition key suits group by key. Beneficial when number of partitions close to number of cores and partitions have roughly the same size

allow_archive_path_syntax​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
1

File/S3 engines/table function will parse paths with '::' as <archive> :: <file>\ if archive has correct extensio

allow_asynchronous_read_from_io_pool_for_merge_tree​

Type
Default value
Type
Bool
Default value
0
Use background I/O pool to read from MergeTree tables. This setting may increase performance for I/O bound queries

allow_changing_replica_until_first_data_packet​

Type
Default value
Type
Bool
Default value
0
If it's enabled, in hedged requests we can start new connection until receiving first data packet even if we have already made some progress (but progress haven't updated for receive_data_timeout timeout), otherwise we disable changing replica after the first time we made progress.

allow_create_index_without_type​

Type
Default value
Type
Bool
Default value
0
Allow CREATE INDEX query without TYPE. Query will be ignored. Made for SQL compatibility tests.

allow_custom_error_code_in_throwif​

Type
Default value
Type
Bool
Default value
0
Enable custom error code in function throwIf(). If true, thrown exceptions may have unexpected error codes.

allow_ddl​

Type
Default value
Type
Bool
Default value
1
If it is set to true, then a user is allowed to executed DDL queries.

allow_deprecated_database_ordinary​

Type
Default value
Type
Bool
Default value
0
Allow to create databases with deprecated Ordinary engine

allow_deprecated_error_prone_window_functions​

Type
Default value
Type
Bool
Default value
0

Allow usage of deprecated error prone window functions (neighbor, runningAccumulate, runningDifferenceStartingWithFirstValue, runningDifference)

allow_deprecated_snowflake_conversion_functions​

Type
Default value
Type
Bool
Default value
0

Functions snowflakeToDateTime, snowflakeToDateTime64, dateTimeToSnowflake, and dateTime64ToSnowflake are deprecated and disabled by default. Please use functions snowflakeIDToDateTime, snowflakeIDToDateTime64, dateTimeToSnowflakeID, and dateTime64ToSnowflakeID instead.

To re-enable the deprecated functions (e.g., during a transition period), please set this setting to true.

allow_deprecated_syntax_for_merge_tree​

Type
Default value
Type
Bool
Default value
0
Allow to create *MergeTree tables with deprecated engine definition syntax

allow_distributed_ddl​

Type
Default value
Type
Bool
Default value
1
If it is set to true, then a user is allowed to executed distributed DDL queries.

allow_drop_detached​

Type
Default value
Type
Bool
Default value
0
Allow ALTER TABLE ... DROP DETACHED PART[ITION] ... queries

allow_execute_multiif_columnar​

Type
Default value
Type
Bool
Default value
1
Allow execute multiIf function columnar

allow_experimental_analyzer​

Type
Default value
Type
Bool
Default value
1

Allow new query analyzer.

allow_experimental_codecs​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
If it is set to true, allow to specify experimental compression codecs (but we don't have those yet and this option does nothing).

allow_experimental_database_glue_catalog​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allow experimental database engine DataLakeCatalog with catalog_type = 'glue'

allow_experimental_database_iceberg​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allow experimental database engine DataLakeCatalog with catalog_type = 'iceberg'

allow_experimental_database_materialized_postgresql​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Allow to create database with Engine=MaterializedPostgreSQL(...).

allow_experimental_database_unity_catalog​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allow experimental database engine DataLakeCatalog with catalog_type = 'unity'

allow_experimental_dynamic_type​

Type
Default value
Type
Bool
Default value
1

Allows creation of Dynamic data type.

allow_experimental_full_text_index​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

If it is set to true, allow to use experimental full-text index.

allow_experimental_funnel_functions​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Enable experimental functions for funnel analysis.

allow_experimental_hash_functions​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Enable experimental hash functions

allow_experimental_inverted_index​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
If it is set to true, allow to use experimental inverted index.

allow_experimental_join_condition​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Support join with inequal conditions which involve columns from both left and right table. e.g. t1.y < t2.y.

allow_experimental_join_right_table_sorting​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

If it is set to true, and the conditions of join_to_sort_minimum_perkey_rows and join_to_sort_maximum_table_rows are met, rerange the right table by key to improve the performance in left or inner hash join.

allow_experimental_json_type​

Type
Default value
Type
Bool
Default value
1

Allows creation of JSON data type.

allow_experimental_kafka_offsets_storage_in_keeper​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allow experimental feature to store Kafka related offsets in ClickHouse Keeper. When enabled a ClickHouse Keeper path and replica name can be specified to the Kafka table engine. As a result instead of the regular Kafka engine, a new type of storage engine will be used that stores the committed offsets primarily in ClickHouse Keeper

allow_experimental_kusto_dialect​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Enable Kusto Query Language (KQL) - an alternative to SQL.

allow_experimental_live_view​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Allows creation of a deprecated LIVE VIEW.

Possible values:

  • 0 — Working with live views is disabled.
  • 1 — Working with live views is enabled.

allow_experimental_materialized_postgresql_table​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Allows to use the MaterializedPostgreSQL table engine. Disabled by default, because this feature is experimental

allow_experimental_nlp_functions​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Enable experimental functions for natural language processing.

allow_experimental_object_type​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Allow the obsolete Object data type

allow_experimental_parallel_reading_from_replicas​

Beta feature. Learn more.

Type
Default value
Type
UInt64
Default value
0
Use up to max_parallel_replicas the number of replicas from each shard for SELECT query execution. Reading is parallelized and coordinated dynamically. 0 - disabled, 1 - enabled, silently disable them in case of failure, 2 - enabled, throw an exception in case of failure

allow_experimental_prql_dialect​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Enable PRQL - an alternative to SQL.

allow_experimental_query_deduplication​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Experimental data deduplication for SELECT queries based on part UUIDs

allow_experimental_statistics​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allows defining columns with statistics and manipulate statistics.

allow_experimental_time_series_table​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allows creation of tables with the TimeSeries table engine.

Possible values:

allow_experimental_ts_to_grid_aggregate_function​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Experimental tsToGrid aggregate function for Prometheus-like timeseries resampling. Cloud only

allow_experimental_variant_type​

Type
Default value
Type
Bool
Default value
1

Allows creation of Variant data type.

allow_experimental_vector_similarity_index​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allow experimental vector similarity index

allow_experimental_window_view​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
Enable WINDOW VIEW. Not mature enough.

allow_general_join_planning​

Type
Default value
Type
Bool
Default value
1

Allows a more general join planning algorithm that can handle more complex conditions, but only works with hash join. If hash join is not enabled, then the usual join planning algorithm is used regardless of the value of this setting.

allow_get_client_http_header​

Type
Default value
Type
Bool
Default value
0

Allow to use the function getClientHTTPHeader which lets to obtain a value of an the current HTTP request's header. It is not enabled by default for security reasons, because some headers, such as Cookie, could contain sensitive info. Note that the X-ClickHouse-* and Authentication headers are always restricted and cannot be obtained with this function.

allow_hyperscan​

Type
Default value
Type
Bool
Default value
1
Allow functions that use Hyperscan library. Disable to avoid potentially long compilation times and excessive resource usage.

allow_introspection_functions​

Type
Default value
Type
Bool
Default value
0
Enables or disables introspection functions for query profiling.

Possible values:

  • 1 — Introspection functions enabled.
  • 0 — Introspection functions disabled.

See Also

allow_materialized_view_with_bad_select​

Type
Default value
Type
Bool
Default value
0

Allow CREATE MATERIALIZED VIEW with SELECT query that references nonexistent tables or columns. It must still be syntactically valid. Doesn't apply to refreshable MVs. Doesn't apply if the MV schema needs to be inferred from the SELECT query (i.e. if the CREATE has no column list and no TO table). Can be used for creating MV before its source table.

allow_named_collection_override_by_default​

Type
Default value
Type
Bool
Default value
1
Allow named collections' fields override by default.

allow_non_metadata_alters​

Type
Default value
Type
Bool
Default value
1
Allow to execute alters which affects not only tables metadata, but also data on disk

allow_nonconst_timezone_arguments​

Type
Default value
Type
Bool
Default value
0

Allow non-const timezone arguments in certain time-related functions like toTimeZone(), fromUnixTimestamp*(), snowflakeToDateTime*()

allow_nondeterministic_mutations​

Type
Default value
Type
Bool
Default value
0
User-level setting that allows mutations on replicated tables to make use of non-deterministic functions such as dictGet.

Given that, for example, dictionaries, can be out of sync across nodes, mutations that pull values from them are disallowed on replicated tables by default. Enabling this setting allows this behavior, making it the user's responsibility to ensure that the data used is in sync across all nodes.

Example

allow_nondeterministic_optimize_skip_unused_shards​

Type
Default value
Type
Bool
Default value
0
Allow nondeterministic (like rand or dictGet, since later has some caveats with updates) functions in sharding key.

Possible values:

  • 0 — Disallowed.
  • 1 — Allowed.

allow_not_comparable_types_in_comparison_functions​

Type
Default value
Type
Bool
Default value
0

Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in comparison functions equal/less/greater/etc.

allow_not_comparable_types_in_order_by​

Type
Default value
Type
Bool
Default value
0

Allows or restricts using not comparable types (like JSON/Object/AggregateFunction) in ORDER BY keys.

allow_prefetched_read_pool_for_local_filesystem​

Type
Default value
Type
Bool
Default value
0
Prefer prefetched threadpool if all parts are on local filesystem

allow_prefetched_read_pool_for_remote_filesystem​

Type
Default value
Type
Bool
Default value
1
Prefer prefetched threadpool if all parts are on remote filesystem

allow_push_predicate_ast_for_distributed_subqueries​

Type
Default value
Type
Bool
Default value
1

Allows push predicate on AST level for distributed subqueries with enabled anlyzer

allow_push_predicate_when_subquery_contains_with​

Type
Default value
Type
Bool
Default value
1
Allows push predicate when subquery contains WITH clause

allow_reorder_prewhere_conditions​

Type
Default value
Type
Bool
Default value
1

When moving conditions from WHERE to PREWHERE, allow reordering them to optimize filtering

allow_settings_after_format_in_insert​

Type
Default value
Type
Bool
Default value
0

Control whether SETTINGS after FORMAT in INSERT queries is allowed or not. It is not recommended to use this, since this may interpret part of SETTINGS as values.

Example:

But the following query will work only with allow_settings_after_format_in_insert:

Possible values:

  • 0 — Disallow.
  • 1 — Allow.
note

Use this setting only for backward compatibility if your use cases depend on old syntax.

allow_simdjson​

Type
Default value
Type
Bool
Default value
1
Allow using simdjson library in 'JSON*' functions if AVX2 instructions are available. If disabled rapidjson will be used.

allow_statistics_optimize​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Allows using statistics to optimize queries

allow_suspicious_codecs​

Type
Default value
Type
Bool
Default value
0

If it is set to true, allow to specify meaningless compression codecs.

allow_suspicious_fixed_string_types​

Type
Default value
Type
Bool
Default value
0
In CREATE TABLE statement allows creating columns of type FixedString(n) with n > 256. FixedString with length >= 256 is suspicious and most likely indicates a misuse

allow_suspicious_indices​

Type
Default value
Type
Bool
Default value
0

Reject primary/secondary indexes and sorting keys with identical expressions

allow_suspicious_low_cardinality_types​

Type
Default value
Type
Bool
Default value
0
Allows or restricts using LowCardinality with data types with fixed size of 8 bytes or less: numeric data types and FixedString(8_bytes_or_less).

For small fixed values using of LowCardinality is usually inefficient, because ClickHouse stores a numeric index for each row. As a result:

  • Disk space usage can rise.
  • RAM consumption can be higher, depending on a dictionary size.
  • Some functions can work slower due to extra coding/encoding operations.

Merge times in MergeTree-engine tables can grow due to all the reasons described above.

Possible values:

  • 1 — Usage of LowCardinality is not restricted.
  • 0 — Usage of LowCardinality is restricted.

allow_suspicious_primary_key​

Type
Default value
Type
Bool
Default value
0

Allow suspicious PRIMARY KEY/ORDER BY for MergeTree (i.e. SimpleAggregateFunction).

allow_suspicious_ttl_expressions​

Type
Default value
Type
Bool
Default value
0

Reject TTL expressions that don't depend on any of table's columns. It indicates a user error most of the time.

allow_suspicious_types_in_group_by​

Type
Default value
Type
Bool
Default value
0

Allows or restricts using Variant and Dynamic types in GROUP BY keys.

allow_suspicious_types_in_order_by​

Type
Default value
Type
Bool
Default value
0

Allows or restricts using Variant and Dynamic types in ORDER BY keys.

allow_suspicious_variant_types​

Type
Default value
Type
Bool
Default value
0

In CREATE TABLE statement allows specifying Variant type with similar variant types (for example, with different numeric or date types). Enabling this setting may introduce some ambiguity when working with values with similar types.

allow_unrestricted_reads_from_keeper​

Type
Default value
Type
Bool
Default value
0
Allow unrestricted (without condition on path) reads from system.zookeeper table, can be handy, but is not safe for zookeeper

alter_move_to_space_execute_async​

Type
Default value
Type
Bool
Default value
0
Execute ALTER TABLE MOVE ... TO [DISK|VOLUME] asynchronously

alter_partition_verbose_result​

Type
Default value
Type
Bool
Default value
0
Enables or disables the display of information about the parts to which the manipulation operations with partitions and parts have been successfully applied. Applicable to ATTACH PARTITION|PART and to FREEZE PARTITION.

Possible values:

  • 0 — disable verbosity.
  • 1 — enable verbosity.

Example

alter_sync​

Type
Default value
Type
UInt64
Default value
1
Allows to set up waiting for actions to be executed on replicas by ALTER, OPTIMIZE or TRUNCATE queries.

Possible values:

  • 0 — Do not wait.
  • 1 — Wait for own execution.
  • 2 — Wait for everyone.

Cloud default value: 0.

note

alter_sync is applicable to Replicated tables only, it does nothing to alters of not Replicated tables.

analyze_index_with_space_filling_curves​

Type
Default value
Type
Bool
Default value
1
If a table has a space-filling curve in its index, e.g. ORDER BY mortonEncode(x, y) or ORDER BY hilbertEncode(x, y), and the query has conditions on its arguments, e.g. x >= 10 AND x <= 20 AND y >= 20 AND y <= 30, use the space-filling curve for index analysis.

analyzer_compatibility_join_using_top_level_identifier​

Type
Default value
Type
Bool
Default value
0

Force to resolve identifier in JOIN USING from projection (for example, in SELECT a + 1 AS b FROM t1 JOIN t2 USING (b) join will be performed by t1.a + 1 = t2.b, rather then t1.b = t2.b).

any_join_distinct_right_table_keys​

Type
Default value
Type
Bool
Default value
0

Enables legacy ClickHouse server behaviour in ANY INNER|LEFT JOIN operations.

note

Use this setting only for backward compatibility if your use cases depend on legacy JOIN behaviour.

When the legacy behaviour is enabled:

  • Results of t1 ANY LEFT JOIN t2 and t2 ANY RIGHT JOIN t1 operations are not equal because ClickHouse uses the logic with many-to-one left-to-right table keys mapping.
  • Results of ANY INNER JOIN operations contain all rows from the left table like the SEMI LEFT JOIN operations do.

When the legacy behaviour is disabled:

  • Results of t1 ANY LEFT JOIN t2 and t2 ANY RIGHT JOIN t1 operations are equal because ClickHouse uses the logic which provides one-to-many keys mapping in ANY RIGHT JOIN operations.
  • Results of ANY INNER JOIN operations contain one row per key from both the left and right tables.

Possible values:

  • 0 — Legacy behaviour is disabled.
  • 1 — Legacy behaviour is enabled.

See also:

apply_deleted_mask​

Type
Default value
Type
Bool
Default value
1
Enables filtering out rows deleted with lightweight DELETE. If disabled, a query will be able to read those rows. This is useful for debugging and "undelete" scenarios

apply_mutations_on_fly​

Type
Default value
Type
Bool
Default value
0
If true, mutations (UPDATEs and DELETEs) which are not materialized in data part will be applied on SELECTs.

apply_settings_from_server​

Type
Default value
Type
Bool
Default value
1

Whether the client should accept settings from server.

This only affects operations performed on the client side, in particular parsing the INSERT input data and formatting the query result. Most of query execution happens on the server and is not affected by this setting.

Normally this setting should be set in user profile (users.xml or queries like ALTER USER), not through the client (client command line arguments, SET query, or SETTINGS section of SELECT query). Through the client it can be changed to false, but can't be changed to true (because the server won't send the settings if user profile has apply_settings_from_server = false).

Note that initially (24.12) there was a server setting (send_settings_to_client), but latter it got replaced with this client setting, for better usability.

asterisk_include_alias_columns​

Type
Default value
Type
Bool
Default value
0
Include ALIAS columns for wildcard query (SELECT *).

Possible values:

  • 0 - disabled
  • 1 - enabled

asterisk_include_materialized_columns​

Type
Default value
Type
Bool
Default value
0
Include MATERIALIZED columns for wildcard query (SELECT *).

Possible values:

  • 0 - disabled
  • 1 - enabled

async_insert​

Type
Default value
Type
Bool
Default value
0
If true, data from INSERT query is stored in queue and later flushed to table in background. If wait_for_async_insert is false, INSERT query is processed almost instantly, otherwise client will wait until data will be flushed to table

async_insert_busy_timeout_decrease_rate​

Type
Default value
Type
Double
Default value
0.2

The exponential growth rate at which the adaptive asynchronous insert timeout decreases

async_insert_busy_timeout_increase_rate​

Type
Default value
Type
Double
Default value
0.2

The exponential growth rate at which the adaptive asynchronous insert timeout increases

async_insert_busy_timeout_max_ms​

Type
Default value
Type
Milliseconds
Default value
200

Maximum time to wait before dumping collected data per query since the first data appeared.

async_insert_busy_timeout_min_ms​

Type
Default value
Type
Milliseconds
Default value
50

If auto-adjusting is enabled through async_insert_use_adaptive_busy_timeout, minimum time to wait before dumping collected data per query since the first data appeared. It also serves as the initial value for the adaptive algorithm

async_insert_deduplicate​

Type
Default value
Type
Bool
Default value
0
For async INSERT queries in the replicated table, specifies that deduplication of inserting blocks should be performed

async_insert_max_data_size​

Type
Default value
Type
UInt64
Default value
10485760

Maximum size in bytes of unparsed data collected per query before being inserted

async_insert_max_query_number​

Type
Default value
Type
UInt64
Default value
450
Maximum number of insert queries before being inserted

async_insert_poll_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
10

Timeout for polling data from asynchronous insert queue

async_insert_use_adaptive_busy_timeout​

Type
Default value
Type
Bool
Default value
1

If it is set to true, use adaptive busy timeout for asynchronous inserts

async_query_sending_for_remote​

Type
Default value
Type
Bool
Default value
1

Enables asynchronous connection creation and query sending while executing remote query.

Enabled by default.

async_socket_for_remote​

Type
Default value
Type
Bool
Default value
1

Enables asynchronous read from socket while executing remote query.

Enabled by default.

azure_allow_parallel_part_upload​

Type
Default value
Type
Bool
Default value
1

Use multiple threads for azure multipart upload.

azure_check_objects_after_upload​

Type
Default value
Type
Bool
Default value
0

Check each uploaded object in azure blob storage to be sure that upload was successful

azure_create_new_file_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables creating a new file on each insert in azure engine tables

azure_ignore_file_doesnt_exist​

Type
Default value
Type
Bool
Default value
0

Ignore absence of file if it does not exist when reading certain keys.

Possible values:

  • 1 — SELECT returns empty result.
  • 0 — SELECT throws an exception.

azure_list_object_keys_size​

Type
Default value
Type
UInt64
Default value
1000
Maximum number of files that could be returned in batch by ListObject request

azure_max_blocks_in_multipart_upload​

Type
Default value
Type
UInt64
Default value
50000

Maximum number of blocks in multipart upload for Azure.

azure_max_inflight_parts_for_one_file​

Type
Default value
Type
UInt64
Default value
20

The maximum number of a concurrent loaded parts in multipart upload request. 0 means unlimited.

azure_max_single_part_copy_size​

Type
Default value
Type
UInt64
Default value
268435456

The maximum size of object to copy using single part copy to Azure blob storage.

azure_max_single_part_upload_size​

Type
Default value
Type
UInt64
Default value
104857600
The maximum size of object to upload using singlepart upload to Azure blob storage.

azure_max_single_read_retries​

Type
Default value
Type
UInt64
Default value
4
The maximum number of retries during single Azure blob storage read.

azure_max_unexpected_write_error_retries​

Type
Default value
Type
UInt64
Default value
4

The maximum number of retries in case of unexpected errors during Azure blob storage write

azure_max_upload_part_size​

Type
Default value
Type
UInt64
Default value
5368709120

The maximum size of part to upload during multipart upload to Azure blob storage.

azure_min_upload_part_size​

Type
Default value
Type
UInt64
Default value
16777216

The minimum size of part to upload during multipart upload to Azure blob storage.

azure_sdk_max_retries​

Type
Default value
Type
UInt64
Default value
10

Maximum number of retries in azure sdk

azure_sdk_retry_initial_backoff_ms​

Type
Default value
Type
UInt64
Default value
10

Minimal backoff between retries in azure sdk

azure_sdk_retry_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
1000

Maximal backoff between retries in azure sdk

azure_skip_empty_files​

Type
Default value
Type
Bool
Default value
0

Enables or disables skipping empty files in S3 engine.

Possible values:

  • 0 — SELECT throws an exception if empty file is not compatible with requested format.
  • 1 — SELECT returns empty result for empty file.

azure_strict_upload_part_size​

Type
Default value
Type
UInt64
Default value
0

The exact size of part to upload during multipart upload to Azure blob storage.

azure_throw_on_zero_files_match​

Type
Default value
Type
Bool
Default value
0

Throw an error if matched zero files according to glob expansion rules.

Possible values:

  • 1 — SELECT throws an exception.
  • 0 — SELECT returns empty result.

azure_truncate_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables truncate before insert in azure engine tables.

azure_upload_part_size_multiply_factor​

Type
Default value
Type
UInt64
Default value
2

Multiply azure_min_upload_part_size by this factor each time azure_multiply_parts_count_threshold parts were uploaded from a single write to Azure blob storage.

azure_upload_part_size_multiply_parts_count_threshold​

Type
Default value
Type
UInt64
Default value
500

Each time this number of parts was uploaded to Azure blob storage, azure_min_upload_part_size is multiplied by azure_upload_part_size_multiply_factor.

backup_restore_batch_size_for_keeper_multi​

Type
Default value
Type
UInt64
Default value
1000
Maximum size of batch for multi request to [Zoo]Keeper during backup or restore

backup_restore_batch_size_for_keeper_multiread​

Type
Default value
Type
UInt64
Default value
10000
Maximum size of batch for multiread request to [Zoo]Keeper during backup or restore

backup_restore_failure_after_host_disconnected_for_seconds​

Type
Default value
Type
UInt64
Default value
3600

If a host during a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation doesn't recreate its ephemeral 'alive' node in ZooKeeper for this amount of time then the whole backup or restore is considered as failed. This value should be bigger than any reasonable time for a host to reconnect to ZooKeeper after a failure. Zero means unlimited.

backup_restore_finish_timeout_after_error_sec​

Type
Default value
Type
UInt64
Default value
180

How long the initiator should wait for other host to react to the 'error' node and stop their work on the current BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.

backup_restore_keeper_fault_injection_probability​

Type
Default value
Type
Float
Default value
0
Approximate probability of failure for a keeper request during backup or restore. Valid value is in interval [0.0f, 1.0f]

backup_restore_keeper_fault_injection_seed​

Type
Default value
Type
UInt64
Default value
0
0 - random seed, otherwise the setting value

backup_restore_keeper_max_retries​

Type
Default value
Type
UInt64
Default value
1000

Max retries for [Zoo]Keeper operations in the middle of a BACKUP or RESTORE operation. Should be big enough so the whole operation won't fail because of a temporary [Zoo]Keeper failure.

backup_restore_keeper_max_retries_while_handling_error​

Type
Default value
Type
UInt64
Default value
20

Max retries for [Zoo]Keeper operations while handling an error of a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.

backup_restore_keeper_max_retries_while_initializing​

Type
Default value
Type
UInt64
Default value
20

Max retries for [Zoo]Keeper operations during the initialization of a BACKUP ON CLUSTER or RESTORE ON CLUSTER operation.

backup_restore_keeper_retry_initial_backoff_ms​

Type
Default value
Type
UInt64
Default value
100
Initial backoff timeout for [Zoo]Keeper operations during backup or restore

backup_restore_keeper_retry_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
5000
Max backoff timeout for [Zoo]Keeper operations during backup or restore

backup_restore_keeper_value_max_size​

Type
Default value
Type
UInt64
Default value
1048576
Maximum size of data of a [Zoo]Keeper's node during backup

backup_restore_s3_retry_attempts​

Type
Default value
Type
UInt64
Default value
1000

Setting for Aws::Client::RetryStrategy, Aws::Client does retries itself, 0 means no retries. It takes place only for backup/restore.

cache_warmer_threads​

ClickHouse Cloud only

Type
Default value
Type
UInt64
Default value
4
Only has an effect in ClickHouse Cloud. Number of background threads for speculatively downloading new data parts into file cache, when cache_populated_by_fetch is enabled. Zero to disable.

calculate_text_stack_trace​

Type
Default value
Type
Bool
Default value
1
Calculate text stack trace in case of exceptions during query execution. This is the default. It requires symbol lookups that may slow down fuzzing tests when a huge amount of wrong queries are executed. In normal cases, you should not disable this option.

cancel_http_readonly_queries_on_client_close​

Type
Default value
Type
Bool
Default value
0
Cancels HTTP read-only queries (e.g. SELECT) when a client closes the connection without waiting for the response.

Cloud default value: 1.

cast_ipv4_ipv6_default_on_conversion_error​

Type
Default value
Type
Bool
Default value
0

CAST operator into IPv4, CAST operator into IPV6 type, toIPv4, toIPv6 functions will return default value instead of throwing exception on conversion error.

cast_keep_nullable​

Type
Default value
Type
Bool
Default value
0
Enables or disables keeping of the Nullable data type in CAST operations.

When the setting is enabled and the argument of CAST function is Nullable, the result is also transformed to Nullable type. When the setting is disabled, the result always has the destination type exactly.

Possible values:

  • 0 — The CAST result has exactly the destination type specified.
  • 1 — If the argument type is Nullable, the CAST result is transformed to Nullable(DestinationDataType).

Examples

The following query results in the destination data type exactly:

Result:

The following query results in the Nullable modification on the destination data type:

Result:

See Also

cast_string_to_dynamic_use_inference​

Type
Default value
Type
Bool
Default value
0

Use types inference during String to Dynamic conversio

cast_string_to_variant_use_inference​

Type
Default value
Type
Bool
Default value
1

Use types inference during String to Variant conversion.

check_query_single_value_result​

Type
Default value
Type
Bool
Default value
1
Defines the level of detail for the CHECK TABLE query result for MergeTree family engines .

Possible values:

  • 0 — the query shows a check status for every individual data part of a table.
  • 1 — the query shows the general table check status.

check_referential_table_dependencies​

Type
Default value
Type
Bool
Default value
0
Check that DDL query (such as DROP TABLE or RENAME) will not break referential dependencies

check_table_dependencies​

Type
Default value
Type
Bool
Default value
1
Check that DDL query (such as DROP TABLE or RENAME) will not break dependencies

checksum_on_read​

Type
Default value
Type
Bool
Default value
1
Validate checksums on reading. It is enabled by default and should be always enabled in production. Please do not expect any benefits in disabling this setting. It may only be used for experiments and benchmarks. The setting is only applicable for tables of MergeTree family. Checksums are always validated for other table engines and when receiving data over the network.

cloud_mode​

Type
Default value
Type
Bool
Default value
0
Cloud mode

cloud_mode_database_engine​

Type
Default value
Type
UInt64
Default value
1

The database engine allowed in Cloud. 1 - rewrite DDLs to use Replicated database, 2 - rewrite DDLs to use Shared database

cloud_mode_engine​

Type
Default value
Type
UInt64
Default value
1
The engine family allowed in Cloud.

  • 0 - allow everything
  • 1 - rewrite DDLs to use *ReplicatedMergeTree
  • 2 - rewrite DDLs to use SharedMergeTree
  • 3 - rewrite DDLs to use SharedMergeTree except when explicitly passed remote disk is specified

UInt64 to minimize public part

cluster_for_parallel_replicas​

Beta feature. Learn more.

Cluster for a shard in which current server is located

collect_hash_table_stats_during_aggregation​

Type
Default value
Type
Bool
Default value
1
Enable collecting hash table statistics to optimize memory allocatio

collect_hash_table_stats_during_joins​

Type
Default value
Type
Bool
Default value
1

Enable collecting hash table statistics to optimize memory allocatio

compatibility​

The compatibility setting causes ClickHouse to use the default settings of a previous version of ClickHouse, where the previous version is provided as the setting.

If settings are set to non-default values, then those settings are honored (only settings that have not been modified are affected by the compatibility setting).

This setting takes a ClickHouse version number as a string, like 22.3, 22.8. An empty value means that this setting is disabled.

Disabled by default.

note

In ClickHouse Cloud the compatibility setting must be set by ClickHouse Cloud support. Please open a case to have it set.

compatibility_ignore_auto_increment_in_create_table​

Type
Default value
Type
Bool
Default value
0
Ignore AUTO_INCREMENT keyword in column declaration if true, otherwise return error. It simplifies migration from MySQL

compatibility_ignore_collation_in_create_table​

Type
Default value
Type
Bool
Default value
1
Compatibility ignore collation in create table

compile_aggregate_expressions​

Type
Default value
Type
Bool
Default value
1
Enables or disables JIT-compilation of aggregate functions to native code. Enabling this setting can improve the performance.

Possible values:

  • 0 — Aggregation is done without JIT compilation.
  • 1 — Aggregation is done using JIT compilation.

See Also

compile_expressions​

Type
Default value
Type
Bool
Default value
0
Compile some scalar functions and operators to native code. Due to a bug in the LLVM compiler infrastructure, on AArch64 machines, it is known to lead to a nullptr dereference and, consequently, server crash. Do not enable this setting.

compile_sort_description​

Type
Default value
Type
Bool
Default value
1
Compile sort description to native code.

connect_timeout​

Type
Default value
Type
Seconds
Default value
10
Connection timeout if there are no replicas.

connect_timeout_with_failover_ms​

Type
Default value
Type
Milliseconds
Default value
1000

The timeout in milliseconds for connecting to a remote server for a Distributed table engine, if the 'shard' and 'replica' sections are used in the cluster definition. If unsuccessful, several attempts are made to connect to various replicas.

connect_timeout_with_failover_secure_ms​

Type
Default value
Type
Milliseconds
Default value
1000

Connection timeout for selecting first healthy replica (for secure connections).

connection_pool_max_wait_ms​

Type
Default value
Type
Milliseconds
Default value
0
The wait time in milliseconds for a connection when the connection pool is full.

Possible values:

  • Positive integer.
  • 0 — Infinite timeout.

connections_with_failover_max_tries​

Type
Default value
Type
UInt64
Default value
3
The maximum number of connection attempts with each replica for the Distributed table engine.

convert_query_to_cnf​

Type
Default value
Type
Bool
Default value
0
When set to true, a SELECT query will be converted to conjuctive normal form (CNF). There are scenarios where rewriting a query in CNF may execute faster (view this Github issue for an explanation).

For example, notice how the following SELECT query is not modified (the default behavior):

The result is:

Let's set convert_query_to_cnf to true and see what changes:

Notice the WHERE clause is rewritten in CNF, but the result set is the identical - the Boolean logic is unchanged:

Possible values: true, false

count_distinct_implementation​

Type
Default value
Type
String
Default value
uniqExact
Specifies which of the uniq* functions should be used to perform the COUNT(DISTINCT ...) construction.

Possible values:

count_distinct_optimization​

Type
Default value
Type
Bool
Default value
0
Rewrite count distinct to subquery of group by

create_if_not_exists​

Type
Default value
Type
Bool
Default value
0

Enable IF NOT EXISTS for CREATE statement by default. If either this setting or IF NOT EXISTS is specified and a table with the provided name already exists, no exception will be thrown.

create_index_ignore_unique​

Type
Default value
Type
Bool
Default value
0
Ignore UNIQUE keyword in CREATE UNIQUE INDEX. Made for SQL compatibility tests.

create_replicated_merge_tree_fault_injection_probability​

Type
Default value
Type
Float
Default value
0
The probability of a fault injection during table creation after creating metadata in ZooKeeper

create_table_empty_primary_key_by_default​

Type
Default value
Type
Bool
Default value
0
Allow to create *MergeTree tables with empty primary key when ORDER BY and PRIMARY KEY not specified

cross_join_min_bytes_to_compress​

Type
Default value
Type
UInt64
Default value
1073741824

Minimal size of block to compress in CROSS JOIN. Zero value means - disable this threshold. This block is compressed when any of the two thresholds (by rows or by bytes) are reached.

cross_join_min_rows_to_compress​

Type
Default value
Type
UInt64
Default value
10000000

Minimal count of rows to compress block in CROSS JOIN. Zero value means - disable this threshold. This block is compressed when any of the two thresholds (by rows or by bytes) are reached.

data_type_default_nullable​

Type
Default value
Type
Bool
Default value
0
Allows data types without explicit modifiers NULL or NOT NULL in column definition will be Nullable.

Possible values:

  • 1 — The data types in column definitions are set to Nullable by default.
  • 0 — The data types in column definitions are set to not Nullable by default.

database_atomic_wait_for_drop_and_detach_synchronously​

Type
Default value
Type
Bool
Default value
0
Adds a modifier SYNC to all DROP and DETACH queries.

Possible values:

  • 0 — Queries will be executed with delay.
  • 1 — Queries will be executed without delay.

database_replicated_allow_explicit_uuid​

Type
Default value
Type
UInt64
Default value
0

0 - Don't allow to explicitly specify UUIDs for tables in Replicated databases. 1 - Allow. 2 - Allow, but ignore the specified UUID and generate a random one instead.

database_replicated_allow_heavy_create​

Type
Default value
Type
Bool
Default value
0

Allow long-running DDL queries (CREATE AS SELECT and POPULATE) in Replicated database engine. Note that it can block DDL queue for a long time.

database_replicated_allow_only_replicated_engine​

Type
Default value
Type
Bool
Default value
0
Allow to create only Replicated tables in database with engine Replicated

database_replicated_allow_replicated_engine_arguments​

Type
Default value
Type
UInt64
Default value
0

0 - Don't allow to explicitly specify ZooKeeper path and replica name for *MergeTree tables in Replicated databases. 1 - Allow. 2 - Allow, but ignore the specified path and use default one instead. 3 - Allow and don't log a warning.

database_replicated_always_detach_permanently​

Type
Default value
Type
Bool
Default value
0
Execute DETACH TABLE as DETACH TABLE PERMANENTLY if database engine is Replicated

database_replicated_enforce_synchronous_settings​

Type
Default value
Type
Bool
Default value
0
Enforces synchronous waiting for some queries (see also database_atomic_wait_for_drop_and_detach_synchronously, mutations_sync, alter_sync). Not recommended to enable these settings.

database_replicated_initial_query_timeout_sec​

Type
Default value
Type
UInt64
Default value
300
Sets how long initial DDL query should wait for Replicated database to process previous DDL queue entries in seconds.

Possible values:

  • Positive integer.
  • 0 — Unlimited.

decimal_check_overflow​

Type
Default value
Type
Bool
Default value
1
Check overflow of decimal arithmetic/comparison operations

deduplicate_blocks_in_dependent_materialized_views​

Type
Default value
Type
Bool
Default value
0
Enables or disables the deduplication check for materialized views that receive data from Replicated* tables.

Possible values:

0 — Disabled. 1 — Enabled.

Usage

By default, deduplication is not performed for materialized views but is done upstream, in the source table. If an INSERTed block is skipped due to deduplication in the source table, there will be no insertion into attached materialized views. This behaviour exists to enable the insertion of highly aggregated data into materialized views, for cases where inserted blocks are the same after materialized view aggregation but derived from different INSERTs into the source table. At the same time, this behaviour "breaks" INSERT idempotency. If an INSERT into the main table was successful and INSERT into a materialized view failed (e.g. because of communication failure with ClickHouse Keeper) a client will get an error and can retry the operation. However, the materialized view won't receive the second insert because it will be discarded by deduplication in the main (source) table. The setting deduplicate_blocks_in_dependent_materialized_views allows for changing this behaviour. On retry, a materialized view will receive the repeat insert and will perform a deduplication check by itself, ignoring check result for the source table, and will insert rows lost because of the first failure.

default_materialized_view_sql_security​

Type
Default value
Type
SQLSecurityType
Default value
DEFINER

Allows to set a default value for SQL SECURITY option when creating a materialized view. More about SQL security.

The default value is DEFINER.

default_max_bytes_in_join​

Type
Default value
Type
UInt64
Default value
1000000000
Maximum size of right-side table if limit is required but max_bytes_in_join is not set.

default_normal_view_sql_security​

Type
Default value
Type
SQLSecurityType
Default value
INVOKER

Allows to set default SQL SECURITY option while creating a normal view. More about SQL security.

The default value is INVOKER.

default_table_engine​

Type
Default value
Type
DefaultTableEngine
Default value
MergeTree

Default table engine to use when ENGINE is not set in a CREATE statement.

Possible values:

  • a string representing any valid table engine name

Cloud default value: SharedMergeTree.

Example

Query:

Result:

In this example, any new table that does not specify an Engine will use the Log table engine:

Query:

Result:

default_temporary_table_engine​

Type
Default value
Type
DefaultTableEngine
Default value
Memory
Same as default_table_engine but for temporary tables.

In this example, any new temporary table that does not specify an Engine will use the Log table engine:

Query:

Result:

default_view_definer​

Type
Default value
Type
String
Default value
CURRENT_USER

Allows to set default DEFINER option while creating a view. More about SQL security.

The default value is CURRENT_USER.

describe_compact_output​

Type
Default value
Type
Bool
Default value
0
If true, include only column names and types into result of DESCRIBE query

describe_extend_object_types​

Type
Default value
Type
Bool
Default value
0
Deduce concrete type of columns of type Object in DESCRIBE query

describe_include_subcolumns​

Type
Default value
Type
Bool
Default value
0
Enables describing subcolumns for a DESCRIBE query. For example, members of a Tuple or subcolumns of a Map, Nullable or an Array data type.

Possible values:

  • 0 — Subcolumns are not included in DESCRIBE queries.
  • 1 — Subcolumns are included in DESCRIBE queries.

Example

See an example for the DESCRIBE statement.

describe_include_virtual_columns​

Type
Default value
Type
Bool
Default value
0
If true, virtual columns of table will be included into result of DESCRIBE query

dialect​

Type
Default value
Type
Dialect
Default value
clickhouse
Which dialect will be used to parse query

dictionary_validate_primary_key_type​

Type
Default value
Type
Bool
Default value
0

Validate primary key type for dictionaries. By default id type for simple layouts will be implicitly converted to UInt64.

distinct_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens when the amount of data exceeds one of the limits.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

distributed_aggregation_memory_efficient​

Type
Default value
Type
Bool
Default value
1
Is the memory-saving mode of distributed aggregation enabled.

distributed_background_insert_batch​

Type
Default value
Type
Bool
Default value
0
Enables/disables inserted data sending in batches.

When batch sending is enabled, the Distributed table engine tries to send multiple files of inserted data in one operation instead of sending them separately. Batch sending improves cluster performance by better-utilizing server and network resources.

Possible values:

  • 1 — Enabled.
  • 0 — Disabled.

distributed_background_insert_max_sleep_time_ms​

Type
Default value
Type
Milliseconds
Default value
30000
Maximum interval for the Distributed table engine to send data. Limits exponential growth of the interval set in the distributed_background_insert_sleep_time_ms setting.

Possible values:

  • A positive integer number of milliseconds.

distributed_background_insert_sleep_time_ms​

Type
Default value
Type
Milliseconds
Default value
100
Base interval for the Distributed table engine to send data. The actual interval grows exponentially in the event of errors.

Possible values:

  • A positive integer number of milliseconds.

distributed_background_insert_split_batch_on_failure​

Type
Default value
Type
Bool
Default value
0
Enables/disables splitting batches on failures.

Sometimes sending particular batch to the remote shard may fail, because of some complex pipeline after (i.e. MATERIALIZED VIEW with GROUP BY) due to Memory limit exceeded or similar errors. In this case, retrying will not help (and this will stuck distributed sends for the table) but sending files from that batch one by one may succeed INSERT.

So installing this setting to 1 will disable batching for such batches (i.e. temporary disables distributed_background_insert_batch for failed batches).

Possible values:

  • 1 — Enabled.
  • 0 — Disabled.
note

This setting also affects broken batches (that may appears because of abnormal server (machine) termination and no fsync_after_insert/fsync_directories for Distributed table engine).

note

You should not rely on automatic batch splitting, since this may hurt performance.

distributed_background_insert_timeout​

Type
Default value
Type
UInt64
Default value
0
Timeout for insert query into distributed. Setting is used only with insert_distributed_sync enabled. Zero value means no timeout.

distributed_cache_bypass_connection_pool​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
0

Only has an effect in ClickHouse Cloud. Allow to bypass distributed cache connection pool

distributed_cache_connect_max_tries​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
20

Only has an effect in ClickHouse Cloud. Number of tries to connect to distributed cache if unsuccessful

distributed_cache_data_packet_ack_window​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
5

Only has an effect in ClickHouse Cloud. A window for sending ACK for DataPacket sequence in a single distributed cache read request

distributed_cache_discard_connection_if_unread_data​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
1

Only has an effect in ClickHouse Cloud. Discard connection if some data is unread.

distributed_cache_fetch_metrics_only_from_current_az​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
1

Only has an effect in ClickHouse Cloud. Fetch metrics only from current availability zone in system.distributed_cache_metrics, system.distributed_cache_events

distributed_cache_log_mode​

ClickHouse Cloud only
Type
Default value
Type
DistributedCacheLogMode
Default value
on_error

Only has an effect in ClickHouse Cloud. Mode for writing to system.distributed_cache_log

distributed_cache_max_unacked_inflight_packets​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
10

Only has an effect in ClickHouse Cloud. A maximum number of unacknowledged in-flight packets in a single distributed cache read request

distributed_cache_min_bytes_for_seek​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
0

Only has an effect in ClickHouse Cloud. Minimum number of bytes to do seek in distributed cache.

distributed_cache_pool_behaviour_on_limit​

ClickHouse Cloud only
Type
Default value
Type
DistributedCachePoolBehaviourOnLimit
Default value
wait

Only has an effect in ClickHouse Cloud. Identifies behaviour of distributed cache connection on pool limit reached

distributed_cache_read_alignment​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
0

Only has an effect in ClickHouse Cloud. A setting for testing purposes, do not change it

distributed_cache_read_request_max_tries​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
20

Only has an effect in ClickHouse Cloud. Number of tries to do distributed cache request if unsuccessful

distributed_cache_receive_response_wait_milliseconds​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
60000

Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive data for request from distributed cache

distributed_cache_receive_timeout_milliseconds​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
10000

Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive any kind of response from distributed cache

distributed_cache_throw_on_error​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
0

Only has an effect in ClickHouse Cloud. Rethrow exception happened during communication with distributed cache or exception received from distributed cache. Otherwise fallback to skipping distributed cache on error

distributed_cache_wait_connection_from_pool_milliseconds​

ClickHouse Cloud only
Type
Default value
Type
UInt64
Default value
100

Only has an effect in ClickHouse Cloud. Wait time in milliseconds to receive connection from connection pool if distributed_cache_pool_behaviour_on_limit is wait

distributed_connections_pool_size​

Type
Default value
Type
UInt64
Default value
1024
The maximum number of simultaneous connections with remote servers for distributed processing of all queries to a single Distributed table. We recommend setting a value no less than the number of servers in the cluster.

distributed_ddl_entry_format_version​

Type
Default value
Type
UInt64
Default value
5
Compatibility version of distributed DDL (ON CLUSTER) queries

distributed_ddl_output_mode​

Type
Default value
Type
DistributedDDLOutputMode
Default value
throw
Sets format of distributed DDL query result.

Possible values:

  • throw — Returns result set with query execution status for all hosts where query is finished. If query has failed on some hosts, then it will rethrow the first exception. If query is not finished yet on some hosts and distributed_ddl_task_timeout exceeded, then it throws TIMEOUT_EXCEEDED exception.
  • none — Is similar to throw, but distributed DDL query returns no result set.
  • null_status_on_timeout — Returns NULL as execution status in some rows of result set instead of throwing TIMEOUT_EXCEEDED if query is not finished on the corresponding hosts.
  • never_throw — Do not throw TIMEOUT_EXCEEDED and do not rethrow exceptions if query has failed on some hosts.
  • none_only_active - similar to none, but doesn't wait for inactive replicas of the Replicated database. Note: with this mode it's impossible to figure out that the query was not executed on some replica and will be executed in background.
  • null_status_on_timeout_only_active — similar to null_status_on_timeout, but doesn't wait for inactive replicas of the Replicated database
  • throw_only_active — similar to throw, but doesn't wait for inactive replicas of the Replicated database

Cloud default value: none.

distributed_ddl_task_timeout​

Type
Default value
Type
Int64
Default value
180
Sets timeout for DDL query responses from all hosts in cluster. If a DDL request has not been performed on all hosts, a response will contain a timeout error and a request will be executed in an async mode. Negative value means infinite.

Possible values:

  • Positive integer.
  • 0 — Async mode.
  • Negative integer — infinite timeout.

distributed_foreground_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables synchronous data insertion into a Distributed table.

By default, when inserting data into a Distributed table, the ClickHouse server sends data to cluster nodes in background mode. When distributed_foreground_insert=1, the data is processed synchronously, and the INSERT operation succeeds only after all the data is saved on all shards (at least one replica for each shard if internal_replication is true).

Possible values:

  • 0 — Data is inserted in background mode.
  • 1 — Data is inserted in synchronous mode.

Cloud default value: 1.

See Also

distributed_group_by_no_merge​

Type
Default value
Type
UInt64
Default value
0
Do not merge aggregation states from different servers for distributed query processing, you can use this in case it is for certain that there are different keys on different shards

Possible values:

  • 0 — Disabled (final query processing is done on the initiator node).
  • 1 - Do not merge aggregation states from different servers for distributed query processing (query completely processed on the shard, initiator only proxy the data), can be used in case it is for certain that there are different keys on different shards.
  • 2 - Same as 1 but applies ORDER BY and LIMIT (it is not possible when the query processed completely on the remote node, like for distributed_group_by_no_merge=1) on the initiator (can be used for queries with ORDER BY and/or LIMIT).

Example

distributed_insert_skip_read_only_replicas​

Type
Default value
Type
Bool
Default value
0

Enables skipping read-only replicas for INSERT queries into Distributed.

Possible values:

  • 0 — INSERT was as usual, if it will go to read-only replica it will fail
  • 1 — Initiator will skip read-only replicas before sending data to shards.

distributed_product_mode​

Type
Default value
Type
DistributedProductMode
Default value
deny
Changes the behaviour of distributed subqueries.

ClickHouse applies this setting when the query contains the product of distributed tables, i.e. when the query for a distributed table contains a non-GLOBAL subquery for the distributed table.

Restrictions:

  • Only applied for IN and JOIN subqueries.
  • Only if the FROM section uses a distributed table containing more than one shard.
  • If the subquery concerns a distributed table containing more than one shard.
  • Not used for a table-valued remote function.

Possible values:

  • deny — Default value. Prohibits using these types of subqueries (returns the "Double-distributed in/JOIN subqueries is denied" exception).
  • 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 GLOBAL IN/GLOBAL JOIN.
  • allow — Allows the use of these types of subqueries.

distributed_push_down_limit​

Type
Default value
Type
UInt64
Default value
1
Enables or disables LIMIT applying on each shard separately.

This will allow to avoid:

  • Sending extra rows over network;
  • Processing rows behind the limit on the initiator.

Starting from 21.9 version you cannot get inaccurate results anymore, since distributed_push_down_limit changes query execution only if at least one of the conditions met:

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

See also:

distributed_replica_error_cap​

Type
Default value
Type
UInt64
Default value
1000
- Type: unsigned int

  • Default value: 1000

The error count of each replica is capped at this value, preventing a single replica from accumulating too many errors.

See also:

distributed_replica_error_half_life​

Type
Default value
Type
Seconds
Default value
60
- Type: seconds

  • Default value: 60 seconds

Controls how fast errors in distributed tables are zeroed. If a replica is unavailable for some time, accumulates 5 errors, and distributed_replica_error_half_life is set to 1 second, then the replica is considered normal 3 seconds after the last error.

See also:

distributed_replica_max_ignored_errors​

Type
Default value
Type
UInt64
Default value
0
- Type: unsigned int

  • Default value: 0

The number of errors that will be ignored while choosing replicas (according to load_balancing algorithm).

See also:

do_not_merge_across_partitions_select_final​

Type
Default value
Type
Bool
Default value
0
Merge parts only in one partition in select final

empty_result_for_aggregation_by_constant_keys_on_empty_set​

Type
Default value
Type
Bool
Default value
1
Return empty result when aggregating by constant keys on empty set.

empty_result_for_aggregation_by_empty_set​

Type
Default value
Type
Bool
Default value
0
Return empty result when aggregating without keys on empty set.

enable_adaptive_memory_spill_scheduler​

Experimental feature. Learn more.
Type
Default value
Type
Bool
Default value
0

Trigger processor to spill data into external storage adpatively. grace join is supported at present.

enable_blob_storage_log​

Type
Default value
Type
Bool
Default value
1

Write information about blob storage operations to system.blob_storage_log table

enable_deflate_qpl_codec​

Type
Default value
Type
Bool
Default value
0
If turned on, the DEFLATE_QPL codec may be used to compress columns.

enable_early_constant_folding​

Type
Default value
Type
Bool
Default value
1
Enable query optimization where we analyze function and subqueries results and rewrite query if there are constants there

enable_extended_results_for_datetime_functions​

Type
Default value
Type
Bool
Default value
0
Enables or disables returning results of type:

Possible values:

  • 0 — Functions return Date or DateTime for all types of arguments.
  • 1 — Functions return Date32 or DateTime64 for Date32 or DateTime64 arguments and Date or DateTime otherwise.

enable_filesystem_cache​

Type
Default value
Type
Bool
Default value
1
Use cache for remote filesystem. This setting does not turn on/off cache for disks (must be done via disk config), but allows to bypass cache for some queries if intended

enable_filesystem_cache_log​

Type
Default value
Type
Bool
Default value
0
Allows to record the filesystem caching log for each query

enable_filesystem_cache_on_write_operations​

Type
Default value
Type
Bool
Default value
0
Write into cache on write operations. To actually work this setting requires be added to disk config too

enable_filesystem_read_prefetches_log​

Type
Default value
Type
Bool
Default value
0
Log to system.filesystem prefetch_log during query. Should be used only for testing or debugging, not recommended to be turned on by default

enable_global_with_statement​

Type
Default value
Type
Bool
Default value
1

Propagate WITH statements to UNION queries and all subqueries

enable_hdfs_pread​

Type
Default value
Type
Bool
Default value
1

Enable or disables pread for HDFS files. By default, hdfsPread is used. If disabled, hdfsRead and hdfsSeek will be used to read hdfs files.

enable_http_compression​

Type
Default value
Type
Bool
Default value
0
Enables or disables data compression in the response to an HTTP request.

For more information, read the HTTP interface description.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

enable_job_stack_trace​

Type
Default value
Type
Bool
Default value
1

Output stack trace of a job creator when job results in exceptio

enable_lightweight_delete​

Type
Default value
Type
Bool
Default value
1
Enable lightweight DELETE mutations for mergetree tables.

enable_memory_bound_merging_of_aggregation_results​

Type
Default value
Type
Bool
Default value
1
Enable memory bound merging strategy for aggregation.

enable_multiple_prewhere_read_steps​

Type
Default value
Type
Bool
Default value
1
Move more conditions from WHERE to PREWHERE and do reads from disk and filtering in multiple steps if there are multiple conditions combined with AND

enable_named_columns_in_function_tuple​

Type
Default value
Type
Bool
Default value
0

Generate named tuples in function tuple() when all names are unique and can be treated as unquoted identifiers.

enable_optimize_predicate_expression​

Type
Default value
Type
Bool
Default value
1

Turns on predicate pushdown in SELECT queries.

Predicate pushdown may significantly reduce network traffic for distributed queries.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Usage

Consider the following queries:

  1. SELECT count() FROM test_table WHERE date = '2018-10-10'
  2. SELECT count() FROM (SELECT * FROM test_table) WHERE date = '2018-10-10'

If enable_optimize_predicate_expression = 1, then the execution time of these queries is equal because ClickHouse applies WHERE to the subquery when processing it.

If enable_optimize_predicate_expression = 0, then the execution time of the second query is much longer because the WHERE clause applies to all the data after the subquery finishes.

enable_optimize_predicate_expression_to_final_subquery​

Type
Default value
Type
Bool
Default value
1
Allow push predicate to final subquery.

enable_order_by_all​

Type
Default value
Type
Bool
Default value
1
Enables or disables sorting with ORDER BY ALL syntax, see ORDER BY.

Possible values:

  • 0 — Disable ORDER BY ALL.
  • 1 — Enable ORDER BY ALL.

Example

Query:

Result:

enable_parsing_to_custom_serialization​

Type
Default value
Type
Bool
Default value
1

If true then data can be parsed directly to columns with custom serialization (e.g. Sparse) according to hints for serialization got from the table.

enable_positional_arguments​

Type
Default value
Type
Bool
Default value
1

Enables or disables supporting positional arguments for GROUP BY, LIMIT BY, ORDER BY statements.

Possible values:

  • 0 — Positional arguments aren't supported.
  • 1 — Positional arguments are supported: column numbers can use instead of column names.

Example

Query:

Result:

enable_reads_from_query_cache​

Type
Default value
Type
Bool
Default value
1
If turned on, results of SELECT queries are retrieved from the query cache.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

enable_s3_requests_logging​

Type
Default value
Type
Bool
Default value
0
Enable very explicit logging of S3 requests. Makes sense for debug only.

enable_scalar_subquery_optimization​

Type
Default value
Type
Bool
Default value
1

If it is set to true, prevent scalar subqueries from (de)serializing large scalar values and possibly avoid running the same subquery more than once.

enable_sharing_sets_for_mutations​

Type
Default value
Type
Bool
Default value
1
Allow sharing set objects build for IN subqueries between different tasks of the same mutation. This reduces memory usage and CPU consumptio

enable_software_prefetch_in_aggregation​

Type
Default value
Type
Bool
Default value
1
Enable use of software prefetch in aggregatio

enable_unaligned_array_join​

Type
Default value
Type
Bool
Default value
0
Allow ARRAY JOIN with multiple arrays that have different sizes. When this settings is enabled, arrays will be resized to the longest one.

enable_url_encoding​

Type
Default value
Type
Bool
Default value
1
Allows to enable/disable decoding/encoding path in uri in URL engine tables.

Enabled by default.

enable_vertical_final​

Type
Default value
Type
Bool
Default value
1

If enable, remove duplicated rows during FINAL by marking rows as deleted and filtering them later instead of merging rows

enable_writes_to_query_cache​

Type
Default value
Type
Bool
Default value
1
If turned on, results of SELECT queries are stored in the query cache.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

enable_zstd_qat_codec​

Type
Default value
Type
Bool
Default value
0

If turned on, the ZSTD_QAT codec may be used to compress columns.

enforce_strict_identifier_format​

Type
Default value
Type
Bool
Default value
0

If enabled, only allow identifiers containing alphanumeric characters and underscores.

engine_file_allow_create_multiple_files​

Type
Default value
Type
Bool
Default value
0
Enables or disables creating a new file on each insert in file engine tables if the format has the suffix (JSON, ORC, Parquet, etc.). If enabled, on each insert a new file will be created with a name following this pattern:

data.Parquet -> data.1.Parquet -> data.2.Parquet, etc.

Possible values:

  • 0 — INSERT query appends new data to the end of the file.
  • 1 — INSERT query creates a new file.

engine_file_empty_if_not_exists​

Type
Default value
Type
Bool
Default value
0
Allows to select data from a file engine table without file.

Possible values:

  • 0 — SELECT throws exception.
  • 1 — SELECT returns empty result.

engine_file_skip_empty_files​

Type
Default value
Type
Bool
Default value
0
Enables or disables skipping empty files in File engine tables.

Possible values:

  • 0 — SELECT throws an exception if empty file is not compatible with requested format.
  • 1 — SELECT returns empty result for empty file.

engine_file_truncate_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables truncate before insert in File engine tables.

Possible values:

  • 0 — INSERT query appends new data to the end of the file.
  • 1 — INSERT query replaces existing content of the file with the new data.

engine_url_skip_empty_files​

Type
Default value
Type
Bool
Default value
0
Enables or disables skipping empty files in URL engine tables.

Possible values:

  • 0 — SELECT throws an exception if empty file is not compatible with requested format.
  • 1 — SELECT returns empty result for empty file.

except_default_mode​

Type
Default value
Type
SetOperationMode
Default value
ALL
Set default mode in EXCEPT query. Possible values: empty string, 'ALL', 'DISTINCT'. If empty, query without mode will throw exception.

external_storage_connect_timeout_sec​

Type
Default value
Type
UInt64
Default value
10
Connect timeout in seconds. Now supported only for MySQL

external_storage_max_read_bytes​

Type
Default value
Type
UInt64
Default value
0
Limit maximum number of bytes when table with external engine should flush history data. Now supported only for MySQL table engine, database engine, and dictionary. If equal to 0, this setting is disabled

external_storage_max_read_rows​

Type
Default value
Type
UInt64
Default value
0
Limit maximum number of rows when table with external engine should flush history data. Now supported only for MySQL table engine, database engine, and dictionary. If equal to 0, this setting is disabled

external_storage_rw_timeout_sec​

Type
Default value
Type
UInt64
Default value
300
Read/write timeout in seconds. Now supported only for MySQL

external_table_functions_use_nulls​

Type
Default value
Type
Bool
Default value
1
Defines how mysql, postgresql and odbc table functions use Nullable columns.

Possible values:

  • 0 — The table function explicitly uses Nullable columns.
  • 1 — The table function implicitly uses Nullable columns.

Usage

If the setting is set to 0, the table function does not make Nullable columns and inserts default values instead of NULL. This is also applicable for NULL values inside arrays.

external_table_strict_query​

Type
Default value
Type
Bool
Default value
0
If it is set to true, transforming expression to local filter is forbidden for queries to external tables.

extract_key_value_pairs_max_pairs_per_row​

Type
Default value
Type
UInt64
Default value
1000

Max number of pairs that can be produced by the extractKeyValuePairs function. Used as a safeguard against consuming too much memory.

extremes​

Type
Default value
Type
Bool
Default value
0
Whether to count extreme values (the minimums and maximums in columns of a query result). Accepts 0 or 1. By default, 0 (disabled). For more information, see the section "Extreme values".

fallback_to_stale_replicas_for_distributed_queries​

Type
Default value
Type
Bool
Default value
1
Forces a query to an out-of-date replica if updated data is not available. See Replication.

ClickHouse selects the most relevant from the outdated replicas of the table.

Used when performing SELECT from a distributed table that points to replicated tables.

By default, 1 (enabled).

filesystem_cache_boundary_alignment​

Type
Default value
Type
UInt64
Default value
0

Filesystem cache boundary alignment. This setting is applied only for non-disk read (e.g. for cache of remote table engines / table functions, but not for storage configuration of MergeTree tables). Value 0 means no alignment.

filesystem_cache_enable_background_download_during_fetch​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
1

Only has an effect in ClickHouse Cloud. Wait time to lock cache for space reservation in filesystem cache

filesystem_cache_enable_background_download_for_metadata_files_in_packed_storage​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
1

Only has an effect in ClickHouse Cloud. Wait time to lock cache for space reservation in filesystem cache

filesystem_cache_max_download_size​

Type
Default value
Type
UInt64
Default value
137438953472
Max remote filesystem cache size that can be downloaded by a single query

filesystem_cache_name​

Filesystem cache name to use for stateless table engines or data lakes

filesystem_cache_prefer_bigger_buffer_size​

Type
Default value
Type
Bool
Default value
1

Prefer bigger buffer size if filesystem cache is enabled to avoid writing small file segments which deteriorate cache performance. On the other hand, enabling this setting might increase memory usage.

filesystem_cache_reserve_space_wait_lock_timeout_milliseconds​

Type
Default value
Type
UInt64
Default value
1000

Wait time to lock cache for space reservation in filesystem cache

filesystem_cache_segments_batch_size​

Type
Default value
Type
UInt64
Default value
20
Limit on size of a single batch of file segments that a read buffer can request from cache. Too low value will lead to excessive requests to cache, too large may slow down eviction from cache

filesystem_cache_skip_download_if_exceeds_per_query_cache_write_limit​

Type
Default value
Type
Bool
Default value
1

Skip download from remote filesystem if exceeds query cache size

filesystem_prefetch_max_memory_usage​

Type
Default value
Type
UInt64
Default value
1073741824
Maximum memory usage for prefetches.

filesystem_prefetch_step_bytes​

Type
Default value
Type
UInt64
Default value
0
Prefetch step in bytes. Zero means auto - approximately the best prefetch step will be auto deduced, but might not be 100% the best. The actual value might be different because of setting filesystem_prefetch_min_bytes_for_single_read_task

filesystem_prefetch_step_marks​

Type
Default value
Type
UInt64
Default value
0
Prefetch step in marks. Zero means auto - approximately the best prefetch step will be auto deduced, but might not be 100% the best. The actual value might be different because of setting filesystem_prefetch_min_bytes_for_single_read_task

filesystem_prefetches_limit​

Type
Default value
Type
UInt64
Default value
200
Maximum number of prefetches. Zero means unlimited. A setting filesystem_prefetches_max_memory_usage is more recommended if you want to limit the number of prefetches

final​

Type
Default value
Type
Bool
Default value
0
Automatically applies FINAL modifier to all tables in a query, to tables where FINAL is applicable, including joined tables and tables in sub-queries, and distributed tables.

Possible values:

  • 0 - disabled
  • 1 - enabled

Example:

flatten_nested​

Type
Default value
Type
Bool
Default value
1
Sets the data format of a nested columns.

Possible values:

  • 1 — Nested column is flattened to separate arrays.
  • 0 — Nested column stays a single array of tuples.

Usage

If the setting is set to 0, it is possible to use an arbitrary level of nesting.

Examples

Query:

Result:

Query:

Result:

force_aggregate_partitions_independently​

Type
Default value
Type
Bool
Default value
0
Force the use of optimization when it is applicable, but heuristics decided not to use it

force_aggregation_in_order​

Type
Default value
Type
Bool
Default value
0
The setting is used by the server itself to support distributed queries. Do not change it manually, because it will break normal operations. (Forces use of aggregation in order on remote nodes during distributed aggregation).

force_data_skipping_indices​

Disables query execution if passed data skipping indices wasn't used.

Consider the following example:

force_grouping_standard_compatibility​

Type
Default value
Type
Bool
Default value
1

Make GROUPING function to return 1 when argument is not used as an aggregation key

force_index_by_date​

Type
Default value
Type
Bool
Default value
0
Disables query execution if the index can't be used by date.

Works with tables in the MergeTree family.

If force_index_by_date=1, ClickHouse checks whether the query has a date key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For example, the condition Date != ' 2000-01-01 ' is acceptable even when it matches all the data in the table (i.e., running the query requires a full scan). For more information about ranges of data in MergeTree tables, see MergeTree.

force_optimize_projection​

Type
Default value
Type
Bool
Default value
0
Enables or disables the obligatory use of projections in SELECT queries, when projection optimization is enabled (see optimize_use_projections setting).

Possible values:

  • 0 — Projection optimization is not obligatory.
  • 1 — Projection optimization is obligatory.

force_optimize_projection_name​

If it is set to a non-empty string, check that this projection is used in the query at least once.

Possible values:

  • string: name of projection that used in a query

force_optimize_skip_unused_shards​

Type
Default value
Type
UInt64
Default value
0
Enables or disables query execution if optimize_skip_unused_shards is enabled and skipping of unused shards is not possible. If the skipping is not possible and the setting is enabled, an exception will be thrown.

Possible values:

  • 0 — Disabled. ClickHouse does not throw an exception.
  • 1 — Enabled. Query execution is disabled only if the table has a sharding key.
  • 2 — Enabled. Query execution is disabled regardless of whether a sharding key is defined for the table.

force_optimize_skip_unused_shards_nesting​

Type
Default value
Type
UInt64
Default value
0
Controls force_optimize_skip_unused_shards (hence still requires force_optimize_skip_unused_shards) depends on the nesting level of the distributed query (case when you have Distributed table that look into another Distributed table).

Possible values:

  • 0 - Disabled, force_optimize_skip_unused_shards works always.
  • 1 — Enables force_optimize_skip_unused_shards only for the first level.
  • 2 — Enables force_optimize_skip_unused_shards up to the second level.

force_primary_key​

Type
Default value
Type
Bool
Default value
0
Disables query execution if indexing by the primary key is not possible.

Works with tables in the MergeTree family.

If force_primary_key=1, ClickHouse checks to see if the query has a primary key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For more information about data ranges in MergeTree tables, see MergeTree.

force_remove_data_recursively_on_drop​

Type
Default value
Type
Bool
Default value
0
Recursively remove data on DROP query. Avoids 'Directory not empty' error, but may silently remove detached data

formatdatetime_f_prints_scale_number_of_digits​

Type
Default value
Type
Bool
Default value
0

Formatter '%f' in function 'formatDateTime' prints only the scale amount of digits for a DateTime64 instead of fixed 6 digits.

formatdatetime_f_prints_single_zero​

Type
Default value
Type
Bool
Default value
0

Formatter '%f' in function 'formatDateTime' prints a single zero instead of six zeros if the formatted value has no fractional seconds.

formatdatetime_format_without_leading_zeros​

Type
Default value
Type
Bool
Default value
0
Formatters '%c', '%l' and '%k' in function 'formatDateTime' print months and hours without leading zeros.

formatdatetime_parsedatetime_m_is_month_name​

Type
Default value
Type
Bool
Default value
1

Formatter '%M' in functions 'formatDateTime' and 'parseDateTime' print/parse the month name instead of minutes.

fsync_metadata​

Type
Default value
Type
Bool
Default value
1
Enables or disables fsync when writing .sql files. Enabled by default.

It makes sense to disable it if the server has millions of tiny tables that are constantly being created and destroyed.

function_implementation​

Choose function implementation for specific target or variant (experimental). If empty enable all of them.

function_json_value_return_type_allow_complex​

Type
Default value
Type
Bool
Default value
0
Control whether allow to return complex type (such as: struct, array, map) for json_value function.

Possible values:

  • true — Allow.
  • false — Disallow.

function_json_value_return_type_allow_nullable​

Type
Default value
Type
Bool
Default value
0
Control whether allow to return NULL when value is not exist for JSON_VALUE function.

Possible values:

  • true — Allow.
  • false — Disallow.

function_locate_has_mysql_compatible_argument_order​

Type
Default value
Type
Bool
Default value
1

Controls the order of arguments in function locate.

Possible values:

  • 0 — Function locate accepts arguments (haystack, needle[, start_pos]).
  • 1 — Function locate accepts arguments (needle, haystack, [, start_pos]) (MySQL-compatible behavior)

function_range_max_elements_in_block​

Type
Default value
Type
UInt64
Default value
500000000
Sets the safety threshold for data volume generated by function range. Defines the maximum number of values generated by function per block of data (sum of array sizes for every row in a block).

Possible values:

  • Positive integer.

See Also

function_sleep_max_microseconds_per_block​

Type
Default value
Type
UInt64
Default value
3000000

Maximum number of microseconds the function sleep is allowed to sleep for each block. If a user called it with a larger value, it throws an exception. It is a safety threshold.

function_visible_width_behavior​

Type
Default value
Type
UInt64
Default value
1

The version of visibleWidth behavior. 0 - only count the number of code points; 1 - correctly count zero-width and combining characters, count full-width characters as two, estimate the tab width, count delete characters.

geo_distance_returns_float64_on_float64_arguments​

Type
Default value
Type
Bool
Default value
1

If all four arguments to geoDistance, greatCircleDistance, greatCircleAngle functions are Float64, return Float64 and use double precision for internal calculations. In previous ClickHouse versions, the functions always returned Float32.

geotoh3_lon_lat_input_order​

Type
Default value
Type
Bool
Default value
0

Function 'geoToH3' accepts (lon, lat) if true, otherwise (lat, lon).

glob_expansion_max_elements​

Type
Default value
Type
UInt64
Default value
1000
Maximum number of allowed addresses (For external storages, table functions, etc).

grace_hash_join_initial_buckets​

Experimental feature. Learn more.

Type
Default value
Type
NonZeroUInt64
Default value
1
Initial number of grace hash join buckets

grace_hash_join_max_buckets​

Experimental feature. Learn more.

Type
Default value
Type
NonZeroUInt64
Default value
1024
Limit on the number of grace hash join buckets

group_by_overflow_mode​

Type
Default value
Type
OverflowModeGroupBy
Default value
throw
Sets what happens when the number of unique keys for aggregation exceeds the limit:

  • throw: throw an exception
  • break: stop executing the query and return the partial result
  • any: continue aggregation for the keys that got into the set, but do not add new keys to the set.

Using the 'any' value lets you run an approximation of GROUP BY. The quality of this approximation depends on the statistical nature of the data.

group_by_two_level_threshold​

Type
Default value
Type
UInt64
Default value
100000
From what number of keys, a two-level aggregation starts. 0 - the threshold is not set.

group_by_two_level_threshold_bytes​

Type
Default value
Type
UInt64
Default value
50000000
From what size of the aggregation state in bytes, a two-level aggregation begins to be used. 0 - the threshold is not set. Two-level aggregation is used when at least one of the thresholds is triggered.

group_by_use_nulls​

Type
Default value
Type
Bool
Default value
0
Changes the way the GROUP BY clause treats the types of aggregation keys. When the ROLLUP, CUBE, or GROUPING SETS specifiers are used, some aggregation keys may not be used to produce some result rows. Columns for these keys are filled with either default value or NULL in corresponding rows depending on this setting.

Possible values:

  • 0 — The default value for the aggregation key type is used to produce missing values.
  • 1 — ClickHouse executes GROUP BY the same way as the SQL standard says. The types of aggregation keys are converted to Nullable. Columns for corresponding aggregation keys are filled with NULL for rows that didn't use it.

See also:

h3togeo_lon_lat_result_order​

Type
Default value
Type
Bool
Default value
0

Function 'h3ToGeo' returns (lon, lat) if true, otherwise (lat, lon).

handshake_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
10000
Timeout in milliseconds for receiving Hello packet from replicas during handshake.

hdfs_create_new_file_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables creating a new file on each insert in HDFS engine tables. If enabled, on each insert a new HDFS file will be created with the name, similar to this pattern:

initial: data.Parquet.gz -> data.1.Parquet.gz -> data.2.Parquet.gz, etc.

Possible values:

  • 0 — INSERT query appends new data to the end of the file.
  • 1 — INSERT query creates a new file.

hdfs_ignore_file_doesnt_exist​

Type
Default value
Type
Bool
Default value
0

Ignore absence of file if it does not exist when reading certain keys.

Possible values:

  • 1 — SELECT returns empty result.
  • 0 — SELECT throws an exception.

hdfs_replication​

Type
Default value
Type
UInt64
Default value
0
The actual number of replications can be specified when the hdfs file is created.

hdfs_skip_empty_files​

Type
Default value
Type
Bool
Default value
0
Enables or disables skipping empty files in HDFS engine tables.

Possible values:

  • 0 — SELECT throws an exception if empty file is not compatible with requested format.
  • 1 — SELECT returns empty result for empty file.

hdfs_throw_on_zero_files_match​

Type
Default value
Type
Bool
Default value
0

Throw an error if matched zero files according to glob expansion rules.

Possible values:

  • 1 — SELECT throws an exception.
  • 0 — SELECT returns empty result.

hdfs_truncate_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables truncation before an insert in hdfs engine tables. If disabled, an exception will be thrown on an attempt to insert if a file in HDFS already exists.

Possible values:

  • 0 — INSERT query appends new data to the end of the file.
  • 1 — INSERT query replaces existing content of the file with the new data.

hedged_connection_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
50

Connection timeout for establishing connection with replica for Hedged requests

Experimental feature. Learn more.
Type
Default value
Type
UInt64
Default value
256

The size of the dynamic candidate list when searching the vector similarity index, also known as 'ef_search'.

hsts_max_age​

Type
Default value
Type
UInt64
Default value
0
Expired time for HSTS. 0 means disable HSTS.

http_connection_timeout​

Type
Default value
Type
Seconds
Default value
1
HTTP connection timeout (in seconds).

Possible values:

  • Any positive integer.
  • 0 - Disabled (infinite timeout).

http_headers_progress_interval_ms​

Type
Default value
Type
UInt64
Default value
100
Do not send HTTP headers X-ClickHouse-Progress more frequently than at each specified interval.

http_make_head_request​

Type
Default value
Type
Bool
Default value
1
The http_make_head_request setting allows the execution of a HEAD request while reading data from HTTP to retrieve information about the file to be read, such as its size. Since it's enabled by default, it may be desirable to disable this setting in cases where the server does not support HEAD requests.

http_max_field_name_size​

Type
Default value
Type
UInt64
Default value
131072
Maximum length of field name in HTTP header

http_max_field_value_size​

Type
Default value
Type
UInt64
Default value
131072
Maximum length of field value in HTTP header

http_max_fields​

Type
Default value
Type
UInt64
Default value
1000000
Maximum number of fields in HTTP header

http_max_multipart_form_data_size​

Type
Default value
Type
UInt64
Default value
1073741824
Limit on size of multipart/form-data content. This setting cannot be parsed from URL parameters and should be set in a user profile. Note that content is parsed and external tables are created in memory before the start of query execution. And this is the only limit that has an effect on that stage (limits on max memory usage and max execution time have no effect while reading HTTP form data).

http_max_request_param_data_size​

Type
Default value
Type
UInt64
Default value
10485760
Limit on size of request data used as a query parameter in predefined HTTP requests.

http_max_tries​

Type
Default value
Type
UInt64
Default value
10
Max attempts to read via http.

http_max_uri_size​

Type
Default value
Type
UInt64
Default value
1048576
Sets the maximum URI length of an HTTP request.

Possible values:

  • Positive integer.

http_native_compression_disable_checksumming_on_decompress​

Type
Default value
Type
Bool
Default value
0
Enables or disables checksum verification when decompressing the HTTP POST data from the client. Used only for ClickHouse native compression format (not used with gzip or deflate).

For more information, read the HTTP interface description.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

http_receive_timeout​

Type
Default value
Type
Seconds
Default value
30

HTTP receive timeout (in seconds).

Possible values:

  • Any positive integer.
  • 0 - Disabled (infinite timeout).

http_response_buffer_size​

Type
Default value
Type
UInt64
Default value
0
The number of bytes to buffer in the server memory before sending a HTTP response to the client or flushing to disk (when http_wait_end_of_query is enabled).

http_response_headers​

Type
Default value
Type
Map
Default value
{}

Allows to add or override HTTP headers which the server will return in the response with a successful query result. This only affects the HTTP interface.

If the header is already set by default, the provided value will override it. If the header was not set by default, it will be added to the list of headers. Headers that are set by the server by default and not overridden by this setting, will remain.

The setting allows you to set a header to a constant value. Currently there is no way to set a header to a dynamically calculated value.

Neither names or values can contain ASCII control characters.

If you implement a UI application which allows users to modify settings but at the same time makes decisions based on the returned headers, it is recommended to restrict this setting to readonly.

Example: SET http_response_headers = '{"Content-Type": "image/png"}'

http_retry_initial_backoff_ms​

Type
Default value
Type
UInt64
Default value
100
Min milliseconds for backoff, when retrying read via http

http_retry_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
10000
Max milliseconds for backoff, when retrying read via http

http_send_timeout​

Type
Default value
Type
Seconds
Default value
30

HTTP send timeout (in seconds).

Possible values:

  • Any positive integer.
  • 0 - Disabled (infinite timeout).
note

It's applicable only to the default profile. A server reboot is required for the changes to take effect.

http_skip_not_found_url_for_globs​

Type
Default value
Type
Bool
Default value
1
Skip URLs for globs with HTTP_NOT_FOUND error

http_wait_end_of_query​

Type
Default value
Type
Bool
Default value
0
Enable HTTP response buffering on the server-side.

http_write_exception_in_output_format​

Type
Default value
Type
Bool
Default value
1

Write exception in output format to produce valid output. Works with JSON and XML formats.

http_zlib_compression_level​

Type
Default value
Type
Int64
Default value
3
Sets the level of data compression in the response to an HTTP request if enable_http_compression = 1.

Possible values: Numbers from 1 to 9.

iceberg_snapshot_id​

Type
Default value
Type
Int64
Default value
0

Query Iceberg table using the specific snapshot id.

iceberg_timestamp_ms​

Type
Default value
Type
Int64
Default value
0

Query Iceberg table using the snapshot that was current at a specific timestamp.

idle_connection_timeout​

Type
Default value
Type
UInt64
Default value
3600
Timeout to close idle TCP connections after specified number of seconds.

Possible values:

  • Positive integer (0 - close immediately, after 0 seconds).

ignore_cold_parts_seconds​

ClickHouse Cloud only

Type
Default value
Type
Int64
Default value
0
Only has an effect in ClickHouse Cloud. Exclude new data parts from SELECT queries until they're either pre-warmed (see cache_populated_by_fetch) or this many seconds old. Only for Replicated-/SharedMergeTree.

ignore_data_skipping_indices​

Ignores the skipping indexes specified if used by the query.

Consider the following example:

The query without ignoring any indexes:

Ignoring the xy_idx index:

Works with tables in the MergeTree family.

ignore_drop_queries_probability​

Type
Default value
Type
Float
Default value
0

If enabled, server will ignore all DROP table queries with specified probability (for Memory and JOIN engines it will replcase DROP to TRUNCATE). Used for testing purposes

ignore_materialized_views_with_dropped_target_table​

Type
Default value
Type
Bool
Default value
0

Ignore MVs with dropped target table during pushing to views

ignore_on_cluster_for_replicated_access_entities_queries​

Type
Default value
Type
Bool
Default value
0
Ignore ON CLUSTER clause for replicated access entities management queries.

ignore_on_cluster_for_replicated_named_collections_queries​

Type
Default value
Type
Bool
Default value
0

Ignore ON CLUSTER clause for replicated named collections management queries.

ignore_on_cluster_for_replicated_udf_queries​

Type
Default value
Type
Bool
Default value
0
Ignore ON CLUSTER clause for replicated UDF management queries.

implicit_select​

Type
Default value
Type
Bool
Default value
0

Allow writing simple SELECT queries without the leading SELECT keyword, which makes it simple for calculator-style usage, e.g. 1 + 2 becomes a valid query.

In clickhouse-local it is enabled by default and can be explicitly disabled.

implicit_table_at_top_level​

If not empty, queries without FROM at the top level will read from this table instead of system.one.

This is used in clickhouse-local for input data processing. The setting could be set explicitly by a user but is not intended for this type of usage.

Subqueries are not affected by this setting (neither scalar, FROM, or IN subqueries). SELECTs at the top level of UNION, INTERSECT, EXCEPT chains are treated uniformly and affected by this setting, regardless of their grouping in parentheses. It is unspecified how this setting affects views and distributed queries.

The setting accepts a table name (then the table is resolved from the current database) or a qualified name in the form of 'database.table'. Both database and table names have to be unquoted - only simple identifiers are allowed.

implicit_transaction​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
If enabled and not already inside a transaction, wraps the query inside a full transaction (begin + commit or rollback)

input_format_parallel_parsing​

Type
Default value
Type
Bool
Default value
1
Enables or disables order-preserving parallel parsing of data formats. Supported only for TSV, TSKV, CSV and JSONEachRow formats.

Possible values:

  • 1 — Enabled.
  • 0 — Disabled.

insert_allow_materialized_columns​

Type
Default value
Type
Bool
Default value
0
If setting is enabled, Allow materialized columns in INSERT.

insert_deduplicate​

Type
Default value
Type
Bool
Default value
1
Enables or disables block deduplication of INSERT (for Replicated* tables).

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

By default, blocks inserted into replicated tables by the INSERT statement are deduplicated (see Data Replication). For the replicated tables by default the only 100 of the most recent blocks for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds). For not replicated tables see non_replicated_deduplication_window.

insert_deduplication_token​

The setting allows a user to provide own deduplication semantic in MergeTree/ReplicatedMergeTree For example, by providing a unique value for the setting in each INSERT statement, user can avoid the same inserted data being deduplicated.

Possible values:

  • Any string

insert_deduplication_token is used for deduplication only when not empty.

For the replicated tables by default the only 100 of the most recent inserts for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds). For not replicated tables see non_replicated_deduplication_window.

note

insert_deduplication_token works on a partition level (the same as insert_deduplication checksum). Multiple partitions can have the same insert_deduplication_token.

Example:

insert_keeper_fault_injection_probability​

Type
Default value
Type
Float
Default value
0
Approximate probability of failure for a keeper request during insert. Valid value is in interval [0.0f, 1.0f]

insert_keeper_fault_injection_seed​

Type
Default value
Type
UInt64
Default value
0
0 - random seed, otherwise the setting value

insert_keeper_max_retries​

Type
Default value
Type
UInt64
Default value
20

The setting sets the maximum number of retries for ClickHouse Keeper (or ZooKeeper) requests during insert into replicated MergeTree. Only Keeper requests which failed due to network error, Keeper session timeout, or request timeout are considered for retries.

Possible values:

  • Positive integer.
  • 0 — Retries are disabled

Cloud default value: 20.

Keeper request retries are done after some timeout. The timeout is controlled by the following settings: insert_keeper_retry_initial_backoff_ms, insert_keeper_retry_max_backoff_ms. The first retry is done after insert_keeper_retry_initial_backoff_ms timeout. The consequent timeouts will be calculated as follows:

For example, if insert_keeper_retry_initial_backoff_ms=100, insert_keeper_retry_max_backoff_ms=10000 and insert_keeper_max_retries=8 then timeouts will be 100, 200, 400, 800, 1600, 3200, 6400, 10000.

Apart from fault tolerance, the retries aim to provide a better user experience - they allow to avoid returning an error during INSERT execution if Keeper is restarted, for example, due to an upgrade.

insert_keeper_retry_initial_backoff_ms​

Type
Default value
Type
UInt64
Default value
100
Initial timeout(in milliseconds) to retry a failed Keeper request during INSERT query execution

Possible values:

  • Positive integer.
  • 0 — No timeout

insert_keeper_retry_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
10000
Maximum timeout (in milliseconds) to retry a failed Keeper request during INSERT query execution

Possible values:

  • Positive integer.
  • 0 — Maximum timeout is not limited

insert_null_as_default​

Type
Default value
Type
Bool
Default value
1
Enables or disables the insertion of default values instead of NULL into columns with not nullable data type. If column type is not nullable and this setting is disabled, then inserting NULL causes an exception. If column type is nullable, then NULL values are inserted as is, regardless of this setting.

This setting is applicable to INSERT ... SELECT queries. Note that SELECT subqueries may be concatenated with UNION ALL clause.

Possible values:

  • 0 — Inserting NULL into a not nullable column causes an exception.
  • 1 — Default column value is inserted instead of NULL.

insert_quorum​

Type
Default value
Type
UInt64Auto
Default value
0
:::note This setting is not applicable to SharedMergeTree, see SharedMergeTree consistency for more information. :::

Enables the quorum writes.

  • If insert_quorum < 2, the quorum writes are disabled.
  • If insert_quorum >= 2, the quorum writes are enabled.
  • If insert_quorum = 'auto', use majority number (number_of_replicas / 2 + 1) as quorum number.

Quorum writes

INSERT succeeds only when ClickHouse manages to correctly write data to the insert_quorum of replicas during the insert_quorum_timeout. If for any reason the number of replicas with successful writes does not reach the insert_quorum, the write is considered failed and ClickHouse will delete the inserted block from all the replicas where data has already been written.

When insert_quorum_parallel is disabled, all replicas in the quorum are consistent, i.e. they contain data from all previous INSERT queries (the INSERT sequence is linearized). When reading data written using insert_quorum and insert_quorum_parallel is disabled, you can turn on sequential consistency for SELECT queries using select_sequential_consistency.

ClickHouse generates an exception:

  • If the number of available replicas at the time of the query is less than the insert_quorum.
  • When insert_quorum_parallel is disabled and an attempt to write data is made when the previous block has not yet been inserted in insert_quorum of replicas. This situation may occur if the user tries to perform another INSERT query to the same table before the previous one with insert_quorum is completed.

See also:

insert_quorum_parallel​

Type
Default value
Type
Bool
Default value
1
note

This setting is not applicable to SharedMergeTree, see SharedMergeTree consistency for more information.

Enables or disables parallelism for quorum INSERT queries. If enabled, additional INSERT queries can be sent while previous queries have not yet finished. If disabled, additional writes to the same table will be rejected.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

See also:

insert_quorum_timeout​

Type
Default value
Type
Milliseconds
Default value
600000
Write to a quorum timeout in milliseconds. If the timeout has passed and no write has taken place yet, ClickHouse will generate an exception and the client must repeat the query to write the same block to the same or any other replica.

See also:

insert_shard_id​

Type
Default value
Type
UInt64
Default value
0
If not 0, specifies the shard of Distributed table into which the data will be inserted synchronously.

If insert_shard_id value is incorrect, the server will throw an exception.

To get the number of shards on requested_cluster, you can check server config or use this query:

Possible values:

  • 0 — Disabled.
  • Any number from 1 to shards_num of corresponding Distributed table.

Example

Query:

Result:

interactive_delay​

Type
Default value
Type
UInt64
Default value
100000
The interval in microseconds for checking whether request execution has been canceled and sending the progress.

intersect_default_mode​

Type
Default value
Type
SetOperationMode
Default value
ALL
Set default mode in INTERSECT query. Possible values: empty string, 'ALL', 'DISTINCT'. If empty, query without mode will throw exception.

join_algorithm​

Type
Default value
Type
JoinAlgorithm
Default value
direct,parallel_hash,hash

Specifies which JOIN algorithm is used.

Several algorithms can be specified, and an available one would be chosen for a particular query based on kind/strictness and table engine.

Possible values:

  • grace_hash

Grace hash join is used. Grace hash provides an algorithm option that provides performant complex joins while limiting memory use.

The first phase of a grace join reads the right table and splits it into N buckets depending on the hash value of key columns (initially, N is grace_hash_join_initial_buckets). This is done in a way to ensure that each bucket can be processed independently. Rows from the first bucket are added to an in-memory hash table while the others are saved to disk. If the hash table grows beyond the memory limit (e.g., as set by max_bytes_in_join, the number of buckets is increased and the assigned bucket for each row. Any rows which don't belong to the current bucket are flushed and reassigned.

Supports INNER/LEFT/RIGHT/FULL ALL/ANY JOIN.

  • hash

Hash join algorithm is used. The most generic implementation that supports all combinations of kind and strictness and multiple join keys that are combined with OR in the JOIN ON section.

When using the hash algorithm, the right part of JOIN is uploaded into RAM.

  • parallel_hash

A variation of hash join that splits the data into buckets and builds several hashtables instead of one concurrently to speed up this process.

When using the parallel_hash algorithm, the right part of JOIN is uploaded into RAM.

  • partial_merge

A variation of the sort-merge algorithm, where only the right table is fully sorted.

The RIGHT JOIN and FULL JOIN are supported only with ALL strictness (SEMI, ANTI, ANY, and ASOF are not supported).

When using the partial_merge algorithm, ClickHouse sorts the data and dumps it to the disk. The partial_merge algorithm in ClickHouse differs slightly from the classic realization. First, ClickHouse sorts the right table by joining keys in blocks and creates a min-max index for sorted blocks. Then it sorts parts of the left table by the join key and joins them over the right table. The min-max index is also used to skip unneeded right table blocks.

  • direct

This algorithm can be applied when the storage for the right table supports key-value requests.

The direct algorithm performs a lookup in the right table using rows from the left table as keys. It's supported only by special storage such as Dictionary or EmbeddedRocksDB and only the LEFT and INNER JOINs.

  • auto

When set to auto, hash join is tried first, and the algorithm is switched on the fly to another algorithm if the memory limit is violated.

  • full_sorting_merge

Sort-merge algorithm with full sorting joined tables before joining.

  • prefer_partial_merge

ClickHouse always tries to use partial_merge join if possible, otherwise, it uses hash. Deprecated, same as partial_merge,hash.

  • default (deprecated)

Legacy value, please don't use anymore. Same as direct,hash, i.e. try to use direct join and hash join join (in this order).

join_any_take_last_row​

Type
Default value
Type
Bool
Default value
0
Changes the behaviour of join operations with ANY strictness.

note

This setting applies only for JOIN operations with Join engine tables.

Possible values:

  • 0 — If the right table has more than one matching row, only the first one found is joined.
  • 1 — If the right table has more than one matching row, only the last one found is joined.

See also:

join_default_strictness​

Type
Default value
Type
JoinStrictness
Default value
ALL
Sets default strictness for JOIN clauses.

Possible values:

  • ALL — If the right table has several matching rows, ClickHouse creates a Cartesian product from matching rows. This is the normal JOIN behaviour from standard SQL.
  • ANY — If the right table has several matching rows, only the first one found is joined. If the right table has only one matching row, the results of ANY and ALL are the same.
  • ASOF — For joining sequences with an uncertain match.
  • Empty string — If ALL or ANY is not specified in the query, ClickHouse throws an exception.

join_on_disk_max_files_to_merge​

Type
Default value
Type
UInt64
Default value
64
Limits the number of files allowed for parallel sorting in MergeJoin operations when they are executed on disk.

The bigger the value of the setting, the more RAM is used and the less disk I/O is needed.

Possible values:

  • Any positive integer, starting from 2.

join_output_by_rowlist_perkey_rows_threshold​

Type
Default value
Type
UInt64
Default value
5

The lower limit of per-key average rows in the right table to determine whether to output by row list in hash join.

join_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Defines what action ClickHouse performs when any of the following join limits is reached:

Possible values:

  • THROW — ClickHouse throws an exception and breaks operation.
  • BREAK — ClickHouse breaks operation and does not throw an exception.

Default value: THROW.

See Also

join_to_sort_maximum_table_rows​

Experimental feature. Learn more.
Type
Default value
Type
UInt64
Default value
10000

The maximum number of rows in the right table to determine whether to rerange the right table by key in left or inner join.

join_to_sort_minimum_perkey_rows​

Experimental feature. Learn more.
Type
Default value
Type
UInt64
Default value
40

The lower limit of per-key average rows in the right table to determine whether to rerange the right table by key in left or inner join. This setting ensures that the optimization is not applied for sparse table keys

join_use_nulls​

Type
Default value
Type
Bool
Default value
0
Sets the type of JOIN behaviour. When merging tables, empty cells may appear. ClickHouse fills them differently based on this setting.

Possible values:

  • 0 — The empty cells are filled with the default value of the corresponding field type.
  • 1 — JOIN behaves the same way as in standard SQL. The type of the corresponding field is converted to Nullable, and empty cells are filled with NULL.

joined_subquery_requires_alias​

Type
Default value
Type
Bool
Default value
1
Force joined subqueries and table functions to have aliases for correct name qualification.

kafka_disable_num_consumers_limit​

Type
Default value
Type
Bool
Default value
0
Disable limit on kafka_num_consumers that depends on the number of available CPU cores.

kafka_max_wait_ms​

Type
Default value
Type
Milliseconds
Default value
5000
The wait time in milliseconds for reading messages from Kafka before retry.

Possible values:

  • Positive integer.
  • 0 — Infinite timeout.

See also:

keeper_map_strict_mode​

Type
Default value
Type
Bool
Default value
0
Enforce additional checks during operations on KeeperMap. E.g. throw an exception on an insert for already existing key

keeper_max_retries​

Type
Default value
Type
UInt64
Default value
10

Max retries for general keeper operations

keeper_retry_initial_backoff_ms​

Type
Default value
Type
UInt64
Default value
100

Initial backoff timeout for general keeper operations

keeper_retry_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
5000

Max backoff timeout for general keeper operations

least_greatest_legacy_null_behavior​

Type
Default value
Type
Bool
Default value
0

If enabled, functions 'least' and 'greatest' return NULL if one of their arguments is NULL.

legacy_column_name_of_tuple_literal​

Type
Default value
Type
Bool
Default value
0

List all names of element of large tuple literals in their column names instead of hash. This settings exists only for compatibility reasons. It makes sense to set to 'true', while doing rolling update of cluster from version lower than 21.7 to higher.

lightweight_deletes_sync​

Type
Default value
Type
UInt64
Default value
2

The same as mutations_sync, but controls only execution of lightweight deletes.

Possible values:

  • 0 - Mutations execute asynchronously.
  • 1 - The query waits for the lightweight deletes to complete on the current server.
  • 2 - The query waits for the lightweight deletes to complete on all replicas (if they exist).

See Also

limit​

Type
Default value
Type
UInt64
Default value
0
Sets the maximum number of rows to get from the query result. It adjusts the value set by the LIMIT clause, so that the limit, specified in the query, cannot exceed the limit, set by this setting.

Possible values:

  • 0 — The number of rows is not limited.
  • Positive integer.

live_view_heartbeat_interval​

Experimental feature. Learn more.

Type
Default value
Type
Seconds
Default value
15
The heartbeat interval in seconds to indicate live query is alive.

load_balancing​

Type
Default value
Type
LoadBalancing
Default value
random
Specifies the algorithm of replicas selection that is used for distributed query processing.

ClickHouse supports the following algorithms of choosing replicas:

See also:

Random (by Default)​

The number of errors is counted for each replica. The query is sent to the replica with the fewest errors, and if there are several of these, to anyone of them. Disadvantages: Server proximity is not accounted for; if the replicas have different data, you will also get different data.

Nearest Hostname​

The number of errors is counted for each replica. Every 5 minutes, the number of errors is integrally divided by 2. Thus, the number of errors is calculated for a recent time with exponential smoothing. If there is one replica with a minimal number of errors (i.e. errors occurred recently on the other replicas), the query is sent to it. If there are multiple replicas with the same minimal number of errors, the query is sent to the replica with a hostname that is most similar to the server's hostname in the config file (for the number of different characters in identical positions, up to the minimum length of both hostnames).

For instance, example01-01-1 and example01-01-2 are different in one position, while example01-01-1 and example01-02-2 differ in two places. This method might seem primitive, but it does not require external data about network topology, and it does not compare IP addresses, which would be complicated for our IPv6 addresses.

Thus, if there are equivalent replicas, the closest one by name is preferred. We can also assume that when sending a query to the same server, in the absence of failures, a distributed query will also go to the same servers. So even if different data is placed on the replicas, the query will return mostly the same results.

Hostname levenshtein distance​

Just like nearest_hostname, but it compares hostname in a levenshtein distance manner. For example:

In Order​

Replicas with the same number of errors are accessed in the same order as they are specified in the configuration. This method is appropriate when you know exactly which replica is preferable.

First or Random​

This algorithm chooses the first replica in the set or a random replica if the first is unavailable. It's effective in cross-replication topology setups, but useless in other configurations.

The first_or_random algorithm solves the problem of the in_order algorithm. With in_order, if one replica goes down, the next one gets a double load while the remaining replicas handle the usual amount of traffic. When using the first_or_random algorithm, the load is evenly distributed among replicas that are still available.

It's possible to explicitly define what the first replica is by using the setting load_balancing_first_offset. This gives more control to rebalance query workloads among replicas.

Round Robin​

This algorithm uses a round-robin policy across replicas with the same number of errors (only the queries with round_robin policy is accounted).

load_balancing_first_offset​

Type
Default value
Type
UInt64
Default value
0
Which replica to preferably send a query when FIRST_OR_RANDOM load balancing strategy is used.

load_marks_asynchronously​

Type
Default value
Type
Bool
Default value
0
Load MergeTree marks asynchronously

local_filesystem_read_method​

Type
Default value
Type
String
Default value
pread_threadpool
Method of reading data from local filesystem, one of: read, pread, mmap, io_uring, pread_threadpool. The 'io_uring' method is experimental and does not work for Log, TinyLog, StripeLog, File, Set and Join, and other tables with append-able files in presence of concurrent reads and writes.

local_filesystem_read_prefetch​

Type
Default value
Type
Bool
Default value
0
Should use prefetching when reading data from local filesystem.

lock_acquire_timeout​

Type
Default value
Type
Seconds
Default value
120
Defines how many seconds a locking request waits before failing.

Locking timeout is used to protect from deadlocks while executing read/write operations with tables. When the timeout expires and the locking request fails, the ClickHouse server throws an exception "Locking attempt timed out! Possible deadlock avoided. Client should retry." with error code DEADLOCK_AVOIDED.

Possible values:

  • Positive integer (in seconds).
  • 0 — No locking timeout.

log_comment​

Specifies the value for the log_comment field of the system.query_log table and comment text for the server log.

It can be used to improve the readability of server logs. Additionally, it helps to select queries related to the test from the system.query_log after running clickhouse-test.

Possible values:

  • Any string no longer than max_query_size. If the max_query_size is exceeded, the server throws an exception.

Example

Query:

Result:

log_formatted_queries​

Type
Default value
Type
Bool
Default value
0
Allows to log formatted queries to the system.query_log system table (populates formatted_query column in the system.query_log).

Possible values:

  • 0 — Formatted queries are not logged in the system table.
  • 1 — Formatted queries are logged in the system table.

log_processors_profiles​

Type
Default value
Type
Bool
Default value
1

Write time that processor spent during execution/waiting for data to system.processors_profile_log table.

See also:

log_profile_events​

Type
Default value
Type
Bool
Default value
1
Log query performance statistics into the query_log, query_thread_log and query_views_log.

log_queries​

Type
Default value
Type
Bool
Default value
1
Setting up query logging.

Queries sent to ClickHouse with this setup are logged according to the rules in the query_log server configuration parameter.

Example:

log_queries_cut_to_length​

Type
Default value
Type
UInt64
Default value
100000
If query length is greater than a specified threshold (in bytes), then cut query when writing to query log. Also limit the length of printed query in ordinary text log.

log_queries_min_query_duration_ms​

Type
Default value
Type
Milliseconds
Default value
0
If enabled (non-zero), queries faster than the value of this setting will not be logged (you can think about this as a long_query_time for MySQL Slow Query Log), and this basically means that you will not find them in the following tables:

  • system.query_log
  • system.query_thread_log

Only the queries with the following type will get to the log:

  • QUERY_FINISH

  • EXCEPTION_WHILE_PROCESSING

  • Type: milliseconds

  • Default value: 0 (any query)

log_queries_min_type​

Type
Default value
Type
LogQueriesType
Default value
QUERY_START
query_log minimal type to log.

Possible values:

  • QUERY_START (=1)
  • QUERY_FINISH (=2)
  • EXCEPTION_BEFORE_START (=3)
  • EXCEPTION_WHILE_PROCESSING (=4)

Can be used to limit which entities will go to query_log, say you are interested only in errors, then you can use EXCEPTION_WHILE_PROCESSING:

log_queries_probability​

Type
Default value
Type
Float
Default value
1
Allows a user to write to query_log, query_thread_log, and query_views_log system tables only a sample of queries selected randomly with the specified probability. It helps to reduce the load with a large volume of queries in a second.

Possible values:

  • 0 — Queries are not logged in the system tables.
  • Positive floating-point number in the range [0..1]. For example, if the setting value is 0.5, about half of the queries are logged in the system tables.
  • 1 — All queries are logged in the system tables.

log_query_settings​

Type
Default value
Type
Bool
Default value
1
Log query settings into the query_log and OpenTelemetry span log.

log_query_threads​

Type
Default value
Type
Bool
Default value
0
Setting up query threads logging.

Query threads log into the system.query_thread_log table. This setting has effect only when log_queries is true. Queries' threads run by ClickHouse with this setup are logged according to the rules in the query_thread_log server configuration parameter.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Example

log_query_views​

Type
Default value
Type
Bool
Default value
1
Setting up query views logging.

When a query run by ClickHouse with this setting enabled has associated views (materialized or live views), they are logged in the query_views_log server configuration parameter.

Example:

low_cardinality_allow_in_native_format​

Type
Default value
Type
Bool
Default value
1
Allows or restricts using the LowCardinality data type with the Native format.

If usage of LowCardinality is restricted, ClickHouse server converts LowCardinality-columns to ordinary ones for SELECT queries, and convert ordinary columns to LowCardinality-columns for INSERT queries.

This setting is required mainly for third-party clients which do not support LowCardinality data type.

Possible values:

  • 1 — Usage of LowCardinality is not restricted.
  • 0 — Usage of LowCardinality is restricted.

low_cardinality_max_dictionary_size​

Type
Default value
Type
UInt64
Default value
8192
Sets a maximum size in rows of a shared global dictionary for the LowCardinality data type that can be written to a storage file system. This setting prevents issues with RAM in case of unlimited dictionary growth. All the data that can't be encoded due to maximum dictionary size limitation ClickHouse writes in an ordinary method.

Possible values:

  • Any positive integer.

low_cardinality_use_single_dictionary_for_part​

Type
Default value
Type
Bool
Default value
0
Turns on or turns off using of single dictionary for the data part.

By default, the ClickHouse server monitors the size of dictionaries and if a dictionary overflows then the server starts to write the next one. To prohibit creating several dictionaries set low_cardinality_use_single_dictionary_for_part = 1.

Possible values:

  • 1 — Creating several dictionaries for the data part is prohibited.
  • 0 — Creating several dictionaries for the data part is not prohibited.

low_priority_query_wait_time_ms​

Beta feature. Learn more.
Type
Default value
Type
Milliseconds
Default value
1000

When the query prioritization mechanism is employed (see setting priority), low-priority queries wait for higher-priority queries to finish. This setting specifies the duration of waiting.

materialize_skip_indexes_on_insert​

Type
Default value
Type
Bool
Default value
1

If INSERTs build and store skip indexes. If disabled, skip indexes will be build and stored during merges or by explicit MATERIALIZE INDEX

materialize_statistics_on_insert​

Type
Default value
Type
Bool
Default value
1

If INSERTs build and insert statistics. If disabled, statistics will be build and stored during merges or by explicit MATERIALIZE STATISTICS

materialize_ttl_after_modify​

Type
Default value
Type
Bool
Default value
1
Apply TTL for old data, after ALTER MODIFY TTL query

materialized_views_ignore_errors​

Type
Default value
Type
Bool
Default value
0
Allows to ignore errors for MATERIALIZED VIEW, and deliver original block to the table regardless of MVs

max_analyze_depth​

Type
Default value
Type
UInt64
Default value
5000
Maximum number of analyses performed by interpreter.

max_ast_depth​

Type
Default value
Type
UInt64
Default value
1000
The maximum nesting depth of a query syntactic tree. If exceeded, an exception is thrown.

note

At this time, it isn't checked during parsing, but only after parsing the query. This means that a syntactic tree that is too deep can be created during parsing, but the query will fail.

max_ast_elements​

Type
Default value
Type
UInt64
Default value
50000
The maximum number of elements in a query syntactic tree. If exceeded, an exception is thrown.

note

At this time, it isn't checked during parsing, but only after parsing the query. This means that a syntactic tree that is too deep can be created during parsing, but the query will fail.

max_autoincrement_series​

Type
Default value
Type
UInt64
Default value
1000

The limit on the number of series created by the generateSeriesID function.

As each series represents a node in Keeper, it is recommended to have no more than a couple of millions of them.

max_backup_bandwidth​

Type
Default value
Type
UInt64
Default value
0
The maximum read speed in bytes per second for particular backup on server. Zero means unlimited.

max_block_size​

Type
Default value
Type
UInt64
Default value
65409
In ClickHouse, data is processed by blocks, which are sets of column parts. The internal processing cycles for a single block are efficient but there are noticeable costs when processing each block.

The max_block_size setting indicates the recommended maximum number of rows to include in a single block when loading data from tables. Blocks the size of max_block_size are not always loaded from the table: if ClickHouse determines that less data needs to be retrieved, a smaller block is processed.

The block size should not be too small to avoid noticeable costs when processing each block. It should also not be too large to ensure that queries with a LIMIT clause execute quickly after processing the first block. When setting max_block_size, the goal should be to avoid consuming too much memory when extracting a large number of columns in multiple threads and to preserve at least some cache locality.

max_bytes_before_external_group_by​

Type
Default value
Type
UInt64
Default value
0
Cloud default value: half the memory amount per replica.

Enables or disables execution of GROUP BY clauses in external memory. (See GROUP BY in external memory)

Possible values:

  • Maximum volume of RAM (in bytes) that can be used by the single GROUP BY operation.
  • 0 — GROUP BY in external memory disabled.
note

If memory usage during GROUP BY operations is exceeding this threshold in bytes, activate the 'external aggregation' mode (spill data to disk).

The recommended value is half of the available system memory.

max_bytes_before_external_sort​

Type
Default value
Type
UInt64
Default value
0
Cloud default value: half the memory amount per replica.

Enables or disables execution of ORDER BY clauses in external memory. See ORDER BY Implementation Details If memory usage during ORDER BY operation exceeds this threshold in bytes, the 'external sorting' mode (spill data to disk) is activated.

Possible values:

  • Maximum volume of RAM (in bytes) that can be used by the single ORDER BY operation. The recommended value is half of available system memory
  • 0 — ORDER BY in external memory disabled.

max_bytes_before_remerge_sort​

Type
Default value
Type
UInt64
Default value
1000000000
In case of ORDER BY with LIMIT, when memory usage is higher than specified threshold, perform additional steps of merging blocks before final merge to keep just top LIMIT rows.

max_bytes_in_distinct​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes of the state (in uncompressed bytes) in memory, which is used by a hash table when using DISTINCT.

max_bytes_in_join​

Type
Default value
Type
UInt64
Default value
0
The maximum size in number of bytes of the hash table used when joining tables.

This setting applies to SELECT ... JOIN operations and the Join table engine.

If the query contains joins, ClickHouse checks this setting for every intermediate result.

ClickHouse can proceed with different actions when the limit is reached. Use the join_overflow_mode settings to choose the action.

Possible values:

  • Positive integer.
  • 0 — Memory control is disabled.

max_bytes_in_set​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes (of uncompressed data) used by a set in the IN clause created from a subquery.

max_bytes_ratio_before_external_group_by​

Type
Default value
Type
Double
Default value
0.5

The ratio of available memory that is allowed for GROUP BY. Once reached, external memory is used for aggregation.

For example, if set to 0.6, GROUP BY will allow using 60% of the available memory (to server/user/merges) at the beginning of the execution, after that, it will start using external aggregation.

max_bytes_ratio_before_external_sort​

Type
Default value
Type
Double
Default value
0.5

The ratio of available memory that is allowed for ORDER BY. Once reached, external sort is used.

For example, if set to 0.6, ORDER BY will allow using 60% of available memory (to server/user/merges) at the beginning of the execution, after that, it will start using external sort.

max_bytes_to_read​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes (of uncompressed data) that can be read from a table when running a query. The restriction is checked for each processed chunk of data, applied only to the deepest table expression and when reading from a remote server, checked only on the remote server.

max_bytes_to_read_leaf​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes (of uncompressed data) that can be read from a local table on a leaf node when running a distributed query. While distributed queries can issue a multiple sub-queries to each shard (leaf) - this limit will be checked only on the read stage on the leaf nodes and will be ignored on the merging of results stage on the root node.

For example, a cluster consists of 2 shards and each shard contains a table with 100 bytes of data. A distributed query which is supposed to read all the data from both tables with setting max_bytes_to_read=150 will fail as in total it will be 200 bytes. A query with max_bytes_to_read_leaf=150 will succeed since leaf nodes will read 100 bytes at max.

The restriction is checked for each processed chunk of data.

note

This setting is unstable with prefer_localhost_replica=1.

max_bytes_to_sort​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes before sorting. If more than the specified amount of uncompressed bytes have to be processed for ORDER BY operation, the behavior will be determined by the sort_overflow_mode which by default is set to throw.

max_bytes_to_transfer​

Type
Default value
Type
UInt64
Default value
0
The maximum number of bytes (uncompressed data) that can be passed to a remote server or saved in a temporary table when the GLOBAL IN/JOIN section is executed.

max_columns_to_read​

Type
Default value
Type
UInt64
Default value
0
The maximum number of columns that can be read from a table in a single query. If a query requires reading more than the specified number of columns, an exception is thrown.

tip

This setting is useful for preventing overly complex queries.

0 value means unlimited.

max_compress_block_size​

Type
Default value
Type
UInt64
Default value
1048576
The maximum size of blocks of uncompressed data before compressing for writing to a table. By default, 1,048,576 (1 MiB). Specifying a smaller block size generally leads to slightly reduced compression ratio, the compression and decompression speed increases slightly due to cache locality, and memory consumption is reduced.

note

This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse.

Don't confuse blocks for compression (a chunk of memory consisting of bytes) with blocks for query processing (a set of rows from a table).

max_concurrent_queries_for_all_users​

Type
Default value
Type
UInt64
Default value
0
Throw exception if the value of this setting is less or equal than the current number of simultaneously processed queries.

Example: max_concurrent_queries_for_all_users can be set to 99 for all users and database administrator can set it to 100 for itself to run queries for investigation even when the server is overloaded.

Modifying the setting for one query or user does not affect other queries.

Possible values:

  • Positive integer.
  • 0 — No limit.

Example

See Also

max_concurrent_queries_for_user​

Type
Default value
Type
UInt64
Default value
0
The maximum number of simultaneously processed queries per user.

Possible values:

  • Positive integer.
  • 0 — No limit.

Example

max_distributed_connections​

Type
Default value
Type
UInt64
Default value
1024
The maximum number of simultaneous connections with remote servers for distributed processing of a single query to a single Distributed table. We recommend setting a value no less than the number of servers in the cluster.

The following parameters are only used when creating Distributed tables (and when launching a server), so there is no reason to change them at runtime.

max_distributed_depth​

Type
Default value
Type
UInt64
Default value
5
Limits the maximum depth of recursive queries for Distributed tables.

If the value is exceeded, the server throws an exception.

Possible values:

  • Positive integer.
  • 0 — Unlimited depth.

max_download_buffer_size​

Type
Default value
Type
UInt64
Default value
10485760
The maximal size of buffer for parallel downloading (e.g. for URL engine) per each thread.

max_download_threads​

Type
Default value
Type
MaxThreads
Default value
4
The maximum number of threads to download data (e.g. for URL engine).

max_estimated_execution_time​

Type
Default value
Type
Seconds
Default value
0

Maximum query estimate execution time in seconds. Checked on every data block when timeout_before_checking_execution_speed expires.

max_execution_speed​

Type
Default value
Type
UInt64
Default value
0
The maximum number of execution rows per second. Checked on every data block when timeout_before_checking_execution_speed expires. If the execution speed is high, the execution speed will be reduced.

max_execution_speed_bytes​

Type
Default value
Type
UInt64
Default value
0
The maximum number of execution bytes per second. Checked on every data block when timeout_before_checking_execution_speed expires. If the execution speed is high, the execution speed will be reduced.

max_execution_time​

Type
Default value
Type
Seconds
Default value
0
The maximum query execution time in seconds.

The max_execution_time parameter can be a bit tricky to understand. It operates based on interpolation relative to the current query execution speed (this behaviour is controlled by timeout_before_checking_execution_speed).

ClickHouse will interrupt a query if the projected execution time exceeds the specified max_execution_time. By default, the timeout_before_checking_execution_speed is set to 10 seconds. This means that after 10 seconds of query execution, ClickHouse will begin estimating the total execution time. If, for example, max_execution_time is set to 3600 seconds (1 hour), ClickHouse will terminate the query if the estimated time exceeds this 3600-second limit. If you set timeout_before_checking_execution_speed to 0, ClickHouse will use the clock time as the basis for max_execution_time.

If query runtime exceeds the specified number of seconds, the behavior will be determined by the 'timeout_overflow_mode', which by default is set to throw.

note

The timeout is checked and the query can stop only in designated places during data processing. It currently cannot stop during merging of aggregation states or during query analysis, and the actual run time will be higher than the value of this setting.

max_execution_time_leaf​

Type
Default value
Type
Seconds
Default value
0
Similar semantically to max_execution_time but only applied on leaf nodes for distributed or remote queries.

For example, if we want to limit the execution time on a leaf node to 10s but have no limit on the initial node, instead of having max_execution_time in the nested subquery settings:

We can use max_execution_time_leaf as the query settings:

max_expanded_ast_elements​

Type
Default value
Type
UInt64
Default value
500000
Maximum size of query syntax tree in number of nodes after expansion of aliases and the asterisk.

max_fetch_partition_retries_count​

Type
Default value
Type
UInt64
Default value
5
Amount of retries while fetching partition from another host.

max_final_threads​

Type
Default value
Type
MaxThreads
Default value
'auto(4)'
Sets the maximum number of parallel threads for the SELECT query data read phase with the FINAL modifier.

Possible values:

  • Positive integer.
  • 0 or 1 — Disabled. SELECT queries are executed in a single thread.

max_http_get_redirects​

Type
Default value
Type
UInt64
Default value
0
Max number of HTTP GET redirects hops allowed. Ensures additional security measures are in place to prevent a malicious server from redirecting your requests to unexpected services.\n\nIt is the case when an external server redirects to another address, but that address appears to be internal to the company's infrastructure, and by sending an HTTP request to an internal server, you could request an internal API from the internal network, bypassing the auth, or even query other services, such as Redis or Memcached. When you don't have an internal infrastructure (including something running on your localhost), or you trust the server, it is safe to allow redirects. Although keep in mind, that if the URL uses HTTP instead of HTTPS, and you will have to trust not only the remote server but also your ISP and every network in the middle.

max_hyperscan_regexp_length​

Type
Default value
Type
UInt64
Default value
0
Defines the maximum length for each regular expression in the hyperscan multi-match functions.

Possible values:

  • Positive integer.
  • 0 - The length is not limited.

Example

Query:

Result:

Query:

Result:

See Also

max_hyperscan_regexp_total_length​

Type
Default value
Type
UInt64
Default value
0
Sets the maximum length total of all regular expressions in each hyperscan multi-match function.

Possible values:

  • Positive integer.
  • 0 - The length is not limited.

Example

Query:

Result:

Query:

Result:

See Also

max_insert_block_size​

Type
Default value
Type
UInt64
Default value
1048449
The size of blocks (in a count of rows) to form for insertion into a table. This setting only applies in cases when the server forms the blocks. For example, for an INSERT via the HTTP interface, the server parses the data format and forms blocks of the specified size. But when using clickhouse-client, the client parses the data itself, and the 'max_insert_block_size' setting on the server does not affect the size of the inserted blocks. The setting also does not have a purpose when using INSERT SELECT, since data is inserted using the same blocks that are formed after SELECT.

The default is slightly more than max_block_size. The reason for this is that certain table engines (*MergeTree) form a data part on the disk for each inserted block, which is a fairly large entity. Similarly, *MergeTree tables sort data during insertion, and a large enough block size allow sorting more data in RAM.

max_insert_delayed_streams_for_parallel_write​

Type
Default value
Type
UInt64
Default value
0
The maximum number of streams (columns) to delay final part flush. Default - auto (100 in case of underlying storage supports parallel write, for example S3 and disabled otherwise)

max_insert_threads​

Type
Default value
Type
UInt64
Default value
0
The maximum number of threads to execute the INSERT SELECT query.

Possible values:

  • 0 (or 1) — INSERT SELECT no parallel execution.
  • Positive integer. Bigger than 1.

Cloud default value: from 2 to 4, depending on the service size.

Parallel INSERT SELECT has effect only if the SELECT part is executed in parallel, see max_threads setting. Higher values will lead to higher memory usage.

max_joined_block_size_rows​

Type
Default value
Type
UInt64
Default value
65409
Maximum block size for JOIN result (if join algorithm supports it). 0 means unlimited.

max_limit_for_ann_queries​

Experimental feature. Learn more.

Type
Default value
Type
UInt64
Default value
1000000
SELECT queries with LIMIT bigger than this setting cannot use vector similarity indices. Helps to prevent memory overflows in vector similarity indices.

max_live_view_insert_blocks_before_refresh​

Experimental feature. Learn more.

Type
Default value
Type
UInt64
Default value
64
Limit maximum number of inserted blocks after which mergeable blocks are dropped and query is re-executed.

max_local_read_bandwidth​

Type
Default value
Type
UInt64
Default value
0
The maximum speed of local reads in bytes per second.

max_local_write_bandwidth​

Type
Default value
Type
UInt64
Default value
0
The maximum speed of local writes in bytes per second.

max_memory_usage​

Type
Default value
Type
UInt64
Default value
0
Cloud default value: depends on the amount of RAM on the replica.

The maximum amount of RAM to use for running a query on a single server. A value of 0 means unlimited.

This setting does not consider the volume of available memory or the total volume of memory on the machine. The restriction applies to a single query within a single server.

You can use SHOW PROCESSLIST to see the current memory consumption for each query. Peak memory consumption is tracked for each query and written to the log.

Memory usage is not fully tracked for states of the following aggregate functions from String and Array arguments:

  • min
  • max
  • any
  • anyLast
  • argMin
  • argMax

Memory consumption is also restricted by the parameters max_memory_usage_for_user and max_server_memory_usage.

max_memory_usage_for_user​

Type
Default value
Type
UInt64
Default value
0
The maximum amount of RAM to use for running a user's queries on a single server. Zero means unlimited.

By default, the amount is not restricted (max_memory_usage_for_user = 0).

Also see the description of max_memory_usage.

For example if you want to set max_memory_usage_for_user to 1000 bytes for a user named clickhouse_read, you can use the statement

You can verify it worked by logging out of your client, logging back in, then use the getSetting function:

max_network_bandwidth​

Type
Default value
Type
UInt64
Default value
0
Limits the speed of the data exchange over the network in bytes per second. This setting applies to every query.

Possible values:

  • Positive integer.
  • 0 — Bandwidth control is disabled.

max_network_bandwidth_for_all_users​

Type
Default value
Type
UInt64
Default value
0
Limits the speed that data is exchanged at over the network in bytes per second. This setting applies to all concurrently running queries on the server.

Possible values:

  • Positive integer.
  • 0 — Control of the data speed is disabled.

max_network_bandwidth_for_user​

Type
Default value
Type
UInt64
Default value
0
Limits the speed of the data exchange over the network in bytes per second. This setting applies to all concurrently running queries performed by a single user.

Possible values:

  • Positive integer.
  • 0 — Control of the data speed is disabled.

max_network_bytes​

Type
Default value
Type
UInt64
Default value
0
Limits the data volume (in bytes) that is received or transmitted over the network when executing a query. This setting applies to every individual query.

Possible values:

  • Positive integer.
  • 0 — Data volume control is disabled.

max_number_of_partitions_for_independent_aggregation​

Type
Default value
Type
UInt64
Default value
128
Maximal number of partitions in table to apply optimizatio

max_os_cpu_wait_time_ratio_to_throw​

Type
Default value
Type
Float
Default value
6

Max ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider rejecting queries. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 1 at this point.

max_parallel_replicas​

Type
Default value
Type
NonZeroUInt64
Default value
1000

The maximum number of replicas for each shard when executing a query.

Possible values:

  • Positive integer.

Additional Info

This options will produce different results depending on the settings used.

note

This setting will produce incorrect results when joins or subqueries are involved, and all tables don't meet certain requirements. See Distributed Subqueries and max_parallel_replicas for more details.

Parallel processing using SAMPLE key​

A query may be processed faster if it is executed on several servers in parallel. But the query performance may degrade in the following cases:

  • The position of the sampling key in the partitioning key does not allow efficient range scans.
  • Adding a sampling key to the table makes filtering by other columns less efficient.
  • The sampling key is an expression that is expensive to calculate.
  • The cluster latency distribution has a long tail, so that querying more servers increases the query overall latency.

Parallel processing using parallel_replicas_custom_key​

This setting is useful for any replicated table.

max_parser_backtracks​

Type
Default value
Type
UInt64
Default value
1000000

Maximum parser backtracking (how many times it tries different alternatives in the recursive descend parsing process).

max_parser_depth​

Type
Default value
Type
UInt64
Default value
1000
Limits maximum recursion depth in the recursive descent parser. Allows controlling the stack size.

Possible values:

  • Positive integer.
  • 0 — Recursion depth is unlimited.

max_parsing_threads​

Type
Default value
Type
MaxThreads
Default value
'auto(4)'

The maximum number of threads to parse data in input formats that support parallel parsing. By default, it is determined automatically

max_partition_size_to_drop​

Type
Default value
Type
UInt64
Default value
50000000000
Restriction on dropping partitions in query time. The value 0 means that you can drop partitions without any restrictions.

Cloud default value: 1 TB.

note

This query setting overwrites its server setting equivalent, see max_partition_size_to_drop

max_partitions_per_insert_block​

Type
Default value
Type
UInt64
Default value
100

Limits the maximum number of partitions in a single inserted block and an exception is thrown if the block contains too many partitions.

  • Positive integer.
  • 0 — Unlimited number of partitions.

Details

When inserting data, ClickHouse calculates the number of partitions in the inserted block. If the number of partitions is more than max_partitions_per_insert_block, ClickHouse either logs a warning or throws an exception based on throw_on_max_partitions_per_insert_block. Exceptions have the following text:

"Too many partitions for a single INSERT block (partitions_count partitions, limit is " + toString(max_partitions) + "). The limit is controlled by the 'max_partitions_per_insert_block' setting. A large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc)."

note

This setting is a safety threshold because using a large number of partitions is a common misconception.

max_partitions_to_read​

Type
Default value
Type
Int64
Default value
-1
Limits the maximum number of partitions that can be accessed in a single query.

The setting value specified when the table is created can be overridden via query-level setting.

Possible values:

  • Positive integer
  • -1 - unlimited (default)
note

You can also specify the MergeTree setting max_partitions_to_read in tables' setting.

max_parts_to_move​

Type
Default value
Type
UInt64
Default value
1000

Limit the number of parts that can be moved in one query. Zero means unlimited.

max_query_size​

Type
Default value
Type
UInt64
Default value
262144
The maximum number of bytes of a query string parsed by the SQL parser. Data in the VALUES clause of INSERT queries is processed by a separate stream parser (that consumes O(1) RAM) and not affected by this restriction.

note

max_query_size cannot be set within an SQL query (e.g., SELECT now() SETTINGS max_query_size=10000) because ClickHouse needs to allocate a buffer to parse the query, and this buffer size is determined by the max_query_size setting, which must be configured before the query is executed.

max_read_buffer_size​

Type
Default value
Type
NonZeroUInt64
Default value
1048576
The maximum size of the buffer to read from the filesystem.

max_read_buffer_size_local_fs​

Type
Default value
Type
UInt64
Default value
131072
The maximum size of the buffer to read from local filesystem. If set to 0 then max_read_buffer_size will be used.

max_read_buffer_size_remote_fs​

Type
Default value
Type
UInt64
Default value
0
The maximum size of the buffer to read from remote filesystem. If set to 0 then max_read_buffer_size will be used.

max_recursive_cte_evaluation_depth​

Type
Default value
Type
UInt64
Default value
1000

Maximum limit on recursive CTE evaluation depth

max_remote_read_network_bandwidth​

Type
Default value
Type
UInt64
Default value
0
The maximum speed of data exchange over the network in bytes per second for read.

max_remote_write_network_bandwidth​

Type
Default value
Type
UInt64
Default value
0
The maximum speed of data exchange over the network in bytes per second for write.

max_replica_delay_for_distributed_queries​

Type
Default value
Type
UInt64
Default value
300
Disables lagging replicas for distributed queries. See Replication.

Sets the time in seconds. If a replica's lag is greater than or equal to the set value, this replica is not used.

Possible values:

  • Positive integer.
  • 0 — Replica lags are not checked.

To prevent the use of any replica with a non-zero lag, set this parameter to 1.

Used when performing SELECT from a distributed table that points to replicated tables.

max_result_bytes​

Type
Default value
Type
UInt64
Default value
0
Limits the result size in bytes (uncompressed). The query will stop after processing a block of data if the threshold is met, but it will not cut the last block of the result, therefore the result size can be larger than the threshold.

Caveats

The result size in memory is taken into account for this threshold. Even if the result size is small, it can reference larger data structures in memory, representing dictionaries of LowCardinality columns, and Arenas of AggregateFunction columns, so the threshold can be exceeded despite the small result size.

danger

The setting is fairly low level and should be used with caution

max_result_rows​

Type
Default value
Type
UInt64
Default value
0
Cloud default value: 0.

Limits the number of rows in the result. Also checked for subqueries, and on remote servers when running parts of a distributed query. No limit is applied when the value is 0.

The query will stop after processing a block of data if the threshold is met, but it will not cut the last block of the result, therefore the result size can be larger than the threshold.

max_rows_in_distinct​

Type
Default value
Type
UInt64
Default value
0
The maximum number of different rows when using DISTINCT.

max_rows_in_join​

Type
Default value
Type
UInt64
Default value
0
Limits the number of rows in the hash table that is used when joining tables.

This settings applies to SELECT ... JOIN operations and the Join table engine.

If a query contains multiple joins, ClickHouse checks this setting for every intermediate result.

ClickHouse can proceed with different actions when the limit is reached. Use the join_overflow_mode setting to choose the action.

Possible values:

  • Positive integer.
  • 0 — Unlimited number of rows.

max_rows_in_set​

Type
Default value
Type
UInt64
Default value
0
The maximum number of rows for a data set in the IN clause created from a subquery.

max_rows_in_set_to_optimize_join​

Type
Default value
Type
UInt64
Default value
0

Maximal size of the set to filter joined tables by each other's row sets before joining.

Possible values:

  • 0 — Disable.
  • Any positive integer.

max_rows_to_group_by​

Type
Default value
Type
UInt64
Default value
0
The maximum number of unique keys received from aggregation. This setting lets you limit memory consumption when aggregating.

If aggregation during GROUP BY is generating more than the specified number of rows (unique GROUP BY keys), the behavior will be determined by the 'group_by_overflow_mode' which by default is throw, but can be also switched to an approximate GROUP BY mode.

max_rows_to_read​

Type
Default value
Type
UInt64
Default value
0
The maximum number of rows that can be read from a table when running a query. The restriction is checked for each processed chunk of data, applied only to the deepest table expression and when reading from a remote server, checked only on the remote server.

max_rows_to_read_leaf​

Type
Default value
Type
UInt64
Default value
0
The maximum number of rows that can be read from a local table on a leaf node when running a distributed query. While distributed queries can issue multiple sub-queries to each shard (leaf) - this limit will be checked only on the read stage on the leaf nodes and ignored on the merging of results stage on the root node.

For example, a cluster consists of 2 shards and each shard contains a table with 100 rows. The distributed query which is supposed to read all the data from both tables with setting max_rows_to_read=150 will fail, as in total there will be 200 rows. A query with max_rows_to_read_leaf=150 will succeed, since leaf nodes will read at max 100 rows.

The restriction is checked for each processed chunk of data.

note

This setting is unstable with prefer_localhost_replica=1.

max_rows_to_sort​

Type
Default value
Type
UInt64
Default value
0
The maximum number of rows before sorting. This allows you to limit memory consumption when sorting. If more than the specified amount of records have to be processed for the ORDER BY operation, the behavior will be determined by the sort_overflow_mode which by default is set to throw.

max_rows_to_transfer​

Type
Default value
Type
UInt64
Default value
0
Maximum size (in rows) that can be passed to a remote server or saved in a temporary table when the GLOBAL IN/JOIN section is executed.

max_sessions_for_user​

Type
Default value
Type
UInt64
Default value
0
Maximum number of simultaneous sessions per authenticated user to the ClickHouse server.

Example:

Possible values:

  • Positive integer
  • 0 - infinite count of simultaneous sessions (default)

max_size_to_preallocate_for_aggregation​

Type
Default value
Type
UInt64
Default value
1000000000000

For how many elements it is allowed to preallocate space in all hash tables in total before aggregatio

max_size_to_preallocate_for_joins​

Type
Default value
Type
UInt64
Default value
1000000000000

For how many elements it is allowed to preallocate space in all hash tables in total before joi

max_streams_for_merge_tree_reading​

Type
Default value
Type
UInt64
Default value
0
If is not zero, limit the number of reading streams for MergeTree table.

max_streams_multiplier_for_merge_tables​

Type
Default value
Type
Float
Default value
5
Ask more streams when reading from Merge table. Streams will be spread across tables that Merge table will use. This allows more even distribution of work across threads and is especially helpful when merged tables differ in size.

max_streams_to_max_threads_ratio​

Type
Default value
Type
Float
Default value
1
Allows you to use more sources than the number of threads - to more evenly distribute work across threads. It is assumed that this is a temporary solution since it will be possible in the future to make the number of sources equal to the number of threads, but for each source to dynamically select available work for itself.

max_subquery_depth​

Type
Default value
Type
UInt64
Default value
100
If a query has more than the specified number of nested subqueries, throws an exception.

tip

This allows you to have a sanity check to protect against the users of your cluster from writing overly complex queries.

max_table_size_to_drop​

Type
Default value
Type
UInt64
Default value
50000000000
Restriction on deleting tables in query time. The value 0 means that you can delete all tables without any restrictions.

Cloud default value: 1 TB.

note

This query setting overwrites its server setting equivalent, see max_table_size_to_drop

max_temporary_columns​

Type
Default value
Type
UInt64
Default value
0
The maximum number of temporary columns that must be kept in RAM simultaneously when running a query, including constant columns. If a query generates more than the specified number of temporary columns in memory as a result of intermediate calculation, then an exception is thrown.

tip

This setting is useful for preventing overly complex queries.

0 value means unlimited.

max_temporary_data_on_disk_size_for_query​

Type
Default value
Type
UInt64
Default value
0
The maximum amount of data consumed by temporary files on disk in bytes for all concurrently running queries.

Possible values:

  • Positive integer.
  • 0 — unlimited (default)

max_temporary_data_on_disk_size_for_user​

Type
Default value
Type
UInt64
Default value
0
The maximum amount of data consumed by temporary files on disk in bytes for all concurrently running user queries.

Possible values:

  • Positive integer.
  • 0 — unlimited (default)

max_temporary_non_const_columns​

Type
Default value
Type
UInt64
Default value
0
Like max_temporary_columns, the maximum number of temporary columns that must be kept in RAM simultaneously when running a query, but without counting constant columns.

note

Constant columns are formed fairly often when running a query, but they require approximately zero computing resources.

max_threads​

Type
Default value
Type
MaxThreads
Default value
'auto(4)'
The maximum number of query processing threads, excluding threads for retrieving data from remote servers (see the 'max_distributed_connections' parameter).

This parameter applies to threads that perform the same stages of the query processing pipeline in parallel. For example, when reading from a table, if it is possible to evaluate expressions with functions, filter with WHERE and pre-aggregate for GROUP BY in parallel using at least 'max_threads' number of threads, then 'max_threads' are used.

For queries that are completed quickly because of a LIMIT, you can set a lower 'max_threads'. For example, if the necessary number of entries are located in every block and max_threads = 8, then 8 blocks are retrieved, although it would have been enough to read just one.

The smaller the max_threads value, the less memory is consumed.

max_threads_for_indexes​

Type
Default value
Type
UInt64
Default value
0
The maximum number of threads process indices.

max_untracked_memory​

Type
Default value
Type
UInt64
Default value
4194304
Small allocations and deallocations are grouped in thread local variable and tracked or profiled only when an amount (in absolute value) becomes larger than the specified value. If the value is higher than 'memory_profiler_step' it will be effectively lowered to 'memory_profiler_step'.

memory_overcommit_ratio_denominator​

Type
Default value
Type
UInt64
Default value
1073741824

It represents the soft memory limit when the hard limit is reached on the global level. This value is used to compute the overcommit ratio for the query. Zero means skip the query. Read more about memory overcommit.

memory_overcommit_ratio_denominator_for_user​

Type
Default value
Type
UInt64
Default value
1073741824

It represents the soft memory limit when the hard limit is reached on the user level. This value is used to compute the overcommit ratio for the query. Zero means skip the query. Read more about memory overcommit.

memory_profiler_sample_max_allocation_size​

Type
Default value
Type
UInt64
Default value
0
Collect random allocations of size less or equal than the specified value with probability equal to memory_profiler_sample_probability. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold work as expected.

memory_profiler_sample_min_allocation_size​

Type
Default value
Type
UInt64
Default value
0
Collect random allocations of size greater or equal than the specified value with probability equal to memory_profiler_sample_probability. 0 means disabled. You may want to set 'max_untracked_memory' to 0 to make this threshold work as expected.

memory_profiler_sample_probability​

Type
Default value
Type
Float
Default value
0
Collect random allocations and deallocations and write them into system.trace_log with 'MemorySample' trace_type. The probability is for every alloc/free regardless of the size of the allocation (can be changed with memory_profiler_sample_min_allocation_size and memory_profiler_sample_max_allocation_size). Note that sampling happens only when the amount of untracked memory exceeds 'max_untracked_memory'. You may want to set 'max_untracked_memory' to 0 for extra fine-grained sampling.

memory_profiler_step​

Type
Default value
Type
UInt64
Default value
4194304
Sets the step of memory profiler. Whenever query memory usage becomes larger than every next step in number of bytes the memory profiler will collect the allocating stacktrace and will write it into trace_log.

Possible values:

  • A positive integer number of bytes.

  • 0 for turning off the memory profiler.

memory_tracker_fault_probability​

Type
Default value
Type
Float
Default value
0
For testing of exception safety - throw an exception every time you allocate memory with the specified probability.

memory_usage_overcommit_max_wait_microseconds​

Type
Default value
Type
UInt64
Default value
5000000
Maximum time thread will wait for memory to be freed in the case of memory overcommit on a user level. If the timeout is reached and memory is not freed, an exception is thrown. Read more about memory overcommit.

merge_table_max_tables_to_look_for_schema_inference​

Type
Default value
Type
UInt64
Default value
1000

When creating a Merge table without an explicit schema or when using the merge table function, infer schema as a union of not more than the specified number of matching tables. If there is a larger number of tables, the schema will be inferred from the first specified number of tables.

merge_tree_coarse_index_granularity​

Type
Default value
Type
UInt64
Default value
8
When searching for data, ClickHouse checks the data marks in the index file. If ClickHouse finds that required keys are in some range, it divides this range into merge_tree_coarse_index_granularity subranges and searches the required keys there recursively.

Possible values:

  • Any positive even integer.

merge_tree_compact_parts_min_granules_to_multibuffer_read​

ClickHouse Cloud only

Type
Default value
Type
UInt64
Default value
16
Only has an effect in ClickHouse Cloud. Number of granules in stripe of compact part of MergeTree tables to use multibuffer reader, which supports parallel reading and prefetch. In case of reading from remote fs using of multibuffer reader increases number of read request.

merge_tree_determine_task_size_by_prewhere_columns​

Type
Default value
Type
Bool
Default value
1
Whether to use only prewhere columns size to determine reading task size.

merge_tree_max_bytes_to_use_cache​

Type
Default value
Type
UInt64
Default value
2013265920
If ClickHouse should read more than merge_tree_max_bytes_to_use_cache bytes in one query, it does not use the cache of uncompressed blocks.

The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.

Possible values:

  • Any positive integer.

merge_tree_max_rows_to_use_cache​

Type
Default value
Type
UInt64
Default value
1048576
If ClickHouse should read more than merge_tree_max_rows_to_use_cache rows in one query, it does not use the cache of uncompressed blocks.

The cache of uncompressed blocks stores data extracted for queries. ClickHouse uses this cache to speed up responses to repeated small queries. This setting protects the cache from trashing by queries that read a large amount of data. The uncompressed_cache_size server setting defines the size of the cache of uncompressed blocks.

Possible values:

  • Any positive integer.

merge_tree_min_bytes_for_concurrent_read​

Type
Default value
Type
UInt64
Default value
251658240
If the number of bytes to read from one file of a MergeTree-engine table exceeds merge_tree_min_bytes_for_concurrent_read, then ClickHouse tries to concurrently read from this file in several threads.

Possible value:

  • Positive integer.

merge_tree_min_bytes_for_concurrent_read_for_remote_filesystem​

Type
Default value
Type
UInt64
Default value
0

The minimum number of bytes to read from one file before MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.

Possible values:

  • Positive integer.

merge_tree_min_bytes_for_seek​

Type
Default value
Type
UInt64
Default value
0
If the distance between two data blocks to be read in one file is less than merge_tree_min_bytes_for_seek bytes, then ClickHouse sequentially reads a range of file that contains both blocks, thus avoiding extra seek.

Possible values:

  • Any positive integer.

merge_tree_min_bytes_per_task_for_remote_reading​

Type
Default value
Type
UInt64
Default value
2097152

Min bytes to read per task.

merge_tree_min_read_task_size​

Type
Default value
Type
UInt64
Default value
8

Hard lower limit on the task size (even when the number of granules is low and the number of available threads is high we won't allocate smaller tasks

merge_tree_min_rows_for_concurrent_read​

Type
Default value
Type
UInt64
Default value
163840
If the number of rows to be read from a file of a MergeTree table exceeds merge_tree_min_rows_for_concurrent_read then ClickHouse tries to perform a concurrent reading from this file on several threads.

Possible values:

  • Positive integer.

merge_tree_min_rows_for_concurrent_read_for_remote_filesystem​

Type
Default value
Type
UInt64
Default value
0

The minimum number of lines to read from one file before the MergeTree engine can parallelize reading, when reading from remote filesystem. We do not recommend using this setting.

Possible values:

  • Positive integer.

merge_tree_min_rows_for_seek​

Type
Default value
Type
UInt64
Default value
0
If the distance between two data blocks to be read in one file is less than merge_tree_min_rows_for_seek rows, then ClickHouse does not seek through the file but reads the data sequentially.

Possible values:

  • Any positive integer.

merge_tree_read_split_ranges_into_intersecting_and_non_intersecting_injection_probability​

Type
Default value
Type
Float
Default value
0

For testing of PartsSplitter - split read ranges into intersecting and non intersecting every time you read from MergeTree with the specified probability.

merge_tree_use_const_size_tasks_for_remote_reading​

Type
Default value
Type
Bool
Default value
1
Whether to use constant size tasks for reading from a remote table.

merge_tree_use_deserialization_prefixes_cache​

Type
Default value
Type
Bool
Default value
1

Enables caching of columns metadata from the file prefixes during reading from Wide parts in MergeTree.

merge_tree_use_prefixes_deserialization_thread_pool​

Type
Default value
Type
Bool
Default value
1

Enables usage of the thread pool for parallel prefixes reading in Wide parts in MergeTree. Size of that thread pool is controlled by server setting max_prefixes_deserialization_thread_pool_size.

merge_tree_use_v1_object_and_dynamic_serialization​

Type
Default value
Type
Bool
Default value
0

When enabled, V1 serialization version of JSON and Dynamic types will be used in MergeTree instead of V2. Changing this setting takes affect only after server restart.

metrics_perf_events_enabled​

Type
Default value
Type
Bool
Default value
0
If enabled, some of the perf events will be measured throughout queries' execution.

metrics_perf_events_list​

Comma separated list of perf metrics that will be measured throughout queries' execution. Empty means all events. See PerfEventInfo in sources for the available events.

min_bytes_to_use_direct_io​

Type
Default value
Type
UInt64
Default value
0
The minimum data volume required for using direct I/O access to the storage disk.

ClickHouse uses this setting when reading data from tables. If the total storage volume of all the data to be read exceeds min_bytes_to_use_direct_io bytes, then ClickHouse reads the data from the storage disk with the O_DIRECT option.

Possible values:

  • 0 — Direct I/O is disabled.
  • Positive integer.

min_bytes_to_use_mmap_io​

Type
Default value
Type
UInt64
Default value
0
This is an experimental setting. Sets the minimum amount of memory for reading large files without copying data from the kernel to userspace. Recommended threshold is about 64 MB, because mmap/munmap is slow. It makes sense only for large files and helps only if data reside in the page cache.

Possible values:

  • Positive integer.
  • 0 — Big files read with only copying data from kernel to userspace.

min_chunk_bytes_for_parallel_parsing​

Type
Default value
Type
NonZeroUInt64
Default value
10485760
- Type: unsigned int

  • Default value: 1 MiB

The minimum chunk size in bytes, which each thread will parse in parallel.

min_compress_block_size​

Type
Default value
Type
UInt64
Default value
65536
For MergeTree tables. In order to reduce latency when processing queries, a block is compressed when writing the next mark if its size is at least min_compress_block_size. By default, 65,536.

The actual size of the block, if the uncompressed data is less than max_compress_block_size, is no less than this value and no less than the volume of data for one mark.

Let's look at an example. Assume that index_granularity was set to 8192 during table creation.

We are writing a UInt32-type column (4 bytes per value). When writing 8192 rows, the total will be 32 KB of data. Since min_compress_block_size = 65,536, a compressed block will be formed for every two marks.

We are writing a URL column with the String type (average size of 60 bytes per value). When writing 8192 rows, the average will be slightly less than 500 KB of data. Since this is more than 65,536, a compressed block will be formed for each mark. In this case, when reading data from the disk in the range of a single mark, extra data won't be decompressed.

note

This is an expert-level setting, and you shouldn't change it if you're just getting started with ClickHouse.

min_count_to_compile_aggregate_expression​

Type
Default value
Type
UInt64
Default value
3
The minimum number of identical aggregate expressions to start JIT-compilation. Works only if the compile_aggregate_expressions setting is enabled.

Possible values:

  • Positive integer.
  • 0 — Identical aggregate expressions are always JIT-compiled.

min_count_to_compile_expression​

Type
Default value
Type
UInt64
Default value
3
Minimum count of executing same expression before it is get compiled.

min_count_to_compile_sort_description​

Type
Default value
Type
UInt64
Default value
3
The number of identical sort descriptions before they are JIT-compiled

min_execution_speed​

Type
Default value
Type
UInt64
Default value
0
Minimal execution speed in rows per second. Checked on every data block when timeout_before_checking_execution_speed expires. If the execution speed is lower, an exception is thrown.

min_execution_speed_bytes​

Type
Default value
Type
UInt64
Default value
0
The minimum number of execution bytes per second. Checked on every data block when timeout_before_checking_execution_speed expires. If the execution speed is lower, an exception is thrown.

min_external_sort_block_bytes​

Type
Default value
Type
UInt64
Default value
104857600

Minimal block size in bytes for external sort that will be dumped to disk, to avoid too many files.

min_external_table_block_size_bytes​

Type
Default value
Type
UInt64
Default value
268402944

Squash blocks passed to the external table to a specified size in bytes, if blocks are not big enough.

min_external_table_block_size_rows​

Type
Default value
Type
UInt64
Default value
1048449

Squash blocks passed to external table to specified size in rows, if blocks are not big enough.

min_free_disk_bytes_to_perform_insert​

Type
Default value
Type
UInt64
Default value
0

Minimum free disk space bytes to perform an insert.

min_free_disk_ratio_to_perform_insert​

Type
Default value
Type
Float
Default value
0

Minimum free disk space ratio to perform an insert.

min_free_disk_space_for_temporary_data​

Type
Default value
Type
UInt64
Default value
0
The minimum disk space to keep while writing temporary data used in external sorting and aggregation.

min_hit_rate_to_use_consecutive_keys_optimization​

Type
Default value
Type
Float
Default value
0.5
Minimal hit rate of a cache which is used for consecutive keys optimization in aggregation to keep it enabled

min_insert_block_size_bytes​

Type
Default value
Type
UInt64
Default value
268402944
Sets the minimum number of bytes in the block which can be inserted into a table by an INSERT query. Smaller-sized blocks are squashed into bigger ones.

Possible values:

  • Positive integer.
  • 0 — Squashing disabled.

min_insert_block_size_bytes_for_materialized_views​

Type
Default value
Type
UInt64
Default value
0
Sets the minimum number of bytes in the block which can be inserted into a table by an INSERT query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into materialized view. By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.

Possible values:

  • Any positive integer.
  • 0 — Squashing disabled.

See also

min_insert_block_size_rows​

Type
Default value
Type
UInt64
Default value
1048449
Sets the minimum number of rows in the block that can be inserted into a table by an INSERT query. Smaller-sized blocks are squashed into bigger ones.

Possible values:

  • Positive integer.
  • 0 — Squashing disabled.

min_insert_block_size_rows_for_materialized_views​

Type
Default value
Type
UInt64
Default value
0
Sets the minimum number of rows in the block which can be inserted into a table by an INSERT query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into materialized view. By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.

Possible values:

  • Any positive integer.
  • 0 — Squashing disabled.

See Also

min_joined_block_size_bytes​

Type
Default value
Type
UInt64
Default value
524288

Minimum block size for JOIN result (if join algorithm supports it). 0 means unlimited.

min_os_cpu_wait_time_ratio_to_throw​

Type
Default value
Type
Float
Default value
2

Min ratio between OS CPU wait (OSCPUWaitMicroseconds metric) and busy (OSCPUVirtualTimeMicroseconds metric) times to consider rejecting queries. Linear interpolation between min and max ratio is used to calculate the probability, the probability is 0 at this point.

mongodb_throw_on_unsupported_query​

Type
Default value
Type
Bool
Default value
1

If enabled, MongoDB tables will return an error when a MongoDB query cannot be built. Otherwise, ClickHouse reads the full table and processes it locally. This option does not apply when 'allow_experimental_analyzer=0'.

move_all_conditions_to_prewhere​

Type
Default value
Type
Bool
Default value
1
Move all viable conditions from WHERE to PREWHERE

move_primary_key_columns_to_end_of_prewhere​

Type
Default value
Type
Bool
Default value
1
Move PREWHERE conditions containing primary key columns to the end of AND chain. It is likely that these conditions are taken into account during primary key analysis and thus will not contribute a lot to PREWHERE filtering.

multiple_joins_try_to_keep_original_names​

Type
Default value
Type
Bool
Default value
0
Do not add aliases to top level expression list on multiple joins rewrite

mutations_execute_nondeterministic_on_initiator​

Type
Default value
Type
Bool
Default value
0
If true constant nondeterministic functions (e.g. function now()) are executed on initiator and replaced to literals in UPDATE and DELETE queries. It helps to keep data in sync on replicas while executing mutations with constant nondeterministic functions. Default value: false.

mutations_execute_subqueries_on_initiator​

Type
Default value
Type
Bool
Default value
0
If true scalar subqueries are executed on initiator and replaced to literals in UPDATE and DELETE queries. Default value: false.

mutations_max_literal_size_to_replace​

Type
Default value
Type
UInt64
Default value
16384
The maximum size of serialized literal in bytes to replace in UPDATE and DELETE queries. Takes effect only if at least one the two settings above is enabled. Default value: 16384 (16 KiB).

mutations_sync​

Type
Default value
Type
UInt64
Default value
0
Allows to execute ALTER TABLE ... UPDATE|DELETE|MATERIALIZE INDEX|MATERIALIZE PROJECTION|MATERIALIZE COLUMN|MATERIALIZE STATISTICS queries (mutations) synchronously.

Possible values:

  • 0 - Mutations execute asynchronously.
  • 1 - The query waits for all mutations to complete on the current server.
  • 2 - The query waits for all mutations to complete on all replicas (if they exist).

mysql_datatypes_support_level​

Defines how MySQL types are converted to corresponding ClickHouse types. A comma separated list in any combination of decimal, datetime64, date2Date32 or date2String.

  • decimal: convert NUMERIC and DECIMAL types to Decimal when precision allows it.
  • datetime64: convert DATETIME and TIMESTAMP types to DateTime64 instead of DateTime when precision is not 0.
  • date2Date32: convert DATE to Date32 instead of Date. Takes precedence over date2String.
  • date2String: convert DATE to String instead of Date. Overridden by datetime64.

mysql_map_fixed_string_to_text_in_show_columns​

Type
Default value
Type
Bool
Default value
1

When enabled, FixedString ClickHouse data type will be displayed as TEXT in SHOW COLUMNS.

Has an effect only when the connection is made through the MySQL wire protocol.

  • 0 - Use BLOB.
  • 1 - Use TEXT.

mysql_map_string_to_text_in_show_columns​

Type
Default value
Type
Bool
Default value
1

When enabled, String ClickHouse data type will be displayed as TEXT in SHOW COLUMNS.

Has an effect only when the connection is made through the MySQL wire protocol.

  • 0 - Use BLOB.
  • 1 - Use TEXT.

mysql_max_rows_to_insert​

Type
Default value
Type
UInt64
Default value
65536
The maximum number of rows in MySQL batch insertion of the MySQL storage engine

network_compression_method​

Type
Default value
Type
String
Default value
LZ4
Sets the method of data compression that is used for communication between servers and between server and clickhouse-client.

Possible values:

  • LZ4 — sets LZ4 compression method.
  • ZSTD — sets ZSTD compression method.

See Also

network_zstd_compression_level​

Type
Default value
Type
Int64
Default value
1
Adjusts the level of ZSTD compression. Used only when network_compression_method is set to ZSTD.

Possible values:

  • Positive integer from 1 to 15.

normalize_function_names​

Type
Default value
Type
Bool
Default value
1

Normalize function names to their canonical names

number_of_mutations_to_delay​

Type
Default value
Type
UInt64
Default value
0
If the mutated table contains at least that many unfinished mutations, artificially slow down mutations of table. 0 - disabled

number_of_mutations_to_throw​

Type
Default value
Type
UInt64
Default value
0
If the mutated table contains at least that many unfinished mutations, throw 'Too many mutations ...' exception. 0 - disabled

odbc_bridge_connection_pool_size​

Type
Default value
Type
UInt64
Default value
16
Connection pool size for each connection settings string in ODBC bridge.

odbc_bridge_use_connection_pooling​

Type
Default value
Type
Bool
Default value
1
Use connection pooling in ODBC bridge. If set to false, a new connection is created every time.

offset​

Type
Default value
Type
UInt64
Default value
0
Sets the number of rows to skip before starting to return rows from the query. It adjusts the offset set by the OFFSET clause, so that these two values are summarized.

Possible values:

  • 0 — No rows are skipped .
  • Positive integer.

Example

Input table:

Query:

Result:

opentelemetry_start_trace_probability​

Type
Default value
Type
Float
Default value
0
Sets the probability that the ClickHouse can start a trace for executed queries (if no parent trace context is supplied).

Possible values:

  • 0 — The trace for all executed queries is disabled (if no parent trace context is supplied).
  • Positive floating-point number in the range [0..1]. For example, if the setting value is 0,5, ClickHouse can start a trace on average for half of the queries.
  • 1 — The trace for all executed queries is enabled.

opentelemetry_trace_processors​

Type
Default value
Type
Bool
Default value
0
Collect OpenTelemetry spans for processors.

optimize_aggregation_in_order​

Type
Default value
Type
Bool
Default value
0
Enables GROUP BY optimization in SELECT queries for aggregating data in corresponding order in MergeTree tables.

Possible values:

  • 0 — GROUP BY optimization is disabled.
  • 1 — GROUP BY optimization is enabled.

See Also

optimize_aggregators_of_group_by_keys​

Type
Default value
Type
Bool
Default value
1
Eliminates min/max/any/anyLast aggregators of GROUP BY keys in SELECT sectio

optimize_and_compare_chain​

Type
Default value
Type
Bool
Default value
1

Populate constant comparison in AND chains to enhance filtering ability. Support operators <, <=, >, >=, = and mix of them. For example, (a < b) AND (b < c) AND (c < 5) would be (a < b) AND (b < c) AND (c < 5) AND (b < 5) AND (a < 5).

optimize_append_index​

Type
Default value
Type
Bool
Default value
0
Use constraints in order to append index condition. The default is false.

Possible values:

  • true, false

optimize_arithmetic_operations_in_aggregate_functions​

Type
Default value
Type
Bool
Default value
1
Move arithmetic operations out of aggregation functions

optimize_count_from_files​

Type
Default value
Type
Bool
Default value
1
Enables or disables the optimization of counting number of rows from files in different input formats. It applies to table functions/engines file/s3/url/hdfs/azureBlobStorage.

Possible values:

  • 0 — Optimization disabled.
  • 1 — Optimization enabled.

optimize_distinct_in_order​

Type
Default value
Type
Bool
Default value
1
Enable DISTINCT optimization if some columns in DISTINCT form a prefix of sorting. For example, prefix of sorting key in merge tree or ORDER BY statement

optimize_distributed_group_by_sharding_key​

Type
Default value
Type
Bool
Default value
1
Optimize GROUP BY sharding_key queries, by avoiding costly aggregation on the initiator server (which will reduce memory usage for the query on the initiator server).

The following types of queries are supported (and all combinations of them):

  • SELECT DISTINCT [..., ]sharding_key[, ...] FROM dist
  • SELECT ... FROM dist GROUP BY sharding_key[, ...]
  • SELECT ... FROM dist GROUP BY sharding_key[, ...] ORDER BY x
  • SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1
  • SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1 BY x

The following types of queries are not supported (support for some of them may be added later):

  • SELECT ... GROUP BY sharding_key[, ...] WITH TOTALS
  • SELECT ... GROUP BY sharding_key[, ...] WITH ROLLUP
  • SELECT ... GROUP BY sharding_key[, ...] WITH CUBE
  • SELECT ... GROUP BY sharding_key[, ...] SETTINGS extremes=1

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

See also:

note

Right now it requires optimize_skip_unused_shards (the reason behind this is that one day it may be enabled by default, and it will work correctly only if data was inserted via Distributed table, i.e. data is distributed according to sharding_key).

optimize_extract_common_expressions​

Type
Default value
Type
Bool
Default value
1

Allow extracting common expressions from disjunctions in WHERE, PREWHERE, ON, HAVING and QUALIFY expressions. A logical expression like (A AND B) OR (A AND C) can be rewritten to A AND (B OR C), which might help to utilize:

  • indices in simple filtering expressions
  • cross to inner join optimizatio

optimize_functions_to_subcolumns​

Type
Default value
Type
Bool
Default value
1

Enables or disables optimization by transforming some functions to reading subcolumns. This reduces the amount of data to read.

These functions can be transformed:

Possible values:

  • 0 — Optimization disabled.
  • 1 — Optimization enabled.

optimize_group_by_constant_keys​

Type
Default value
Type
Bool
Default value
1

Optimize GROUP BY when all keys in block are constant

optimize_group_by_function_keys​

Type
Default value
Type
Bool
Default value
1
Eliminates functions of other keys in GROUP BY sectio

optimize_if_chain_to_multiif​

Type
Default value
Type
Bool
Default value
0
Replace if(cond1, then1, if(cond2, ...)) chains to multiIf. Currently it's not beneficial for numeric types.

optimize_if_transform_strings_to_enum​

Type
Default value
Type
Bool
Default value
0
Replaces string-type arguments in If and Transform to enum. Disabled by default cause it could make inconsistent change in distributed query that would lead to its fail.

optimize_injective_functions_in_group_by​

Type
Default value
Type
Bool
Default value
1

Replaces injective functions by it's arguments in GROUP BY sectio

optimize_injective_functions_inside_uniq​

Type
Default value
Type
Bool
Default value
1
Delete injective functions of one argument inside uniq*() functions.

optimize_min_equality_disjunction_chain_length​

Type
Default value
Type
UInt64
Default value
3
The minimum length of the expression expr = x1 OR ... expr = xN for optimizatio

optimize_min_inequality_conjunction_chain_length​

Type
Default value
Type
UInt64
Default value
3
The minimum length of the expression expr <> x1 AND ... expr <> xN for optimizatio

optimize_move_to_prewhere​

Type
Default value
Type
Bool
Default value
1
Enables or disables automatic PREWHERE optimization in SELECT queries.

Works only for *MergeTree tables.

Possible values:

  • 0 — Automatic PREWHERE optimization is disabled.
  • 1 — Automatic PREWHERE optimization is enabled.

optimize_move_to_prewhere_if_final​

Type
Default value
Type
Bool
Default value
0
Enables or disables automatic PREWHERE optimization in SELECT queries with FINAL modifier.

Works only for *MergeTree tables.

Possible values:

  • 0 — Automatic PREWHERE optimization in SELECT queries with FINAL modifier is disabled.
  • 1 — Automatic PREWHERE optimization in SELECT queries with FINAL modifier is enabled.

See Also

optimize_multiif_to_if​

Type
Default value
Type
Bool
Default value
1
Replace 'multiIf' with only one condition to 'if'.

optimize_normalize_count_variants​

Type
Default value
Type
Bool
Default value
1

Rewrite aggregate functions that semantically equals to count() as count().

optimize_on_insert​

Type
Default value
Type
Bool
Default value
1

Enables or disables data transformation before the insertion, as if merge was done on this block (according to table engine).

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Example

The difference between enabled and disabled:

Query:

Result:

Note that this setting influences Materialized view behaviour.

optimize_or_like_chain​

Type
Default value
Type
Bool
Default value
0
Optimize multiple OR LIKE into multiMatchAny. This optimization should not be enabled by default, because it defies index analysis in some cases.

optimize_read_in_order​

Type
Default value
Type
Bool
Default value
1
Enables ORDER BY optimization in SELECT queries for reading data from MergeTree tables.

Possible values:

  • 0 — ORDER BY optimization is disabled.
  • 1 — ORDER BY optimization is enabled.

See Also

optimize_read_in_window_order​

Type
Default value
Type
Bool
Default value
1
Enable ORDER BY optimization in window clause for reading data in corresponding order in MergeTree tables.

optimize_redundant_functions_in_order_by​

Type
Default value
Type
Bool
Default value
1
Remove functions from ORDER BY if its argument is also in ORDER BY

optimize_respect_aliases​

Type
Default value
Type
Bool
Default value
1
If it is set to true, it will respect aliases in WHERE/GROUP BY/ORDER BY, that will help with partition pruning/secondary indexes/optimize_aggregation_in_order/optimize_read_in_order/optimize_trivial_count

optimize_rewrite_aggregate_function_with_if​

Type
Default value
Type
Bool
Default value
1
Rewrite aggregate functions with if expression as argument when logically equivalent. For example, avg(if(cond, col, null)) can be rewritten to avgOrNullIf(cond, col). It may improve performance.

note

Supported only with the analyzer (enable_analyzer = 1).

optimize_rewrite_array_exists_to_has​

Type
Default value
Type
Bool
Default value
0
Rewrite arrayExists() functions to has() when logically equivalent. For example, arrayExists(x -> x = 1, arr) can be rewritten to has(arr, 1)

optimize_rewrite_sum_if_to_count_if​

Type
Default value
Type
Bool
Default value
1

Rewrite sumIf() and sum(if()) function countIf() function when logically equivalent

optimize_skip_merged_partitions​

Type
Default value
Type
Bool
Default value
0
Enables or disables optimization for OPTIMIZE TABLE ... FINAL query if there is only one part with level > 0 and it doesn't have expired TTL.

  • OPTIMIZE TABLE ... FINAL SETTINGS optimize_skip_merged_partitions=1

By default, OPTIMIZE TABLE ... FINAL query rewrites the one part even if there is only a single part.

Possible values:

  • 1 - Enable optimization.
  • 0 - Disable optimization.

optimize_skip_unused_shards​

Type
Default value
Type
Bool
Default value
0
Enables or disables skipping of unused shards for SELECT queries that have sharding key condition in WHERE/PREWHERE (assuming that the data is distributed by sharding key, otherwise a query yields incorrect result).

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

optimize_skip_unused_shards_limit​

Type
Default value
Type
UInt64
Default value
1000
Limit for number of sharding key values, turns off optimize_skip_unused_shards if the limit is reached.

Too many values may require significant amount for processing, while the benefit is doubtful, since if you have huge number of values in IN (...), then most likely the query will be sent to all shards anyway.

optimize_skip_unused_shards_nesting​

Type
Default value
Type
UInt64
Default value
0
Controls optimize_skip_unused_shards (hence still requires optimize_skip_unused_shards) depends on the nesting level of the distributed query (case when you have Distributed table that look into another Distributed table).

Possible values:

  • 0 — Disabled, optimize_skip_unused_shards works always.
  • 1 — Enables optimize_skip_unused_shards only for the first level.
  • 2 — Enables optimize_skip_unused_shards up to the second level.

optimize_skip_unused_shards_rewrite_in​

Type
Default value
Type
Bool
Default value
1
Rewrite IN in query for remote shards to exclude values that does not belong to the shard (requires optimize_skip_unused_shards).

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

optimize_sorting_by_input_stream_properties​

Type
Default value
Type
Bool
Default value
1
Optimize sorting by sorting properties of input stream

optimize_substitute_columns​

Type
Default value
Type
Bool
Default value
0
Use constraints for column substitution. The default is false.

Possible values:

  • true, false

optimize_syntax_fuse_functions​

Type
Default value
Type
Bool
Default value
0
Enables to fuse aggregate functions with identical argument. It rewrites query contains at least two aggregate functions from sum, count or avg with identical argument to sumCount.

Possible values:

  • 0 — Functions with identical argument are not fused.
  • 1 — Functions with identical argument are fused.

Example

Query:

Result:

optimize_throw_if_noop​

Type
Default value
Type
Bool
Default value
0
Enables or disables throwing an exception if an OPTIMIZE query didn't perform a merge.

By default, OPTIMIZE returns successfully even if it didn't do anything. This setting lets you differentiate these situations and get the reason in an exception message.

Possible values:

  • 1 — Throwing an exception is enabled.
  • 0 — Throwing an exception is disabled.

optimize_time_filter_with_preimage​

Type
Default value
Type
Bool
Default value
1

Optimize Date and DateTime predicates by converting functions into equivalent comparisons without conversions (e.g. toYear(col) = 2023 -> col >= '2023-01-01' AND col <= '2023-12-31')

optimize_trivial_approximate_count_query​

Type
Default value
Type
Bool
Default value
0
Use an approximate value for trivial count optimization of storages that support such estimation, for example, EmbeddedRocksDB.

Possible values:

  • 0 — Optimization disabled.
  • 1 — Optimization enabled.

optimize_trivial_count_query​

Type
Default value
Type
Bool
Default value
1
Enables or disables the optimization to trivial query SELECT count() FROM table using metadata from MergeTree. If you need to use row-level security, disable this setting.

Possible values:

  • 0 — Optimization disabled.
  • 1 — Optimization enabled.

See also:

optimize_trivial_insert_select​

Type
Default value
Type
Bool
Default value
0

Optimize trivial 'INSERT INTO table SELECT ... FROM TABLES' query

optimize_uniq_to_count​

Type
Default value
Type
Bool
Default value
1
Rewrite uniq and its variants(except uniqUpTo) to count if subquery has distinct or group by clause.

optimize_use_implicit_projections​

Type
Default value
Type
Bool
Default value
1
Automatically choose implicit projections to perform SELECT query

optimize_use_projections​

Type
Default value
Type
Bool
Default value
1
Enables or disables projection optimization when processing SELECT queries.

Possible values:

  • 0 — Projection optimization disabled.
  • 1 — Projection optimization enabled.

optimize_using_constraints​

Type
Default value
Type
Bool
Default value
0
Use constraints for query optimization. The default is false.

Possible values:

  • true, false

os_thread_priority​

Type
Default value
Type
Int64
Default value
0
Sets the priority (nice) for threads that execute queries. The OS scheduler considers this priority when choosing the next thread to run on each available CPU core.

note

To use this setting, you need to set the CAP_SYS_NICE capability. The clickhouse-server package sets it up during installation. Some virtual environments do not allow you to set the CAP_SYS_NICE capability. In this case, clickhouse-server shows a message about it at the start.

Possible values:

  • You can set values in the range [-20, 19].

Lower values mean higher priority. Threads with low nice priority values are executed more frequently than threads with high values. High values are preferable for long-running non-interactive queries because it allows them to quickly give up resources in favour of short interactive queries when they arrive.

output_format_compression_level​

Type
Default value
Type
UInt64
Default value
3

Default compression level if query output is compressed. The setting is applied when SELECT query has INTO OUTFILE or when writing to table functions file, url, hdfs, s3, or azureBlobStorage.

Possible values: from 1 to 22

output_format_compression_zstd_window_log​

Type
Default value
Type
UInt64
Default value
0

Can be used when the output compression method is zstd. If greater than 0, this setting explicitly sets compression window size (power of 2) and enables a long-range mode for zstd compression. This can help to achieve a better compression ratio.

Possible values: non-negative numbers. Note that if the value is too small or too big, zstdlib will throw an exception. Typical values are from 20 (window size = 1MB) to 30 (window size = 1GB).

output_format_parallel_formatting​

Type
Default value
Type
Bool
Default value
1
Enables or disables parallel formatting of data formats. Supported only for TSV, TSKV, CSV and JSONEachRow formats.

Possible values:

  • 1 — Enabled.
  • 0 — Disabled.

page_cache_inject_eviction​

Type
Default value
Type
Bool
Default value
0

Userspace page cache will sometimes invalidate some pages at random. Intended for testing.

parallel_distributed_insert_select​

Type
Default value
Type
UInt64
Default value
0
Enables parallel distributed INSERT ... SELECT query.

If we execute INSERT INTO distributed_table_a SELECT ... FROM distributed_table_b queries and both tables use the same cluster, and both tables are either replicated or non-replicated, then this query is processed locally on every shard.

Possible values:

  • 0 — Disabled.
  • 1 — SELECT will be executed on each shard from the underlying table of the distributed engine.
  • 2 — SELECT and INSERT will be executed on each shard from/to the underlying table of the distributed engine.

parallel_replica_offset​

Beta feature. Learn more.

Type
Default value
Type
UInt64
Default value
0
This is internal setting that should not be used directly and represents an implementation detail of the 'parallel replicas' mode. This setting will be automatically set up by the initiator server for distributed queries to the index of the replica participating in query processing among parallel replicas.

parallel_replicas_allow_in_with_subquery​

Beta feature. Learn more.
Type
Default value
Type
Bool
Default value
1

If true, subquery for IN will be executed on every follower replica.

parallel_replicas_count​

Beta feature. Learn more.

Type
Default value
Type
UInt64
Default value
0
This is internal setting that should not be used directly and represents an implementation detail of the 'parallel replicas' mode. This setting will be automatically set up by the initiator server for distributed queries to the number of parallel replicas participating in query processing.

parallel_replicas_custom_key​

Beta feature. Learn more.

An arbitrary integer expression that can be used to split work between replicas for a specific table. The value can be any integer expression.

Simple expressions using primary keys are preferred.

If the setting is used on a cluster that consists of a single shard with multiple replicas, those replicas will be converted into virtual shards. Otherwise, it will behave same as for SAMPLE key, it will use multiple replicas of each shard.

parallel_replicas_custom_key_range_lower​

Beta feature. Learn more.
Type
Default value
Type
UInt64
Default value
0

Allows the filter type range to split the work evenly between replicas based on the custom range [parallel_replicas_custom_key_range_lower, INT_MAX].

When used in conjunction with parallel_replicas_custom_key_range_upper, it lets the filter evenly split the work over replicas for the range [parallel_replicas_custom_key_range_lower, parallel_replicas_custom_key_range_upper].

Note: This setting will not cause any additional data to be filtered during query processing, rather it changes the points at which the range filter breaks up the range [0, INT_MAX] for parallel processing.

parallel_replicas_custom_key_range_upper​

Beta feature. Learn more.
Type
Default value
Type
UInt64
Default value
0

Allows the filter type range to split the work evenly between replicas based on the custom range [0, parallel_replicas_custom_key_range_upper]. A value of 0 disables the upper bound, setting it the max value of the custom key expression.

When used in conjunction with parallel_replicas_custom_key_range_lower, it lets the filter evenly split the work over replicas for the range [parallel_replicas_custom_key_range_lower, parallel_replicas_custom_key_range_upper].

Note: This setting will not cause any additional data to be filtered during query processing, rather it changes the points at which the range filter breaks up the range [0, INT_MAX] for parallel processing

parallel_replicas_for_cluster_engines​

Type
Default value
Type
Bool
Default value
1

Replace table function engines with their -Cluster alternatives

parallel_replicas_for_non_replicated_merge_tree​

Beta feature. Learn more.

Type
Default value
Type
Bool
Default value
0
If true, ClickHouse will use parallel replicas algorithm also for non-replicated MergeTree tables

parallel_replicas_index_analysis_only_on_coordinator​

Beta feature. Learn more.
Type
Default value
Type
Bool
Default value
1

Index analysis done only on replica-coordinator and skipped on other replicas. Effective only with enabled parallel_replicas_local_pla

parallel_replicas_local_plan​

Beta feature. Learn more.
Type
Default value
Type
Bool
Default value
1

Build local plan for local replica

parallel_replicas_mark_segment_size​

Beta feature. Learn more.
Type
Default value
Type
UInt64
Default value
0

Parts virtually divided into segments to be distributed between replicas for parallel reading. This setting controls the size of these segments. Not recommended to change until you're absolutely sure in what you're doing. Value should be in range [128; 16384]

parallel_replicas_min_number_of_rows_per_replica​

Beta feature. Learn more.

Type
Default value
Type
UInt64
Default value
0
Limit the number of replicas used in a query to (estimated rows to read / min_number_of_rows_per_replica). The max is still limited by 'max_parallel_replicas'

parallel_replicas_mode​

Beta feature. Learn more.
Type
Default value
Type
ParallelReplicasMode
Default value
read_tasks

Type of filter to use with custom key for parallel replicas. default - use modulo operation on the custom key, range - use range filter on custom key using all possible values for the value type of custom key.

parallel_replicas_only_with_analyzer​

Beta feature. Learn more.
Type
Default value
Type
Bool
Default value
1

The analyzer should be enabled to use parallel replicas. With disabled analyzer query execution fallbacks to local execution, even if parallel reading from replicas is enabled. Using parallel replicas without the analyzer enabled is not supported

parallel_replicas_prefer_local_join​

Beta feature. Learn more.
Type
Default value
Type
Bool
Default value
1

If true, and JOIN can be executed with parallel replicas algorithm, and all storages of right JOIN part are *MergeTree, local JOIN will be used instead of GLOBAL JOIN.

parallel_view_processing​

Type
Default value
Type
Bool
Default value
0
Enables pushing to attached views concurrently instead of sequentially.

parallelize_output_from_storages​

Type
Default value
Type
Bool
Default value
1

Parallelize output for reading step from storage. It allows parallelization of query processing right after reading from storage if possible

parsedatetime_parse_without_leading_zeros​

Type
Default value
Type
Bool
Default value
1

Formatters '%c', '%l' and '%k' in function 'parseDateTime' parse months and hours without leading zeros.

partial_merge_join_left_table_buffer_bytes​

Type
Default value
Type
UInt64
Default value
0
If not 0 group left table blocks in bigger ones for left-side table in partial merge join. It uses up to 2x of specified memory per joining thread.

partial_merge_join_rows_in_right_blocks​

Type
Default value
Type
UInt64
Default value
65536
Limits sizes of right-hand join data blocks in partial merge join algorithm for JOIN queries.

ClickHouse server:

  1. Splits right-hand join data into blocks with up to the specified number of rows.
  2. Indexes each block with its minimum and maximum values.
  3. Unloads prepared blocks to disk if it is possible.

Possible values:

  • Any positive integer. Recommended range of values: [1000, 100000].

partial_result_on_first_cancel​

Type
Default value
Type
Bool
Default value
0
Allows query to return a partial result after cancel.

parts_to_delay_insert​

Type
Default value
Type
UInt64
Default value
0
If the destination table contains at least that many active parts in a single partition, artificially slow down insert into table.

parts_to_throw_insert​

Type
Default value
Type
UInt64
Default value
0
If more than this number active parts in a single partition of the destination table, throw 'Too many parts ...' exception.

periodic_live_view_refresh​

Type
Default value
Type
Seconds
Default value
60
Interval after which periodically refreshed live view is forced to refresh.

poll_interval​

Type
Default value
Type
UInt64
Default value
10
Block at the query wait loop on the server for the specified number of seconds.

postgresql_connection_attempt_timeout​

Type
Default value
Type
UInt64
Default value
2

Connection timeout in seconds of a single attempt to connect PostgreSQL end-point. The value is passed as a connect_timeout parameter of the connection URL.

postgresql_connection_pool_auto_close_connection​

Type
Default value
Type
Bool
Default value
0
Close connection before returning connection to the pool.

postgresql_connection_pool_retries​

Type
Default value
Type
UInt64
Default value
2

Connection pool push/pop retries number for PostgreSQL table engine and database engine.

postgresql_connection_pool_size​

Type
Default value
Type
UInt64
Default value
16
Connection pool size for PostgreSQL table engine and database engine.

postgresql_connection_pool_wait_timeout​

Type
Default value
Type
UInt64
Default value
5000
Connection pool push/pop timeout on empty pool for PostgreSQL table engine and database engine. By default it will block on empty pool.

postgresql_fault_injection_probability​

Type
Default value
Type
Float
Default value
0

Approximate probability of failing internal (for replication) PostgreSQL queries. Valid value is in interval [0.0f, 1.0f]

prefer_column_name_to_alias​

Type
Default value
Type
Bool
Default value
0
Enables or disables using the original column names instead of aliases in query expressions and clauses. It especially matters when alias is the same as the column name, see Expression Aliases. Enable this setting to make aliases syntax rules in ClickHouse more compatible with most other database engines.

Possible values:

  • 0 — The column name is substituted with the alias.
  • 1 — The column name is not substituted with the alias.

Example

The difference between enabled and disabled:

Query:

Result:

Query:

Result:

prefer_external_sort_block_bytes​

Type
Default value
Type
UInt64
Default value
16744704

Prefer maximum block bytes for external sort, reduce the memory usage during merging.

prefer_global_in_and_join​

Type
Default value
Type
Bool
Default value
0
Enables the replacement of IN/JOIN operators with GLOBAL IN/GLOBAL JOIN.

Possible values:

  • 0 — Disabled. IN/JOIN operators are not replaced with GLOBAL IN/GLOBAL JOIN.
  • 1 — Enabled. IN/JOIN operators are replaced with GLOBAL IN/GLOBAL JOIN.

Usage

Although SET distributed_product_mode=global can change the queries behavior for the distributed tables, it's not suitable for local tables or tables from external resources. Here is when the prefer_global_in_and_join setting comes into play.

For example, we have query serving nodes that contain local tables, which are not suitable for distribution. We need to scatter their data on the fly during distributed processing with the GLOBAL keyword — GLOBAL IN/GLOBAL JOIN.

Another use case of prefer_global_in_and_join is accessing tables created by external engines. This setting helps to reduce the number of calls to external sources while joining such tables: only one call per query.

See also:

prefer_localhost_replica​

Type
Default value
Type
Bool
Default value
1
Enables/disables preferable using the localhost replica when processing distributed queries.

Possible values:

  • 1 — ClickHouse always sends a query to the localhost replica if it exists.
  • 0 — ClickHouse uses the balancing strategy specified by the load_balancing setting.
note

Disable this setting if you use max_parallel_replicas without parallel_replicas_custom_key. If parallel_replicas_custom_key is set, disable this setting only if it's used on a cluster with multiple shards containing multiple replicas. If it's used on a cluster with a single shard and multiple replicas, disabling this setting will have negative effects.

prefer_warmed_unmerged_parts_seconds​

ClickHouse Cloud only

Type
Default value
Type
Int64
Default value
0
Only has an effect in ClickHouse Cloud. If a merged part is less than this many seconds old and is not pre-warmed (see cache_populated_by_fetch), but all its source parts are available and pre-warmed, SELECT queries will read from those parts instead. Only for Replicated-/SharedMergeTree. Note that this only checks whether CacheWarmer processed the part; if the part was fetched into cache by something else, it'll still be considered cold until CacheWarmer gets to it; if it was warmed, then evicted from cache, it'll still be considered warm.

preferred_block_size_bytes​

Type
Default value
Type
UInt64
Default value
1000000
This setting adjusts the data block size for query processing and represents additional fine-tuning to the more rough 'max_block_size' setting. If the columns are large and with 'max_block_size' rows the block size is likely to be larger than the specified amount of bytes, its size will be lowered for better CPU cache locality.

preferred_max_column_in_block_size_bytes​

Type
Default value
Type
UInt64
Default value
0
Limit on max column size in block while reading. Helps to decrease cache misses count. Should be close to L2 cache size.

preferred_optimize_projection_name​

If it is set to a non-empty string, ClickHouse will try to apply specified projection in query.

Possible values:

  • string: name of preferred projectio

prefetch_buffer_size​

Type
Default value
Type
UInt64
Default value
1048576
The maximum size of the prefetch buffer to read from the filesystem.

Type
Default value
Type
Bool
Default value
1

Allows to print deep-nested type names in a pretty way with indents in DESCRIBE query and in toTypeName() function.

Example:

priority​

Type
Default value
Type
UInt64
Default value
0
Priority of the query. 1 - the highest, higher value - lower priority; 0 - do not use priorities.

push_external_roles_in_interserver_queries​

Type
Default value
Type
Bool
Default value
1

Enable pushing user roles from originator to other nodes while performing a query.

query_cache_compress_entries​

Type
Default value
Type
Bool
Default value
1
Compress entries in the query cache. Lessens the memory consumption of the query cache at the cost of slower inserts into / reads from it.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

query_cache_max_entries​

Type
Default value
Type
UInt64
Default value
0
The maximum number of query results the current user may store in the query cache. 0 means unlimited.

Possible values:

  • Positive integer >= 0.

query_cache_max_size_in_bytes​

Type
Default value
Type
UInt64
Default value
0
The maximum amount of memory (in bytes) the current user may allocate in the query cache. 0 means unlimited.

Possible values:

  • Positive integer >= 0.

query_cache_min_query_duration​

Type
Default value
Type
Milliseconds
Default value
0
Minimum duration in milliseconds a query needs to run for its result to be stored in the query cache.

Possible values:

  • Positive integer >= 0.

query_cache_min_query_runs​

Type
Default value
Type
UInt64
Default value
0
Minimum number of times a SELECT query must run before its result is stored in the query cache.

Possible values:

  • Positive integer >= 0.

query_cache_nondeterministic_function_handling​

Type
Default value
Type
QueryResultCacheNondeterministicFunctionHandling
Default value
throw
Controls how the query cache handles SELECT queries with non-deterministic functions like rand() or now().

Possible values:

  • 'throw' - Throw an exception and don't cache the query result.
  • 'save' - Cache the query result.
  • 'ignore' - Don't cache the query result and don't throw an exception.

query_cache_share_between_users​

Type
Default value
Type
Bool
Default value
0
If turned on, the result of SELECT queries cached in the query cache can be read by other users. It is not recommended to enable this setting due to security reasons.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

query_cache_squash_partial_results​

Type
Default value
Type
Bool
Default value
1
Squash partial result blocks to blocks of size max_block_size. Reduces performance of inserts into the query cache but improves the compressability of cache entries (see query_cache_compress-entries).

Possible values:

  • 0 - Disabled
  • 1 - Enabled

query_cache_system_table_handling​

Type
Default value
Type
QueryResultCacheSystemTableHandling
Default value
throw

Controls how the query cache handles SELECT queries against system tables, i.e. tables in databases system.* and information_schema.*.

Possible values:

  • 'throw' - Throw an exception and don't cache the query result.
  • 'save' - Cache the query result.
  • 'ignore' - Don't cache the query result and don't throw an exception.

query_cache_tag​

A string which acts as a label for query cache entries. The same queries with different tags are considered different by the query cache.

Possible values:

  • Any string

query_cache_ttl​

Type
Default value
Type
Seconds
Default value
60
After this time in seconds entries in the query cache become stale.

Possible values:

  • Positive integer >= 0.

query_condition_cache_store_conditions_as_plaintext​

Type
Default value
Type
Bool
Default value
0

Stores the filter condition for the query condition cache in plaintext. If enabled, system.query_condition_cache shows the verbatim filter condition which makes it easier to debug issues with the cache. Disabled by default because plaintext filter conditions may expose sensitive information.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

query_metric_log_interval​

Type
Default value
Type
Int64
Default value
-1

The interval in milliseconds at which the query_metric_log for individual queries is collected.

If set to any negative value, it will take the value collect_interval_milliseconds from the query_metric_log setting or default to 1000 if not present.

To disable the collection of a single query, set query_metric_log_interval to 0.

Default value: -1

query_plan_aggregation_in_order​

Type
Default value
Type
Bool
Default value
1

Toggles the aggregation in-order query-plan-level optimization. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_convert_join_to_in​

Type
Default value
Type
Bool
Default value
0

Allow to convert JOIN to subquery with IN if output columns tied to only left table

query_plan_convert_outer_join_to_inner_join​

Type
Default value
Type
Bool
Default value
1

Allow to convert OUTER JOIN to INNER JOIN if filter after JOIN always filters default values

query_plan_enable_multithreading_after_window_functions​

Type
Default value
Type
Bool
Default value
1
Enable multithreading after evaluating window functions to allow parallel stream processing

query_plan_enable_optimizations​

Type
Default value
Type
Bool
Default value
1
Toggles query optimization at the query plan level.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable all optimizations at the query plan level
  • 1 - Enable optimizations at the query plan level (but individual optimizations may still be disabled via their individual settings)

query_plan_execute_functions_after_sorting​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which moves expressions after sorting steps. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_filter_push_down​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which moves filters down in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_join_shard_by_pk_ranges​

Type
Default value
Type
Bool
Default value
0

Apply sharding for JOIN if join keys contain a prefix of PRIMARY KEY for both tables. Supported for hash, parallel_hash and full_sorting_merge algorithms

query_plan_join_swap_table​

Type
Default value
Type
BoolAuto
Default value
auto

Determine which side of the join should be the build table (also called inner, the one inserted into the hash table for a hash join) in the query plan. This setting is supported only for ALL join strictness with the JOIN ON clause. Possible values are:

  • 'auto': Let the planner decide which table to use as the build table.
  • 'false': Never swap tables (the right table is the build table).
  • 'true': Always swap tables (the left table is the build table).

query_plan_lift_up_array_join​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which moves ARRAY JOINs up in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_lift_up_union​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which moves larger subtrees of the query plan into union to enable further optimizations. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_max_limit_for_lazy_materialization​

Type
Default value
Type
UInt64
Default value
10

Control maximum limit value that allows to use query plan for lazy materialization optimization. If zero, there is no limit

query_plan_max_optimizations_to_apply​

Type
Default value
Type
UInt64
Default value
10000
Limits the total number of optimizations applied to query plan, see setting query_plan_enable_optimizations. Useful to avoid long optimization times for complex queries. In the EXPLAIN PLAN query, stop applying optimizations after this limit is reached and return the plan as is. For regular query execution if the actual number of optimizations exceeds this setting, an exception is thrown.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

query_plan_merge_expressions​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which merges consecutive filters. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_merge_filter_into_join_condition​

Type
Default value
Type
Bool
Default value
1

Allow to merge filter into JOIN condition and convert CROSS JOIN to INNER.

query_plan_merge_filters​

Type
Default value
Type
Bool
Default value
1

Allow to merge filters in the query pla

query_plan_optimize_lazy_materialization​

Type
Default value
Type
Bool
Default value
1

Use query plan for lazy materialization optimizatio

query_plan_optimize_prewhere​

Type
Default value
Type
Bool
Default value
1

Allow to push down filter to PREWHERE expression for supported storages

query_plan_push_down_limit​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which moves LIMITs down in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_read_in_order​

Type
Default value
Type
Bool
Default value
1
Toggles the read in-order optimization query-plan-level optimization. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_remove_redundant_distinct​

Type
Default value
Type
Bool
Default value
1

Toggles a query-plan-level optimization which removes redundant DISTINCT steps. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_remove_redundant_sorting​

Type
Default value
Type
Bool
Default value
1

Toggles a query-plan-level optimization which removes redundant sorting steps, e.g. in subqueries. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_reuse_storage_ordering_for_window_functions​

Type
Default value
Type
Bool
Default value
1
Toggles a query-plan-level optimization which uses storage sorting when sorting for window functions. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_split_filter​

Type
Default value
Type
Bool
Default value
1
:::note This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed. :::

Toggles a query-plan-level optimization which splits filters into expressions. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

  • 0 - Disable
  • 1 - Enable
Type
Default value
Type
Bool
Default value
1

Toggles a query-plan-level optimization which tries to use the vector similarity index. Only takes effect if setting query_plan_enable_optimizations is 1.

note

This is an expert-level setting which should only be used for debugging by developers. The setting may change in future in backward-incompatible ways or be removed.

Possible values:

  • 0 - Disable
  • 1 - Enable

query_plan_use_new_logical_join_step​

Type
Default value
Type
Bool
Default value
1

Use new logical join step in query pla

query_profiler_cpu_time_period_ns​

Type
Default value
Type
UInt64
Default value
1000000000
Sets the period for a CPU clock timer of the query profiler. This timer counts only CPU time.

Possible values:

  • A positive integer number of nanoseconds.

    Recommended values:

    • 10000000 (100 times a second) nanoseconds and more for single queries.
    • 1000000000 (once a second) for cluster-wide profiling.

  • 0 for turning off the timer.

Temporarily disabled in ClickHouse Cloud.

See also:

query_profiler_real_time_period_ns​

Type
Default value
Type
UInt64
Default value
1000000000
Sets the period for a real clock timer of the query profiler. Real clock timer counts wall-clock time.

Possible values:

  • Positive integer number, in nanoseconds.

    Recommended values:

    • 10000000 (100 times a second) nanoseconds and less for single queries.
    • 1000000000 (once a second) for cluster-wide profiling.

  • 0 for turning off the timer.

Temporarily disabled in ClickHouse Cloud.

See also:

queue_max_wait_ms​

Type
Default value
Type
Milliseconds
Default value
0
The wait time in the request queue, if the number of concurrent requests exceeds the maximum.

rabbitmq_max_wait_ms​

Type
Default value
Type
Milliseconds
Default value
5000
The wait time for reading from RabbitMQ before retry.

read_backoff_max_throughput​

Type
Default value
Type
UInt64
Default value
1048576
Settings to reduce the number of threads in case of slow reads. Count events when the read bandwidth is less than that many bytes per second.

read_backoff_min_concurrency​

Type
Default value
Type
UInt64
Default value
1
Settings to try keeping the minimal number of threads in case of slow reads.

read_backoff_min_events​

Type
Default value
Type
UInt64
Default value
2
Settings to reduce the number of threads in case of slow reads. The number of events after which the number of threads will be reduced.

read_backoff_min_interval_between_events_ms​

Type
Default value
Type
Milliseconds
Default value
1000
Settings to reduce the number of threads in case of slow reads. Do not pay attention to the event, if the previous one has passed less than a certain amount of time.

read_backoff_min_latency_ms​

Type
Default value
Type
Milliseconds
Default value
1000
Setting to reduce the number of threads in case of slow reads. Pay attention only to reads that took at least that much time.

read_from_filesystem_cache_if_exists_otherwise_bypass_cache​

Type
Default value
Type
Bool
Default value
0
Allow to use the filesystem cache in passive mode - benefit from the existing cache entries, but don't put more entries into the cache. If you set this setting for heavy ad-hoc queries and leave it disabled for short real-time queries, this will allows to avoid cache threshing by too heavy queries and to improve the overall system efficiency.

read_from_page_cache_if_exists_otherwise_bypass_cache​

Type
Default value
Type
Bool
Default value
0

Use userspace page cache in passive mode, similar to read_from_filesystem_cache_if_exists_otherwise_bypass_cache.

read_in_order_two_level_merge_threshold​

Type
Default value
Type
UInt64
Default value
100
Minimal number of parts to read to run preliminary merge step during multithread reading in order of primary key.

read_in_order_use_buffering​

Type
Default value
Type
Bool
Default value
1

Use buffering before merging while reading in order of primary key. It increases the parallelism of query executio

read_in_order_use_virtual_row​

Type
Default value
Type
Bool
Default value
0

Use virtual row while reading in order of primary key or its monotonic function fashion. It is useful when searching over multiple parts as only relevant ones are touched.

read_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
What to do when the limit is exceeded.

read_overflow_mode_leaf​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens when the volume of data read exceeds one of the leaf limits.

Possible options:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result.

read_priority​

Type
Default value
Type
Int64
Default value
0
Priority to read data from local filesystem or remote filesystem. Only supported for 'pread_threadpool' method for local filesystem and for threadpool method for remote filesystem.

read_through_distributed_cache​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
0

Only has an effect in ClickHouse Cloud. Allow reading from distributed cache

readonly​

Type
Default value
Type
UInt64
Default value
0
0 - no read-only restrictions. 1 - only read requests, as well as changing explicitly allowed settings. 2 - only read requests, as well as changing settings, except for the 'readonly' setting.

receive_data_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
2000
Connection timeout for receiving first packet of data or packet with positive progress from replica

receive_timeout​

Type
Default value
Type
Seconds
Default value
300
Timeout for receiving data from the network, in seconds. If no bytes were received in this interval, the exception is thrown. If you set this setting on the client, the 'send_timeout' for the socket will also be set on the corresponding connection end on the server.

regexp_max_matches_per_row​

Type
Default value
Type
UInt64
Default value
1000
Sets the maximum number of matches for a single regular expression per row. Use it to protect against memory overload when using greedy regular expression in the extractAllGroupsHorizontal function.

Possible values:

  • Positive integer.

reject_expensive_hyperscan_regexps​

Type
Default value
Type
Bool
Default value
1
Reject patterns which will likely be expensive to evaluate with hyperscan (due to NFA state explosion)

remerge_sort_lowered_memory_bytes_ratio​

Type
Default value
Type
Float
Default value
2
If memory usage after remerge does not reduced by this ratio, remerge will be disabled.

remote_filesystem_read_method​

Type
Default value
Type
String
Default value
threadpool
Method of reading data from remote filesystem, one of: read, threadpool.

remote_filesystem_read_prefetch​

Type
Default value
Type
Bool
Default value
1
Should use prefetching when reading data from remote filesystem.

remote_fs_read_backoff_max_tries​

Type
Default value
Type
UInt64
Default value
5
Max attempts to read with backoff

remote_fs_read_max_backoff_ms​

Type
Default value
Type
UInt64
Default value
10000
Max wait time when trying to read data for remote disk

remote_read_min_bytes_for_seek​

Type
Default value
Type
UInt64
Default value
4194304
Min bytes required for remote read (url, s3) to do seek, instead of read with ignore.

rename_files_after_processing​

  • Type: String

  • Default value: Empty string

This setting allows to specify renaming pattern for files processed by file table function. When option is set, all files read by file table function will be renamed according to specified pattern with placeholders, only if files processing was successful.

Placeholders​

  • %a — Full original filename (e.g., "sample.csv").
  • %f — Original filename without extension (e.g., "sample").
  • %e — Original file extension with dot (e.g., ".csv").
  • %t — Timestamp (in microseconds).
  • %% — Percentage sign ("%").

Example​

  • Option: --rename_files_after_processing="processed_%f_%t%e"

  • Query: SELECT * FROM file('sample.csv')

If reading sample.csv is successful, file will be renamed to processed_sample_1683473210851438.csv

replace_running_query​

Type
Default value
Type
Bool
Default value
0
When using the HTTP interface, the 'query_id' parameter can be passed. This is any string that serves as the query identifier. If a query from the same user with the same 'query_id' already exists at this time, the behaviour depends on the 'replace_running_query' parameter.

0 (default) – Throw an exception (do not allow the query to run if a query with the same 'query_id' is already running).

1 – Cancel the old query and start running the new one.

Set this parameter to 1 for implementing suggestions for segmentation conditions. After entering the next character, if the old query hasn't finished yet, it should be cancelled.

replace_running_query_max_wait_ms​

Type
Default value
Type
Milliseconds
Default value
5000
The wait time for running the query with the same query_id to finish, when the replace_running_query setting is active.

Possible values:

  • Positive integer.
  • 0 — Throwing an exception that does not allow to run a new query if the server already executes a query with the same query_id.

replication_wait_for_inactive_replica_timeout​

Type
Default value
Type
Int64
Default value
120
Specifies how long (in seconds) to wait for inactive replicas to execute ALTER, OPTIMIZE or TRUNCATE queries.

Possible values:

  • 0 — Do not wait.
  • Negative integer — Wait for unlimited time.
  • Positive integer — The number of seconds to wait.

restore_replace_external_dictionary_source_to_null​

Type
Default value
Type
Bool
Default value
0

Replace external dictionary sources to Null on restore. Useful for testing purposes

restore_replace_external_engines_to_null​

Type
Default value
Type
Bool
Default value
0

For testing purposes. Replaces all external engines to Null to not initiate external connections.

restore_replace_external_table_functions_to_null​

Type
Default value
Type
Bool
Default value
0

For testing purposes. Replaces all external table functions to Null to not initiate external connections.

restore_replicated_merge_tree_to_shared_merge_tree​

Type
Default value
Type
Bool
Default value
0

Replace table engine from ReplicatedMergeTree -> SharedMergeTree during RESTORE.

result_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Cloud default value: throw

Sets what to do if the volume of the result exceeds one of the limits.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

Using 'break' is similar to using LIMIT. Break interrupts execution only at the block level. This means that amount of returned rows is greater than max_result_rows, multiple of max_block_size and depends on max_threads.

Example

rewrite_count_distinct_if_with_count_distinct_implementation​

Type
Default value
Type
Bool
Default value
0

Allows you to rewrite countDistcintIf with count_distinct_implementation setting.

Possible values:

  • true — Allow.
  • false — Disallow.

s3_allow_multipart_copy​

Type
Default value
Type
Bool
Default value
1

Allow multipart copy in S3.

s3_allow_parallel_part_upload​

Type
Default value
Type
Bool
Default value
1
Use multiple threads for s3 multipart upload. It may lead to slightly higher memory usage

s3_check_objects_after_upload​

Type
Default value
Type
Bool
Default value
0
Check each uploaded object to s3 with head request to be sure that upload was successful

s3_connect_timeout_ms​

Type
Default value
Type
UInt64
Default value
1000

Connection timeout for host from s3 disks.

s3_create_new_file_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables creating a new file on each insert in s3 engine tables. If enabled, on each insert a new S3 object will be created with the key, similar to this pattern:

initial: data.Parquet.gz -> data.1.Parquet.gz -> data.2.Parquet.gz, etc.

Possible values:

  • 0 — INSERT query creates a new file or fail if file exists and s3_truncate_on_insert is not set.
  • 1 — INSERT query creates a new file on each insert using suffix (from the second one) if s3_truncate_on_insert is not set.

See more details here.

s3_disable_checksum​

Type
Default value
Type
Bool
Default value
0
Do not calculate a checksum when sending a file to S3. This speeds up writes by avoiding excessive processing passes on a file. It is mostly safe as the data of MergeTree tables is checksummed by ClickHouse anyway, and when S3 is accessed with HTTPS, the TLS layer already provides integrity while transferring through the network. While additional checksums on S3 give defense in depth.

s3_ignore_file_doesnt_exist​

Type
Default value
Type
Bool
Default value
0

Ignore absence of file if it does not exist when reading certain keys.

Possible values:

  • 1 — SELECT returns empty result.
  • 0 — SELECT throws an exception.

s3_list_object_keys_size​

Type
Default value
Type
UInt64
Default value
1000
Maximum number of files that could be returned in batch by ListObject request

s3_max_connections​

Type
Default value
Type
UInt64
Default value
1024
The maximum number of connections per server.

s3_max_get_burst​

Type
Default value
Type
UInt64
Default value
0
Max number of requests that can be issued simultaneously before hitting request per second limit. By default (0) equals to s3_max_get_rps

s3_max_get_rps​

Type
Default value
Type
UInt64
Default value
0
Limit on S3 GET request per second rate before throttling. Zero means unlimited.

s3_max_inflight_parts_for_one_file​

Type
Default value
Type
UInt64
Default value
20
The maximum number of a concurrent loaded parts in multipart upload request. 0 means unlimited.

s3_max_part_number​

Type
Default value
Type
UInt64
Default value
10000

Maximum part number number for s3 upload part.

s3_max_put_burst​

Type
Default value
Type
UInt64
Default value
0
Max number of requests that can be issued simultaneously before hitting request per second limit. By default (0) equals to s3_max_put_rps

s3_max_put_rps​

Type
Default value
Type
UInt64
Default value
0
Limit on S3 PUT request per second rate before throttling. Zero means unlimited.

s3_max_redirects​

Type
Default value
Type
UInt64
Default value
10
Max number of S3 redirects hops allowed.

s3_max_single_operation_copy_size​

Type
Default value
Type
UInt64
Default value
33554432

Maximum size for single-operation copy in s3. This setting is used only if s3_allow_multipart_copy is true.

s3_max_single_part_upload_size​

Type
Default value
Type
UInt64
Default value
33554432
The maximum size of object to upload using singlepart upload to S3.

s3_max_single_read_retries​

Type
Default value
Type
UInt64
Default value
4
The maximum number of retries during single S3 read.

s3_max_unexpected_write_error_retries​

Type
Default value
Type
UInt64
Default value
4
The maximum number of retries in case of unexpected errors during S3 write.

s3_max_upload_part_size​

Type
Default value
Type
UInt64
Default value
5368709120
The maximum size of part to upload during multipart upload to S3.

s3_min_upload_part_size​

Type
Default value
Type
UInt64
Default value
16777216
The minimum size of part to upload during multipart upload to S3.

s3_request_timeout_ms​

Type
Default value
Type
UInt64
Default value
30000
Idleness timeout for sending and receiving data to/from S3. Fail if a single TCP read or write call blocks for this long.

s3_retry_attempts​

Type
Default value
Type
UInt64
Default value
100
Setting for Aws::Client::RetryStrategy, Aws::Client does retries itself, 0 means no retries

s3_skip_empty_files​

Type
Default value
Type
Bool
Default value
1

Enables or disables skipping empty files in S3 engine tables.

Possible values:

  • 0 — SELECT throws an exception if empty file is not compatible with requested format.
  • 1 — SELECT returns empty result for empty file.

s3_strict_upload_part_size​

Type
Default value
Type
UInt64
Default value
0
The exact size of part to upload during multipart upload to S3 (some implementations does not supports variable size parts).

s3_throw_on_zero_files_match​

Type
Default value
Type
Bool
Default value
0
Throw an error, when ListObjects request cannot match any files

s3_truncate_on_insert​

Type
Default value
Type
Bool
Default value
0
Enables or disables truncate before inserts in s3 engine tables. If disabled, an exception will be thrown on insert attempts if an S3 object already exists.

Possible values:

  • 0 — INSERT query creates a new file or fail if file exists and s3_create_new_file_on_insert is not set.
  • 1 — INSERT query replaces existing content of the file with the new data.

See more details here.

s3_upload_part_size_multiply_factor​

Type
Default value
Type
UInt64
Default value
2
Multiply s3_min_upload_part_size by this factor each time s3_multiply_parts_count_threshold parts were uploaded from a single write to S3.

s3_upload_part_size_multiply_parts_count_threshold​

Type
Default value
Type
UInt64
Default value
500
Each time this number of parts was uploaded to S3, s3_min_upload_part_size is multiplied by s3_upload_part_size_multiply_factor.

s3_use_adaptive_timeouts​

Type
Default value
Type
Bool
Default value
1
When set to true than for all s3 requests first two attempts are made with low send and receive timeouts. When set to false than all attempts are made with identical timeouts.

s3_validate_request_settings​

Type
Default value
Type
Bool
Default value
1

Enables s3 request settings validation.

Possible values:

  • 1 — validate settings.
  • 0 — do not validate settings.

s3queue_default_zookeeper_path​

Type
Default value
Type
String
Default value
/clickhouse/s3queue/
Default zookeeper path prefix for S3Queue engine

s3queue_enable_logging_to_s3queue_log​

Type
Default value
Type
Bool
Default value
0
Enable writing to system.s3queue_log. The value can be overwritten per table with table settings

s3queue_migrate_old_metadata_to_buckets​

Type
Default value
Type
Bool
Default value
0

Migrate old metadata structure of S3Queue table to a new one

schema_inference_cache_require_modification_time_for_url​

Type
Default value
Type
Bool
Default value
1
Use schema from cache for URL with last modification time validation (for URLs with Last-Modified header)

schema_inference_use_cache_for_azure​

Type
Default value
Type
Bool
Default value
1
Use cache in schema inference while using azure table functio

schema_inference_use_cache_for_file​

Type
Default value
Type
Bool
Default value
1
Use cache in schema inference while using file table functio

schema_inference_use_cache_for_hdfs​

Type
Default value
Type
Bool
Default value
1
Use cache in schema inference while using hdfs table functio

schema_inference_use_cache_for_s3​

Type
Default value
Type
Bool
Default value
1
Use cache in schema inference while using s3 table functio

schema_inference_use_cache_for_url​

Type
Default value
Type
Bool
Default value
1
Use cache in schema inference while using url table functio

secondary_indices_enable_bulk_filtering​

Type
Default value
Type
Bool
Default value
1

Enable the bulk filtering algorithm for indices. It is expected to be always better, but we have this setting for compatibility and control.

select_sequential_consistency​

Type
Default value
Type
UInt64
Default value
0
:::note This setting differ in behavior between SharedMergeTree and ReplicatedMergeTree, see SharedMergeTree consistency for more information about the behavior of select_sequential_consistency in SharedMergeTree. :::

Enables or disables sequential consistency for SELECT queries. Requires insert_quorum_parallel to be disabled (enabled by default).

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Usage

When sequential consistency is enabled, ClickHouse allows the client to execute the SELECT query only for those replicas that contain data from all previous INSERT queries executed with insert_quorum. If the client refers to a partial replica, ClickHouse will generate an exception. The SELECT query will not include data that has not yet been written to the quorum of replicas.

When insert_quorum_parallel is enabled (the default), then select_sequential_consistency does not work. This is because parallel INSERT queries can be written to different sets of quorum replicas so there is no guarantee a single replica will have received all writes.

See also:

send_logs_level​

Type
Default value
Type
LogsLevel
Default value
fatal
Send server text logs with specified minimum level to client. Valid values: 'trace', 'debug', 'information', 'warning', 'error', 'fatal', 'none'

send_logs_source_regexp​

Send server text logs with specified regexp to match log source name. Empty means all sources.

send_progress_in_http_headers​

Type
Default value
Type
Bool
Default value
0
Enables or disables X-ClickHouse-Progress HTTP response headers in clickhouse-server responses.

For more information, read the HTTP interface description.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

send_timeout​

Type
Default value
Type
Seconds
Default value
300
Timeout for sending data to the network, in seconds. If a client needs to send some data but is not able to send any bytes in this interval, the exception is thrown. If you set this setting on the client, the 'receive_timeout' for the socket will also be set on the corresponding connection end on the server.

serialize_query_plan​

Type
Default value
Type
Bool
Default value
0

Serialize query plan for distributed processing

session_timezone​

Beta feature. Learn more.

Sets the implicit time zone of the current session or query. The implicit time zone is the time zone applied to values of type DateTime/DateTime64 which have no explicitly specified time zone. The setting takes precedence over the globally configured (server-level) implicit time zone. A value of '' (empty string) means that the implicit time zone of the current session or query is equal to the server time zone.

You can use functions timeZone() and serverTimeZone() to get the session time zone and server time zone.

Possible values:

  • Any time zone name from system.time_zones, e.g. Europe/Berlin, UTC or Zulu

Examples:

Assign session time zone 'America/Denver' to the inner DateTime without explicitly specified time zone:

danger

Not all functions that parse DateTime/DateTime64 respect session_timezone. This can lead to subtle errors. See the following example and explanation.

This happens due to different parsing pipelines:

  • toDateTime() without explicitly given time zone used in the first SELECT query honors setting session_timezone and the global time zone.
  • In the second query, a DateTime is parsed from a String, and inherits the type and time zone of the existing columnd. Thus, setting session_timezone and the global time zone are not honored.

See also

set_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens when the amount of data exceeds one of the limits.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

shared_merge_tree_sync_parts_on_partition_operations​

Type
Default value
Type
Bool
Default value
1

Automatically synchronize set of data parts after MOVE|REPLACE|ATTACH partition operations in SMT tables. Cloud only

short_circuit_function_evaluation​

Type
Default value
Type
ShortCircuitFunctionEvaluation
Default value
enable
Allows calculating the if, multiIf, and, and or functions according to a short scheme. This helps optimize the execution of complex expressions in these functions and prevent possible exceptions (such as division by zero when it is not expected).

Possible values:

  • enable — Enables short-circuit function evaluation for functions that are suitable for it (can throw an exception or computationally heavy).
  • force_enable — Enables short-circuit function evaluation for all functions.
  • disable — Disables short-circuit function evaluation.

short_circuit_function_evaluation_for_nulls​

Type
Default value
Type
Bool
Default value
1

Optimizes evaluation of functions that return NULL when any argument is NULL. When the percentage of NULL values in the function's arguments exceeds the short_circuit_function_evaluation_for_nulls_threshold, the system skips evaluating the function row-by-row. Instead, it immediately returns NULL for all rows, avoiding unnecessary computation.

short_circuit_function_evaluation_for_nulls_threshold​

Type
Default value
Type
Double
Default value
1

Ratio threshold of NULL values to execute functions with Nullable arguments only on rows with non-NULL values in all arguments. Applies when setting short_circuit_function_evaluation_for_nulls is enabled. When the ratio of rows containing NULL values to the total number of rows exceeds this threshold, these rows containing NULL values will not be evaluated.

show_table_uuid_in_table_create_query_if_not_nil​

Type
Default value
Type
Bool
Default value
0

Sets the SHOW TABLE query display.

Possible values:

  • 0 — The query will be displayed without table UUID.
  • 1 — The query will be displayed with table UUID.

single_join_prefer_left_table​

Type
Default value
Type
Bool
Default value
1
For single JOIN in case of identifier ambiguity prefer left table

skip_redundant_aliases_in_udf​

Type
Default value
Type
Bool
Default value
0

Redundant aliases are not used (substituted) in user-defined functions in order to simplify it's usage.

Possible values:

  • 1 — The aliases are skipped (substituted) in UDFs.
  • 0 — The aliases are not skipped (substituted) in UDFs.

Example

The difference between enabled and disabled:

Query:

Result:

Query:

Result:

skip_unavailable_shards​

Type
Default value
Type
Bool
Default value
0
Enables or disables silently skipping of unavailable shards.

Shard is considered unavailable if all its replicas are unavailable. A replica is unavailable in the following cases:

  • ClickHouse can't connect to replica for any reason.

    When connecting to a replica, ClickHouse performs several attempts. If all these attempts fail, the replica is considered unavailable.

  • Replica can't be resolved through DNS.

    If replica's hostname can't be resolved through DNS, it can indicate the following situations:

    • Replica's host has no DNS record. It can occur in systems with dynamic DNS, for example, Kubernetes, where nodes can be unresolvable during downtime, and this is not an error.

    • Configuration error. ClickHouse configuration file contains a wrong hostname.

Possible values:

  • 1 — skipping enabled.

    If a shard is unavailable, ClickHouse returns a result based on partial data and does not report node availability issues.

  • 0 — skipping disabled.

    If a shard is unavailable, ClickHouse throws an exception.

sleep_after_receiving_query_ms​

Type
Default value
Type
Milliseconds
Default value
0
Time to sleep after receiving query in TCPHandler

sleep_in_send_data_ms​

Type
Default value
Type
Milliseconds
Default value
0
Time to sleep in sending data in TCPHandler

sleep_in_send_tables_status_ms​

Type
Default value
Type
Milliseconds
Default value
0
Time to sleep in sending tables status response in TCPHandler

sort_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens if the number of rows received before sorting exceeds one of the limits.

Possible values:

  • throw: throw an exception.
  • break: stop executing the query and return the partial result.

split_intersecting_parts_ranges_into_layers_final​

Type
Default value
Type
Bool
Default value
1

Split intersecting parts ranges into layers during FINAL optimizatio

split_parts_ranges_into_intersecting_and_non_intersecting_final​

Type
Default value
Type
Bool
Default value
1

Split parts ranges into intersecting and non intersecting during FINAL optimizatio

splitby_max_substrings_includes_remaining_string​

Type
Default value
Type
Bool
Default value
0
Controls whether function splitBy*() with argument max_substrings > 0 will include the remaining string in the last element of the result array.

Possible values:

  • 0 - The remaining string will not be included in the last element of the result array.
  • 1 - The remaining string will be included in the last element of the result array. This is the behavior of Spark's split() function and Python's 'string.split()' method.

stop_refreshable_materialized_views_on_startup​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
0
On server startup, prevent scheduling of refreshable materialized views, as if with SYSTEM STOP VIEWS. You can manually start them with SYSTEM START VIEWS or SYSTEM START VIEW <name> afterwards. Also applies to newly created views. Has no effect on non-refreshable materialized views.

storage_file_read_method​

Type
Default value
Type
LocalFSReadMethod
Default value
pread
Method of reading data from storage file, one of: read, pread, mmap. The mmap method does not apply to clickhouse-server (it's intended for clickhouse-local).

storage_system_stack_trace_pipe_read_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
100
Maximum time to read from a pipe for receiving information from the threads when querying the system.stack_trace table. This setting is used for testing purposes and not meant to be changed by users.

stream_flush_interval_ms​

Type
Default value
Type
Milliseconds
Default value
7500
Works for tables with streaming in the case of a timeout, or when a thread generates max_insert_block_size rows.

The default value is 7500.

The smaller the value, the more often data is flushed into the table. Setting the value too low leads to poor performance.

stream_like_engine_allow_direct_select​

Type
Default value
Type
Bool
Default value
0

Allow direct SELECT query for Kafka, RabbitMQ, FileLog, Redis Streams, and NATS engines. In case there are attached materialized views, SELECT query is not allowed even if this setting is enabled.

stream_like_engine_insert_queue​

When stream-like engine reads from multiple queues, the user will need to select one queue to insert into when writing. Used by Redis Streams and NATS.

stream_poll_timeout_ms​

Type
Default value
Type
Milliseconds
Default value
500
Timeout for polling data from/to streaming storages.

system_events_show_zero_values​

Type
Default value
Type
Bool
Default value
0
Allows to select zero-valued events from system.events.

Some monitoring systems require passing all the metrics values to them for each checkpoint, even if the metric value is zero.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

Examples

Query

Result

Query

Result

table_function_remote_max_addresses​

Type
Default value
Type
UInt64
Default value
1000
Sets the maximum number of addresses generated from patterns for the remote function.

Possible values:

  • Positive integer.

tcp_keep_alive_timeout​

Type
Default value
Type
Seconds
Default value
290
The time in seconds the connection needs to remain idle before TCP starts sending keepalive probes

temporary_data_in_cache_reserve_space_wait_lock_timeout_milliseconds​

Type
Default value
Type
UInt64
Default value
600000

Wait time to lock cache for space reservation for temporary data in filesystem cache

temporary_files_codec​

Type
Default value
Type
String
Default value
LZ4
Sets compression codec for temporary files used in sorting and joining operations on disk.

Possible values:

  • LZ4 — LZ4 compression is applied.
  • NONE — No compression is applied.

throw_if_deduplication_in_dependent_materialized_views_enabled_with_async_insert​

Type
Default value
Type
Bool
Default value
1

Throw exception on INSERT query when the setting deduplicate_blocks_in_dependent_materialized_views is enabled along with async_insert. It guarantees correctness, because these features can't work together.

throw_if_no_data_to_insert​

Type
Default value
Type
Bool
Default value
1
Allows or forbids empty INSERTs, enabled by default (throws an error on an empty insert). Only applies to INSERTs using clickhouse-client or using the gRPC interface.

throw_on_error_from_cache_on_write_operations​

Type
Default value
Type
Bool
Default value
0
Ignore error from cache when caching on write operations (INSERT, merges)

throw_on_max_partitions_per_insert_block​

Type
Default value
Type
Bool
Default value
1
Allows you to control the behaviour when max_partitions_per_insert_block is reached.

Possible values:

  • true - When an insert block reaches max_partitions_per_insert_block, an exception is raised.
  • false - Logs a warning when max_partitions_per_insert_block is reached.
tip

This can be useful if you're trying to understand the impact on users when changing max_partitions_per_insert_block.

throw_on_unsupported_query_inside_transaction​

Experimental feature. Learn more.

Type
Default value
Type
Bool
Default value
1
Throw exception if unsupported query is used inside transactio

timeout_before_checking_execution_speed​

Type
Default value
Type
Seconds
Default value
10
Checks that execution speed is not too slow (no less than min_execution_speed), after the specified time in seconds has expired.

timeout_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what to do if the query is run longer than the max_execution_time or the estimated running time is longer than max_estimated_execution_time.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

timeout_overflow_mode_leaf​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens when the query in leaf node run longer than max_execution_time_leaf.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

totals_auto_threshold​

Type
Default value
Type
Float
Default value
0.5
The threshold for totals_mode = 'auto'. See the section "WITH TOTALS modifier".

totals_mode​

Type
Default value
Type
TotalsMode
Default value
after_having_exclusive
How to calculate TOTALS when HAVING is present, as well as when max_rows_to_group_by and group_by_overflow_mode = 'any' are present. See the section "WITH TOTALS modifier".

trace_profile_events​

Type
Default value
Type
Bool
Default value
0
Enables or disables collecting stacktraces on each update of profile events along with the name of profile event and the value of increment and sending them into trace_log.

Possible values:

  • 1 — Tracing of profile events enabled.
  • 0 — Tracing of profile events disabled.

transfer_overflow_mode​

Type
Default value
Type
OverflowMode
Default value
throw
Sets what happens when the amount of data exceeds one of the limits.

Possible values:

  • throw: throw an exception (default).
  • break: stop executing the query and return the partial result, as if the source data ran out.

transform_null_in​

Type
Default value
Type
Bool
Default value
0
Enables equality of NULL values for IN operator.

By default, NULL values can't be compared because NULL means undefined value. Thus, comparison expr = NULL must always return false. With this setting NULL = NULL returns true for IN operator.

Possible values:

  • 0 — Comparison of NULL values in IN operator returns false.
  • 1 — Comparison of NULL values in IN operator returns true.

Example

Consider the null_in table:

Query:

Result:

Query:

Result:

See Also

traverse_shadow_remote_data_paths​

Type
Default value
Type
Bool
Default value
0

Traverse frozen data (shadow directory) in addition to actual table data when query system.remote_data_paths

union_default_mode​

Sets a mode for combining SELECT query results. The setting is only used when shared with UNION without explicitly specifying the UNION ALL or UNION DISTINCT.

Possible values:

  • 'DISTINCT' — ClickHouse outputs rows as a result of combining queries removing duplicate rows.
  • 'ALL' — ClickHouse outputs all rows as a result of combining queries including duplicate rows.
  • '' — ClickHouse generates an exception when used with UNION.

See examples in UNION.

unknown_packet_in_send_data​

Type
Default value
Type
UInt64
Default value
0
Send unknown packet instead of data Nth data packet

use_async_executor_for_materialized_views​

Type
Default value
Type
Bool
Default value
0

Use async and potentially multithreaded execution of materialized view query, can speedup views processing during INSERT, but also consume more memory.

use_cache_for_count_from_files​

Type
Default value
Type
Bool
Default value
1
Enables caching of rows number during count from files in table functions file/s3/url/hdfs/azureBlobStorage.

Enabled by default.

use_client_time_zone​

Type
Default value
Type
Bool
Default value
0
Use client timezone for interpreting DateTime string values, instead of adopting server timezone.

use_compact_format_in_distributed_parts_names​

Type
Default value
Type
Bool
Default value
1

Uses compact format for storing blocks for background (distributed_foreground_insert) INSERT into tables with Distributed engine.

Possible values:

  • 0 — Uses user[:password]@host:port#default_database directory format.
  • 1 — Uses [shard{shard_index}[_replica{replica_index}]] directory format.
note
  • with use_compact_format_in_distributed_parts_names=0 changes from cluster definition will not be applied for background INSERT.
  • with use_compact_format_in_distributed_parts_names=1 changing the order of the nodes in the cluster definition, will change the shard_index/replica_index so be aware.

use_concurrency_control​

Type
Default value
Type
Bool
Default value
1

Respect the server's concurrency control (see the concurrent_threads_soft_limit_num and concurrent_threads_soft_limit_ratio_to_cores global server settings). If disabled, it allows using a larger number of threads even if the server is overloaded (not recommended for normal usage, and needed mostly for tests).

use_hedged_requests​

Type
Default value
Type
Bool
Default value
1

Enables hedged requests logic for remote queries. It allows to establish many connections with different replicas for query. New connection is enabled in case existent connection(s) with replica(s) were not established within hedged_connection_timeout or no data was received within receive_data_timeout. Query uses the first connection which send non empty progress packet (or data packet, if allow_changing_replica_until_first_data_packet); other connections are cancelled. Queries with max_parallel_replicas > 1 are supported.

Enabled by default.

Disabled by default on Cloud.

use_hive_partitioning​

Type
Default value
Type
Bool
Default value
1

When enabled, ClickHouse will detect Hive-style partitioning in path (/name=value/) in file-like table engines File/S3/URL/HDFS/AzureBlobStorage and will allow to use partition columns as virtual columns in the query. These virtual columns will have the same names as in the partitioned path, but starting with _.

use_iceberg_metadata_files_cache​

Type
Default value
Type
Bool
Default value
1

If turned on, iceberg table function and iceberg storage may utilize the iceberg metadata files cache.

use_iceberg_partition_pruning​

Type
Default value
Type
Bool
Default value
0

Use Iceberg partition pruning for Iceberg tables

use_index_for_in_with_subqueries​

Type
Default value
Type
Bool
Default value
1
Try using an index if there is a subquery or a table expression on the right side of the IN operator.

use_index_for_in_with_subqueries_max_values​

Type
Default value
Type
UInt64
Default value
0
The maximum size of the set in the right-hand side of the IN operator to use table index for filtering. It allows to avoid performance degradation and higher memory usage due to the preparation of additional data structures for large queries. Zero means no limit.

use_json_alias_for_old_object_type​

Type
Default value
Type
Bool
Default value
0

When enabled, JSON data type alias will be used to create an old Object('json') type instead of the new JSON type.

use_page_cache_for_disks_without_file_cache​

Type
Default value
Type
Bool
Default value
0

Use userspace page cache for remote disks that don't have filesystem cache enabled.

use_page_cache_with_distributed_cache​

Type
Default value
Type
Bool
Default value
0

Use userspace page cache when distributed cache is used.

use_query_cache​

Type
Default value
Type
Bool
Default value
0
If turned on, SELECT queries may utilize the query cache. Parameters enable_reads_from_query_cache and enable_writes_to_query_cache control in more detail how the cache is used.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

use_query_condition_cache​

Type
Default value
Type
Bool
Default value
1

Enable the query condition cache. The cache stores ranges of granules in data parts which do not satisfy the condition in the WHERE clause, and reuse this information as an ephemeral index for subsequent queries.

Possible values:

  • 0 - Disabled
  • 1 - Enabled

use_skip_indexes​

Type
Default value
Type
Bool
Default value
1
Use data skipping indexes during query execution.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

use_skip_indexes_if_final​

Type
Default value
Type
Bool
Default value
0
Controls whether skipping indexes are used when executing a query with the FINAL modifier.

By default, this setting is disabled because skip indexes may exclude rows (granules) containing the latest data, which could lead to incorrect results. When enabled, skipping indexes are applied even with the FINAL modifier, potentially improving performance but with the risk of missing recent updates.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

use_skip_indexes_if_final_exact_mode​

Type
Default value
Type
Bool
Default value
0

Controls whether granules returned by a skipping index are expanded in newer parts to return correct results when executing a query with the FINAL modifier.

Using skip indexes may exclude rows (granules) containing the latest data which could lead to incorrect results. This setting can ensure that correct results are returned by scanning newer parts that have overlap with the ranges returned by the skip index.

Possible values:

  • 0 — Disabled.
  • 1 — Enabled.

use_structure_from_insertion_table_in_table_functions​

Type
Default value
Type
UInt64
Default value
2

Use structure from insertion table instead of schema inference from data. Possible values: 0 - disabled, 1 - enabled, 2 - auto

use_uncompressed_cache​

Type
Default value
Type
Bool
Default value
0
Whether to use a cache of uncompressed blocks. Accepts 0 or 1. By default, 0 (disabled). Using the uncompressed cache (only for tables in the MergeTree family) can significantly reduce latency and increase throughput when working with a large number of short queries. Enable this setting for users who send frequent short requests. Also pay attention to the uncompressed_cache_size configuration parameter (only set in the config file) – the size of uncompressed cache blocks. By default, it is 8 GiB. The uncompressed cache is filled in as needed and the least-used data is automatically deleted.

For queries that read at least a somewhat large volume of data (one million rows or more), the uncompressed cache is disabled automatically to save space for truly small queries. This means that you can keep the 'use_uncompressed_cache' setting always set to 1.

use_variant_as_common_type​

Type
Default value
Type
Bool
Default value
0

Allows to use Variant type as a result type for if/multiIf/array/map functions when there is no common type for argument types.

Example:

use_with_fill_by_sorting_prefix​

Type
Default value
Type
Bool
Default value
1

Columns preceding WITH FILL columns in ORDER BY clause form sorting prefix. Rows with different values in sorting prefix are filled independently

validate_enum_literals_in_operators​

Type
Default value
Type
Bool
Default value
0

If enabled, validate enum literals in operators like IN, NOT IN, ==, != against the enum type and throw an exception if the literal is not a valid enum value.

validate_mutation_query​

Type
Default value
Type
Bool
Default value
1

Validate mutation queries before accepting them. Mutations are executed in the background, and running an invalid query will cause mutations to get stuck, requiring manual intervention.

Only change this setting if you encounter a backward-incompatible bug.

validate_polygons​

Type
Default value
Type
Bool
Default value
1

Enables or disables throwing an exception in the pointInPolygon function, if the polygon is self-intersecting or self-tangent.

Possible values:

  • 0 — Throwing an exception is disabled. pointInPolygon accepts invalid polygons and returns possibly incorrect results for them.
  • 1 — Throwing an exception is enabled.

wait_changes_become_visible_after_commit_mode​

Experimental feature. Learn more.

Type
Default value
Type
TransactionsWaitCSNMode
Default value
wait_unknown
Wait for committed changes to become actually visible in the latest snapshot

wait_for_async_insert​

Type
Default value
Type
Bool
Default value
1
If true wait for processing of asynchronous insertio

wait_for_async_insert_timeout​

Type
Default value
Type
Seconds
Default value
120
Timeout for waiting for processing asynchronous insertio

wait_for_window_view_fire_signal_timeout​

Experimental feature. Learn more.

Type
Default value
Type
Seconds
Default value
10
Timeout for waiting for window view fire signal in event time processing

window_view_clean_interval​

Experimental feature. Learn more.

Type
Default value
Type
Seconds
Default value
60
The clean interval of window view in seconds to free outdated data.

window_view_heartbeat_interval​

Experimental feature. Learn more.

Type
Default value
Type
Seconds
Default value
15
The heartbeat interval in seconds to indicate watch query is alive.

workload​

Type
Default value
Type
String
Default value
default
Name of workload to be used to access resources

write_through_distributed_cache​

ClickHouse Cloud only
Type
Default value
Type
Bool
Default value
0

Only has an effect in ClickHouse Cloud. Allow writing to distributed cache (writing to s3 will also be done by distributed cache)

zstd_window_log_max​

Type
Default value
Type
Int64
Default value
0
Allows you to select the max window log of ZSTD (it will not be used for MergeTree family)