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
- 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
casecolumn 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.