Re: Idea for getting rid of VACUUM FREEZE on cold pages

Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010:

> Problem: currently, if your database has a large amount of "cold" data,
> such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer
> needs to touch it thanks to the visibility map. However, every
> freeze_age transactions, very old pages need to be sucked into memory
> and rewritten just in order to freeze those pages. This can have a huge
> impact on system performance, and seems unjustified because the pages
> are not actually being used.

I think this is nonsense. If you have 3-years-old sales transactions,
and your database has any interesting churn, tuples those pages have
been frozen for a very long time *already*. The problem is vacuum
reading them in so that it can verify there's nothing to do. If we want
to avoid *reading* those pages, this solution is useless:

> Suggested resolution: we would add a 4-byte field to the *page* header
> which would track the XID wraparound count.

because you still have to read the page.

I think what you're looking for is for this Xid wraparound count to be
stored elsewhere, not inside the page. That way vacuum can read it and
skip the page without reading it altogether. I think a "freeze map" has
been mentioned downthread.

I remember mentioning some time ago that we could declare some tables as
frozen, i.e. "not needing vacuum". This strikes me as similar, except
at the page level rather than table level.

--
Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>