Summary of plans to avoid the annoyance of Freezing - Mailing list pgsql-hackers

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)

* 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

* 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.