pg_visibility
The pg_visibility module provides a way to examine the visibility map (VM) and page-level visibility information for a table. It also provides functions to check the integrity of the visibility map and to force rebuilding of the visibility map.
Three different bits are used to store information about page-level visibility. The "all-visible" bit in the visibility map indicates that all tuples on the corresponding page of a relation are visible to all current and future transactions. The "all-frozen" bit in the visibility map indicates that every tuple on the page is frozen, meaning no vacuum is needed to modify the page until an insert, update, delete, or lock is performed on a tuple on that page. The PD_ALL_VISIBLE bit in the page header has the same meaning as the "all-visible" bit in the visibility map, but it is stored in the data page itself rather than in a separate data structure. These two bits are normally consistent with each other, but sometimes after crash recovery, the page's "all-visible" bit may be set while the visibility map bit is cleared. The reported values may also be inconsistent due to modifications that occur between when pg_visibility checks the visibility map and when it checks the data page. Any event that causes data corruption may also cause these bits to be inconsistent.
Functions that display information about PD_ALL_VISIBLE are much more expensive than those that examine the visibility map, because they must read the relation's data blocks rather than just the (much smaller) visibility map. Similarly, functions that check the relation's data blocks are also expensive.
1. Functions
pg_visibility_map(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean) returns record
Returns the "all-visible" and "all-frozen" bits from the visibility map for the given block of the given relation.
pg_visibility(relation regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns record
Returns the "all-visible" and "all-frozen" bits from the visibility map for the given block of the given relation, plus the PD_ALL_VISIBLE bit of the block.
pg_visibility_map(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean) returns setof record
Returns the "all-visible" and "all-frozen" bits from the visibility map for each block of the given relation.
pg_visibility(relation regclass, blkno OUT bigint, all_visible OUT boolean, all_frozen OUT boolean, pd_all_visible OUT boolean) returns setof record
Returns the "all-visible" and "all-frozen" bits from the visibility map for each block of the given relation, plus the PD_ALL_VISIBLE bit for each block.
pg_visibility_map_summary(relation regclass, all_visible OUT bigint, all_frozen OUT bigint) returns record
Returns the number of "all-visible" pages and "all-frozen" pages in the relation according to the visibility map.
pg_check_frozen(relation regclass, t_ctid OUT tid) returns setof tid
Returns the TIDs of non-frozen tuples stored in pages marked as "all-frozen" in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupted.
pg_check_visible(relation regclass, t_ctid OUT tid) returns setof tid
Returns the TIDs of non-all-visible tuples stored in pages marked as all-visible in the visibility map. If this function returns a non-empty set of TIDs, the visibility map is corrupted.
pg_truncate_visibility_map(relation regclass) returns void
Truncates the visibility map for the given relation. This function is useful if you believe the visibility map for the relation is corrupted and want to force its rebuild. After this function is executed, the first VACUUM on the given relation will scan every page in the relation and rebuild the visibility map. (Until then, queries will see the visibility map as containing all zeros.)
By default, these functions can only be executed by superusers and members of the pg_stat_scan_tables role, except for pg_truncate_visibility_map(relation regclass) which can only be executed by superusers.