Other session settings

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

adaptive_aggregator_freeze_threshold

The number of keys at which the adaptive aggregator freezes a thread’s local hash table (see enable_adaptive_aggregator). Smaller values keep the frozen tables cache-resident, larger values let them absorb more of the frequent keys. 0 freezes the tables at the first opportunity, which makes the algorithm behave like pure sharding by the key hash: every key is routed by its hash and aggregated by a single owner, just deferred to the merge phase instead of exchanged between threads during the scan.

adaptive_aggregator_freeze_threshold_bytes

The memory size at which the adaptive aggregator freezes a thread’s local hash table (see enable_adaptive_aggregator). A table freezes at whichever of this and adaptive_aggregator_freeze_threshold is reached first. The size is the local table’s own allocated bytes (its hash-table buffer plus its arenas), checked between blocks. The byte bound matters when the keys or the aggregation states are wide: the key-count threshold alone would let such tables outgrow the CPU caches. At the default, tables of ordinary key and state widths keep freezing by the key count. 0 disables the byte bound, so the key-count threshold alone decides.

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.

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:

• JOIN strictness

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.

compression

Applies a generic compression to the response body, e.g., compression=gz. Note this is independent of Content-Encoding (HTTP compression) and the legacy compress parameter (ClickHouse-native compression). Specifying a compressed file extension in the URL path is equivalent.

This is an HTTP-interface response-shaping setting: it is consumed before the query is executed (the response buffers are set up up-front), so it must be supplied via the HTTP URL parameter, the URL path file extension, or a user profile, not via an in-query SETTINGS clause (where it has no effect and is rejected).

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.

Supported values:

• clickhouse (default) — standard ClickHouse SQL.
• kusto — Kusto Query Language. Requires the experimental setting allow_experimental_kusto_dialect.
• prql — PRQL. Requires the experimental setting allow_experimental_prql_dialect.
• polyglot — transpiles SQL from other dialects (MySQL, PostgreSQL, etc.) into ClickHouse SQL. Requires the experimental setting allow_experimental_polyglot_dialect.
• promql — PromQL (Prometheus Query Language) evaluated over a TimeSeries table, configured by the promql_database, promql_table, and promql_evaluation_time settings.
• clickhouse_json — instead of SQL text, the query is interpreted as a JSON AST (the output of parseQueryToJSON). The SET query is still recognized in plain form so that the dialect can be switched back. Requires the experimental setting enable_json_ast_dialect.

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.

explain_syntax_single_record

Return EXPLAIN SYNTAX output as a single record (with embedded newlines) instead of one record per line, so the result is a single, recoverable row (for example, SELECT count() FROM (EXPLAIN SYNTAX ...) returns 1).

Specifying the single_record option explicitly in the EXPLAIN SYNTAX statement (for example, EXPLAIN SYNTAX single_record = 0 SELECT ...) always overrides this setting.

Set to false to restore the pre-26.8 one-record-per-line output, or set compatibility to any version older than 26.8.

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. Applied only to CREATE queries with a path that has no glob or {_partition_id} placeholder. A path with {_partition_id} always uses wildcard. A path with another glob uses no partition strategy and ignores PARTITION BY. If this setting is wildcard but the path has no {_partition_id}, no partition strategy is used; table engines that can not persist this decision in their engine arguments (e.g. HDFS) reject such a CREATE instead.

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

filter

Adds a WHERE clause to the query as a wrapping subquery. Multiple filters are combined with AND. The HTTP interface allows multiple filter URL parameters which are combined with AND in order, and with the value of this setting.

This is a query-construction setting applied by the engine on the parsed query (wrapping it as a derived table), so it composes with the existing query and works on every protocol: it can be supplied via the HTTP URL parameter, an in-query SETTINGS clause, or a user profile.

It shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

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;
┌

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 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

format

Overrides the FORMAT of the query for both input and output. Wins over the format specified in the query and in the file extension. The more specific input_format and output_format settings take precedence over this generic format setting for their respective direction.

framing_output_format

Allows to multiplex different parts of the query response in a single stream: chunks of data, totals and extremes, progress packets, profile events (metrics), and server logs - everything that the native protocol supports.

Framing formats are independent of output formats: they encapsulate bytes produced by any output format, by separating and potentially encoding these chunks of bytes. The concatenation of the payloads of all data, totals and extremes packets is exactly what the output format would have produced without framing. Auxiliary packets (progress, logs, profile events, exceptions) are represented as JSON.

One deliberate exception: an output format that drops totals and extremes in its plain output because it cannot represent them (the JSONCompactEachRow family) does emit them under framing, into the totals and extremes packets. For such formats the concatenation of the data packets alone is exactly the unframed output, and the totals and extremes packets carry additional rows that the unframed output does not contain.

Server logs are included if the send_logs_level setting is set, and profile events are included if the send_profile_events setting is enabled (they are sent at most once in interactive_delay microseconds, and progress packets are also throttled by interactive_delay).

