agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Oleg Gurev <gurevoleg@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Autovacuum and vacuum spoil reltuples statistics on nontruncated relation
Date: Mon, 17 Aug 2026 13:13:01 +0300
Message-ID: <2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com> (raw)
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#b8...
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.
Message-ID: <2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com>
Permalink: ../2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com/
Also on: postgresql.org/message-id/2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com
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: pgsql-bugs@postgresql.org
Cc: gurevoleg@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Autovacuum and vacuum spoil reltuples statistics on nontruncated relation
In-Reply-To: <2bbe2557-83a1-443e-bde8-a3c25c6ac29a@gmail.com>
* 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