On Wed, Sep 6, 2023 at 5:27 PM Daulat <daulat.dba@xxxxxxxxx> wrote:
Hi Team,Do we have a max limit of partitions for a table in postgres?
As far as I can tell, there is no real hard limit to that per se. After all, you will eventually run out of OIDs for pg_attribute but that would affect the number of columns in the database.
However I can say that based on past testing you probably don't want thousands of partitions in your database. At least last time I tried, planning time would eventually become unreasonably high though I assume things have improved somewhat since then (and to be fair, that system was also in a pretty unusual configuration that may have made things worse).
Since that is a gradual process the specific limits for your application may depend on your latency requirements for the queries in your application. I would recommend testing your application by creating all the partitions you expect even if they are empty, and seeing how long EXPLAIN takes to run. If that's good enough, then go for it. If it is too long then you could pre-calculate what partition to hit or you could use fewer partitions.
Since that is a gradual process the specific limits for your application may depend on your latency requirements for the queries in your application. I would recommend testing your application by creating all the partitions you expect even if they are empty, and seeing how long EXPLAIN takes to run. If that's good enough, then go for it. If it is too long then you could pre-calculate what partition to hit or you could use fewer partitions.
Thanks.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
