Re: [POC] hash partitioning

Hi Ammit,

On Wed, 1 Mar 2017 11:14:15 +0900
Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> Nagata-san,
>
> On 2017/02/28 23:33, Yugo Nagata wrote:
> > Hi all,
> >
> > Now we have a declarative partitioning, but hash partitioning is not
> > implemented yet. Attached is a POC patch to add the hash partitioning
> > feature. I know we will need more discussions about the syntax and other
> > specifications before going ahead the project, but I think this runnable
> > code might help to discuss what and how we implement this.
>
> Great!

Thank you!

>
> > * Description
> >
> > In this patch, the hash partitioning implementation is basically based
> > on the list partitioning mechanism. However, partition bounds cannot be
> > specified explicitly, but this is used internally as hash partition
> > index, which is calculated when a partition is created or attached.
> >
> > The tentative syntax to create a partitioned table is as bellow;
> >
> > CREATE TABLE h (i int) PARTITION BY HASH(i) PARTITIONS 3 USING hashint4;
> >
> > The number of partitions is specified by PARTITIONS, which is currently
> > constant and cannot be changed, but I think this is needed to be changed in
> > some manner. A hash function is specified by USING. Maybe, specifying hash
> > function may be ommitted, and in this case, a default hash function
> > corresponding to key type will be used.
> >
> > A partition table can be create as bellow;
> >
> > CREATE TABLE h1 PARTITION OF h;
> > CREATE TABLE h2 PARTITION OF h;
> > CREATE TABLE h3 PARTITION OF h;
> >
> > FOR VALUES clause cannot be used, and the partition bound is
> > calclulated automatically as partition index of single integer value.
> >
> > When trying create partitions more than the number specified
> > by PARTITIONS, it gets an error.
> >
> > postgres=# create table h4 partition of h;
> > ERROR: cannot create hash partition more than 3 for h
>
> Instead of having to create each partition individually, wouldn't it be
> better if the following command
>
> CREATE TABLE h (i int) PARTITION BY HASH (i) PARTITIONS 3;
>
> created the partitions *automatically*?
>
> It makes sense to provide a way to create individual list and range
> partitions separately, because users can specify custom bounds for each.
> We don't need that for hash partitions, so why make users run separate
> commands (without the FOR VALUES clause) anyway? We may perhaps need to
> offer a way to optionally specify a user-defined name for each partition
> in the same command, along with tablespace, storage options, etc. By
> default, the names would be generated internally and the user can ALTER
> individual partitions after the fact to specify tablespace, etc.

I though that creating each partition individually is needed because some
user will want to specify a tablespce to each partition. However, as you
say, that isn't need for many cases because use can move a partition
to other tablespaces afterward by ALTER.

Thanks,
Yugo Nagata

>
> Thanks,
> Amit
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>