Re: [HACKERS] [POC] hash partitioning

Поиск
Список
Период
Сортировка
От amul sul
Тема Re: [HACKERS] [POC] hash partitioning
Дата
Msg-id CAAJ_b96vxCx1EaANLbEjnJCrLfvvyX2v_yDn-h39JmnmpozxtA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] [POC] hash partitioning  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Список pgsql-hackers
Дерево обсуждения
On Mon, Sep 11, 2017 at 5:30 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Robert Haas wrote:
> On Mon, Sep 11, 2017 at 4:17 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
> >> Rebased 0002 against this commit & renamed to 0001, PFA.
> >
> > Given that we have default partition support now, I am wondering
> > whether hash partitioned tables also should have default partitions.
> > The way we have structured hash partitioning syntax, there can be
> > "holes" in partitions. Default partition would help plug those holes.
>
> Yeah, I was thinking about that, too.  On the one hand, it seems like
> it's solving the problem the wrong way: if you've set up hash
> partitioning properly, you shouldn't have any holes.  On the other
> hand, supporting it probably wouldn't cost anything noticeable and
> might make things seem more consistent.  I'm not sure which way to
> jump on this one.

How difficult/tedious/troublesome would be to install the missing
partitions if you set hash partitioning with a default partition and
only later on notice that some partitions are missing?  I think if the
answer is that you need to exclusive-lock something for a long time and
this causes a disruption in production systems, then it's better not to
allow a default partition at all and just force all the hash partitions
to be there from the start.


I am also leaning toward ​not to support a default partition for a hash partitioned table.

The major drawback I can see is the constraint get created on the default partition
table.  IIUC, constraint on the default partition table are just negation of partition
constraint on all its sibling partitions.

Consider a hash partitioned table having partitions with (modulus 64, remainder 0) ,
...., (modulus 64, remainder 62) hash bound and partition column are col1, col2,...,so on,
then constraint for the default partition will be :

NOT( (satisfies_hash_partition(64, 0, hash_fn1(col1), hash_fn2(col2), ...) && ... &&
      satisfies_hash_partition(64, 62, hash_fn1(col1),hash_fn2(col2), ...))

​Which will be much harmful to the performance than any other partitioning
strategy because it calculate a hash for the same partitioning key multiple time.
We could overcome this by having an another SQL function (e.g satisfies_default_hash_partition)
which calculates hash value once and checks the remainder, and that would be
a different path from the current default partition framework.

​Regards,
Amul​

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Geoghegan
Дата:
Сообщение: Re: [HACKERS] The case for removing replacement selection sort
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: [HACKERS] The case for removing replacement selection sort