Re: Creating partitions automatically at least on HASH?

On Mon, Jul 15, 2019 at 1:29 AM Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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