AnalyticDB:Tiered storage of hot and cold data

At table creation

Specify the policy using the storage_policy parameter in your CREATE TABLE statement:

CREATE TABLE your_table (
    ...
)
PARTITION BY ...
PROPERTIES (
    "storage_policy" = "MIXED",   -- HOT, COLD, or MIXED
    "hot_partition_count" = "5"   -- Required when storage_policy = MIXED
);

For an existing table

Use ALTER TABLE to change the storage policy. After running this statement, you can track migration progress—see Query the storage policy change progress.

-- Switch to mixed storage with 5 hot partitions
ALTER TABLE your_table
SET PROPERTIES (
    "storage_policy" = "MIXED",
    "hot_partition_count" = "5"
);

For the full syntax reference, see CREATE TABLE and ALTER TABLE.

When a new partition is inserted

All partitions are re-sorted to keep exactly N hot partitions. The partition with the smallest key value is demoted from hot to cold.

Example: hot_partition_count is 5. A new partition 20241226 (the largest key value) is inserted. After a BUILD job, the system promotes 20241226 to hot and moves 20241221 (the previous smallest hot partition) to cold.

When you change the hot partition count

• Increase from N to M (M > N): M - N cold partitions (those with the largest key values) are promoted to hot.

• Decrease from N to M (M < N): N - M hot partitions (those with the smallest key values) are demoted to cold.

Example — increase from 5 to 6: Partition 20241220 (largest cold partition key) moves from cold to hot.

Example — decrease from 5 to 4: Partition 20241221 (smallest hot partition key) moves from hot to cold.

Query for all tables

SELECT * FROM information_schema.table_usage;

Query for a specific table

SELECT * FROM information_schema.table_usage
WHERE table_schema = '<schema_name>' AND table_name = '<table_name>';

Response parameters

Usage notes:

• All size values (rt_*, hot_*, cold_*) change as INSERT, UPDATE, DELETE, and BUILD operations run.

• If hot_total_size and cold_total_size are both 0 after writing data, the data is synchronized in real time. rt_total_size reflects the current size. Run a BUILD statement to convert real-time data to historical data, after which hot_total_size and cold_total_size are populated.

• The configured hot_partition_count specifies the number of hot partitions per shard. The queried hot_partition_count is the union across all shards and may be larger if partition distribution differs between shards.

Example — queried `hot_partition_count` vs. configured value:

Table A has two shards and hot_partition_count is configured as 2.

• Shard 1: P4, P5 are hot; P1, P2, P3 are cold.

• Shard 2: P3, P4 are hot; P1, P2 are cold.

The queried value is the union: {P4, P5} ∪ {P3, P4} = {P3, P4, P5}, so hot_partition_count returns 3.

Query for all tables

SELECT * FROM information_schema.storage_policy_modify_progress;

Query for a specific table

SELECT * FROM information_schema.storage_policy_modify_progress
WHERE table_schema = '<schema_name>' AND table_name = '<table_name>';

Response parameters