Other session settings - ClickHouse Documentation

These settings are available in system.settings and are autogenerated from source.

add_http_cors_header

Write add http CORS header.

analyze_index_with_space_filling_curves

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_inline_views

When enabled, the analyzer substitutes ordinary (non-materialized, non-parameterized) views with their defining subqueries, enabling cross-boundary optimizations such as predicate pushdown and column pruning.

any_join_distinct_right_table_keys

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

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.

archive_adaptive_buffer_max_size_bytes

Limits the maximum size of the adaptive buffer used when writing to archive files (for example, tar archives

arrow_flight_request_descriptor_type

Type of descriptor to use for Arrow Flight requests. ‘path’ sends the dataset name as a path descriptor. ‘command’ sends a SQL query as a command descriptor (required for Dremio). Possible values:

‘path’ — Use FlightDescriptor::Path (default, works with most Arrow Flight servers)
‘command’ — Use FlightDescriptor::Command with a SELECT query (required for Dremio)

backup_slow_all_threads_after_retryable_s3_error

When set to true, all threads executing S3 requests to the same backup endpoint are slowed down after any single S3 request encounters a retryable S3 error, such as ‘Slow Down’. When set to false, each thread handles s3 request backoff independently of the others.

cache_warmer_threads

Only has an effect in ClickHouse Cloud. Number of background threads for speculatively downloading new data parts into the filesystem cache, when cache_populated_by_fetch is enabled. Zero to disable.

calculate_text_stack_trace

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

Cancels HTTP read-only queries (e.g. SELECT) when a client closes the connection without waiting for the response. Cloud default value: 1.

checksum_on_read

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.

connection_pool_max_wait_ms

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

The maximum number of connection attempts with each replica for the Distributed table engine.

convert_query_to_cnf

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

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT number AS x
    FROM numbers(20)
) AS a
WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15))
SETTINGS convert_query_to_cnf = false;

The result is:

┌─explain────────────────────────────────────────────────────────┐
│ SELECT x                                                       │
│ FROM                                                           │
│ (                                                              │
│     SELECT number AS x                                         │
│     FROM numbers(20)                                           │
│     WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15)) │
│ ) AS a                                                         │
│ WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15))     │
│ SETTINGS convert_query_to_cnf = 0                              │
└────────────────────────────────────────────────────────────────┘

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

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT number AS x
    FROM numbers(20)
) AS a
WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15))
SETTINGS convert_query_to_cnf = true;

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

┌─explain───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ SELECT x                                                                                                              │
│ FROM                                                                                                                  │
│ (                                                                                                                     │
│     SELECT number AS x                                                                                                │
│     FROM numbers(20)                                                                                                  │
│     WHERE ((x <= 15) OR (x <= 5)) AND ((x <= 15) OR (x >= 1)) AND ((x >= 10) OR (x <= 5)) AND ((x >= 10) OR (x >= 1)) │
│ ) AS a                                                                                                                │
│ WHERE ((x >= 10) OR (x >= 1)) AND ((x >= 10) OR (x <= 5)) AND ((x <= 15) OR (x >= 1)) AND ((x <= 15) OR (x <= 5))     │
│ SETTINGS convert_query_to_cnf = 1                                                                                     │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Possible values: true, false

count_matches_stop_at_empty_match

Stop counting once a pattern matches zero-length in the countMatches function.

cross_to_inner_join_rewrite

Use inner join instead of comma/cross join if there are joining expressions in the WHERE section. Values: 0 - no rewrite, 1 - apply if possible for comma/cross, 2 - force rewrite all comma joins, cross - if possible

data_type_default_nullable

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.

decimal_check_overflow

Check overflow of decimal arithmetic/comparison operations

deduplicate_blocks_in_dependent_materialized_views

Enables or disables the deduplication check for materialized views that receive data from Replicated* tables. Possible values:

0 — Disabled.
1 — Enabled.

