I was fiddling with VACUUM and noticed some unexpected behavior where SELECT ing rows from a table seems to reduce the work VACUUM has to do afterwards.

Test Data

Note: autovacuum is disabled

CREATE TABLE numbers (num bigint); ALTER TABLE numbers SET ( autovacuum_enabled = 'f', toast.autovacuum_enabled = 'f' ); INSERT INTO numbers SELECT generate_series(1, 5000);

Trial 1

Now we run an update on all the rows,

UPDATE numbers SET num = 0;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO: vacuuming "public.numbers" INFO: "numbers": removed 5000 row versions in 23 pages INFO: "numbers": found 5000 removable, 5000 nonremovable row versions in 45 out of 45 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 6585 There were 0 unused item pointers.

Trial 2

Now we issue another UPDATE , but this time we add a SELECT afterward,

UPDATE numbers SET num = 1; SELECT * FROM numbers;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO: vacuuming "public.numbers" INFO: "numbers": removed 56 row versions in 22 pages INFO: "numbers": found 56 removable, 5000 nonremovable row versions in 45 out of 45 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 6586 There were 56 unused item pointers.

What exactly is happening here? Why does the second version I run, after the SELECT remove dead tuples from the pages it visits, quite like VACUUM does?

I am running Postgres 11.3 on macOS 10.14.5.