Ron <ronljohnsonjr@gmail.com> 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
