Tiered storage with disks, volumes and storage policies

ClickHouse can place one table’s data on several storage devices: fast NVMe for recent data, cheaper disks or object storage for old data, or simply more disks when one fills up. The feature goes by many names (multi-volume storage, tiered storage, hot/cold storage), and the pieces are the same: disks, volumes and storage policies. Everything below ran on ClickHouse 26.4 with two local directories standing in for the devices.

Three building blocks

A disk is a named place that stores data: a local directory (usually a mount point), or an object store such as S3.
A volume is an ordered group of disks. Volumes are ordered by priority: the first volume is where new data goes.
A storage policy is an ordered list of volumes, plus rules for moving data. A table has exactly one policy.

A policy contains one or more volumes; a volume contains one or more disks. Within a volume with several disks, ClickHouse spreads parts over them (JBOD style, round robin). Across volumes, data flows from the first to the later ones.

table ──► storage policy ──► volume (priority 1) ──► disk(s)
                         └─► volume (priority 2) ──► disk(s)

Disks, volumes and policies are defined on the server in configuration, not in SQL. Put this in a file such as /etc/clickhouse-server/config.d/storage.xml:

<clickhouse>
  <storage_configuration>
    <disks>
      <hot_disk>
        <path>/mnt/nvme/clickhouse/</path>
      </hot_disk>
      <cold_disk>
        <path>/mnt/hdd/clickhouse/</path>
      </cold_disk>
    </disks>
    <policies>
      <hot_cold>
        <volumes>
          <hot>
            <disk>hot_disk</disk>
          </hot>
          <cold>
            <disk>cold_disk</disk>
          </cold>
        </volumes>
        <move_factor>0.1</move_factor>
      </hot_cold>
    </policies>
  </storage_configuration>
</clickhouse>

Two points about the config:

The default disk always exists. It is the server’s data directory (path in the main config) and you do not define it. A policy named default also exists implicitly, with the default disk as its only volume.
The directory behind each disk must be writable by the clickhouse user and, for local disks, should end with a slash.

Check what the server sees:

SELECT name, type FROM system.disks ORDER BY name;

┌─name──────┬─type──┐
│ cold_disk │ Local │
│ default   │ Local │
│ hot_disk  │ Local │
└───────────┴───────┘

(system.disks also shows each disk’s path, free_space and total_space.)

SELECT policy_name, volume_name, volume_priority, disks, move_factor
FROM system.storage_policies
ORDER BY policy_name, volume_priority;

┌─policy_name─┬─volume_name─┬─volume_priority─┬─disks─────────┬─move_factor─┐
│ default     │ default     │               1 │ ['default']   │           0 │
│ hot_cold    │ hot         │               1 │ ['hot_disk']  │         0.1 │
│ hot_cold    │ cold        │               2 │ ['cold_disk'] │         0.1 │
└─────────────┴─────────────┴─────────────────┴───────────────┴─────────────┘

Using a policy

Pick the policy when you create the table. Data is written to the first volume, and a TTL ... TO VOLUME clause defines when it moves on:

CREATE TABLE tiered (ts DateTime, v UInt32)
ENGINE = MergeTree
ORDER BY ts
TTL ts + INTERVAL 30 DAY TO VOLUME 'cold'
SETTINGS storage_policy = 'hot_cold';

Insert one row set from yesterday and one from 90 days ago:

INSERT INTO tiered SELECT now() - INTERVAL 1 DAY,  number FROM numbers(1000);
INSERT INTO tiered SELECT now() - INTERVAL 90 DAY, number FROM numbers(1000);

SELECT name, disk_name, rows FROM system.parts WHERE `table` = 'tiered' AND active ORDER BY name;

┌─name──────┬─disk_name─┬─rows─┐
│ all_1_1_0 │ hot_disk  │ 1000 │
│ all_2_2_0 │ cold_disk │ 1000 │
└───────────┴───────────┴──────┘

