AnalyticDB：Penyimpanan bertingkat data hot dan cold

Sintaksis

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

Untuk melihat nama subpartisi dari tabel partisi, jalankan \d+ di psql.

Contoh

Buat tabel partisi bernama 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));

Masukkan data ke subpartisi 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);

Pindahkan subpartisi tiered_storage_partition_hdfs_1_prt_1 ke penyimpanan dingin setelah tiga hari. Misalnya, jika Anda menjalankan pernyataan ALTER TABLE pada pukul 09.00.00 tanggal 17 Juli 2023, subpartisi tiered_storage_partition_hdfs_1_prt_1 akan dipindahkan ke penyimpanan dingin tiga hari kemudian pada pukul 09.00.00 tanggal 20 Juli 2023. Subpartisi lain tidak terpengaruh.

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

Kueri status penyimpanan tabel

Anda dapat menggunakan salah satu metode berikut untuk menanyakan status penyimpanan tabel. Kueri akan mengembalikan cold untuk cold table dan hot untuk hot table.

• Metode 1:

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

• Metode 2:

  SELECT pg_tiered_storage_relation_status(<table_oid>::bigint);

  Temukan OID tabel dengan menjalankan SELECT oid FROM pg_class where relname='<table_name>';.

Sintaksis

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

Untuk melihat nama subpartisi dari tabel partisi, jalankan \d+ di psql.

Contoh

• Contoh 1: Segera pindahkan subpartisi ke penyimpanan dingin.

  Buat tabel partisi bernama tiered_storage_partition_oss di skema public.

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

  Catatan

  Contoh ini membuat dua subpartisi: tiered_storage_partition_oss_1_prt_1 dan tiered_storage_partition_oss_1_prt_2.

  Masukkan data ke subpartisi 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);

  Segera pindahkan subpartisi ke penyimpanan dingin.

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

• Contoh 2: Gunakan pg_cron untuk menjadwalkan konversi subpartisi harian.

  Buat tabel partisi harian bernama daily_log_details di database 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
  );

  Atur pekerjaan untuk mengonversi subpartisi yang lebih dari 10 hari ke Penyimpanan Dingin. Pekerjaan dijalankan pada pukul 03.00 dan dieksekusi oleh etl_user. Ikuti langkah-langkah berikut.

  1. Buat fungsi pembersihan di database 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. Hubungkan ke database postgres dan jalankan pernyataan tersebut.

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

• Contoh 3: Gunakan pg_cron untuk menjadwalkan konversi subpartisi bulanan.

  Buat tabel partisi bulanan bernama month_log_details di database 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
  );

  Atur pekerjaan untuk mengonversi subpartisi yang lebih dari 3 bulan ke Penyimpanan Dingin. Pekerjaan dijalankan pada pukul 05.00 pada hari pertama setiap bulan dan dieksekusi oleh etl_user. Ikuti langkah-langkah berikut.

  1. Buat fungsi pembersihan di database 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. Hubungkan ke database postgres dan jalankan pernyataan tersebut.

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

Kueri status penyimpanan tabel

Jalankan pernyataan berikut untuk menanyakan status penyimpanan tabel. Kueri akan mengembalikan cold untuk cold table dan hot untuk hot table.

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