Re: Large number of partitions of a table

Victor Sudakov <vas@sibptus.ru> writes:
> Do you have success (or disaster) stories for having a large number of
> partitions in a table (like maybe 10000) in a production database?
> I've found a great article
> https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/
> and basically it says 70000 partitions are OK but would like to hear
> more opinions especially from production experience.

One thing that cannot be said too strongly is that it depends A LOT
on which Postgres major version you are working with.  The earliest
releases that had partitions had a lot of trouble with more than a
few dozen partitions.  It's gotten better with each release since
then, because we've been sanding down various rough edges over
time (and will continue to do so).  But it's still possible to burn
yourself, and always will be, for example if you issue a query that
requires scanning all the partitions not just a few.  Thus, your
results will also vary A LOT depending on your exact workload.

Since you've given no details about either of those points,
it's impossible to give any confident answer about how well
10K partitions would work for you.

The specific point that depesz was responding to in that blog
was the 64K-ish limit on rangetable entries in a query.  That is
a thing, as he could have shown by using queries that weren't
amenable to plan-time pruning.  (It's also an ex-thing, having
been fixed for v15 [1]; but that doesn't help you today.)
Now, if you use no queries that can't be pruned to a few
partitions, then it's academic for you.  But you can pretty
much guarantee that you will run into it some of the time
if you try to do real work with a 70K-partition table.

> If a table itself has e.g. 50 indexes, partitioning it will create 10000
> extra tables and 50*10000=500000 indexes, isn't it a heavy burden on the
> system catalogs (pg_statistic etc). It may slow down ANALYZE or have
> some other adverse effects.

Right.  Although there are not *hard* limits on the number of
database objects involved (mumble 4G OID limit mumble), you're
definitely risking performance issues when you get into thousands
of partitions.  If you need such a setup badly enough to deal
with restrictions on what you can do with the table, then do what
you gotta do ... but I suspect that overall, most people will be
happier with fewer partitions not more.

            regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e3ec3c00d