pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACUUM/ANALYZE double-count the backend's own pending table stats (n_live_tup = 2*N after INSERT + ANALYZE)
15+ messages / 5 participants
[nested] [flat]

* VACUUM/ANALYZE double-count the backend's own pending table stats (n_live_tup = 2*N after INSERT + ANALYZE)
@ 2026-10-14 10:39  Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 2 replies; 15+ messages in thread

From: Staroverov Ilja @ 2026-10-14 10:39 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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



Attachments:

v1-0001-Fix-double-counting-of-pending-relation-stats-by-.patch (application/octet-stream, 9.8K) download From 569e2838791a6e634b6a99089e8c2455e6124617 Mon Sep 17 00:00:00 2001 From: Staroverov Ilja <iljastarov@gmail.com> Date: Wed, 14 Oct 2026 12:44:24 +0300 Subject: [PATCH v1] Fix double-counting of pending relation stats by VACUUM and ANALYZE VACUUM and ANALYZE overwrite live_tuples and dead_tuples of the shared stats entry with their own counts. Those counts already include the effects of transactions committed earlier by the same backend, but the corresponding deltas could still be pending in the backend's local stats (pending stats are flushed at most once per PGSTAT_MIN_INTERVAL), and were then added again on top of the new values at the next flush. For example, INSERT of N rows immediately followed by ANALYZE or VACUUM in autocommit mode left n_live_tup at 2*N, and DELETE followed by ANALYZE could leave it at 0. ANALYZE also reset mod_since_analyze only for the pending changed_tuples to be added right back. ANALYZE did already discount pending delta_dead_tuples, but not delta_live_tuples. Fix by forgetting the pending live/dead deltas (and, for ANALYZE with resetcounter, the pending changed_tuples) when VACUUM or ANALYZE report new values. A pending truncation reset of live/dead counters is superseded as well; for ANALYZE its reset of ins_since_vacuum is applied right away. Also, the commit of a transaction that did not truncate a relation cleared the pending truncdropped flag left by an earlier, not yet flushed TRUNCATE of the same backend, so that e.g. TRUNCATE followed by an INSERT of M rows left n_live_tup at (old count + M). Only ever set the flag at commit, never clear it; same for COMMIT PREPARED. --- src/backend/utils/activity/pgstat_relation.c | 52 +++++++++++++++++--- src/test/regress/expected/stats.out | 41 +++++++++++++++ src/test/regress/sql/stats.sql | 25 ++++++++++ 3 files changed, 112 insertions(+), 6 deletions(-) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index 5c70543ba..cfebd0cd6 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -273,6 +273,23 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, if (!pgstat_track_counts) return; + /* + * The live and dead tuple counts computed by VACUUM already include the + * effects of transactions previously committed by this backend whose + * stats have not been flushed yet. Forget the pending deltas for those + * counters, else they would be added on top of the new values at the + * next flush. A pending truncation reset is superseded as well. + */ + if (pgstat_should_count_relation(rel)) + { + PgStat_RelationStatus *pending = rel->pgstat_info; + + Assert(pending->kind == PGSTAT_KIND_RELATION); + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + pending->tab.truncdropped = false; + } + /* Store the data in the table's hash table entry. */ ts = GetCurrentTimestamp(); elapsedtime = TimestampDifferenceMilliseconds(starttime, ts); @@ -341,6 +358,7 @@ pgstat_report_analyze(Relation rel, Oid dboid = (rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId); TimestampTz ts; PgStat_Counter elapsedtime; + bool truncdropped = false; if (!pgstat_track_counts) return; @@ -360,19 +378,34 @@ pgstat_report_analyze(Relation rel, if (pgstat_should_count_relation(rel) && rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) { + PgStat_RelationStatus *pending = rel->pgstat_info; PgStat_TableXactStatus *trans; - Assert(rel->pgstat_info->kind == PGSTAT_KIND_RELATION); - for (trans = rel->pgstat_info->tab.trans; trans; trans = trans->upper) + Assert(pending->kind == PGSTAT_KIND_RELATION); + for (trans = pending->tab.trans; trans; trans = trans->upper) { livetuples -= trans->tuples_inserted - trans->tuples_deleted; deadtuples -= trans->tuples_updated + trans->tuples_deleted; } - /* count stuff inserted by already-aborted subxacts, too */ - deadtuples -= rel->pgstat_info->tab.counts_xact.delta_dead_tuples; /* Since ANALYZE's counts are estimates, we could have underflowed */ livetuples = Max(livetuples, 0); deadtuples = Max(deadtuples, 0); + + /* + * Likewise, ANALYZE has already counted the effects of transactions + * previously committed by this backend (and of already-aborted + * subxacts) whose stats have not been flushed yet. Forget the + * pending deltas for those, else they would be added on top of the + * new values at the next flush. A pending truncation reset of the + * live/dead counters is superseded too, but we still have to apply + * its reset of ins_since_vacuum below. + */ + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + if (resetcounter) + pending->tab.counts_xact.changed_tuples = 0; + truncdropped = pending->tab.truncdropped; + pending->tab.truncdropped = false; } /* Store the data in the table's hash table entry. */ @@ -391,6 +424,8 @@ pgstat_report_analyze(Relation rel, tabentry->live_tuples = livetuples; tabentry->dead_tuples = deadtuples; + if (truncdropped) + tabentry->ins_since_vacuum = 0; /* * If commanded, reset mod_since_analyze to zero. This forgets any @@ -641,9 +676,13 @@ AtEOXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool isCommit) relstat->tab.counts_xact.tuples_deleted += trans->tuples_deleted; if (isCommit) { - relstat->tab.truncdropped = trans->truncdropped; + /* + * Don't clear a truncation still pending from an earlier + * transaction whose stats have not been flushed yet. + */ if (trans->truncdropped) { + relstat->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ relstat->tab.counts_xact.delta_live_tuples = 0; relstat->tab.counts_xact.delta_dead_tuples = 0; @@ -829,9 +868,10 @@ pgstat_twophase_postcommit(FullTransactionId fxid, uint16 info, pgstat_info->tab.counts_xact.tuples_inserted += rec->tuples_inserted; pgstat_info->tab.counts_xact.tuples_updated += rec->tuples_updated; pgstat_info->tab.counts_xact.tuples_deleted += rec->tuples_deleted; - pgstat_info->tab.truncdropped = rec->truncdropped; + /* see AtEOXact_PgStat_Relations() */ if (rec->truncdropped) { + pgstat_info->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ pgstat_info->tab.counts_xact.delta_live_tuples = 0; pgstat_info->tab.counts_xact.delta_dead_tuples = 0; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 8b1547124..5e28db6e5 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -293,6 +293,47 @@ FROM prevstats AS pr; COMMIT; ----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- (If the stats happen to get flushed in between anyway, the results are the +-- same.) +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +TRUNCATE stats_pending_trunc; +INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); +INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); +DELETE FROM stats_pending_analyze WHERE id <= 4; +ANALYZE stats_pending_analyze; +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; + relname | n_live_tup | n_dead_tup | n_mod_since_analyze +-----------------------+------------+------------+--------------------- + stats_pending_analyze | 6 | 4 | 0 + stats_pending_trunc | 3 | 0 | 13 + stats_pending_vacuum | 10 | 0 | 10 +(3 rows) + +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; +----- -- Basic tests for pg_stat_all_indexes ----- CREATE TEMPORARY TABLE test_idx_tup_read AS diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 674637e17..302ff9af0 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -153,6 +153,31 @@ FROM prevstats AS pr; COMMIT; +----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- (If the stats happen to get flushed in between anyway, the results are the +-- same.) +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); +TRUNCATE stats_pending_trunc; +INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); +INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); +DELETE FROM stats_pending_analyze WHERE id <= 4; +ANALYZE stats_pending_analyze; +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; + ----- -- Basic tests for pg_stat_all_indexes ----- -- 2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-14 18:47  Manu <manuelreyesbravo@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  1 sibling, 1 reply; 15+ messages in thread

From: Manu @ 2026-10-14 18:47 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>

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.diff (text/x-patch, 4.3K) download diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index cfebd0cd6dd..9e6559596e7 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -279,6 +279,11 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, * stats have not been flushed yet. Forget the pending deltas for those * counters, else they would be added on top of the new values at the * next flush. A pending truncation reset is superseded as well. + * + * Likewise, the pending inserts must not count towards ins_since_vacuum, + * which is reset below. We can't just zero tuples_inserted, which also + * feeds the cumulative tuples_inserted counter, so remember how many of + * them the flush has to skip. */ if (pgstat_should_count_relation(rel)) { @@ -288,6 +293,8 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, pending->tab.counts_xact.delta_live_tuples = 0; pending->tab.counts_xact.delta_dead_tuples = 0; pending->tab.truncdropped = false; + pending->tab.ins_before_vacuum = + pending->tab.counts_xact.tuples_inserted; } /* Store the data in the table's hash table entry. */ @@ -993,7 +1000,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) * triggering for inserts more often than they maybe should, which is * probably not going to be common enough to be too concerned about here. */ - tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted; + tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted - + lstats->tab.ins_before_vacuum; tabentry->blocks_fetched += lstats->tab.counts.blocks_fetched; tabentry->blocks_hit += lstats->tab.counts.blocks_hit; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 187d82c96fe..6b6a705aba5 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -236,6 +236,8 @@ typedef struct PgStat_RelationStatus struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */ PgStat_TableCounts counts; /* event counts to be sent */ PgStat_TableCountsXact counts_xact; /* transactional counts */ + /* pending tuples_inserted already covered by a VACUUM */ + PgStat_Counter ins_before_vacuum; } tab; /* index counters */ diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 5e28db6e57d..154b99a4c12 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -322,14 +322,14 @@ SELECT pg_stat_force_next_flush(); (1 row) -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; - relname | n_live_tup | n_dead_tup | n_mod_since_analyze ------------------------+------------+------------+--------------------- - stats_pending_analyze | 6 | 4 | 0 - stats_pending_trunc | 3 | 0 | 13 - stats_pending_vacuum | 10 | 0 | 10 + relname | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum +-----------------------+------------+------------+---------------------+-------------------- + stats_pending_analyze | 6 | 4 | 0 | 10 + stats_pending_trunc | 3 | 0 | 13 | 3 + stats_pending_vacuum | 10 | 0 | 10 | 0 (3 rows) DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 302ff9af05c..b7b4570e5a3 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -173,7 +173,7 @@ ANALYZE stats_pending_analyze; INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum;
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-14 20:28  Sebastian Webber <sebastian@swebber.me>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Sebastian Webber @ 2026-10-14 20:28 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Staroverov Ilja <i.staroverov@ftdata.ru>

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


