PostgreSQL: VACUUM VERBOSE to find data pages and dead row versions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

If you don’t know about that data pages, you must visit below article.
What is MVCC ?

Because of MVCC architecture, PostgreSQL generates dead rows at every UPDATE and DELETE.

You must remove the fragmentation by executing VACUUM ANALYZE command which removes the dead rows and update the related database statistics.

You can also use VACUUM VERBOSE to troubleshoot total number of data pages, total number of removable dead row version while executing a VACUUM ANALYZE.
(Check your query message window, where you can find detailed information generated by VACUUM VERBOSE.)

This is really very helpful information for PostgreSQL DBA to make sure about the fragmentation.

I have prepared a detailed demonstration of this.

Create a table with sample data:

Check the size of table:

Execute VACUUM VERBOSE to get data pages and dead row versions related information:

Execute one DELETE to generate few dead tuples:

Now, examine the result of VACUUM VERBOSE where you can find 527040 removable rows:
You can find total 3970 pages and 527040 removable rows.

Now, execute one more time and you can find 3132 pages and 0 removable rows:

Check the size of table: