public inbox for [email protected]  

help / color / mirror / Atom feed

From: Anastasia Lubennikova <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Fabien COELHO <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Amul Sul <[email protected]>
Subject: Re: Proposal: Automatic partition creation
Date: Tue, 14 Jul 2020 00:11:56 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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.

I compared the syntax of other databases. You can find an overview here 
[1]. It
seems that there is no industry standard, so every DBMS has its own
implementation. I decided to rely on a Greenplum syntax, as the most 
similar to
the original PostgreSQL syntax.

New proposal is:

CREATE TABLE numbers(int number)
PARTITION BY partition_method (list_of_columns)
USING (partition_desc)

where partition_desc is:

MODULUS n
| 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'
  
example:

CREATE TABLE years(int year)
PARTITION BY RANGE (year)
USING
(START (2006) END (2016) EVERY (1),
DEFAULT PARTITION other_years);

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_cha...

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Proposal: Automatic partition creation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox