Ron <ronljohnsonjr@xxxxxxxxx> writes: > On 10/31/22 17:26, Ryan Ruenroeng wrote: >> I have a table with 50+ million rows that gets data added to/wiped from it >> every 90 days. We are planning to break this table into a few thousand >> partitions. > 1. That's a lot of partitions. Older (like v12) query optimizers don't do a > great job handle that many partitions. Newer ones don't either, if you are incautious enough to issue a query that touches all/most of the partitions --- or even just that the planner cannot prove doesn't touch all/most of the partitions. So that sort of setup hinges critically on having very stylized queries that you've vetted in advance. We'll probably continue to make incremental improvements in how well things work with lots of partitions, but I don't foresee it ever becoming a cost-free thing. TBH, if you've got 50m rows, I'm not sure you need partitions at all. You sure as heck do not need "a few thousand" of them. You should be basing your partitioning design on predictable maintenance operations that will match your partition boundaries. For example, if your business requirement is to keep five years' worth of records and you want to drop the oldest month's records in bulk once a month, then partitioning by month would be pretty helpful to make those drops cheap. That would lead to 60 active partitions which is entirely reasonable. regards, tom lane
