Re: [HACKERS] [POC] hash partitioning - Mailing list pgsql-hackers

On Wed, 1 Mar 2017 10:52:58 +0530
amul sul <sulamul@gmail.com> wrote:

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