Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
thanks, but is there any way to tell if it's a partial vacuum or whoule table vaccum? –  francs May 14 '12 at 6:08
    
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. –  kgrittn May 14 '12 at 8:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.