Hologres:Dynamic partitioning

Syntax

You can configure dynamic partitioning properties when you create a partitioned table or modify the properties of an existing one. The following syntax is used.

Parameters

SELECT * FROM pg_timezone_names;

Naming conventions for child partitioned tables

The auto_partitioning.time_unit parameter for a dynamically partitioned table can be set to HOUR, DAY, MONTH, QUARTER, or YEAR. The name of a newly created child partition consists of the parent table name and a time suffix, in the format {parent_table}_{time_suffix}. The time suffix is generated based on the scheduled time and the format template that corresponds to the specified time unit. The following table describes these relationships.

Examples

The following examples show how to pre-create partitions for the next three days, retain partitions for the last two days, and set the time zone to Asia/Shanghai. The time unit is DAY.

The logic for adding and deleting partitions is as follows:

Retain a specific child partitioned table

By default, the system automatically creates and deletes child partitioned tables based on the configured dynamic partitioning rules. Child partitioned tables that are outside the retention range are automatically deleted. In some scenarios, you may need to retain data in important partitions. For example, in an E-commerce scenario, you may need to retain data from previous Double 11 shopping festivals for year-over-year and month-over-month analysis. To retain a specific child partitioned table, you can add the keep_alive property to it. The following syntax is used.

• Syntax supported in Hologres V2.1 and later:

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

• Syntax supported in all Hologres versions:

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

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 last N historical partitions in hot storage to handle frequent queries. The M partitions before those can be stored in the cold layer to save costs. By combining this with the dynamic partitioning feature, you can also delete data that is older than all retained partitions.

Create a dynamic partitioned table

Example: Create one partition per day. Dynamically retain the last 7 historical partitions in hot storage and the 23 partitions before them in cold storage. Delete any partitions that are older than this range. The following sample code shows how to do this.

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 result.

Modify a storage policy

You can modify the hot storage policy by changing the value of the auto_partitioning.num_hot parameter. Note that if you modify this policy, child partitioned tables that are already in cold storage are not automatically moved back to hot storage. Assume that the current date is July 1, 2022, and you create the following partitioned table.

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;

The data distribution can change in the following two scenarios:

• Scenario 1: Expanding a partition policy based on a hot storage cutoff

  To change the number of hot partitions in the dynamic management policy to 4, run the following code:

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

  Because child partitioned tables that are already in cold storage are not automatically moved back to hot storage, the result is shown in the following figure:

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

  To set the number of hot partitions in the dynamic management policy to 2, run the following code:

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

  Child partitioned tables that are already in cold storage are not automatically moved to hot storage. However, data is still migrated from hot storage to cold storage. The effect of this change is shown in the following figure:

Convert a cold storage partitioned table to a dynamic partitioned table

For example, to convert a partitioned table with the cold storage property into a dynamically partitioned table and set the partitions from the last 7 days to hot storage, follow these steps.

1. Prepare the data.

   -- Specify the cold storage medium 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 dynamically partitioned table and set the partitions from 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 partitioning configuration and scheduling status

You can run the following SQL statement to query information about dynamically 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 are the query results.

View logs for creating and purging child partitioned tables

Query Log does not record logs for creating and purging child partitioned tables. You can 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 fields.

The query returns the following results.

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

You can run 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 existing child partitioned tables affected by the automatic cleanup logic?

Yes, they are. The system purges child partitioned tables based on their names. If a child partitioned table's name follows the {parent_table}_{time_suffix} naming convention, it is purged. Otherwise, it is not.

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

For a newly created dynamic partitioning task, the system checks for the task every 10 minutes by default. Therefore, the child partitioned tables are created within 10 minutes. You can check for the tables again after this period.