High Performance PostgreSQL for Rails

Use HASH Partitioning for a Fixed Amount of Buckets

PostgreSQL Version 11 added a third type of declarative partitioning called HASH.^[378] If RANGE or LIST types don’t work well for your use case, consider the HASH type.

Imagine that you’ve got a table sized around 500 GB that you’d like to split up. By splitting it up ten ways, you’ll end up with ten 50-GB tables as partitions. The 50 GB tables may be easier to manage and faster to query, especially when the data being queried lives in a single partition, and queries can be issued that include partition identifiers.

To create three partitions, use the MODULUS operator with the primary key id value to route row DML operations into a partition.

Let’s set this up. The following SQL statements ...