Autovacuum and vacuum spoil reltuples statistics on nontruncated relation

From: Oleg Gurev <gurevoleg(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Autovacuum and vacuum spoil reltuples statistics on nontruncated relation
Date: 2026-08-17 10:13:01
Message-ID: 2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexander Korotkov 2026-08-17 10:27:24 Re: MERGE/SPLIT PARTITIONS issues/questions
Previous Message PG Bug reporting form 2026-08-17 04:13:01 BUG #19623: Postmaster livelocks respawning io workers when children die after crash restart; pg_ctl stop fails