Re: [POC] hash partitioning

On Wed, 1 Mar 2017 10:52:58 +0530
amul sul <sulamul(at)gmail(dot)com> wrote:

> On Tue, Feb 28, 2017 at 8:03 PM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> 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.

Thanks.

>
> > * 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
> >
> > An inserted record is stored in a partition whose index equals
> > abs(hashfunc(key)) % <number_of_partitions>. In the above
> > example, this is abs(hashint4(i))%3.
> >
> > postgres=# insert into h (select generate_series(0,20));
> > INSERT 0 21
> >
> > postgres=# select *,tableoid::regclass from h;
> > i | tableoid
> > ----+----------
> > 0 | h1
> > 1 | h1
> > 2 | h1
> > 4 | h1
> > 8 | h1
> > 10 | h1
> > 11 | h1
> > 14 | h1
> > 15 | h1
> > 17 | h1
> > 20 | h1
> > 5 | h2
> > 12 | h2
> > 13 | h2
> > 16 | h2
> > 19 | h2
> > 3 | h3
> > 6 | h3
> > 7 | h3
> > 9 | h3
> > 18 | h3
> > (21 rows)
> >
> > * Todo / discussions
> >
> > In this patch, we cannot change the number of partitions specified
> > by PARTITIONS. I we can change this, the partitioning rule
> > (<partition index> = abs(hashfunc(key)) % <number_of_partitions>)
> > is also changed and then we need reallocatiing records between
> > partitions.
> >
> > In this patch, user can specify a hash function USING. However,
> > we migth need default hash functions which are useful and
> > proper for hash partitioning.
> >
> ​IMHO, we should try to keep create partition syntax simple and aligned
> with other partition strategy. For e.g:
> CREATE TABLE h (i int) PARTITION BY HASH(i);
>
> I Agree that it is unavoidable partitions number in modulo hashing,
> but we can do in other hashing technique. Have you had thought about
> Linear hashing[1] or Consistent hashing​[2]?​ This will allow us to
> add/drop
> partition with minimal row moment. ​

Thank you for your information of hash technique. I'll see them
and try to allowing the number of partitions to be changed.

Thanks,
Yugo Nagata

>
> ​+1 for the default hash function corresponding to partitioning key type.​
>
> Regards,
> Amul
> ​
>
> [1] https://en.wikipedia.org/wiki/Linear_hashing
> [2] https://en.wikipedia.org/wiki/Consistent_hashing

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