Settings

A setting is a named, typed value that changes how one statement runs, such as how long a request may go without progress. Settings come from one compiled-in registry. The parser, the binder, SHOW SETTINGS, the system.settings relation and this page all describe the same registry.

The SETTINGS clause

SETTINGS is the final clause of a SELECT or STREAM SELECT and applies to the whole statement.

<statement> SETTINGS <name> = <value> [, <name> = <value> …]

<value> := TRUE | FALSE | <unsigned integer> | '<string>' | INTERVAL <n> <unit> | ? | DEFAULT

SELECT *
FROM transfers
SETTINGS result_batch_rows = 2
SELECT *
FROM transfers
SETTINGS result_batch_rows = 8

SELECT *
FROM transfers
SETTINGS request_timeout = 300

Rules:

• At most 32 assignments and 4,096 bytes in one clause.
• SETTINGS is the last clause of the statement and applies to all of it, subqueries included.
• A value may be a bound parameter ?.
• One statement per request, with at most one final ;.
• SETTINGS is still a legal identifier, so SELECT * FROM system.settings works.

Where settings apply

• Queries. SELECT and STREAM SELECT take the SETTINGS clause.
• Definitions. Views, transformations and materialized views carry their own settings, such as keep_alive and checkpoint_class, which apply every time the definition runs.
• Sessions. SET applies a value to every later statement on a WebSocket session.
• Nodes. The node configuration sets the values in force for every session.

Session settings: SET, RESET, SHOW SETTINGS

On a WebSocket session, a value can be set once for every later statement on that session:

SET <name> = <value>      SET <name> TO <value>
RESET <name>              RESET ALL
SHOW SETTINGS [LIKE '<pattern>']

SET request_timeout = '300ms' RESET request_timeout

SET result_batch_rows = 3 SHOW SETTINGS LIKE 'result_batch_rows'

SET result_batch_rows TO ? RESET ALL

• Each statement captures the session values in force when it is admitted. A SET sent after a SELECT on the same socket does not change that SELECT.
• Session values end with the connection; a reconnect starts from the node values.
• Over HTTP, put the value in the statement's SETTINGS clause.
• Each setting has a range; the error for a value outside it names the setting.

Precedence

For each setting, the value in force is the first one present in this order:

1. the statement's SETTINGS clause;
2. the definition;
3. the session (SET);
4. the node configuration;
5. the registry default.

request_timeout and max_stream_duration can only be tightened: a session or statement value may lower a node's configured value, never raise it.

Names

Setting names are snake_case and case-insensitive. An unknown name returns a "did you mean" suggestion. The engine sizes query and seed parallelism, chooses join strategies and prunes shards itself, so those choices need no setting.

Registered settings

The table below lists the registry. Query system.settings for the values in force on a node, with each value's source.

Settings that apply only at the node scope are set in the node configuration, never in SQL.