Optimizing The Efficiency of Vacuum Processing - Visibility Map

The concept of a visibility map is a feature introduced in PostgreSQL to optimize the efficiency of vacuum processing. It tracks the visibility information of individual data pages within a table.

The main purpose of the visibility map is to reduce the need for scanning entire tables during the vacuum process. When vacuuming a table, one of the primary tasks is to identify and remove dead tuples (rows) from the table's data pages. However, scanning every page of a table can be a time-consuming and resource-intensive operation, especially for large tables. The visibility map addresses this challenge by storing a bit for each data page in a separate map file. This bit represents the visibility status of the tuples on the corresponding data page. It can have two states: "All Visible" or "Some Tuples May Be Invisible."

During vacuum processing, instead of scanning every data page, PostgreSQL first checks the visibility map to determine if a particular page has all visible tuples. If the visibility map indicates that all tuples on a page are visible to all transactions, PostgreSQL can skip the scanning of that page altogether. This optimization significantly reduces the amount of I/O and processing required during vacuuming.

Freeze Processing

Freeze processing in PostgreSQL offers two modes, known as lazy mode and eager mode, which are chosen based on specific conditions.

By default, freeze processing operates in lazy mode, where it scans only the pages containing dead tuples using the visibility map (VM) of the target tables. On the other hand, eager mode is triggered under certain circumstances. In eager mode, freeze processing scans all pages, regardless of whether they contain dead tuples or not.

Improving Freeze Processing in Eager Mode

Earlier versions of the eager mode in PostgreSQL were inefficient as they would scan all pages, even if all the tuples within a page were already frozen.

To address this inefficiency, improvements were made to the visibility map (VM) and freeze process in later versions. The enhanced VM now includes information about whether all tuples within a page are frozen or not. Consequently, when freeze processing is executed in eager mode, pages that solely consist of frozen tuples can be skipped, resulting in improved efficiency.