| From: | Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Christophe Courtois <christophe(dot)courtois(at)dalibo(dot)com> |
| Subject: | n_dead_tup could be way off just after a vacuum |
| Date: | 2026-03-10 17:24:18 |
| Message-ID: | df75c0f4-cc2f-4d3e-98e3-61859aab7368@dalibo.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Kirill Reshke | 2026-03-10 17:33:30 | Re: SQL:2011 Application Time Update & Delete |
| Previous Message | Andrew Dunstan | 2026-03-10 17:18:42 | Re: support fast default for domain with constraints |