Hologres:Dynamic partition management

Syntax description

You can configure dynamic partition management properties when you create a partitioned table or modify them after the table is created. The syntax is as follows.

Parameter description

SELECT * FROM pg_timezone_names;

Table name generation rules

When the auto_partitioning.time_unit parameter is set to HOUR, DAY, MONTH, QUARTER, or YEAR, dynamic partitioning uses the parent table name plus a time suffix as the name for newly created partitions. The format is {parent_table}_{time_suffix}. The time suffix is generated based on the scheduled time for automatic partitioning and the format template corresponding to the time unit. The specific mappings are as follows.

Examples

The following example shows how to set the time unit to DAY, pre-create partitions for the next 3 days, retain historical partitions for the last 2 days, and set the time zone to Asia/Shanghai.

The logic for adding and removing partitions is as follows:

Retain specific child partitions

By default, the system automatically creates and deletes child partitions based on the configured dynamic partitioning rules. Child partitions outside the retention range are automatically deleted. However, in some scenarios, you may need to retain data in important partitions. For example, in an e-commerce scenario, you might need to retain data from the Double 11 shopping festival each year for year-over-year and month-over-month analysis. You can retain a specific child partition by adding the keep_alive property to the table. The syntax is as follows.

• Syntax supported from V2.1:

  -- Retain a partition
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');
  
  -- Stop retaining a partition: After the retention property is removed, dynamic partition management immediately triggers a purge of expired data.
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');
  

• Syntax supported in all versions:

  -- Retain a partition
  CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');
  
  -- Stop retaining a partition: After the retention property is removed, dynamic partition management immediately triggers a purge of expired data.
  CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');

  Note

  Replace table_name with the actual name of the child partition.

Dynamically manage the storage medium for partitioned tables

When you work with partitioned tables, using tiered storage for hot and cold data can effectively balance cost and performance. For example, you can dynamically retain the N most recent historical partitions in hot storage to meet frequent query demands, while storing the next M partitions in cold storage to save costs. You can implement this scenario using the dynamic partition management feature to delete data that is older than N+M partitions.

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;

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;

Change a cold storage partitioned table to a dynamic partitioned table

For example, to change a partitioned table with the cold storage property to a dynamic partitioned table and set the partitions for the last 7 days to hot storage, use the following method.

1. Data preparation.

   -- Specify the cold storage medium as the storage policy when creating 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_20230808 PARTITION OF tbl2 FOR VALUES IN('20230808');
   CREATE TABLE tbl2_20230809 PARTITION OF tbl2 FOR VALUES IN('20230809');
   CREATE TABLE tbl2_20230810 PARTITION OF tbl2 FOR VALUES IN('20230810');
   CREATE TABLE tbl2_20230817 PARTITION OF tbl2 FOR VALUES IN('20230817');
   COMMIT;

2. Modify the table to be a dynamic partitioned table and set the partitions for the last 7 days to hot storage.

   BEGIN;
   CALL set_table_property('tbl2', 'storage_mode', 'hot'); -- Set the parent table to hot.
   CALL set_table_property('tbl2_20230810', 'storage_mode', 'cold'); -- Set partitions that do not need to be moved to hot storage to cold.
   CALL set_table_property('tbl2_20230809', 'storage_mode', 'cold');
   CALL set_table_property('tbl2_20230808', 'storage_mode', 'cold');
   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', '10');
   COMMIT;

View dynamic partition configurations and scheduling status

Run the following SQL statement to query information about the dynamic partitioned tables and their configurations in the current database.

SELECT
    nsp_name AS schema_name,
    tbl_name AS table_name,
    ENABLE,
    time_unit,
    time_zone,
    num_precreate,
    num_retention,
    b.usename AS create_user,
    cret_time,
    schd_start_time,
    options
FROM
    hologres.hg_partitioning_config AS a
    LEFT JOIN pg_user AS b ON a.cret_user = b.usesysid;

The following describes the fields.

The following result is returned.

View logs for creating and purging child partitions

The Query Log does not record logs for creating and purging child partitions. Run the following SQL statement to query these logs.

SELECT                           
    relname,
    relowner,
    schdtime,
    trigtime,
    status,
    message,
    precreate,
    discard
FROM
    hologres.hg_partitioning_log 

The following describes the field names listed above.

The following result is returned.

How do I enable dynamic partitioning for an existing partitioned table?

Use the following SQL statements to enable dynamic partitioning for an existing partitioned table.

-- SQL example for Hologres V2.1 and later
ALTER TABLE auto_part_old SET (
   auto_partitioning_enable = 'true',
   auto_partitioning_time_unit = 'HOUR',
   auto_partitioning_time_zone = 'PRC',
   auto_partitioning_num_precreate = '4',
   auto_partitioning_num_retention = '-1',
   auto_partitioning_num_hot = '-1'
);

-- SQL example for all Hologres versions
BEGIN;
CALL set_table_property('auto_part_old', 'auto_partitioning.enable', 'true');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_unit', 'HOUR');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_zone', 'PRC');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_precreate', '4');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_retention', '-1');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_hot', '-1');
COMMIT;

After I enable dynamic partitioning for an existing partitioned table, are the historical child partitions affected by the automatic purge logic?

The system purges child partitions based on their names. If a child partition's name matches the {parent_table}_{time_suffix} naming convention, it will be purged. Otherwise, it will not be purged.

I created a dynamic partitioned table and set num_precreate to 3. The parent table was created successfully after I ran the SQL statement, but the three child partitions were not created. Why?

For the initial task of creating dynamic partitions, the system checks every 10 minutes by default. Therefore, the child partitions will be created within 10 minutes. Check again later.