Visibility map in PostgreSQL
What is Visibility Map ?
Visibility Map is a bitmap file which is created for every table. This file is named with _vm. This file contains entry for each page in the table along with two bits/flags(all_frozen,all_visible). The first bit say “all rows in the block are visible to all transactions”, second bit say “all rows in the block are frozen”. The second bit “all_frozen” is set only when “all_visible” is set. (if all rows are visible then only all can get freeze)
Why Visibility Map ?
Improve vacuum performance – The vaccum process check for the bits of the page. If the first bit – “all rows are visible” – is set then vaccum process no need to process the page.
Improve freezing performance – The vacuum process while freezing the complete table (transaction id wraparound) if the second bit – “all rows are frozen” – is set then vacuum process no need to process the page.
Improve query performance – during Index-Only scan (instead of seeing in table page, get data from index if all columns in select are part of index) if the all_visible is set then it don’t access the table page to check the visibility of row, which reduce I/O.
Who create/modify this file ?
During the first vacuum process the file will be created by vacuum process and it will be modified by subsequent user processes who do DML(update, delete) on the table. There are two bits which say “all rows are visible” one is in visibility-map file and second is in the page of table it self(PD_ALL_VISIBLE). Any DML operation need to update both the bits(all_visible and PD_ALL_VISIBLE).
All modifications to this file are WAL-logged so that during time of crash PostgreSQL can do recovery.
Below is the example which shows how this file is created and modified
postgres=# create table test_vm as select generate_series(1,100000) as id, md5(generate_series(1,100000)::text) as name;
postgres=# select pg_relation_filepath('test_vm'); pg_relation_filepath
----------------------
base/13323/65584
(1 row)
postgres=# \! ls -l $PGDATA/base/13323/65584*
-rw-------. 1 postgres postgres 6832128 May 2 00:08 /var/lib/pgsql/9.6/data/base/13323/65584
postgres=#
postgres=# vacuum test_vm;
VACUUM
postgres=# \! ls -l $PGDATA/base/13323/65584*
-rw-------. 1 postgres postgres 6832128 May 2 00:08 /var/lib/pgsql/9.6/data/base/13323/65584
-rw-------. 1 postgres postgres 24576 May 2 00:09 /var/lib/pgsql/9.6/data/base/13323/65584_fsm
-rw-------. 1 postgres postgres 8192 May 2 00:09 /var/lib/pgsql/9.6/data/base/13323/65584_vm
postgres=#
postgres=# create extension pg_visibility ;
ERROR: extension "pg_visibility" already exists
postgres=# SELECT * FROM pg_visibility('test_vm'::regclass) limit 10;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | t | f | t ====> All visible
1 | t | f | t
2 | t | f | t
....
.....
(10 rows)
postgres=#
postgres=# begin;
BEGIN
postgres=# delete from test_vm where id < 5;
DELETE 4
postgres=#
postgres=# SELECT * FROM pg_visibility('test_vm'::regclass) limit 10;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | f | f | f ====> delete command modified the bits
1 | t | f | t
2 | t | f | t
3 | t | f | t
....
....
(10 rows)pg_visibility is an extension used to read the visibility map file.
Reference:
Viswamitra's blog 