Does SELECT remove dead rows like VACUUM does?
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
, andSELECT
can trigger space reclamation, but often not duringINSERT ... 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.