PostgreSQL use visibility map (VM) to determine whether all tuples in the page are visible and whether further vacuum effort needed. (Reference: pg_visibility)
- The all-visible bit in the visibility map indicates that every tuple in the corresponding page of the relation is visible to every current and future transaction.
- The all-frozen bit in the visibility map indicates that every tuple in the page is frozen; that is, no future vacuum will need to modify the page until such time as a tuple is inserted, updated, deleted, or locked on that page.
Let's examine in detail.
1. Install the pg_visibility extension.
mytest=> create extension pg_visibility;
CREATE EXTENSION
mytest=> \x
Expanded display is on.
mytest=> \dx
List of installed extensions
-[ RECORD 1 ]-----------------------------------
Name | pg_visibility
Version | 1.2
Schema | public
Description | examine the visibility map (VM) and page-level visibility info
-[ RECORD 2 ]------------------------------------
Name | plpgsql
Version | 1.0
Schema | pg_catalog
Description | PL/pgSQL procedural language
mytest=> \x
Expanded display is on.
mytest=> \df pg_visibility_map
List of functions
-[ RECORD 1 ]-------+---------------------------
Schema | public
Name | pg_visibility_map
Result data type | record
Argument data types | regclass, blkno bigint, OUT all_visible boolean, OUT all_frozen boolean
Type | func
-[ RECORD 2 ]-------+---------------------------
Schema | public
Name | pg_visibility_map
Result data type | SETOF record
Argument data types | regclass, OUT blkno bigint, OUT all_visible boolean, OUT all_frozen boolean
Type | func
2. Create sample table (t) and insert testing data
mytest=> create table t (id int,name varchar(10));
CREATE TABLE
mytest=> insert into t values(1,'a'),(2,'b');
INSERT 0 2
3. Check the VM before vacuum
mytest=> select pg_visibility_map('t'::regclass);
pg_visibility_map
-------------------
(0,f,f)
(1 row)
mytest=> select age(relfrozenxid) from pg_class where relname='t';
age
-----
16
(1 row)
Findings:
- blkno: 0
- all_visible: f
- all_frozen: f
- relfrozenxid age: 16 (The number may vary based on your database workload)
4. Run the Vacuum, and check VM, "all_visible" shall be updated.
mytest=> vacuum (verbose) t;
INFO: vacuuming "public.t"
INFO: "t": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 598
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mytest=> select pg_visibility_map('t'::regclass);
pg_visibility_map
-------------------
(0,t,f)
(1 row)
mytest=> select age(relfrozenxid) from pg_class where relname='t';
age
-----
16
(1 row)
Findings:
- blkno: 0
- all_visible: t
- all_frozen: f
- relfrozenxid age: 16
5. Run the Vacuum with "freeze=true", and check VM, "all_frozen" shall be updated. relfrozenxid age reduced as well.
mytest=> vacuum (verbose,freeze) t;
INFO: aggressively vacuuming "public.t"
INFO: "t": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 599
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mytest=> select pg_visibility_map('t'::regclass);
pg_visibility_map
-------------------
(0,t,t)
(1 row)
mytest=> select age(relfrozenxid) from pg_class where relname='t';
age
-----
0
(1 row)
- blkno: 0
- all_visible: t
- all_frozen: t
- relfrozenxid age: 0
