When to use PARTITION BY HASH?

To: pgsql-general@lists.postgresql.org, pgsql-performance@lists.postgresql.org

Please don't cross post to multiple lists.

On Tue, Jun 02, 2020 at 07:17:11PM +0200, Oleksandr Shulgin wrote:

I was reading up on declarative partitioning[1] and I'm not sure what could
be a possible application of Hash partitioning.

It's a good question. See Tom's complaint here.
/messages/by-id/31605.1586112900@sss.pgh.pa.us

It *does* provide the benefit of smaller indexes and smaller tables, which
might allow seq scans to outpeform index scans.

It's maybe only useful for equality conditions on the partition key, and not
for ranges. Here, it scans a single partition:

postgres=# CREATE TABLE t(i int) PARTITION BY HASH(i); CREATE TABLE t1 PARTITION OF t FOR VALUES WITH (REMAINDER 0, MODULUS 3);
postgres=# CREATE TABLE t2 PARTITION OF t FOR VALUES WITH (MODULUS 3, REMAINDER 1);
postgres=# CREATE TABLE t3 PARTITION OF t FOR VALUES WITH (MODULUS 3, REMAINDER 2);
postgres=# INSERT INTO t SELECT i%9 FROM generate_series(1,9999)i; ANALYZE t;
postgres=# explain analyze SELECT * FROM t WHERE i=3;
Seq Scan on t2 (cost=0.00..75.55 rows=2222 width=4) (actual time=0.021..0.518 rows=2222 loops=1)
Filter: (i = 3)
Rows Removed by Filter: 2222

--
Justin

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:

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

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

Why the cross-posting? (-performance is oriented toward problem solving,
not theory, so -general is the one and only PostgreSQL list this should
have been sent to)

Anyway, quoting the documentation you linked to:

"When choosing how to partition your table, it's also important to consider
what changes may occur in the future. For example, if you choose to have
one partition per customer and you currently have a small number of large
customers, consider the implications if in several years you instead find
yourself with a large number of small customers. In this case, it may be
better to choose to partition by HASH and choose a reasonable number of
partitions rather than trying to partition by LIST and hoping that the
number of customers does not increase beyond what it is practical to
partition the data by."

Hashing does indeed preclude some of the benefits and introduces others.

I suspect that having a hash function that turns its input into a different
output and checking for equality on the output would be better than trying
to "OR" a partition list together in order to combine multiple inputs onto
the same table.

David J.

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

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.

I'm sure there will be many delightful answers to your question, and I look
forward to them! 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.

Postgres can also parallelize queries on partitions. This is different
from a parallel sequential scan, which can also happen per-partition, so
there are multiple levels of parallel opportunity.

And last that I can think of, you can put the different partitions in
different tablespaces, improving the total IO bandwidth.

-Michel

Greetings,

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

* 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.

Yeah, I tend to agree with this.

Is anyone actually using it? What are typical use cases? What benefits
does such a partitioning scheme provide?

I'm sure folks are using it but that doesn't make it a good solution.

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.

Having smaller tables can be helpful when it comes to dealing with
things like VACUUM (particularly since, even though we can avoid having
to scan the entire heap, we have to go through the indexes in order to
clean them up and generally larger tables have larger indexes),
however..

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?

You're entirely correct with this- there's certainly no small number of
situations where you end up with a 'hot' partition when using hashing
(which is true in other RDBMS's too, of course...) and that ends up
being pretty painful to deal with.

Also, you're right that you don't get to do bulk load/drop when using
hash partitioning, which is absolutely one of the largest benefits to
partitioning in the first place, so, yeah, their usefullness is.. rather
limited. Better to do your own partitioning based on actual usage
patterns that you know and the database's hash function certainly
doesn't.

Thanks,

Stephen

On Tue, Jun 2, 2020 at 7:47 PM Stephen Frost <sfrost@snowman.net> wrote:

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

Sorry for the trouble, I should've checked it more carefully.
When posting I did think it may be relevant to the performance list as well.

At the same time, wouldn't it make sense to document this policy explicitly?
/me resists the urge of cross-posting to pgsql-www ;)

Cheers,
--
Alex

On Tue, Jun 2, 2020 at 7:33 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

To: pgsql-general@lists.postgresql.org,

pgsql-performance@lists.postgresql.org

Please don't cross post to multiple lists.

On Tue, Jun 02, 2020 at 07:17:11PM +0200, Oleksandr Shulgin wrote:

I was reading up on declarative partitioning[1] and I'm not sure what

could

be a possible application of Hash partitioning.

It's a good question. See Tom's complaint here.
/messages/by-id/31605.1586112900@sss.pgh.pa.us

It *does* provide the benefit of smaller indexes and smaller tables, which
might allow seq scans to outpeform index scans.

