Google Cloud’s AlloyDB is the next-generation managed PostgreSQL service that is designed to handle cloud-scale operational and analytical workloads. It has several autopilot features that allow it to self-update and self-tune, including automatic memory management and adaptive autovacuum. This blog focuses on how AlloyDB’s adaptive autovacuum feature mitigates PostgreSQL VACUUM challenges and runs the process in the most efficient and uninterrupted manner.
PostgreSQL’s Multi Version Concurrency Control (MVCC) enables multiple transactions to occur simultaneously without blocking each other by creating multiple versions of each row (tuples). It achieves this by creating multiple versions of each row, where each version corresponds to a different point in time. In MVCC, each transaction is assigned a unique Transaction ID (XID), which represents the order of the transaction’s execution. PostgreSQL’s MVCC causes two issues.
1. Transaction ID wraparound: PostgreSQL transaction IDs or XIDs are 32-bit unsigned integers that are assigned to each transaction and also get incremented. When they reach their maximum value, it would wrap around to zero (similar to a ring buffer) and can lead to data corruption.
2. Bloat: The large accumulation of obsolete data in tables, indexes, and system catalogs leads to bloat. This over time reduces database performance as the query planner’s accuracy is impacted and also the read operations have to go through more pages.
To address these issues, PostgreSQL has a VACUUM process. When invoked manually, it scans the tables and eliminates inactive tuples and updates table statistics. Users usually configure AUTOVACUUM, an automated background process in PostgreSQL that triggers the VACUUM process based on parameters such as autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty.
However, there are some of the challenges of PostgreSQL AUTOVACUUM:
The default autovacuum settings may not be sufficient for all workloads. It can be challenging to determine the optimal settings for a particular database and even specific tables.
The autovacuum process can be a source of contention, especially on busy systems. If not properly configured or managed, the autovacuum process can have a negative impact on performance.
The autovacuum has fixed resource budgets (cost limit, number of vacuum worker processes, and memory). It cannot automatically adjust vacuum workload based on the customer workload and available resources.
For databases with very high transaction rates where the VACUUM process is not able to keep up, that can lead to increase in open XIDs, which will eventually lead to a XID wrap-around situation and cause long system downtime.
VACUUM that lags behind can also cause table space bloat with a lot of dead tuples and index entries. This increases unnecessary storage usage, which in turn impacts backup and restore times. This also affects query performance.
Autovacuum settings need to be adjusted carefully to suit the workload to avoid availability and performance issues. But it can be difficult to adjust vacuum settings, if the workload changes all the time.
AlloyDB’s adaptive autovacuum
AlloyDB for PostgreSQL is designed to handle mission-critical operational and analytical workloads. Large and dynamic workloads make it difficult to manually tune the autovacuum settings. AlloyDB’s adaptive autovacuum is a feature that automatically adjusts the frequency of vacuuming and analyzes operations based on the workload of the database. This helps to ensure that the database is always running at peak performance, even as the workload changes, without any interruption from the vacuum process. AlloyDB’s adaptive autovacuum goals are to:
Ensure reliable and consistent application transactional performance
Maintain high availability of the system by avoiding XID wraparound problems
Enable a hands-off approach to vacuum tuning, freeing the DBAs from having to manually tune the settings for each workload
Honor any autovacuum settings updated/tuned by users and adjust adaptive settings accordingly
AlloyDB’s adaptive autovacuum process monitors and updates the autovacuum-related PostgreSQL parameter values in real time. For example, multiple autovacuum workers can run concurrently on different tables, controlled by the autovacuum_max_workers parameter. The maintenance_work_mem parameter value defines the work memory used by each autovacuum worker. AlloyDB adjusts these parameters dynamically.
How does adaptive autovacuum work?
AlloyDB Adaptive autovacuum uses a number of factors to determine the frequency of vacuuming and analyze operations, including:
The size of the database
The number of dead tuples in the database
The age of the data in the database
The number of transactions per second vs estimated vacuum speed (for XID throttling, see #2 below)
The following are the adaptive autovacuum improvements and automatically adjusted settings in AlloyDB:
1. Dynamic vacuum resource management: Instead of using a fixed cost limit, AlloyDB uses real-time resource statistics to adjust the vacuum workers. When the system is busy, the vacuum process and resources are throttled. If enough memory is available, additional memory is allocated for vacuum workers to accelerate index vacuum.