public inbox for [email protected]  

help / color / mirror / Atom feed

From: Frédéric Yhuel <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Christophe Courtois <[email protected]>
Subject: n_dead_tup could be way off just after a vacuum
Date: Tue, 10 Mar 2026 18:24:18 +0100
Message-ID: <[email protected]> (raw)

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.

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: n_dead_tup could be way off just after a vacuum
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox