> Possibly there’s something more unusual happening, because if you run an update at the
> *exact* autovacuum_vacuum_threshold, and then shortly after run the same number of
> updates again, the autovacuum log only shows that one set of tuples removed. Shouldn’t
> it be A + B, as two updates have run? (so “tuples: 1000 removed” instead of the
> “tuples: 500 removed” shown below)
Even stranger, is that if you do an update of exactly the threshold, wait, and then do a *SINGLE* row update:
postgres=# VACUUM FULL autovacuum_threshold_test;
VACUUM
postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id <= 500;
UPDATE 500
postgres=# SELECT pg_sleep(30);
pg_sleep
----------
(1 row)
postgres=# UPDATE autovacuum_threshold_test SET id = id WHERE id = 501;
UPDATE 1
The resulting autovacuum ran but only removed 1 row, even though the autovacuum_vaccum_threshold is set at 500:
< 2018-02-14 15:20:08.512 GMT > LOG: automatic vacuum of table "postgres.public.autovacuum_threshold_test": index
scans:0
pages: 0 removed, 7 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 1 removed, 1000 remain, 0 are dead but not yet removable
buffer usage: 37 hits, 2 misses, 4 dirtied
avg read rate: 18.426 MB/s, avg write rate: 36.851 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
Greg Clough
Senior Technology Engineer
Ipreo
Castle House | 37-35 Paul St | London EC2A 4LS