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

On May 21, 2010, at 23:57 , Josh Berkus wrote:
> From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it:
>
> 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
havea huge impact on system performance, and seems unjustified because the pages are not actually being used. 
>
> Suggested resolution: we would add a 4-byte field to the *page* header which would track the XID wraparound count.
Anypage whose wraparound count was not equal to the current one would be considered to have all frozen tuples.  This
wouldremove the necessity to read and write old pages just to freeze them, a humongous gain for databases with long
dataretention horizons, let alone data warehouses. 

If I understand this correctly, VACUUM usually only frees old tuples, but never increases the oldest xid in the
pg_classrecord. Once that value becomes older than freeze_age, VACUUM needs to scan the whole relation to freeze old
tuples.That results in most of the pages being marked dirty and subsequently being written out, causing an IO storm.
If,OTOH, the wraparound count was stored in the page header, VACUUM would still need to read those pages, but wouldn't
needto write them out. 

Alternatively, VACUUM could freeze a few pages on each run, even if the xids are below freeze_age. It could pick those
pagesrandomly, or maybe even prefer pages whose tuples have older xmin/xmas values. That would spread the load out more
evenly,much like we try to spread checkpoints out over the whole checkpoint interval. 

best regards,
Florian Pflugi