Interval Range Partitioning for Automatic Partition Management - PolarDB

Interval range partitioning extends range partitioning with automatic partition creation. When a row arrives whose partition key falls above the transition point — the upper boundary of existing partitions — PolarDB for PostgreSQL (Compatible with Oracle) creates a new partition automatically at the specified interval, without requiring a manual ALTER TABLE statement.

How it works

Interval range partitioning adds an INTERVAL clause to PARTITION BY RANGE. The INTERVAL clause sets the width of each auto-created partition. The transition point is the high value of the last manually defined range partition. Any row above that boundary triggers automatic partition creation.

Transition point example: Suppose the transition point is February 15, 2019 and you insert a row dated May 10, 2019. The database creates the partition covering April 15 to May 15, 2019. It skips the February 15–March 15 and March 15–April 15 intervals because no data falls in those ranges.

System-generated names: Auto-created partitions receive system-generated names (for example, SYS916340103). The name varies between sessions.

Limitations

The following restrictions apply when using the INTERVAL clause:

Only one partition key column can be specified. The column type must be numeric or date range.
At least one range partition must be defined before auto-creation can occur.
In composite partitioning, interval partitioning can be the primary (top-level) strategy. Interval partitioning cannot be used for subpartitioning.
Interval partitioning cannot be used on index-organized tables.
Domain indexes cannot be created on list-partitioned tables.
DEFAULT and MAXVALUE partitions cannot be defined on an interval-partitioned table.
Partition key values of NULL, NaN (Not-a-Number), or Infinity are not supported.
The interval expression must evaluate to a non-negative constant.
Partitions are created in ascending order only.

Syntax

CREATE TABLE [ schema. ]<table_name>
   <table_definition>
   PARTITION BY RANGE(<column>[, <column> ]...)
   [INTERVAL (<constant> | <expression>)]
   [SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...)]
   (<range_partition_definition>[, <range_partition_definition>]...)
   [ENABLE ROW MOVEMENT];

Where range_partition_definition is:

PARTITION [<partition_name>]
  VALUES LESS THAN (<value>[, <value>]...)
  [TABLESPACE <tablespace_name>]
  [(<subpartition>, ...)]

INTERVAL expressions

The INTERVAL parameter accepts numerical and time intervals.

Numerical interval — group every 10 consecutive values into one partition:

INTERVAL (10)

Time intervals:

Granularity	Expression
Year	`INTERVAL (NUMTOYMINTERVAL(1, 'year'))`
Month	`INTERVAL (NUMTOYMINTERVAL(1, 'month'))`
Day	`INTERVAL (NUMTODSINTERVAL(1, 'day'))`
Week	`INTERVAL (NUMTODSINTERVAL(7, 'day'))`

For all other CREATE TABLE parameters, see CREATE TABLE...PARTITION BY.

Create an interval range-partitioned table

The following example creates a sales table partitioned by month on the sold_month column. Partitions p1 and p2 are defined manually; any row whose sold_month falls after the p2 upper boundary triggers automatic monthly partition creation.

CREATE TABLE sales
(
  prod_id       int,
  prod_quantity int,
  sold_month    date
)
PARTITION BY RANGE(sold_month)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p1 VALUES LESS THAN('15-JAN-2019'),
  PARTITION p2 VALUES LESS THAN('15-FEB-2019')
);

Query the ALL_TAB_PARTITIONS view to confirm the initial partitions:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 partition_name |      high_value
----------------+----------------------
 P1             | '15-JAN-19 00:00:00'
 P2             | '15-FEB-19 00:00:00'
(2 rows)

Insert a row dated May 10, 2019 — above the p2 transition point:

INSERT INTO sales VALUES (1, 200, '10-MAY-2019');

Output:

INSERT 0 1

Query the partitions again to see the auto-created partition:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 partition_name |      high_value
----------------+----------------------
 P1             | '15-JAN-19 00:00:00'
 P2             | '15-FEB-19 00:00:00'
 SYS916340103   | '15-MAY-19 00:00:00'
(3 rows)

The database created partition SYS916340103 to hold the May 10 row and skipped the February–March and March–April intervals.

Convert between range and interval range partitioning

Convert a range-partitioned table to interval range partitioning

Use ALTER TABLE...SET INTERVAL to add an interval to an existing range-partitioned table. After conversion, the database creates new partitions automatically whenever data falls above the transition point.

ALTER TABLE <table_name> SET INTERVAL (<constant> | <expression>);

Convert an interval range-partitioned table to range partitioning

Pass empty parentheses to SET INTERVAL to disable auto-creation. The table reverts to standard range partitioning.

ALTER TABLE <table_name> SET INTERVAL ();

Example: convert range to interval range partitioning

The following example converts the sales range-partitioned table to monthly interval partitioning, then inserts a row to trigger auto-creation.

Create a standard range-partitioned table:

CREATE TABLE sales
(
  prod_id       int,
  prod_quantity int,
  sold_month    date
)
PARTITION BY RANGE(sold_month)
(
  PARTITION p1 VALUES LESS THAN('15-JAN-2019'),
  PARTITION p2 VALUES LESS THAN('15-FEB-2019')
);

Query the partitions before conversion:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 partition_name |      high_value
----------------+----------------------------------------------------------------------
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(2 rows)

Convert to monthly interval range partitioning:

ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

Insert a row dated April 5, 2019 — above the existing partition boundary:

INSERT INTO sales VALUES (1, 100, '05-APR-2019');

Output:

INSERT 0 1

Query the partitions after insertion:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 partition_name |      high_value
----------------+----------------------------------------------------------------------
 SYS596430103   | FOR VALUES FROM ('15-MAR-19 00:00:00') TO ('15-APR-19 00:00:00')
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(3 rows)

The system created partition SYS596430103 to hold the April 5 row.