Configure adaptive autovacuum

Select a documentation version:

This page provides an overview of AlloyDB Omni adaptive autovacuum and describes how to configure it.

Vacuuming is an important PostgreSQL database maintenance operation. It is used to reclaim storage space occupied by updated or deleted rows to avoid table or index bloat. It is also used for freezing transaction IDs to prevent transaction ID wraparound and for updating the table statistics.

For more information about vacuuming, see Routine vacuuming.

To automate the process of vacuuming and collectively updating the table statistics, AlloyDB Omni supports adaptive autovacuum. Adaptive autovacuum enables automatic adjustment of vacuum workload when your workload changes, removes dead tuples, and updates table statistics.

Autovacuum ensures a reliable and consistent application transactional performance and maintains high availability of the system by preventing transaction ID wraparound.

By default, AlloyDB Omni enables adaptive autovacuum. You can disable or modify adaptive autovacuum on AlloyDB Omni by using database flags.

Benefits

Adaptive autovacuum offers the following benefits:

  • Automatic adjustment of resources. AlloyDB Omni automatically adjusts resources such as CPU, I/O, number of vacuum processes, and memory for the vacuum process based on the real time status of the workload and available resources without affecting the system performance.

  • Prevention of transaction ID wraparound. AlloyDB Omni monitors the vacuum progress and transaction ID consumption speed. When necessary, AlloyDB Omni throttles its transaction ID consumption by vacuuming every table in the database, preventing transaction ID wraparound.

  • Periodic vacuum for tables. AlloyDB Omni triggers autovacuum based on the number of dead tuples and the pages that need to be scanned. It removes dead tuples in a timely manner to avoid table or index bloat.

  • Automatic detection of blockers. AlloyDB Omni automatically detects blockers such as long running transactions, orphan prepared transactions, orphan replication slots, and shows warning messages similar to the following in the postgres log:

    Found a backend process PROCESS_ID with a long running transaction whose transaction
id age AGE is larger than or equal to the transaction age threshold AGE_THRESHOLD.

Configure adaptive autovacuum

By default, adaptive autovacuum is enabled on AlloyDB Omni. You can disable adaptive autovacuum by setting an instance's enable_google_adaptive_autovacuum database flag to off.

AlloyDB Omni supports several standard PostgreSQL database flags related to vacuum and autovacuum, for example, vacuum_cost_delay and autovacuum_vacuum_cost_delay. You don't need to set values in any of these flags for adaptive autovacuum to work because adaptive autovacuum adapts and optimizes its behavior to your real workloads. If you set values in these flags, then adaptive autovacuum adjusts its behavior to take your preferences into account.

To see the complete list of supported PostgreSQL database flags related to vacuum and autovacuum, see vacuum flags and autovacuum flags.