n_dead_tup could be way off just after a vacuum

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.

Browse pgsql-hackers by date

  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