PostgreSQL 9.6 is shipping with a new contrib module manipulating and giving some input on the visibility map of a relation:
Add pg_visibility contrib module. This lets you examine the visibility map as well as page-level visibility information. I initially wrote it as a debugging aid, but was encouraged to polish it for commit. Patch by me, reviewed by Masahiko Sawada. Discussion: 56D77803.6080503@BlueTreble.com
The visibility map, associated to a relation in its own file, which is named with the suffix _vm, tracks information related to the visibility of tuples on relation pages for each backend. Up to 9.5, 1 bit was used per heap page, meaning that if this bit is set all the tuples stored on this page are visible to all the transactions. In 9.6, 2 bits are being used, the extra bit added is used to track if all tuples on a given page have been frozen or not, critically improving VACUUM performance by preventing full table scans.
pg_visibility contains a couple of functions allowing one to get a look at the status of each page’s bits. The first one, and aimed at general purposes, gives an insight about the all-frozen and all-visible status for each page of a relation, plus the status of PD_ALL_VISIBLE which is the same information as the all-visible flag except that it is stored in the heap page itself and not the VM file:
=# CREATE TABLE tab_visible AS SELECT generate_series(1,1000) AS id; SELECT 1000 =# SELECT * FROM pg_visibility('tab_visible'::regclass); blkno | all_visible | all_frozen | pd_all_visible -------+-------------+------------+---------------- 0 | f | f | f 1 | f | f | f 2 | f | f | f 3 | f | f | f 4 | f | f | f (5 rows)
This function can take an optional argument in the shape of a block number. pg_visibility_map is similar to the previous function, except that it does not scan the all-visible flag value on the page and it just fetches what is available on the visibility map.
Then come the sanity checkers: pg_check_visible and pg_check_frozen that return a list of TIDs where refer to tuples that are respectively not all-visible and all-frozen even if the page they are on is marked as such. Those functions returning an empty set means that the database is not corrupted. If there are entries. Oops.
=# SELECT pg_check_visible('tab_visible'::regclass); pg_check_visible ------------------ (0 rows) =# SELECT pg_check_frozen('tab_visible'::regclass); pg_check_frozen ----------------- (0 rows)
And finally is a function that may become useful for maintenance purposes: pg_truncate_visibility_map which removes the visibility map of a relation. The next VACUUM that runs on this relation will forcibly rebuilt the visibility map of the relation. Note that this action is WAL-logged.
=# SELECT pg_truncate_visibility_map('tab_visible'::regclass); pg_truncate_visibility_map ---------------------------- (1 row)