What is AlloyDB for PostgreSQL?

Google Cloud’s next-generation managed PostgreSQL service, AlloyDB, handles cloud-scale operational and analytical workloads. Autopilot features like memory management and adaptive autovacuum let it self-update and adjust. This blog discusses how AlloyDB’s adaptive autovacuum technology optimizes PostgreSQL VACUUM and executes the procedure efficiently.

Multi Version Concurrency Control (MVCC) in PostgreSQL allows many transactions to run concurrently without blocking each other by producing various row versions. Each row has numerous variations, each representing a distinct historical period. Each transaction in MVCC has a unique Transaction ID (XID) that indicates its execution order. MVCC in PostgreSQL creates two concerns.

1. Transaction ID wraparound: PostgreSQL XIDs are 32-bit unsigned numbers issued to each transaction and increased. Data corruption may occur when they reach their maximum value and wrap around to zero, like a ring buffer.

2. Bloat: Tables, indexes, and system catalogs accumulate outdated data. This affects database performance over time because the query planner’s accuracy is affected and read operations must traverse through more pages.

The VACUUM procedure in PostgreSQL addresses these difficulties. Manually scanning tables removes dormant tuples and updates statistics. PostgreSQL users define AUTOVACUUM, an autonomous background process that triggers VACUUM depending on parameters like autovacuum_vacuum_cost_limit, delay, page_hit, page_miss, and page_dirty.

However, PostgreSQL AUTOVACUUM has certain drawbacks:

• The default autovacuum settings may not work for all workloads. Finding the best database and table configurations might be difficult.
• The autovacuum operation might be annoying on busy systems. Autovacuum might slow performance if not enabled or handled appropriately.
• Cost, vacuum worker operations, and memory are fixed in the autovacuum. It cannot automatically adapt vacuum workload to customer and resource demands.
• For databases with high transaction rates, when the VACUUM process cannot keep up, open XIDs might grow, causing XID wrap-around and protracted system downtime.
• Laggard vacuum may also inflate table space with dead tuples and index entries. This wastes storage, slowing backups and restores. It also impacts query performance.

Autovacuum parameters must be changed to match workload to prevent availability and performance concerns. If workload fluctuates often, vacuum settings might be difficult to adapt.

AlloyDB adaptive autovacuum

AlloyDB for PostgreSQL handles mission-critical operational and analytical demands. Autovacuum settings are hard to manually modify with large and dynamic workloads. AlloyDB’s adaptive autovacuum modifies vacuuming frequency and evaluates operations depending on database demand. This keeps the database operating at top performance even when workload varies without vacuum process disruption. The adaptive autovacuum aims of AlloyDB are:

• Maintain stable application transactional performance
• Keep system availability high by preventing XID wraparound.
• Free DBAs from manually tweaking workload parameters with hands-off vacuum tuning.
• Autovacuum settings changed by users are honored and adaptive settings adjusted appropriately.

AlloyDB’s adaptive autovacuum mechanism continuously adjusts PostgreSQL autovacuum parameter settings. By setting autovacuum_max_workers, several workers may operate on distinct tables. Each autovacuum worker uses work memory defined by maintenance_work_mem. AlloyDB dynamically modifies these properties.

The adaptive autovacuum works how?

AlloyDB Adaptive autovacuum analyzes operations and vacuuming frequency based on many factors:

• Database size
• Number of database dead tuples
• Database data age
• Transactions per second versus. anticipated vacuum speed

The adaptive autovacuum enhancements and automatically modified parameters in AlloyDB are:

1.Dynamic vacuum resource management: AlloyDB adjusts vacuum workers based on real-time resource information rather than a cost restriction. In busy systems, vacuum process and resources are throttled. If memory is available, vacuum workers get more memory to speed index vacuum.

2. Dynamic XID Throttling: AlloyDB continually checks vacuuming and XID use. If XID wraparound is detected, AlloyDB will slowly delay transactions to control XID usage. It gives vacuuming additional resources to catch up and return to the safe zone. Transactions per second will decrease until XIDs reach the safe zone. Vacuum workers grow dynamically with XID age.

3.Efficient vacuuming for bigger tables: The default vacuum uses table-specific statistics from pg_stat_all_tables with the dead tuple ratio. Small tables benefit from that, while bigger, often updated tables may not. The improved scan method in AlloyDB triggers the autovacuum more frequently to scan sections of huge tables and eliminate dead tuples faster.

4. Log warning messages: AlloyDB detects vacuum blocks such long-running transactions, orphaned prepared transactions, orphaned replication slots, etc., and reports warnings in PostgreSQL logs so users may solve problems quickly.

• “Found a backend process XXX with a long-running transaction whose transaction id age exceeds the transaction age threshold.”
• Found an old prepared transaction XXX whose transaction id age is more than or equal to the transaction age threshold, database oid: XXX, owner oid: XXX”
• “Found a replication slot XXX whose min transaction id age is greater than or equal to the threshold.”

For AlloyDB, allow adaptive autovacuum by default. To deactivate or enable it afterwards, use the option enable_google_adaptive_autovacuum. 

Advantages of AlloyDB adaptive autovacuum

AlloyDB adaptive autovacuum adjusts the vacuum process depending on workload resource consumption without human tuning. Autovacuum parameters may be adjusted, and AlloyDB will respect them.

Benefits of adaptive autovacuum include:

Performance boost: Adaptive autovacuum removes bloat to keep the database working smoothly as workload changes.

Reduced maintenance: Adaptive autovacuum automatically changes vacuuming frequency and analyzes operations, reducing maintenance.

Improved availability: Adaptive autovacuum prevents XID wraparound, preventing database failures and improving availability.

Enabling Adaptive Autovacuum in AlloyDB benchmarking tests on 64vCPU SKU with 100% cached TPC-C runs reduced Frozen XID age from ~1.5B to ~1B and dead tuples count from ~1.2B to 0.7B, allowing the database to perform optimally over longer periods.

Conclusion

Adaptive autovacuum automates the vacuum process and improves AlloyDB database speed and availability. Allow adaptive autovacuum today if you haven’t.

News source: