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
