Hologres:Tiered storage of hot data and cold data

You can configure the storage_mode parameter in the SET_TABLE_PROPERTY statement to specify a storage mode for a non-partitioned table. Valid values of this parameter:

• hot

• cold

For example, you can create a table named tbl1 and set the storage mode of the table to cold storage. Sample statements:

-- Specify the storage mode as cold storage when you create the table.
BEGIN;
CREATE TABLE tbl1 (
 "id" int NOT NULL,
 "name" text NOT NULL
);
CALL set_table_property('tbl1', 'storage_mode', 'cold');
COMMIT;

Hologres allows you to change the storage mode after you create the table. For example, you can change the storage mode of the tbl1 table to hot storage. The system asynchronously migrates data to the hot storage tier. Sample statements:

-- Change the storage mode to hot storage after you create the table.
CALL set_table_property('tbl1', 'storage_mode', 'hot');

You can specify the SET_TABLE_PROPERTY parameter to configure the storage mode for a partitioned table. Child tables inherit the storage mode of their parent tables by default. You can also specify storage modes for the child tables separately. Valid values:

• hot

• cold

For example, you can set the storage mode of the parent table named tbl2 to cold storage. When you write data to the child table named tbl2_v1 of the parent table, the data is written to the cold storage tier. Sample statements:

