Planned lessonMID-014-L05

Demo: an idle transaction blocking vacuum

On This Lesson 3 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

create bloat with an idle transaction and fix it.

Draft outline details

Create a hot table stock_log and a loop that updates rows continuously; show baseline size and n_dead_tup.
In another session run BEGIN; SELECT count(*) FROM stock_log; and leave it open.
Run VACUUM (VERBOSE) stock_log; it reports dead rows "not yet removable".
Planned mistake: disable autovacuum to "fix" growth; bloat gets worse. Fix: re-enable it.
Terminate the idle session (or set idle_in_transaction_session_timeout) and vacuum again; dead tuples drop.
Note table size stays large (reusable space, not returned to the OS).

Prepares MID-014-LAB1.

Which message in VACUUM VERBOSE reveals the culprit?

Show answer

Dead row versions "not yet removable" because of an old transaction.

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.