Vacuum vs Vacuum freeze impact on visibility map in PostgreSQL

 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