It's maybe only useful for equality conditions on the partition key, and
not
for ranges. Here, it scans a single partition:

postgres=# CREATE TABLE t(i int) PARTITION BY HASH(i); CREATE TABLE t1
PARTITION OF t FOR VALUES WITH (REMAINDER 0, MODULUS 3);
postgres=# CREATE TABLE t2 PARTITION OF t FOR VALUES WITH (MODULUS 3,
REMAINDER 1);
postgres=# CREATE TABLE t3 PARTITION OF t FOR VALUES WITH (MODULUS 3,
REMAINDER 2);
postgres=# INSERT INTO t SELECT i%9 FROM generate_series(1,9999)i; ANALYZE
t;
postgres=# explain analyze SELECT * FROM t WHERE i=3;
Seq Scan on t2 (cost=0.00..75.55 rows=2222 width=4) (actual
time=0.021..0.518 rows=2222 loops=1)
Filter: (i = 3)
Rows Removed by Filter: 2222

I see. So it works with low cardinality in the partitioned column. With
high cardinality an index scan on an unpartitioned table would be
preferable I guess.

The documentation page I've linked only contains examples around
partitioning BY RANGE. I believe it'd be helpful to extend it with some
meaningful examples for LIST and HASH partitioning.

Regards,
--
Alex

(sticking to pgsql-general)

On Tue, Jun 2, 2020 at 7:45 PM Michel Pelletier <pelletier.michel@gmail.com>
wrote:

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.

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

On Wed, Jun 03, 2020 at 09:45:48AM +0200, Oleksandr Shulgin wrote:

I see. So it works with low cardinality in the partitioned column. With
high cardinality an index scan on an unpartitioned table would be
preferable I guess.

The documentation page I've linked only contains examples around
partitioning BY RANGE. I believe it'd be helpful to extend it with some
meaningful examples for LIST and HASH partitioning.

I agree. I think it would also be useful to mention the "benefits" which
aren't likely to apply to hash partitioning.

Would you want to propose an example to include ?
Eventually it needs to be submitted as a patch to -hackers.

--
Justin

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.

Cheers,

Jeff

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. While it might not be
possible with any given partitioning scheme either, using hash partitioning
most certainly precludes that.

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?

--
Alex

"Bulk loads ...",

As I see - There is an interesting bulkload benchmark:

"How Bulkload performance is affected by table partitioning in PostgreSQL"
by Beena Emerson (Enterprisedb, December 4, 2019 )

*SUMMARY: This article covers how benchmark tests can be used to
demonstrate the effect of table partitioning on performance. Tests using
range- and hash-partitioned tables are compared and the reasons for their
different results are explained: 1. Range partitions
2. Hash partitions 3. Combination graphs
4. Explaining the behavior 5. Conclusion*

*"For the hash-partitioned table, the first value is inserted in the first
partition, the second number in the second partition and so on till all the
partitions are reached before it loops back to the first partition again
until all the data is exhausted. Thus it exhibits the worst-case scenario
where the partition is repeatedly switched for every value inserted. As a
result, the number of times the partition is switched in a
range-partitioned table is equal to the number of partitions, while in a
hash-partitioned table, the number of times the partition has switched is
equal to the amount of data being inserted. This causes the massive
difference in timing for the two partition types."*

https://www.enterprisedb.com/postgres-tutorials/how-bulkload-performance-affected-table-partitioning-postgresql

Regards,
Imre

Oleksandr Shulgin <oleksandr.shulgin@zalando.de> ezt írta (időpont: 2020.
jún. 2., K, 19:17):

On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin <
oleksandr.shulgin@zalando.de> wrote:

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.

That only works if the bulk load is starting from zero. If you are adding
a million rows to something that already has 100 million, you would
probably spend more time rebuilding the indexes than you saved by dropping
them. And of course to go with an empty partition, you have to be using
partitioning of some kind to start with; and then you need to be futzing
around creating/detaching and indexing and attaching. With hash
partitioning, you might get much of the benefit with none of the futzing.

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?

Data density. If the rows are spread out randomly throughout the table,
the density of currently relevant tuples per MB of RAM is much lower than
if they are in partitions which align with current relevance. Of course
you could CLUSTER the table on what would otherwise be the partition key,
but clustered tables don't stay clustered, while partitioned ones stay
partitioned. Also, clustering the table wouldn't help with the relevant
data density in the indexes (other than the index being clustered on, or
other ones highly correlated with that one). This can be particularly
important for index maintenance and with HDD, as the OS disk cache is in my
experince pretty bad at deciding when to write dirty blocks which have been
handed to it, versus retain them in the hopes they will be re-dirtied soon,
or have adjacent blocks dirtied and then combined into one write.

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

