My colleague Christophe Courtois and I came across some surprising
behavior. After executing the following script, n_live_tup and
n_dead_tup are way off. We know they are not meant to be exact, but the
table will be autovacuumed for no reason afterwards.
DROP TABLE IF EXISTS foo ;
CREATE TABLE foo (id int);
INSERT INTO foo (id) SELECT * FROM generate_series(1,1000);
DELETE FROM foo WHERE id <= 500;
VACUUM foo ;
SELECT pg_sleep(1);
SELECT relname, n_tup_ins, n_tup_del,
n_live_tup, n_dead_tup,
last_autovacuum, last_autoanalyze,
last_vacuum, last_analyze
FROM pg_stat_user_tables WHERE relname='foo' \gx
-[ RECORD 1 ]----+------------------------------
relname | foo
n_tup_ins | 1000
n_tup_del | 500
n_live_tup | 1000 <--- should be 500 after a VACUUM
n_dead_tup | 500 <--- should be 0 after a VACUUM
last_autovacuum | ∅
last_autoanalyze | ∅
last_vacuum | 2026-03-10 18:11:09.893913+01
last_analyze | ∅
This issue appears on all tested versions from 9.6 to 18.
If we sleep one second between the DELETE and the VACUUM, the problem
disappears, because (IIUC) pgstat_relation_flush_cb() gets executed
before the VACUUM.
I wonder if this is a known issue, and if pg_stat_report_vacuum() should
clear the pending stats, or something.
