Re: When to use PARTITION BY HASH?

On Wed, Jun 3, 2020 at 4:55 AM Oleksandr Shulgin <
oleksandr(dot)shulgin(at)zalando(dot)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

>
> Regards,
> --
> Alex
>
>