system.settings

System table containing information about session settings for current user.

Description

Contains information about session settings for current user.

Columns

• name (String) — Setting name.
• value (String) — Setting value.
• changed (UInt8) — Shows whether the setting was explicitly defined in the config or explicitly changed.
• description (String) — Short setting description.
• min (Nullable(String)) — Minimum value of the setting, if any is set via constraints. If the setting has no minimum value, contains NULL.
• max (Nullable(String)) — Maximum value of the setting, if any is set via constraints. If the setting has no maximum value, contains NULL.
• disallowed_values (Array(String)) — List of disallowed values
• readonly (UInt8) — Shows whether the current user can change the setting: 0 — Current user can change the setting, 1 — Current user can’t change the setting.
• type (String) — The type of the value that can be assigned to this setting.
• default (String) — Setting default value.
• alias_for (String) — The name of the original setting if the setting is an alias for another setting.
• is_obsolete (UInt8) — Shows whether a setting is obsolete.
• tier (Enum8(‘Production’ = 0, ‘Obsolete’ = 4, ‘Experimental’ = 8, ‘Beta’ = 12)) — Support level for this feature. ClickHouse features are organized in tiers, varying depending on the current status of their development and the expectations one might have when using them:

• PRODUCTION: The feature is stable, safe to use and does not have issues interacting with other PRODUCTION features.
• BETA: The feature is stable and safe. The outcome of using it together with other features is unknown and correctness is not guaranteed. Testing and reports are welcome.
• EXPERIMENTAL: The feature is under development. Only intended for developers and ClickHouse enthusiasts. The feature might or might not work and could be removed at any time.
• OBSOLETE: No longer supported. Either it is already removed or it will be removed in future releases.

Example

The following example shows how to get information about settings which name contains min_i.

SELECT *
FROM system.settings
WHERE name LIKE '%min_insert_block_size_%'
FORMAT Vertical

Row 1:
──────
name:        min_insert_block_size_rows
value:       1048449
changed:     0
description: Sets the minimum number of rows in the block that can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones.

Possible values:

- Positive integer.
- 0 — Squashing disabled.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64
default:     1048449
alias_for:   
is_obsolete: 0
tier:        Production

Row 2:
──────
name:        min_insert_block_size_bytes
value:       268402944
changed:     0
description: Sets the minimum number of bytes in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones.

Possible values:

- Positive integer.
- 0 — Squashing disabled.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64
default:     268402944
alias_for:   
is_obsolete: 0
tier:        Production

Row 3:
──────
name:        min_insert_block_size_rows_for_materialized_views
value:       0
changed:     0
description: Sets the minimum number of rows in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into [materialized view](/reference/statements/create/view). By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.

Possible values:

- Any positive integer.
- 0 — Squashing disabled.

## See Also {#see-also}

- [min_insert_block_size_rows](/reference/settings/session-settings#min_insert_block_size_rows)
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64
default:     0
alias_for:   
is_obsolete: 0
tier:        Production

Row 4:
──────
name:        min_insert_block_size_bytes_for_materialized_views
value:       0
changed:     0
description: Sets the minimum number of bytes in the block which can be inserted into a table by an `INSERT` query. Smaller-sized blocks are squashed into bigger ones. This setting is applied only for blocks inserted into [materialized view](/reference/statements/create/view). By adjusting this setting, you control blocks squashing while pushing to materialized view and avoid excessive memory usage.

Possible values:

- Any positive integer.
- 0 — Squashing disabled.

## See Also {#see-also}

- [min_insert_block_size_bytes](/reference/settings/session-settings#min_insert_block_size_bytes)
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64
default:     0
alias_for:   
is_obsolete: 0
tier:        Production

Using of WHERE changed can be useful, for example, when you want to check:

• Whether settings in configuration files are loaded correctly and are in use.
• Settings that changed in the current session.

SELECT * FROM system.settings WHERE changed AND name='load_balancing'

See Also

• Settings
• Permissions for Queries
• Constraints on Settings
• SHOW SETTINGS statement