Making use of more VACUUM features

VACUUM has steadily been improved over the years. In this section, you will learn about some of the more recent improvements.

In many cases, VACUUM can skip pages. This is especially true when the visibility map suggests that a block is visible to everyone. VACUUM may also skip a page that is heavily used by some other transaction. DISABLE_PAGE_SKIPPING disables this kind of behavior and ensures that all pages are cleaned during this run. 

One more way to improve on VACUUM is to use SKIP_LOCKED: the idea here is to make sure that VACUUM does not harm concurrency. If SKIP_LOCKED is used, VACUUM will automatically skip over relations, which cannot instantly be locked, thus avoiding conflict resolution. This kind of feature can be very useful in the event of heavy concurrency.

One of the important and sometimes overlooked aspects of VACUUM is the need to clean up indexes. After VACUUM has successfully processed a heap, indexes are taken care of. If you want to prevent this from happening you can make use of INDEX_CLEANUP. By default, INDEX_CLEANUP is true but depending on your workload you might decide to skip index cleanup in some rare cases. So, what are those rare cases? Why might anybody not want to clean up indexes? The answer is simple: if your database may potentially soon shut down due to transaction wraparound, it makes sense to run VACUUM as quickly as possible. If you've got a choice between downtime and some kind of postponed cleanup, you should opt for VACUUM quickly to keep your database alive.