Subject: Re: Proposal: Automatic partition creation
References: <7fec3abb-c663-c0d2-8452-a46141be6d4a@postgrespro.ru>
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Fabien COELHO <coelho@cri.ensmp.fr>, Justin Pryzby
 <pryzby@telsasoft.com>, Robert Haas <robertmhaas@gmail.com>,
 Amul Sul <sulamul@gmail.com>
Message-ID: <7c4013db-a628-c8bd-ce54-064389109e0f@postgrespro.ru>
Date: Tue, 14 Jul 2020 00:11:56 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.8.0
MIME-Version: 1.0
In-Reply-To: <7fec3abb-c663-c0d2-8452-a46141be6d4a@postgrespro.ru>
Content-Type: multipart/alternative;
 boundary="------------91C44D38F3400B8633F944F3"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------91C44D38F3400B8633F944F3
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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_change.29

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


--------------91C44D38F3400B8633F944F3
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 06.07.2020 13:45, Anastasia
      Lubennikova wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:7fec3abb-c663-c0d2-8452-a46141be6d4a@postgrespro.ru">The
      previous discussion of automatic partition creation [1] has
      addressed static and dynamic creation of partitions and ended up
      with several syntax proposals.
      <br>
      In this thread, I want to continue this work.
      <br>
      <br>
      ...<br>
      [1]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre">https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre</a><br>
    </blockquote>
    <br>
    <p>Syntax proposal v2, that takes into account received feedback.<br>
      <br>
      I compared the syntax of other databases. You can find an overview
      here [1]. It <br>
      seems that there is no industry standard, so every DBMS has its
      own <br>
      implementation. I decided to rely on a Greenplum syntax, as the
      most similar to <br>
      the original PostgreSQL syntax.<br>
      <br>
      New proposal is:<br>
    </p>
    <pre>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);</pre>
    <p>It is less wordy than the previous version. It uses a free
      keyword option <br>
      style. It covers static partitioning for all methods, default
      partition for <br>
      list and range methods, and can be extended to implement dynamic
      partitioning <br>
      for range partitions.<br>
      <br>
    </p>
    <p>[1]
<a class="moz-txt-link-freetext" href="https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS">https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS</a><br>
      [2]
<a class="moz-txt-link-freetext" href="https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29">https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29</a><br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Anastasia Lubennikova
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>
  </body>
</html>

--------------91C44D38F3400B8633F944F3--