5.10. Required Maintenance Processes

PostgreSQL’s concurrency control mechanism requires the following maintenance processes:

  1. Remove dead tuples and index tuples that point to corresponding dead tuples.
  2. Remove unnecessary parts of the clog.
  3. Freeze old txids.
  4. Update FSM, VM, and statistics.

Section 5.3.2 and Section 5.4.3 explained the need for the first and second processes. The third process addresses the transaction ID wraparound problem, which the following subsection describes.

In PostgreSQL, the VACUUM process handles these tasks. Chapter 6 describes VACUUM in detail.

Section Contents

5.10.1. Transaction Wraparound Problem

Assume a transaction with txid 100 inserts Tuple_1; therefore, the t_xmin of Tuple_1 is 100.

The server runs for a very long period without any modifications to Tuple_1. When the current txid reaches 2.1 billion + 100, a SELECT command is executed. At this time, Tuple_1 is visible because txid 100 is considered to be in the past.

If the same SELECT command is executed when the current txid reaches 2.1 billion + 101, Tuple_1 becomes invisible. This happens because txid 100 is now considered to be in the future relative to the current txid (Figure 5.21).

This is the transaction wraparound problem in PostgreSQL.

Figure 5.21. Wraparound problem.

5.10.2. Freeze Processing

To solve this problem, PostgreSQL uses a concept called the frozen txid and implements a process called FREEZE.

PostgreSQL defines the frozen txid as a special reserved txid (value 2). This ID is always older than all other txids; therefore, the frozen txid is always inactive and visible to all transactions.

The vacuum process invokes the freeze process. The freeze process scans table files and rewrites the t_xmin of tuples to the frozen txid (2) if the t_xmin value is older than the current txid minus vacuum_freeze_min_age (default is 50 million). Chapter 6 provides more details.

For example, in Figure 5.22 a), the current txid is 50,002,500 when the VACUUM command invokes the freeze process. In this case, the process rewrites the t_xmin of both Tuple_1 and Tuple_2 to 2.

In versions 9.4 (2014) or later, PostgreSQL sets the XMIN_FROZEN bit in the t_infomask field of the tuple instead of rewriting the t_xmin value (Figure 5.22 b).

Figure 5.22. Freeze process.