Re: Define hash partition for certain column values

On 1/11/21 12:36 AM, Tom Lane wrote:
> =?utf-8?B?0JPQvtC70YPQsdC10LLQsCDQr9C90LA=?= <ishsha(at)yandex(dot)ru> writes:
>> Hello, I've found in source code that there is a function satisfies_hash_partition(oid, modulus, remainder, column_values[]) which allows to check if the certain column value will be placed in the certain partition. I' d like to know if there is an opportunity not to check the certain partition but to define which partition will be the certain column value placed in.
> If you want to control what goes where, use list partitioning (or,
> perhaps, range partitioning). Hash is only suitable if you do not
> care which partition any particular row goes to.
>
> Personally, I think hash partitioning is mostly academic, precisely
> because of that. If the partitioning doesn't line up with application
> requirements, you give up too much of the benefit of using partitions.

In non-MBCC systems, hash partitioning minimizes lock conflicts because the
writes aren't all going into the same page.  OLTP systems can use this
feature to distribute writes across pages; some also allow for "mixed
pages", where records from multiple tables get written to the same page. 
(This then means that one DIO is used to read a parent and all it's child
records.  Naturally, range reports are *very* slow, but sometimes OLTP
performance is paramount.)

--
Angular momentum makes the world go 'round.