In PostgreSQL 9.3.19 log I see the following two consecutive entries for autovacuum of a given table:
2018-06-29 17:24:06 CDT 13177 14/870454 0 LOG: automatic vacuum of table "openbravo.public.ad_session_status": index scans: 0
pages: 0 removed, 235 remain
tuples: 0 removed, 14669 remain
buffer usage: 1090 hits, 673 misses, 4 dirtied
avg read rate: 6.745 MB/s, avg write rate: 0.040 MB/s
--
2018-06-29 17:24:55 CDT 13529 40/699086 0 LOG: automatic vacuum of table "openbravo.public.ad_session_status": index scans: 0
pages: 0 removed, 235 remain
tuples: 0 removed, 13039 remain
buffer usage: 1143 hits, 663 misses, 0 dirtied
avg read rate: 3.086 MB/s, avg write rate: 0.000 MB/s
All autovacuums are logged: log_autovacuum_min_duration=0.
There are no other manual vacuums in-between.
How can it be that if none of these two vacuums is removing any dead tuple, the remaining number of tuples decreases after 2nd one? Has PostgreSQL other ways to remove dead rows?
One explanation might be HOT updates.
If there is room in the table block and no updated column is indexed, PostgreSQL will place the new row version in the same block as the original and create a “HOT chain” where the old row version points to the new one. This allows PostgreSQL to skip updating all the indexes that point to this table row.
Apart from reducing I/O during UPDATE, HOT also allows removing old row versions “on the fly”: whenever a page is visited that is almost full and the requisite lock on it can be obtained, PostgreSQL will perform a “micro-vacuum” on that block by reorganizing it.
This is what may cause the observed reduction in tuples.
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 = 'ad_session_status';
If n_tup_hot_upd is greater than zero, we have got a case.