PostgreSQL 14: Automatic hash and list partitioning?

Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature:

• You can partition by range, list and hash
• Attaching and detaching partitions
• Foreign keys
• Sub-partitioning
• Indexing and constrains on partitions
• Partition pruning

What is missing, is the possibility to let PostgreSQL create partitions automatically. With this patch this will finally be possible for hash and list partitioning, once it gets committed.

Lets start with list partitioning: Looking at the patch, new syntax is introduced:

CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
CONFIGURATION (values in (1, 2), (3, 4) DEFAULT PARTITION tbl_default);

Taking that as an example we should see all partitions created automatically, if we create a partitioned table like this:

postgres=# create table tpart_list ( a text primary key, b int, c int )
           partition by list(a)
           configuration (values in ('a'),('b'),('c'),('d') default partition tpart_list_default);
CREATE TABLE

That should have created 5 partitions automatically: a,b,c,d and the default partition:

postgres=# d+ tpart_list
                           Partitioned table "public.tpart_list"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | text    |           | not null |         | extended |              | 
 b      | integer |           |          |         | plain    |              | 
 c      | integer |           |          |         | plain    |              | 
Partition key: LIST (a)
Indexes:
    "tpart_list_pkey" PRIMARY KEY, btree (a)
Partitions: tpart_list_0 FOR VALUES IN ('a'),
            tpart_list_1 FOR VALUES IN ('b'),
            tpart_list_2 FOR VALUES IN ('c'),
            tpart_list_3 FOR VALUES IN ('d'),
            tpart_list_default DEFAULT

Nice. The same works for hash partitioned tables but the syntax is slightly different:

CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
CONFIGURATION (modulus 3);

The idea is the same, of course: You need to specify the “configuration” and when you go for hash partitioning you need to provide the modulus:

postgres=# create table tpart_hash ( a int primary key, b text)
           partition by hash (a)
           configuration (modulus 5);
CREATE TABLE
postgres=# d+ tpart_hash
                           Partitioned table "public.tpart_hash"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 a      | integer |           | not null |         | plain    |              | 
 b      | text    |           |          |         | extended |              | 
Partition key: HASH (a)
Indexes:
    "tpart_hash_pkey" PRIMARY KEY, btree (a)
Partitions: tpart_hash_0 FOR VALUES WITH (modulus 5, remainder 0),
            tpart_hash_1 FOR VALUES WITH (modulus 5, remainder 1),
            tpart_hash_2 FOR VALUES WITH (modulus 5, remainder 2),
            tpart_hash_3 FOR VALUES WITH (modulus 5, remainder 3),
            tpart_hash_4 FOR VALUES WITH (modulus 5, remainder 4)

Really nice, great work and thanks to all involved. I hope that the next steps will be:

• Support automatic partition creation for range partitioning
• Support automatic partition creation on the fly when data comes in, which requires a new partition. In the thread this is referenced as “dynamic” partitioning and what is implemented here is referenced as “static” partitioning