📢 This post covers a metadata file called the Visibility Map (VM). First, we look at ① what the VM file is, which Vacuum uses as its reference information, and then ② how it changes with user operations and how to observe it.
Visibility Map
What is the VM file?
The VM is a metadata file that represents the state of each page of a heap relation (*table) with 2 bit values. It holds a condensed summary of the state of the tuples (rows) on each page.
The VM file is named after the relation OID with the suffix "_vm", and it is updated when a user runs an explicit vacuum such as
VACUUM or VACUUM FREEZE, and also when autovacuum is run by the autovacuum launcher process.📌 In PostgreSQL, a relation means a table, and a page means a block.
Information managed by the VM
Up to PostgreSQL 9.6, the VM represented only ALL_VISIBLE information with a 1 bit flag per data page. In later versions, it was extended to 2 bits per page to improve performance, so it also represents ALL_FROZEN information. Each bit means the following.
1st Bit : ALL_VISIBLE
Indicates whether the rows on the page are visible to all transactions (Boolean), which tells you whether dead tuples (rows) exist.
If ALL_VISIBLE=1 (True), all rows on the page are visible and there are no dead tuples. If it is 0 (False), the page either has dead tuples or its state is unknown because Vacuum has not run on it yet.
📌 In PostgreSQL, a row (tuple) is either live or dead. A tuple that is no longer referenced because of an Update/Delete is called a dead tuple.
2nd Bit : ALL_FROZEN
Applies only when ALL_VISIBLE is True. Indicates whether all rows on the page are frozen (Boolean).
📌 Freezing means assigning a special XID called the Frozen XID.
The 2 bit VM is used for the following purposes.
- VACUUM Performance : Vacuum targets only pages whose ALL_VISIBLE value is False, which reduces access to unnecessary pages and improves Vacuum performance
- Freezing Performance : When freezing rows to avoid Transaction ID Wraparound, pages whose ALL_FROZEN value is True are skipped, which shortens the work
- Query Performance : In an Index Only Scan (covering index), if ALL_VISIBLE is True for the target page, the table lookup can be avoided, which improves SQL performance
📌 Unfortunately, in PostgreSQL a table lookup can still occur even if you set things up properly to get an Index Only Scan. This is because the index itself does not contain the information needed to decide whether a row should or should not be read. To avoid this problem, PostgreSQL checks the ALL_VISIBLE value in the VM at SQL execution time to decide whether a table lookup is needed.
Observing VM changes
pg_visibility
You can check the 2 bit VM values with the pg_visibility extension, used as
pg_visibility('relation name').How the VM changes
The 2 bit VM values observable through pg_visibility change with DML and Vacuum operations as shown in the figure below, with the following characteristics.
- A newly created page on which no Vacuum operation has run has the VM value [0,0]
- After a Vacuum operation, the VM value of a page is [1,0] or [1,1].
- Vacuum Freeze sets the VM state to [1,1] regardless of the previous state.
- DML sets the VM state to [0,0] regardless of the previous state.
- Vacuum Full sets the VM state to [0,0] regardless of the previous state. (rebuild)
With the VM, we finish covering the background knowledge needed to understand Vacuum. The remaining posts cover how Vacuum and Autovacuum actually work.
Related articles
