VACUUM/ANALYZE double-count the backend's own pending table stats (n_live_tup = 2*N after INSERT + ANALYZE)

Поиск
Список
Период
Сортировка
Искать

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>
Дата:
Вложения:
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

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Вложения:
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

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Sebastian Webber <sebastian@swebber.me>
Дата:
Вложения:
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

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
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


Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
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

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Дата:
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#07b7a951d9a983ce9432d6bf86979ff2

Regards,

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


Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
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

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
João Rito <joaovictor.rito@gmail.com>
Дата:
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#07b7a951d9a983ce9432d6bf86979ff2

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


Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
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%40mail.gmail.com

--
Ilya Staroverov

Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
João Rito <joaovictor.rito@gmail.com>
Дата:
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
 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%40mail.gmail.com
>
> --
> Ilya Staroverov
>


Re: VACUUM/ANALYZE double-count the backend's own pending table stats

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
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

FAQ