Re: Postgres partition max limit

2023-09-06 Thread Chris Travers 
On Wed, Sep 6, 2023 at 5:27 PM Daulat  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

Re: Postgres partition max limit

2023-09-06 Thread Laurenz Albe 
On Wed, 2023-09-06 at 13:35 +0530, Daulat wrote:
> Do we have a max limit of partitions for a table in postgres?

I don't think there is a technical limit.

But as soon as you have more than a few thousand partitions, the
experience won't be that great any more.

Yours,
Laurenz Albe

Postgres partition max limit

2023-09-06 Thread Daulat 
Hi Team,

Do we have a max limit of partitions for a table in postgres?

Thanks.