In chapter 6 we discuss vacuum processing in PostgreSQL. It is a maintenance process which helps to ensure efficient operation of the database. It mainly involves removing dead tuples and freezing transaction IDs to improve performance and free up space.
Vacuum processing was executed manually until version 8.0 when autovacuum daemon was introduced which automated the process. There are two main modes for vacuum processing, Concurrent VACUUM and Full VACUUM.
Concurrent VACUUM, also known as VACUUM, removes dead tuples for each page while also allowing other transactions to read the table at the same time. On the other hand, Full VACUUM removes dead tuples and defragments live tuples for the entire file but it locks the table which prevents other transactions from accessing it during the process.
In this chapter, we look at the steps involved in concurrent vacuum processing. These include removing dead tuples and index tuples which point to dead tuples, freezing old transaction IDs, updating system catalogs and performing various cleanup tasks.
A key optimization which has been introduced in version 8.4 is the Visibility Map or VM. It increases the efficiency of removing dead tuples by keeping track of the page visibility. VM allows the vacuum process to skip pages which do not have any dead tuples and thus reduces the overall cost of the process.
Freeze processing is an important part of vacuum processing. It involves freezing old transaction IDs to prevent the wraparound of transaction IDs and to ensure the stability of the database in the long-run. It operates in two modes: lazy mode and eager mode.
In lazy mode, freeze processing scans only pages that contain dead tuples, meanwhile in eager mode, it scans all pages and also performs some additional tasks such as updating system catalogs and removing unnecessary files. The mode used can depend on certain conditions, such as the value of the freezeLimit transaction ID and the state of the pg_database system catalog.
Finally, the chapter briefly talks about the PARALLEL option, which allows vacuuming and index cleaning to be performed in parallel for tables with multiple indexes. This is supported in the VACUUM command but not in autovacuum.
Top comments (0)