When enabled, ClickHouse performs deduplication of blocks in materialized views that depend on Replicated* tables. This setting is useful for ensuring that materialized views do not contain duplicate data when the insertion operation is being retried due to a failure. See Also

NULL Processing in IN Operators

defer_partition_pruning_after_final

When enabled (default), partition pruning is skipped for FINAL queries on tables whose partition-key columns are not part of the sorting key. This is the correctness-safe behavior introduced in 26.3: FINAL may need to deduplicate rows that share a primary key but live in different partitions, and partition pruning would silently exclude such rows from the deduplication input. When disabled, partition pruning is applied even with FINAL, restoring the pre-26.3 behavior. This can be substantially faster for queries with WHERE predicates on the partition column, but is only correct when rows with the same primary key cannot exist in different partitions — e.g. event-log tables whose partition column is set at insert time and never changes. This setting only affects partitioned tables whose partition-key columns are not contained in the sorting key; for other tables partition pruning is always applied. Possible values:

0 — Apply partition pruning before FINAL (pre-26.3 behavior, faster but unsafe in the general case).
1 — Defer partition pruning to after FINAL (default, correctness-safe).

describe_compact_output

If true, include only column names and types into result of DESCRIBE query

dialect

Which dialect will be used to parse query

discard_query_data

If enabled, the server skips sending query result rows to the client. The query is still executed and logged fully on the server, and the client still receives the remaining packets. Used for shadow traffic, benchmarks, and fuzzing. Has no effect for secondary queries. Affects only the native TCP protocol.

distinct_overflow_mode

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.

do_not_merge_across_partitions_select_final

Improve FINAL queries by avoiding merges across different partitions. When enabled, during SELECT FINAL queries, parts from different partitions will not be merged together. Instead, merging will only occur within each partition separately. This can significantly improve query performance when working with partitioned tables.

dynamic_throw_on_type_mismatch

When applying a function to a Dynamic column using the default implementation, controls what happens for rows whose actual type is incompatible with the function:

true (default) — throw an exception.
false — return NULL for those rows instead.

enforce_strict_identifier_format

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

engine_url_skip_empty_files

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.

exact_rows_before_limit

When enabled, ClickHouse will provide exact value for rows_before_limit_at_least statistic, but with the cost that the data before limit will have to be read completely

except_default_mode

Set default mode in EXCEPT query. Possible values: empty string, ‘ALL’, ‘DISTINCT’. If empty, query without mode will throw exception.

exclude_materialize_skip_indexes_on_insert

Excludes specified skip indexes from being built and stored during INSERTs. The excluded skip indexes will still be built and stored during merges or by an explicit MATERIALIZE INDEX query. Has no effect if materialize_skip_indexes_on_insert is false. Example:

CREATE TABLE tab
(
    a UInt64,
    b UInt64,
    INDEX idx_a a TYPE minmax,
    INDEX idx_b b TYPE set(3)
)
ENGINE = MergeTree ORDER BY tuple();

SET exclude_materialize_skip_indexes_on_insert='idx_a'; -- idx_a will be not be updated upon insert
--SET exclude_materialize_skip_indexes_on_insert='idx_a, idx_b'; -- neither index would be updated on insert

INSERT INTO tab SELECT number, number / 50 FROM numbers(100); -- only idx_b is updated

-- since it is a session setting it can be set on a per-query level
INSERT INTO tab SELECT number, number / 50 FROM numbers(100, 100) SETTINGS exclude_materialize_skip_indexes_on_insert='idx_b';

ALTER TABLE tab MATERIALIZE INDEX idx_a; -- this query can be used to explicitly materialize the index

SET exclude_materialize_skip_indexes_on_insert = DEFAULT; -- reset setting to default

execute_exists_as_scalar_subquery

Execute non-correlated EXISTS subqueries as scalar subqueries. As for scalar subqueries, the cache is used, and the constant folding applies to the result. Cloud default value: 0.

