Automatic Partitioning Policy

Automatic partitioning is an automatic commit process. When the data inserted by a DML operation cannot match any existing partition or the partition specified by the created sparsely partitioned index does not exist, an autonomous transaction is triggered to execute automatic partitioning. This process imposes a temporary lock on the partitioned table and blocks DDL commands on other partitions. The blocking period is very short, which has no impact on system running or user operations.

The behavior of automatic partitioning is as follows:

• Partitions that are created by a DML service through automatic partitioning cannot be rolled back. That is, after the transaction is rolled back, new partitions still exist. You can query the PG_PARTITION system catalog to view the new partition.
• When automatic partitioning is triggered by creating a sparsely partitioned index, if the index creation transaction is rolled back due to an exception, the new partition may still exist (the rollback occurs after the automatic partitioning is triggered) or may not exist (the rollback point occurs before the automatic partitioning is triggered). You can query the PG_PARTITION system catalog to view the new partition.
• Automatic partitioning and common partition-related DQL/DML services do not block each other. The two types of services can occur in parallel.
• During automatic partitioning, a lock is imposed for a short period of time, which does not affect system running or user operations. However, multiple concurrent automatic partitioning threads are supported if they are triggered due to DML/sparsely partitioned index services.
• The automatic partitioning process is mutually exclusive with partition-level DDL operations. If automatic partitioning is triggered by a DML/sparsely partitioned index service when another thread executes a partition-level DDL operation and the DDL operation is not committed, the automatic partitioning process will be blocked. However, if automatic partitioning is not committed, the partition-level DDL service will not be blocked.

Automatic partitioning is triggered in either of the following ways:

• If new data produced by a DML service cannot match any existing partition, a partition is automatically created based on the rules and then the corresponding data is inserted into the new partition.
• When a sparsely partitioned index is created by running PARTITION/SUBPARTITION FOR partition_value, if the specified partition does not exist, a partition is automatically created based on the rules and then a sparsely partitioned index is created for the new partition.