Mladen Gogala <gogala.mladen@gmail.com> writes:
> On 1/13/22 18:35, Tom Lane wrote:
>> .. or else reduce the number of partitions you're using. (People
>> frequently seem to think that more partitions are free. That is
>> extremely not true. I generally think that if you're using more
>> than a few dozen partitions per table, you are making a mistake.)
> Interesting. What resources do partitions consume?
Locks, planning time, execution time, cache-management overhead.
To the extent that all of your queries against a table can be
"pruned" to only touch a few partitions, you might come out ahead on
execution time; but I think few workloads are completely stylized
like that.
The poster-child use case for partitions is where you can make
them fit a recurring bulk-deletion scenario; for instance, once
a month you want to drop the oldest month's worth of data.
If you've got terabytes of data then there's also something to
be said for being able to split up vacuuming and other background
overhead. But I think a lot of people are way too eager to
apply partitioning where it doesn't fit, or to overuse it even
where it does fit.
regards, tom lane