PolarDB:Interval range partitioning

Introduction

PolarDB for PostgreSQL (Compatible with Oracle) supports interval range partitioning, which allows for automatic partition creation. The interval is defined when you create a range-partitioned table. If new data does not fall within the range of existing partitions, PolarDB for PostgreSQL (Compatible with Oracle) automatically creates a new partition.

Interval range partitioning is an extension to range partitioning. As new data that does not belong to the time range of any existing partitions arrives, the database automatically creates new partitions to hold the data. To implement interval range partitioning, use the INTERVAL clause and specify the time interval for new partitions. The partition key determines the high value, or transition point, above which new partitions are created with the specified interval.

Assume that in an interval range-partitioned table, the time interval is set to one month and the most recent partition is two-month old. As new data arrives, the database only creates a partition for the current month to hold the data. It skips partitions for the months in-between. For example, assume the time range of the latest partition is January 15 to February 15. As data from May 10 is inserted into the table, the database creates a partition for April 15 to May 15 to hold the data while skipping the creation of partitions for February 15 to March 15 and March 15 to April 15.

Limits

The following limitations apply to the INTERVAL clause:

• Only one partition key column can be specified, with the type restricted to numeric or date range.

• At least one range partition must be defined.

• In multi-level partitioning, interval partitioning can be used as the primary partitioning strategy. It cannot be used for sub-partitioning.

• Interval partitioning cannot be used for index-organized tables.

• You cannot create domain indexes on list-partitioned tables.

• A DEFAULT or MAXVALUE partition cannot be defined for an interval partitioned table.

• Partition key columns do not support NULL, Not-a-Number, or Infinity values.

• The interval partitioning expression must produce non-negative constant values.

• Interval partitions can only be created in ascending order.

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>, ...)]

The INTERVAL parameter supports only numerical and time intervals.

• Numerical interval

  Put every 10 adjacent numbers in one partition:

  INTERVAL (10)

• Time interval

  • Set automatic partitioning by year:

    INTERVAL (NUMTOYMINTERVAL(1,'year'))

  • Set automatic partitioning by month:

    INTERVAL (NUMTOYMINTERVAL(1,'month'))

  • Set automatic partitioning by day:

    INTERVAL (NUMTODSINTERVAL(1,'day'))

  • Set automatic partitioning by week:

    INTERVAL (NUMTODSINTERVAL(7,'day'))

For information about other parameters, see CREATE TABLE...PARTITION BY.

Example

In this example, the sales table is interval range-partitioned on the sold_moth column. The purpose of interval range partitioning the table is to create a transition point, above which new partitions are automatically created.

Create an interval range-partitioned table and insert data into the table:

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.
SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
 partition_name |      high_value
----------------+----------------------
 P1             | '15-JAN-19 00:00:00'
 P2             | '15-FEB-19 00:00:00'
(2 rows)

-- Insert data from a date above the transition point.
INSERT INTO sales VALUES (1,200,'10-MAY-2019');
INSERT 0 1

-- Data is inserted, and a new partition with a system-generated name is created to hold it. The partition name varies based on sessions.
SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 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)

Conversion between range-partitioned tables and interval range-partitioned tables

Convert a range-partitioned table into an interval range-partitioned table

Use ALTER TABLE...SET INTERVAL to convert a range-partitioned table into an interval range-partitioned table.

After you set a range or time interval, the database automatically creates a partition based on the specified range or time interval. Data is then inserted into the new partition.

The syntax is as follows:

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

Convert an interval range-partitioned table into a range-partitioned table

Execute the SET INTERVAL() command to disable interval range partitioning. After you disable interval range partitioning, the database transforms an interval range-partitioned table into a range-partitioned table, with the range or time interval for implementing interval range partitioning into that for range partitioning.

The syntax is as follows:

ALTER TABLE <table_name> SET INTERVAL ();

Example

In this example, the partitioning scheme of the sales table is changed from range partitioning to interval range partitioning.

-- Create a range-partitioned table named sales.
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 ALL_TAB_PARTITIONS view.
SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 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 the sales table into an interval range-partitioned table.
ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

-- Insert data beyond the time range of existing partitions.
INSERT INTO sales VALUES (1,100,'05-APR-2019');
INSERT 0 1

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 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)