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

> Well, maybe I'm confused here, but arranging things so that we NEVER
> have to visit the page after initially writing it seems like it's
> setting the bar almost impossibly high.

That is the use case, though. What I've encountered so far at 3 client
sites is tables which are largely append-only, with a few selects and
very few updates (< 2%) on recent data. In general, once data gets
flushed out of memory, it goes to disk and never gets recalled, and
certainly not written. Thinks are hunky-dory until we reach
max_freeze_age, at which point the server has to chew through hundreds
of gigabytes of old data just to freeze them, sometimes bringing the
application to a halt in the process.

The user's perspective on this is quite reasonable: if I haven't
selected these pages, and I haven't written to them, why does autovacuum
need to visit them and screw up my server performance?

> Consider a table that is
> regularly written but append-only. Every time autovacuum kicks in,
> we'll go and remove any dead tuples and then mark the pages
> PD_ALL_VISIBLE and set the visibility map bits, which will cause
> subsequent vacuums to ignore the all-visible portions of the table...
> until anti-wraparound kicks in, at which point we'll vacuum the entire
> table and freeze everything.
>
> If, however, we decree that you can't write a new tuple into a
> PD_ALL_VISIBLE page without freezing the existing tuples, then you'll
> still have the small, incremental vacuums but those are pretty cheap,

That only works if those pages were going to be autovacuumed anyway. In
the case outlined above (which I've seen at 3 different production sites
this year), they wouldn't be; a table with less than 2% updates and
deletes does not get vacuumed until max_freeze_age for any reason. For
that matter, pages which are getting autovacuumed are not a problem,
period; they're being read and written and freezing them is not an issue.

I'm not seeing a way of fixing this common issue short of overhauling
CLOG, or of creating a freeze_map. Darn.

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