Re: When to use PARTITION BY HASH? - Mailing list pgsql-general

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.