Hologres: Dynamic Partition Management

Syntax

You can set automatic partitioning properties when creating a partitioned table or for an existing one.

Parameters

SELECT * FROM pg_timezone_names;

Child table naming rules

When you set the auto_partitioning.time_unit property to HOUR, DAY, MONTH, QUARTER, or YEAR, Hologres names new child tables by appending a time suffix to the parent table name. The format is {parent_table}_{time_suffix}. The time suffix is generated based on the automatic partitioning schedule and the format template for the specified time unit. The following table describes the mappings.

Examples

The following example creates a table partitioned by day. It is configured to pre-create partitions for the next three days, retain historical partitions for two days, and use the Asia/Shanghai time zone.

The following table describes the logic for creating and deleting partitions.

Retain specific child tables

By default, Hologres automatically creates and deletes child tables based on the configured automatic partitioning rules, deleting any that fall outside the retention range. However, in some scenarios, you may need to retain important data, such as retaining data from annual shopping festivals for year-over-year analysis in an e-commerce business. To retain specific child tables, set the keep_alive property for them. The syntax is as follows.

• Syntax for Hologres V2.1 and later:

  -- Retain a child table.
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');
  -- Stop retaining a child table. After you remove this property, automatic partitioning immediately triggers a cleanup of expired data.
  ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');
  

• Syntax for all versions:

  -- Retain a child table.
  CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');
  -- Stop retaining a child table. After you remove this property, automatic partitioning immediately triggers a cleanup of expired data.
  CALL set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');

  Note

  Replace table_name with the name of the child table.

Manage storage tiers for partitioned tables

When you manage partitioned tables, storage tiering helps you balance cost and performance. For example, you can keep the latest N historical partitions in hot storage for frequent queries, and then store the next M older partitions in cold storage to reduce costs. Combined with automatic partitioning, this feature lets you define a policy to automatically delete data outside the N+M retention period.

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;

Convert a cold table to an auto-partitioned table

To convert a partitioned table from cold storage to an automatic partitioned table and move the partitions for the last seven days to hot storage, follow these steps.

1. Prepare data.

   -- Specify the cold storage tier 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_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 to an automatic partitioned table and set the partitions for the last seven days to hot storage.

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

View automatic partitioning configurations and schedules

To query the information and configurations of automatic partitioned tables in the current database, run the following SQL statement.

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 fields in the query result.

The following code block shows a sample query result.

 schema_name |       table_name       | enable | time_unit | time_zone | num_precreate | num_retention |   create_user    |          cret_time          |     schd_start_time      | options
-------------+------------------------+--------+-----------+-----------+---------------+---------------+------------------+-----------------------------+--------------------------+---------
 public      | test_auto_part_daily      | t      | day       | PRC       |             4 |            -1 | 1534141793071340 | 2022-04-01 11:45:33+08 | 2050-01-01 00:00:00+08 | {}
 public      | test_auto_part_hourly     | t      | hour      | PRC       |             4 |            -1 | 1534141793071340 | 2022-04-01 11:44:39+08 | 2050-01-03 18:00:00+08 | {}
 public      | test_auto_part_weekly     | t      | week      | PRC       |             2 |             3 | 1534141793071340 | 2022-04-01 11:45:43+08 | 2050-01-03 00:00:00+08 | {}
 public      | test_auto_part_monthly    | t      | month     | PRC       |             1 |             0 | 1534141793071340 | 2022-04-01 11:45:55+08 | 2050-01-01 00:00:00+08 | {}
 public      | test_auto_part_quarterly  | t      | quarter   | PRC       |             2 |            -1 | 1534141793071340 | 2022-04-01 11:46:09+08 | 2050-01-01 00:00:00+08 | {}
 public      | test_auto_part_yearly     | t      | year      | PRC       |             1 |            -1 | 1534141793071340 | 2022-04-01 11:46:18+08 | 2050-01-01 00:00:00+08 | {}
(6 rows)

View child table logs

The Query Log does not record logs for creating or purging child 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 table describes the fields in the query result.

The following code block shows a sample query result.

   relname   |     relowner     |        schdtime        |        trigtime        | status  | message |          precreate           |     discard
-----------+------------------+------------------------+------------------------+---------+---------+------------------------------+------------------
 public.tbl1 | 1534141793071340 | 2022-04-01 11:00:00+08 | 2022-04-01 11:24:22+08 | SUCCESS |         | {tbl1_2022032713,tbl1_2022032714} | {}
 public.tbl2 | 1534141793071340 | 2022-04-01 12:00:00+08 | 2022-04-01 12:38:31+08 | SUCCESS |         | {tbl2_2022040112}                 | {}
 public.tbl2 | 1534141793071340 | 2022-04-01 13:00:00+08 | 2022-04-01 13:00:00+08 | SUCCESS |         | {tbl2_2022040113}                 | {}
 public.tbl2 | 1534141793071340 | 2022-04-01 14:00:00+08 | 2022-04-01 14:00:00+08 | SUCCESS |         | {tbl2_2022040114}                 | {tbl2_2022040112}
(4 rows)

Enable automatic partitioning for existing tables

To enable automatic partitioning for an existing partitioned table, run one of the following SQL statements.

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

Automatic cleanup for existing child tables

The system determines whether to delete a child table based on its name. If a child table's name matches the {parent_table}_{time_suffix} naming convention, it is eligible for cleanup. Otherwise, it is not affected.

Delayed creation of num_precreate child tables

When you first enable automatic partitioning for a table, the creation task does not run instantly. The system checks for partitions to create every 10 minutes by default. As a result, the system creates your child tables within this 10-minute window.