| 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.
| 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 |