0

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.

  • 1
    10k partitions might work with Postgres 11, probably better to wait for Postgres 12 (which is in beta now, so you could try it). 100k partitions will probably not perform well definitely not with 11, and I doubt that the improvements in 12 will make that perform well). But you could use hash partitioning with e.g. 1000 or 5000 partitions (so multiple IDs per partition). Or range partitioning: put IDs 1 - 1000 into one partition, 1001 to 2000 in the next and so on. – a_horse_with_no_name Jun 6 at 1:41
  • Definitely give Postgres 12 a try. Selecting from one partition among, say, 10k that are defined is at least hundreds of times faster in Postgres 12 than in 11, because of the improved partition planning. – amitlan Jun 6 at 7:42
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.