How Does the VACUUM Process Work in PostgreSQL

Efficient database performance is essential for responsive applications and smooth user experiences. Over time, PostgreSQL tables accumulate dead tuples, degrading performance by occupying disk space and slowing queries. The VACUUM process prevents this by cleaning up dead tuples, reclaiming space, and optimizing storage.

The VACUUM process in PostgreSQL is a critical maintenance operation that removes dead tuples from tables and indexes. Dead tuples are rows deleted or updated and no longer visible to active transactions but still occupy disk space. VACUUM reclaims this space for future data, updates PostgreSQL’s visibility map to track live tuples, and enables optimized query execution.

PostgreSQL’s MVCC and Its Relation to VACUUM 

PostgreSQL's MVCC(Multiversion Concurrency Control) mechanism enables concurrent access to the database by creating multiple versions of a row whenever an update or delete operation occurs. Each transaction views a consistent snapshot of the data, independent of other ongoing operations. While this approach minimizes locking and maximizes performance, it also leaves behind outdated row versions, or dead tuples, that are no longer needed. The VACUUM process is crucial for cleaning up these remnants. By removing dead tuples and marking the associated storage space as reusable, VACUUM ensures that PostgreSQL’s MVCC implementation continues to operate efficiently, balancing the need for concurrency with storage optimization.

Different Ways PostgreSQL Performs VACUUM

PostgreSQL uses the VACUUM process to maintain table health and optimize storage by removing dead tuples. There are different approaches to performing VACUUM, each suited for specific scenarios:

1. Standard VACUUM: Removing Dead Tuples

The standard VACUUM process in PostgreSQL is essential for maintaining database performance by cleaning up dead tuples—remnants of rows that were updated or deleted but are no longer visible to active transactions. While these tuples do not impact data integrity, they continue to occupy valuable disk space.

Key steps in the standard VACUUM process include:

 1.Scanning the table to identify dead tuples using the lazy_scan_heap() function.

 2.Removing dead tuples and updating indexes via lazy_vacuum_heap().

 3.Updating the Free Space Map (FSM) to mark reclaimed space as reusable within PostgreSQL.

 4.Updating the visibility map, which tracks pages with only live tuples, enabling the database to skip unnecessary scans during query execution.

A standard VACUUM does not release space back to the operating system but instead makes it available internally for new data. It is a lightweight, non-intrusive operation that allows concurrent reads and writes, making it ideal for routine maintenance to prevent moderate table bloat.

Core Function in PostgreSQL Source Code:

void vacuum_rel(Relation rel, VacuumParams *params, BufferAccessStrategy bstrategy)

Located in: src/backend/commands/vacuum.c

This function calls table_relation_vacuum() which performs a vacuum on the relation where it sets up things and calls lazy_scan_heap() function to identify dead tuples. 

2. VACUUM FULL: Deep Cleaning and Space Reclamation

VACUUM FULL in PostgreSQL goes beyond the standard VACUUM process by physically rewriting the table into a new file. It effectively removes dead tuples, compacts the table, and releases unused space back to the operating system. This process is more resource-intensive than the standard VACUUM, as it requires an ACCESS EXCLUSIVE lock on the table, blocking other operations during execution. VACUUM FULL is typically used in scenarios where there is significant table bloat, and the normal vacuum or auto vacuum process isn't sufficient.

Internally, VACUUM FULL is performed through the cluster_rel() function, which carries out the task of rebuilding the table and optimizing its storage. Here's how the process works:

1. Locking the Table:

The operation begins by acquiring an ACCESS EXCLUSIVE lock on the table, which blocks all other operations. This ensures that no other transactions interfere with the table while the vacuuming process is running.

2. Opening the Relation:

The try_open_relation() function is used to open the relation (table) for vacuuming. If the table is eligible, the function proceeds by calling rebuild_relation(), which handles the rebuilding of the table's data in its index.

3. Rebuilding the Relation:

The core of VACUUM FULL involves rebuilding the table, which is done in the following steps:

* In make_new_heap():

This function creates a new physical file for the table. It sets up necessary storage parameters and initializes the visibility map and Free Space Map (FSM). The visibility map helps PostgreSQL track pages with only live tuples, while the FSM keeps track of available free space within the table, optimizing space reuse.

* In copy_table_data():

This function is responsible for copying data from the original table to the new table. It scans the original table sequentially and copies only the live tuples (those visible to active transactions). Dead tuples are ignored, and this step also defragments the heap by arranging the live tuples without any gaps.

* In finish_heap_swap():

After the data has been copied, finish_heap_swap() replaces the old relation file with the newly created one. It also handles special cases, such as TOAST tables (for large objects like text or bytea), ensuring that these associated tables are correctly updated. Finally, it updates the system catalogs to reflect the changes made during the operation.

When to Use VACUUM FULL

While VACUUM FULL is useful for reclaiming disk space and addressing table bloat, it is resource-intensive and requires an exclusive lock, which can disrupt other database activities. This makes it suitable for maintenance windows when heavy table updates or deletions have caused substantial bloat. It should be used sparingly, as it is not necessary for routine maintenance. For regular table cleaning, the standard VACUUM or auto vacuum should be preferred.

Core Functions and Source Files:

The primary function that handles the full vacuum operation is cluster_rel(), which is responsible for rebuilding the table and cleaning up the storage. Key functions involved include:

* make_new_heap() – Creates a new physical file and sets up storage parameters.

* copy_table_data() – Copies only live tuples and defragments the heap.

* finish_heap_swap() – Swaps the old relation with the new one and updates associated system catalogs.

The function cluster_rel() is a part of the PostgreSQL source code located in the src/backend/commands/cluster.c file. And the function is called from the above mentioned vacuum_rel() function. By understanding the flow and these functions, you can gain insight into how PostgreSQL manages table storage and space reclamation during VACUUM FULL.

3. Autovacuum: Automated Maintenance for VACUUM

PostgreSQL includes an autovacuum process that runs in the background to ensure consistent database performance without requiring manual intervention. This process is managed by the autovacuum launcher and worker processes, which automatically monitor database activity and trigger VACUUM operations when specific thresholds are met.

Key Components of Autovacuum:

1. Launcher Process: The autovacuum launcher process monitors the database for tables that require vacuuming. When a table meets the conditions for vacuuming, the launcher process spawns worker processes to handle the task.

void AutoVacLauncherMain(char *startup_data, size_t startup_data_len)

2. Worker Process: The worker process executes the VACUUM operation on specific tables. It performs the necessary tasks of cleaning up dead tuples and maintaining table storage efficiently.

void AutoVacWorkerMain(char *startup_data, size_t startup_data_len)

Both the functions are located in the src/backend/postmaster/autovacuum.c file.

Autovacuum Triggers:

Autovacuum is triggered based on the number of dead tuples in a table, which is controlled by the following parameters:

* Scale Factor: A proportion of the table size (default is 0.2, meaning 20% of tuples).

* Threshold: A minimum number of dead tuples that must exist before autovacuum is triggered (default is 50).

These parameters are controlled via the following configuration settings (In the postgresql.conf file):

* autovacuum_vacuum_threshold = 50

* autovacuum_vacuum_scale_factor = 0.2

Autovacuum Decision Logic:

The decision of when to trigger the autovacuum operation is based on statistics collected by PostgreSQL. The autovacuum process uses information like the number of dead tuples, table size, and thresholds to determine when it’s time to run a vacuum operation.

 It uses statistics stored in system catalogs to assess when a table requires vacuuming.

Autovacuum Execution:

Once a worker process is triggered, it performs the actual vacuuming through the do_autovacuum() function. This function handles the cleaning of dead tuples, updating statistics, and reclaiming space.

static void do_autovacuum(void)

The function is located in the src/backend/postmaster/autovacuum.c file.

Why Autovacuum is Important

Autovacuum ensures that VACUUM operations are run automatically in the background, preventing tables from accumulating too much bloat and helping maintain database performance. The process is lightweight and does not lock tables, making it ideal for routine maintenance without disrupting other operations.

However, autovacuum does not eliminate the need for manual VACUUM FULL operations. When a table experiences heavy bloat or significant updates and deletions, VACUUM FULL may still be necessary to reclaim space.

Autovacuum parameters can be configured in postgresql.conf or overridden on a per-table basis using ALTER TABLE.

SELECT pid, datname, usename, state, query
FROM pg_stat_activity
WHERE query LIKE 'VACUUM%' AND state = 'active';

SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum, vacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0;