On Tue, Feb 28, 2017 at 8:03 PM, Yugo Nagata <nag...@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. > * 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. +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