From PostgreSQL wiki
Jump to: navigation, search

Some history: PostgreSQL has used 32 bit transaction IDs forever. Before PostgreSQL 7.2, when you ran out of 32 bit transaction IDs you had to dump and restore the database cluster. In 7.2 (2005) we got "safe transaction wraparound", but it means that we need to 'freeze' tuples (ie visit them again and rewrite them) some time before the xid counter wraps around. In some deployments that can be a very IO intensive and has been known to lead to outages (avoidable by better planning, but not helped by the inadequate algorithm that autovacuum uses to trigger the work that has no defences against deciding that the whole database needs freeze vacuuming due to wraparound when there isn't enough time left). In theory, by making judicious use of 64 bit xids, we might be able to avoid having to do that completely, so that we only ever write tuples once (with a 64 bit xid you could do 50,000 TPS for over 11 million years; admittedly you'd have to dump-and-restore after that).

In PostgreSQL 12 we introduced a 64 bit transaction ID type called FullTransactionId and started using it as the main generator of transaction IDs, and we started using that in the transaction stack, meaning that any AM code can now use GetTopFullTransactionId() or GetCurrentFullTransactionId().

A couple of small things now take advantage of this:

  • As described in the original thread that lead to the creation of FullTransactionId, txid_current() is now reliable (previously it could, in theory at least on a very busy system, report the wrong epoch depending on the phasing of checkpoints and wraparound)

Some "small projects" about expanding the use of FullTransactionId:

  • It might be nice to switch the xid-based SLRUs (CLOG etc) over to using filenames that are derived from the fxid, so we can get rid of their wraparound support logic; why not just have filenames that go up forever?
  • ... more things here ...

Some future "big projects" to use FullTransactionId as the end goal of this work:

  • the traditional heap format could be changed to use 64 bit xids without making the tuple header larger, by putting a reference fxid on the page header; then you only need to freeze tuples if the reference fxid gets too old, and that can only happen while you're already writing to the page, so you win
  • new table AMs such as zheap, zedstore have no freezing as a basic design goal and will use fxid rather than xid (along with other systems that make xids less important and get rid of other other reasons to need "vacuum")

TODO: Help find links to previous discussions of 64 bit xid ideas and collate them all here!

Some other relevant blogs and material: