Summary of plans to avoid the annoyance of Freezing

Freezing is painful for VLDBs and high transaction rate systems. We have a
number of proposals to improve things...

1. Allow parallel cores to be used with vacuumdb (Dilip)
Idea is if we have to get the job done, lets do it as fast as we can using
brute force. Reasonable thinking, but there are more efficient approaches.

2. Freeze Map (Sawada)
This works and we have a mostly-ready patch. I'm down to do final review on
this, which is why I'm producing this summary and working out what's the
best next action for Postgres.

3. Speed up autovacuums when they are triggered to avoid wraparounds (Simon)
Idea is to do a VACUUM scan which only freezes tuples. If we dirty a page
from freezing we then also prune it, but don't attempt to scan indexes to
remove the now-truncated dead tuples.
This looks very straightforward, no technical issues. Might even be able to
backpatch it.
[patch investigated but not finished yet]

4. 64-bit Xids (Alexander)
Proposal rejected

5. 64-bit Xid Visibility, with Xid Epoch in page header (Heikki)
* Epoch is stored on new pages, using a new page format. Epoch of existing
pages is stored in control file - code changes to bufpage.c look isolated
* All transaction age tests are made using both xid and epoch - the code
changes to tqual.c look isolated
* We won't need to issue anti-wraparound vacuums again, ever
* We will still need to issue clog-trimming vacuums, so we'll need a new
parameter to control when to start scanning to avoid clog growing too large
* relfrozenxid will be set to InvalidTransactionId once all existing pages
have been made visible
* relhintxid and relhintepoch will be required so we have a backstop to
indicate where to truncate clog (and same for multixacts)
* There is no need to FREEZE except when UPDATEing/DELETEing pages from
previous epochs
* VACUUM FREEZE will only freeze old pages; on a new cluster it will work
same as VACUUM
* VACUUMs that touch every non-all-visible page will be able to set
relhintxid to keep clog trimmed, so never need to scan all blocks in table
* Code changes seem fairly isolated
* No action required at pg_upgrade
* Additional benefit: we can move to 32-bit CRC checksums on data pages at
same time as doing this (seamlessly).
* 8 bytes additional space required per page (~0.1% growth in database size)
* (Any other changes to page headers can be made or reserved at this time)

To me 3) would have been useful if we'd done it earlier. Now that we have
2) and 5), I don't see much point pursuing 3).

The main options are 2) or 5)

Freeze Map (2) makes Freezing more efficient for larger tables, but doesn't
avoid the need altogether. 5) is a deeper treatment of the underlying
problem and is a better architecture for the future of Postgres, IMHO.

I was previously a proponent of (2) as a practical way forwards, but my
proposal here today is that we don't do anything further on 2) yet, and
seek to make progress on 5) instead.

If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.

If Heikki wishes to work on (5), that's good. Otherwise, I think its
something I can understand and deliver by 1 Jan, though likely for 1 Nov CF.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services