explain_query_plan_default

Default format used by EXPLAIN PLAN. Possible values:

pretty (default since 26.7) — actions, compact, and pretty default to true, producing a compact, pretty, action-annotated plan.
legacy — pre-26.7 output.

Specifying the actions, compact, or pretty options explicitly in the EXPLAIN statement (for example, EXPLAIN actions = 0, compact = 0, pretty = 0 SELECT ...) always overrides this setting. EXPLAIN PLAN with json = 1 or distributed = 1 keeps the legacy (pre-26.7) defaults regardless of this setting, unless actions, compact, or pretty are set explicitly. The pretty output cannot represent JSON results or per-shard distributed plans, so those modes are only rendered correctly in legacy form.

extract_key_value_pairs_max_pairs_per_row

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

extremes

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

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

file_like_engine_default_partition_strategy

Default partition strategy for file like engines.

filesystem_prefetches_limit

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

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:

CREATE TABLE test
(
    key Int64,
    some String
)
ENGINE = ReplacingMergeTree
ORDER BY key;

INSERT INTO test FORMAT Values (1, 'first');
INSERT INTO test FORMAT Values (1, 'second');

SELECT * FROM test;
┌─key─┬─some───┐
│   1 │ second │
└─────┴────────┘
┌─key─┬─some──┐
│   1 │ first │
└─────┴───────┘

SELECT * FROM test SETTINGS final = 1;
┌─key─┬─some───┐
│   1 │ second │
└─────┴────────┘

SET final = 1;
SELECT * FROM test;
┌─key─┬─some───┐
│   1 │ second │
└─────┴────────┘

finalize_projection_parts_synchronously

When enabled, projection parts are finalized synchronously during INSERT, reducing peak memory usage at the cost of reduced S3 upload parallelism. By default, each projection’s output stream is kept alive until the entire part (including all projections) is finalized, which allows overlapping S3 uploads but increases peak memory proportional to the number of projections. This setting only affects the INSERT path; merge and mutation already finalize projections synchronously.

flatten_nested

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:

SET flatten_nested = 1;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();

SHOW CREATE TABLE t_nest;

