PostgreSQL Speed up VACUUM - Part 1

In another article on VACUUM, We discussed the importance of VACUUM in PostgreSQL and provided a detailed questionnaire to help you understand its intricacies. This article focus on how to optimize VACUUM performance by adjusting various parameters and best practices. These optimizations are crucial for scenarios where rapid VACUUM execution is essential, such as preventing transaction ID wraparound issues that could lead to database outages or quickly removing a huge backlog of dead tuples to improve overall database performance.

Optimize Throttling Flags

A significant accumulation of dead tuples or high transaction ID utilization often stems from inefficient VACUUM operations, typically caused by slow execution or blockages that hinder the process.

Slow VACCUM:

PostgreSQL provides numerous parameters to control the resource consumption of VACUUM at both the instance and the table level. These parameters have default values designed to prevent a single VACUUM job from monopolizing system resources. However, you can adjust these parameters to make VACUUM more aggressive, leading to faster completion times.

autovacuum_vacuum_cost_limit
autovacuum_vacuum_cost_delay
maintenance_work_mem
autovacuum_work_mem
autovacuum_max_workers
autovacuum_vacuum_scale_factor

Blocked VACUUM:

To prevent runaway queries and free up resources, we can enforce limits on query execution time, either per query or per user. Additionally, idle sessions can be automatically terminated after a predefined period of inactivity.

statement_timeout
idle_in_transaction_session_timeout

Drop Unused Indexes

Unused indexes in PostgreSQL can be detrimental to performance. Not only do they require maintenance during VACUUM, adding overhead to the process, but they can also hinder HOT Updates, a feature that optimizes in-place updates. Fortunately, PostgreSQL 12 introduced the ability to defer index cleanup, eliminating the slow random I/O associated with this operation and improving VACUUM efficiency.

VACUUM (INDEX_CLEANUP False, VERBOSE) test;

FILLFACTOR

The fillfactor property plays a crucial role in enabling HOT updates within a PostgreSQL table. By controlling how full data pages are allowed to become, fillfactor reserves space for future updates. This allows modifications to occur directly on the existing data page, preventing cascading changes to the table's indexes and significantly improving VACUUM performance. I have discussed the FILLFACTOR in good detail in another article.

Table Truncate

While it might seem drastic, there may be situations where a database outage is imminent due to transaction ID wraparound, and the required anti-wraparound VACUUM on a large table would take hours to complete. In such critical cases, if the table’s use case allows (for example, a logging table not actively used by applications), truncating the table can be a viable solution to quickly restore normal database operations. This decision should be made carefully, considering the business impact and data retention policies.

Increase vacuum_freeze_min_age

To alleviate immediate pressure during a potential transaction ID wraparound crisis, we can temporarily increase the vacuum_freeze_min_age flag. This reduces the workload for the anti-wraparound VACUUM by allowing some newer rows to remain unfrozen. These rows can be frozen later when the database is stable and operating normally.

Small/Partitioned Tables

Dealing with numerous smaller tables, running VACUUM jobs in parallel can significantly expedite the process, especially since PostgreSQL’s current capabilities for parallel processing within a single table are limited. Additionally, managing transaction ID wraparound is generally easier with smaller tables compared to a single, massive table.

There are plenty of development opportunities in the VACUUM space to make it further efficient and a few of them are already getting discussed in the hacker emailing:

• Postgres v13 added capaciblity of vacuuming indexes in parallel and I would like to see more parallelism in during other phases of vaccuum activity.
• Currently a vacuum job uses a maximum of 1 GB maintenance_work_mem. [UPDATE] Postgres v17 introduced an adaptive radix tree which significantly reduces the memory consumption during Indexe cleanup phase.
• Currently for a vacuum job, Postgres allocates a small ring buffer (to avoid cache sweep). Once the ring buffer space is full, it needs to sync the changes from WAL buffers and that creates a lot of thrashing . times. [UPDATE] Postgres v16 introduced a configrable flag for this ring buffer known as vacuum_buffer_usage_limit.
• Currently, VACUUM resets a table’s transaction ID age only after a complete and successful run. For example, if a table’s age reaches 500 million, the age can be reset to vacuum_freeze_min_age, but this requires visiting all unfrozen blocks referenced by the Visibility Map. To address this, I initated a disccusion on incremental wraparound vacuum. This approach, however, could introduce performance trade-offs. Scanning the heap table through an out-of-order B-tree index might lead to increased random reads and decreased sequential reads, potentially impacting overall efficiency.

Alright, enough with the theoretical deep dive for now! In the next installment (part 2), we’ll shift our focus to the practical side of things. We’ll explore how TOAST (The Oversized-Attribute Storage Technique) influences VACUUM performance and conduct some comparative runtime analysis to see these concepts in action.