Planned lessonMID-014-L03

Vacuum and autovacuum

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

explain what vacuum does and why to keep autovacuum on.

An UPDATE writes a new row version and marks the old one dead; DELETE marks it dead.
VACUUM marks dead versions reusable, updates the visibility map, and freezes old transaction ids.
Autovacuum runs it automatically when enough rows change; tune per table for hot tables.
Plain VACUUM does not shrink the file; VACUUM FULL does but takes an exclusive lock.
Never disable autovacuum; fix its settings instead.

Draft outline details

SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

Why does an UPDATE need vacuum later?

Show answer

It leaves the old row version behind as a dead tuple.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.