Message-ID: <2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com>
Date: Mon, 17 Aug 2026 13:13:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
From: Oleg Gurev <gurevoleg@gmail.com>
Subject: Autovacuum and vacuum spoil reltuples statistics on nontruncated
 relation
To: pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/2bbe2557-83a1-443e-bde8-a3c25c6ac29a%40gmail.com>
Precedence: bulk

Hello.
Looks like I've got similar bug as was described in this thread:
https://www.postgresql.org/message-id/flat/3dec196d-72a6-447f-ad2e-f2668f2907a9%40oss.nttdata.com#b83f0d78023e8980ec4ac0e5568b9f9d

Autovacuum continue to spoil reltuple statistics when vacuum_truncate is 
off and long transaction is going.

==============================================================
Reproduction:
Start cluster with GUC "vacuum_truncate = off". (since REL_18)
==============================================================
Sesson 1:
CREATE TABLE t AS SELECT i FROM generate_series(1, 50000) i;
begin;
SELECT txid_current();
==============================================================
Session 2:
DELETE from t WHERE i > 40000;
SELECT
         to_char(now(), 'HH24:MI:SS'),
         c.relname,
         c.relpages,
         c.reltuples,
         st.n_live_tup,
         st.autovacuum_count,
         st.autoanalyze_count,
         age(c.relfrozenxid)
FROM    pg_class c,
         pg_stat_all_tables st
WHERE c.oid = st.relid
AND   c.relname = 't'
\watch 1
==============================================================
reltuples and reltuples will start to decrease.

My investigation led me to vac_estimate_reltuples() function and page 
density calculation. Here we rely on uniform distribution of tuples over 
all pages. But without truncation this led us to mistake on counting new 
tuples count.
If we don't run long transaction - autovacuum perform one shot on 't' 
doing vacuum and analyze. So it helps to see correct statistics unless 
we run vacuum t; manually...

The key issue is than vac_estimate_reltuples() rely on total pages 
number. And pages counted by deviding relation file size by BLOCKSZ.

I didn't found any "cheap" solution on this case. Probably, someone can 
help.

workaround:
vacuum analyze t; can fixup statisctics and tell autovacuum don't touch 
this relation. But any manual "vacuum t;" will break statisctics again.
vacuum full t; solve the problem by the cost of truncation.