[PATCH] Automatic HASH and LIST partition creation

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Amul Sul <sulamul(at)gmail(dot)com>
Subject: [PATCH] Automatic HASH and LIST partition creation
Date: 2020-08-25 10:14:29
Message-ID: 726c63e2-ef34-8113-f3cf-90e1646e4c2b@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 14.07.2020 00:11, Anastasia Lubennikova wrote:
> On 06.07.2020 13:45, Anastasia Lubennikova wrote:
>> The previous discussion of automatic partition creation [1] has
>> addressed static and dynamic creation of partitions and ended up with
>> several syntax proposals.
>> In this thread, I want to continue this work.
>> ...
>> [1]
>> https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre
> Syntax proposal v2, that takes into account received feedback.
> CREATE TABLE numbers(int number)
> PARTITION BY partition_method (list_of_columns)
> USING (partition_desc)
> where partition_desc is:
> | VALUES IN (value_list), [DEFAULT PARTITION part_name]
> | START ([datatype] 'start_value')
> END ([datatype] 'end_value')
> EVERY (partition_step), [DEFAULT PARTITION part_name]
> where partition_step is:
> [datatype] [number | INTERVAL] 'interval_value'
> It is less wordy than the previous version. It uses a free keyword option
> style. It covers static partitioning for all methods, default
> partition for
> list and range methods, and can be extended to implement dynamic
> partitioning
> for range partitions.
> [1]
> https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS
> [2]
> https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29
Here is the patch for automated HASH and LIST partitioning, that
implements proposed syntax.

Range partitioning is more complicated. It will require new support
function to calculate bounds, new catalog attribute to store them and so
on. So I want to start small and implement automated range partitioning
in a separate patch later.

1) Syntax

New syntax is heavily based on Greenplum syntax for automated
partitioning with one change. Keyword "USING", that was suggested above,
causes shift/reduce conflict with "USING method" syntax of a table
access method. It seems that Greenplum folks will face this problem later.

I stick to CONFIGURATION as an existing keyword that makes sense in this
Any better ideas are welcome.

Thus, current version is:

CREATE TABLE table_name (attrs)
PARTITION BY partition_method (list_of_columns)
CONFIGURATION (partition_desc)

where partition_desc is:

| VALUES IN (value_list) [DEFAULT PARTITION part_name]

This syntax can be easily extended for range partitioning as well.

2) Implementation

PartitionBoundAutoSpec is a new part of PartitionSpec, that contains
information needed to generate partition bounds.

For HASH and LIST automatic partition creation, transformation happens
during parse analysis of CREATE TABLE statement.
transformPartitionAutoCreate() calculates bounds and generates
statements to create partition tables.

Partitions are named in a format: $tablename_$partnum. One can use post
create hook to rename relations.

For LIST partition one can also define a default partition.


The patch lacks documentation, because I expect some details may change
during discussion. Other than that, the feature is ready for review.


Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
auto_part_hash_list_v0.patch text/x-patch 19.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2020-08-25 10:20:21 passwordcheck: Log cracklib diagnostics
Previous Message Pavel Stehule 2020-08-25 09:22:44 Re: proposal - function string_to_table