sqlpostgresqldatabase-partitioning

Postgres SQL - Large Number of Partitions

I am trying to understand any limits (either exact or practical) that exist on the number of partitions for a single Postgres database, and any performance concerns that arise as you approach these limits. To this end, please answer the following related questions:

 

  1. What is the maximum total number of partitions a database can hold?

  2. If I am planning to have more than 10,000 partitions in my database, is there anything I should be concerned about?

  3. Is there any number of partitions at which concerns arise solely because of the sheer number of partitions and, if so, approximately what number of partitions do these concerns arise at?

Solution
    1. About 1,431,650,303. That's the limit of how many relations PostgreSQL can hold, but you need to subtract the ones that are built-in. Still, about 1.4 billion.
    2. Nothing you wouldn't have to be concerned about when preparing to accommodate 10'000 independent tables.
    3. That number is only speculated about in the wider context of how many relations is too many. 10'000 is nowhere near what's speculated. Here's @Laurenz Albe casually testing on a table with 66'000 partitions.

    It's best to test. You can use a simple PL/pgSQL loop to spawn 10'000 partitions in seconds, then a single generate_series() can populate all of them in seconds. After that, stress-test with pgbench and see if its performance is satisfactory.

    I just created an empty table with 10k partitions and I can confirm a select * takes 0.15s on it. That sucks, but if I think about trying to run a long and weird select from 10k tables some other way, it's actually impressively cheap and convenient. Also, silly and expensive if I have no need for all these partitions - to justify it, the access pattern should match what partitioning is designed for:

    The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions.

    Clearly, an unconditional select * is a direct opposite of that.

    If I instead generate 5% unique 21'000'000 random rows, put them in one regular table and one table partitioned by ranges, then select * with a condition that matches a single partition