Postgresql – What are the advanvates and disadvantages of vacuum in postgresql

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.

1) I would have tried cluster followed by analyze. My only hesitation is I am not 100% sure what happened here. Is it possible there was some index corruption as well? Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID?

2) I have no idea. I have never seen anything like that before. I would need a lot more diagnostic information even to hazard a guess. If it happens again it would be worth knowing the physical size of the table, for example.

Edit: Remembering that 8.4 still had max_fsm_pages, if that was set too low between vacuums that could cause this sort of table bloat.

Edit2: Vacuum full, however should have been able to handle bloat due to max_fsm_pages, at least it does in my experience, suggesting again to me that something deeper was wrong.