6.6. Full VACUUM

Although Concurrent VACUUM is essential for operation, it is not sufficient. For example, it cannot reduce table size even after removing many dead tuples.

Figure 6.10 shows an extreme example. Suppose a table consists of three pages, and each page contains six tuples. The following commands remove tuples and then clean the dead tuples:

Figure 6.10. An example showing the disadvantages of (concurrent) VACUUM.
testdb=# DELETE FROM tbl WHERE id % 6 != 0;
testdb=# VACUUM tbl;

The VACUUM command removes the dead tuples, but the table size remains unchanged. This waste of disk space negatively impacts database performance. In this example, reading only three tuples requires loading three pages from disk.

To resolve this situation, PostgreSQL provides Full VACUUM mode. Figure 6.11 outlines this mode.

Figure 6.11. Outline of Full VACUUM mode.

  • [1] Create a new table file (Figure 6.11(1)):
    When the VACUUM FULL command is executed, PostgreSQL acquires an AccessExclusiveLock for the table and creates a new 8 KB table file. The AccessExclusiveLock prevents other users from accessing the table.

  • [2] Copy live tuples to the new table (Figure 6.11(2)):
    PostgreSQL copies only live tuples from the old table file to the new table.

  • [3] Remove the old file and rebuild associated structures (Figure 6.11(3)):
    After copying all live tuples, PostgreSQL removes the old file. It then rebuilds all associated indexes and updates the FSM, VM, statistics, and system catalogs.

The pseudocode for Full VACUUM is shown below:

Pseudocode: Full VACUUM
(1)  FOR each table
(2)     Acquire AccessExclusiveLock lock for the table
(3)     Create a new table file
(4)     FOR each live tuple in the old table
(5)        Copy the live tuple to the new table file
(6)        Freeze the tuple IF necessary
        END FOR
(7)     Remove the old table file
(8)     Rebuild all indexes
(9)     Update FSM and VM
(10)    Update statistics
        Release AccessExclusiveLock lock
     END FOR
(11) Remove unnecessary clog files and pages if possible

Consider two points when using the VACUUM FULL command:

  1. No one can access (read or write) the table during the Full VACUUM process.
  2. The process temporarily uses up to twice the disk space of the table. Therefore, check the remaining disk capacity before processing a large table.
When to Use VACUUM FULL.

There is no universal answer for when to execute VACUUM FULL. However, the pg_freespacemap extension provides useful insights.

The following query calculates the average free space ratio for a specific table:

testdb=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION

testdb=# SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('accounts');
 number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
            1640 | 99 bytes           |                1.21
(1 row)

The result might indicate minimal free space (e.g., 1.21%).

If a user deletes most tuples and runs the VACUUM command, many pages may become empty (e.g., 86.97% free space). However, the total number of pages remains unchanged because the table file has not been compacted:

testdb=# DELETE FROM accounts WHERE aid %10 != 0 OR aid < 100;
DELETE 90009

testdb=# VACUUM accounts;
VACUUM

testdb=# SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('accounts');
 number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
            1640 | 7124 bytes         |               86.97
(1 row)

To inspect the free space ratio for each specific page, use this query:

testdb=# SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
                FROM pg_freespace('accounts');
 blkno | avail | freespace ratio
-------+-------+-----------------
     0 |  7904 |           96.00
     1 |  7520 |           91.00
     2 |  7136 |           87.00
     3 |  7136 |           87.00
     4 |  7136 |           87.00
     5 |  7136 |           87.00
....

In this situation, running VACUUM FULL compacts the table file:

testdb=# VACUUM FULL accounts;
VACUUM
testdb=# SELECT count(*) as "number of blocks",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('accounts');
 number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
             164 | 0 bytes            |                0.00
(1 row)

By compacting the table, VACUUM FULL reduces the physical size of the table file and improves efficiency.