Avoiding auto-freezing and page corruptions

There are some aspects of VACUUM whose reason to exist is complex to explain, and occasionally they have negative behavior. Let's look more deeply at those and find some solutions.

How to do it...

PostgreSQL uses internal transaction identifiers that are 4 bytes long, so we only have 2^32 transaction IDs (about 4 billion). PostgreSQL starts again from the beginning when that wraps around, allocating new identifiers in a circular manner. The reason we do this is that moving to an 8-byte identifier has various other negative effects and costs that we would rather not pay, so we keep the 4-byte transaction identifier, which means we need to do regular sweeps to replace old transaction identifiers with a special value that is not interpreted in a circular way, and is called Frozen Transaction ID; that's why this procedure is known as freezing.

There are two routes that a row can take in PostgreSQL: a row version dies and needs to be removed by VACUUM...