Tiered storage for hot and cold data in ClickHouse - ApsaraDB for ClickHouse

Prerequisites

Community-compatible edition

The cluster runs version 20.8 or later.
The data backup feature is disabled for the cluster.

Note

For clusters running an earlier version, you can migrate data to a cluster that runs version 20.8 or later before you enable tiered storage of hot and cold data. For more information, see Data migration between Alibaba Cloud ClickHouse Community-compatible Edition clusters.
The cold and hot data tiered storage feature is not supported in the US (Silicon Valley) and US (Virginia) regions.

Enterprise edition

The cluster uses the ESSD_L1 or ESSD_L2 storage type. To use this storage type, you must submit a ticket to add your account to the allowlist.

Background

The tiered storage of hot and cold data classifies data into two categories based on access frequency:

Hot data: Frequently accessed data. This data is stored on a hot data disk, such as an ESSD or ultra disk that you select during cluster creation, for high-performance access.
Cold data: Infrequently accessed data. This data is stored on a lower-cost cold data disk for cost-effective storage. Cold storage uses OSS object storage only and cannot be changed to ESSD PL0 or other custom disk types.

The storage policies for tiered storage of hot and cold data are as follows:

Community-compatible edition

Storage policy	Description
Default storage policy	New data is written to the hot data disk for high-performance queries. When the used storage on the hot data disk reaches a specified threshold, the system automatically moves the largest data part to the cold data disk to free up space.
TTL-based storage policy	This policy extends the default storage policy. You can add a TTL clause to automatically move all data older than a specified interval to the cold data disk.

Enterprise edition

The system automatically offloads data that is no longer modified to lower-cost OSS to reduce storage costs.

Global TTL: In the Enterprise Edition, you can configure a global TTL to control the tiered storage of hot and cold data.
Offload time: After you enable the tiered storage of hot and cold data, the system scans all data files at 00:00 UTC every day. It offloads data parts whose age exceeds the global TTL from the hot data disk to the cold data disk (OSS).

Precautions

Community-compatible edition

Enable this feature during off-peak hours. After you enable this feature, the cluster immediately restarts. During the restart, you can only view cluster information and cannot upgrade the configuration, scale the cluster out or in, or modify parameters.
You must specify a storage policy for your tables when you use this feature. ApsaraDB for ClickHouse supports only the hot_to_cold storage policy. Custom storage policies, such as configuring multiple policies (for example, default and hd_policy) that point to different storage directories, are not supported.
- When you create a table, use SETTINGS storage_policy = 'hot_to_cold' to specify the storage policy.
- Modify the storage policy of an existing table.
  After January 1, 2024
  
  Run the following statement to modify the policy:
```
ALTER TABLE <table_name> MODIFY SETTING storage_policy = 'hot_to_cold';
```
  Before January 1, 2024
  
  You can add a new storage policy through specific management operations. This allows you to use the new policy to migrate data from the hot data disk to the cold data disk.
This feature cannot be disabled after it is enabled.
Disk downgrade is not supported for clusters with cold-hot tiered storage enabled.

Enterprise edition

Modifying data in cold storage, for example, through Mutation or Optimize operations, or a system-triggered Merge operation, reloads the data into hot storage to rebuild the data files. The new data files are not immediately moved back to cold storage. They are offloaded again only after their age exceeds the global TTL. Therefore, avoid frequent additions, deletions, or modifications to cold data.
Offloading is not instantaneous. The system dynamically adjusts the offload rate based on factors such as the current I/O pressure on the storage system. The process may take several hours, depending on the amount of data to be offloaded.
The following rules apply when you modify the TTL:
- If you increase the TTL, the change takes effect at the next offload time. However, data that is already in cold storage is not moved back to hot storage.
- If you decrease the TTL, the change takes effect at the next offload time.
The minimum value for the global TTL is 1 day, and the minimum adjustment increment is 1 day.
Disk downgrade is not supported for clusters with cold-hot tiered storage enabled.

Billing

The billing rules are as follows:

Type	Details
Hot data storage	The hot data storage capacity equals the storage capacity you purchased for the cluster.
Cold data storage	Cold data storage is billed on a pay-as-you-go basis. You do not need to purchase a fixed amount of storage space in advance. You are billed hourly for the storage you use.

For more information about the unit prices of cold data storage, see Storage space fees for the Community-compatible Edition and Pay-as-you-go billing for the Enterprise Edition.
Billing formula: Cold storage fee = Unit price of cold data storage × Cold data usage × Usage duration.
Example: If the unit price of cold data storage is USD 0.000026 per GB-hour and you use 2,000 GB of cold storage from 19:00 to 20:00, the total fee is calculated as follows: USD 0.000026 per GB-hour × 2,000 GB × 1 hour = USD 0.052. If you release 500 GB of cold storage space and the actual usage is 1,500 GB from 20:00 to 21:00, the total fee for this hour is calculated as follows: USD 0.000026 per GB-hour × 1,500 GB × 1 hour = USD 0.039.

Note

The prices in this example are for reference only. The actual prices on the console prevail.

Enable tiered storage

Community-compatible edition

Log on to the Alibaba Cloud ClickHouse console. In the upper-left corner of the page, select the region where your cluster is deployed.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the target cluster.
In the left-side navigation pane, click Cold and hot layered storage.
Click Click to Enable Layered Storage.
In the dialog box that appears, click OK.

After you confirm the operation, the cluster restarts. The feature is enabled after the cluster state changes from Changing Configuration to Running.

Enterprise edition

Note

For Alibaba Cloud ClickHouse Enterprise Edition, you must first submit a ticket to add your account to the allowlist for this feature. Then, follow these steps to enable the feature for your cluster.

Log on to the Alibaba Cloud ClickHouse console. In the upper-left corner of the page, select the region where your cluster is deployed.
On the Clusters page, click the Enterprise Edition Clusters tab, and then click the ID of the target cluster.
In the left-side navigation pane, click Cold and hot layered storage.
Click Click to Enable Layered Storage.
In the dialog box that appears, configure the global TTL.
Click OK.

The feature is enabled after the cluster state changes to Running and the Tiered Storage page shows that the feature is Enabled.

After the feature is enabled, you can click the edit icon next to Parameter Value to adjust the global TTL. The new TTL takes effect in the next offload cycle (00:00 UTC on the following day).

Manage storage policies for the Community-compatible edition

Default storage policy

After you enable tiered storage of hot and cold data, the default storage policy includes the following parameters.

Parameter

Description

move_factor

When the available space on the hot data disk drops below this ratio, the system automatically moves the largest data part to the cold data disk.

The value of this parameter ranges from [0,1]. A value of 0 disables tiered storage based on disk capacity. The default value is 0.1, which indicates that data is automatically moved when the available space is less than 10%. You can also set a TTL-based tiered storage policy when you create a table.

Note

To view your disk space, see View disk space.

prefer_not_to_merge

Specifies whether to merge data on the cold data disk. Valid values:

true: Data is not merged. This is the default value.
false: Data is merged.

TTL-based storage policy

You can add a TTL clause to the default storage policy to automatically move all data older than a specified interval to the cold data disk.

Syntax

TTL <time_column> + INTERVAL <number> <unit> TO DISK 'cold_disk'

Parameters

Parameter

Description

time_column

A column of the Date or DateTime data type.

number unit

The time interval.

Common units include DAY, WEEK, MONTH, and YEAR.

Note

For more information about TTL clauses, see TTL for columns and tables.

Example

The following statement creates a table and moves all data older than 90 days to the cold data disk based on the date column.

CREATE TABLE ttl_test_tbl
(
    `f1` String,
    `f2` String,
    `f3` Int64,
    `f4` Float64,
    `date` Date
)
ENGINE = MergeTree()
PARTITION BY date
ORDER BY f1
TTL date + INTERVAL 90 DAY TO DISK 'cold_disk'
SETTINGS storage_policy = 'hot_to_cold';

Modify a TTL-based policy

You can change the Date or DateTime column and the time interval for a TTL-based tiered storage policy.

Precautions

Data impact:
- Existing data: By default, the new policy also applies to existing data, but this process is asynchronous and takes time. To apply the change immediately, run the ALTER TABLE ... MATERIALIZE TTL; statement after you modify the policy. If you do not want to change the storage policy for existing data, run the SET materialize_ttl_after_modify=0; statement before you modify the policy.
- New data is tiered according to the new policy.
After you modify a TTL-based storage policy, data that is already on the cold data disk is not automatically moved back to the hot data disk. To move the data, see Move data between hot and cold data disks.

Syntax

ALTER TABLE <table_name> ON CLUSTER default MODIFY TTL <time_column> + INTERVAL <number> <unit> TO DISK 'cold_disk';

Parameters

Parameter	Description
table_name	The table name.
time_column	The target Date or DateTime column.
number unit	The time interval. Common units include DAY, WEEK, MONTH, and YEAR.

Data management for the Community-compatible edition

Move data between hot and cold disks

Move data from a hot data disk to a cold data disk:

ALTER TABLE <table_name> ON CLUSTER default MOVE PARTITION <partition> TO DISK 'cold_disk';

Move data from a cold data disk to a hot data disk:
```
ALTER TABLE <table_name> ON CLUSTER default MOVE PARTITION <partition> TO DISK 'default';
```
Note
partition is the partition name, which you can find in the system.parts table.

View data on hot and cold disks

View data on a hot data disk:

SELECT * FROM system.parts WHERE database = '<db_name>' AND TABLE = '<tbl_name>' AND disk_name ='default' AND active = 1;

View data on a cold data disk:

SELECT * FROM system.parts WHERE database = '<db_name>' AND TABLE = '<tbl_name>' AND disk_name ='cold_disk' AND active = 1;

View disk space

After you enable tiered storage of hot and cold data, you can run the following statement to view disk space:

SELECT * FROM system.disks;

The following result is returned:

┌─name─────┬─────────path───────────────────────────┬─free_space───────────┬─total_space──────────┬─used_space───┬─keep_free_space───┬──type──┐
│ cold_disk│ /clickhouse/data/data/disks/cold_disk/ │ 18446744073709551615 │ 18446744073709551615 │ 115312080698 │   0               │   OSS  │
│ default  │ /clickhouse/data/data/                 │ 201663209472         │ 207083249664         │ 5420040192   │   0               │  local │
└──────────┴────────────────────────────────────────┴──────────────────────┴──────────────────────┴──────────────┴───────────────────┴────────┘

The following table describes the columns in the result set.

Parameter	Description
name	The disk name. cold_disk: the cold data disk. default: the hot data disk.
path	The data storage path on the disk.
free_space	The available space on the disk, in bytes. The available space on a cold data disk is not limited.
total_space	The total space on the disk, in bytes. The total space on a cold data disk is effectively unlimited.
used_space	The used disk space, in bytes.
keep_free_space	The reserved free space, in bytes.
type	The storage medium. OSS: Cold data is stored in Object Storage Service (OSS). local: Hot data is stored on a local disk.

FAQ

Q: Why is my data not being moved from the hot data disk to the cold data disk?

A: Whether data is moved to a cold data disk depends on the values of move_ttl_info.min and move_ttl_info.max in the system.parts table. You can run the following statement to query these values:

SELECT move_ttl_info.min, move_ttl_info.max FROM system.parts WHERE database = '<db_name>' AND table = '<tb_name>' AND disk_name = 'default'

If the current time is earlier than either of these values, the system does not move the data part. It is also possible that the system is processing or preparing to move the data, and the operation has not yet completed.

Prerequisites

Community-compatible edition

Enterprise edition

Background

Community-compatible edition

Enterprise edition

Precautions

Community-compatible edition

After January 1, 2024

Before January 1, 2024

Enterprise edition

Billing

Enable tiered storage

Community-compatible edition

Enterprise edition

Manage storage policies for the Community-compatible edition

Default storage policy

TTL-based storage policy

Syntax

Parameters

Example

Modify a TTL-based policy

Precautions

Syntax

Parameters

Data management for the Community-compatible edition

Move data between hot and cold disks

View data on hot and cold disks

View disk space

FAQ