Postgres 11 partitioning

PostgreSQL supported table partitioning  implementation based on inheritance and triggers for over more than a decade now. However, the declarative partition support was added in Postgres 10 release in Oct 2017.  Since Postgres 10, Postgres  supports built-in declarative partitioning so it was easier to create partitions but you still need to manage trigger to update records on parent table. Additionally, you couldn’t able to add Primary Key and Foreign Keys on partitioned tables. The recent release of Postgres 11 solves all of these problems.

Postgres 11 adds a lot more partitioning features to manage partitioned tables easier than ever! Below is the comparison of partitioning features across Postgres releases:

Postgres 11 supports RANGE, LIST and HASH partition types. You can also create sub-partitions  on child tables too!

Let’s take an example to partition the table using RANGE and LIST partition types.

RANGE Partitioning:

 -- create parent table to store SMS campaign subscribers 
app=# CREATE TABLE sms_campaign_subscribers (id bigint not null, sms_campaign_id bigint not null) PARTITION BY RANGE (sms_campaign_id);
CREATE TABLE

-- create child table to store campaign with sms_campaign_id >= 111 and < 112
app=# CREATE TABLE sms_campaign_subscriber_111 PARTITION OF sms_campaign_subscribers FOR VALUES FROM (111) TO (112) ;
CREATE TABLE

-- Describe parent table
app=# \d+ sms_campaign_subscribers
 Table "public.sms_campaign_subscribers"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+--------+-----------+----------+---------+---------+--------------+-------------
 id | bigint | | not null | | plain | |
 sms_campaign_id | bigint | | not null | | plain | |
Partition key: RANGE (sms_campaign_id)
Partitions: sms_campaign_subscriber_111 FOR VALUES FROM ('111') TO ('112')

-- Describe child table

app=# \d+ sms_campaign_subscriber_111
 Table "public.sms_campaign_subscriber_111"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+--------+-----------+----------+---------+---------+--------------+-------------
 id | bigint | | not null | | plain | |
 sms_campaign_id | bigint | | not null | | plain | |
Partition of: sms_campaign_subscribers FOR VALUES FROM ('111') TO ('112')
Partition constraint: ((sms_campaign_id IS NOT NULL) AND (sms_campaign_id >= '111'::bigint) AND (sms_campaign_id < '112'::bigint))
 -- insert into parent table
app=# insert into sms_campaign_subscribers values(1,'111');
INSERT 0 1
 -- query parent table
app=# select * from sms_campaign_subscribers;
 id | sms_campaign_id
----+-----------------
 1 | 111
(1 row)

-- you can also insert directly into child table

app=# insert into sms_campaign_subscriber_111 values(2,'111');
INSERT 0 1

-- query parent table
app=# select * from sms_campaign_subscribers;
 id | sms_campaign_id
----+-----------------
 1 | 111
 2 | 111
(2 rows)

-- query directly child table
app=# select * from sms_campaign_subscriber_111;
 id | sms_campaign_id
----+-----------------
 1 | 111
 2 | 111
(2 rows)

Problem:

While working on partitioning, I was stumbled upon below syntax issue…

app=# CREATE TABLE sms_campaign_subscribers (id bigint not null, sms_campaign_id bigint not null) PARTITION BY RANGE (sms_campaign_id);
CREATE TABLE
app=# CREATE TABLE sms_campaign_subscriber_111 PARTITION OF sms_campaign_subscribers FOR VALUES FROM (111) TO (112) PARTITION BY RANGE(sms_campaign_id);
CREATE TABLE
app=# \d sms_campaign_subscribers
 Table "public.sms_campaign_subscribers"
 Column | Type | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 id | bigint | | not null |
 sms_campaign_id | bigint | | not null |
Partition key: RANGE (sms_campaign_id)
Number of partitions: 1 (Use \d+ to list them.)
app=# \d sms_campaign_subscriber_111
 Table "public.sms_campaign_subscriber_111"
 Column | Type | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 id | bigint | | not null |
 sms_campaign_id | bigint | | not null |
Partition of: sms_campaign_subscribers FOR VALUES FROM ('111') TO ('112')
Partition key: RANGE (sms_campaign_id)

The insert was failing …

 -- insert into parent table
app=# insert into sms_campaign_subscribers values(1,'111');
ERROR: no partition of relation "sms_campaign_subscriber_111" found for row
DETAIL: Partition key of the failing row contains (sms_campaign_id) = (111).
 
-- insert into parent table
app=# insert into sms_campaign_subscriber_111 values(1,111);
ERROR: no partition of relation "sms_campaign_subscriber_111" found for row
DETAIL: Partition key of the failing row contains (sms_campaign_id) = (111).

Solution:

CREATE TABLE sms_campaign_subscriber_111 PARTITION OF sms_campaign_subscribers FOR VALUES FROM (111) TO (112) PARTITION BY RANGE(sms_campaign_id);

In my case, it is actually make sense to use LIST partition instead of RANGE partition.

-- drop table if exists
app=# drop table if exists sms_campaign_subscribers;
DROP TABLE
-- create parent table
app=# CREATE TABLE sms_campaign_subscribers (id bigint not null, sms_campaign_id bigint not null) PARTITION BY LIST (sms_campaign_id);
CREATE TABLE

-- create child table
app=# CREATE TABLE sms_campaign_subscriber_111 PARTITION OF sms_campaign_subscribers FOR VALUES IN (111) ;
CREATE TABLE
app=# \d+ sms_campaign_subscribers
 Table "public.sms_campaign_subscribers"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+--------+-----------+----------+---------+---------+--------------+-------------
 id | bigint | | not null | | plain | |
 sms_campaign_id | bigint | | not null | | plain | |
Partition key: LIST (sms_campaign_id)
Partitions: sms_campaign_subscriber_111 FOR VALUES IN ('111')

-- describe tables
app=# \d+ sms_campaign_subscribers
 Table "public.sms_campaign_subscribers"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+--------+-----------+----------+---------+---------+--------------+-------------
 id | bigint | | not null | | plain | |
 sms_campaign_id | bigint | | not null | | plain | |
Partition key: LIST (sms_campaign_id)
Partitions: sms_campaign_subscriber_111 FOR VALUES IN ('111')

app=# insert into sms_campaign_subscribers values(1,'111');
INSERT 0 1
app=# insert into sms_campaign_subscribers values(2,'111');
INSERT 0 1
app=# select * from sms_campaign_subscribers;
 id | sms_campaign_id
----+-----------------
 1 | 111
 2 | 111
(2 rows)

Let’s try to insert row outside of defined partition key range.. it will fail!

app=# insert into sms_campaign_subscribers values (3,113);
ERROR: no partition of relation "sms_campaign_subscribers" found for row
DETAIL: Partition key of the failing row contains (sms_campaign_id) = (113).

Let’s make use of Postgres 11 feature “default partition” to store data if it doesn’t fall into specified partition range…

app=# CREATE TABLE sms_campaign_subscriber_default PARTITION OF sms_campaign_subscribers DEFAULT ;
CREATE TABLE
app=# insert into sms_campaign_subscribers values (3,113);
INSERT 0 1
app=# select * from sms_campaign_subscribers;
 id | sms_campaign_id
----+-----------------
 1 | 111
 2 | 111
 3 | 113
(3 rows)

Yay. Everything seems to be working 🙂

I went through basic example but you can have PK and FK constraint on partitioned table or even create sub-partitions in Postgres 11. I will explore them in future posts.

I hope this write will help you to get started on exploring partitioning feature in Postgres.