Re: Creating partitions automatically at least on HASH?

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
Cc: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Creating partitions automatically at least on HASH?
Date: 2019-07-15 21:51:08
Message-ID: alpine.DEB.2.21.1907152332350.8986@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2019-07-15 22:05:37 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)
Previous Message Tomas Vondra 2019-07-15 21:05:30 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)