-- 
Sebastian Webber

Attachments:

pending-stats-autovacuum.sh (text/x-shellscript, 5.0K) download #!/bin/sh # Measure extra autovacuum/autoanalyze runs caused by VACUUM/ANALYZE # double-counting the backend's own pending table stats. # # Needs only psql and a scratch server started with autovacuum_naptime=1s, # for example: # # docker run -d --name pg -e POSTGRES_HOST_AUTH_METHOD=trust postgres:18 \ # -c autovacuum_naptime=1s -c log_autovacuum_min_duration=0 # docker cp pending-stats-autovacuum.sh pg:/tmp/ # docker exec -u postgres pg sh /tmp/pending-stats-autovacuum.sh # # Connection settings come from the usual PG* environment variables. The # script creates and drops the database pending_stats_test. # # Each scenario runs on NTAB tables, each in its own short session: # # 1. a warm-up statement, whose idle-time flush sets the backend's last # flush time, so the next changes stay pending (flushes happen at most # once per second); # 2. the data change; # 3. ANALYZE or VACUUM. # # "ctl" scenarios add SELECT pg_sleep(1.5) before step 3, which lets the # pending stats flush first. Expected counters after each cycle, if the # stats were exact: # # insert+analyze mod_since_analyze 0 -> no autoanalyze # delete+vacuum dead_tuples 0 -> no autovacuum # insert+vacuum ins_since_vacuum 0 -> no autovacuum # # Autovacuum thresholds use pg_class.reltuples, which is correct; the # counters above are what it compares them with. set -eu NTAB=${NTAB:-20} NROWS=${NROWS:-10000} WAIT=${WAIT:-20} DB=pending_stats_test psql -X -q -v ON_ERROR_STOP=1 -d postgres <<EOF DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB; EOF P="psql -X -q -At -v ON_ERROR_STOP=1 -d $DB" $P -c "SELECT 'server: ' || version()" $P -c "SELECT 'autovacuum=' || current_setting('autovacuum') || ' naptime=' || current_setting('autovacuum_naptime')" if [ "$($P -c "SELECT current_setting('autovacuum_naptime')")" != "1s" ]; then echo "warning: autovacuum_naptime is not 1s; raise WAIT accordingly" >&2 fi SCENARIOS="ins_anl_bug ins_anl_ctl del_vac_bug del_vac_ctl ins_vac_bug ins_vac_ctl" # Setup: delete+vacuum tables start with NROWS rows, the others empty. # Every table gets VACUUM ANALYZE so all counters start at zero. # Autovacuum stays disabled on the tables until the workload is done, so # the counters can be read before it reacts. for s in $SCENARIOS; do i=1 while [ $i -le $NTAB ]; do $P -c "CREATE TABLE ${s}_$i (id int) WITH (autovacuum_enabled = false)" case $s in del_vac_*) $P -c "INSERT INTO ${s}_$i SELECT generate_series(1, $NROWS)";; esac i=$((i + 1)) done done $P -c "CREATE TABLE warm (i int)" $P -c "VACUUM ANALYZE" sleep 3 $P <<EOF CREATE TABLE baseline AS SELECT relname, autovacuum_count, autoanalyze_count FROM pg_stat_user_tables; EOF # Workload. for s in $SCENARIOS; do i=1 while [ $i -le $NTAB ]; do t=${s}_$i case $s in ins_anl_*) change="INSERT INTO $t SELECT generate_series(1, $NROWS);" maint="ANALYZE $t;";; del_vac_*) change="DELETE FROM $t WHERE id <= $NROWS / 2;" maint="VACUUM $t;";; ins_vac_*) change="INSERT INTO $t SELECT generate_series(1, $NROWS);" maint="VACUUM $t;";; esac case $s in *_ctl) pause="SELECT pg_sleep(1.5);";; *) pause="";; esac printf '%s\n' "INSERT INTO warm VALUES (1);" "$change" "$pause" "$maint" \ | $P >/dev/null i=$((i + 1)) done done # Counters right after the workload. All sessions have exited, so all # pending stats are flushed. $P -c "SELECT pg_sleep(0.5)" >/dev/null $P <<'EOF' \pset format aligned \pset tuples_only off \pset footer off \echo \echo Counters after the workload (averages per table): SELECT split_part(relname, '_', 1) || '_' || split_part(relname, '_', 2) AS workload, split_part(relname, '_', 3) AS kind, round(avg(n_live_tup)) AS n_live_tup, round(avg(n_dead_tup)) AS n_dead_tup, round(avg(n_mod_since_analyze)) AS mod_since_analyze, round(avg(n_ins_since_vacuum)) AS ins_since_vacuum FROM pg_stat_user_tables WHERE relname ~ '_(bug|ctl)_' GROUP BY 1, 2 ORDER BY 1, 2 DESC; EOF for s in $SCENARIOS; do i=1 while [ $i -le $NTAB ]; do $P -c "ALTER TABLE ${s}_$i RESET (autovacuum_enabled)" i=$((i + 1)) done done echo echo "Autovacuum enabled on the tables; waiting ${WAIT}s..." sleep "$WAIT" $P <<'EOF' \pset format aligned \pset tuples_only off \pset footer off \echo \echo Autovacuum/autoanalyze runs after the workload (sum over all tables): SELECT split_part(s.relname, '_', 1) || '_' || split_part(s.relname, '_', 2) AS workload, split_part(s.relname, '_', 3) AS kind, count(*) AS tables, sum(s.autovacuum_count - b.autovacuum_count) AS autovacuums, sum(s.autoanalyze_count - b.autoanalyze_count) AS autoanalyzes FROM pg_stat_user_tables s JOIN baseline b USING (relname) WHERE s.relname ~ '_(bug|ctl)_' GROUP BY 1, 2 ORDER BY 1, 2 DESC; EOF psql -X -q -d postgres -c "DROP DATABASE $DB"
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-14 22:53  Manu <manuelreyesbravo@gmail.com>
  parent: Sebastian Webber <sebastian@swebber.me>
  0 siblings, 1 reply; 15+ messages in thread

From: Manu @ 2026-10-14 22:53 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Sebastian Webber <sebastian@swebber.me>; Staroverov Ilja <i.staroverov@ftdata.ru>

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






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-15 06:08  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Staroverov Ilja @ 2026-10-15 06:08 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Sebastian Webber <sebastian@swebber.me>

Hi Manu, Sebastian,

Thanks a lot for the review, the stress testing and the autovacuum
numbers, and for finding 81e82a2bd48.

v2 attached:

0001 is v1 plus a pinned test. Pending stats are only flushed while
the backend is idle, so the TRUNCATE and ANALYZE cases now run inside
a procedure, with a COMMIT between the steps. Nothing can be flushed
in between, whatever the timing. I checked that on unpatched master
the test still fails with pg_sleep(1.5) between the steps (n_live_tup
12 and 13 instead of 6 and 3). VACUUM can't run in a procedure, so the
VACUUM case is still best effort: if a flush slips in between the
INSERT and the VACUUM, it passes without testing anything, but it
can't fail spuriously. Manu, if you had a way to pin that one too,
I'd be glad to use it.

0002 is Manu's ins_since_vacuum delta, unchanged. I kept it as a
separate patch with Manu as the author. It looks right to me:
ins_before_vacuum lives only in the pending entry, which is freed after
a successful flush. tuples_inserted only grows while the entry is
pending, so the difference can't go negative. A second VACUUM simply
re-assigns it. On top of v1, it also passes my edge cases (2PC
TRUNCATE, relcache invalidation between the INSERT and the ANALYZE,
TOAST, matview, partitioned table, ANALYZE of a column list, and a
database-wide VACUUM ANALYZE with assertions enabled).

Both apply cleanly to current master (73e69062c).

Manu, about the two pre-existing TRUNCATE/subtransaction cases: yes,
please send the repros. Since they aren't caused by this patch, maybe
they would be better handled as a separate patch, so that this one
stays small enough to back-patch. Sebastian's numbers show that 14-18
are affected too. I can prepare back-branch versions if a committer
wants them.

--
Ilya Staroverov



Attachments: download all (2 files, .tar.gz)

v2-0001-Fix-double-counting-of-pending-relation-stats-by-.patch (application/octet-stream, 11.1K) download From cb115c49f03d42b44fe323ccd9b7fe8fefa15495 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Thu, 15 Oct 2026 08:45:34 +0300 Subject: [PATCH v2 1/2] Fix double-counting of pending relation stats by VACUUM and ANALYZE VACUUM and ANALYZE overwrite live_tuples and dead_tuples of the shared stats entry with their own counts. Those counts already include the effects of transactions committed earlier by the same backend, but the corresponding deltas could still be pending in the backend's local stats (pending stats are flushed at most once per PGSTAT_MIN_INTERVAL), and were then added again on top of the new values at the next flush. For example, INSERT of N rows immediately followed by ANALYZE or VACUUM in autocommit mode left n_live_tup at 2*N, and DELETE followed by ANALYZE could leave it at 0. ANALYZE also reset mod_since_analyze only for the pending changed_tuples to be added right back. ANALYZE did already discount pending delta_dead_tuples, but not delta_live_tuples. Fix by forgetting the pending live/dead deltas (and, for ANALYZE with resetcounter, the pending changed_tuples) when VACUUM or ANALYZE report new values. A pending truncation reset of live/dead counters is superseded as well; for ANALYZE its reset of ins_since_vacuum is applied right away. Also, the commit of a transaction that did not truncate a relation cleared the pending truncdropped flag left by an earlier, not yet flushed TRUNCATE of the same backend, so that e.g. TRUNCATE followed by an INSERT of M rows left n_live_tup at (old count + M). Only ever set the flag at commit, never clear it; same for COMMIT PREPARED. Like 81e82a2bd48, this is about deltas of an earlier transaction of the same backend that have not been flushed yet. Author: Ilya Staroverov <i.staroverov@ftdata.ru> Reviewed-by: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Tested-by: Sebastian Webber <sebastian@swebber.me> Discussion: https://postgr.es/m/1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain --- src/backend/utils/activity/pgstat_relation.c | 52 ++++++++++++++++--- src/test/regress/expected/stats.out | 54 ++++++++++++++++++++ src/test/regress/sql/stats.sql | 38 ++++++++++++++ 3 files changed, 138 insertions(+), 6 deletions(-) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index 5c70543ba..cfebd0cd6 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -273,6 +273,23 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, if (!pgstat_track_counts) return; + /* + * The live and dead tuple counts computed by VACUUM already include the + * effects of transactions previously committed by this backend whose + * stats have not been flushed yet. Forget the pending deltas for those + * counters, else they would be added on top of the new values at the + * next flush. A pending truncation reset is superseded as well. + */ + if (pgstat_should_count_relation(rel)) + { + PgStat_RelationStatus *pending = rel->pgstat_info; + + Assert(pending->kind == PGSTAT_KIND_RELATION); + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + pending->tab.truncdropped = false; + } + /* Store the data in the table's hash table entry. */ ts = GetCurrentTimestamp(); elapsedtime = TimestampDifferenceMilliseconds(starttime, ts); @@ -341,6 +358,7 @@ pgstat_report_analyze(Relation rel, Oid dboid = (rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId); TimestampTz ts; PgStat_Counter elapsedtime; + bool truncdropped = false; if (!pgstat_track_counts) return; @@ -360,19 +378,34 @@ pgstat_report_analyze(Relation rel, if (pgstat_should_count_relation(rel) && rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) { + PgStat_RelationStatus *pending = rel->pgstat_info; PgStat_TableXactStatus *trans; - Assert(rel->pgstat_info->kind == PGSTAT_KIND_RELATION); - for (trans = rel->pgstat_info->tab.trans; trans; trans = trans->upper) + Assert(pending->kind == PGSTAT_KIND_RELATION); + for (trans = pending->tab.trans; trans; trans = trans->upper) { livetuples -= trans->tuples_inserted - trans->tuples_deleted; deadtuples -= trans->tuples_updated + trans->tuples_deleted; } - /* count stuff inserted by already-aborted subxacts, too */ - deadtuples -= rel->pgstat_info->tab.counts_xact.delta_dead_tuples; /* Since ANALYZE's counts are estimates, we could have underflowed */ livetuples = Max(livetuples, 0); deadtuples = Max(deadtuples, 0); + + /* + * Likewise, ANALYZE has already counted the effects of transactions + * previously committed by this backend (and of already-aborted + * subxacts) whose stats have not been flushed yet. Forget the + * pending deltas for those, else they would be added on top of the + * new values at the next flush. A pending truncation reset of the + * live/dead counters is superseded too, but we still have to apply + * its reset of ins_since_vacuum below. + */ + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + if (resetcounter) + pending->tab.counts_xact.changed_tuples = 0; + truncdropped = pending->tab.truncdropped; + pending->tab.truncdropped = false; } /* Store the data in the table's hash table entry. */ @@ -391,6 +424,8 @@ pgstat_report_analyze(Relation rel, tabentry->live_tuples = livetuples; tabentry->dead_tuples = deadtuples; + if (truncdropped) + tabentry->ins_since_vacuum = 0; /* * If commanded, reset mod_since_analyze to zero. This forgets any @@ -641,9 +676,13 @@ AtEOXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool isCommit) relstat->tab.counts_xact.tuples_deleted += trans->tuples_deleted; if (isCommit) { - relstat->tab.truncdropped = trans->truncdropped; + /* + * Don't clear a truncation still pending from an earlier + * transaction whose stats have not been flushed yet. + */ if (trans->truncdropped) { + relstat->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ relstat->tab.counts_xact.delta_live_tuples = 0; relstat->tab.counts_xact.delta_dead_tuples = 0; @@ -829,9 +868,10 @@ pgstat_twophase_postcommit(FullTransactionId fxid, uint16 info, pgstat_info->tab.counts_xact.tuples_inserted += rec->tuples_inserted; pgstat_info->tab.counts_xact.tuples_updated += rec->tuples_updated; pgstat_info->tab.counts_xact.tuples_deleted += rec->tuples_deleted; - pgstat_info->tab.truncdropped = rec->truncdropped; + /* see AtEOXact_PgStat_Relations() */ if (rec->truncdropped) { + pgstat_info->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ pgstat_info->tab.counts_xact.delta_live_tuples = 0; pgstat_info->tab.counts_xact.delta_dead_tuples = 0; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 8b1547124..ea6b260d4 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -293,6 +293,60 @@ FROM prevstats AS pr; COMMIT; ----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- Pending stats are only flushed while the backend is idle, so run the +-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not +-- flushed in between. VACUUM cannot run in a procedure; if the stats happen +-- to get flushed between the INSERT and the VACUUM, that case still passes, +-- but doesn't test anything. +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$ +BEGIN + TRUNCATE stats_pending_trunc; + COMMIT; + INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); + COMMIT; + INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); + COMMIT; + DELETE FROM stats_pending_analyze WHERE id <= 4; + COMMIT; + ANALYZE stats_pending_analyze; +END +$$; +CALL stats_pending_proc(); +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; + relname | n_live_tup | n_dead_tup | n_mod_since_analyze +-----------------------+------------+------------+--------------------- + stats_pending_analyze | 6 | 4 | 0 + stats_pending_trunc | 3 | 0 | 13 + stats_pending_vacuum | 10 | 0 | 10 +(3 rows) + +DROP PROCEDURE stats_pending_proc(); +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; +----- -- Basic tests for pg_stat_all_indexes ----- CREATE TEMPORARY TABLE test_idx_tup_read AS diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 674637e17..d446a7fd9 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -153,6 +153,44 @@ FROM prevstats AS pr; COMMIT; +----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- Pending stats are only flushed while the backend is idle, so run the +-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not +-- flushed in between. VACUUM cannot run in a procedure; if the stats happen +-- to get flushed between the INSERT and the VACUUM, that case still passes, +-- but doesn't test anything. +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); +CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$ +BEGIN + TRUNCATE stats_pending_trunc; + COMMIT; + INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); + COMMIT; + INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); + COMMIT; + DELETE FROM stats_pending_analyze WHERE id <= 4; + COMMIT; + ANALYZE stats_pending_analyze; +END +$$; +CALL stats_pending_proc(); +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; +DROP PROCEDURE stats_pending_proc(); +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; + ----- -- Basic tests for pg_stat_all_indexes ----- -- 2.43.0
v2-0002-Don-t-count-pending-inserts-in-ins_since_vacuum-a.patch (application/octet-stream, 5.5K) download From e3bd8b150af470ff3b2a7d9651350c5fa5e87d3d Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Date: Thu, 15 Oct 2026 08:47:11 +0300 Subject: [PATCH v2 2/2] Don't count pending inserts in ins_since_vacuum after VACUUM Like live_tuples and dead_tuples, VACUUM resets ins_since_vacuum while inserts committed earlier by the same backend may still be pending, and the next flush then added them back, so e.g. INSERT of N rows followed by VACUUM left n_ins_since_vacuum at N, which could trigger a needless insert-driven autovacuum. We can't simply forget the pending tuples_inserted, since it also feeds the cumulative tuples_inserted counter, so remember how many of them VACUUM has already covered and have the flush skip those for ins_since_vacuum. Author: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Reviewed-by: Ilya Staroverov <i.staroverov@ftdata.ru> Tested-by: Sebastian Webber <sebastian@swebber.me> Discussion: https://postgr.es/m/1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain --- src/backend/utils/activity/pgstat_relation.c | 10 +++++++++- src/include/pgstat.h | 2 ++ src/test/regress/expected/stats.out | 12 ++++++------ src/test/regress/sql/stats.sql | 2 +- 4 files changed, 18 insertions(+), 8 deletions(-) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index cfebd0cd6..9e6559596 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -279,6 +279,11 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, * stats have not been flushed yet. Forget the pending deltas for those * counters, else they would be added on top of the new values at the * next flush. A pending truncation reset is superseded as well. + * + * Likewise, the pending inserts must not count towards ins_since_vacuum, + * which is reset below. We can't just zero tuples_inserted, which also + * feeds the cumulative tuples_inserted counter, so remember how many of + * them the flush has to skip. */ if (pgstat_should_count_relation(rel)) { @@ -288,6 +293,8 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, pending->tab.counts_xact.delta_live_tuples = 0; pending->tab.counts_xact.delta_dead_tuples = 0; pending->tab.truncdropped = false; + pending->tab.ins_before_vacuum = + pending->tab.counts_xact.tuples_inserted; } /* Store the data in the table's hash table entry. */ @@ -993,7 +1000,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) * triggering for inserts more often than they maybe should, which is * probably not going to be common enough to be too concerned about here. */ - tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted; + tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted - + lstats->tab.ins_before_vacuum; tabentry->blocks_fetched += lstats->tab.counts.blocks_fetched; tabentry->blocks_hit += lstats->tab.counts.blocks_hit; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 187d82c96..6b6a705ab 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -236,6 +236,8 @@ typedef struct PgStat_RelationStatus struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */ PgStat_TableCounts counts; /* event counts to be sent */ PgStat_TableCountsXact counts_xact; /* transactional counts */ + /* pending tuples_inserted already covered by a VACUUM */ + PgStat_Counter ins_before_vacuum; } tab; /* index counters */ diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index ea6b260d4..ea7f47d63 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -334,14 +334,14 @@ SELECT pg_stat_force_next_flush(); (1 row) -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; - relname | n_live_tup | n_dead_tup | n_mod_since_analyze ------------------------+------------+------------+--------------------- - stats_pending_analyze | 6 | 4 | 0 - stats_pending_trunc | 3 | 0 | 13 - stats_pending_vacuum | 10 | 0 | 10 + relname | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum +-----------------------+------------+------------+---------------------+-------------------- + stats_pending_analyze | 6 | 4 | 0 | 10 + stats_pending_trunc | 3 | 0 | 13 | 3 + stats_pending_vacuum | 10 | 0 | 10 | 0 (3 rows) DROP PROCEDURE stats_pending_proc(); diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index d446a7fd9..fd30a09ce 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -185,7 +185,7 @@ CALL stats_pending_proc(); INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP PROCEDURE stats_pending_proc(); -- 2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-15 07:11  Manu <manuelreyesbravo@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 1 reply; 15+ messages in thread

From: Manu @ 2026-10-15 07:11 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>; Sebastian Webber <sebastian@swebber.me>

Hi Ilya,

> about the two pre-existing TRUNCATE/subtransaction cases: yes, please
> send the repros.

Attached: p1 and p2. They reproduce on 14 through master, and neither
patch changes them, so they aren't from this one. Agreed they're better
as a separate, back-patchable patch; I left the probable fix area (the
truncdrop save/restore across subxacts) in a comment, no fix.

> Manu, if you had a way to pin that one too, I'd be glad to use it.

I did find a deterministic one (diffs attached, works on 14..master and
also catches the ins_since_vacuum case), but let me be upfront: it's
unconventional. The test function is marked IMMUTABLE only so it can be
indexed, and it does DML during ANALYZE (an armed expression index
inserts into the table whose TOAST the same VACUUM then cleans). A
committer may not want that in a test. The clean alternative is a
test-only injection point in the flush path, but that's a core change.

My honest lean is the injection point, or just leaving the VACUUM case
best-effort as you have it, rather than the index trick. Your call; the
diffs are there if you want them.

Regards,
Manu

Attachments: download all (4 files, .tar.gz)

p1_repro.sql.txt (text/plain, 1.7K) download -- P1: inserts still pending when a TRUNCATE commits are added to -- n_ins_since_vacuum after the TRUNCATE resets it. -- -- One session. The procedure keeps the backend busy between the steps, and -- pending stats are only flushed while the backend is idle, so the first -- INSERT is still pending when the TRUNCATE commits. Runs on 14 and later. \set ON_ERROR_STOP 1 \pset footer off SELECT current_setting('server_version_num')::int >= 150000 AS has_force_flush \gset \if :has_force_flush \set flush 'SELECT pg_stat_force_next_flush()' \else \set flush 'SELECT pg_sleep(1.1)' \endif CREATE TABLE p1_pending (id int) WITH (autovacuum_enabled = off); CREATE TABLE p1_flushed (id int) WITH (autovacuum_enabled = off); CREATE PROCEDURE p1_steps() LANGUAGE plpgsql AS $$ BEGIN INSERT INTO p1_pending SELECT generate_series(1, 9); COMMIT; TRUNCATE p1_pending; COMMIT; INSERT INTO p1_pending SELECT generate_series(1, 6); END $$; \echo == P1: INSERT 9; TRUNCATE; INSERT 6 (one procedure, nothing flushed in between) CALL p1_steps(); :flush \g /dev/null SELECT n_live_tup, n_ins_since_vacuum, (SELECT count(*) FROM p1_pending) AS count FROM pg_stat_user_tables WHERE relname = 'p1_pending'; \echo expected n_ins_since_vacuum = 6 (rows inserted since the TRUNCATE); bug: 15 \echo == control: same statements, stats flushed after each step INSERT INTO p1_flushed SELECT generate_series(1, 9); :flush \g /dev/null TRUNCATE p1_flushed; :flush \g /dev/null INSERT INTO p1_flushed SELECT generate_series(1, 6); :flush \g /dev/null SELECT n_live_tup, n_ins_since_vacuum, (SELECT count(*) FROM p1_flushed) AS count FROM pg_stat_user_tables WHERE relname = 'p1_flushed'; \echo control gives n_ins_since_vacuum = 6 DROP PROCEDURE p1_steps(); DROP TABLE p1_pending, p1_flushed;
p2_repro.sql.txt (text/plain, 2.3K) download -- P2: TRUNCATE followed, in the same transaction, by work that is undone or -- by an ANALYZE, leaves wrong live/dead counts. Deterministic: no -- dependency on flush timing. Runs on 14 and later. \set ON_ERROR_STOP 1 \pset footer off SELECT current_setting('server_version_num')::int >= 150000 AS has_force_flush \gset \if :has_force_flush \set flush 'SELECT pg_stat_force_next_flush()' \else \set flush 'SELECT pg_sleep(1.1)' \endif \echo == P2a: BEGIN; TRUNCATE; SAVEPOINT; INSERT 100; ROLLBACK TO; COMMIT CREATE TABLE p2a (id int) WITH (autovacuum_enabled = off); BEGIN; TRUNCATE p2a; SAVEPOINT s; INSERT INTO p2a SELECT generate_series(1, 100); ROLLBACK TO s; COMMIT; :flush \g /dev/null SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'p2a'; \echo expected n_dead_tup = 100 (the aborted rows are in the new relfilenode); bug: 0 \echo VACUUM VERBOSE shows the 100 dead rows it removes: VACUUM (VERBOSE) p2a; \echo == P2a control: same without the TRUNCATE CREATE TABLE p2a_ctl (id int) WITH (autovacuum_enabled = off); BEGIN; SAVEPOINT s; INSERT INTO p2a_ctl SELECT generate_series(1, 100); ROLLBACK TO s; COMMIT; :flush \g /dev/null SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'p2a_ctl'; \echo control gives n_dead_tup = 100 \echo == P2b: 288 rows; BEGIN; SAVEPOINT; TRUNCATE; ANALYZE; ROLLBACK TO; COMMIT CREATE TABLE p2b (id int) WITH (autovacuum_enabled = off); INSERT INTO p2b SELECT generate_series(1, 288); ANALYZE p2b; :flush \g /dev/null BEGIN; SAVEPOINT s; TRUNCATE p2b; ANALYZE p2b; ROLLBACK TO s; COMMIT; :flush \g /dev/null SELECT s.n_live_tup, c.reltuples, (SELECT count(*) FROM p2b) AS count FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid WHERE s.relname = 'p2b'; \echo expected n_live_tup = 288; bug: 0. pg_class.reltuples keeps the right value \echo == P2b, top-level variant: BEGIN; TRUNCATE; ANALYZE; ROLLBACK CREATE TABLE p2c (id int) WITH (autovacuum_enabled = off); INSERT INTO p2c SELECT generate_series(1, 288); ANALYZE p2c; :flush \g /dev/null BEGIN; TRUNCATE p2c; ANALYZE p2c; ROLLBACK; :flush \g /dev/null SELECT s.n_live_tup, c.reltuples, (SELECT count(*) FROM p2c) AS count FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid WHERE s.relname = 'p2c'; DROP TABLE p2a, p2a_ctl, p2b, p2c;
vacuum-pin-0001.diff (text/x-patch, 6.9K) download diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index ea6b260..9e16f0d 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -298,13 +298,10 @@ COMMIT; -- forgotten when another transaction commits before the stats are flushed. -- Pending stats are only flushed while the backend is idle, so run the -- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not --- flushed in between. VACUUM cannot run in a procedure; if the stats happen --- to get flushed between the INSERT and the VACUUM, that case still passes, --- but doesn't test anything. +-- flushed in between. VACUUM is tested separately below. ----- CREATE TABLE stats_pending_trunc(id int); CREATE TABLE stats_pending_analyze(id int); -CREATE TABLE stats_pending_vacuum(id int); INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); SELECT pg_stat_force_next_flush(); pg_stat_force_next_flush @@ -326,8 +323,6 @@ BEGIN END $$; CALL stats_pending_proc(); -INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); -VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); pg_stat_force_next_flush -------------------------- @@ -341,11 +336,58 @@ SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze -----------------------+------------+------------+--------------------- stats_pending_analyze | 6 | 4 | 0 stats_pending_trunc | 3 | 0 | 13 - stats_pending_vacuum | 10 | 0 | 10 -(3 rows) +(2 rows) DROP PROCEDURE stats_pending_proc(); -DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; +DROP TABLE stats_pending_trunc, stats_pending_analyze; +-- VACUUM cannot run in a procedure, so let the VACUUM command do the INSERT +-- itself: analyzing stats_vacuum_src evaluates its index expression, which +-- (while armed) inserts into stats_vacuum_pending, whose TOAST table the same +-- command vacuums next. The backend is never idle in between, so nothing +-- can be flushed, and VACUUM (ANALYZE) doesn't analyze TOAST tables, so the +-- TOAST table's stats come from VACUUM only. reltuples is set by that VACUUM. +-- stats_vacuum_expr() is labeled IMMUTABLE only so that it can be indexed. +CREATE TABLE stats_vacuum_pending(t text); +ALTER TABLE stats_vacuum_pending ALTER t SET STORAGE EXTERNAL; +CREATE TABLE stats_vacuum_src(id int); +CREATE FUNCTION stats_vacuum_fill() RETURNS void LANGUAGE plpgsql AS $$ +BEGIN + INSERT INTO public.stats_vacuum_pending + SELECT repeat('x', 10000) FROM generate_series(1, 10); +END +$$; +CREATE FUNCTION stats_vacuum_expr(int) RETURNS int IMMUTABLE + LANGUAGE plpgsql AS $$ +BEGIN + IF current_setting('stats_vacuum.armed', true) = 'on' THEN + PERFORM public.stats_vacuum_fill(); + END IF; + RETURN $1; +END +$$; +CREATE INDEX ON stats_vacuum_src (stats_vacuum_expr(id)); +INSERT INTO stats_vacuum_src VALUES (1); +SET stats_vacuum.armed = on; +VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending; +RESET stats_vacuum.armed; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok, + s.n_dead_tup + FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid + WHERE c.oid = (SELECT reltoastrelid FROM pg_class + WHERE oid = 'stats_vacuum_pending'::regclass); + toasted | live_ok | n_dead_tup +---------+---------+------------ + t | t | 0 +(1 row) + +DROP TABLE stats_vacuum_src, stats_vacuum_pending; +DROP FUNCTION stats_vacuum_expr(int), stats_vacuum_fill(); ----- -- Basic tests for pg_stat_all_indexes ----- diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index d446a7f..b9c65af 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -159,13 +159,10 @@ COMMIT; -- forgotten when another transaction commits before the stats are flushed. -- Pending stats are only flushed while the backend is idle, so run the -- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not --- flushed in between. VACUUM cannot run in a procedure; if the stats happen --- to get flushed between the INSERT and the VACUUM, that case still passes, --- but doesn't test anything. +-- flushed in between. VACUUM is tested separately below. ----- CREATE TABLE stats_pending_trunc(id int); CREATE TABLE stats_pending_analyze(id int); -CREATE TABLE stats_pending_vacuum(id int); INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); SELECT pg_stat_force_next_flush(); CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$ @@ -182,14 +179,51 @@ BEGIN END $$; CALL stats_pending_proc(); -INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); -VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP PROCEDURE stats_pending_proc(); -DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; +DROP TABLE stats_pending_trunc, stats_pending_analyze; + +-- VACUUM cannot run in a procedure, so let the VACUUM command do the INSERT +-- itself: analyzing stats_vacuum_src evaluates its index expression, which +-- (while armed) inserts into stats_vacuum_pending, whose TOAST table the same +-- command vacuums next. The backend is never idle in between, so nothing +-- can be flushed, and VACUUM (ANALYZE) doesn't analyze TOAST tables, so the +-- TOAST table's stats come from VACUUM only. reltuples is set by that VACUUM. +-- stats_vacuum_expr() is labeled IMMUTABLE only so that it can be indexed. +CREATE TABLE stats_vacuum_pending(t text); +ALTER TABLE stats_vacuum_pending ALTER t SET STORAGE EXTERNAL; +CREATE TABLE stats_vacuum_src(id int); +CREATE FUNCTION stats_vacuum_fill() RETURNS void LANGUAGE plpgsql AS $$ +BEGIN + INSERT INTO public.stats_vacuum_pending + SELECT repeat('x', 10000) FROM generate_series(1, 10); +END +$$; +CREATE FUNCTION stats_vacuum_expr(int) RETURNS int IMMUTABLE + LANGUAGE plpgsql AS $$ +BEGIN + IF current_setting('stats_vacuum.armed', true) = 'on' THEN + PERFORM public.stats_vacuum_fill(); + END IF; + RETURN $1; +END +$$; +CREATE INDEX ON stats_vacuum_src (stats_vacuum_expr(id)); +INSERT INTO stats_vacuum_src VALUES (1); +SET stats_vacuum.armed = on; +VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending; +RESET stats_vacuum.armed; +SELECT pg_stat_force_next_flush(); +SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok, + s.n_dead_tup + FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid + WHERE c.oid = (SELECT reltoastrelid FROM pg_class + WHERE oid = 'stats_vacuum_pending'::regclass); +DROP TABLE stats_vacuum_src, stats_vacuum_pending; +DROP FUNCTION stats_vacuum_expr(int), stats_vacuum_fill(); ----- -- Basic tests for pg_stat_all_indexes
vacuum-pin-0002-tests.diff (text/x-patch, 2.9K) download diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 9e16f0d..a5eb65b 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -329,13 +329,13 @@ SELECT pg_stat_force_next_flush(); (1 row) -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; - relname | n_live_tup | n_dead_tup | n_mod_since_analyze ------------------------+------------+------------+--------------------- - stats_pending_analyze | 6 | 4 | 0 - stats_pending_trunc | 3 | 0 | 13 + relname | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum +-----------------------+------------+------------+---------------------+-------------------- + stats_pending_analyze | 6 | 4 | 0 | 10 + stats_pending_trunc | 3 | 0 | 13 | 3 (2 rows) DROP PROCEDURE stats_pending_proc(); @@ -377,13 +377,13 @@ SELECT pg_stat_force_next_flush(); (1 row) SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok, - s.n_dead_tup + s.n_dead_tup, s.n_ins_since_vacuum FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid WHERE c.oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'stats_vacuum_pending'::regclass); - toasted | live_ok | n_dead_tup ----------+---------+------------ - t | t | 0 + toasted | live_ok | n_dead_tup | n_ins_since_vacuum +---------+---------+------------+-------------------- + t | t | 0 | 0 (1 row) DROP TABLE stats_vacuum_src, stats_vacuum_pending; diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index b9c65af..32ae660 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -180,7 +180,7 @@ END $$; CALL stats_pending_proc(); SELECT pg_stat_force_next_flush(); -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP PROCEDURE stats_pending_proc(); @@ -218,7 +218,7 @@ VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending; RESET stats_vacuum.armed; SELECT pg_stat_force_next_flush(); SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok, - s.n_dead_tup + s.n_dead_tup, s.n_ins_since_vacuum FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid WHERE c.oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'stats_vacuum_pending'::regclass);
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-15 07:48  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Staroverov Ilja @ 2026-10-15 07:48 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Sebastian Webber <sebastian@swebber.me>

Hi Manu,

Thanks for the repros. I can confirm both on 18.6.

As for pinning the VACUUM case, I agree with your lean: an IMMUTABLE
function doing DML from an index expression during ANALYZE is likely
to put a committer off, and an injection point seems like too much for
a bug fix. So let's keep the VACUUM case best-effort, as it is in v2.

As for P1 and P2, would you like to take them to a separate thread
yourself? You found them and have already narrowed down where the fix
probably belongs. If you'd rather not, I'm happy to work on them, just
let me know.

--
Ilya Staroverov



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-15 07:52  Manu <manuelreyesbravo@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 1 reply; 15+ messages in thread

From: Manu @ 2026-10-15 07:52 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Staroverov Ilja <i.staroverov@ftdata.ru>; Sebastian Webber <sebastian@swebber.me>

Hi Ilya,

Thanks for confirming the repros, and agreed on keeping the VACUUM case
best-effort.

> would you like to take them to a separate thread yourself?

Yes, I'll take P1 and P2 to their own thread and post them with a fix;
since they reach back to 14 that keeps this one small enough to
back-patch. I'll CC you.

Regards,
Manu






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-10-15 08:05  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Staroverov Ilja @ 2026-10-15 08:05 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Sebastian Webber <sebastian@swebber.me>

Re-attaching v2 unchanged, so that cfbot tests it rather than the
vacuum-pin diffs from upthread.



