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