Cutting Partition Maintenance Costs for the E-commerce and IoT Industries by Leveraging Automatic Partition Management Feature

Scenarios

E-commerce and IoT systems frequently use partitioned tables with time-based partition columns to manage time-sensitive data like orders and sensor readings. These tables can organize and access data efficiently. However, standard partitioned tables lack automatic features to create new partitions or remove outdated ones. Maintenance personnel need to manually handle these tasks periodically, increasing O&M costs.

To solve this problem, DWS offers automatic partition management. By setting the period and ttl parameters at the table level, the system creates new partitions and removes outdated ones automatically, reducing O&M costs and enhancing query efficiency.

The feature works with both time-based and other column types like INT, BIGINT, VARCHAR, and TEXT, increasing its versatility and adaptability.

An example of CREATE TABLE syntax:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE CPU1(
    id integer,
    IP text,
    time integer
) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD')     ---A new partition is automatically created every day. If the nowtime minus the partition boundary time is greater than 7 days, the partition will be automatically removed.
partition by range(time)
(
    PARTITION P1 VALUES LESS THAN('20230213'),
    PARTITION P2 VALUES LESS THAN('20230215')
);

• period: interval for automatically creating partitions. The default value is 1 day. The value range is 1 hour to 100 years. For example, if period is set to 1 day, a new partition is created every day.
• ttl: how long partitions stay before being removed automatically. It ranges from 1 hour to 100 years. A partition is removed if the nowtime minus the partition boundary time exceeds the ttl value Partition boundary: the partition boundary value defined when a partitioned table is created. In the example above, 20230213 is the boundary for partition P1. When ttl is set to 7 days and the nowtime is 20230220, this partition is removed.

• time_format:

  When the partition column uses VARCHAR, TEXT, INT, or BIGINT instead of TIMESTAMP, you need to set the time_format parameter at the table level. This tells the system how to parse the time value in the partition column for automatic partition management. You can set time_format only if the partition key is INT4, INT8, VARCHAR, or TEXT, and a period is specified.

  Here are the time_format options and restrictions for each type of partition column:

  Table 1 Time format supported by the VARCHAR/TEXT type

  Format	Description	Sample Input
  YYYY	Use four digits for the year (0000-9999).	2024
  MM	Use two digits for the month (01-12).	05
  DD	Use two digits for the date (01–31).	17
  HH24	Use two digits for the hour (00–23).	14
  MI	Use two digits for the minute (00–59).	32
  SS	Use two digits for the second (00–59).	45

  

  • The precision can be accurate to seconds.
  • The entered content cannot contain letters, such as MONTH, AM, and PM.
  • The time format must be in descending order, for example, YYYYMMDDHH24MISS.

  Table 2 Time formats supported by the INT/BIGINT type

  Format	Description	Sample Input
  YYYY	Use four digits for the year (0000-9999).	2024
  MM	Use two digits for the month (01-12).	05
  DD	Use two digits for the date (01–31).	17
  HH24	Use two digits for the hour (00–23).	14

  

  • The precision can be accurate to hours.
  • The input content cannot contain non-numeric elements.
  • The time format must be in descending order, for example, YYYYMMDDHH24.

Constraints

Automatic Partition Creation Rules

The partition management function relies on period and ttl. period controls whether to automatically create partitions, and ttl controls whether to automatically remove expired partitions. The system automatically creates or deletes partitions for the first time 30 seconds after period or ttl is set.

• Automatically creating partitions

  One or more partitions are automatically created at the interval specified by period. The maximum partition boundary time is greater than (nowTime + 30 x period). As long as there is an automatically created partition, real-time data will not fail to be imported within the next 30 periods.

  Figure 1 Automatic partition creation
  
• Automatically deleting expired partitions

  Partitions whose boundary time is earlier than nowTime-ttl are considered expired partitions. The automatic partition management function traverses all partitions and deletes expired partitions after each period. If all partitions are expired partitions, the system retains one partition and truncates the table.

Creating an ECS

Purchase an ECS in custom config mode. After purchasing an ECS, log in to the ECS by referring to Logging In to a Linux ECS.

When creating an ECS, ensure that the ECS is in the same region, AZ, and VPC subnet as the stream data warehouse. Select the OS used by the gsql client (CentOS 7.6 is used as an example) as the ECS OS, and select using passwords to log in.

Creating a cluster

1. Create a cluster on the DWS console. For details, see Creating a DWS Storage-Compute Coupled Cluster.

Using the gsql CLI Client to Connect to a Cluster

1. Remotely log in to the Linux server where gsql is to be installed as user root, and run the following command in the Linux command window to download the gsql client:

   1	wget https://obs.ap-southeast-1.myhuaweicloud.com/dws/download/dws_client_8.1.x_redhat_x64.zip --no-check-certificate

2. Decompress the client.

   1	cd <Path_for_storing_the_client> unzip dws_client_8.1.x_redhat_x64.zip

   Where,

   • <Path_for_storing_the_client>: Replace it with the actual path.
   • dws_client_8.1.x_redhat_x64.zip: This is the client tool package name of RedHat x64. Replace it with the actual name.

3. Configure the DWS client.

   1	source gsql_env.sh

   If the following information is displayed, the gsql client is successfully configured:

   1	All things done.

4. Use the gsql client to connect to a DWS database (using the password you defined when creating the cluster).

   1	gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r

   If the following information is displayed, the connection succeeded:

   1

   gaussdb=>

Specifying period and ttl During Partitioned Table Creation to Enable Automatic Partitioning

When you create a partitioned table, you can specify partitions or do not specify partitions.

