VACUUM Reclaims Dead Rows, autovacuum Does It for You, and VACUUM FULL Rewrites the Table
VACUUM walks a table and marks the space held by dead row versions as reusable, so future inserts fill the gaps instead of extending the file; autovacuum is the background process that does this automatically when enough of a table has changed. ANALYZE is the separate job, usually run alongside, that resamples the table so the planner's row counts are current. VACUUM FULL is a different and far heavier thing: it rewrites the whole table to give space back to the operating system and takes a lock that blocks everyone, so it is a maintenance-window tool, not a routine fix.
Questions this Concept answers
Why is `VACUUM FULL` a maintenance-window tool rather than a routine fix for a slow table?
J
jeremy
Video
How VACUUM Reclaims Dead Tuple Space in PostgreSQL
Under PostgreSQL's multiversion concurrency control a DELETE only marks a row version dead and an UPDATE is implemented as a delete plus an insert, so obsolete versions accumulate in the data files a…