0

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?

1

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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