Re: pg_multixact not getting truncated

On 11/10/14, 12:16 AM, Josh Berkus wrote:
> On 11/09/2014 08:00 PM, Josh Berkus wrote:
> On 11/08/2014 01:46 PM, Andres Freund wrote:
>>> 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
>
> It turns out that not even a program of preventative scheduled vacuuming
> helps. This is because the template0 database anchors the minmxid and
> prevents it from being advanced until autovacuum gets around to that
> database, at whatever the minmxid threshold is.

How did template0 even get a MultiXact? That sounds like they're really abusing the template databases. :( (Do keep in mind that MXID 1 is a special value.)

Regarding linking the two settings, I agree with others that XIDs and MXIDs are basically completely independent (as your customer apparently has discovered). If you set both of the min_age parameters fairly small then it doesn't matter which max limit (the table_age parameters) you hit; you'll get a full scan and the low min_age limits will mean you'll get good freezing of both.

The only other thing I can think of would be having yet another set of minimum age limits that come into play when you're doing a full scan as opposed to a partial one, but that seems like overkill to me. I guess another option would be to get more aggressive depending on the size of pg_multixact/...

BTW, the only reason I know of not to set both min_age parameters to zero is to prevent loss of forensic information. If that's not a concern you can always just set them to zero. Even if it is a concern, I suspect that the forensic info you could gather from a MultiXact is a lot more limited than for an XID, so it's probably pretty safe setting that to zero.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com