By Mouhamadou Diaw

The Beta 1 version PostgreSQL 10 is now released with lot of new features. One of the most popular is the native support of table partitioning.
In this blog we are going to see how we can implement partitioned table in PostgreSQL 10. Note that RANGE and LIST partition are the supported methods. In following example RANGE partition is used.
The first step is to create the table to be partitioned, let’s say for example table sales.

postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)


create table sales (year date,
product varchar(10),
total int)
partition by range (year);


postgres=# d sales
Table "public.sales"
Column | Type | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
year | date | | not null |
product | character varying(10) | | |
total | integer | | |
Partition key: RANGE (year)

After the table we have to create the corresponding partitions. Each partition’s definition must specify the bounds that correspond to the partitioning method and partition key of the parent

postgres=# create table sales_2014 partition of sales for values from ('2014-01-01') to ('2014-12-31');
CREATE TABLE
postgres=# create table sales_2015 partition of sales for values from ('2015-01-01') to ('2015-12-31');
CREATE TABLE
postgres=# create table sales_2016 partition of sales for values from ('2016-01-01') to ('2016-12-31');
CREATE TABLE
postgres=# create table sales_2017 partition of sales for values from ('2017-01-01') to ('2017-12-31');
CREATE TABLE


postgres=# d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | sales | table | postgres
public | sales_2014 | table | postgres
public | sales_2015 | table | postgres
public | sales_2016 | table | postgres
public | sales_2017 | table | postgres
(5 rows)

Now let’s insert some data into the base table sales.
postgres=# insert into sales values ('2014-05-23','art1',20000);
INSERT 0 1
postgres=# insert into sales values ('2014-12-12','art2',120000);
INSERT 0 1
postgres=# insert into sales values ('2014-05-07','art3',2050);
INSERT 0 1
postgres=# insert into sales values ('2015-10-23','art1',23000);
INSERT 0 1
postgres=# insert into sales values ('2015-10-03','art2',3000);
INSERT 0 1
postgres=# insert into sales values ('2015-01-02','art3',25400);
INSERT 0 1
postgres=# insert into sales values ('2016-03-04','art1',1256802);
INSERT 0 1
postgres=# insert into sales values ('2016-05-08','art2',320000);
INSERT 0 1
postgres=# insert into sales values ('2016-08-11','art3',220000);
INSERT 0 1
postgres=# insert into sales values ('2017-07-06','art1',320000);
INSERT 0 1
postgres=#

Querying base table sales we can see inserted data.

postgres=# table sales;
year | product | total
------------+---------+---------
2014-05-23 | art1 | 20000
2014-12-12 | art2 | 120000
2014-05-07 | art3 | 2050
2015-10-23 | art1 | 23000
2015-10-03 | art2 | 3000
2015-01-02 | art3 | 25400
2016-03-04 | art1 | 1256802
2016-05-08 | art2 | 320000
2016-08-11 | art3 | 220000
2017-07-06 | art1 | 320000
(10 rows)

But what is important is that all data are stored in the partitions not in the base table. As we can see the query on the base table sales only, does not return any value.

postgres=# select * from only sales;
year | product | total
------+---------+-------
(0 rows)
postgres=#

This can be verified also in the execution plan of the query

postgres=# explain select * from sales;
QUERY PLAN
---------------------------------------------------------------------
Append (cost=0.00..84.00 rows=4400 width=46)
-> Seq Scan on sales_2014 (cost=0.00..21.00 rows=1100 width=46)
-> Seq Scan on sales_2015 (cost=0.00..21.00 rows=1100 width=46)
-> Seq Scan on sales_2016 (cost=0.00..21.00 rows=1100 width=46)
-> Seq Scan on sales_2017 (cost=0.00..21.00 rows=1100 width=46)
(5 rows)

We can also query corresponding partitions

postgres=# table sales_2014;
year | product | total
------------+---------+--------
2014-05-23 | art1 | 20000
2014-12-12 | art2 | 120000
2014-05-07 | art3 | 2050
(3 rows)
postgres=# table sales_2015;
year | product | total
------------+---------+-------
2015-10-23 | art1 | 23000
2015-10-03 | art2 | 3000
2015-01-02 | art3 | 25400
(3 rows)
postgres=# table sales_2016;
year | product | total
------------+---------+---------
2016-03-04 | art1 | 1256802
2016-05-08 | art2 | 320000
2016-08-11 | art3 | 220000
(3 rows)
postgres=# table sales_2017;
year | product | total
------------+---------+--------
2017-07-06 | art1 | 320000
(1 row)
postgres=#

If I don’t need values for the year 2014, the easiest way is just to drop the corresponding partition like
DROP TABLE sales_2014;
Another way is to simply detach the partition from the partitioned table but retain access to it as a table in its own right

postgres=# ALTER TABLE sales DETACH PARTITION sales_2014;
ALTER TABLE

We can see new data in sales table and that data of year 2014 are no longer in the table

postgres=# table sales;
year | product | total
------------+---------+---------
2015-10-23 | art1 | 23000
2015-10-03 | art2 | 3000
2015-01-02 | art3 | 25400
2016-03-04 | art1 | 1256802
2016-05-08 | art2 | 320000
2016-08-11 | art3 | 220000
2017-07-06 | art1 | 320000
(7 rows)

One advantage of detaching the partition is that we can move data in slower storage.
If for any reason we need to add them again to the partitioned table sales we can just execute

postgres=# ALTER TABLE sales ATTACH PARTITION sales_2014 for values from ('2014-01-01') to ('2014-12-31');
ALTER TABLE
postgres=#


postgres=# table sales;
year | product | total
------------+---------+---------
2014-05-23 | art1 | 20000
2014-12-12 | art2 | 120000
2014-05-07 | art3 | 2050
2015-10-23 | art1 | 23000
2015-10-03 | art2 | 3000
2015-01-02 | art3 | 25400
2016-03-04 | art1 | 1256802
2016-05-08 | art2 | 320000
2016-08-11 | art3 | 220000
2017-07-06 | art1 | 320000
(10 rows)

In the documentation we can find following limitations apply to partitioned tables:

There is no facility available to create the matching indexes on all partitions automatically. Indexes must be added to each partition with separate commands. This also means that there is no way to create a primary key, unique constraint, or exclusion constraint spanning all partitions; it is only possible to constrain each leaf partition individually.

Since primary keys are not supported on partitioned tables, foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to some other table.

Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

An UPDATE that causes a row to move from one partition to another fails, because the new value of the row fails to satisfy the implicit partition constraint of the original partition.

Row triggers, if necessary, must be defined on individual partitions, not the partitioned table

Post Views: 379
Thumbnail [60x60]
by
Oracle Team