Thread: When to use PARTITION BY HASH?

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?

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

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?

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

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

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

Please don't cross post to multiple lists without any particular reason
for doing so- pick whichever list makes sense and post to that.

On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:

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.

From my point of view, hash partitioning is very useful for spreading out high insert/update load.

Yes its' true you end up with more smaller tables than one big large one, but remember the indexes are (often) tree data structures.  Smaller trees are faster than bigger trees.  By making the indexes smaller they are faster.  Since the planner can knows to only examine the specific index it needs, this ends up being a lot faster.

With hash partitioning you are not expected, in general, to end up with a small number of partitions being accessed more heavily than the rest.  So your indexes will also not fit into memory.

I have the feeling that using a hash function to distribute rows simply contradicts the basic assumption of when you would think of partitioning your table at all: that is to make sure the most active part of the table and indexes is small enough to be cached in memory.

On Wed, Jun 3, 2020 at 7:55 AM Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:

With hash partitioning you are not expected, in general, to end up with a small number of partitions being accessed more heavily than the rest.  So your indexes will also not fit into memory.

I have the feeling that using a hash function to distribute rows simply contradicts the basic assumption of when you would think of partitioning your table at all: that is to make sure the most active part of the table and indexes is small enough to be cached in memory.

While hash partitioning doesn't appeal to me, I think this may be overly pessimistic.  It would not be all that unusual for your customers to take turns being highly active and less active.  Especially if you do occasional bulk loads all with the same customer_id for any given load, for example.

So while you might not have a permanently hot partition, you could have partitions which are hot in turn.  Of course you could get the same benefit (and probably better) with list or range partitioning rather than hash, but then you have to maintain those lists or ranges when you add new customers.

On Thu, Jun 4, 2020 at 4:32 PM Jeff Janes <jeff.janes@gmail.com> wrote:

On Wed, Jun 3, 2020 at 7:55 AM Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:

With hash partitioning you are not expected, in general, to end up with a small number of partitions being accessed more heavily than the rest.  So your indexes will also not fit into memory.

I have the feeling that using a hash function to distribute rows simply contradicts the basic assumption of when you would think of partitioning your table at all: that is to make sure the most active part of the table and indexes is small enough to be cached in memory.

While hash partitioning doesn't appeal to me, I think this may be overly pessimistic.  It would not be all that unusual for your customers to take turns being highly active and less active.  Especially if you do occasional bulk loads all with the same customer_id for any given load, for example.

For a bulk load you'd likely want to go with an empty partition w/o indexes and build them later, after loading the tuples. 

So while you might not have a permanently hot partition, you could have partitions which are hot in turn.  Of course you could get the same benefit (and probably better) with list or range partitioning rather than hash, but then you have to maintain those lists or ranges when you add new customers.

Why are LRU eviction from the shared buffers and OS disk cache not good enough to handle this?

This actually applies to any partitioning scheme: the hot dataset could be recognized by these caching layers.  Does it not happen in practice?

Do you also assign the partitions to different tablespaces as you've hinted below or do you see performance improvement from partitioning alone?  How does that work?  Does it give better  results than using a RAID to spread the disk IO, for example?

Yes its' true you end up with more smaller tables than one big large one, but remember the indexes are (often) tree data structures.  Smaller trees are faster than bigger trees.  By making the indexes smaller they are faster.  Since the planner can knows to only examine the specific index it needs, this ends up being a lot faster.

That sounds logical, but can it be demonstrated?  If the index(es) fit in memory fully, it doesn't make a measurable difference, I guess?

With hash partitioning you are not expected, in general, to end up with a small number of partitions being accessed more heavily than the rest.  So your indexes will also not fit into memory.

I have the feeling that using a hash function to distribute rows simply contradicts the basic assumption of when you would think of partitioning your table at all: that is to make sure the most active part of the table and indexes is small enough to be cached in memory.

Regards,

--
Alex

Well lets take a step back here and look at the question, hash partitioning exists in Postgres, is it useful?  While I appreciate the need to see a fact demonstrated, and generally avoiding argument by authority, it is true that many of the very smartest database people in the world conceived of, discussed, implemented and documented this feature for us.   It stands to reason that it is useful, or it wouldn't exist.  So maybe this is more about finding or needing better partitioning documentation.

With hash partitioning you are not expected, in general, to end up with a small number of partitions being accessed more heavily than the rest.  So your indexes will also not fit into memory.

Indexes are not (usually) constant time structures, they take more time the bigger they get.  So partitioned indexes will be smaller, quicker to insert into, and quicker to vacuum, and also gain possible pruning advantages on query when you split them up.  If the planner can, knowing the key, exclude all but one partition, it won't even look at the other tables, so if you hash partition by primary key, you reduce the search space to 1/N immediately. 

Indexes with high update activity also suffer from a problem called "index bloat" where spares "holes" get punched in the buckets of btree indexes from updates and delete deletes.  These holes are minimized by vacuuming but the bigger the index gets, the harder that process is to maintain.  Smaller indexes suffer less from index bloat, and remedying the situation is easier because you can reindex partitions independently of each other.  Your not just reducing the query load to an Nth, you're reducing the maintenance load.

I have the feeling that using a hash function to distribute rows simply contradicts the basic assumption of when you would think of partitioning your table at all: that is to make sure the most active part of the table and indexes is small enough to be cached in memory.

I think you might be framing this with a specific data pattern in mind, not all data distributions have a "most active" or power law distribution of data.