Automated partition management

In production environments, data partitioning is often done according to time. MatrixDB provides some UDFs for managing partitions, and you can manually maintain partitions according to your needs. For example: one partition every day, one partition every 8 hours, etc. But time is an infinitely extended dimension. When creating partition tables, administrators usually just create partitions in the future period in advance, and then regularly add new time period partitions. This undoubtedly increases operation and maintenance costs.

Automated partition management is to automate the work of manually maintaining partitions, thereby reducing the cost of administrators to maintain databases. include:

Automatic partition creation and deletion
Default partitioning automatically

The automated partition management feature is included in the matrixts extension, so first create the matrixts extension:

CREATE EXTENSION matrixts;

By calling set_policy, setting policies for the target table, partition maintenance automation is achieved:

Currently, there are two strategies:

auto_partitioning
auto_splitting

1. auto_partitioning

The auto_partitioning policy is applied to the automated management of ordinary partition tables, and implements the following 2 automation functions:

New partition creation
Expired partition cleaning

Example, create a test partition table metrics, as follows:

CREATE TABLE metrics(
    time timestamp with time zone,
    tag_id int,
    read float,
    write float
)
DISTRIBUTED BY (tag_id)
PARTITION BY RANGE (time);

Create an auto_partitioning automatic partitioning policy for the metrics table:

SELECT set_policy(
    'metrics',
    'auto_partitioning'
);

If you need to carry mode information, just pass the parameter schema_name.table_name.

After setting the policy, call list_policy to view the policy:

SELECT * FROM list_policy('metrics');

 reloid | relname | class_id |    class_name     |   action    | seq | disabled |               check_func               |      check_args      |                    act_func                    |       act_args        | version
--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  17798 | metrics |        2 | auto_partitioning | retention   |   1 | t        | matrixts_internal.apm_generic_expired  | {"after": "1 year"}  | matrixts_internal.apm_generic_drop_partition   | {}                    | 1.0
  17798 | metrics |        2 | auto_partitioning | auto_create |   2 | f        | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_partition | {"period": "8 hours"} | 1.0
(2 rows)

As you can see, auto_partitioning contains two operations in total:

Retention: Delete expired partitions
auto_create: new partition creation

The detection parameter of retention is {"after": "1 year"}, which means detecting whether the partition has been more than one year and then deleting it. The detection parameter of auto_create is {"before": "3 days"}, which means to check whether a new partition needs to be created 3 days in advance; the execution parameter is {"period": "8 hours"}, which means to create a new partition with a span of 8 hours. This is the default setting for the auto_partitioning policy.

If you want to adjust these parameters, you need to call set_policy_action.

Example, adjust retention to automatically delete after 3 months; auto_create is adjusted 2 days in advance, with a time span of 1 day:

SELECT set_policy_action(
    'metrics',
    'retention',
    '3 months'
);

SELECT set_policy_action(
    'metrics',
    'auto_create',
    '{
        "before": "2 days",
        "period": "1 day"
    }'
);

Notes!
For action with only one parameter, just write the target value string directly, such as retention. For actions containing multiple parameters, the target value needs to be completed through JSON splicing.

2. auto_splitting

The auto_splitting policy implements the default partition cutting operation for partition tables containing default partitions.

Example, create a partition table and create a default partition for it:

CREATE TABLE metrics(
    time timestamp with time zone,
    tag_id int,
    read float,
    write float
)
DISTRIBUTED BY (tag_id)
PARTITION BY RANGE (time);

CREATE TABLE metrics_others PARTITION OF metrics DEFAULT;

Set the auto_splitting policy:

SELECT set_policy(
    'metrics',
    'auto_splitting'
);

View the policy:

SELECT * FROM list_policy('metrics');
 reloid | relname | class_id |   class_name   |   action   | seq | disabled |                check_func                |        check_args        |                       act_func                        |       act_args        | version
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  17798 | metrics |        1 | auto_splitting | retention  |   1 | t        | matrixts_internal.apm_generic_expired    | {"after": "1 year"}      | matrixts_internal.apm_generic_drop_partition          | {}                    | 1.0
  17798 | metrics |        1 | auto_splitting | auto_split |   2 | f        | matrixts_internal.apm_generic_older_than | {"age": "1 month 2days"} | matrixts_internal.apm_generic_split_default_partition | {"period": "1 month"} | 1.0
(2 rows)

As you can see, the auto_splitting policy contains 2 operations, expired partition deletion retention and default partition automatic cutting auto_split.

Among them, {"age": "1 month 2days"} means that the oldest data in the default partition is earlier than January 2 days ago, and the current time is cut. {"period": "1 month"} means that the partition granularity after cutting is 1 month.

3. Operation status settings

From the above list_policy result you will notice that you contain a column called disabled. This column is used to set the operation status. No matter which strategy currently exists, the status of the retention operation is disabled.

To enable this operation, call enable_policy_action:

SELECT enable_policy_action('metrics', 'retention');

To disable an action, call disable_policy_action:

SELECT disable_policy_action('metrics', 'auto_split');

4. Delete Policy

drop_policy is the partition table deletion policy:

SELECT drop_policy('metrics');

Notes!
Each partition table can only set one partition policy, so the deletion policy only provides the partition table name.