Idea for getting rid of VACUUM FREEZE on cold pages - Mailing list pgsql-hackers

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 have a 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.  Any page whose wraparound 
count was not equal to the current one would be considered to have all 
frozen tuples.  This would remove the necessity to read and write old 
pages just to freeze them, a humongous gain for databases with long data 
retention horizons, let alone data warehouses.

All xids on the page would, necessarily, need to belong to the same 
wraparound; if a page gets updated and its wraparound count (hereafter 
WCID) is lower than current, all tuples on the page would be frozen 
before any data is written to it.  XIDs which were before the max_freeze 
horizon on a page which was being written anyway would be frozen as they 
are now.

Obvious issues:

(1) In a case of rows written close to the wraparound point, this would 
cause a set of tuples to be frozen sooner than they would be in the 
current system.

(2) It's not clear what to do with a page where there are XIDs which are 
just before wraparound (like XID # 2.4b) which are still visible and 
receives a write with a new cycle xid (#1).

(3) This will require changing the page structure, with all that 
entails.  So it should probably be done when we're making another change 
(like adding CRCs).

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


pgsql-hackers by date:

Previous
From: Jeff Davis
Date:
Subject: (9.1) btree_gist support for searching on "not equals"
Next
From: Marko Tiikkaja
Date:
Subject: Re: (9.1) btree_gist support for searching on "not equals"