Re: Summary of plans to avoid the annoyance of Freezing

On 10 August 2015 at 18:02, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> 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.
>

Good idea.

> > 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
>

Freezing is not a necessary pre-condition for either of those things, I am
happy to say. There is confusion here because for ( 1 ) the shrink was
performed after freezing, but when you have access to the epoch there is no
need for exhaustive freezing - only in special cases, as noted. If we are
lucky those special cases will mean a massive reduction in I/O. For ( 2 ) a
normal VACUUM is sufficient and as Robert observes, maybe just HOT is
enough.

In the new world, the clog can be shrunk when everything has been hinted.
Given that is possible with just a normal VACUUM, I think the new
anti-freeze design (hey, we need a name, right?) will mean the clog
actually stays smaller in most cases than it does now.

> 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.
>

There would be no further need for the VACUUM FREEZE command. It would do
nothing desirable.

> 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.
>

I myself was in favour of the freeze map solution for some time, but I'm
not anymore. Thank discussions at Pgcon slowly working their way into my
brain.

> 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.
>

I doubt we have seen the last of that pain, but its not my fingers on the
chopping board, so squeeze all you want. ;-)

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services