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.

Improve this question
4
  • 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.
    – user1822
    Commented Jun 6, 2019 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
    Commented Jun 6, 2019 at 7:42
  • Don't know if you can have more than 65,000 - something about not more than that per query and if you're querying a partitioned table, each partition counts as an object - not terribly sure of my ground here - am currently researching partitioning and read that - don't have reference URL...
    – Vérace
    Commented Aug 9, 2021 at 14:20
  • 1
    You might be interested in this.
    – Vérace
    Commented Aug 10, 2021 at 5:34

1 Answer 1

Reset to default
2

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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