VACUUM FULL – More is better?

VACUUM in PostgreSQL^® has always been surrounded by myths and misinformation. A particularly common belief seems to be that VACUUM FULL helps as a preventive measure. In many cases, the exact opposite is true.

VACUUM – The vacuum cleaner

Since the introduction of MVCC (Multi Version Concurrency Control) in PostgreSQL^® 6.5 in 1999, the VACUUM command has existed. This command defragments the so-called heap, i.e., the files that contain the table data, and frees up unused disk space. This is necessary because PostgreSQL^® does not physically delete rows on UPDATE or DELETE; instead, it creates a new version of the row or simply marks the row as deleted. The old version must be retained as long as there are transactions that can still “see” that row version. If a table is heavily used for UPDATE or DELETE/INSERT, and VACUUM runs too infrequently (for example, because autovacuum is not used), the so-called “dead” space in a table can grow significantly.

VACUUM FULL – Preventive reorganization?

Many administrators therefore believe that, for this reason, it is appropriate to prevent table growth in advance by running nightly VACUUM FULL jobs. This is a poor strategy for several reasons:

1. Unlike normal VACUUM, VACUUM FULL requires an exclusive table lock, meaning access is not possible for any concurrent transactions (including read-only queries).
2. VACUUM FULL performs a complete physical reorganization of the table, but not of the indexes. This changed with PostgreSQL^® 9.0. An exclusive table lock is still required.
3. If a database uses WAL archiving, VACUUM FULL causes a massive increase in transaction log volume. This can lead to problems with the backup archive.
4. You definitely need a maintenance window for exclusive access to the tables.
5. Unlike normal VACUUM, VACUUM FULL is therefore not suitable for use in 24/7 databases.

While most disadvantages can be avoided with a maintenance window, the drawbacks of running VACUUM FULL very frequently are more serious. PostgreSQL^® versions up to and including 8.4 are particularly affected. To understand this, you need to look at how the VACUUM FULL command works in these versions:

1. VACUUM FULL scans the table sequentially for dead space. The dead areas found are stored in an array in main memory during VACUUM FULL. If the array is full (limited by maintenance_work_mem), visible (i.e., active) rows are moved from the bottom into the dead areas found (provided there is sufficient space).
2. If indexes exist on the table, they must also be updated.
3. Once the array has been processed, the algorithm starts over, repeating until the end of the table is reached.
4. The table is then physically shrunk.

The main problem is reordering rows into the freed space. This causes massive I/O on the storage system. Even more serious, however, is the fact that the index must also be updated during reordering. If this happens very frequently, the index itself can become heavily fragmented. In that case, the index grows as well—this is known as index bloat. It may therefore be necessary to run a REINDEX on the tables directly after VACUUM FULL, especially if tables were heavily fragmented and many tuples were reordered. For very large tables, all of this also results in very long runtimes. Starting with PostgreSQL^® 9.0, VACUUM FULL behaves like the CLUSTER command, i.e., the table is read sequentially and rebuilt completely in parallel. The advantage is that only active rows are read, while “dead” rows are left out. The indexes are then recreated. This eliminates many disadvantages of the old algorithm, but it does not remove the need for exclusive table locks. In addition, in the worst case, reorganizing the table requires as much additional disk space as the table currently being processed.

VACUUM and autovacuum for daily or very granular maintenance

VACUUM and autovacuum are designed for daily or continuous maintenance of PostgreSQL^® databases.

1. If you define a careful VACUUM policy with normal VACUUM or, even better, autovacuum, you do not need VACUUM FULL.
2. Autovacuum should definitely be considered, but it must be adapted to the workload.
3. If a table is still heavily bloated, then with current PostgreSQL^® 8.x versions, CLUSTER can often shrink the table significantly faster, without the problem of index fragmentation. Since CLUSTER reorganizes the table based on an index, you need at least one index. You should also make sure to update the optimizer statistics afterward with ANALYZE.
4. Up to and including PostgreSQL^® 8.3, it is absolutely necessary to review the parameters max_fsm_pages and max_fsm_relations before going into production. The values of these parameters can only be changed by restarting the database and they influence the amount of fragmented free space recorded in tables and indexes, as well as the number of tables and indexes that can be tracked by VACUUM (VACUUM FULL does not use the so-called Free Space Map). Starting with PostgreSQL^® 8.4, the FSM is adjusted automatically per table.
5. Under favorable circumstances, VACUUM can also shrink a table. If the table contains only empty blocks at the end and no transaction currently wants to insert new rows into those areas, normal VACUUM can also compact the table accordingly.

So why use VACUUM FULL at all?

VACUUM FULL is a command that is not designed for daily maintenance. If the proverbial damage has already been done and a table is heavily bloated, then depending on the PostgreSQL^® version, freeing disk space with VACUUM FULL is unavoidable. With older PostgreSQL^® versions, administrators should consider using the CLUSTER command instead, especially when the table requires a great deal of disk space. If you still want to use VACUUM FULL, then on older PostgreSQL^® versions you should also recreate the indexes with REINDEX. More information on this topic can be found in the PostgreSQL^® Wiki.

Further information

All blog posts on PostgreSQL^® are also available as the PostgreSQL^® category with its own feed. We are also happy to help with support and services for PostgreSQL^®.

This post was originally written by Bernd Helmle.