Hologres:Dynamic partitioning

Background information

The dynamic partitioning feature is optimized in different versions to provide more functionalities.

• Hologres V1.3 and later allow you to configure dynamic partitioning rules. The system runs scheduled tasks to create child partitioned tables in advance and delete expired child partitioned tables based on the dynamic partitioning rules.

• Hologres V1.3.37 and later support dynamic management of tiered storage of hot and cold data. This way, cold storage can be automatically used for data. This helps reduce storage costs. For more information, see Tiered storage of hot data and cold data.

• Hologres V2.1.11 and later allow you to configure columns of the DATE type as partition key columns.

• Hologres V2.2 and later allow you to configure the schd_start_time property when you configure dynamic partitioning for a table. You can use this property to specify the time when partitions are scheduled to be created, deleted, or stored in the cold storage tier.

Limits

• Hologres allows you to import data to a child table rather than a parent table.

  Note

  Realtime Compute for Apache Flink allows you to import data to a parent table in Hologres in real time. For more information, see Write data to a partitioned result table in Hologres in real time.

• Each partitioning rule can be used to create only one partitioned table.

• The PARTITION BY clause supports only list partitioning. The partition key must be a single column.

• If a partitioned table has a primary key, the partition key must be a subset of the primary key.

• You can configure the schd_start_time property only for parent partitioned tables but not child partitioned tables.

Configure dynamic partitioning

Syntax

You can configure dynamic partitioning for a partitioned table when you create the table. You can also configure dynamic partitioning for an existing partitioned table. The following sample code shows the syntax:

• Syntax supported in Hologres V2.1 and later:

  • Configure dynamic partitioning for a partitioned table when you create the table.

    -- Configure dynamic partitioning for a partitioned table when you create the table.
    CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
      {
       <column_name> <column_type> [ <column_constraints>, [...]]
       | <table_constraints>
       [, ...]
      }
    ])
    PARTITION BY LIST(<column_name>)
    WITH (
       auto_partitioning_enable = 'xxx',
       auto_partitioning_time_unit = 'xxx',
       auto_partitioning_time_zone = 'xxx',
       auto_partitioning_num_precreate = 'xxx',
       auto_partitioning_num_retention = 'xxx',
       auto_partitioning_num_hot='xxx',
       auto_partitioning_schd_start_time = 'xxx'
    );

  • Modify dynamic partitioning settings for an existing partitioned table.

    -- Modify dynamic partitioning settings for an existing partitioned table.
    ALTER TABLE [<schema_name>.]<table_name> SET (
       auto_partitioning_enable = 'xxx',
       auto_partitioning_time_unit = 'xxx',
       auto_partitioning_time_zone = 'xxx',
       auto_partitioning_num_precreate = 'xxx',
       auto_partitioning_num_retention = 'xxx',
       auto_partitioning_num_hot='xxx',
       auto_partitioning_schd_start_time = 'xxx'
    );
    

• Syntax supported in all Hologres versions:

  • Configure dynamic partitioning for a partitioned table when you create the table.

    -- Configure dynamic partitioning for a partitioned table when you create the table.
    BEGIN;
    CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
      {
       <column_name> <column_type> [ <column_constraints>, [...]]
       | <table_constraints>
       [, ...]
      }
    ])
    PARTITION BY LIST(<column_name>);
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_hot', 'xxx');
    CALL set_table_property ('[<schema_name>.]<table_name>', 'auto_partitioning.schd_start_time', 'xxx');
    COMMIT;

  • Modify dynamic partitioning settings for an existing partitioned table.

    -- Modify dynamic partitioning settings for an existing partitioned table.
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
    CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');

Parameter description

SELECT * FROM pg_timezone_names;

Naming conventions for child partitioned tables

The auto_partitioning.time_unit parameter for dynamic partitioning can be set to HOUR, DAY, MONTH, QUARTER, or YEAR. The name of a child partitioned table that is automatically created by using the dynamic partitioning feature consists of the name of its parent partitioned table and a time suffix, in the {parent_table}_{time_suffix} format. The time suffix follows a specific format and is generated based on the scheduled time and time unit for dynamic partitioning. The following table describes the relationships between time suffixes and the scheduled time for dynamic partitioning.

Examples

The following SQL statements provide examples on how to configure dynamic partitioning with auto_partitioning.time_unit set to DAY. In the examples, partitions are created for the next three days and historical partitions for the previous two days are retained based on the current date in the time zone of Asia/Shanghai.

