Implementing Hot‑Cold Storage Separation in ClickHouse Using SSD and HDD

The original ClickHouse cluster consisted of early SAS machines followed by additional SATA nodes, forming a 5‑shard × 2‑replica layout with six SAS and four SATA servers, which caused high read/write latency and frequent complaints from business users.

Due to a tight budget, only one SSD machine could be purchased; the team therefore adopted a hot‑cold storage model by attaching the SSD to /data on each of the six existing SATA servers and using /data1 for the HDD.

Data is first written to the SSD; later, according to the configured rules, cold data is automatically migrated to the HDD. The ClickHouse configuration uses three shards × 2 replicas, with config.xml pointing to /data/clickhouse_ssd/ and a three‑node ZooKeeper ensemble distributed across the machines.

By default ClickHouse creates a disk named default and a storage policy that selects this disk. To achieve hot‑cold separation, a custom storage policy named moving_from_ssd_to_hdd was manually created, referencing a hot‑disk tag (SSD) and a cold‑disk tag (HDD). The policy is applied to tables via storage_policy = 'moving_from_ssd_to_hdd' .

Configuration files ( /etc/clickhouse-server/config.d/storage.xml ) were edited to label the SSD and HDD paths and to define the new storage policy. Data parts are moved based on the move_factor (range [0,1)); a larger value speeds up migration, and individual parts can be moved manually with statements such as ALTER TABLE tab_name MOVE PART 'xxxx_xx' TO VOLUME cold; .

After several months the setup resolved latency issues and reduced hardware costs. However, during a routine restart an error occurred:

2022.10.31 17:32:06.569278 [ 33885 ] {}
Application: DB::Exception: "default" disk path should be provided in
not it
: Cannot attach table `system`.`query_thread_log` from metadata file /data/clickhouse_ssd/store/db1/...

The root cause, identified from a GitHub issue, was that the same path was declared for both the default disk and the custom SSD disk, violating the rule that non‑default disks must not share the default path.

Fix: modify storage.xml to remove the duplicate /data/clickhouse_ssd declaration for the custom disk. After updating the file, the system.storage_policies table shows the corrected configuration, and the ClickHouse instance restarts successfully.

Keywords: #clickhouse hot‑cold storage.