AnalyticDB:Tiered storage for hot and cold data

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

CREATE TABLE tiered_storage_heap_oss (a int, b int) DISTRIBUTED BY(a) ;
INSERT INTO  tiered_storage_heap_oss SELECT random() * 1000,1 FROM generate_series(1,100);

Syntaxe

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

Vous pouvez exécuter \d+ dans psql pour afficher les noms des sous-partitions d'une table partitionnée spécifique.

Exemple

• Exemple 1 : Convertir immédiatement une sous-partition en partition froide.

  Créez une table partitionnée nommée tiered_storage_partition_oss dans le schéma public.

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

  Remarque

  Cet exemple crée deux sous-partitions : tiered_storage_partition_oss_1_prt_1 et tiered_storage_partition_oss_1_prt_2.

  Écrivez des données dans la sous-partition 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);

  Convertissez immédiatement la sous-partition en partition froide.

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

• Exemple 2 : Utiliser pg_cron pour planifier la conversion des sous-partitions quotidiennes.

  Créez une table partitionnée quotidienne nommée daily_log_details dans la base de données etl.

  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
  );

  Configurez une tâche qui s'exécute à 03:00 en tant qu'utilisateur etl_user pour convertir les sous-partitions âgées de plus de 10 jours en partitions froides. Suivez ces étapes :

  1. Créez une fonction de nettoyage dans la base de données etl.

     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. Connectez-vous à la base de données postgres et exécutez l'instruction de conversion.

     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');

• Exemple 3 : Utiliser pg_cron pour planifier la conversion des sous-partitions mensuelles.

  Créez une table partitionnée mensuelle nommée month_log_details dans la base de données etl.

  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
  );

  Configurez une tâche qui s'exécute à 05:00 en tant qu'utilisateur etl_user pour convertir les sous-partitions âgées de plus de 3 mois en partitions froides. Suivez ces étapes :

  1. Créez une fonction de nettoyage dans la base de données etl.

     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. Connectez-vous à la base de données postgres et exécutez l'instruction de conversion.

     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');

Interroger le statut de stockage d'une table

Exécutez l'instruction suivante pour interroger le statut de stockage d'une table. La requête renvoie cold pour une table froide et hot pour une table chaude.

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