A successful stream ends with a final progress packet carrying the final counters (result_rows, result_bytes, memory_usage), written after the trailing log and profile_events packets emitted by the query-finish logging, like the final progress packet of the native protocol. On failure, the exception packet is the last packet instead - with one exception: when the failure happens after part of the packet stream has already been produced into the response and can no longer be discarded (a packet write fails partway through, the delivery of the exception packet itself fails, or the response stream fails while being flushed or closed), the framing fails closed - the stream is terminated without a terminal exception packet, and the client observes a truncated response and an aborted HTTP connection instead of a parseable terminal packet. Nothing is ever appended after a partial packet stream, so a plain HTTP error body is never mixed into it.

Anything a query enables only through its own SETTINGS clause - a framing format, send_logs_level, or send_profile_events - is not known until the query has been parsed, so the corresponding logs and profile events are captured only from query execution onwards. The logs and profile events of the parse, plan, and analysis phase are captured only when the setting comes from the session or the URL. For example, a query that fails during analysis (such as a reference to an unknown table) and enables send_logs_level only in its SETTINGS clause delivers just the exception packet, not the analysis-phase logs; set send_logs_level on the session or the URL to capture those.

The same late-discovery caveat applies to send_logs_source_regexp: the log queue filters entries by source at the moment each entry is captured, so a regexp set only in the query’s own SETTINGS clause takes effect from query execution onwards. The log packets of the parse, plan, and analysis phase are filtered by the session or URL value of the setting (they are unfiltered when it is not set there), so they may include sources that do not match the query-level regexp; conversely, entries dropped by a narrower session or URL regexp are not recovered by a broader query-level one. Set send_logs_source_regexp on the session or the URL to filter the whole query lifecycle.

The setting currently applies to the HTTP protocol and is ignored for other interfaces.

Possible values:

• None - transparently routes everything applicable (data, totals, extremes, progress) to the output format, and ignores everything that is not applicable (metrics, logs), so everything works as it is by default.
• EventStream - frames packets as HTTP server-sent events (text/event-stream). Every packet is sent as an event with the corresponding name: data, totals, extremes, progress, log, profile_events, exception. Progress and other auxiliary packets are sent as JSON. Because server-sent events are a text protocol that treats line breaks (including carriage returns, \r) as delimiters, a block of formatted data is base64-encoded into a single data field of the event, which decodes to the fully formatted payload with all of its newlines; the Content-Type carries a payload=base64 parameter to say so. Any output format can be carried this way byte-exactly, text and binary alike.
• JSONEachPacketBase64 - every packet is a JSON object on a separate line, and the formatted data is base64-encoded, e.g. {"packet":"data","data":"eyJ4IjoxfQo="}. Suitable for binary output formats.
• JSONEachPacketString - every packet is a JSON object on a separate line, and the formatted data is put into a string, e.g. {"packet":"data","data":"{\"x\":1}\n"}.

JSONEachPacketString puts the payload bytes into a JSON string without validating or re-encoding them. String and FixedString columns can hold arbitrary bytes, so text output formats (such as JSONEachRow, TSV, or CSV) may emit invalid UTF-8 for such values - just as ClickHouse’s own JSONEachRow does with the default output_format_json_validate_utf8 = 0 - and then the resulting NDJSON stream is not guaranteed to be valid UTF-8. Use JSONEachPacketBase64 for byte-exact transport of arbitrary bytes.

Example:

curl "http://localhost:8123/?framing_output_format=JSONEachPacketString" -d "SELECT number FROM numbers(3) FORMAT JSONEachRow"

Result:

{"packet":"data","data":"{\"number\":\"0\"}\n{\"number\":\"1\"}\n{\"number\":\"2\"}\n"}
{"packet":"profile_events","profile_events":[{"host_name":"localhost","current_time":"2026-07-11 00:00:00","thread_id":"0","type":"increment","name":"SelectedRows","value":"3"}]}
{"packet":"progress","progress":{"read_rows":"3","read_bytes":"24","total_rows_to_read":"3","result_rows":"3","result_bytes":"24","elapsed_ns":"1265958"}}

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

input_format

Overrides the input format of the query. Wins over the format specified in the query.

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. The value is passed through to LIMIT and accepts everything that LIMIT accepts, including negative values (count from the end of the result) and fractions in (0, 1) (interpreted as a share of the result).

Possible values:

• 0 — The number of rows is not limited.
• Positive integer — exact number of rows.
• Negative integer — return the last N rows.
• A real number in the open range (0, 1) — return that fraction of the result.

This setting shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

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.

Enabling it automatically adjusts settings that control features not supported by distributed query plans yet:

