Re: Freeze avoidance of very large table.

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> It's possible that we could use this infrastructure to freeze
> more aggressively in other circumstances. For example, perhaps
> VACUUM should freeze any page it intends to mark all-visible.
> That's not a guaranteed win, because it might increase WAL
> volume: setting a page all-visible does not emit an FPI for that
> page, but freezing any tuple on it would, if the page hasn't
> otherwise been modified since the last checkpoint. Even if that
> were no issue, the freezing itself must be WAL-logged. But if we
> could somehow get to a place where all-visible => frozen, then
> autovacuum would never need to visit all-visible pages, a huge
> win.

That would eliminate full-table scan vacuums, right? It would do
that by adding incremental effort and WAL to the "normal"
autovacuum run to eliminate the full table scan and the associated
mass freeze WAL-logging? It's hard to see how that would not be an
overall win.

> We could also attack the problem from the other end. Instead of
> trying to set the bits on the individual tuples, we could decide
> that whenever a page is marked all-visible, we regard it as
> frozen regardless of the bits set or not set on the individual
> tuples. Anybody who wants to modify the page must freeze any
> unfrozen tuples "for real" before clearing the visibility map
> bit. This would have the same end result as the previous idea:
> all-visible would essentially imply frozen, and autovacuum could
> ignore those pages categorically.

Besides putting work into the foreground that could be done in the
background, that sounds more complicated. Also, there is no
ability to "pace" the freeze load or use scheduled jobs to shift
the work to off-peak hours.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company