Re: Postgres partition max limit

On Wed, Sep 6, 2023 at 5:27 PM Daulat <daulat(dot)dba(at)gmail(dot)com> 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.

>
> Thanks.
>
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more