Thread: Creating partitions automatically at least on HASH?

Hello pgdevs,

sorry if this has been already discussed, but G did not yield anything 
convincing about that.

While looking at HASH partitioning and creating a few ones, it occured to 
me that while RANGE and LIST partitions cannot be guessed easily, it would 
be easy to derive HASH partitioned table for a fixed MODULUS, e.g. with

   CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);
   -- or some other syntax

Postgres could derive statically the 10 subtables, eg named foo_$0$ to 
foo_$1$.

That would not be a replacement for the feature where one may do something 
funny and doubtful like (MODULUS 2 REMAINDER 0, MODULUS 4 REMAINDER 1, 
MODULUS 4 REMAINDER 3).

The same declarative approach could eventually be considered for RANGE 
with a fixed partition duration and starting and ending points.

This would be a relief on the longer path of dynamically creating 
partitions, but with lower costs than a dynamic approach.

The ALTER thing would be a little pain.

Thoughts?

-- 
Fabien.

On Mon, Jul 15, 2019 at 1:29 AM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> Hello pgdevs,
>
> sorry if this has been already discussed, but G did not yield anything
> convincing about that.
>
> While looking at HASH partitioning and creating a few ones, it occured to
> me that while RANGE and LIST partitions cannot be guessed easily, it would
> be easy to derive HASH partitioned table for a fixed MODULUS, e.g. with
>
>    CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);
>    -- or some other syntax
>
> Postgres could derive statically the 10 subtables, eg named foo_$0$ to
> foo_$1$.
>
> That would not be a replacement for the feature where one may do something
> funny and doubtful like (MODULUS 2 REMAINDER 0, MODULUS 4 REMAINDER 1,
> MODULUS 4 REMAINDER 3).
>
> The same declarative approach could eventually be considered for RANGE
> with a fixed partition duration and starting and ending points.
>
> This would be a relief on the longer path of dynamically creating
> partitions, but with lower costs than a dynamic approach.

Yeah, I think something like this would be reasonable, but I think
that the best syntax is not really clear.  We might want to look at
how other systems handle this.

I don't much like AUTOMATIC.  It doesn't read like SQL's usual
pseudo-English.  WITH would be better, but doesn't work because of
grammar conflicts.  We need something that will let you specify just a
modulus for hash partitions, a start, end, and interval for range
partitions, and a list of bounds for list partitions.  If we're
willing to create a new keyword, we could make PARTITIONS a keyword.
Then:

PARTITION BY HASH (whatever) PARTITIONS 8
PARTITION BY RANGE (whatever) PARTITIONS FROM 'some value' TO 'some
later value' ADD 'some delta'
PARTITION BY LIST (whatever) PARTITIONS ('bound', 'other bound',
('multiple', 'bounds', 'same', 'partition'))

That looks fairly clean.  The method used to generate the names of the
backing tables would need some thought.

> The ALTER thing would be a little pain.

Why would we need to do anything about ALTER?  I'd view this as a
convenience way to set up a bunch of initial partitions, nothing more.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jul 15, 2019 at 10:54 AM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Jul 15, 2019 at 1:29 AM Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> > Hello pgdevs,
> >
> > sorry if this has been already discussed, but G did not yield anything
> > convincing about that.
> >
> > While looking at HASH partitioning and creating a few ones, it occured to
> > me that while RANGE and LIST partitions cannot be guessed easily, it would
> > be easy to derive HASH partitioned table for a fixed MODULUS, e.g. with
> >
> >    CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);
> >    -- or some other syntax
> >
> > Postgres could derive statically the 10 subtables, eg named foo_$0$ to
> > foo_$1$.
> >
> > That would not be a replacement for the feature where one may do something
> > funny and doubtful like (MODULUS 2 REMAINDER 0, MODULUS 4 REMAINDER 1,
> > MODULUS 4 REMAINDER 3).
> >
> > The same declarative approach could eventually be considered for RANGE
> > with a fixed partition duration and starting and ending points.
> >
> > This would be a relief on the longer path of dynamically creating
> > partitions, but with lower costs than a dynamic approach.
>
> Yeah, I think something like this would be reasonable, but I think
> that the best syntax is not really clear.  We might want to look at
> how other systems handle this.

Greenplum has a syntax that covers some cases but not the hash case.

For range based partitions we have:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

This is equivelant to the below so you can also declare and name each
partition individually. For example:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( PARTITION Jan16 START (date '2016-01-01') INCLUSIVE ,
  PARTITION Feb16 START (date '2016-02-01') INCLUSIVE ,
  PARTITION Mar16 START (date '2016-03-01') INCLUSIVE ,
  PARTITION Apr16 START (date '2016-04-01') INCLUSIVE ,
  PARTITION May16 START (date '2016-05-01') INCLUSIVE ,
  PARTITION Jun16 START (date '2016-06-01') INCLUSIVE ,
  PARTITION Jul16 START (date '2016-07-01') INCLUSIVE ,
  PARTITION Aug16 START (date '2016-08-01') INCLUSIVE ,
  PARTITION Sep16 START (date '2016-09-01') INCLUSIVE ,
  PARTITION Oct16 START (date '2016-10-01') INCLUSIVE ,
  PARTITION Nov16 START (date '2016-11-01') INCLUSIVE ,
  PARTITION Dec16 START (date '2016-12-01') INCLUSIVE
                  END (date '2017-01-01') EXCLUSIVE );

You can do similar things with numeric

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
  DEFAULT PARTITION extra );

ENUM

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

Also it supports multilevel partitioning using a PARTITION TEMPLATE
and SUBPARTITION TEMPLATE. The partitioning template ensures that the
structure at every level is the same.

CREATE TABLE p3_sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
    SUBPARTITION BY RANGE (month)
       SUBPARTITION TEMPLATE (
        START (1) END (13) EVERY (1),
        DEFAULT SUBPARTITION other_months )
           SUBPARTITION BY LIST (region)
             SUBPARTITION TEMPLATE (
               SUBPARTITION usa VALUES ('usa'),
               SUBPARTITION europe VALUES ('europe'),
               SUBPARTITION asia VALUES ('asia'),
               DEFAULT SUBPARTITION other_regions )
( START (2002) END (2012) EVERY (1),
  DEFAULT PARTITION outlying_years );

-- Rob

Hello Robert and Robert,

>>
>>    CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);
>>    -- or some other syntax
>>
>> This would be a relief on the longer path of dynamically creating
>> partitions, but with lower costs than a dynamic approach.
>
> Yeah, I think something like this would be reasonable, but I think
> that the best syntax is not really clear.  We might want to look at
> how other systems handle this.

> I don't much like AUTOMATIC.  It doesn't read like SQL's usual
> pseudo-English.

My English is kind-of broken. The intention is to differentiate the 3
cases with some syntax to say very clearly whether:

  - no partitions are created immediately (current case)
    but will have to be created manually later

  - static partitions are created automatically, based on provided
    parameters

  - dynamic partitions will be created later, when needed, based
    on provided parameters again.

Even if all that is not implemented immediately.

> We need something that will let you specify just a modulus for hash 
> partitions, a start, end, and interval for range partitions, and a list 
> of bounds for list partitions.  If we're willing to create a new 
> keyword, we could make PARTITIONS a keyword. Then:
>
> PARTITION BY HASH (whatever) PARTITIONS 8

I think that it should reuse already existing keywords, i.e. MODULUS 
should appear somewhere.

Maybe:

   ... PARTITION BY HASH (whatever)
       [ CREATE [IMMEDIATE | DEFERRED] PARTITIONS (MODULUS 8) |
         NOCREATE or maybe NO CREATE ];

This way the 3 cases are syntactically covered. Then they just need to be 
implemented:-) The IMMEDIATE case for HASH is pretty straightforward.

> PARTITION BY RANGE (whatever) PARTITIONS FROM 'some value' TO 'some
> later value' ADD 'some delta'

Robert Eckhardt "greenplum" syntax for ranges looks okay as well, and 
cover some corner cases (default, included/excluded bound...).

> PARTITION BY LIST (whatever) PARTITIONS ('bound', 'other bound',
> ('multiple', 'bounds', 'same', 'partition'))

Possibly.

> That looks fairly clean.  The method used to generate the names of the
> backing tables would need some thought.

Pg has a history of doing simple things, eg $ stuff on constraints, _pk 
for primary keys... I would not look too far.

