Default Partition - Adopting the Odds

PostgreSQL 10 introduced declarative partitioning, which included list and range partitioning. But, it had always been an open question as to what happens if we get rows which do not qualify to be placed in any of the given list/range partitions. That's when PostgreSQL 11 introduces a solution to this by adding a DEFAULT partition.

 

What is DEFAULT partition

If a partitioned table has a DEFAULT partition, then a partition key value not fitting into any other partition of the given parent will be routed to DEFAULT partition; i.e. in the case where the table is list partitioned, all the rows which do not fit in any of the lists for given partitions would go to the DEFAULT partition. If the table is range partitioned, then all the rows that do not satisfy the range conditions of any of the partitions would go to the DEFAULT partition.

Syntax:

CREATE TABLE table_name PARTITION OF parent_table_name DEFAULT;

 

DEFAULT partition for list partitioned table:

Let's create a list partitioned table and a couple of partitions on it:

CREATE TABLE list_parted(a int, b int) PARTITION BY LIST(a);

CREATE TABLE list_part_1 PARTITION OF list_parted FOR VALUES IN (1, 2, 3);

CREATE TABLE list_part_2 PARTITION OF list_parted FOR VALUES IN (6, 7, 8);

Now, if we try to insert the following row in Postgres 10, it will throw an error:

postgres=# INSERT INTO list_parted VALUES (4, 44);

ERROR: no partition of relation "list_parted" found for row

DETAIL: Partition key of the failing row contains (a) = (4).

This is because list_parted does not have any partition having ‘4’ listed in its list.

This kind of inserts can now be handled with the introduction of a DEFAULT partition in Postgres 11.

Add a DEFAULT partition list_part_default to list_parted, and any values of column ‘a’ that do not match those listed in list_part_1 and list_part_2 would be inserted in list_part_default.

Let's now add a default partition to store rows that don't qualify for any other partition: 

postgres=# CREATE TABLE list_part_default PARTITION OF list_parted DEFAULT;

CREATE TABLE

postgres=# INSERT INTO list_parted VALUES (1, 11), (4, 44), (7, 77), (9, 99);

INSERT 0 4 

Let’s check how these records are routed to different partitions of list_parted table:

postgres=# SELECT tableoid::regclass, * FROM list_parted;

tableoid | a | b

-------------------+---+----

list_part_1 | 1 | 11

list_part_2 | 7 | 77

list_part_default | 4 | 44

list_part_default | 9 | 99

(4 rows)

list_parted does not have any explicit list partitions covering column ‘a’ having values 4 or 9, but it still accepted those rows where a=4 or a=9, and routed them to DEFAULT partition list_part_default.

Now, let's look at what constraints are defined on the DEFAULT partition: 

postgres=# \d+ list_part_default

Table "public.list_part_default"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+---------+---------+--------------+-------------

a | integer | | | | plain | |

b | integer | | | | plain | |

Partition of: list_parted DEFAULT

Partition constraint: (NOT ((a IS NOT NULL) AND (a = ANY (ARRAY[1, 2, 3, 6, 7, 8]))))

The partition constraint on the DEFAULT partition list_part_default is built such that it would accept all the values other than the ones that are accepted by partitions list_part_1 and list_part_2.

 

DEFAULT partition for range partitioned table:

Similar to list partitions, a DEFAULT partition can be added to a range-partitioned table as of Postgres 11.

Let's see an example of DEFAULT partitions for range-partitioned tables as well: 

CREATE TABLE range_parted (a date) PARTITION BY RANGE (a);

CREATE TABLE range_part_1 PARTITION OF range_parted FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');

CREATE TABLE range_part_2 PARTITION OF range_parted FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');

CREATE TABLE range_part_default PARTITION OF range_parted DEFAULT;

Let's insert a record with a date that won't fit ranges for range_part_1 and range_part_2:

postgres=# INSERT INTO range_parted VALUES ('2017-01-01');

INSERT 0 1

postgres=# SELECT tableoid::regclass, * FROM range_parted;

tableoid | a

--------------------+------------

range_part_default | 2017-01-01

(1 row)

The record is inserted in the DEFAULT partition range_part_default.

 

Limitations:

If a DEFAULT partition exists, a new partition cannot be added if there are any rows in DEFAULT partition such that they would otherwise fit in the new partition being added. This is because it will require row movement from DEFAULT to newly added partition, and it’s a costly operation to do implicitly.

E.g. the following command for creating list_part_3 would fail: 

postgres=# CREATE TABLE list_part_3 PARTITION OF list_parted FOR VALUES IN (4, 9, 10);

ERROR: updated partition constraint for default partition "list_part_default" would be violated by some row

The reason is list_part_default already has rows with partition key ‘a’ having values 4 and 9. If list_part_3 is added allowing values 4, 9 and 10, then there will be two partitions having records with the same partition key value.

Instead, if we have to create list_part_3, one of the workarounds would be to create a table by selecting rows out of list_part_default that match the new values set, and then delete those rows from list_part_default, and attach the new table as a partition to list_parted.

Jeevan Ladhe is a Principal Software Engineer at EnterpriseDB.