The VM was enhanced in version 9.6 to improve the efficiency of freeze processing. The new VM shows page visibility and information about whether tuples are frozen or not in each page (Section 6.3.3).

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.

When starting freeze processing, PostgreSQL calculates the freezeLimit txid and freezes tuples whose t_xmin is less than the freezeLimit txid.

The freezeLimit txid is defined as follows:

where \(\verb|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, \(\verb|OldestXmin|\) is 100. If no other transactions exist, \(\verb|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, \(\verb|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.

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, 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 < (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)

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.

The clog, described in Section 5.4, stores transaction states. When pg_database.datfrozenxid is updated, PostgreSQL attempts to remove unnecessary clog files. Note that corresponding clog pages are also removed.

Figure 6.7 shows an example. If the minimum pg_database.datfrozenxid is contained in the clog file '0002', the older files ('0000' and '0001') can be removed because all transactions stored in those files can be treated as frozen txids in the whole database cluster.

$ psql testdb -c "SELECT datname, datfrozenxid FROM pg_database"
  datname  | datfrozenxid 
-----------+--------------
 template1 |      7308883
 template0 |      7556347
 postgres  |      7339732
 testdb    |      7506298
(4 rows)

$ ls -la -h data/pg_xact/	# In versions 9.6 or earlier, "ls -la -h data/pg_clog/"
total 316K
drwx------  2 postgres postgres   28 Dec 29 17:15 .
drwx------ 20 postgres postgres 4.0K Dec 29 17:13 ..
-rw-------  1 postgres postgres 256K Dec 29 17:15 0006
-rw-------  1 postgres postgres  56K Dec 29 17:15 0007

Vacuum processing has been automated with the autovacuum daemon, making the operation of PostgreSQL extremely easy.

The autovacuum daemon periodically invokes several autovacuum_worker processes. By default, it wakes every 1 minute (defined by autovacuum_naptime) and invokes three workers (defined by autovacuum_max_works).

The autovacuum workers invoked by the autovacuum deamon perform vacuum processing concurrently for respective tables, gradually and with minimal impact on database activity.

The autovacuum process runs for a target table if any of the following conditions are satisfied:

1. The current txid precedes the following expression: \begin{align} \verb|relfrozenxid| + \verb|autovacuum_freeze_max_age|, \end{align} where $\verb|relfrozenxid|$ is the relfrozenxid value of the target table that is defind in the pg_class, and autovacuum_freeze_max_age (the default is 200,000,000) is a configuration parameter.
   If this condition is satisfied, the autovacuum process runs for the target table to perform freeze processing.


2. The number of dead tuples is greater than the following expression: \begin{align} \verb|autovacuum_vacuum_threshold| + \verb|autovacuum_vacuum_scale_factor| \times \verb|reltuples|, \end{align} where autovacuum_vacuum_threshold (the default is 50) and autovacuum_vacuum_scale_factor (the default is 0.2) are configuration parameters, $\verb|reltuples|$ is the number of tuples in the target table.
   For example, if the target table has 10,000 tuples and 2,100 dead tuples, the autovacuum process runs for the target table since $ 2100 \gt 50 + 0.2 \times 10000 $.


3. The number of inserted tuples in the target table is greater than the following expression: \begin{align} \verb|autovacuum_vacuum_insert_threshold| + \verb|autovacuum_vacuum_insert_scale_factor| \times \verb|reltuples|, \end{align} where autovacuum_vacuum_insert_threshold (the default is 1000) and autovacuum_vacuum_insert_scale_factor (the default is 0.2) are configuration parameters, $\verb|reltuples|$ is the number of tuples in the target table.
   For example, if the target table has 10,000 tuples and 3,010 inserted tuples, the autovacuum process runs for the target table since $ 3010 \gt 1000 + 0.2 \times 10000 $.
   This condition has been added since version 13.

In addition, if the following condition is satisfied for the target table, the autovacuum process will also perform analyze processing.

where $\verb|mod_since_analyze|$ is the number of modified tuples (by INSERT, DELETE or UPDATE) since the previous analyze processing, autovacuum_analyze_threshold (the default is 50) and autovacuum_analyze_scale_factor (the default is 0.1) are configuration parameters, $\verb|reltuples|$ is the number of tuples in the target table.

For example, if the target table has 10,000 tuples and 1,100 modified tuples since the previous analyze processing, the autovacuum process will run since \(1100 \gt 50 + 0.1 \times 10000\).

As frequently mentioned, table bloat is one of the most annoying things in managing PostgreSQL. Several things can cause that problem, and Autovacuum is one of them.

The autovacuum runs when the number of dead tuples is greater than: 250 for 1,000 relations, 20,050 for 100,000 relations, and 20,000,050 for 100,000,000 relations. It is clear from these examples that the more tuples a table has, the less often autovacuum runs.

A good known tip is to reduce the autovacuum_vacuum_scale_factor value. In fact, the default of autovacuum_vacuum_scale_factor (0.2) is too learge for big tables.

PostgreSQL can set an appropriate autovacuum_vacuum_scale_factor in each table using ALTER TABLE command. For example, I show how to set the new value of autovacuum_vacuum_scale_factor for the table pgbench_accounts.

postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE

If you need that Autovacuum runs for the target tables without depending on the number of their tuples, you can also do it.

For example, assume that you need that Autovacuum processing whenever the number of dead tuples reaches 10,000. In this case, by setting the following storage parameters for the table, the Autovacuum process will perform the vacuum processing each time it reaches 10,000:

postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE
postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE

Although Concurrent VACUUM is essential for operation, it is not sufficient. For example, it cannot reduce the size of a table even if many dead tuples are removed.

Figure 6.8 shows an extreme example. Suppose that a table consists of three pages, and each page contains six tuples. The following DELETE command is executed to remove tuples, and the VACUUM command is executed to remove dead tuples:

Fig. 6.8. An example showing the disadvantages of (concurrent) VACUUM.