엑셈

📢 In this document, we first look at ① the purpose of Vacuum and ② Vacuum operating modes. Then we explain ③ how Vacuum Freeze works, since it behaves a bit differently, and finally ④ the overall Vacuum process. Because this document combines concepts introduced in the previous documents, we recommend reading it with a solid understanding of them.

Manual Vacuum

What Is Vacuum?

Vacuum, named after the vacuum cleaner, is a concept specific to PostgreSQL. As emphasized in the previous documents, it exists to avoid side effects that stem from how PostgreSQL works, such as space inefficiency caused by the way MVCC works and XID wraparound.

Depending on the command combination, Vacuum is divided into VACUUM, VACUUM FULL, VACUUM FREEZE, and so on. In this document, we look at manual Vacuum run directly by the user, not Vacuum run by the Autovacuum Launcher.

Purpose of Vacuum

The main purposes of Vacuum can be summarized in 4 points:

In this document, we cover the core goals of Vacuum, reclaiming disk space and preventing XID wraparound, and then look at the Vacuum operating modes.

Reclaiming Disk Space

To implement the MVCC model, PostgreSQL keeps old versions of data instead of deleting them, so frequently modified tables inevitably grow. However, as long as old versions are guaranteed to no longer be referenced by any running transaction, they can safely be deleted or marked reusable.

Vacuum reclaims disk space by marking old-version data as reusable or returning the physical space of old-version data to the OS.

The former is called Standard Vacuum and is run with the VACUUM command. The latter rebuilds the file without the old-version data and is run with the VACUUM FULL command.

Standard Vacuum works as follows.

In terms of space management, Standard Vacuum does not seem to minimize space usage. However, the reusable space reclaimed by regular Vacuum runs curbs data growth, so it partly achieves the goal of keeping space usage optimal.

Vacuum Full, on the other hand, works in a completely different way from Standard Vacuum.

If Standard Vacuum is a process that reclaims reusable space from existing data, Vacuum Full is similar to a table reorganization.

In other words, it builds a new table from only the live tuples and drops the old table, which allows physical space to be returned. However, this rebuild requires acquiring an exclusive lock on the table, which reduces concurrency, so use it with care. Also, enough disk space for the new table must be available before the operation can start.

Preventing Loss of Old Data

Data freezing to prevent XID wraparound is another main purpose of Vacuum.

Data freezing works as follows and runs after the disk space cleanup described above.

As mentioned before, data freezing targets rows (tuples) whose age is greater (older) than VACUUM_FREEZE_MIN_AGE. Lowering VACUUM_FREEZE_MIN_AGE freezes more rows but needs more time and resources.

At first glance, you might think a low VACUUM_FREEZE_MIN_AGE has the advantage of reducing the rows that need freezing later. However, if you set a low VACUUM_FREEZE_MIN_AGE on a table where recent data is frequently modified (Delete/Update), recently frozen data can switch back to ALL_FROZEN=0. This causes the inefficiency of repeatedly freezing recently changed data, so choose a setting that fits the table's purpose and usage pattern.

📌 vacuum_freeze_min_age

The minimum age of a row that Vacuum freezes. The default is 50 million (50,000,000).

Standard Vacuum Mode

Internally, Standard Vacuum runs in 2 modes, called Eager Mode and Lazy Mode. Let's look at the criteria for each mode and how each works internally.

📌 These two mode names do not appear to be official terms in the documentation. Eager Mode and Lazy Mode are also called Aggressive Vacuum and Vacuum, but in this document we use Eager Mode / Lazy Mode to distinguish them from the Aggressive Vacuum used for Vacuum Freeze.

Mode Criteria

Which of the two modes is chosen depends only on the table's age.

If the table's age is greater (older) than VACUUM_FREEZE_TABLE_AGE when Vacuum runs, it runs in Eager Mode; if it is less than VACUUM_FREEZE_TABLE_AGE, it runs in Lazy Mode.

📌 vacuum_freeze_table_age

The age used to decide the Vacuum mode. The default is 150,000,000.

Differences Between the Modes

The difference between the modes, which depends on the table's age, lies in the target pages. Each mode defines its target pages as follows.

In other words, Lazy Mode targets only pages with dead tuples (ALL_VISIBLE_=0), regardless of whether they are frozen, while Eager Mode targets more broadly every page that is not frozen (ALL_FROZEN=0). Why the difference?

Let's take an example. Suppose a table only receives inserts and is never modified. If Standard Vacuum runs after the inserts while the rows' age has not yet reached VACUUM_FREEZE_MIN_AGE, every page of the table will be ALL_VISIBLE=1, ALL_FROZEN=0.

But if Vacuum only ran in Lazy Mode, pages in that state (ALL_VISIBLE=1, ALL_FROZEN=0) would always be excluded from Vacuum. In other words, even pages that need no space cleanup but hold rows that may need data freezing would be excluded from Vacuum.

To prevent this, when the table's age is greater than VACUUM_FREEZE_TABLE_AGE, Vacuum runs in Eager Mode, which targets every page with ALL_FROZEN=0.

A Vacuum run in Eager Mode means the data freezing state of every page has been checked, so the relfrozenxid used for the table's age can also be updated.

📌 Having checked the data freezing state of every row does not mean every row was frozen; it means every row was confirmed not to need freezing.

📌 Before 9.6, the VM had no ALL_FROZEN bit, so Eager Mode had to scan every page of the table, which was inefficient. With the ALL_FROZEN bit added in 9.6, pages to skip can be identified, which improved Vacuum performance, or more precisely, Eager Mode Vacuum performance.

relfrozenxid After Eager Mode

The relfrozenxid value used for a table's age can be updated when certain conditions are met. - From PostgreSQL Vacuum - 3. Age

The certain conditions not fully explained in that document refer to having checked, through Eager Mode, whether every page with ALL_FROZEN=0 needs data freezing. When Vacuum runs in Eager Mode, the table's relfrozenxid changes as follows.

The changed relfrozenxid also means the following.

The age of the table's oldest row is guaranteed to be VACUUM_FREEZE_MIN_AGE.

Since the age of the table's oldest row is VACUUM_FREEZE_MIN_AGE, the table's age also changes to VACUUM_FREEZE_MIN_AGE. (It gets younger.)

Vacuum Freeze

Besides VACUUM and VACUUM FULL, mentioned when explaining the purpose of Vacuum, there is also the VACUUM FREEZE command.

In fact, Vacuum with the FREEZE option works exactly like Standard Vacuum. The only difference is that it internally sets the following 2 parameters to 0 before running Vacuum.

When VACUUM_FREEZE_TABLE_AGE is set to 0 internally, the table's age is always greater than VACUUM_FREEZE_TABLE_AGE, so Vacuum runs in Eager Mode. And when VACUUM_FREEZE_MIN_AGE, the criterion for data freezing, is set to 0, every tuple in the target pages becomes a target for data freezing.

Because Vacuum with the FREEZE option runs more aggressively than Standard Vacuum, it is also called Aggressive Vacuum.