Vacuum Processing in PostgresSQL

Vacuum processing is one of the core mechanism in PostgresSQL. It basically involves the removal of dead tuples in the database. In order to remove the dead tuples, it involves two modes

• Concurrent VACUUM
• Full VACUUM

Concurrent VACUUM:

Concurrent VACUUM also known as VACUUM. It removes dead tuple for each page of the table and other transactions can read the data while the process is still running. It involves three blocks:

First Block:

This block performs the freeze processing and removes index tuples that points to dead tuples.

Second Block:

This block removes the dead tuples and update both Free space map (FSM) and Visibility map (VM).

Third Block:

This block performs the cleanup after the deletion of indexes and update both the statistics and the system catalog.

Post-processing:

When the vacuum processing is complete, after updating both statistics and system catalogs related to vacuum processing, it removes unnecessary parts of the clog if possible.

Visiblity Map:

Vacuum processing is quite costly so visibility map was introduced. Each table has a visibility map that holds the visibility of the page in table file and determines the dead tuples.

Freeze Processing:

Freeze Processing has two modes

• Lazy Mode: When freeze processing scans only pages that contain dead tuples using the visibility map of the target tables.

• Eager Mode: It scans all pages to inspect all tuples in tables and update relevant system catalogs and removes unnecessary files.

Full VACUUM:

Concurrent vacuum is essential but it is not sufficient so here comes the full vacuum. It not only removes the dead tuples but also reduced the table size. But two things should be considered here:

• Nobody can access the table when Full VACUUM is processing.
• At most twice the disk space is used temporarily.

References:

https://www.interdb.jp/pg/pgsql06.html