>> The ALTER thing would be a little pain.
>
> Why would we need to do anything about ALTER?  I'd view this as a
> convenience way to set up a bunch of initial partitions, nothing more.

I'm naïve: I'd like that the user could change their mind about a given 
parameter and change it with ALTER:-)

-- 
Fabien.

Hello Robert & Robert,

> - no partitions are created immediately (current case)
>   but will have to be created manually later
>
> - static partitions are created automatically, based on provided
>   parameters
>
> - dynamic partitions will be created later, when needed, based
>   on provided parameters again.
>
> Even if all that is not implemented immediately.
>
>> We need something that will let you specify just a modulus for hash 
>> partitions, a start, end, and interval for range partitions, and a list of 
>> bounds for list partitions.  If we're willing to create a new keyword, we 
>> could make PARTITIONS a keyword. Then:
>> 
>> PARTITION BY HASH (whatever) PARTITIONS 8
>
> I think that it should reuse already existing keywords, i.e. MODULUS should 
> appear somewhere.
>
> Maybe:
>
>  ... PARTITION BY HASH (whatever)
>      [ CREATE [IMMEDIATE | DEFERRED] PARTITIONS (MODULUS 8) |
>        NOCREATE or maybe NO CREATE ];

I have given a small go at the parser part of that.

There are 3 types of partitions with 3 dedicated syntax structures to 
handle their associated parameters (WITH …, FROM … TO …, IN …). ISTM that 
it is a "looks good from far away" idea, but when trying to extend that it 
is starting to be a pain. If a 4th partition type is added, should it be 
yet another syntax? So I'm looking for an generic and extensible syntax 
that could accomodate all cases for automatic creation of partitions.

Second problem, adding a "CREATE" after "PARTITION BY … (…)" create 
shift-reduce conflicts with potential other CREATE TABLE option 
specification syntax. Not sure which one, but anyway. So the current 
generic syntax I'm considering is using "DO" as a trigger to start the 
optional automatic partition creation stuff:

   CREATE TABLE Stuff (...)
     PARTITION BY [HASH | RANGE | LIST] (…)
       DO NONE -- this is the default
       DO [IMMEDIATE|DEFERRED] USING (…)

Where the USING part would be generic keword value pairs, eg:

For HASH: (MODULUS 8) and/or (NPARTS 10)

For RANGE: (START '1970-01-01', STOP '2020-01-01', INCREMENT '1 year')
     and/or (START 1970, STOP 2020, NPARTS 50)

And possibly for LIST: (IN (…), IN (…), …), or possibly some other 
keyword.

The "DEFERRED" could be used as an open syntax for dynamic partitioning, 
if later someone would feel like doing it.

ISTM that "USING" is better than "WITH" because WITH is already used 
specifically for HASH and other optional stuff in CREATE TABLE.

The text constant would be interpreted depending on the partitioning 
expression/column type.

Any opinion about the overall approach?

-- 
Fabien.

Hello Rafia,

>>    CREATE TABLE Stuff (...)
>>      PARTITION BY [HASH | RANGE | LIST] (…)
>>        DO NONE -- this is the default
>>        DO [IMMEDIATE|DEFERRED] USING (…)
>>
>> Where the USING part would be generic keword value pairs, eg:
>>
>> For HASH: (MODULUS 8) and/or (NPARTS 10)
>>
>> For RANGE: (START '1970-01-01', STOP '2020-01-01', INCREMENT '1 year')
>>      and/or (START 1970, STOP 2020, NPARTS 50)
>>
>> And possibly for LIST: (IN (…), IN (…), …), or possibly some other
>> keyword.
>>
>> The "DEFERRED" could be used as an open syntax for dynamic partitioning,
>> if later someone would feel like doing it.
>>
> ISTM that "USING" is better than "WITH" because WITH is already used
>> specifically for HASH and other optional stuff in CREATE TABLE.
>>
>> The text constant would be interpreted depending on the partitioning
>> expression/column type.
>>
>> Any opinion about the overall approach?

> I happen to start a similar discussion [1] being unaware of this one 
> and there Ashutosh Sharma talked about interval partitioning in Oracle. 
> Looking
> closely it looks like we can have this automatic partitioning more
> convenient by having something similar. Basically, it is creating
> partitions on demand or lazy partitioning.

Yep, the "what" of dynamic partitioning is more or less straightforward, 
along the line you are describing.

For me there are really two questions:

  - having a extendable syntax, hence the mail I sent, which would cover
    both automatic static & dynamic partitioning and their parameters,
    given that we already have manual static, automatic static should
    be pretty easy.

  - implementing the stuff, with limited performance impact if possible
    for the dynamic case, which is non trivial.

> To explain a bit more, let's take range partition for example, first 
> parent table is created and it's interval and start and end values are 
> specified and it creates only the parent table just like it works today.

> Now, if there comes a insertion that does not belong to the existing (or 
> any, in the case of first insertion) partition(s), then the 
> corresponding partition is created,

Yep. Now, you also have to deal with race conditions issues, i.e. two 
parallel session inserting tuples that must create the same partition, and 
probably you would like to avoid a deadlock.

> I think it is extensible to other partitioning schemes as well. Also it 
> is likely to have a positive impact on the queries, because there will 
> be required partitions only and would not require to educate 
> planner/executor about many empty partitions.

Yep, but it creates other problems to solve…

-- 
Fabien.

Hello Fabien, Rafia,

Thanks for starting this discussion.

On Tue, Aug 27, 2019 at 5:36 PM Rafia Sabih <rafia.pghackers@gmail.com> wrote:
> On Mon, 26 Aug 2019 at 19:46, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>> > I happen to start a similar discussion [1] being unaware of this one
>> > and there Ashutosh Sharma talked about interval partitioning in Oracle.
>> > Looking
>> > closely it looks like we can have this automatic partitioning more
>> > convenient by having something similar. Basically, it is creating
>> > partitions on demand or lazy partitioning.
>>
>> Yep, the "what" of dynamic partitioning is more or less straightforward,
>> along the line you are describing.
>>
>> For me there are really two questions:
>>
>>   - having a extendable syntax, hence the mail I sent, which would cover
>>     both automatic static & dynamic partitioning and their parameters,
>>     given that we already have manual static, automatic static should
>>     be pretty easy.
>>
>>   - implementing the stuff, with limited performance impact if possible
>>     for the dynamic case, which is non trivial.
>>
>> > To explain a bit more, let's take range partition for example, first
>> > parent table is created and it's interval and start and end values are
>> > specified and it creates only the parent table just like it works today.
>>
>> > Now, if there comes a insertion that does not belong to the existing (or
>> > any, in the case of first insertion) partition(s), then the
>> > corresponding partition is created,
>>
>> Yep. Now, you also have to deal with race conditions issues, i.e. two
>> parallel session inserting tuples that must create the same partition, and
>> probably you would like to avoid a deadlock.
>>
> Hmmm, that shouldn't be very hard. Postgres handles many such things and I think mostly by a mutex guarded shared
memorystructure. E.g. we can have a shared memory structure associated with the parent table holding the information of
allthe available partitions, and keep this structure guarded by mutex. Anytime a new partition has to be created the
relevantinformation is first entered in this structure before actually creating it. 

I like the Fabien's approach to focus on automatic creation of
partitions only "statically" at first, deferring any complex matters
of the "dynamic" counterpart to a later date.  One advantage is that
we get to focus on the details of the UI for this feature, which has
complexities of its own.  Speaking of which, how about the following
variant of the syntax that Fabien proposed earlier:

CREATE TABLE ... PARTITION BY partition_method (list_of_columns)
partition_auto_create_clause

where partition_auto_create_clause is:

PARTITIONS { IMMEDIATE | DEFERRED } USING (partition_descriptor)

where partition_descriptor is:

MODULUS integer | FROM (range_start) END (range_end) INTERVAL
(range_step) | list_values

where range_ start/end/step is:

(expr [,...])

and list_values is:

(expr [,...]) [, ....]

Note that list_values contains one parenthesized list per partition.
This is slightly different from what Robert suggested upthread in that
even a single value needs parentheses.

Automatic creation of multi-column range partitions seems a bit tricky
as thinking about a multi-column "interval" is tricky.

Needless to say, PARTITIONS DEFERRED will cause an unsupported feature
error in the first cut.

Thanks,
Amit