AnalyticDB:ホットデータとコールドデータの階層化ストレージ

構文

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

パーティションテーブルのサブパーティション名を表示するには、psql で \d+ を実行します。

例

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

サブパーティション 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);

サブパーティション tiered_storage_partition_hdfs_1_prt_1 を 3 日後にコールドストレージに移動します。例えば、2023 年 7 月 17 日 09:00:00 に ALTER TABLE 文を実行すると、tiered_storage_partition_hdfs_1_prt_1 サブパーティションは 3 日後の 2023 年 7 月 20 日 09:00:00 にコールドストレージに移動されます。他のサブパーティションは影響を受けません。

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

テーブルのストレージステータスのクエリ

次のいずれかのメソッドを使用して、テーブルのストレージステータスをクエリできます。クエリは、コールドテーブルの場合は cold を、ホットテーブルの場合は hot を返します。

• メソッド 1：

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

• メソッド 2：

  SELECT pg_tiered_storage_relation_status(<table_oid>::bigint);

  SELECT oid FROM pg_class where relname='<table_name>'; を実行して、テーブルの OID を検索します。

構文

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

パーティションテーブルのサブパーティション名を表示するには、psql で \d+ を実行します。

例

• 例 1：サブパーティションをすぐにコールドストレージに移動する

  public スキーマに tiered_storage_partition_oss という名前のパーティションテーブルを作成します。

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

  説明

  この例では、tiered_storage_partition_oss_1_prt_1 と tiered_storage_partition_oss_1_prt_2 の 2 つのサブパーティションが作成されます。

  サブパーティション 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);

  サブパーティションをすぐにコールドストレージに移動します。

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

• 例 2：pg_cron を使用して、日次サブパーティションの変換をスケジュールする

  etl データベースに daily_log_details という名前の日次パーティションテーブルを作成します。

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

  10 日より古いサブパーティションをコールドストレージに変換するジョブを設定します。ジョブは 03:00 に実行され、etl_user によって実行されます。次の手順に従います。

  1. 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''';
         
         -- 期限切れのパーティションをフェッチします
         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. postgres データベースに接続し、文を実行します。

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

• 例 3：pg_cron を使用して、月次サブパーティションの変換をスケジュールする

  etl データベースに month_log_details という名前の月次パーティションテーブルを作成します。

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

  3 か月より古いサブパーティションをコールドストレージに変換するジョブを設定します。ジョブは毎月 1 日の 05:00 に実行され、etl_user によって実行されます。次の手順に従います。

  1. 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''';
         
         -- 期限切れのパーティションをフェッチします
         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. postgres データベースに接続し、文を実行します。

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

テーブルのストレージステータスのクエリ

次の文を実行して、テーブルのストレージステータスをクエリします。クエリは、コールドテーブルの場合は cold を、ホットテーブルの場合は hot を返します。

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