Re: Summary of plans to avoid the annoyance of Freezing

Simon,

Thank you for this summary! I was losing track, myself.

On 08/09/2015 11:03 PM, Simon Riggs wrote:
> Freezing is painful for VLDBs and high transaction rate systems. We have
> a number of proposals to improve things...

> 3. Speed up autovacuums when they are triggered to avoid wraparounds (Simon)
> Idea is to do a VACUUM scan which only freezes tuples. If we dirty a
> page from freezing we then also prune it, but don't attempt to scan
> indexes to remove the now-truncated dead tuples.
> This looks very straightforward, no technical issues. Might even be able
> to backpatch it.
> [patch investigated but not finished yet]

There's a lesser version of this item which remains relevant unless we
implement (5). That is, currently the same autovacuum_vaccuum_delay
(AVVD) applies to regular autovacuums as does to anti-wraparound
autovacuums. If the user has set AV with a high delay, this means that
anti-wraparound AV may never complete. For that reason, we ought to
have a separate parameter for AVVD, which defaults to a lower number
(like 5ms), or even to zero.

Of course, if we implement (5), that's not necessary, since AV will
never trigger an anti-wraparound freeze.

> Having a freeze map would be wholly unnecessary if we don't ever need to
> freeze whole tables again. Freezing would still be needed on individual
> blocks where an old row has been updated or deleted; a freeze map would
> not help there either.
>
> So there is no conflict, but options 2) and 3) are completely redundant
> if we go for 5). After investigation, I now think 5) is achievable in
> 9.6, but if I am wrong for whatever reason, we have 2) as a backstop.

It's not redundant. Users may still want to freeze for two reasons:

1. to shrink the clog and multixact logs

2. to support INDEX-ONLY SCAN

In both of those cases, having a freeze map would speed up the manual
vacuum freeze considerably. Otherwise, we're just punting on the
problem, and making it worse for users who wait too long.

Now, it might still be the case that the *overhead* of a freeze map is a
bad tradeoff if we don't have to worry about forced wraparound. But
that's a different argument.

BTW, has it occured to anyone that implementing XID epoch headers is
going to mean messing with multixact logs again? Just thought I'd open
a papercut and pour some lemon juice on it.

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