Vacuum vs Vacuum Full - PostgreSQL

Have you ever been in a situation where you find out that your PostgreSQL DB is taking too much storage?

You must have then googled on how to manage/decrease disk usage for a PostgreSQL installation. And the results must have led you two commands, VACUUM and VACUUM FULL.

Well, I found myself in the same situation while managing our self-hosted sentry setup. This led me to deep dive into the above two commands used for Postgre maintenance.

Lets discuss about the two distinct commands and how they serve a unique role in managing database health, performance, and storage utilization. This article delves into the differences between VACUUM and VACUUM FULL, exploring their internal processes, advantages, disadvantages, and their impact on database performance, storage, and resource utilization. It also shares insights on PostgreSQL auto-vacuum.

Overview

• VACUUM: A maintenance utility that cleans up database rows that have been marked as deleted or are no longer visible to any transactions. It marks the space occupied by these rows as available for future reuse, without locking the table against concurrent read or write operations.
• VACUUM FULL: A more intensive version of VACUUM, this command not only removes dead tuples but also physically compacts the database files, requiring an exclusive lock on each table it processes. This operation can significantly reduce the physical size of the database but at the cost of database availability during the operation.

Key Differences in Operation

• Concurrency: VACUUM allows for database operations to continue unimpeded, while VACUUM FULL locks tables, preventing access until the operation is complete.
• Resource Usage: VACUUM is designed to be lightweight and minimally disruptive, whereas VACUUM FULL is resource-intensive, requiring significant disk I/O and processing power.
• Storage Impact: VACUUM does not reduce the physical file size but makes space within those files available for reuse. VACUUM FULL, in contrast, can significantly reduce the database's physical footprint on the disk by rewriting table files without the dead tuples.

Pros and Cons

| Aspect           | VACUUM           | VACUUM FULL             |
|------------------|------------------|-------------------------|
| Performance      | Minimal, allows  | Significant, due to     |
| Impact           | for continuous   | exclusive locks and     |
|                  | operation.       | high I/O.               |
|                  |                  |                         |
| Efficiency       | High, as it can  | Lower, best used        |
|                  | be run frequently| sparingly due to        |
|                  | with little      | resource demands.       |
|                  | impact.          |                         |
|                  |                  |                         |
| Storage          | Indirect, does   | Direct, reduces file    |
| Reclamation      | not release space| size and releases space |
|                  | to OS.           | to OS.                  |
|                  |                  |                         |
| Best Use Case    | Regular          | Post-major deletions or |
|                  | maintenance.     | to reclaim disk space.  |
|------------------|------------------|-------------------------|        

Internal Process Comparison

VACUUM

• Scan: PostgreSQL scans the table to identify "dead tuples" — rows that have been updated or deleted and are no longer visible to any transactions. This scan also helps to update the visibility map, which tracks which pages can be skipped during future scans for dead tuples.
• Mark Space Reusable: Instead of physically removing the dead tuples from the disk, VACUUM marks the space they occupy as available for reuse by future inserts or updates to the table. This approach avoids heavy disk writes and keeps the operation lightweight.
• Prevent Transaction ID Wraparound: PostgreSQL uses a transaction ID system for versioning rows. VACUUM helps to prevent the transaction ID from wrapping around by freezing the IDs of old rows, making more transaction IDs available for future use.
• Update Indexes: While VACUUM does not directly remove dead tuples from indexes, it updates index pointers to ensure they no longer point to dead rows. This process can indirectly lead to some space being freed up within the index files.
• Visibility Map Update: Updates the visibility map to mark which pages have no dead tuples and are therefore fully visible to all transactions. This optimization helps speed up subsequent scans, especially for index-only scans.

VACUUM FULL

• Lock Table: VACUUM FULL starts by acquiring an exclusive lock on the table to be vacuumed, preventing any other reads, writes, or schema modifications on that table during the operation.
• Create New Table File: PostgreSQL creates a new, empty file on disk to store the compacted version of the table. This step is where VACUUM FULL differs significantly from VACUUM, as it physically moves rows into a new file.
• Copy Live Tuples: The system scans the original table file and copies all "live" tuples (rows that are still valid and visible to transactions) into the new file. During this process, it discards the dead tuples, effectively reclaiming their space.
• Rebuild Indexes: After all live tuples have been copied to the new table file, PostgreSQL rebuilds all indexes on the table. This step is necessary because the physical locations of the tuples have changed, and the indexes must be updated to reflect these new locations.
• Swap Files: Once the new table file is fully populated and the indexes are rebuilt, PostgreSQL swaps the old table file with the new one. The old file is then deleted from the disk.
• Release Lock: Finally, the exclusive lock on the table is released, allowing normal operations to resume.

Impact

VACUUM

• Performance: Enhances query performance over time by ensuring space is efficiently reused, reducing table bloat and maintaining index efficiency.
• Resource Utilization: Minimal impact on CPU and disk I/O, making it ideal for frequent execution.

VACUUM FULL

• Performance: Can temporarily degrade performance due to the exclusive locks required, but results in a more compact and often faster database.
• Resource Utilization: High, due to the need to rewrite entire tables, which increases disk I/O and CPU usage.

Optimal Use Cases

• For Regular Maintenance: Use VACUUM to maintain database health and performance without disrupting normal operations. Ideal for frequent, scheduled maintenance tasks.
• Post-Bulk Operations: After large-scale deletions, updates, or bulk data imports, VACUUM FULL can be beneficial to compact the database and reclaim unused space, improving overall efficiency.

Auto-VACUUM in PostgreSQL

In addition to manual vacuuming, PostgreSQL features an AUTOVACUUM daemon that automates the process of vacuuming the database. This background process is designed to monitor database activity and automatically perform VACUUM (and ANALYZE, which updates statistics for the optimizer) operations on tables based on their levels of insert, update, and delete operations. The primary goal of AUTOVACUUM is to reduce the need for manual maintenance, ensuring that the database remains efficient and that transaction ID wraparound issues are prevented without administrative intervention.

Highlights of AUTOVACUUM:

• Automatic Maintenance: Runs VACUUM and ANALYZE operations as needed, based on database activity, without requiring manual scheduling.
• Configurable: While enabled by default, its behavior can be finely tuned or disabled for individual tables through configuration parameters.
• Performance Optimization: Designed to minimize the impact on database performance by dynamically adjusting the aggressiveness of vacuuming and analyzing based on system load and activity levels.
• Prevents Transaction ID Wraparound: By regularly vacuuming tables, it helps prevent the occurrence of transaction ID wraparound, a situation that can lead to significant downtime if not managed.

Conclusion

The choice between VACUUM and VACUUM FULL hinges on the specific needs of database maintenance, balancing performance optimization with resource utilization and operational continuity. Regular use of VACUUM ensures efficient space reuse and performance maintenance, while VACUUM FULL serves as a powerful tool for significant space reclamation and database compaction after extensive changes. Understanding and leveraging these tools effectively is key to optimizing PostgreSQL databases for high performance, reliability, and storage efficiency.

Incorporating AUTOVACUUM into your PostgreSQL maintenance strategy can significantly reduce the administrative overhead of maintaining database health and performance. It complements manual VACUUM and VACUUM FULL operations by ensuring that the database is regularly and automatically optimized for both space utilization and query performance.