• Specifying partitions:

  If partitions are specified when a partition management table is created, the syntax rule is the same as that for creating an ordinary partitioned table. The only difference is that the table-level parameters period and ttl are specified.

  Example: Create a partition management table CPU1, specify a partition. The partition column is of the timestamp type.

  1 2 3 4 5 6 7 8 9 10 11

  -- Time type CREATE TABLE CPU1( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  Example: Create partition management tables CPU2 and CPU3 and specify partitions. The partition columns are of the INT and VARCHAR types.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  -- INT type CREATE TABLE CPU2( id integer, IP text, time integer ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD') partition by range(time) ( PARTITION P1 VALUES LESS THAN('20230213'), PARTITION P2 VALUES LESS THAN('20230215') ); -- VARCHAR type CREATE TABLE CPU3( id integer, IP text, time varchar ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYY-MM-DD HH24:MI:SS') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  For partitioned tables with INT, BIGINT, VARCHAR, or TEXT types, if automatic partitioning is enabled, the system adds missing partitions within the ttl range. This is based on the ttl value and the existing minimum partition boundary (min_bound). The specific rules are as follows:

  The rules depend on the relationship between min_bound and the current time (cur_time), and are categorized as follows:

  Condition	Description
  min_bound > cur_time + 29 x period	The current minimum partition size is sufficient. The system sees no need for additional partitions in the future and will not create them automatically.
  min_bound > cur_time and min_bound < cur_time + 29 x period	The system uses period as the step to supplement the past partitions until the minimum partition boundary is less than (cur_time - ttl).
  min_bound < cur_time and min_bound > cur_time - ttl	The system uses period as the step to supplement the past partitions until the minimum partition boundary is less than (cur_time - ttl).
  min_bound < cur_time - ttl	The current minimum partition is earlier than the ttl range and will be removed. The system does not continue to supplement the past partitions.

  

  • cur_time indicates the current system time.
  • period indicates the period of automatic partitioning.
  • The automatic supplementation logic ensures that partitions exist in a valid time window, facilitating data import and query.
  • For INT, BIGINT, VARCHAR, and TEXT partitioned tables, if a large ttl value and a small period value are specified during table creation, and the manually specified boundary of the first partition is between the current time and cur_time - ttl, the system may automatically add missing historical partitions within the TTL lifecycle based on the automatic partitioning supplementation rule.

    For example, if ttl is set to '100 years' and period is set to '1 day', a large number of historical partitions may be automatically created, significantly increasing the time required for table creation. A long TTL and a short period are not recommended during table creation. TTL is used to clear expired data and control the valid lifecycle. Set it based on the data retention period.

  • Use the following method when creating an INT, BIGINT, VARCHAR, or TEXT partitioned table. This prevents TTL-based creation of a large number of historical partitions:
    1. Do not set the TTL during table creation.
    2. After the table is created, use the ALTER TABLE ... SET (...) syntax to set the TTL (and the required period) to enable automatic partition management.

    This method prevents a large number of historical partitions from being created within the TTL lifecycle, thereby reducing the time required for table creation.

• Not specifying partitions

  When creating a partition management table, you can specify only the partition key but not partitions. In this case, two default partitions will be created with period as the partition time range. The boundary time of the first default partition is the first hour, day, week, month, or year past the current time. The time unit is selected based on the maximum unit of period. The boundary time of the second default partition is the boundary time of the first partition plus period. Assume that the current time is 2023-02-17 16:32:45, and the boundary of the first default partition is described in the following table.

  Table 3 Description of the period parameter

  period	Maximum period Unit	Boundary of First Default Partition
  1 hour	Hour	2023-02-17 17:00:00
  1day	Day	2023-02-18 00:00:00
  1 month	Month	2023-03-01 00:00:00
  13 months	Year	2024-01-01 00:00:00

  For INT, BIGINT, VARCHAR, and TEXT partition tables, if no partition is set, the system automatically creates initial partitions when the automatic partition management is turned on. The creation rules are as follows:

  • Supplement two partitions forward (compared with the current time).
  • Supplement ttl/period partitions backward (calculated based on the lifecycle ttl and partition period).

  Example: Create a partition management table CPU4 without specifying partitions. The partition column is of the timestamp type.

  1 2 3 4 5 6 7

  -- Time type CREATE TABLE CPU4( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time);

  Example: Create partition management tables CPU5 and CPU6 without specifying partitions. The partition columns are of the INT and VARCHAR types, respectively.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  -- INT type CREATE TABLE CPU5( id integer, IP text, time integer ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD') partition by range(time); -- VARCHAR type CREATE TABLE CPU6( id integer, IP text, time varchar ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYY-MM-DD HH24:MI:SS') partition by range(time);

Using ALTER TABLE RESET to Set period and ttl to Enable Automatic Partition Management for Existing Partitioned Tables

This method is for common partitioned tables that meet partition management constraints.

• Create a common partitioned table CPU7.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  -- Time type CREATE TABLE CPU7( id integer, IP text, time timestamp ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-14 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:56:12') ); -- VARCHAR type CREATE TABLE CPU7( id integer, IP text, time varchar ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

• Enable both automatic partition creation and deletion.

  1	ALTER TABLE CPU7 SET (PERIOD='1 day',TTL='7 days');

• Enable automatic partition creation.

  1	ALTER TABLE CPU7 SET (PERIOD='1 day');

• Enable automatic partition deletion. To enable it, you need to first enable automatic partition creation, or it will fail to be enabled.

  1	ALTER TABLE CPU7 SET (TTL='7 days');

• Modify period and ttl to modify the partition management function.

  1	ALTER TABLE CPU7 SET (TTL='10 days',PERIOD='2 days');

Querying Partition Information

Query information about automatically created partitions.

1

SELECT * FROM DBA_TAB_PARTITIONS WHERE table_name = 'CPU1';

Disabling Automatic Partition Management

You can run ALTER TABLE RESET to delete period and ttl to disable partition management.

• If there is ttl, period cannot be deleted separately.
• The time series table does not support ALTER TABLE RESET.

• Disable both automatic partition creation and deletion.

  1	ALTER TABLE CPU1 RESET (PERIOD,TTL);

• Disable automatic partition deletion.

  1	ALTER TABLE CPU7 RESET (TTL);

• Disable automatic partition creation. If the table contains the ttl parameter, the operation will fail.

  1	ALTER TABLE CPU7 RESET (PERIOD);

• For partition tables of the INT, BIGINT, VARCHAR, and TEXT types, disable the TIME_FORMAT option as prompted.

  1	ALTER TABLE CPU7 RESET (TIME_FORMAT);