Limits of table partitioning (in PostgreSQL) I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version).


I have a data warehouse application with a central table that has a sizeable number of columns, two of these being JSONB columns.
As common for data warehouse applications, I have a number of indices on the central table, roughly one for each query type that is running.
The central table has a case column that contains an integer value and identifies one subset of data.
After the import, the records for a particular case are immutable (but I might need to purge all records for a case and re-import it once in a while).
For each case, I have ~100k to 3M records (a pretty much bimodal distribution with 70% making up ~100k, and 30% making up ~3M records).
I currently have one thousand cases and would expect this to grow by one thousand cases a year.
95% of all queries to go a single case. 5% of all queries go to a dozen or maybe 200 cases.


Now my questions:


Would it make sense to partition by case ID for performance improvements? I'm currently not so much concerned about query performance (Postgres is really awesome) but more about import times.
If I'm partitioning by case, would I hash the case ID integer or would I start a new one-element range for each case?
If I'm getting 10k or 100k cases, would Postgres scale to this number of partitions?
I'm considering using something like Citrus for sharding over multiple physical servers. Is there anything to keep in mind here?


I did quite some online search regarding this topic but did not find much beyond table partitioning for log files.
I'd be more than happy if you could direct me to good resources for research.
100k partitions is an awful lot.  I would not try that without doing some serious testing first.  Include in the testing things like your backup scripts, and pg_upgrade runs (you don't want to design a database schema that cannot be feasibly upgraded to the next major version). Or just don't do it.

You would probably get most of the advantages of partitions without resorting to one partition per case.  You could use hash partitioning (new in v11) or range partitioning, for example, to have a couple hundred partitions with a few hundred cases per partition.


  5% of all queries go to a dozen or maybe 200 cases.


How does that get expressed?  A app-generated in-list of literal case_id? A join to a parent case table with a where clause to identify the dozen to 200?