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.

In some scenarios, automatic partitioning is still executed in the same transaction, that is, the hybrid transaction mode is used. In this case, logical decoding is not supported. The involved scenarios are as follows:

• The total number of connections that trigger new partitions or other autonomous transactions exceeds the maximum number of connections (max_concurrent_autonomous_transactions), or max_concurrent_autonomous_transactions is set to 0.
• The partitioned table or parent partition is created by the current transaction.
• If DDL operations are performed on a partitioned table in the same transaction and an autonomous transaction is used, a deadlock is triggered and the hybrid transaction mode of adding partitions is used.

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.