6.6. Reclaiming Bloated Space

The VACUUM command removes dead tuples and prevents transaction ID wraparound. However, it cannot reclaim disk space caused by table bloat.

Table bloat is a condition in which the physical size of a table does not decrease even after dead tuples are removed. Figure 6.10 shows an extreme example.

Suppose a table consists of three pages, and each page contains six tuples. The following commands delete most tuples and then remove the resulting dead tuples:

testdb=# DELETE FROM tbl WHERE id % 6 != 0;
testdb=# VACUUM tbl;

The VACUUM command removes the dead tuples, but the table size remains unchanged. As a result, the relation file still occupies three pages, even though only three live tuples remain.

In general, this issue occurs when a large volume of dead tuples is generated.

Until version 18, the only built-in solution was to rebuild the relation files and indexes using either CLUSTER or VACUUM FULL. Both commands acquire ACCESS EXCLUSIVE locks on the entire table during execution, blocking all operations while rebuilding. This disrupts operations if the reconstruction takes a long time.

PostgreSQL 19 (2026) introduced the REPACK command (not the pg_repack extension). Both VACUUM FULL and CLUSTER now use the same underlying implementation. Furthermore, a CONCURRENTLY mode was added to acquire the ACCESS EXCLUSIVE lock only during file switching, enabling table reconstruction with higher online availability than before.

The following sections describe REPACK (the replacement for VACUUM FULL) and REPACK CONCURRENTLY.

6.6.1. REPACK (VACUUM FULL)

The REPACK (VACUUM FULL) command uses a simple strategy that recreates a new physical table file under an exclusive lock. Figure 6.11 outlines this command.

• (1) Create a new table file:
  When the REPACK command is executed, PostgreSQL creates a new 8 KB table file and acquires AccessExclusiveLock on both the new and old tables. The locks prevent other operations from accessing the tables.

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

• (3) Remove the old file and rebuild associated structures:
  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 REPACK (VACUUM FULL) is shown below:

Pseudocode: REPACK (VACUUM FULL)

(1)  FOR each table
(2)     Acquire AccessExclusiveLock lock for the (old) table
(3)     Create a new table file with AccessExclusiveLock
(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 (after releasing the exclusive lock)
(8)     Rebuild all indexes
(9)     Update FSM and VM
(10)    Update statistics
        Release AccessExclusiveLock lock for the new table
     END FOR
(11) Remove unnecessary clog files and pages if possible

Consider two points when using the REPACK (VACUUM FULL) command:

1. No operations can access (read or write) the table during the 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.

6.6.2. REPACK CONCURRENTLY

The CONCURRENTLY option, introduced in version 19, allows concurrent searches and updates on the target table during space reclamation, except for a brief AccessExclusiveLock during the final phase.

The basic implementation strategy matches the standard REPACK by creating a new table and copying live tuples from the old table. However, the CONCURRENTLY option introduces the following internal processes:

• At the start of the REPACK process, an MVCC snapshot is taken.
• The backend copies only the live tuples visible to this snapshot to the new table.
• Changes that occur during the copying process are subsequently applied to the new table.

To capture changes during copying, the REPACK command utilizes the WAL decoding mechanism used in logical replication. It performs as follows:

• The backend executing REPACK starts a background worker called repack_decoding_worker.

  • The worker decodes WAL records generated by concurrent transactions, extracts the changes made to the target table, and writes them to a temporary BufFile (implemented in buffile.c).

• The REPACK backend reads the change elements from the BufFile and applies them to the new table.

This mechanism works as long as the configuration parameter wal_level is set to “replica” or higher. The target table must have a REPLICA IDENTITY (“DEFAULT” or “USING INDEX”) configured.

Note that the CONCURRENTLY option cannot be used for the following tables:

• Partitioned tables
• UNLOGGED tables
• TOAST tables
• Tables without a REPLICA IDENTITY (“DEFAULT” or “USING INDEX”)
• System catalogs

6.6.2.1. Capturing and Applying Concurrent Changes

The repack_decoding_worker starts every time the REPACK CONCURRENTLY command is executed and terminates once the process finishes.

This worker decodes WAL records starting from the LSN when the snapshot was taken. It extracts only the changes made to the target table and passes them to the REPACK backend via a BufFile.

To simplify the explanation, locking operations are omitted here, focusing instead on the flow of WAL decoding and applying changes.

Assume that the target table tbl is defined as follows:

testdb=# \d tbl
                 Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 data   | text    |           |          | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)

In this example, the REPLICA IDENTITY is the primary key: “tbl_pkey”.

Figure 6.12 illustrates a conceptual two-phase REPACK CONCURRENTLY process (the actual implementation consists of three phases, as explained in the next section).

Phase 1: Copy Tuples

• (1) backend_1 (which executes the REPACK command) creates a repack_decoding_worker process.

• (2) The repack_decoding_worker initiates a transaction and shares its precise snapshot with backend_1 to ensure consistency.

• (3) backend_1 creates a new table file.

• (4) backend_2 inserts Tuple_B into the old table. This operation adds Tuple_B to the old table and generates a corresponding WAL record.

• (5) backend_1 copies live tuples (such as Tuple_A) from the old table to the new table using the acquired snapshot. At this point, Tuple_B is invisible to the snapshot, so it is not copied.

Phase 2: Apply Changes

• (6) After completing the copy, backend_1 builds all associated indexes for the new table. In this example, only Tuple_A is initially reflected in the new index.

• (7) backend_1 executes XLogFlush() to ensure that the WAL generated in Phase 1 is persisted.

• (8) The repack_decoding_worker decodes the WAL records generated during Phase 1 and outputs the changes (in this example, “INSERT Tuple_B”) to the BufFile.

• (9) backend_1 reads the BufFile and applies the changes to the new table. Here, Tuple_B is inserted into the new table using the normal INSERT path. As a result, all indexes on the new table are updated automatically by PostgreSQL’s regular index maintenance mechanism.

By applying changes afterwards, the table can be rebuilt while still allowing concurrent user searches and updates.

6.6.2.2. Three Phases of REPACK CONCURRENTLY

This subsection describes the process according to the actual implementation.

To focus on the types of locks held during each phase, internal backend interactions and the repack_decoding_worker details are omitted here.

As shown in Figure 6.13, the implementation applies changes twice to minimize the operational blockages caused by the AccessExclusiveLock.

This approach rests on the assumption that the volume of concurrent changes generated during Phase 2 will be relatively small. This allows Phase 3 — which applies the remaining changes and performs the final switchover under the strict lock — to complete quickly.

When the REPACK command runs with the CONCURRENTLY option, the repack_decoding_worker starts, and the following processing takes place:

• Phase 1: Copy Tuples
  Live tuples are copied in the same manner as described in the previous section.

  During this phase, the REPACK backend holds a ShareUpdateExclusiveLock on the old table and an AccessExclusiveLock on the new table.

• Phase 2: Apply Changes for Phase 1
  Changes that occurred during Phase 1 are applied to the new table. The locks held are identical to those in Phase 1.

  During this phase, updates and inserts by other backends may still occur. In this example, Tuple_X is updated to Tuple_Z.

• Phase 3: Apply Changes for Phase 2
  An AccessExclusiveLock is acquired on the old table to prevent further updates to it.

  With no additional changes occurring, the changes accumulated during Phase 2 are applied to the new table.

After this, the repack_decoding_worker terminates. Similar to the standard REPACK (VACUUM FULL), the old and new tables/indexes are swapped, the old table/index files are removed, and the FSM, VM, and statistics are updated.

As shown in this example, applying concurrent changes during Phases 2 and 3 may generate new dead tuples in the new table file. Therefore, unlike the non-concurrent implementation, REPACK CONCURRENTLY cannot guarantee a completely dead-tuple-free table.