Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgres 9.6 feature highlight - pg_visibility

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)

Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart

7 Steps to configure BDR replication in postgresql

Top 20 Highest Paying URL Shortener Companies - Best URL Shorteners To Earn Fast