AnalyticDB:Automatic data migration from hot storage to cold storage

Step 1: Create a partitioned table with a migration policy

Use the tiered_storage_cooldown_policy parameter in the WITH clause to attach a policy at table creation time:

CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition, ...)
[WITH(tiered_storage_cooldown_policy='XXX')]
PARTITION BY {LIST|RANGE} (PARTITION columns)
[distribution_options]

For other CREATE TABLE parameters, see SQL syntax.

HPN policy example — retain the 3 partitions with the largest partition key values:

CREATE TABLE tiered_storage_partition1 (a INT, b DATE)
  WITH(tiered_storage_cooldown_policy='HPN:3')
  PARTITION BY LIST (b)
  (
    VALUES ('2024-06-10'),
    VALUES ('2024-06-07'),
    VALUES ('2024-06-06')
  );

TTL policy example — retain partitions for 2 days:

CREATE TABLE tiered_storage_partition1 (a INT, b DATE)
  WITH(tiered_storage_cooldown_policy='TTL:2')
  PARTITION BY LIST (b)
  (
    VALUES ('2024-06-10'),
    VALUES ('2024-06-07'),
    VALUES ('2024-06-06')
  );

Step 2: Modify or remove a policy

To change or remove a migration policy on an existing table, use ALTER TABLE:

-- Change the policy
ALTER TABLE test_tbl SET(tiered_storage_cooldown_policy='HPN:5');

-- Remove the policy
ALTER TABLE test_tbl SET(tiered_storage_cooldown_policy='');

HPN policy

An HPN policy keeps the M partitions with the largest partition key values in hot storage. Partitions are sorted in lexicographical order by partition key value.

• The DEFAULT partition is the last to move to cold storage.

• The NULL partition moves to cold storage before the DEFAULT partition.

Format: HPN:M

• HPN is case-sensitive.

• M must be a non-negative integer.

• No spaces are allowed anywhere in the value.

TTL policy

A TTL policy moves partitions to cold storage when the difference between their partition key value (or upper range value) and the current date exceeds the retention period.

• For RANGE partitions: the upper range value is compared to the current date.

• For LIST partitions: the partition key value is compared to the current date.

• DEFAULT and NULL partitions are never moved to cold storage by a TTL policy.

Example: If today is 2024-08-10 and the retention period is 8 days, all partitions with key values of 2024-08-02 or earlier are moved to cold storage.

Format: TTL:N [YEAR | MONTH | DAY]

• N must be a non-negative integer.

• YEAR, MONTH, and DAY are case-sensitive. The default unit is DAY.

• A single space is required between N and the unit.

• No trailing spaces are allowed.

Maximum concurrent tasks

SHOW tiered_storage.adb_tiered_storage_max_worker;

Controls how many partitions can be migrated to cold storage simultaneously. A higher value speeds up migration when multiple tables have partitions to move, but does not change the migration speed for any single table. Setting this too high may cause excessive CPU usage during migration.

Scheduler trigger interval

SHOW tiered_storage.adb_tiered_storage_worker_launch_interval;

Sets how often (in seconds) the scheduler checks for partitions to migrate. A shorter interval provides faster response to policy changes; a longer interval reduces system overhead.

Scheduling window

SHOW tiered_storage.adb_worker_time_window_str;

Restricts migration to a daily time window. Example: 02:00-04:15. Invalid values are ignored. Tasks already running when the window closes continue until they finish.

HPN policy: keep 2 monthly partitions hot

This example creates a range-partitioned table partitioned by month. With HPN:2, the 2 most recent monthly partitions stay in hot storage and older ones move to cold storage.

CREATE TABLE tiered_storage_partition4 (a INT, b DATE)
  WITH(tiered_storage_cooldown_policy='HPN:2')
  PARTITION BY RANGE(b)
  (
    START ('2023-11-01'::date) END ('2023-12-01'::date),
    START ('2023-12-01'::date) END ('2024-01-01'::date),
    START ('2024-01-01'::date) END ('2024-02-01'::date),
    START ('2024-02-01'::date) END ('2024-03-01'::date)
  );

The scheduler sorts partitions by the b column (DATE type) and keeps the 2 with the largest values in hot storage. To ensure new partitions are treated as hot when the scheduler runs at 02:00, set the scheduling window to 02:00-04:00 and add new partitions before 02:00 on the first day of each month.

TTL policy: retain 90 days of data

This example moves partitions older than 90 days to cold storage.

CREATE TABLE tiered_storage_partition1 (a INT, b DATE)
  WITH(tiered_storage_cooldown_policy='TTL:90')
  PARTITION BY LIST (b)
  (
    VALUES ('2024-06-10'),
    VALUES ('2024-06-09'),
    VALUES ('2024-06-08'),
    VALUES ('2024-06-07'),
    VALUES ('2024-06-06'),
    ...    -- Replace with actual partition key values.
    VALUES ('2023-11-01')
  );

CREATE TABLE tiered_storage_partition3 (a INT, b DATE)
  WITH(tiered_storage_cooldown_policy='TTL:90')
  PARTITION BY RANGE(b)
  (
    START ('2023-11-01'::date) END ('2023-12-01'::date),
    START ('2023-12-01'::date) END ('2024-01-01'::date),
    START ('2024-01-01'::date) END ('2024-02-01'::date),
    ...  -- Replace with actual partition range values.
    START ('2024-05-01'::date) END ('2024-06-01'::date)
  );

For LIST partitions, the scheduler compares the partition key value to the current date. For RANGE partitions, it uses the upper range value. Partitions more than 90 days old move to cold storage, ensuring hot data is retained for 90 days.

Can I configure scheduled hot-to-cold migration for a non-partitioned table?

Non-partitioned tables don't support the automatic migration policies described in this topic, but you can use the pg_cron extension to schedule manual migration. For details, see Tiered storage of hot and cold data.

Can I base a migration policy on a field that isn't the partition key?

No. Automatic migration policies only operate on partition keys. For TTL policies, the retention period is measured against the partition key value or upper range value, not other columns.

Where is cold data stored?

Cold data moves to an Object Storage Service (OSS) bucket that AnalyticDB for PostgreSQL creates automatically. The bucket is region-specific and uses locally redundant storage (LRS). No storage path configuration is needed.