Infrastructure at your Service

Daniel Westermann

EDB EPAS 12 comes with interval partitioning

While community PostgreSQL 12 is in beta quite some time now (currently in beta 3) it usually takes some time until EDB EPAS will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access here, release notes here. One of the new features is interval partitioning which you already might know from Oracle. Basically you do not need to create range partitions in advance but the system will create the partitions for you once you add data that does not fit into any of the current partitions. Lets see how that works.

Without interval partitioning you would need to create a range partitioned table like this (note that this is Oracle syntax which is supported by EPAS but not by community PostgreSQL):

create table my_part_tab ( id int
                         , dummy text
                         , created date
                         )
                         partition by range (created)
                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))
                         );

The issue with that is, that once you want to add data that does not fit into any of the current partitions you will run into issues like that:

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# select * from my_part_tab;
 id | dummy |      created       
----+-------+--------------------
  1 | aaa   | 05-JAN-19 00:00:00
(1 row)

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
psql: ERROR:  no partition of relation "my_part_tab" found for row
DETAIL:  Partition key of the failing row contains (created) = (05-FEB-19 00:00:00).
edb=# 

Only when you create the partition required manually you will be able to store the data (or it goes to a default partition, which comes with its own issues):

edb=# alter table my_part_tab add partition my_part_tab_2 values less than (to_date('01.03.2019','DD.MM.YYYY'));
ALTER TABLE
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# select * from my_part_tab;
 id | dummy |      created       
----+-------+--------------------
  1 | aaa   | 05-JAN-19 00:00:00
  1 | aaa   | 05-FEB-19 00:00:00
(2 rows)
edb=# \d+ my_part_tab
                                     Partitioned table "public.my_part_tab"
 Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti
---------+-----------------------------+-----------+----------+---------+----------+--------------+----------
 id      | integer                     |           |          |         | plain    |              | 
 dummy   | text                        |           |          |         | extended |              | 
 created | timestamp without time zone |           |          |         | plain    |              | 
Partition key: RANGE (created) NULLS LAST
Partitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),
            my_part_tab_my_part_tab_2 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00')

Of course it is not a big deal to create the partitions for the next 20 years in advance but there is a more elegant way of doing this by using interval partitioning:

drop table my_part_tab;
create table my_part_tab ( id int
                         , dummy text
                         , created date
                         )
                         partition by range (created)
                         interval (numtoyminterval(1,'month'))
                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))
                         );

Having the table partitioned like that new partitions will be created on the fly as required:

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.03.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# \d+ my_part_tab
                                     Partitioned table "public.my_part_tab"
 Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti
---------+-----------------------------+-----------+----------+---------+----------+--------------+----------
 id      | integer                     |           |          |         | plain    |              | 
 dummy   | text                        |           |          |         | extended |              | 
 created | timestamp without time zone |           |          |         | plain    |              | 
Partition key: RANGE (created) INTERVAL ('1 mon'::interval)
Partitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),
            my_part_tab_sys138880102 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00'),
            my_part_tab_sys138880103 FOR VALUES FROM ('01-MAR-19 00:00:00') TO ('01-APR-19 00:00:00')

edb=# 

A nice addition which is not (yet) available in community PostgreSQL.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure