Advanced How-tos Intermediate
Amul Sul Principal Software Engineer May 5, 2020
This article introduces the interval partitioning feature for EDB Postgres Advanced Server and demonstrates how it can be used to automatically create new table partitions whenever required.
EDB Postgres Advanced Server v12 (EPAS) introduces the Interval Partitioning feature. Interval partitioning allows a database to automatically create a new partition when newly inserted data exceeds the range of an existing partition. Interval partitioning is an extension to range partitioning to handle situations where the specific range will be never-ending but range partitions need to be created whenever required—for example, a table partitioned on a date range where the data to be loaded progresses uniformly in a timely manner and the required partition for that date range needs to be created in time order.
The EPAS partitioning syntax has been extended to include an INTERVAL clause, which specifies the range size for a new partition to be created.
Here is the interval partitioning syntax for the CREATE TABLE command:
CREATE TABLE table_name ( … ) PARTITION BY RANGE( … ) INTERVAL (expression) ( Range_partitions_definition, ... );
For the complete creation of the interval range partitioning syntax, see the EDB Postgres Advance Server User Guide Database Compatibility for Oracle® Developer’s Guide Section 10.3.1.
The ALTER TABLE syntax allows you to transform existing range partitioned table to the interval partition as:
ALTER TABLE table_name SET INTERVAL ( … );
For additional information about the ALTER TABLE interval partitioning syntax, see Database Compatibility for Oracle® Developer’s Guide Section 10.3.9.
Consider a partitioned table containing sales data that is divided into monthly partitions according to sale date as follows:
CREATE TABLE sales ( sale_date DATE, country_code TEXT, product_sku TEXT, units INTEGER ) PARTITION BY RANGE (sale_date) ( PARTITION part_01 values LESS THAN (TO_DATE('01-FEB-2020','DD-MON-YYYY')), PARTITION part_02 values LESS THAN (TO_DATE('01-MAR-2020','DD-MON-YYYY')) );
This partitioned sales table can accommodate data through FEB-2020; if data is loaded into this table with a later date than FEB-2020, it will fail with the following error:
edb=# INSERT INTO sales VALUES ('2020-03-12', 'NJ', 'P-000K8', 80); ERROR: no partition of relation "sales" found for row DETAIL: Partition key of the failing row contains (sale_date) = (12-MAR-20 00:00:00).
This will keep failing until the required partitions are created. This could be a serious issue if the system is mission critical and has gone through a huge complex computation for the sale data to be inserted.
In such a situation, interval partitioning can help. We can alter this existing range partition to an interval partition so that required partitions can be added automatically:
ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));
Now, try to insert previously failed data again:
edb=# INSERT INTO sales VALUES ('2020-03-12', 'NJ', 'P-000K8', 80); INSERT 0 1
Using the following query you can see a new partition named SYS985610103 has been added to the sales table:
edb=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS; partition_name | high_value ----------------+---------------------- PART_01 | '01-FEB-20 00:00:00' PART_02 | '01-MAR-20 00:00:00' SYS985610103 | '01-APR-20 00:00:00' (3 rows)
Interval partitioning is quite a compelling feature to have, but it does have the following restrictions/limitations that you need to be aware of:
● Interval partitioning is restricted to a single partition key (i.e., if you try to create or alter an existing partitioned table having a multi-column partitioned key, it will fail).
● The supported key must be of numerical or date range type.
● DEFAULT and MAXVALUE cannot be defined for an interval partitioned table.
● Data to be inserted cannot have NULL, Not-a-Number, and Infinity values specified in the partitioning key column:
edb=# INSERT INTO sales VALUES (NULL, 'NJ', 'P-000K8', 80); ERROR: required interval partition for the relation "sales" is not created DETAIL: Interval partitioned table does not allow partition keys to be null.
● The interval partitioning expression must yield a constant value and must not be a negative value.
edb=# CREATE TABLE sales_6 ( sale_date DATE, units INTEGER ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(-1,'MONTH')) ( PARTITION part_01 values LESS THAN (TO_DATE('01-FEB-2020','DD-MON-YYYY')) ); ERROR: interval expression is not a constant of the correct type
● For the interval partitioned table, at least one partition should be defined first. If you create an interval partitioned table or alter an existing partitioned table to be an interval partition, it will fail at INSERT with the following error:
edb=# CREATE TABLE sales_7 ( sale_date DATE, units INTEGER ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')); CREATE TABLE edb=# INSERT INTO sales_7 VALUES ('2020-02-12', 80); ERROR: cannot create interval partition for relation "sales_7" DETAIL: Interval partitioned table must have at least one partition.
Without first defining a partition, it won't be possible to calculate the partitioning bound of the next partition to be created. You can fix that using the ALTER TABLE ... ADD PARTITION command.
As a Principal Software Engineer, Amul works predominantly on database server development at EDB. He is an active contributor in PostgreSQL feature development as well as in EDB Postgres Advanced Server propriety feature development. Amul has a cumulative experience of 7 years in database development. Prior to EDB, he was a Senior Software Engineer at NTT Data. Amul holds a Master degree in Computer Application from Mumbai University.