system.storage_policies

System table containing information about storage policies and volumes which are defined in server configuration.

Description

Contains information about storage policies and volumes which are defined in server configuration.

Columns

• policy_name (String) — The name of the storage policy.
• volume_name (String) — The name of the volume.
• volume_priority (UInt64) — The priority of the volume.
• disks (Array(String)) — The list of all disks names which are a part of this storage policy.
• volume_type (Enum8(‘JBOD’ = 0, ‘SINGLE_DISK’ = 1, ‘UNKNOWN’ = 2)) — The type of the volume - JBOD or a single disk.
• max_data_part_size (UInt64) — the maximum size of a part that can be stored on any of the volumes disks.
• move_factor (Float32) — When the amount of available space gets lower than this factor, data automatically starts to move on the next volume if any (by default, 0.1).
• prefer_not_to_merge (UInt8) — You should not use this setting. Disables merging of data parts on this volume (this is harmful and leads to performance degradation).
• perform_ttl_move_on_insert (UInt8) — Disables TTL move on data part INSERT. By default (if enabled) if we insert a data part that already expired by the TTL move rule it immediately goes to a volume/disk declared in move rule.
• load_balancing (Enum8(‘ROUND_ROBIN’ = 0, ‘LEAST_USED’ = 1)) — Policy for disk balancing, round_robin or least_used.

Volume selection on INSERT

When INSERT creates a new data part, ClickHouse picks a destination disk by trying the rules below in order. The first rule that matches and can reserve space for the part wins; otherwise (rule does not apply, no free space, or max_data_part_size exceeded) evaluation continues with the next rule.

1. TTL move rule — if a TTL <expr> TO VOLUME 'X' (or TO DISK 'X') clause is already in the past for the rows being inserted, and perform_ttl_move_on_insert = 1 (default) on the TTL destination volume (for TO DISK 'X', the volume containing disk X), the part is written directly to that destination. If reservation there fails, the insert falls back to steps 2–4; a warning is logged but the INSERT does not fail for this reason alone.
2. max_data_part_size — a volume rejects parts larger than its max_data_part_size. This is checked per volume; it does not gate a step-1 TTL ... TO DISK 'X' reservation, which targets the disk directly.
3. volume_priority — among the remaining volumes, the one with the lowest volume_priority value is chosen. Volumes without an explicit <volume_priority> are ordered by their position in the configuration.
4. load_balancing — once a volume is chosen, the disk inside that volume is selected according to its load_balancing policy (round_robin or least_used).

Override

If min_free_disk_bytes_to_perform_insert or min_free_disk_ratio_to_perform_insert is non-zero, the precedence above is bypassed. INSERT tries only the volume with the lowest volume_priority and throws NOT_ENOUGH_SPACE if no disk in that volume meets the threshold. Inserts into the system database are exempt.

perform_ttl_move_on_insert is read from the TTL destination volume, not from the source volume. For a TO DISK 'X' rule, the flag is read from the volume that contains disk X. Setting it on any other volume of the policy has no effect on the insert path.

To force inserts to honour volume_priority even when an “already expired” TTL move rule applies, set perform_ttl_move_on_insert = 0 on the TTL destination volume (for TO DISK 'X', on the volume that contains disk X). The part is then written to the priority-N volume first and moved to the TTL destination by a background move task (observable via system.moves). See the perform_ttl_move_on_insert setting on the MergeTree engine.