The old rows went straight to the cold volume at insert time, because the TTL rule already applied to them. For data that ages in place, a background job checks TTL conditions periodically and moves parts when the rule is met. Parts move as a whole, which is one more reason to partition by time: a part that mixes old and new rows moves as a unit.

A rule can have several steps:

TTL ts + INTERVAL 7 DAY  TO VOLUME 'warm',
    ts + INTERVAL 90 DAY TO VOLUME 'cold',
    ts + INTERVAL 2 YEAR DELETE

Moving data by hand

You can move a part or a partition manually, which is handy for testing a new tier or for emptying a disk before removing it:

ALTER TABLE tiered MOVE PART 'all_1_1_0' TO VOLUME 'cold';
ALTER TABLE tiered MOVE PARTITION tuple() TO DISK 'cold_disk';

After the first statement both original parts are on cold_disk; the second statement moves every part of the (unpartitioned) table to the named disk.

Moving because the disk is full

move_factor is the other trigger. If the free space on a volume falls below that fraction of its capacity, ClickHouse moves parts to the next volume, regardless of TTL. With 0.1, data starts moving when less than 10 percent is free. Setting it high, like 0.99, forces nearly everything out, so keep it moderate. Two further volume options matter: max_data_part_size_bytes stops parts above a size from being written to that volume, and prefer_not_to_merge excludes a volume’s parts from merges (useful on slow cold storage).

Adding a policy to an existing table

You can change the policy of a table with MODIFY SETTING, but the new policy must contain the volumes of the old one:

ALTER TABLE plain MODIFY SETTING storage_policy = 'hot_cold';

Code: 36. DB::Exception: New storage policy `hot_cold` shall contain volumes of the old storage policy `default`. (BAD_ARGUMENTS)

That is real output for a table on the implicit default policy. The fix is to define the new policy so it keeps the same volume name and disk, then add the new volumes after it:

<policies>
  <default_and_cold>
    <volumes>
      <default>
        <disk>default</disk>
      </default>
      <cold>
        <disk>cold_disk</disk>
      </cold>
    </volumes>
  </default_and_cold>
</policies>

The sequence for an existing replicated cluster is:

Add the disks and the policy to every server’s config, and make sure each server has loaded it (check system.storage_policies; some changes need a restart).
ALTER TABLE t ON CLUSTER my_cluster MODIFY SETTING storage_policy = 'default_and_cold';
ALTER TABLE t ON CLUSTER my_cluster MODIFY TTL ts + INTERVAL 1 YEAR TO VOLUME 'cold';
Verify with queries on system.disks, system.storage_policies and system.parts.

MODIFY TTL materialises the rule for existing parts by default, which can start a large move. To only apply it to new data, set materialize_ttl_after_modify = 0 on the statement.

Seeing where the bytes are

SELECT disk_name, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE `table` = 'tiered' AND active
GROUP BY disk_name
ORDER BY disk_name;

┌─disk_name─┬─parts─┬─rows─┬─size─────┐
│ cold_disk │     1 │ 1000 │ 4.46 KiB │
│ hot_disk  │     1 │ 1000 │ 4.46 KiB │
└───────────┴───────┴──────┴──────────┘

On a cluster, wrap system.parts in clusterAllReplicas('my_cluster', system.parts) and group by hostName() as well.

clickhouse-disks

The clickhouse binary ships a small utility for working with configured disks directly, outside the SQL layer. It exposes file-system-like commands (list, copy, move, link, read, write, remove, list-disks) that work on any configured disk, local or remote, and copy accepts --disk-from and --disk-to, so it can copy between a local disk and an object store:

clickhouse disks --config-file /etc/clickhouse-server/config.xml --query "list-disks"

It is a handy rclone-like tool when the data you need to move is already described as disks in your configuration. Be careful with remove: it deletes files without regard to what the table thinks.

What to remember

One table, one policy; one volume, one or more disks.
Define everything in the server configuration; reference it by name from SQL.
Movement is driven by TTL rules, by move_factor pressure, or by hand.
A new policy must be a superset of the old policy’s volumes.
To use object storage as a tier, see ClickHouse and S3.