Re: When to use PARTITION BY HASH?

Hi,

I use it quite often, since I'm dealing with partitioning keys that have
high cardinality, ie, high number of different values.  If your
cardinality is very high, but your spacing between values is not
uniform, HASH will balance your partitioned tables naturally.  If your
spacing between values is consistent, perhaps RANGE partitioning would
be better.

Regards,
Michael Vitale

Oleksandr Shulgin wrote on 6/2/2020 1:17 PM:
> Hi!
>
> I was reading up on declarative partitioning[1] and I'm not sure what
> could be a possible application of Hash partitioning.
>
> Is anyone actually using it? What are typical use cases?  What
> benefits does such a partitioning scheme provide?
>
> On its face, it seems that it can only give you a number of tables
> which are smaller than the un-partitioned one, but I fail to see how
> it would provide any of the potential advantages listed in the
> documentation.
>
> With a reasonable hash function, the distribution of rows across
> partitions should be more or less equal, so I wouldn't expect any of
> the following to hold true:
> - "...most of the heavily accessed rows of the table are in a single
> partition or a small number of partitions."
> - "Bulk loads and deletes can be accomplished by adding or removing
> partitions...",
> etc.
>
> That *might* turn out to be the case with a small number of distinct
> values in the partitioning column(s), but then why rely on hash
> assignment instead of using PARTITION BY LIST in the first place?
>
> Regards,
> --
> Alex
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>