Caching only happens at the page level, not the tuple level. So if your
hot tuples are interspersed with cold ones, you can get poor caching
effectiveness.

Cheers,

Jeff

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

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?

In general you could find write throughput improvements from all three,
partitioning, tablespacing, and disk striping. It depends on your problem.
Hash partitioning is a common feature in other databases as well. The
hash strategy works for many distributed access patterns.

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?

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. For example i work with a lot of commercial airline position data
that services both real-time queries and ad-hoc analytical queries over
arbitrary airframe identifiers. There is no advantage trying to have a
"most active" data strategy because all airframes in the air at any given
time are by definition most active. A medium sized drone may send out as
many pings as a jumbo jet in a given interval of time.

-Michel

On 6/5/20 8:51 AM, Jeff Janes wrote:

On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin
<oleksandr.shulgin@zalando.de <mailto:oleksandr.shulgin@zalando.de>> wrote:

[snip]

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.

That only works if the bulk load is starting from zero. If you are adding
a million rows to something that already has 100 million, you would
probably spend more time rebuilding the indexes than you saved by dropping
them.

It's too bad that Postgres doesn't have "deferred index updates" during bulk
(but still transactional) loads, where the index nodes are updated /en
masse/ every "commit count" number of rows. That's *really useful* in this
situation, but I've only seen it in one legacy RDBMS.

--
Angular momentum makes the world go 'round.

The article referenced below assumes a worst case scenario for
bulk-loading with hash partitioned tables.  It assumes that the values
being inserted are in strict ascending or descending order with no gaps
(like a sequence number incrementing by 1), thereby ensuring every
partition is hit in order before repeating the process.  If the values
being inserted are not strictly sequential with no gaps, then the
performance is much better.  Obviously, what part of the tables and
indexes are in memory has a lot to do with it as well.

Regards,
Michael Vitale

Imre Samu wrote on 6/5/2020 7:48 AM:

On Sun, 7 Jun 2020 at 23:41, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

The article referenced below assumes a worst case scenario for bulk-loading with hash partitioned tables. It assumes that the values being inserted are in strict ascending or descending order with no gaps (like a sequence number incrementing by 1), thereby ensuring every partition is hit in order before repeating the process. If the values being inserted are not strictly sequential with no gaps, then the performance is much better. Obviously, what part of the tables and indexes are in memory has a lot to do with it as well.

In PostgreSQL 12, COPY was modified to support bulk-inserts for
partitioned tables. This did speed up many scenarios. Internally, how
this works is that we maintain a series of multi insert buffers, one
per partition. We generally only flush those buffers to the table when
the buffer for the partition fills. However, there is a sort of
sanity limit [1]https://github.com/postgres/postgres/blob/master/src/backend/commands/copy.c#L2569 on the number of multi insert buffers we maintain at
once and currently, that is 32. Technically we could increase that
limit, but there would still need to be a limit. Unfortunately, for
this particular case, since we're most likely touching between 199-799
other partitions before hitting the first one again, that will mean
that we really don't get any multi-inserts, which is likely the reason
why the performance is worse for hash partitioning.

With PG12 and for this particular case, you're likely to see COPY
performance drop quite drastically when going from 32 to 33
partitions. The code was more designed for hitting partitions more
randomly rather than in this sort-of round-robin way that we're likely
to get from hash partitioning on a serial column.

David

[1]: https://github.com/postgres/postgres/blob/master/src/backend/commands/copy.c#L2569

On Sat, Jun 6, 2020 at 6:14 PM Michel Pelletier <pelletier.michel@gmail.com>
wrote:

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.

Fair point.

I've found the original commit adding this feature in version 11:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e
It says:

"Hash partitioning is useful when you want to partition a growing data
set evenly. This can be useful to keep table sizes reasonable, which
makes maintenance operations such as VACUUM faster, or to enable
partition-wise join."

It also includes a link to discussion, though that starts in the middle of
a long thread.
The original thread is here:
/messages/by-id/20170228233313.fc14d8b6.nagata@sraoss.co.jp

However, these threads only argue about implementation details and it's not
easy to find a discussion of motivation for this particular partitioning
scheme support.
I guess it was quite obvious to the participants at that point already.

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.

Thanks for taking your time to explain it in detail. Though I do not tend
to believe the insert/scan performance benefit is measurable without trying
it, I do see the benefits for maintenance.

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.

I'm just referring to the first bullet-point in the docs:

"Query performance can be improved dramatically in certain situations,
particularly when most of the heavily accessed rows of the table are in a
single partition or a small number of partitions. The partitioning
substitutes for leading columns of indexes, reducing index size and making
it more likely that the heavily-used parts of the indexes fit in memory."

I think it does not apply to hash partitioning in the general case.

--
Alex

Wow! That is good to know!

Sent from my iPad