This document describes the automated partition management policies and usage in YMatrix.
In production environments, data is often partitioned by time. YMatrix provides several user-defined functions (UDFs) for managing partitions, allowing administrators to manually maintain partitions based on their requirements—for example, creating one partition per day or every 8 hours.
However, time is an infinite dimension. When creating partitioned tables, administrators typically pre-create partitions only for a limited future period and then periodically add new partitions. This increases operational overhead.
Automated partition management eliminates the need for manual maintenance by automating partition lifecycle operations, thereby reducing administrative burden. It supports the following features:
The automated partition management functionality is included in the matrixts extension. Therefore, you must first create the matrixts extension:
=# CREATE EXTENSION matrixts;Use the set_policy() function to assign a policy to a target table and enable automatic partition maintenance.
Currently, three policies are supported:
auto_partitioningauto_splittingauto_partitioning_exNote!
Starting from version 5.0, YMatrix introduces theauto_partitioning_expolicy. Use this policy instead ofauto_partitioningto take advantage of advanced features such as batch partition creation, forced retention of specific historical partitions, and customizable operation time windows.
The auto_partitioning policy enables automated management of regular partitioned tables and provides two automation capabilities:
Example: Create a test partitioned table named metrics:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
USING MARS3
DISTRIBUTED BY (tag_id)
ORDER BY (time,tag_id)
PARTITION BY RANGE (time);Set the auto_partitioning auto-partitioning policy on the metrics table:
=# SELECT set_policy(
'metrics'::regclass,
'auto_partitioning'
);If schema qualification is required, pass the parameter as
'schema_name.metrics'.
After setting the policy, use list_policy() to view it:
=# SELECT * FROM list_policy('metrics'::regclass);
reloid | relname | class_id | class_name | action | seq | disabled | check_func | check_args | act_func | act_args | versi
on
--------+---------+----------+-------------------+-------------+-----+----------+----------------------------------------+----------------------+------------------------------------------------+-----------------------+------
---
23361 | metrics | 2 | auto_partitioning | retention | 1 | t | matrixts_internal.apm_generic_expired | {"after": "1 year"} | matrixts_internal.apm_generic_drop_partition | {} | 1.0
23361 | 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 shown, the auto_partitioning policy includes two actions:
retention: Delete expired partitionsauto_create: Create new partitionsThe retention action uses the check argument {"after": "1 year"}, meaning that any partition older than one year will be deleted.
The auto_create action checks whether a new partition should be created using {"before": "3 days"}—that is, three days in advance—and creates partitions with a time span of {"period": "8 hours"}. These are the default settings for the auto_partitioning policy.
To adjust these parameters, call set_policy_action().
Example: Change retention to delete partitions after 3 months; modify auto_create to look ahead 2 days and create daily partitions:
=# SELECT set_policy_action(
'metrics'::regclass,
'retention',
'3 months'
);
=# SELECT set_policy_action(
'metrics'::regclass,
'auto_create',
'{
"before": "2 days",
"period": "1 day"
}'
);Note!
For actions with a single parameter, provide the target value directly as a string (e.g.,'3 months'). For actions with multiple parameters, use a JSON object to specify all arguments (e.g.,'{"before": "2 days", "period": "1 day"}').
The auto_splitting policy applies to partitioned tables that include a default partition. It automatically splits the default partition based on time.
Example: Create a partitioned table and define a default partition:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
USING MARS3
DISTRIBUTED BY (tag_id)
ORDER BY (time,tag_id)
PARTITION BY RANGE (time);
=# CREATE TABLE metrics_others PARTITION OF metrics DEFAULT;Apply the auto_splitting policy:
=# SELECT set_policy(
'metrics'::regclass,
'auto_splitting'
);View the policy:
=# SELECT * FROM list_policy('metrics'::regclass);
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)The auto_splitting policy includes two actions: retention (delete expired partitions) and auto_split (automatically split the default partition).
Here, {"age": "1 month 2days"} means the split is triggered when the oldest data in the default partition is older than 1 month and 2 days.
{"period": "1 month"} indicates that each resulting partition covers a 1-month interval.
The auto_partitioning_ex policy extends the functionality of auto_partitioning, offering advanced partition management features:
These features are detailed in the examples below.
Create a partitioned table and define a special partition:
=# CREATE TABLE metrics(
time timestamp with time zone,
tag_id int,
read float,
write float
)
USING MARS3
DISTRIBUTED BY (tag_id)
ORDER BY (time,tag_id)
PARTITION BY RANGE (time);
=# CREATE TABLE metrics_invalid_apmtrap PARTITION OF metrics FOR VALUES FROM ('-infinity') TO ('2023-01-01');Set the auto_partitioning_ex policy:
=# SELECT set_policy(
'metrics'::regclass,
'auto_partitioning_ex'
);View the policy:
=# SELECT * FROM list_policy('metrics'::regclass);
reloid | relname | class_id | class_name | action | seq | disabled | check_func | check_args
| act_func | act_args | version
--------+---------+----------+----------------------+-------------+-----+----------+-------------------------------------------+---------------------------------------------------------------------------------------------------------------------
-------------------+------------------------------------------------+-----------------------+---------
34917 | metrics | 4 | auto_partitioning_ex | retention | 1 | t | matrixts_internal.apm_generic_expired | {"after": "1 year"}
| matrixts_internal.apm_generic_drop_partition | {} | 1.0
34917 | metrics | 4 | auto_partitioning_ex | auto_create | 2 | f | matrixts_internal.apm_generic_incoming_ex | {"before": "3 days", "period": "8 hours", "end_time": "00:00:00", "batch_size": "0", "start_time": "00:00:00", "stor
age_type": "heap"} | matrixts_internal.apm_generic_append_partition | {"period": "8 hours"} | 1.0
(2 rows)From the output, the auto_create action under auto_partitioning_ex has the parameter "before": "3 days" and "period": "8 hours". This means the automated system will ensure that all partitions for the next 3 days are pre-created, each covering an 8-hour interval. For example, if the current time is March 1, 00:01, the system triggers partition creation and ensures all partitions up to March 4, 00:00 are ready. At March 4, 00:00, the system begins creating the next 8-hour partition (March 4, 00:00–08:00). With this design, the auto_partitioning_ex policy guarantees that partitions for the next 3 days are always prepared in advance.
However, other concurrent tasks may hold locks on the table, causing lock contention when the automated system attempts to create partitions. To reduce the frequency of such contention, the auto_partitioning_ex policy introduces the batch_size option.
Use the following command to set it:
=# SELECT public.set_policy_action('metrics'::regclass, 'auto_create', '{ "batch_size": "4" }');This means that whenever a new partition needs to be created, the system will create 4 additional future partitions at once. So, when creating the March 4, 00:00–08:00 partition on March 1, 00:01, it will also create the next four 8-hour partitions: March 4, 08:00–16:00; March 4, 16:00–24:00; March 5, 00:00–08:00; and March 5, 08:00–16:00.
This avoids triggering partition creation again between March 1 and March 4, thus reducing lock contention.
batch_size is 0, meaning no batching occurs—the behavior is identical to auto_partitioning.batch_size is 7.Note!
While batch creation reduces lock contention frequency (e.g., from daily to every few days), it cannot eliminate lock conflicts entirely when the automated task executes DDL statements. This limitation stems from fundamental database concurrency mechanisms.
Therefore, adequate monitoring and alerting for queries, locks, and automated partition management execution remain essential.
As shown above, we created a partition named metrics_invalid_apmtrap. When the retention feature (automatic partition deletion) is enabled under auto_partitioning_ex, any partition whose name contains the string apmtrap (such as metrics_invalid_apmtrap) will not be dropped, even if it meets the expiration condition. This achieves permanent retention of designated historical partitions.
This feature is designed to implement a "trap" partition that captures out-of-range data (e.g., timestamps like 1970-01-01). The trap partition absorbs invalid data that would otherwise go into the default partition, preventing rapid growth of the default partition. Additionally, since the trap partition does not participate in queries or DDL operations, it helps preserve query performance.
From the earlier list_policy() result, observe the parameters start_time and end_time in the check_args field. These parameters control the time window during which automated partition management tasks can run, helping avoid partition creation during peak business hours.
For example:
"start_time": "01:00:00", "end_time": "04:00:00" allows operations between 1:00 AM and 4:00 AM daily.
"start_time": "22:00:00", "end_time": "03:00:00" allows operations from 10:00 PM to 3:00 AM the next day.
The default value is "00:00:00" for both fields, indicating no time restriction—operations are allowed at any time.
Example:
SELECT public.set_policy_action('table_name'::regclass, 'auto_create', '{ "start_time": "00:00:00", "end_time": "04:00:00" }');Note!
The time window must be at least 1 hour long. The default check interval for automated partition management is 1 hour. A window shorter than 1 hour may miss the trigger opportunity.
Reaching theend_timedoes not immediately terminate ongoing operations. Any triggered task continues until completion. Theend_timeonly prevents new checks from being initiated after that point—it does not forcibly stop running tasks. Therefore, when setting theend_time, allow sufficient buffer time before the next business peak.
From the list_policy() output, note the disabled column. This column controls whether a policy action is active. By default, regardless of the policy type, the retention action is disabled.
To enable an action, use enable_policy_action():
=# SELECT enable_policy_action('metrics'::regclass, 'retention');To disable an action, use disable_policy_action():
=# SELECT disable_policy_action('metrics', 'auto_split');Use drop_policy() to remove a policy from a partitioned table:
=# SELECT drop_policy('metrics'::regclass);Note!
Each partitioned table can have only one partition policy. Therefore, only the table name is required to drop the policy.
--- SPLIT ---
4.1 apm_operation_log
The apm_operation_log table records every user operation performed when configuring APM. Example:
=# SELECT username = CURRENT_USER, relname, class_id, class_name, action, operation, mod_field, old_val, new_val
FROM matrixts_internal.apm_operation_log ORDER BY ts;
?column? | relname | class_id | class_name | action | operation | mod_field | old_val | new_val
----------+--------------------+----------+------------+------------+-----------+------------+------------------------+------------------------
t | apm_test.split_set | | | test_split | add | check_args | | {"age": "1 month"}
t | apm_test.split_set | | | test_split | add | act_args | | {"period": "2 weeks"}
t | apm_test.split_set | | | test_split | mod | act_args | {"period": "2 weeks"} | {"period": "12 hours"}
t | apm_test.split_set | | | test_split | mod | check_args | {"age": "1 month"} | {"age": "15 days"}
t | apm_test.split_set | | | test_split | mod | check_args | {"age": "15 days"} | {"age": "5 days"}
t | apm_test.split_set | | | test_split | mod | act_args | {"period": "12 hours"} | {"period": "2 days"}
t | apm_test.split_set | | | test_split | drop | | |
t | apm_test.split_set | | | test_split | add | check_args | | {"age": "10 days"}
t | apm_test.split_set | | | test_split | add | act_args | | {"period": "1 hour"}
(9 rows)The apm_action_log table records each automatic partitioning operation executed by the APM background process. Example:
=# (select * from matrixts_internal.apm_action_log order by ts limit 6) union all (select * from matrixts_internal.apm_action_log order by ts desc limit 6) order by 1;
ts | pid | reloid | relname | nspname | class_id | class_name | action | check_func | check_args | act_func
| act_args | state | message | details | addopt | client
----------------------------+-------+--------+--------------------------+--------------------+----------+-------------------+-------------+----------------------------------------+----------------------+------------------------------------------
------+---------------------+---------+-------------------+---------+--------+----------
2022-11-18 15:48:45.963264 | 15776 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-18 15:48:46.165418 | 15776 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-18 15:48:46.172096 | 15776 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-18 15:48:46.332781 | 15776 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-19 00:48:45.992494 | 24930 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-19 00:48:46.064604 | 24930 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-21 00:48:47.708846 | 14091 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-21 00:48:47.812423 | 14091 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-22 00:48:46.552925 | 8539 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-22 00:48:53.25854 | 8539 | 18360 | mx_query_execute_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
2022-11-22 00:48:53.261857 | 8539 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | START | action start | | | schedule
2022-11-22 00:48:55.625125 | 8539 | 18373 | mx_query_usage_history | matrixmgr_internal | 2 | auto_partitioning | auto_create | matrixts_internal.apm_generic_incoming | {"before": "3 days"} | matrixts_internal.apm_generic_append_part
ition | {"period": "1 day"} | SUCCESS | do action success | | | schedule
(12 rows)