Best Practices for Automatic Partition Management

Scenarios

For partition tables whose partition columns are time, the automatic partition management function can be added to automatically create partitions and delete expired partitions, reducing partition table maintenance costs and improving query performance. To facilitate data query and maintenance, the time column is often used as the partition column of a partitioned table that stores time-related data, such as e-commerce order information and real-time IoT data. When the time-related data is imported to a partitioned table, the table should have partitions of the corresponding time ranges. Common partition tables do not automatically create new partitions or delete expired partitions. Therefore, maintenance personnel need to periodically create new partitions and delete expired partitions, leading to increased O&M costs.

Addressing this, GaussDB(DWS) introduces the automatic partition management feature. You can set the table-level parameters period and ttl to enable the automatic partition management function, which automatically creates partitions and deletes expired partitions, reducing partition table maintenance costs and improving query performance.

period: interval for automatically creating partitions. The default value is 1 day. The value range is 1 hour ~ 100 years.

ttl: time for automatically eliminate partitions. The value range is 1 hour ~ 100 years. The partition elimination policy is based on the condition that nowtime - partition boundary > ttl. Partitions that meet this condition will be eliminated.

• Automatic partition creation

  One or more partitions are automatically created at the interval specified by period to make the maximum partition boundary time 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.

Constraints

Creating an ECS

For details, see "Creating an ECS" in the lastic Cloud Server User Guide. When the ECS is created, log in to the ECS. For details, see "Remotely Logging In to a Linux ECS Using a Password (SSH)".

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. Log in to the Huawei Cloud management console.
2. Choose Service List > Analytics > Data Warehouse Service. On the page that is displayed, click Create Cluster in the upper right corner.
3. Configure the parameters according to Table 1.

   Table 1 Software configuration

   Parameter	Configuration
   Region	Select EU-Dublin. NOTE: EU-Dublin is used as an example. You can select other regions as required. Ensure that all operations are performed in the same region.
   AZ	AZ2
   Product	Standard data warehouse
   CPU Architecture	X86
   Node Flavor	dws2.m6.4xlarge.8 (16 vCPUs | 128 GB | 2000 GB SSD) NOTE: If this flavor is sold out, select other AZs or flavors.
   Nodes	3
   Cluster Name	dws-demo
   Administrator Account	dbadmin
   Administrator Password	-
   Confirm Password	-
   Database Port	8000
   VPC	vpc-default
   Subnet	subnet-default(192.168.0.0/24)
   Security Group	Automatic creation
   EIP	Buy now
   Bandwidth	1Mbit/s
   Advanced Settings	Default

4. Confirm the information, click Next, and then click Submit.
5. Wait about 6 minutes. After the cluster is created, click next to the cluster name. On the displayed cluster information page, record the value of Public Network Address.

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.eu-west-101.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 GaussDB(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 GaussDB(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=>

Automatic partition management

The partition management function is bound to the table-level parameters period and ttl. Automatic partition creation is enabled with the enabling of period, and automatic partition deletion is enabled with the enabling of ttl. 30 seconds after period or ttl is set, the automatic partition creation or deletion works for the first time.

You can enable the partition management function in either of the following ways:

• Specify period and ttl when creating a table.

  This way is applicable when you create a partition management table. There are two syntaxes for creating a partition management table. One specifies partitions, and the other does not.

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

  The following example shows how to create a partition management table CPU1 and specify partitions.

  1 2 3 4 5 6 7 8 9 10

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

  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 2 Description of the period parameter

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

  Run the following command to create the partition management table CPU2 with no partitions specified:

  1 2 3 4 5 6

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

• Run the ALTER TABLE RESET command to set period and ttl.

  This method is used to add the partition management function to an ordinary partitioned table that meets the partition management constraints.

  • Run the following command to create an ordinary partition table CPU3:

    1 2 3 4 5 6 7 8 9 10

    CREATE TABLE CPU3( 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') );

  • To enable the automatic partition creation and deletion functions, run the following command:

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

  • To enable only the automatic partition creation function, run the following command:

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

  • To enable only the automatic partition deletion function, run the following command (If automatic partition creation is not enabled in advance, the operation will fail):

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

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

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

• Disabling the partition management function

  You can run the ALTER TABLE RESET command to delete the table-level parameters period and ttl to disable the partition management function.

  

  • The period cannot be deleted separately with TTL.
  • The time series table does not support ALTER TABLE RESET. ​
  • Run the following command to disable the automatic partition creation and deletion functions:

    1	ALTER TABLE CPU1 RESET (PERIOD,TTL);

  • To disable only the automatic partition deletion, run the following command:

    1	ALTER TABLE CPU3 RESET (TTL);

  • To disable only the automatic partition creation function, run the following command (If the table contains the ttl parameter, the operation will fail):

    1	ALTER TABLE CPU3 RESET (PERIOD);