云原生数据仓库AnalyticDB PostgreSQL版：数据存储冷热分层

6.0版实例

• 支持将单个热表转为冷表。

• 支持将分区表的部分子分区转为冷分区，暂不支持将整个分区表父表转为冷表。

• 热表或者热分区转冷会建立临时表存放临时数据，转冷结束会自动清除。请确保各个segment节点上磁盘剩余空间大于当前正在转冷的热表空间之和。

• 热表转为冷表后，原表上关联的主键、索引、序列、rule、注释等会自动被删除，且无法找回。

• 暂不支持将冷表或者冷分区直接转热，可以通过CREATE TABLE AS SELECT的方式将冷数据存储至本地磁盘。

• 冷表或者冷分区的数据处于只读状态，不支持修改（写入、删除、更新），或者任何DDL操作（ALTER COLUMN、DROP COLUMN等）。冷表支持删除（DROP TABLE）。

• 冷表的底层存储格式类似于AOCS表，自带压缩，所占空间会与原表大致相等。

• 冷表不支持索引。

7.0版实例

• 支持将单个热表转为冷表。

• 支持将分区表的部分子分区转为冷分区，暂不支持将整个分区表父表转为冷表。

• 热表转为冷表后，原表上关联的主键、索引、序列、rule、注释等会自动被删除，且无法找回。

• 暂不支持将冷表或者冷分区直接转热，可以通过CREATE TABLE AS SELECT的方式将冷数据存储至本地磁盘。

• 冷表或者冷分区的数据允许读取和追加写入，不支持修改（删除和更新），或者任何DDL操作（ALTER COLUMN、DROP COLUMN等）。冷表支持删除（DROP TABLE）。

• 对于带主键或者唯一索引的分区表，其子表暂不支持转为冷表。带有普通类型的索引的分区表则不受限制，其子表可以转化为冷表。

• 冷表在OSS所占空间会与原表大致相等或略小。

• 冷表不支持索引。

6.0版实例

普通表整表转冷

语法

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

示例

创建一个普通表tiered_storage_heap并插入数据。

CREATE TABLE tiered_storage_heap (a int, b int);
INSERT INTO tiered_storage_heap SELECT random() * 1000,1 FROM generate_series(1,1000);

• 示例一：指定三天后（3days）将普通表tiered_storage_heap转冷。例如，2023年07月17日09:00:00执行ALTER TABLE操作，那么三天后（2023年07月20日09:00:00）会将tiered_storage_heap整表转冷。

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

• 示例二：指定任意时间点（2023年07月28日 16:53:58）将普通表tiered_storage_heap转冷。

  ALTER TABLE tiered_storage_heap SET ttl '2023-07-28 16:53:58'::Timestamp move to storage_cold;

• 示例三：将转冷时间点设为过去的时间点，可以立刻对表进行转冷。

  • 指定转冷时间点为三天前（-3days）。执行如下命令，对表立刻转冷。

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

  • 指定过去的具体时间点。若当前时间为2023年07月17日16:53:58，您可以执行如下命令，对表立刻转冷。

    ALTER TABLE tiered_storage_heap SET ttl '2022-07-16 16:53:58'::Timestamp move to storage_cold;

分区表按子分区转冷

语法

ALTER TABLE <分区子表名> 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转冷。例如，2023年07月17日09:00:00执行ALTER TABLE操作，那么三天后（2023年07月20日09:00:00）会将tiered_storage_partition_hdfs_1_prt_1子分区整表转冷，其他分区均不会有任何迁移操作。

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

查询指定表的冷热状态

您可以选择任意一种方式查询表的冷热状态。冷表返回cold，热表返回hot。

• 方式一：

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

• 方式二：

  SELECT pg_tiered_storage_relation_status(<表的oid>::bigint);

  通过SELECT oid FROM pg_class where relname='<table_name>';查询表的oid。

7.0版实例

普通表整表转冷

语法

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

示例

在public的Schema中创建一个普通表tiered_storage_heap_oss并插入数据。

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

• 示例一：整表立即转冷。

  执行以下语句，将普通表整表立即转冷。

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

• 示例二：使用pg_cron设置整表定时转冷。

  假设执行用户为etl_user，将数据库etl中的普通表tiered_storage_heap_oss，设置在次日凌晨1点将普通表转冷。连接到postgres数据库，执行以下语句。

  SELECT cron.schedule('etl_table_transfer_to_cold', '0 1 * * *', 'SELECT pg_tiered_storage_move_table_to_storage_cold('public', 'tiered_storage_heap_oss');', 'etl', 'etl_user');

  次日凌晨1点后检查转冷成功后，可执行以下语句，删除该定时任务。

  SELECT cron.unschedule(<定时任务ID>);

  说明

  定时任务ID为创建任务时自动生成的，可以通过查看cron.job表的jobid字段查看。

分区表按子分区转冷

语法

SELECT pg_tiered_storage_move_table_to_storage_cold('<schema_name>', '<分区子表名>');

您可以使用psql执行\d+查看具体分区表的子表名。

示例

• 示例一：对分区子表立即转冷。

  在public的Schema中创建一个分区表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));

  说明

  本示例会生成2个分区子表tiered_storage_partition_oss_1_prt_1和tiered_storage_partition_oss_1_prt_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');

• 示例二：使用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天前的分区子表转冷，执行用户为etl_user，转冷执行时间为3点。按照如下步骤操作。

  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''';
         
         -- 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. 连接到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');

• 示例三：使用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个月前的分区子表转冷，执行用户为etl_user，转冷执行时间为5点。按照如下步骤操作。

  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''';
         
         -- 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. 连接到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>|<分区子表名>')

6.0版实例

当具备完整备份时，可以恢复到任意时间点的状态，恢复时间点的冷热状态与备份时刻一致。

7.0版实例

当具备完整备份时，可以恢复到指定时间点的状态。恢复时间点的冷热状态与备份时刻一致。AnalyticDB PostgreSQL 7.0版存在如下限制：

• 如果数据表转冷后无数据写入，可以恢复到任意时间点的状态。

• 如果数据表转冷后有数据写入，可以恢复到转冷前任何备份点的状态，如果恢复到冷存状态，暂时只能恢复到冷表最后一次写入对应的状态。