Victor Sudakov <vas@xxxxxxxxxx> 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