The following table describes the execution results.

Scenarios

Retain a child partitioned table

By default, the system creates and deletes child partitioned tables based on the configurations of dynamic partitioning. Child partitioned tables are automatically deleted after they expire. You may want to retain the data in an important child partitioned table. For example, you want to retain the e-commerce data generated during Double 11 to perform year-on-year or period-over-period analysis. In such scenarios, Hologres allows you to add the keep_alive property to the child partitioned tables that you want to retain to prevent the tables from being automatically deleted.

• Syntax supported in Hologres V2.1 and later:

  -- Add the keep_alive property to a child partitioned table.
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');
  
  -- Remove the keep_alive property from a child partitioned table. After you remove the keep_alive property, the child partitioned table is deleted after the table expires.
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');
  

• Syntax supported in all Hologres versions:

  -- Add the keep_alive property to a child partitioned table.
  call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');
  
  -- Remove the keep_alive property from a child partitioned table. After you remove the keep_alive property, the child partitioned table is deleted after the table expires.
  call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');

Dynamically manage storage modes of partitioned tables

You can enable tiered storage of hot data and cold data for partitioned tables to reduce costs and optimize query performance in an efficient manner. For example, you can store data in the previous N historical partitions that are frequently accessed in the hot storage tier, and store data in the M partitions that are created earlier than the N partitions in the cold storage tier. This helps reduce storage costs. The partitions that are created earlier than the M and N partitions are dynamically deleted.

Create a partitioned table and configure dynamic partitioning for the table

In this example, a partitioned table with dynamic partitioning configured is created. A partition is created for each day. Data in the previous 7 historical partitions is stored in the hot storage tier, and data in the 23 historical partitions that are created earlier than the 7 partitions is stored in the cold storage tier. Partitions that are created earlier than the 30 historical partitions are deleted. Sample code:

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.

Modify a storage policy

You can use the auto_partitioning.num_hot parameter to specify the number of partitions in the hot storage tier. Take note that you cannot migrate a partition from the cold storage tier to the hot storage tier. In this example, a partitioned table is created on July 1, 2022. Sample code:

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 partitioned tables that are stored in the cold storage tier are not migrated to the hot storage tier. The following figure shows the configuration effect.

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

  Child partitioned tables that are stored in the hot storage tier are migrated to the cold storage tier. The following figure shows the configuration effect.

Convert a partitioned table that uses the cold storage mode into a partitioned table that has dynamic partitions

In this example, a partitioned table that uses the cold storage mode is converted into a partitioned table that contains dynamic partitions. Partitions that are created in the previous 7 days are stored in the hot storage tier. Procedure:

1. Prepare data.

   -- Create a partitioned table and set the storage mode of the table to cold storage.
   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. Convert the table partitions into dynamic partitions and store partitions that are created in the previous 7 days in the hot storage tier.

   begin;
   CALL set_table_property('tbl2', 'storage_mode', 'hot'); -- Set the storage mode of the parent partitioned table to hot storage.
   CALL set_table_property('tbl2_20230810', 'storage_mode', 'cold'); -- Set the storage mode of partitions that do not require storage mode conversion to cold storage.
   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;

Query dynamic partitioning configurations

You can execute the following SQL statement to query dynamic partitioning configurations for a table in a 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 table describes the parameters in the statement.

The following figure shows the query result.

Query the logs for creating and deleting child partitioned tables

The logs for creating and deleting child partitioned tables are not included in query logs. You can execute the following SQL statement to query the logs for creating and deleting child partitioned tables:

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

The following table describes the parameters in the statement.

The following figure shows the query result.

FAQ

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

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

-- Syntax in Hologres V2.1
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'
);

-- Syntax in 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;

If I enable dynamic partitioning for an existing partitioned table, are the existing child partitioned tables automatically deleted?

Hologres determines whether to delete a child partitioned table based on the table name. If the name of a child partitioned table follows the {parent_table}_{time_suffix} naming rule, the child partitioned table is automatically deleted after the table expires. Otherwise, the child partitioned table is not automatically deleted.

I execute SQL statements to create a partitioned table and configure dynamic partitioning for the table with the num_precreate parameter set to 3. However, a parent partitioned table is created but no child partitioned tables are created. Why?

By default, Hologres performs checks every 10 minutes on new tasks for which dynamic partitioning is configured. The child partitioned tables are created within 10 minutes. You can check the child partitioned tables later.