What happens during auto vacuum / vacuum in PostgreSQL?

Question

The official documentaion here seemed quite cryptic to me. https://www.postgresql.org/docs/13/routine-vacuuming.html#AUTOVACUUM

Can someone offer an explanation of what happens under the hood during autovacuum or manually triggerred vacuum?

Answer 1

The autovacuum launcher process wakes up regularly and determines – based on the statistical data in pg_stat_all_tables and pg_class and certain parameters settings – if a table needs to be VACUUMed or ANALYZEd. If yes, it starts an autovacuum worker process that performs the required operation.

VACUUM does a lot of things. The basic procedure is:

• scan the table for dead row versions

• scan all indexes and remove references to these dead row versions

• scan the table again to remove the dead row versions found in the first step

• if we are not done with the table yet, repeat the whole process

On the side lines, it also does these things:

• update table statistics in pg_class

• bring the “free space map” up to date, so that INSERTs and UPDATEs know where to find a block with enough free space

• bring the “visibility map” up to date, so that PostgreSQL can perform efficient index-only scans

• “freeze” all live rows older than a certain age to allow the transaction ID counter to wrap around without data loss