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_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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2