Attachments: download all (2 files, .tar.gz)

v2-0001-Fix-double-counting-of-pending-relation-stats-by-.patch (application/octet-stream, 11.1K) download From cb115c49f03d42b44fe323ccd9b7fe8fefa15495 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Thu, 15 Oct 2026 08:45:34 +0300 Subject: [PATCH v2 1/2] Fix double-counting of pending relation stats by VACUUM and ANALYZE VACUUM and ANALYZE overwrite live_tuples and dead_tuples of the shared stats entry with their own counts. Those counts already include the effects of transactions committed earlier by the same backend, but the corresponding deltas could still be pending in the backend's local stats (pending stats are flushed at most once per PGSTAT_MIN_INTERVAL), and were then added again on top of the new values at the next flush. For example, INSERT of N rows immediately followed by ANALYZE or VACUUM in autocommit mode left n_live_tup at 2*N, and DELETE followed by ANALYZE could leave it at 0. ANALYZE also reset mod_since_analyze only for the pending changed_tuples to be added right back. ANALYZE did already discount pending delta_dead_tuples, but not delta_live_tuples. Fix by forgetting the pending live/dead deltas (and, for ANALYZE with resetcounter, the pending changed_tuples) when VACUUM or ANALYZE report new values. A pending truncation reset of live/dead counters is superseded as well; for ANALYZE its reset of ins_since_vacuum is applied right away. Also, the commit of a transaction that did not truncate a relation cleared the pending truncdropped flag left by an earlier, not yet flushed TRUNCATE of the same backend, so that e.g. TRUNCATE followed by an INSERT of M rows left n_live_tup at (old count + M). Only ever set the flag at commit, never clear it; same for COMMIT PREPARED. Like 81e82a2bd48, this is about deltas of an earlier transaction of the same backend that have not been flushed yet. Author: Ilya Staroverov <i.staroverov@ftdata.ru> Reviewed-by: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Tested-by: Sebastian Webber <sebastian@swebber.me> Discussion: https://postgr.es/m/1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain --- src/backend/utils/activity/pgstat_relation.c | 52 ++++++++++++++++--- src/test/regress/expected/stats.out | 54 ++++++++++++++++++++ src/test/regress/sql/stats.sql | 38 ++++++++++++++ 3 files changed, 138 insertions(+), 6 deletions(-) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index 5c70543ba..cfebd0cd6 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -273,6 +273,23 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, if (!pgstat_track_counts) return; + /* + * The live and dead tuple counts computed by VACUUM already include the + * effects of transactions previously committed by this backend whose + * stats have not been flushed yet. Forget the pending deltas for those + * counters, else they would be added on top of the new values at the + * next flush. A pending truncation reset is superseded as well. + */ + if (pgstat_should_count_relation(rel)) + { + PgStat_RelationStatus *pending = rel->pgstat_info; + + Assert(pending->kind == PGSTAT_KIND_RELATION); + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + pending->tab.truncdropped = false; + } + /* Store the data in the table's hash table entry. */ ts = GetCurrentTimestamp(); elapsedtime = TimestampDifferenceMilliseconds(starttime, ts); @@ -341,6 +358,7 @@ pgstat_report_analyze(Relation rel, Oid dboid = (rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId); TimestampTz ts; PgStat_Counter elapsedtime; + bool truncdropped = false; if (!pgstat_track_counts) return; @@ -360,19 +378,34 @@ pgstat_report_analyze(Relation rel, if (pgstat_should_count_relation(rel) && rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) { + PgStat_RelationStatus *pending = rel->pgstat_info; PgStat_TableXactStatus *trans; - Assert(rel->pgstat_info->kind == PGSTAT_KIND_RELATION); - for (trans = rel->pgstat_info->tab.trans; trans; trans = trans->upper) + Assert(pending->kind == PGSTAT_KIND_RELATION); + for (trans = pending->tab.trans; trans; trans = trans->upper) { livetuples -= trans->tuples_inserted - trans->tuples_deleted; deadtuples -= trans->tuples_updated + trans->tuples_deleted; } - /* count stuff inserted by already-aborted subxacts, too */ - deadtuples -= rel->pgstat_info->tab.counts_xact.delta_dead_tuples; /* Since ANALYZE's counts are estimates, we could have underflowed */ livetuples = Max(livetuples, 0); deadtuples = Max(deadtuples, 0); + + /* + * Likewise, ANALYZE has already counted the effects of transactions + * previously committed by this backend (and of already-aborted + * subxacts) whose stats have not been flushed yet. Forget the + * pending deltas for those, else they would be added on top of the + * new values at the next flush. A pending truncation reset of the + * live/dead counters is superseded too, but we still have to apply + * its reset of ins_since_vacuum below. + */ + pending->tab.counts_xact.delta_live_tuples = 0; + pending->tab.counts_xact.delta_dead_tuples = 0; + if (resetcounter) + pending->tab.counts_xact.changed_tuples = 0; + truncdropped = pending->tab.truncdropped; + pending->tab.truncdropped = false; } /* Store the data in the table's hash table entry. */ @@ -391,6 +424,8 @@ pgstat_report_analyze(Relation rel, tabentry->live_tuples = livetuples; tabentry->dead_tuples = deadtuples; + if (truncdropped) + tabentry->ins_since_vacuum = 0; /* * If commanded, reset mod_since_analyze to zero. This forgets any @@ -641,9 +676,13 @@ AtEOXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool isCommit) relstat->tab.counts_xact.tuples_deleted += trans->tuples_deleted; if (isCommit) { - relstat->tab.truncdropped = trans->truncdropped; + /* + * Don't clear a truncation still pending from an earlier + * transaction whose stats have not been flushed yet. + */ if (trans->truncdropped) { + relstat->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ relstat->tab.counts_xact.delta_live_tuples = 0; relstat->tab.counts_xact.delta_dead_tuples = 0; @@ -829,9 +868,10 @@ pgstat_twophase_postcommit(FullTransactionId fxid, uint16 info, pgstat_info->tab.counts_xact.tuples_inserted += rec->tuples_inserted; pgstat_info->tab.counts_xact.tuples_updated += rec->tuples_updated; pgstat_info->tab.counts_xact.tuples_deleted += rec->tuples_deleted; - pgstat_info->tab.truncdropped = rec->truncdropped; + /* see AtEOXact_PgStat_Relations() */ if (rec->truncdropped) { + pgstat_info->tab.truncdropped = true; /* forget live/dead stats seen by backend thus far */ pgstat_info->tab.counts_xact.delta_live_tuples = 0; pgstat_info->tab.counts_xact.delta_dead_tuples = 0; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 8b1547124..ea6b260d4 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -293,6 +293,60 @@ FROM prevstats AS pr; COMMIT; ----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- Pending stats are only flushed while the backend is idle, so run the +-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not +-- flushed in between. VACUUM cannot run in a procedure; if the stats happen +-- to get flushed between the INSERT and the VACUUM, that case still passes, +-- but doesn't test anything. +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$ +BEGIN + TRUNCATE stats_pending_trunc; + COMMIT; + INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); + COMMIT; + INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); + COMMIT; + DELETE FROM stats_pending_analyze WHERE id <= 4; + COMMIT; + ANALYZE stats_pending_analyze; +END +$$; +CALL stats_pending_proc(); +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; + relname | n_live_tup | n_dead_tup | n_mod_since_analyze +-----------------------+------------+------------+--------------------- + stats_pending_analyze | 6 | 4 | 0 + stats_pending_trunc | 3 | 0 | 13 + stats_pending_vacuum | 10 | 0 | 10 +(3 rows) + +DROP PROCEDURE stats_pending_proc(); +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; +----- -- Basic tests for pg_stat_all_indexes ----- CREATE TEMPORARY TABLE test_idx_tup_read AS diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 674637e17..d446a7fd9 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -153,6 +153,44 @@ FROM prevstats AS pr; COMMIT; +----- +-- Changes committed by this backend whose stats are still pending must not be +-- counted twice by VACUUM/ANALYZE, and a pending TRUNCATE must not be +-- forgotten when another transaction commits before the stats are flushed. +-- Pending stats are only flushed while the backend is idle, so run the +-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not +-- flushed in between. VACUUM cannot run in a procedure; if the stats happen +-- to get flushed between the INSERT and the VACUUM, that case still passes, +-- but doesn't test anything. +----- +CREATE TABLE stats_pending_trunc(id int); +CREATE TABLE stats_pending_analyze(id int); +CREATE TABLE stats_pending_vacuum(id int); +INSERT INTO stats_pending_trunc SELECT generate_series(1, 10); +SELECT pg_stat_force_next_flush(); +CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$ +BEGIN + TRUNCATE stats_pending_trunc; + COMMIT; + INSERT INTO stats_pending_trunc SELECT generate_series(1, 3); + COMMIT; + INSERT INTO stats_pending_analyze SELECT generate_series(1, 10); + COMMIT; + DELETE FROM stats_pending_analyze WHERE id <= 4; + COMMIT; + ANALYZE stats_pending_analyze; +END +$$; +CALL stats_pending_proc(); +INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); +VACUUM stats_pending_vacuum; +SELECT pg_stat_force_next_flush(); +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze + FROM pg_stat_user_tables + WHERE relname LIKE 'stats_pending_%' ORDER BY relname; +DROP PROCEDURE stats_pending_proc(); +DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; + ----- -- Basic tests for pg_stat_all_indexes ----- -- 2.43.0
v2-0002-Don-t-count-pending-inserts-in-ins_since_vacuum-a.patch (application/octet-stream, 5.5K) download From e3bd8b150af470ff3b2a7d9651350c5fa5e87d3d Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Date: Thu, 15 Oct 2026 08:47:11 +0300 Subject: [PATCH v2 2/2] Don't count pending inserts in ins_since_vacuum after VACUUM Like live_tuples and dead_tuples, VACUUM resets ins_since_vacuum while inserts committed earlier by the same backend may still be pending, and the next flush then added them back, so e.g. INSERT of N rows followed by VACUUM left n_ins_since_vacuum at N, which could trigger a needless insert-driven autovacuum. We can't simply forget the pending tuples_inserted, since it also feeds the cumulative tuples_inserted counter, so remember how many of them VACUUM has already covered and have the flush skip those for ins_since_vacuum. Author: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Reviewed-by: Ilya Staroverov <i.staroverov@ftdata.ru> Tested-by: Sebastian Webber <sebastian@swebber.me> Discussion: https://postgr.es/m/1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain --- src/backend/utils/activity/pgstat_relation.c | 10 +++++++++- src/include/pgstat.h | 2 ++ src/test/regress/expected/stats.out | 12 ++++++------ src/test/regress/sql/stats.sql | 2 +- 4 files changed, 18 insertions(+), 8 deletions(-) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index cfebd0cd6..9e6559596 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -279,6 +279,11 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, * stats have not been flushed yet. Forget the pending deltas for those * counters, else they would be added on top of the new values at the * next flush. A pending truncation reset is superseded as well. + * + * Likewise, the pending inserts must not count towards ins_since_vacuum, + * which is reset below. We can't just zero tuples_inserted, which also + * feeds the cumulative tuples_inserted counter, so remember how many of + * them the flush has to skip. */ if (pgstat_should_count_relation(rel)) { @@ -288,6 +293,8 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, pending->tab.counts_xact.delta_live_tuples = 0; pending->tab.counts_xact.delta_dead_tuples = 0; pending->tab.truncdropped = false; + pending->tab.ins_before_vacuum = + pending->tab.counts_xact.tuples_inserted; } /* Store the data in the table's hash table entry. */ @@ -993,7 +1000,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) * triggering for inserts more often than they maybe should, which is * probably not going to be common enough to be too concerned about here. */ - tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted; + tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted - + lstats->tab.ins_before_vacuum; tabentry->blocks_fetched += lstats->tab.counts.blocks_fetched; tabentry->blocks_hit += lstats->tab.counts.blocks_hit; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 187d82c96..6b6a705ab 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -236,6 +236,8 @@ typedef struct PgStat_RelationStatus struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */ PgStat_TableCounts counts; /* event counts to be sent */ PgStat_TableCountsXact counts_xact; /* transactional counts */ + /* pending tuples_inserted already covered by a VACUUM */ + PgStat_Counter ins_before_vacuum; } tab; /* index counters */ diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index ea6b260d4..ea7f47d63 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -334,14 +334,14 @@ SELECT pg_stat_force_next_flush(); (1 row) -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; - relname | n_live_tup | n_dead_tup | n_mod_since_analyze ------------------------+------------+------------+--------------------- - stats_pending_analyze | 6 | 4 | 0 - stats_pending_trunc | 3 | 0 | 13 - stats_pending_vacuum | 10 | 0 | 10 + relname | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum +-----------------------+------------+------------+---------------------+-------------------- + stats_pending_analyze | 6 | 4 | 0 | 10 + stats_pending_trunc | 3 | 0 | 13 | 3 + stats_pending_vacuum | 10 | 0 | 10 | 0 (3 rows) DROP PROCEDURE stats_pending_proc(); diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index d446a7fd9..fd30a09ce 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -185,7 +185,7 @@ CALL stats_pending_proc(); INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP PROCEDURE stats_pending_proc(); -- 2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-05 05:48  Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  1 sibling, 1 reply; 15+ messages in thread

From: Bertrand Drouvot @ 2026-11-05 05:48 UTC (permalink / raw)
  To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: pgsql-bugs@lists.postgresql.org

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

It looks like to me that those cases cases fall into the same category as [1]
which was ultimately not considered a bug.

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

So maybe only the TRUNCATE case deserves a fix?

[1]: https://postgr.es/m/xnd433j72ixptoydkifnleke43bvql4rxrw4ohnodx6hk4wcho%40m46ab2bt33jh#07b7a951d9a983...

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-05 07:07  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Staroverov Ilja @ 2026-11-05 07:07 UTC (permalink / raw)
  To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Hi Bertrand,

Thanks for the pointer. Yes, the VACUUM example in that thread has the
same cause. I agree with keeping stats updates batched, but I think
we can avoid this particular double count without changing that policy.

The patch only adjusts the pending counters of the backend running
VACUUM or ANALYZE. When it reports new live/dead estimates, it discards
its own pending deltas from earlier transactions, rather than adding
them on top of those estimates at the next flush. This does not require
more frequent flushes or access to another backend's pending stats.
The cumulative insert/update/delete counters are preserved.

pgstat_report_analyze() already subtracts the current transaction's
changes to avoid double counting at commit, and also subtracts pending
delta_dead_tuples. This is the same local accounting problem for changes
committed earlier by that backend. For mod_since_analyze, the patch
also discards the pending changed_tuples when ANALYZE resets the shared
counter. Otherwise an INSERT of N rows followed by ANALYZE can leave
n_mod_since_analyze at N after the pending stats are flushed, despite
there having been no subsequent changes.

Other backends' unflushed deltas can still make the estimates inaccurate.
I agree that solving that would be a different and much larger change.

> So maybe only the TRUNCATE case deserves a fix?

I'd still consider the local VACUUM/ANALYZE correction worthwhile. It
avoids reintroducing changes already covered by that backend's own
maintenance operation, including changes that can trigger another
autoanalyze or autovacuum, while retaining the existing flush policy.
I can separate the TRUNCATE fix so it can be reviewed independently.

--
Ilya Staroverov



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-12 02:41  João Rito <joaovictor.rito@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 1 reply; 15+ messages in thread

From: João Rito @ 2026-11-12 02:41 UTC (permalink / raw)
  To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Manu <manuelreyesbravo@gmail.com>; sebastian@swebber.me <sebastian@swebber.me>

Hi Ilya,

Thank you for the patch. I tried v2 (0001 and 0002 together, the cfbot
branch cf/7460, CF entry 7460) and would like to share what I saw. I
can send the scripts and the output if they are useful.

Setup
- Master at c1694b7 (11 Nov 2026) plus the two patches as one diff (4
files: pgstat_relation.c, pgstat.h, and the stats test files). `git
apply --check` was clean on that master.
- configure with --enable-cassert --enable-debug --enable-tap-tests
--without-icu, on Linux x86_64. I built the unpatched tree first, then
the patched tree. Both builds had 0 compiler warnings.
- A small note for others: my first patched build was incremental
without --enable-depend. Since pgstat.h changes a struct, I got stale
objects, "write past chunk end in PgStat Pending" and 27 regression
failures. After `make clean` and a full rebuild, none of this
happened, so I think it was my build and not the patch.

Tests that passed
- `make check` with the patch: all 243 tests passed, including stats.

Manual tests (temporary cluster, autovacuum off for the first runs; I
compared the unpatched and the patched build, and used
pg_stat_force_next_flush() before reading pg_stat_user_tables)
- INSERT 1000, then ANALYZE in the same session (autocommit):
unpatched gives n_live_tup 2000 and n_mod_since_analyze 1000; patched
gives 1000 and 0 (count(*) is 1000).
- INSERT 1000, then VACUUM: unpatched gives n_live_tup 2000 and
n_ins_since_vacuum 1000; patched gives n_live_tup 1000 and
n_ins_since_vacuum 0.
- INSERT 1000, DELETE 400, UPDATE 100 (separate transactions), then
VACUUM (ANALYZE): unpatched gives n_live_tup 1200 and n_dead_tup 500;
patched gives 600 and 0 (count(*) is 600). n_tup_ins/upd/del stay
1000/100/400 on both builds.
- INSERT 1000 and DELETE 400 in one transaction, then VACUUM
(ANALYZE): unpatched gives n_live_tup 1200 and n_dead_tup 400; patched
gives 600 and 0.
- TRUNCATE, then INSERT 300 in a later transaction, no VACUUM or
ANALYZE: unpatched n_live_tup is 1300; patched is 300 (count(*) is
300).
- pg_stat_reset() between the INSERT and the ANALYZE: patched gives
n_live_tup 1000 (correct). pg_stat_reset() after the ANALYZE and then
50 more rows: patched gives n_live_tup 50 and n_mod_since_analyze 50,
unpatched gives 1050 for both.
- Partitioned table with two leaves, INSERT 1000, ANALYZE of the
parent: patched gives n_live_tup 499 and 501 for the leaves (the
parent shows 0), n_mod_since_analyze 0; unpatched gave 998 and 1002
with n_mod_since_analyze 499 and 501.
- TRUNCATE inside a transaction with SAVEPOINT, INSERT 5, ROLLBACK TO,
COMMIT, then INSERT 300 later: patched gives n_live_tup 300 (count(*)
is 300), unpatched 1300.
- Same scripts with autovacuum on (naptime 1s), patched build only:
the numbers right after the flush were the same as above. After 5 s,
autovacuum had analyzed the subxact table once (autoanalyze_count 1,
n_mod_since_analyze 0, n_dead_tup 5).
- Autovacuum check, patched build only: a table with
autovacuum_vacuum_insert_threshold=100 and scale factor 0 (same for
analyze), INSERT 1000, manual VACUUM, flush. Right after the flush:
n_live_tup 1000, n_mod_since_analyze 1000, n_ins_since_vacuum 0. After
5 s: autovacuum_count 0 and autoanalyze_count 1. So no extra
insert-triggered autovacuum after the manual VACUUM, which is what I
expected from 0002.
- The server did not crash during these runs (assert-enabled build). I
counted TRAP/assert/PANIC lines in the server log: 0 in the three
repro/edge runs (unpatched, patched with autovacuum off, patched with
autovacuum on). I did not grep the log of the separate autovacuum
check.

Questions and comments
- After TRUNCATE and a later INSERT 300 (case 5 above), the patched
build gives n_ins_since_vacuum 300 but n_mod_since_analyze 1300. In
the subxact case it is n_ins_since_vacuum 305 and n_mod_since_analyze
1300. I do not know if n_mod_since_analyze should also restart after a
truncate. It looks the same on the unpatched build, so I assume it is
outside this patch, but I wanted to ask in case you already thought
about it. I think this overlaps the TRUNCATE/subtransaction cases Manu
raised, which you plan to handle separately.
- In the partitioned table case the n_tup_ins and n_live_tup of the
parent stay 0 on both builds. This is only an observation.
- Bertrand raised upthread that these cases may be like the one in [1]
(not treated as a bug). I do not have an opinion on that, but the
numbers above show the first VACUUM/ANALYZE case clearly (2x
n_live_tup, and n_mod_since_analyze equal to the inserted rows right
after ANALYZE). I think splitting out the TRUNCATE fix, as you
offered, is a good idea.
- I did not review the code of pgstat_report_vacuum() and
pgstat_report_analyze() in depth.

Not tested
- 0001 and 0002 separately (I only tested them together). Each
scenario ran once, with no repeats and no variation in flush timing.
- Whether the new regression tests fail without the fix (I did not run
the patched tests against the unpatched code; Sebastian reported
upthread that the v1 stats test fails on unpatched master), and the
unpatched `make check`.
- The autovacuum scenario on the unpatched build (I only ran it on the
patched build), and Sebastian's pending-stats-autovacuum.sh script.
- Two-phase commit (PREPARE TRANSACTION and COMMIT PREPARED), TOAST,
matviews, ANALYZE with a column list, database-wide VACUUM ANALYZE,
relcache invalidation between statements.
- Several backends changing the same table at the same time.
- Back-patching and branches 14 to 18.
- make check-world, the isolation and other TAP suites (only make
check was run).
- Meson build, non-assert builds, Windows, 32-bit, other compilers, ICU builds.
- Code style (pgindent), documentation, and a full code review.

[1] https://postgr.es/m/xnd433j72ixptoydkifnleke43bvql4rxrw4ohnodx6hk4wcho%40m46ab2bt33jh#07b7a951d9a983...

Thanks again, and please let me know if you want me to rerun anything
with different settings.

Best regards,
João Rito

Em qua., 11 de nov. de 2026 às 23:31, Staroverov Ilja
<i.staroverov@ftdata.ru> escreveu:
>
> Hi Bertrand,
>
> Thanks for the pointer. Yes, the VACUUM example in that thread has the
> same cause. I agree with keeping stats updates batched, but I think
> we can avoid this particular double count without changing that policy.
>
> The patch only adjusts the pending counters of the backend running
> VACUUM or ANALYZE. When it reports new live/dead estimates, it discards
> its own pending deltas from earlier transactions, rather than adding
> them on top of those estimates at the next flush. This does not require
> more frequent flushes or access to another backend's pending stats.
> The cumulative insert/update/delete counters are preserved.
>
> pgstat_report_analyze() already subtracts the current transaction's
> changes to avoid double counting at commit, and also subtracts pending
> delta_dead_tuples. This is the same local accounting problem for changes
> committed earlier by that backend. For mod_since_analyze, the patch
> also discards the pending changed_tuples when ANALYZE resets the shared
> counter. Otherwise an INSERT of N rows followed by ANALYZE can leave
> n_mod_since_analyze at N after the pending stats are flushed, despite
> there having been no subsequent changes.
>
> Other backends' unflushed deltas can still make the estimates inaccurate.
> I agree that solving that would be a different and much larger change.
>
> > So maybe only the TRUNCATE case deserves a fix?
>
> I'd still consider the local VACUUM/ANALYZE correction worthwhile. It
> avoids reintroducing changes already covered by that backend's own
> maintenance operation, including changes that can trigger another
> autoanalyze or autovacuum, while retaining the existing flush policy.
> I can separate the TRUNCATE fix so it can be reviewed independently.
>
> --
> Ilya Staroverov
>






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-12 04:43  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: João Rito <joaovictor.rito@gmail.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Staroverov Ilja @ 2026-11-12 04:43 UTC (permalink / raw)
  To: João Rito <joaovictor.rito@gmail.com>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Manu <manuelreyesbravo@gmail.com>; sebastian@swebber.me <sebastian@swebber.me>

Hi Joao,

Thanks for testing both builds and for spelling out the coverage and
limitations. Yes, please send the scripts and output, particularly for
the TRUNCATE/SAVEPOINT and pg_stat_reset() cases.

On n_mod_since_analyze, 1300 is consistent with the current behavior,
since TRUNCATE does not reset that counter. Whether it should do so
was discussed in 2021 [1], with a patch to reset it and discussion of
whether that would delay a useful autoanalyze after TRUNCATE. I would
not treat this observation as a new bug or propose changing that policy
as part of this patch. It is distinct from losing a pending TRUNCATE
flag when a later transaction commits.

The 305 for n_ins_since_vacuum is also consistent with the existing
accounting of aborted inserts. pgstat_relation_flush_cb() uses
tuples_inserted for this counter and explicitly notes that it includes
aborted inserts. I'd like to see the exact SAVEPOINT placement before
relating that result to Manu's reproducers. It does not by itself show
the same problem.

The parent's zero counters are expected here too. Inserts are counted
on the leaf partitions, and ANALYZE of a partitioned parent explicitly
reports zero live/dead tuples to the cumulative stats system. The
parent's pg_class.reltuples estimate is maintained separately.

[1] https://www.postgresql.org/message-id/flat/CAD21AoAwdh5FqoVobm9qbQUR%3DtJjqnX8u5-d1RVFS1V%3DEexFMw%4...

--
Ilya Staroverov



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-12 11:23  João Rito <joaovictor.rito@gmail.com>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 1 reply; 15+ messages in thread

From: João Rito @ 2026-11-12 11:23 UTC (permalink / raw)
  To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Manu <manuelreyesbravo@gmail.com>; sebastian@swebber.me <sebastian@swebber.me>

Hi Ilja,

Thanks for the quick reply and for the link to the 2021 thread. I agree
that n_mod_since_analyze staying at 1300 after TRUNCATE follows the
existing policy and is not something this patch should change.

Here are the scripts and output. Build: master c1694b7, with and without
v2, --enable-cassert --enable-debug, autovacuum=off, a fresh initdb for
each run, all statements run from one psql -f session.

E4 (TRUNCATE and SAVEPOINT):

  CREATE TABLE e4(id int);
  INSERT INTO e4 SELECT generate_series(1,1000);
  SELECT pg_stat_force_next_flush();
  BEGIN;
  TRUNCATE e4;
  SAVEPOINT s;
  INSERT INTO e4 SELECT generate_series(1,5);
  ROLLBACK TO s;
  COMMIT;
  INSERT INTO e4 SELECT generate_series(1,300);
  SELECT pg_stat_force_next_flush();
  SELECT n_tup_ins, n_live_tup, n_mod_since_analyze, n_ins_since_vacuum
    FROM pg_stat_user_tables WHERE relname = 'e4';

              n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
  unpatched     1305    |    1300    |     1300     |     1305
  v2            1305    |     300    |     1300     |      305

count(*) is 300. So the TRUNCATE comes before the SAVEPOINT, and the 5
rows are inserted inside the savepoint and rolled back. The 305 is the
300 committed rows plus the 5 aborted inserts, which matches what you
said. n_live_tup is correct with v2.

pg_stat_reset() cases:

E1, reset between INSERT and ANALYZE:

  CREATE TABLE e1(id int);
  INSERT INTO e1 SELECT generate_series(1,1000);
  SELECT pg_stat_reset();
  ANALYZE e1;
  SELECT pg_stat_force_next_flush();

              n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
  unpatched     1000    |    2000    |     1000     |     1000
  v2            1000    |    1000    |        0     |     1000

count(*) is 1000. Here v2 fixes the double count.

E2, reset after ANALYZE, then 50 more rows:

  CREATE TABLE e2(id int);
  INSERT INTO e2 SELECT generate_series(1,1000);
  ANALYZE e2;
  SELECT pg_stat_reset();
  INSERT INTO e2 SELECT generate_series(1,50);
  SELECT pg_stat_force_next_flush();

              n_tup_ins | n_live_tup | mod_since_an | ins_since_vac
  unpatched     1050    |    1050    |     1050     |     1050
  v2            1050    |      50    |       50     |     1050

count(*) is 1050. In my first mail I only gave the 50 vs 1050 and did
not explain it, sorry. Looking closer, I don't think this is a problem
with v2. With v2, ANALYZE takes in the 1000 pending inserts, and then
pg_stat_reset() clears the value ANALYZE stored, so only the 50 new rows
remain. Without the patch, the 1000 pending inserts reach shared stats
only after the reset, so the result happens to match count(*). To check
this, I ran two variants on v2:

- E2b: the same, but with pg_stat_force_next_flush() and one more
  statement between the INSERT and ANALYZE, so the 1000 inserts are
  flushed before ANALYZE. Result: n_tup_ins 50, n_live_tup 50,
  n_mod_since_analyze 50, n_ins_since_vacuum 50.
- E2c: the same as E2, but ANALYZE runs in a second session. Result:
  1050 for all four columns.

So with v2, E2 gives the same n_live_tup as when the inserts are flushed
before ANALYZE. The only odd part is that n_tup_ins and
n_ins_since_vacuum are still 1050 while n_live_tup is 50, because only
the live/dead/mod counters are taken in by ANALYZE. After a reset these
numbers are approximate anyway, so I think this is fine, maybe worth a
line in the commit message.

E3 (partitioned parent): as you said, the parent shows zero, and that is
expected.

No assertion failures or warnings in the server logs in any run.

The runner script, in case it helps:

  B=$HOME/inst7460/bin; D=$HOME/d7460
  rm -rf $D; $B/initdb -D $D
  echo "port=5492
  autovacuum=off
  unix_socket_directories='/tmp'
  track_counts=on" >> $D/postgresql.conf
  $B/pg_ctl -D $D -l $D/log -w start
  $B/psql -X -h /tmp -p 5492 -d postgres -f edge.sql
  $B/pg_ctl -D $D -m fast -w stop

Regards,
Joao Rito


Em qui., 12 de nov. de 2026 às 01:43, Staroverov Ilja
<i.staroverov@ftdata.ru> escreveu:
>
> Hi Joao,
>
> Thanks for testing both builds and for spelling out the coverage and
> limitations. Yes, please send the scripts and output, particularly for
> the TRUNCATE/SAVEPOINT and pg_stat_reset() cases.
>
> On n_mod_since_analyze, 1300 is consistent with the current behavior,
> since TRUNCATE does not reset that counter. Whether it should do so
> was discussed in 2021 [1], with a patch to reset it and discussion of
> whether that would delay a useful autoanalyze after TRUNCATE. I would
> not treat this observation as a new bug or propose changing that policy
> as part of this patch. It is distinct from losing a pending TRUNCATE
> flag when a later transaction commits.
>
> The 305 for n_ins_since_vacuum is also consistent with the existing
> accounting of aborted inserts. pgstat_relation_flush_cb() uses
> tuples_inserted for this counter and explicitly notes that it includes
> aborted inserts. I'd like to see the exact SAVEPOINT placement before
> relating that result to Manu's reproducers. It does not by itself show
> the same problem.
>
> The parent's zero counters are expected here too. Inserts are counted
> on the leaf partitions, and ANALYZE of a partitioned parent explicitly
> reports zero live/dead tuples to the cumulative stats system. The
> parent's pg_class.reltuples estimate is maintained separately.
>
> [1] https://www.postgresql.org/message-id/flat/CAD21AoAwdh5FqoVobm9qbQUR%3DtJjqnX8u5-d1RVFS1V%3DEexFMw%4...
>
> --
> Ilya Staroverov
>






^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: VACUUM/ANALYZE double-count the backend's own pending table stats
@ 2026-11-13 07:34  Staroverov Ilja <i.staroverov@ftdata.ru>
  parent: João Rito <joaovictor.rito@gmail.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Staroverov Ilja @ 2026-11-13 07:34 UTC (permalink / raw)
  To: João Rito <joaovictor.rito@gmail.com>; +Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Manu <manuelreyesbravo@gmail.com>; sebastian@swebber.me <sebastian@swebber.me>

Hi Joao,

Thanks for the scripts and the additional checks. The SAVEPOINT
placement clarifies the 305, and the E2b/E2c comparisons help explain
the reset case. I agree that these results do not indicate an
additional issue with v2.

--
Ilya Staroverov



^ permalink  raw  reply  [nested|flat] 15+ messages in thread


end of thread, other threads:[~2026-11-13 07:34 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-14 10:39 VACUUM/ANALYZE double-count the backend's own pending table stats (n_live_tup = 2*N after INSERT + ANALYZE) Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-14 18:47 ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Manu <manuelreyesbravo@gmail.com>
2026-10-14 20:28   ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Sebastian Webber <sebastian@swebber.me>
2026-10-14 22:53     ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Manu <manuelreyesbravo@gmail.com>
2026-10-15 06:08       ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-15 07:11         ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Manu <manuelreyesbravo@gmail.com>
2026-10-15 07:48           ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-15 07:52             ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Manu <manuelreyesbravo@gmail.com>
2026-10-15 08:05               ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>
2026-11-05 05:48 ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
2026-11-05 07:07   ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>
2026-11-12 02:41     ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats João Rito <joaovictor.rito@gmail.com>
2026-11-12 04:43       ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>
2026-11-12 11:23         ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats João Rito <joaovictor.rito@gmail.com>
2026-11-13 07:34           ` Re: VACUUM/ANALYZE double-count the backend's own pending table stats Staroverov Ilja <i.staroverov@ftdata.ru>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox