Autovacuum Introduction and Tuning

Why VACUUM Is Required

PostgreSQL uses multi-version concurrency control (MVCC) to ensure data consistency and accessibility in a high-concurrency environment. Each transaction runs on its own database snapshot at the time when the transaction starts. This means that outdated data cannot be deleted immediately. When the UPDATE and DELETE operations are performed, PostgreSQL retains old versions of tuples for other ongoing transactions. Each statement that modifies the database generates a transaction ID (XID). In PostgreSQL, an UPDATE operation can be viewed as a combination of an INSERT operation and then a DELETE operation. This can lead to two main issues in the database: XID wraparound and table bloat.

XID Wraparound

PostgreSQL assigns an XID to each transaction. The XID is a 32-bit integer ranging from 1 to 2^31. When the XID reaches 2^31 (2,147,483,648), PostgreSQL starts assigning XIDs from 1 again for new transactions. This process is known as XID wraparound.

A transaction can only see the committed data from transactions that have earlier IDs. For a transaction with ID 100 (the created tuple xmin is 100), when the number of transactions reaches 2^31 + 100, the transaction can see transactions from 101 to 2^31 + 100. When the next transaction 2^31 + 101 is started, this tuple becomes a future transaction and is invisible to the current transaction. If future transactions are invisible, the transaction isolation and consistency will be affected and the database will be unreliable.

VACUUM FREEZE can prevent XID wraparound by marking old transaction IDs as frozen (FrozenTransactionId=2).

VACUUM FREEZE also clears dead tuples.

Table Bloat

According to the MVCC mechanism, a DELETE operation in PostgreSQL does not delete tuples but marks them as deleted. These tuples are called dead tuples. The same applies to UPDATE, which essentially involves a DELETE operation followed by an INSERT operation. Dead tuples still occupy storage space, leading to table bloat even with minimal visible data in the table.

VACUUM helps address table bloat by cleaning up dead tuples. However, it does not reorganize the storage location of live tuples on the table. Instead, it maintains the Free Space Map (FSM) of the table to facilitate future tuple INSERT operations and prevent unnecessary space consumption.

VACUUM FULL is more straightforward as it rebuilds the original table. However, it locks the table during execution. This means you cannot read from or write to the table while VACUUM FULL is in progress. If a table contains a large amount of data, executing VACUUM FULL will take a long time. You are advised to perform the operation during off-peak hours.

Autovacuum

Autovacuum is a daemon that automatically executes the VACUUM and ANALYZE commands. It checks for bloated tables in a database and reclaims space for reuse. The autovacuum daemon consists of two processes: autovacuum launcher and autovacuum worker.

The autovacuum launcher process is started by the postmaster when the autovacuum parameter is set to on. It schedules the autovacuum worker process to perform VACUUM operations on tables. The autovacuum worker process actually executes the VACUUM operation. It connects to a database based on the autovacuum launcher process's schedule and selects a table to vacuum.

Conditions for Triggering Autovacuum

Autovacuum is triggered when the number of dead tuples reaches the threshold. The formula for calculating the threshold is as follows:

Take VACUUM as an example. autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold are two parameters in PostgreSQL.

• number_of_live_tuples is the reltuples field in the pg_class system catalog. You can run the select reltuples from pg_class where relname = '$TBL_NAME' command to obtain the value.
• You can run the select n_dead_tup from pg_stat_user_tables where relname = '$TBL_NAME' command to obtain the value of number_of_dead_tuples.
• n_mod_since_analyze is the number of tuples that have been inserted, updated, and deleted since the previous auto-analyze operation.

When autovacuum_vacuum_scale_factor is set to 0.2, autovacuum_vacuum_threshold is set to 50, and the number of live tuples in the table is 2,000, the threshold is 450 (2,000 x 0.2 + 50). When the value of number_of_dead_tuples exceeds 450, autovacuum is triggered.

number_of_dead_tuples > autovacuum_vacuum_scale_factor * number_of_live_tuples + autovacuum_vacuum_threshold

The condition for triggering auto-analyze is as follows:

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * number_of_live_tuples

When autovacuum_analyze_scale_factor is set to 0.2, autovacuum_analyze_threshold is set to 50, and the number of live tuples in the table is 2,000, the threshold is 450 (2,000 x 0.2 + 50). When the value of n_mod_since_analyze exceeds 450, auto-analyze is triggered.

To check the tuple information and the last autovacuum time, run the following statements:

SELECT relname  AS tablename,
       n_live_tup AS livetuples,
       n_dead_tup AS deadtuples,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables;

The pg_stat_progress_vacuum system view can be used to trace the real-time status of VACUUM operations. However, VACUUM FULL, which involves rewriting a table, will not be recorded in this view. For details about this view, see the official documentation.

If the age of a table is greater than the value of autovacuum_freeze_max_age (400 million by default on the cloud), the autovacuum daemon automatically freezes the table.

Once the database age exceeds 2 billion, the database breaks down and does not accept new transactions. You need to run VACUUM FULL in single-user mode to rectify the fault.

To check the database age, run the following statement:

SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname <> 'template1' AND datname <> 'template0' ORDER BY age DESC;

To check the top 5 oldest tables, run the following statement:

SELECT relname, relfrozenxid,  age(relfrozenxid) aa FROM pg_class WHERE relfrozenxid != 0 ORDER BY aa DESC LIMIT 5;

Autovacuum Parameters

You can check autovacuum parameters by querying the pg_settings system catalog.

select category, name,setting,unit,source,min_val,max_val from pg_settings where category = 'Autovacuum' ;
category  |                name                 |  setting  | unit | source  | min_val |  max_val   
------------+-------------------------------------+-----------+------+---------+---------+------------
 Autovacuum | autovacuum                          | on        |      | default |         | 
 Autovacuum | autovacuum_analyze_scale_factor     | 0.1       |      | default | 0       | 100
 Autovacuum | autovacuum_analyze_threshold        | 50        |      | default | 0       | 2147483647
 Autovacuum | autovacuum_freeze_max_age           | 200000000 |      | default | 100000  | 2000000000
 Autovacuum | autovacuum_max_workers              | 3         |      | default | 1       | 262143
 Autovacuum | autovacuum_multixact_freeze_max_age | 400000000 |      | default | 10000   | 2000000000
 Autovacuum | autovacuum_naptime                  | 60        | s    | default | 1       | 2147483
 Autovacuum | autovacuum_vacuum_cost_delay        | 2         | ms   | default | -1      | 100
 Autovacuum | autovacuum_vacuum_cost_limit        | -1        |      | default | -1      | 10000
 Autovacuum | autovacuum_vacuum_scale_factor      | 0.2       |      | default | 0       | 100
 Autovacuum | autovacuum_vacuum_threshold         | 50        |      | default | 0       | 2147483647

The workload of autovacuum depends on the following parameters:

• autovacuum_vacuum_cost_limit: specifies workload completed at a time.
• autovacuum_vacuum_cost_delay: specifies how many milliseconds autovacuum pauses after reaching the cost limit specified by autovacuum_vacuum_cost_limit.

Autovacuum Problems

• Long-running transactions or uncommitted transactions prevent autovacuum from functioning.

  According to the MVCC mechanism, autovacuum cannot clear dead tuples if there are any long-running transactions or uncommitted transactions. For data snapshots taken before a deletion or update done by the current transaction, autovacuum skips these dead tuples. If autovacuum cannot work properly, check sessions in the idle in transaction state.

  Run the following SQL statement to query the lock information of the autovacuum daemon:

  select locktype, relation::regclass, pid, mode, granted from pg_locks where pid in($autovacuum_pid);

  Run the following SQL statement to query a long-running transaction and wait event:

  select pid,wait_event_type,wait_event,state,backend_start,xact_start,query_start,state_change, query from pg_stat_activity where state not in ('idle', 'active');

  Run the following SQL statement to kill the transaction:

  select  pg_terminate_backend(pid);

• Deprecated replication slots prevent autovacuum from functioning.

  Replication slots store information required by the standby instance to catch up with the primary instance. If the standby instance breaks down or lags far behind the primary instance, the columns in the replication slots on the primary instance cannot be cleared.

  Run the following SQL statement to query replication slots that contain old transactions:

  SELECT slot_name, slot_type, database, xmin, catalog_xmin FROM pg_replication_slots ORDER BY age(xmin), age(catalog_xmin) DESC;

  Use pg_drop_replication_slot() to delete the deprecated replication slots.

• Autovacuum often uses too many resources or disrupts workloads.

  This is generally because autovacuum is executed too frequently. Adjust the following parameters to reduce the execution frequency:

  • Increase the value of autovacuum_vacuum_cost_delay and decrease the value of autovacuum_vacuum_cost_limit.
  • Decrease the value of autovacuum_max_workers.

  If workloads are heavy, create a scheduled task to clear data during off-peak hours or create a scheduled autovacuum task for specific tables.

• Table bloat becomes faster.

  When autovacuum is running properly, a surge in workload can rapidly create numerous dead tuples. As a result, autovacuum lags behind, and query performance deteriorates. Adjust the following autovacuum parameters to tune performance:

  • Decrease the value of autovacuum_vacuum_cost_delay and increase the value of autovacuum_vacuum_cost_limit.
  • Increase the value of autovacuum_max_workers to increase the number of concurrent tasks.
• Autovacuum is suspended or executed slowly.

  If autovacuum is suspended or executed slowly, execute VACUUM manually.

  1. Run the following SQL statement to query the status of autovacuum and find the PID of autovacuum:

     SELECT datname, usename, pid, now() - xact_start AS runtime, query
     FROM pg_stat_activity 
     WHERE upper(query) LIKE '%VACUUM%' 
     ORDER BY xact_start;

  2. Run the following command multiple times to check whether autovacuum is running properly:

     SELECT pg_terminate_backend($PID);

     After confirming that autovacuum is running properly, manually run VACUUM FREEZE on the table to be cleared to terminate the autovacuum daemon.

     The verbose parameter is optional.

     vacuum freeze verbose $TBL_NAME;