AnalyticDB:Tiered storage of hot and cold data

Syntax

ALTER TABLE <child_partition_name> SET ttl interval '<scheduling_interval>' move to storage_cold;

To view the subpartition names for a partitioned table, run \d+ in psql.

Example

Create a partitioned table named tiered_storage_partition_hdfs.

CREATE TABLE tiered_storage_partition_hdfs(a int,b int) distributed by (a) partition by range(a) (start(1) end(20) every(10));

Insert data into the subpartition tiered_storage_partition_hdfs_1_prt_1.

INSERT INTO tiered_storage_partition_hdfs_1_prt_1 values(1, 1), (2, 2), (3, 3), (4, 4);

Moves the subpartition tiered_storage_partition_hdfs_1_prt_1 to cold storage after three days. For example, if you run the ALTER TABLE statement at 09:00:00 on July 17, 2023, the tiered_storage_partition_hdfs_1_prt_1 subpartition is moved to cold storage three days later at 09:00:00 on July 20, 2023. Other Subpartitions are not affected.

ALTER TABLE tiered_storage_partition_hdfs_1_prt_1 SET ttl interval '3days' move to storage_cold;

Query the storage status of a table

You can use either of the following methods to query the storage status of a table. The query returns cold for a cold table and hot for a hot table.

• Method 1:

  SELECT pg_tiered_storage_relation_status('<table_name>'::regclass::oid::bigint);

• Method 2:

  SELECT pg_tiered_storage_relation_status(<table_oid>::bigint);

  Find the table OID by running SELECT oid FROM pg_class where relname='<table_name>';.

Syntax

SELECT pg_tiered_storage_move_table_to_storage_cold('<schema_name>', '<child_partition_name>');

To view the subpartition names for a partitioned table, run \d+ in psql.

Examples

• Example 1: Immediately move a subpartition to cold storage.

  Create a partitioned table named tiered_storage_partition_oss in the public schema.

  CREATE TABLE tiered_storage_partition_oss(a int,b int) DISTRIBUTED BY (a) PARTITION BY range(a) (start(1) end(20) every(10));

  Note

  This example creates two subpartitions: tiered_storage_partition_oss_1_prt_1 and tiered_storage_partition_oss_1_prt_2.

  Insert data into the subpartition tiered_storage_partition_oss_1_prt_1.

  INSERT INTO  tiered_storage_partition_oss_1_prt_1 VALUES(1, 1), (2, 2), (3, 3), (4, 4);

  Immediately move the subpartition to cold storage.

  SELECT pg_tiered_storage_move_table_to_storage_cold('public', 'tiered_storage_partition_oss_1_prt_1');

• Example 2: Use pg_cron to schedule daily subpartitions for conversion.

  Create a daily partitioned table named daily_log_details in the etl database.

  CREATE TABLE daily_log_details (id INT, log_message text, created_date character varying(64))
  PARTITION BY LIST (created_date)
  (
     PARTITION p20230601 VALUES ('20230601'),
     PARTITION p20230602 VALUES ('20230602'),
     PARTITION p20230603 VALUES ('20230603'),
     PARTITION p20230604 VALUES ('20230604'),
     PARTITION p20230605 VALUES ('20230605'),
     PARTITION p20230606 VALUES ('20230606'),
     PARTITION p20230607 VALUES ('20230607'),
     PARTITION p20230608 VALUES ('20230608'),
     PARTITION p20230609 VALUES ('20230609'),
     PARTITION p20230610 VALUES ('20230610'),
     PARTITION p20230611 VALUES ('20230611'),
     DEFAULT PARTITION others
  );

  Set a job to convert subpartitions older than 10 days to Cold Storage. The job runs at 03:00 and is executed by the etl_user. Follow these steps.

  1. Create a cleanup function in the etl database.

     CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_daily_table_to_cold_storage(schemaname text, tablename text) RETURNS void
     AS $$
     DECLARE
         fetch_overdue_partition_sql text;
         cold_storage_sql text;
         target record;
     BEGIN
         fetch_overdue_partition_sql := 'WITH targetpartitions AS
         (SELECT * FROM pg_partitions 
         WHERE tablename = $1
         AND schemaname = $2 
         AND partitionlevel = 1
         AND partitionisdefault = FALSE)
         SELECT partitiontablename FROM targetpartitions WHERE 
         to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMMDD'') <= current_date - INTERVAL ''10 days''';
         
         -- Fetch overdue partitions
         FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
            cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
            raise notice 'sql %', cold_storage_sql;
            EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
         END LOOP;
     END;
     $$
     LANGUAGE plpgsql;

  2. Connect to the postgres database and run the statement.

     SELECT cron.schedule('etl_daily_transfer_to_cold', '0 3 * * *', 'SELECT pg_tiered_storage_move_partition_daily_table_to_cold_storage(''public'', ''daily_log_details'');', 'etl', 'etl_user');

• Example 3: Use pg_cron to schedule monthly subpartitions for conversion.

  Create a monthly partitioned table named month_log_details in the etl database.

  CREATE TABLE month_log_details (id INT, log_message text, created_date character varying(64))
  PARTITION BY LIST (created_date)
  (
     PARTITION p202306 VALUES ('202306'),
     PARTITION p202307 VALUES ('202307'),
     PARTITION p202308 VALUES ('202308'),
     PARTITION p202309 VALUES ('202309'),
     PARTITION p202310 VALUES ('202310'),
     DEFAULT PARTITION others
  );

  Set a job to convert subpartitions older than 3 months to Cold Storage. The job runs at 05:00 on the first day of each month and is executed by the etl_user. Follow these steps.

  1. Create a cleanup function in the etl database.

     CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_table_to_cold_storage(schemaname text, tablename text) RETURNS void
     AS $$
     DECLARE
         fetch_overdue_partition_sql text;
         cold_storage_sql text;
         target record;
     BEGIN
         fetch_overdue_partition_sql := 'WITH targetpartitions AS
         (SELECT * FROM pg_partitions 
         WHERE tablename = $1
         AND schemaname = $2 
         AND partitionlevel = 1
         AND partitionisdefault = FALSE)
         SELECT partitiontablename FROM targetpartitions WHERE 
         to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMM'') <= current_date - INTERVAL ''3 months''';
         
         -- Fetch overdue partitions
         FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
            cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
            raise notice 'sql %', cold_storage_sql;
            EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
         END LOOP;
     END;
     $$
     LANGUAGE plpgsql;

  2. Connect to the postgres database and run the statement.

     SELECT cron.schedule('etl_month_transfer_to_cold', '0 5 1 * *', 'SELECT pg_tiered_storage_move_partition_table_to_cold_storage(''public'', ''month_log_details'');', 'etl', 'etl_user');

Query the storage status of a table

Run the following statement to query the storage status of a table. The query returns cold for a cold table and hot for a hot table.

SELECT pg_tiered_storage_table_status('<schema_name>', '<table_name>|<child_partition_name>')