Re: pg_multixact not getting truncated

On 11/08/2014 01:46 PM, Andres Freund wrote:

> I think it'd be a good idea to tune them more automatedly in the
> future. But I think the current situation where you can vastly increase
> multivacuum_freeze_max_age while having
> multivacuum_multixact_freeze_max_age is *much* more useful in practice
> than when they always were the same.

Can you explain that? Because I'm not picturing the situation where
that would make sense.

>> I'm still not convinced that it makes sense to have a separate multixact
>> threshold at all **since the same amount of vacuuming needs to be done
>> regardless of whether we're truncating xids or mxids**.
>
> That's just plain wrong. The growth rate of one can be nearly
> independent of the other. It can e.g. be very sensible to have a huge
> xid freeze limit, but a much smaller multixact limit.

Yah, so who cares? Either way you're in for a full table scan, and if
you're doing the full table scan anyway, you might as well freeze the xids.

>> Certainly when I play with tuning this for customers, I'm going to lower
>> vacuum_freeze_table_age as well.
>
> I'm these days suggesting that people should add manual vacuuming for
> "older" relations during off peak hours on busy databases. There's too
> many sites which service degrades noticeably during a full table vacuum.

Me too: https://github.com/pgexperts/flexible-freeze

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com