Re: Setting vacuum_freeze_min_age really low

On Mon, Mar 25, 2013 at 4:31 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> In the past, setting vacuum_freeze_min_age (vfma) really low (say to
> 10000 or 50000) would have caused lots of extra writing work due to
> dirtying extra pages for freezing. This has been our stated reason to
> keep vfma high, despite the obvious advantage of freezing tuples while
> they're still in the cache.

That, and Tom's concern about forensics, which I understand to be the
larger sticking point.

> With the visibility map, though, vfma should only be dirtying pages
> which vacuum is already visiting because there's dirty tuples on the
> page. That is, pages which vacuum will probably dirty anyway, freezing
> or not. (This is assuming one has applied the 9.2.3 update.)

I think this is probably not accurate, although I'll defer to someone
with more real-world experience. I'd guess that it's uncommon for
actively updated data and very-rarely-updated data to be mixed
together on the same pages with any real regularity. IOW, the dirty
pages probably don't have anything on them that can be frozen anyway.

So, if the table's age is less than vacuum_freeze_table_age, we'll
only scan pages not already marked all-visible. Regardless of vfma,
we probably won't freeze much.

On the other hand, if the table's age is at least
vacuum_freeze_table_age, we'll scan the whole table and freeze a lotta
stuff all at once. Again, whether vfma is high or low won't matter
much: it's definitely less than vacuum_freeze_table_age.

Basically, I would guess that both the costs and the benefits of
changing this are pretty small. It would be nice to hear from someone
who has tried it, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company