• enable_parallel_replicas = 0 and automatic_parallel_replicas_mode = 0 — the distributed plan does its own work distribution;
• correlated_subqueries_use_in_memory_buffer = 0;
• use_skip_indexes_on_data_read = 0;
• compile_expressions = 0;
• query_plan_direct_read_from_text_index = 0.

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. The value is passed through to OFFSET and accepts everything that OFFSET accepts, including negative values and fractions in (0, 1).

Possible values:

• 0 — No rows are skipped.
• Positive integer.
• Negative integer.
• A real number in the open range (0, 1) — skip that fraction of the result.

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 │
└─────┘

This setting shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

order

Adds an ORDER BY clause to the query as a wrapping subquery. Accepts an arbitrary expression list.

This is a query-construction setting applied by the engine on the parsed query (wrapping it as a derived table), so it composes with the existing query and works on every protocol: it can be supplied via the HTTP URL parameter, an in-query SETTINGS clause, or a user profile.

It shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

output_format

Overrides the output format of the query. Wins over the format specified in the query, in the file extension, or via default_format.

page

Sets the page number for paginated results. Equivalent to offset = limit * (page - 1). Can only be specified when limit is set and offset is not. Pages are 1-based. Inherits the same negative/fractional support as limit and offset.

This is a query-construction setting applied by the engine on the parsed query (wrapping it as a derived table), so it composes with the existing query and works on every protocol: it can be supplied via the HTTP URL parameter, an in-query SETTINGS clause, or a user profile.

It shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

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

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

SELECT *
FROM numbers_mt(100000)
FORMAT Null;

6666 rows in set. ...

resumable_backup_from_snapshot

Enables resumable BACKUP FROM SNAPSHOT: a failed attempt can be rerun without recopying the entries of batches that already completed. Only available in ClickHouse Cloud, for directory-style S3 and AzureBlobStorage destinations. Enabling it in ClickHouse open-source builds, where BACKUP FROM SNAPSHOT itself is unavailable, makes BACKUP fail with WRONG_BACKUP_SETTINGS.

rows_before_aggregation

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

run_query_in_background

If enabled, the server schedules the query in the background, immediately returns an empty successful result, and runs the query to completion regardless of what happens to the connection.

A background query does not survive a server restart. On shutdown it obeys the same server settings as a foreground query: shutdown_wait_unfinished_queries chooses between cancelling it and waiting for it (queued entries are discarded either way, without a system.query_log entry), and shutdown_wait_unfinished limits how long the server waits.

Track the query by its query_id: in system.processes while it is running and in system.query_log after it finishes and the query log entry is flushed.

Applies to queries received over the native TCP and HTTP protocols. Over HTTP, pass the setting as a URL parameter. It cannot be changed with SET; enable it per query, or at the user or profile level.

The main use case is a long INSERT ... SELECT that must not be lost when the client connection drops.

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

Wraps the query as a subquery with an explicit SELECT expression list. When non-empty, the result-producing query is wrapped as SELECT <expr_list> FROM (<query>).

This is a query-construction setting applied by the engine on the parsed query (wrapping it as a derived table), so it composes with the existing query and works on every protocol: it can be supplied via the HTTP URL parameter, an in-query SETTINGS clause, or a user profile.

It shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

select_sequential_consistency

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:

• insert_quorum
• insert_quorum_timeout
• insert_quorum_parallel

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

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'
┌

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

Adds a simple ORDER BY clause to the query as a wrapping subquery. Accepts a comma-separated list of identifiers or positional column references (positive integers) with an optional + (ASC) or - (DESC) prefix. Example: sort=a,-b orders by a ascending and b descending; sort=1,-2 orders by the first column ascending and the second descending. Cannot be combined with order.

This is a query-construction setting applied by the engine on the parsed query (wrapping it as a derived table), so it composes with the existing query and works on every protocol: it can be supplied via the HTTP URL parameter, an in-query SETTINGS clause, or a user profile.

It shapes result-producing SELECT / UNION queries. For a write query (INSERT … SELECT, CREATE … AS SELECT) it takes effect only when the source SELECT carries it in its own SETTINGS clause; a value inherited from a profile or session, or set on the INSERT / CREATE statement itself, does not propagate into the source SELECT — the same non-propagation rule that applies to any other setting.

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.

statistics_max_set_size_for_exact_selectivity_estimation

The maximum size of the set in the right-hand side of the IN operator for which the selectivity estimator derives the exact ranges covered by the set. Deriving them costs a Field per element, a sort, and one statistics probe per element, which for a large set dominates query planning. Above this limit the estimator instead derives the selectivity from the size of the set and its bounding range, which is a single linear pass over the set without the sort or the per-element statistics probes. Zero means no limit.

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.

time_series_prefer_recent_samples_table

Read from the recent samples table of a TimeSeries table instead of the main samples table when the whole requested time range fits in the TTL window of the recent samples table (see the recent_samples_ttl_seconds setting of the TimeSeries table engine).

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.

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)