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