Result:

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
    `n.a` Array(UInt32),
    `n.b` Array(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query:

SET flatten_nested = 0;

CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();

SHOW CREATE TABLE t_nest;

Result:

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
    `n` Nested(a UInt32, b UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

fsync_metadata

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.

functions_h3_default_if_invalid

If false, h3 functions, e.g. h3CellAreaM2, throw an exception if input is invalid. If true, they return 0 or default value.

geo_distance_returns_float64_on_float64_arguments

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_argument_order

Function ‘geoToH3’ accepts (lon, lat) if set to ‘lon_lat’ and (lat, lon) if set to ‘lat_lon’.

glob_expansion_max_elements

Maximum number of allowed addresses (For external storages, table functions, etc).

h3togeo_lon_lat_result_order

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

handshake_timeout_ms

Timeout in milliseconds for receiving Hello packet from replicas during handshake.

hedged_connection_timeout_ms

Connection timeout for establishing connection with replica for Hedged requests

highlight_max_matches_per_row

Sets the maximum number of highlight matches per row in the highlight function. Use it to protect against excessive memory usage when highlighting highly repetitive patterns in large texts. Possible values:

Positive integer.

hnsw_candidate_list_size_for_search

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

hsts_max_age

Expired time for HSTS. 0 means disable HSTS.

idle_connection_timeout

Timeout to close idle TCP connections after specified number of seconds. Possible values:

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

inject_random_order_for_select_without_order_by

If enabled, injects ‘ORDER BY rand()’ into SELECT queries without ORDER BY clause. Applied only for subquery depth = 0. Subqueries and INSERT INTO … SELECT are not affected. If the top-level construct is UNION, ‘ORDER BY rand()’ is injected into all children independently. Only useful for testing and development (missing ORDER BY is a source of non-deterministic query results).

interactive_delay

The interval in microseconds for checking whether request execution has been canceled and sending the progress.

intersect_default_mode

Set default mode in INTERSECT query. Possible values: empty string, ‘ALL’, ‘DISTINCT’. If empty, query without mode will throw exception.

least_greatest_legacy_null_behavior

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

legacy_column_name_of_tuple_literal

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.

limit

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.

load_marks_asynchronously

Load MergeTree marks asynchronously Cloud default value: 1.

lock_acquire_timeout

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.

low_priority_query_wait_time_ms

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.

make_distributed_plan

Make distributed query plan.

merge_table_max_tables_to_look_for_schema_inference

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.

mongodb_throw_on_unsupported_query

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

multiple_joins_try_to_keep_original_names

Do not add aliases to top level expression list on multiple joins rewrite

normalize_function_names

Normalize function names to their canonical names

offset

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:

CREATE TABLE test (i UInt64) ENGINE = MergeTree() ORDER BY i;
INSERT INTO test SELECT number FROM numbers(500);

Query:

SET limit = 5;
SET offset = 7;
SELECT * FROM test LIMIT 10 OFFSET 100;

Result:

┌───i─┐
│ 107 │
│ 108 │
│ 109 │
└─────┘

paimon_target_snapshot_id

Query-level targeted snapshot read for Paimon incremental mode. When >0, the reader will only fetch the delta for the specified snapshot_id without advancing the committed watermark. Default: -1 (disabled)

parallelize_output_from_storages

Parallelize output for reading step from storage. It allows parallelization of query processing right after reading from storage if possible

partial_result_on_first_cancel

Allows query to return a partial result after cancel.

per_part_index_stats

Logs index statistics per part

poll_interval

Block at the query wait loop on the server for the specified number of seconds.

polyglot_dialect

Source SQL dialect for the polyglot transpiler (e.g. ‘sqlite’, ‘mysql’, ‘postgresql’, ‘snowflake’, ‘duckdb’).

postgresql_fault_injection_probability

Approximate probability of failing internal (for replication) PostgreSQL queries. Valid value is in interval [0.0f, 1.0f]

predicate_statistics_sample_rate

Collect predicate selectivity statistics into system.predicate_statistics_log. When set to N > 0, approximately 1/N of queries are sampled (by the query ID). 0 means disabled.

prefetch_buffer_size

The maximum size of the prefetch buffer to read from the filesystem. Values above 256 MiB are clamped to 256 MiB, as a read buffer never needs to be larger.

print_pretty_type_names

Allows to print deep-nested type names in a pretty way with indents in DESCRIBE query and in toTypeName() function. Example:

CREATE TABLE test (a Tuple(b String, c Tuple(d Nullable(UInt64), e Array(UInt32), f Array(Tuple(g String, h Map(String, Array(Tuple(i String, j UInt64))))), k Date), l Nullable(String))) ENGINE=Memory;
DESCRIBE TABLE test FORMAT TSVRaw SETTINGS print_pretty_type_names=1;

a   Tuple(
    b String,
    c Tuple(
        d Nullable(UInt64),
        e Array(UInt32),
        f Array(Tuple(
            g String,
            h Map(
                String,
                Array(Tuple(
                    i String,
                    j UInt64
                ))
            )
        )),
        k Date
    ),
    l Nullable(String)
)

priority

Priority of the query. 1 - the highest, higher value - lower priority; 0 - do not use priorities.

push_external_roles_in_interserver_queries

Enable pushing user roles from originator to other nodes while performing a query.

query_metric_log_interval

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

queue_max_wait_ms

The wait time in the request queue, if the number of concurrent requests exceeds the maximum.

rabbitmq_max_wait_ms

The wait time for reading from RabbitMQ before retry.

readonly

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.

recursive_cte_max_steps_in_type_inference

Maximum number of iterations for inferring column types in recursive CTEs. Column types are determined by iteratively applying getLeastSupertype across the non-recursive and recursive sides of the UNION ALL until convergence. Set to 0 to disable type widening and use the types from the non-recursive part only.

regexp_max_matches_per_row

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

Reject patterns which will likely be expensive to evaluate with hyperscan (due to NFA state explosion)

remerge_sort_lowered_memory_bytes_ratio

If memory usage after remerge does not reduced by this ratio, remerge will be disabled.

remote_read_min_bytes_for_seek

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

replication_wait_for_inactive_replica_timeout

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.

reserve_memory

Used in workload scheduling. The minimum amount of RAM reserved to be used for running a query on a single server. Reservation is made through the WORKLOAD hierarchy using the value of a workload query setting. If not enough memory is available to the workload, a query is prevented from starting and waits in pending state until the reservation can be fulfilled. A value of 0 means no reservation. This setting takes effect only if MEMORY RESERVATION resource is created.

restore_replicated_merge_tree_to_shared_merge_tree

Replace table engine from ReplicatedMergeTree -> SharedMergeTree during RESTORE. Cloud default value: 1.

result_overflow_mode

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

Query

SET max_threads = 3, max_block_size = 3333;
SET max_result_rows = 3334, result_overflow_mode = 'break';

SELECT *
FROM numbers_mt(100000)
FORMAT Null;

Result

6666 rows in set. ...

rows_before_aggregation

When enabled, ClickHouse will provide exact value for rows_before_aggregation statistic, represents the number of rows read before aggregatio

secondary_indices_enable_bulk_filtering

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

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:

session_timezone

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:

SELECT timeZone(), serverTimeZone() FORMAT CSV

"Europe/Berlin","Europe/Berlin"

SELECT timeZone(), serverTimeZone() SETTINGS session_timezone = 'Asia/Novosibirsk' FORMAT CSV

"Asia/Novosibirsk","Europe/Berlin"

Assign session time zone ‘America/Denver’ to the inner DateTime without explicitly specified time zone:

SELECT toDateTime64(toDateTime64('1999-12-12 23:23:23.123', 3), 3, 'Europe/Zurich') SETTINGS session_timezone = 'America/Denver' FORMAT TSV

1999-12-13 07:23:23.123

Not all functions that parse DateTime/DateTime64 respect session_timezone. This can lead to subtle errors. See the following example and explanation.

CREATE TABLE test_tz (`d` DateTime('UTC')) ENGINE = Memory AS SELECT toDateTime('2000-01-01 00:00:00', 'UTC');

SELECT *, timeZone() FROM test_tz WHERE d = toDateTime('2000-01-01 00:00:00') SETTINGS session_timezone = 'Asia/Novosibirsk'
0 rows in set.

SELECT *, timeZone() FROM test_tz WHERE d = '2000-01-01 00:00:00' SETTINGS session_timezone = 'Asia/Novosibirsk'
┌───────────────────d─┬─timeZone()───────┐
│ 2000-01-01 00:00:00 │ Asia/Novosibirsk │
└─────────────────────┴──────────────────┘

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

timezone

set_overflow_mode

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.

single_join_prefer_left_table

For single JOIN in case of identifier ambiguity prefer left table

skip_redundant_aliases_in_udf

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:

SET skip_redundant_aliases_in_udf = 0;
CREATE FUNCTION IF NOT EXISTS test_03274 AS ( x ) -> ((x + 1 as y, y + 2));

EXPLAIN SYNTAX SELECT test_03274(4 + 2);

Result:

SELECT ((4 + 2) + 1 AS y, y + 2)

Query:

SET skip_redundant_aliases_in_udf = 1;
CREATE FUNCTION IF NOT EXISTS test_03274 AS ( x ) -> ((x + 1 as y, y + 2));

EXPLAIN SYNTAX SELECT test_03274(4 + 2);

Result:

SELECT ((4 + 2) + 1, ((4 + 2) + 1) + 2)

sleep_after_receiving_query_ms

Time to sleep after receiving query in TCPHandler

snappy_mode

Controls the wire format used for snappy compression for generic file I/O paths such as file and url. HTTP Content-Encoding: snappy always uses the framing format and ignores this setting. Note that the raw snappy block format produced by a single snappy::Compress call (for example, the Prometheus remote protocol payloads handled by SnappyBasicReadBuffer) is a separate, protocol-specific wire format and is not controlled by this setting. Possible values:

basic — Hadoop snappy block format. Compatible with files read and written by Hadoop. Supports both reading and writing.
framed — Snappy framing format, the standard streaming format defined by Google. Supports both reading and writing.

sort_overflow_mode

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.

splitby_max_substrings_includes_remaining_string

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

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.

tcp_keep_alive_timeout

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

Wait time to lock cache for space reservation for temporary data in filesystem cache

throw_if_no_data_to_insert

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.

timeout_before_checking_execution_speed

Checks that execution speed is not too slow (no less than min_execution_speed), after the specified time in seconds has expired.

transfer_overflow_mode

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

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:

┌──idx─┬─────i─┐
│    1 │     1 │
│    2 │  NULL │
│    3 │     3 │
└──────┴───────┘

Query:

SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 0;

Result:

┌──idx─┬────i─┐
│    1 │    1 │
└──────┴──────┘

Query:

SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 1;

Result:

┌──idx─┬─────i─┐
│    1 │     1 │
│    2 │  NULL │
└──────┴───────┘

See Also

NULL Processing in IN Operators

traverse_shadow_remote_data_paths

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.

unique_key_max_encoded_size

Maximum size (in bytes) of the order-preserving binary encoding of a single UNIQUE KEY row.

unknown_packet_in_send_data

Send unknown packet instead of data Nth data packet

variant_throw_on_type_mismatch

When applying a function to a Variant column using the default implementation, controls what happens for rows whose actual type is incompatible with the function:

true (default) — throw an exception.
false — return NULL for those rows instead.

wait_changes_become_visible_after_commit_mode

Wait for committed changes to become actually visible in the latest snapshot

workload

Name of workload to be used to access resources

write_full_path_in_iceberg_metadata

Write full paths (including s3://) into iceberg metadata files.

zstd_window_log_max

Allows you to select the max window log of ZSTD (it will not be used for MergeTree family)

Last modified on July 24, 2026

OverviewSettings for MergeTree which are in `system.merge_tree_settings`

​add_http_cors_header

​analyze_index_with_space_filling_curves

​analyzer_inline_views

​any_join_distinct_right_table_keys

​archive_adaptive_buffer_max_size_bytes

​arrow_flight_request_descriptor_type

​backup_slow_all_threads_after_retryable_s3_error

​cache_warmer_threads

​calculate_text_stack_trace

​cancel_http_readonly_queries_on_client_close

​checksum_on_read

​connection_pool_max_wait_ms

​connections_with_failover_max_tries

​convert_query_to_cnf

​count_matches_stop_at_empty_match

​cross_to_inner_join_rewrite

​data_type_default_nullable

​decimal_check_overflow

​deduplicate_blocks_in_dependent_materialized_views

​defer_partition_pruning_after_final

​describe_compact_output

​dialect

​discard_query_data

​distinct_overflow_mode

​do_not_merge_across_partitions_select_final

​dynamic_throw_on_type_mismatch

​enforce_strict_identifier_format

​engine_url_skip_empty_files

​exact_rows_before_limit

​except_default_mode

​exclude_materialize_skip_indexes_on_insert

​execute_exists_as_scalar_subquery

​explain_query_plan_default

​extract_key_value_pairs_max_pairs_per_row

​extremes

​fallback_to_stale_replicas_for_distributed_queries

​file_like_engine_default_partition_strategy

​filesystem_prefetches_limit

​final

​finalize_projection_parts_synchronously

​flatten_nested

​fsync_metadata

​functions_h3_default_if_invalid

​geo_distance_returns_float64_on_float64_arguments

​geotoh3_argument_order

​glob_expansion_max_elements

​h3togeo_lon_lat_result_order

​handshake_timeout_ms

​hedged_connection_timeout_ms

​highlight_max_matches_per_row

​hnsw_candidate_list_size_for_search

​hsts_max_age

​idle_connection_timeout

​inject_random_order_for_select_without_order_by

​interactive_delay

​intersect_default_mode

​least_greatest_legacy_null_behavior

​legacy_column_name_of_tuple_literal

​limit

​load_marks_asynchronously

​lock_acquire_timeout

​low_priority_query_wait_time_ms

​make_distributed_plan

​merge_table_max_tables_to_look_for_schema_inference

​mongodb_throw_on_unsupported_query

​multiple_joins_try_to_keep_original_names

​normalize_function_names

​offset

​paimon_target_snapshot_id

​parallelize_output_from_storages

​partial_result_on_first_cancel

​per_part_index_stats

​poll_interval

​polyglot_dialect

​postgresql_fault_injection_probability

​predicate_statistics_sample_rate

​prefetch_buffer_size

​print_pretty_type_names

​priority

​push_external_roles_in_interserver_queries

add_http_cors_header

analyze_index_with_space_filling_curves

analyzer_inline_views

any_join_distinct_right_table_keys

archive_adaptive_buffer_max_size_bytes

arrow_flight_request_descriptor_type

backup_slow_all_threads_after_retryable_s3_error

cache_warmer_threads

calculate_text_stack_trace

cancel_http_readonly_queries_on_client_close

checksum_on_read

connection_pool_max_wait_ms

connections_with_failover_max_tries

convert_query_to_cnf

count_matches_stop_at_empty_match

cross_to_inner_join_rewrite

data_type_default_nullable

decimal_check_overflow

deduplicate_blocks_in_dependent_materialized_views

defer_partition_pruning_after_final

describe_compact_output

dialect

discard_query_data

distinct_overflow_mode

do_not_merge_across_partitions_select_final

dynamic_throw_on_type_mismatch

enforce_strict_identifier_format

engine_url_skip_empty_files

exact_rows_before_limit

except_default_mode

exclude_materialize_skip_indexes_on_insert

execute_exists_as_scalar_subquery

explain_query_plan_default

extract_key_value_pairs_max_pairs_per_row

extremes

fallback_to_stale_replicas_for_distributed_queries

file_like_engine_default_partition_strategy

filesystem_prefetches_limit

final

finalize_projection_parts_synchronously

flatten_nested

fsync_metadata

functions_h3_default_if_invalid

geo_distance_returns_float64_on_float64_arguments

geotoh3_argument_order

glob_expansion_max_elements

h3togeo_lon_lat_result_order

handshake_timeout_ms

hedged_connection_timeout_ms

highlight_max_matches_per_row

hnsw_candidate_list_size_for_search

hsts_max_age

idle_connection_timeout

inject_random_order_for_select_without_order_by

interactive_delay

intersect_default_mode

least_greatest_legacy_null_behavior

legacy_column_name_of_tuple_literal

limit

load_marks_asynchronously

lock_acquire_timeout

low_priority_query_wait_time_ms

make_distributed_plan

merge_table_max_tables_to_look_for_schema_inference

mongodb_throw_on_unsupported_query

multiple_joins_try_to_keep_original_names

normalize_function_names

offset

paimon_target_snapshot_id

parallelize_output_from_storages

partial_result_on_first_cancel

per_part_index_stats

poll_interval

polyglot_dialect

postgresql_fault_injection_probability

predicate_statistics_sample_rate

prefetch_buffer_size

print_pretty_type_names

priority

push_external_roles_in_interserver_queries