6.3. Freeze Processing

Freeze processing, described in Section 5.10.2, has two modes. For convenience, these modes are referred to as lazy mode and eager mode. PostgreSQL performs the process in either mode depending on certain conditions.

Freeze processing typically runs in lazy mode, but eager mode runs when specific conditions are satisfied.

In lazy mode, freeze processing scans only pages that contain dead tuples by using the Visibility Map (VM) of the target table.

In contrast, eager mode scans all pages regardless of whether they contain dead tuples. This mode also updates system catalogs related to freeze processing and removes unnecessary parts of the clog if possible.

Sections 6.3.1 and 6.3.2 describe these modes, respectively. Section 6.3.3 describes improvements to the freeze process in eager mode.

6.3.1. Lazy Mode

PostgreSQL calculates the freezeLimit_txid at the start of freeze processing and freezes tuples whose t_xmin is less than this value.

The freezeLimit_txid is defined as follows:

Where: OldestXmin is the oldest txid among currently running transactions.

For example, if three transactions (txids 100, 101, and 102) are running when the VACUUM command is executed, OldestXmin is 100. If no other transactions exist, OldestXmin is the txid of the transaction executing the VACUUM command. The vacuum_freeze_min_age is a configuration parameter (the default is 50,000,000).

Figure 6.5 shows a specific example. Table_1 consists of three pages, each containing three tuples. When the VACUUM command is executed, the current txid is 50,002,500 and no other transactions exist. In this case, OldestXmin is 50,002,500; thus, the freezeLimit_txid is 2,500. Freeze processing proceeds as follows:

• 0th page:
  PostgreSQL freezes all three tuples because their t_xmin values are less than the freezeLimit_txid. In addition, this vacuum process removes Tuple_1 because it is a dead tuple.

• 1st page:
  The vacuum process skips this page by referring to the Visibility Map (VM).

• 2nd page:
  PostgreSQL freezes Tuple_7 and Tuple_8, and removes Tuple_7.

Before the vacuum process completes, PostgreSQL updates the statistics related to vacuuming, such as n_live_tup, n_dead_tup, last_vacuum, and vacuum_count in pg_stat_all_tables.

As shown in the above example, lazy mode might not freeze all eligible tuples because it can skip pages.

6.3.2. Eager Mode

Eager mode compensates for the limitations of lazy mode. It scans all pages to inspect every tuple in a table, updates the relevant system catalogs, and removes unnecessary files and pages from the clog where possible.

Eager mode is performed when the following condition is satisfied:

Where:

• pg_database.datfrozenxid represents a column in the pg_database system catalog and holds the oldest frozen txid for each database.
• vacuum_freeze_table_age is a configuration parameter with a default of 150,000,000.

Figure 6.6 shows a specific example:

Assume that the value of pg_database.datfrozenxid is 1821.

In Table_1, both Tuple_1 and Tuple_7 have been removed, while Tuple_10 and Tuple_11 have been inserted into the second page. When the VACUUM command is executed, the current txid is 150,002,000, and no other concurrent transactions exist. Consequently, OldestXmin is set to 150,002,000, and the freezeLimit_txid becomes 100,002,000.

In this case, the condition is satisfied because:

$$ 1821 \lt (150002000 - 150000000) $$

Therefore, the freeze processing performs in eager mode as follows.

• 0th page:
  PostgreSQL checks Tuple_2 and Tuple_3 even though all tuples are already frozen.
• 1st page:
  PostgreSQL freezes the three tuples in this page because all t_xmin values are less than the freezeLimit_txid. Lazy mode would skips this page.
• 2nd page:
  PostgreSQL freezes Tuple_10, but not Tuple_11.

After freezing each table, PostgreSQL updates the pg_class.relfrozenxid of the target table. pg_class is a system catalog, and each pg_class.relfrozenxid column holds the latest frozen txid of the corresponding table.

In this example, Table_1’s pg_class.relfrozenxid is updated to the current freezeLimit_txid (100,002,000). This indicates that all tuples with a t_xmin less than 100,002,000 in Table_1 are frozen.

6.3.2.1. Updating pg_database.datfrozenxid

Before the vacuum process completes, PostgreSQL updates pg_database.datfrozenxid if necessary, which holds the minimum pg_class.relfrozenxid in the corresponding database.

For example, if only Table_1 is frozen in eager mode, the pg_database.datfrozenxid of the database remains unchanged. This is because the pg_class.relfrozenxid of other relations (other tables and system catalogs visible from the current database) have not changed (Figure 6.7(1)).

If all relations in the current database are frozen in eager mode, pg_database.datfrozenxid is updated because the pg_class.relfrozenxid of all relations in the database are updated to the current freezeLimit_txid (Figure 6.7(2)).

testdb=# VACUUM table_1;
VACUUM
testdb=# SELECT n.nspname as "Schema", c.relname as "Name", c.relfrozenxid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE c.relkind IN ('r','')
                   AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'
                   AND pg_catalog.pg_table_is_visible(c.oid)
                   ORDER BY c.relfrozenxid::text::bigint DESC;
   Schema   |            Name         | relfrozenxid
------------+-------------------------+--------------
 public     | table_1                 |    100002000
 public     | table_2                 |         1846
 pg_catalog | pg_database             |         1827
 pg_catalog | pg_user_mapping         |         1821
 pg_catalog | pg_largeobject          |         1821

...

 pg_catalog | pg_transform            |         1821
(57 rows)

testdb=# SELECT datname, datfrozenxid FROM pg_database WHERE datname = 'testdb';
 datname | datfrozenxid
---------+--------------
 testdb  |         1821
(1 row)

6.3.3. Improving Freeze Processing in Eager Mode

Eager mode in versions 9.5 and earlier is inefficient because it always scans all pages. For instance, in the example in Section 6.3.2, the process scans the 0th page even if all tuples in that page are already frozen.

To address this issue, version 9.6 improved both the VM and the freeze process. As mentioned in Section 6.2.1, the new VM records whether all tuples in each page are frozen. When freeze processing runs in eager mode, the process skips pages that contain only frozen tuples.

Figure 6.8 shows an example. During the freezing of this table, the process skips the 0th page by referring to the VM. After the 1st page is frozen, the associated VM information is updated because all tuples on that page are now frozen.