-- Specify the storage mode as cold storage when you create the table.
BEGIN;
CREATE TABLE tbl2(
  c1 text not null,
  c2 text
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl2', 'storage_mode', 'cold');
create table tbl2_v1 partition of tbl2 for values in('v1');
COMMIT;

Hologres allows you to change the storage mode after you create the table. For example, you can change the storage mode of the tbl2 parent table to hot storage. The system asynchronously migrates data in all child tables of this parent table to the hot storage tier. Sample statements:

-- Change the storage mode to hot storage after you create the table.
CALL set_table_property('tbl2', 'storage_mode', 'hot');

For example, you can change the storage mode of a child table to cold storage by setting the storage_mode parameter to cold for the child table. Sample statements:

-- Create child tables named tbl2_v2 and tbl2_v3 for the tbl2 parent table.

create table tbl2_v2 partition of tbl2 for values in('v2');
create table tbl2_v3 partition of tbl2 for values in('v3');

-- Query the storage mode of the child tables. The child tables inherit the storage mode of their parent table by default.

SELECT * from hg_table_storage_status('public', 'tbl2');

-- Change the storage mode of the tbl2_v3 child table to cold storage.

CALL set_table_property('tbl2_v3', 'storage_mode', 'cold');
                

Create a partitioned table. The default storage mode of the table is hot storage. Create a partition every day. Configure a storage rule to retain the most recent seven partitions in the hot storage tier, retain the other 23 partitions that were generated in the last 30 days in the cold storage tier, and delete earlier partitions. The following sample code shows the configuration of the partitioned table:

BEGIN;
CREATE TABLE tbl2(
  c1 text not null,
  c2 text
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl2', 'auto_partitioning.enable', 'true');
CALL set_table_property('tbl2', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property('tbl2', 'auto_partitioning.num_precreate', '3');
CALL set_table_property('tbl2', 'auto_partitioning.num_hot', '7');
CALL set_table_property('tbl2', 'auto_partitioning.num_retention', '30');
COMMIT;

The following figure shows the configuration result.

To change the number of partitions in the hot storage tier, change the value of the auto_partitioning.num_hot parameter. After the change is complete, child tables that are already stored in the cold storage tier are not immediately migrated to the hot storage tier. The following sample code shows the DDL statements if the current date is July 1, 2022:

BEGIN;
CREATE TABLE tbl_p(
  c1 text not null,
  c2 text
)
PARTITION BY LIST(c2);
CALL set_table_property('tbl_p', 'auto_partitioning.enable', 'true');
CALL set_table_property('tbl_p', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property('tbl_p', 'auto_partitioning.num_precreate', '3');
CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '3');
CALL set_table_property('tbl_p', 'auto_partitioning.num_retention', '10');
COMMIT;

Partition storage may be changed in the following scenarios:

• Scenario 1: Increase the number of partitions in the hot storage tier

  To increase the number of partitions in the hot storage tier to 4, execute the following statement:

  CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '4');

  Child tables that are already stored in the cold storage tier are not immediately migrated to the hot storage tier. The following figure shows the configuration result.

• Scenario 2: Decrease the number of partitions in the hot storage tier

  To decrease the number of partitions in the hot storage tier to 2, execute the following statement:

  CALL set_table_property('tbl_p', 'auto_partitioning.num_hot', '2');

  The data that needs to be migrated from the hot storage tier to the cold storage tier is immediately migrated. The following figure shows the configuration result.

To retain the storage mode of specific partitions, set the keep_storage parameter to true for the partitions. In most cases, you can retain a partition in the hot storage tier or migrate a partition that is stored in the cold storage tier to the hot storage tier. For example, you can execute the following statements to retain the data generated on November 11, 2021 in a partition in the hot storage tier to ensure query performance:

call set_table_property('tbl1_20211111', 'keep_storage', 'true');
call set_table_property('tbl1_20211111', 'storage_mode', 'hot');

To restore dynamic management of the partition, execute the following statement:

call set_table_property('tbl1_20211111', 'keep_storage', 'false');

To query the partitions that are retained in the database, execute the following statement:

SELECT
  table_namespace as schema_name
  ,table_name
FROM hologres.hg_table_properties
WHERE property_key='keep_storage'
AND property_value='true';

You can call the hg_table_storage_status function to query the storage status of data in a table. The returned result of the hg_table_storage_status function includes the storage size of user data, not the storage size of metadata. Sample statements:

SELECT * from hg_table_storage_status('<schema_name>', '<table_name>');

The following table describes columns in the result returned by the function.

Sample statements:

-- Query the storage status of data in a non-partitioned table.
SELECT * from hg_table_storage_status('public', 'tbl1');-- The returned data size is in bytes.
 table_name | hot_size |   cold_size   | status
------------+----------+---------------+--------
 tbl1       |  145643  |      3685     | transferring

-- Query the storage status of data in a partitioned table.
SELECT * from hg_table_storage_status('public', 'tbl2');-- The returned data size is in bytes.
   table_name    | hot_size | cold_size | status
-----------------+----------+-----------+--------
 tbl2_2022062222 |        0 |         0 | hot
 tbl2_2022062221 |     1125 |         0 | hot
 tbl2_2022062220 |     1245 |         0 | hot
 tbl2_2022062219 |     1358 |         0 | hot
 tbl2_2022062218 |        0 |      1875 | cold
 tbl2_2022062217 |        0 |      1143 | cold
 tbl2_2022062216 |        0 |      1299 | cold

Query the access frequency of all partitions:

select parent_table_name,count(*) as partition_cnt,
sum(hot_storage_size)/1024/1024/1024 as hot_size_gb
from hologres.hg_table_info
where type = 'PARTITION' and collect_time::DATE = CURRENT_DATE - interval '1 day'
group by parent_table_name
order by hot_size_gb desc;

Query the access frequency of the specified partitions:

select a.table_name,
(a.total_read_count - b.total_read_count) as read_count,
(a.total_write_count - b.total_write_count) as write_count,
a.hot_storage_size
from (select *
      from hologres.hg_table_info
      where type = 'PARTITION'
      and parent_table_name = '${p_table_name}'
      and collect_time::DATE = CURRENT_DATE - interval '1 day') a
join
(select *
     from hologres.hg_table_info
     where type = 'PARTITION'
     and parent_table_name = '${p_table_name}'
     and collect_time::DATE = CURRENT_DATE - interval '${days} day') b
on a.table_name = b.table_name
order by table_name desc;