Define ideal number of hash partitions
The following discussion from !358 (merged) should be addressed before deploying the registry database to pre/staging/prod:
-
@abrandl started a discussion: A remaining question is how many hash partitions to create. This is a fundamental issue and rather difficult to change after the fact. With PG12, handling a larger number of partitions has significantly improved:
https://www.2ndquadrant.com/en/blog/postgresql-12-partitioning/
I would still rather start in the few hundreds range, but a little less conservative than if we were on PG11. I don't have hard data on that and it's a rather arbitrary choice. To nail something down, perhaps start with 256 partitions and once we have some good test data loaded somewhere, we might want to review the most critical queries and also the most "painful" ones (for this partitioning setup) - the only that comes to mind for the latter is the cascading delete to a partitioned table.