From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: pgsql-general@lists.postgresql.org
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
Date: Tue, 2 Jun 2020 13:39:40 -0400
Message-ID: <2835080c-b6ac-d13a-c788-4fd03b2c7a23@sqlexec.com> (raw)
In-Reply-To: <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
References: <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: MichaelDBA@sqlexec.com, oleksandr.shulgin@zalando.de, pgsql-general@lists.postgresql.org, pgsql-performance@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
In-Reply-To: <2835080c-b6ac-d13a-c788-4fd03b2c7a23@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox