VACUUM/ANALYZE double-count the backend's own pending table stats (n_live_tup = 2*N after INSERT + ANALYZE)
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254197psql -h localhost -U postgresThis image is from patchset v1 (message #1) - the current patchset v2 (message #2) has not produced an image.
Hi,
VACUUM and ANALYZE overwrite live_tuples/dead_tuples in the shared stats
entry with their own counts. Those counts already include the effects
of transactions this backend committed earlier. But the matching deltas
can still be pending in the backend's local stats, because pending
stats are flushed at most once per PGSTAT_MIN_INTERVAL. They are then
added again on top of the new values at the next flush.
I know these counters are only approximate when other sessions modify
the table concurrently. This case is different: it happens in a single
session, it is deterministic, and the backend has exact information
about its own pending counts.
Reproduced on 18.6 and on master (8b9ffcb11), in autocommit mode:
CREATE TABLE t (id int);
INSERT INTO t SELECT generate_series(1, 1000);
ANALYZE t; -- or VACUUM t;
SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 't';
n_live_tup | n_dead_tup | n_mod_since_analyze
------------+------------+---------------------
2000 | 0 | 1000
With a DELETE, the result goes the other way:
-- t has 1000 rows, stats flushed
DELETE FROM t WHERE id <= 500;
ANALYZE t;
SELECT pg_stat_force_next_flush();
-- n_live_tup = 0, n_dead_tup = 500 (count(*) = 500)
-- same, but VACUUM instead of ANALYZE:
-- n_live_tup = 0, n_dead_tup = 500 (the dead tuples were removed)
pg_class.reltuples is correct in all these cases. Only the cumulative
stats are wrong, and they stay wrong until the next VACUUM/ANALYZE.
When the INSERT and ANALYZE run in a single explicit transaction, the
result is correct, because pgstat_report_analyze() subtracts the counts
of the in-progress transaction (rel->pgstat_info->trans) precisely to
avoid double counting. It doesn't do the same for already-committed but
still-pending deltas. It does subtract the pending delta_dead_tuples
("count stuff inserted by already-aborted subxacts, too"), but not
delta_live_tuples. pgstat_report_vacuum() doesn't look at pending stats
at all.
Why it matters: pg_class.reltuples is fine, but the counters that
autovacuum actually uses are inflated:
- After INSERT + ANALYZE, mod_since_analyze is N right after the
ANALYZE, which brings the next autoanalyze forward.
- After DELETE + VACUUM, dead_tuples is reported as N although
VACUUM has just removed them, which can trigger an extra
autovacuum.
n_live_tup is also what monitoring and bloat estimation queries
usually read. The pattern "bulk load / bulk delete, then ANALYZE or
VACUUM in the same session" is common in ETL scripts.
A related problem is in the same file. AtEOXact_PgStat_Relations()
does
relstat->tab.truncdropped = trans->truncdropped;
on every commit. So if one transaction truncates a table and a later
transaction of the same backend modifies it before the pending stats
are flushed, the pending truncation reset is lost. No VACUUM or ANALYZE
is needed for this one:
-- t has 1000 rows, stats flushed
TRUNCATE t;
INSERT INTO t SELECT generate_series(1, 300);
SELECT pg_stat_force_next_flush();
-- n_live_tup = 1300 (count(*) = 300)
pgstat_twophase_postcommit() does the same.
The attached patch for master:
- Makes VACUUM and ANALYZE forget the pending delta_live_tuples and
delta_dead_tuples of the relation, because their new totals
supersede them. With ANALYZE's resetcounter, it also forgets the
pending changed_tuples. A pending truncation reset is superseded as
well. For ANALYZE, its reset of ins_since_vacuum is applied right
away. The old subtraction of pending delta_dead_tuples in ANALYZE
goes away, because the new code covers it. Forgetting the deltas
is better than subtracting them, because subtracting interacts
badly with the clamping to 0. For example, after DELETE + VACUUM,
dead_tuples would be clamped to 0 and then the pending deltas
would be added back anyway.
- Only ever sets truncdropped at commit and COMMIT PREPARED, never
clears it.
- Adds a regression test to stats.sql. The test fails without the
fix (n_live_tup 12/13/20 instead of 6/3/10). It passes with the
fix whether or not the stats happen to get flushed between
statements.
One thing this patch doesn't fix: after INSERT + VACUUM in the same
session, ins_since_vacuum is still N, because the flush adds the
pending tuples_inserted to it. That counter also feeds n_tup_ins, so
fixing it would need a separate field. I left it alone.
The patch conflicts with the "pgstat: Flush some statistics within
running transactions, take 2" patch (v17). That patch doesn't change
either behavior, but with its flushed_xact baselines, "forget the
pending deltas" would presumably have to mean "advance the baseline"
rather than "zero the counters". I can rebase in whichever order is preferred.
--
Ilya Staroverov
Hi Ilya,
I reproduced v1 and stress-tested it: it holds up. 8000 randomized
single-session sequences (lazy vs eager flush as the oracle), plus
2PC, the truncdropped paths and post-snapshot inserts all match
count(*) with the patch. Nice work.
One thing, which is the corner you already called out:
One thing this patch doesn't fix: after INSERT + VACUUM in the same
session, ins_since_vacuum is still N, because the flush adds the
pending tuples_inserted to it.
It is the same double count your patch fixes for live/dead, in the same
function, and it does fire a spurious insert-triggered autovacuum right
after a manual VACUUM. Minimal:
INSERT 1000; VACUUM t; SELECT pg_stat_force_next_flush();
-- n_ins_since_vacuum = 1000, should be 0
The attached delta sits on top of yours, untouched; it applies clean
(+18/-8) and closes it: a pending ins_before_vacuum field, appended at
the end of the struct so the inline pgstat_count_* offsets do not move
(keeps a back-branch fix viable). pgstat_report_vacuum() records what it
already covered; the flush adds only the difference, so tuples_inserted
and n_tup_ins stay cumulative. make check is green and the oracle drops
to zero failures (minus TRUNCATE, below). Fold it into v2 if you like,
or I can post it as a separate 0002.
Two pre-existing things the oracle flagged that predate your patch (same
on unpatched master, so not v1's doing): pending inserts before a
TRUNCATE, and n_dead_tup after a subxact rolled back past a TRUNCATE.
Both look like they need the truncdrop save/restore across
subtransactions. Happy to send the repros separately if useful.
Regards,
Manu
Attachments:
ins-since-vacuum.difftext/x-patchDownload+18-7
Hi Manu, Ilya,
On Wed, 14 Oct 2026 at 15:47, Manu <manuelreyesbravo@gmail.com> wrote:
The attached delta sits on top of yours, untouched; it applies clean
(+18/-8) and closes it
Confirmed here. master at d733fcaf5ca, meson debug build with cassert,
Debian trixie. v1 alone and v1 + your delta both apply, build without
new warnings and pass the full suite (meson test). The stats test
passes with each patch and fails on unpatched master.
I also measured what the double count costs autovacuum. The attached
script needs only psql and a scratch server with
autovacuum_naptime=1s. It runs three workloads on 20 tables each,
with each cycle in its own short session: insert + ANALYZE,
delete + VACUUM and insert + VACUUM. Each workload has a control that
lets the pending stats flush before the ANALYZE or VACUUM. The table
shows extra autoanalyze runs for the first workload and extra
autovacuum runs for the other two, per 20 tables:
control unpatched v1 v1 + delta
insert + ANALYZE 0 20 0 0
delete + VACUUM 0 20 0 0
insert + VACUUM 0 20 20 0
The unpatched column is the same on the official 14.24, 15.19, 16.15,
17.11 and 18.6 images. 14 still has the stats collector, so this
predates shared-memory stats. The controls stay at 0 on every build.
On whether this is just an approximation: 81e82a2bd48 fixed the same
class of problem in 2016 (deltas of an earlier transaction in the
same backend not sent yet, for TRUNCATE in a prepared transaction)
and called it an evident oversight.
One thing about the test, from the same commit message: a regression
test for d42358efb was reverted because it was too timing-sensitive
for the buildfarm. The new test passes with the fix whatever the
flush timing, so it should not be flaky. But if a flush slips in
between statements on a slow animal, it also passes without the fix,
so it may not catch a regression there.
Disclosure: I used Claude Opus 5.5 (reasoning high) for the analysis
and tests. Codex gpt-6.1-sol (reasoning high) checked independently
whether this could be by design. I reviewed the results.
Regards,
Sebastian
Em qua., 14 de out. de 2026 às 15:47, Manu <manuelreyesbravo@gmail.com>
escreveu:
Hi Ilya,
I reproduced v1 and stress-tested it: it holds up. 8000 randomized
single-session sequences (lazy vs eager flush as the oracle), plus
2PC, the truncdropped paths and post-snapshot inserts all match
count(*) with the patch. Nice work.One thing, which is the corner you already called out:
One thing this patch doesn't fix: after INSERT + VACUUM in the same
session, ins_since_vacuum is still N, because the flush adds the
pending tuples_inserted to it.It is the same double count your patch fixes for live/dead, in the same
function, and it does fire a spurious insert-triggered autovacuum right
after a manual VACUUM. Minimal:INSERT 1000; VACUUM t; SELECT pg_stat_force_next_flush();
-- n_ins_since_vacuum = 1000, should be 0The attached delta sits on top of yours, untouched; it applies clean
(+18/-8) and closes it: a pending ins_before_vacuum field, appended at
the end of the struct so the inline pgstat_count_* offsets do not move
(keeps a back-branch fix viable). pgstat_report_vacuum() records what it
already covered; the flush adds only the difference, so tuples_inserted
and n_tup_ins stay cumulative. make check is green and the oracle drops
to zero failures (minus TRUNCATE, below). Fold it into v2 if you like,
or I can post it as a separate 0002.Two pre-existing things the oracle flagged that predate your patch (same
on unpatched master, so not v1's doing): pending inserts before a
TRUNCATE, and n_dead_tup after a subxact rolled back past a TRUNCATE.
Both look like they need the truncdrop save/restore across
subtransactions. Happy to send the repros separately if useful.Regards,
Manu
--
Sebastian Webber
Attachments:
pending-stats-autovacuum.shtext/x-shellscript; charset=US-ASCII; name=pending-stats-autovacuum.shDownload
Hi Sebastian,
if a flush slips in between statements on a slow animal, it also
passes without the fix, so it may not catch a regression there.
Good point. Pinning the flush in the test so it cannot pass without
the fix is easy to add; I will leave that for Ilya's v2 unless he
would rather I post it.
Thanks also for the independent confirmation and the autovacuum
numbers, and for digging up 81e82a2bd48 as a precedent. That settles
the "just an approximation" question.
Regards,
Manu