Avoiding auto-freezing and page corruptions
There are some aspects of VACUUM whose reason for existence is complex to explain, and occasionally they have negative behaviors. Let's look at these in more details and find some solutions to them.
PostgreSQL uses internal transaction identifiers that are 4 bytes long, so we only have 232 transaction IDs (about four 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, which is called frozen transaction ID; that's why this procedure is known as freezing.
How to do it…
There are two routes that a row can take in PostgreSQL—a row version dies and needs to be...