I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing 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);
  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.

  • 2
    What client do you use to run your commands? Is autocommit enabled in it? – mustaccio Jun 5 at 18:20
  • 2
    I'm going to delete the question "Is VACUUM table basically just SELECT * FROM table under the hood?" (it's not) I think that's a good follow up, the answer here is simply that SELECT can remove dead rows, and it does share that in common with VACUUM. How they're different will be a very exhaustive conversation about XID rollover, and a ton of other things. That question is basically "What other things does vacuum do besides remove dead rows." (Which would be kind of vague) – Evan Carroll Jun 5 at 18:50
  • @mustaccio I did these tests with a Ruby script using ActiveRecord, which uses the PG gem under the hood. I believe autocommit is enabled by default as you don't need to issue any COMMIT unless BEGIN is used explicitly. – rafbm Jun 6 at 14:46

From this post on /r/PostgreSQL to an answer by Laurenz Albe it seems that Heap Only Tuples (HOT) updates may be responsible. From the description of HOT updates in src/backend/access/heap/README.HOT

Effectively, space reclamation happens during tuple retrieval when the page is nearly full (<10% free) and a buffer cleanup lock can be acquired. This means that UPDATE, DELETE, and SELECT can trigger space reclamation, but often not during INSERT ... VALUES because it does not retrieve a row.

The quote is not in the original answer, but the rest is a quote,

To support or refute this theory, run the following query:

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'TABLE_NAME';

If n_tup_hot_upd is greater than zero, we have got a case.

  • Now we're talking. +1 – mustaccio Jun 6 at 13:24
  • HOT seems to be a good explanation. If I CREATE INDEX idx_numbers ON numbers USING btree (num), VACUUM output changes to INFO: "numbers": removed 5000 row versions in 45 pages. Note however that in the index-less scenario, n_tup_hot_upd is always 0, both between the UPDATE and SELECT and between the SELECT and VACUUM. I also made sure to run SELECT pg_sleep(10) between each statement so that statistics are up-to-date (I do see seq_scan: 2, one for the UPDATE and one for the SELECT). – rafbm Jun 6 at 15:27
  • Does the select generate WAL in this case? I was under the impression that selects don’t generate WAL at all. If yes, this would mean the removal of dead rows gets propagated to any slaves. If no, this means that vacuuming is still necessary on the slave. It would also mean that masters and slaves aren’t bit identical. Hmm, maybe I need to do some research and post a question and/or answer or two. – Colin 't Hart Jun 6 at 15:59

In the special case of an unindexed table, yes, SELECT can do the same work as VACUUM (as far as removing dead rows is concerned).

  • 3
    Could you add an explanation? – Laurenz Albe Jun 5 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.