What happens during auto vacuum / vacuum in PostgreSQL?
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?
The autovacuum launcher process wakes up regularly and determines – based on the statistical data in
pg_classand certain parameters settings – if a table needs to be
ANALYZEd. If yes, it starts an autovacuum worker process that performs the required operation.
VACUUMdoes 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
bring the “free space map” up to date, so that
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