Size of Partitions in Partitioned Tables

According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies that when creating partitioned tables, the partitions should be relatively similar in size. Is this true?

Well, yes it's true. One important reason for partitioning is to reduce the I/O load for queries. That is, only one partition needs to be read for a given query.

If you make a bunch of assumptions, such as:

• An even distribution of rows over queries over time (relative to the partitioning key)

• Record sizes independent of the partitioning key

• Equal access for the partitions

Then you will discover that for "n" partitions the most efficient solution is when they are equal sized.

That said, there are definitely special cases. If the partitioning key is referenced through ranges (between), then hash partitioning is not optimal. If some data is accessed much more frequently (such as the most recent data), then equal-sized partitions may not be suitable. If you are using partitions for fast deletes (by dropping the partition), then hash-based partitions are not suitable.

StackExchange Database Administrators Q#213616, answer score: 2