Postgresql – Partial vacuum and whole table vacuum

postgresqlvacuum

From postgresql document, I know vacuum can skip pages that are already marked in visibility map, so vacuum progress only vacuum pages which requires vacuum,and we call this partival vacuum; Sometimes all pages of a table happen to require vacuuming to remove dead row version, we call this whole table vacuum ( not vacuum full ), My question is: How can we see if a vacuum is a partial vacuum or whole table vacuum, the following is the output of vacuum verbose commond, can we judge from this ?

--vacuum verbose 
mydb=> vacuum verbose test_vacuum2;
INFO:  vacuuming "mydb.test_vacuum2"
INFO:  "test_vacuum2": found 0 removable, 774 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 226 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Best Answer

I don't think that you can tell from the "verbose" listing whether any pages were skipped because they were known to contain only visible tuples.

I think that it will only vacuum pages flagged as "all visible" if it decides to freeze tuples in the table, either because of approaching transaction wraparound or because you explicitly ran VACUUM with the FREEZE option. (If you use the vacuumdb command-line executable, this is the -F option.)

In our shop we supplement the autovacuum activity with a scheduled VACUUM FREEZE ANALYZE on the database during non-peak hours. That minimizes the overhead of autovacuum during peak hours, and slightly improves query performance, because visibility checks are simpler.

Related Question