6.3. Freeze Processing

Freeze processing has two modes. For convenience, these modes are referred to as lazy mode and eager mode. It is performed in either mode depending on certain conditions.

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

In lazy mode, freeze processing scans only pages that contain dead tuples using the respective VM of the target tables.

In contrast, eager mode scans all pages regardless of whether each page contains dead tuples or not. It 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 how to improve the freeze process in eager mode.

6.3.1. Lazy Mode

When starting freeze processing, PostgreSQL calculates the $\text{freezeLimit_txid}$ and freezes tuples whose t_xmin is less than the freezeLimit_txid.

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 that executes this VACUUM command. Here, vacuum_freeze_min_age is a configuration parameter (the default is 50,000,000).

Figure 6.3 shows a specific example. Here, Table_1 consists of three pages, and each page has three tuples. When the VACUUM command is executed, the current txid is 50,002,500 and there are no other transactions. In this case, OldestXmin is 50,002,500; thus, the freezeLimit_txid is 2500. Freeze processing is executed as follows.

• 0^th page:
  Three tuples are frozen because all t_xmin values are less than the freezeLimit_txid. In addition, Tuple_1 is removed in this vacuum process due to a dead tuple.

• 1^st page:
  This page is skipped by referring to the VM.

• 2^nd page:
  Tuple_7 and Tuple_8 are frozen; Tuple_7 is removed.

Before completing the vacuum process, the statistics related to vacuuming are updated, e.g. pg_stat_all_tables’ n_live_tup, n_dead_tup, last_vacuum, vacuum_count, etc.

As shown in the above example, the lazy mode might not be able to freeze tuples completely because it can skip pages.

6.3.2. Eager Mode

The eager mode compensates for the defect of the lazy mode. It scans all pages to inspect all tuples in tables, updates relevant system catalogs, and removes unnecessary files and pages of the clog if possible.

The eager mode is performed when the following condition is satisfied:

In the condition above, $\text{pg_database.datfrozenxid}$ represents the columns of the pg_database system catalog and holds the oldest frozen txid for each database. Details are described later; therefore, we assume that the value of all pg_database.datfrozenxid are 1821 (which is the initial value just after installation of a new database cluster in version 9.5). Vacuum_freeze_table_age is a configuration parameter (the default is 150,000,000).

Figure 6.4 shows a specific example. In Table_1, both Tuple_1 and Tuple_7 have been removed. Tuple_10 and Tuple_11 have been inserted into the 2nd page. When the VACUUM command is executed, the current txid is 150,002,000, and there are no other transactions. Thus, OldestXmin is 150,002,000 and the freezeLimit_txid is 100,002,000. In this case, the above condition is satisfied because

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

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

(Note that this is the behavior of versions 9.5 or earlier; the latest behavior is described in Section 6.3.3.)

• 0^th page:
  Tuple_2 and Tuple_3 have been checked even though all tuples have been frozen.

• 1^st page:
  Three tuples in this page have been frozen because all t_xmin values are less than the freezeLimit_txid. Note that this page is skipped in lazy mode.

• 2^nd page:
  Tuple_10 has been frozen. Tuple_11 has not.

After freezing each table, the pg_class.relfrozenxid of the target table is updated. The pg_class is a system catalog, and each pg_class.relfrozenxid column holds the latest frozen xid of the corresponding table. In this example, Table_1’s pg_class.relfrozenxid is updated to the current freezeLimit_txid (i.e. 100,002,000), which means that all tuples whose t_xmin is less than 100,002,000 in Table_1 are frozen.

Before completing the vacuum process, pg_database.datfrozenxid is updated if necessary. Each pg_database.datfrozenxid column 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 this database is not updated because the pg_class.relfrozenxid of other relations (both other tables and system catalogs that can be seen from the current database) have not been changed (Fig. 6.5(1)). If all relations in the current database are frozen in eager mode, the pg_database.datfrozenxid of the database is updated because all relations’ pg_class.relfrozenxid for this database are updated to the current freezeLimit_txid (Fig. 6.5(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

The eager mode in version 9.5 or earlier versions is not efficient because always scans all pages. For instance, in the example of Section 6.3.2, the 0th page is scanned even though all tuples in its page are frozen.

To deal with this issue, the VM and freeze process have been improved in version 9.6. As mentioned in Section 6.2.1, the new VM has information about whether all tuples are frozen in each page. When freeze processing is executed in eager mode, pages that contain only frozen tuples can be skipped.

Figure 6.6 shows an example. When freezing this table, the 0th page is skipped by referring to the VM’s information. After freezing the 1st page, the associated VM information is updated because all tuples of this page have been frozen.