Re: [BUG / PATCH] PG19: VACUUM misestimates reltuples (often 0) after on-access VM setting

On Wed, Oct 21, 2026 at 2:37 AM Vaibhav Jain <jainva(at)google(dot)com> wrote:
>
> In PostgreSQL 19 (reproduced on REL_19_STABLE at 48b43081e55 and master),
> VACUUM can misestimate pg_class.reltuples -- often setting reltuples = 0
> and resetting pg_stat_all_tables.n_live_tup to 0 on a newly populated table --
> if a read-only query runs between loading the table and the first VACUUM. It
> can also corrupt an existing reltuples estimate that was just computed by
> ANALYZE.

Thanks for the report. While reviewing the on-access VM setting
feature, an LLM did flag this misestimation but because much of it was
possible with repeated vacuums before and because a subsequent analyze
would fix many cases after, I didn't worry about it and thought of it
as a future enhancement.

The cases you've pointed out where a subsequent analyze doesn't fix
the estimate are problematic, though. So, I think we do have to fix
it. And, of course, some of the cases wouldn't ordinarily be hit
without on-access VM setting.

> 3. Proposed fix and design trade-offs
> -------------------------------------
> The attached patch fixes these issues as follows:
>
> 1. Count PageIsNew blocks separately (`vacrel->scanned_new_pages`) in
> lazy_scan_new_or_empty() (and contrib/pgstattuple's statapprox_heap()) and
> pass `scanned_new_pages` to vac_estimate_reltuples(). Exclude those
> uninitialized pages from both the sampled pages (`sampled_pages =
> scanned_pages - scanned_new_pages`) and sampleable pages (`sampleable_pages
> = total_pages - scanned_new_pages`), since they are never marked
> all-visible and are not representative of the relation's initialized pages.
>
> 2. When prior tuple density is unknown (`old_rel_tuples < 0 || old_rel_pages == 0`)
> and VACUUM sampled `<= 1` initialized page or `< 2%` of sampleable pages,
> return `-1` (unknown) rather than extrapolating from the tail or returning
> `0` for a previously empty relation.
>
> 3. Restrict the `sampled_pages <= 1` early return to relations whose size has
> not changed (`old_rel_pages == total_pages`), matching the `< 2%` guard, so
> that relations that grew since the last VACUUM/ANALYZE still scale using
> `old_density`.
>
> 4. Pass `vacrel->new_live_tuples` directly to pgstat_report_vacuum() and keep
> the existing `tabentry->live_tuples` when `livetuples < 0`, so a VACUUM
> that could not estimate live tuples does not wipe `n_live_tup` to 0.

I think 2-4 can be done in a preliminary commit and 1 in its own commit.

We'll need to preserve ABI compatibility of vac_estimate_reltuples()
in 19 since it is public. It's a bit unfortunate, but I think we can
just add and use a wrapper function. Existing users of
vac_estimate_reltuples() can upgrade to the new extended version if
they so choose. That's what I've done in the attached. On master, I'll
just modify vac_estimate_reltuples() directly.

A fun side effect of this work is that we can now set new pages in the
VM during vacuum. I think we can do a nice follow-on commit to master
to do this. It seems like a win.

> Note on returning -1 vs. PG18 behavior:
> When a table has no prior statistics (`reltuples = -1` or `relpages = 0`) and
> on-access pruning marks nearly all pages all-visible before the first VACUUM,
> returning `-1` means `pg_class.reltuples` remains `-1` after that VACUUM
> (which vac_estimate_reltuples() already did in PG19 when `scanned_pages <= 1`
> and `old_rel_tuples == -1`), whereas in PG18 the first VACUUM always scanned
> all pages and set `reltuples` to the exact count. With `relpages > 0` and
> `reltuples = -1`, table_block_relation_estimate_size() falls back to its
> relpages / tuple-width estimate and autovacuum schedules an ANALYZE (since
> `n_live_tup` and `n_mod_since_analyze` are preserved).
>
> Two alternatives could avoid leaving `reltuples = -1` after VACUUM:
> (a) Disable VM page-skipping in VACUUM (`vacrel->skipwithvm = false`) when
> `rel->rd_rel->reltuples < 0 || rel->rd_rel->relpages == 0`, forcing the
> first VACUUM on an unanalyzed table to scan all pages as in PG18, at the
> cost of giving up the I/O savings of b46e1e54d/378a216187a on the first
> VACUUM when ANALYZE has not run first.
> (b) Fall back to pgstat's `live_tuples` count when vac_estimate_reltuples()
> returns `-1`, though that would introduce a dependency from
> `pg_class.reltuples` on cumulative stats.

I think it's fine to set reltuples back to -1 when we no longer have a
good estimate. I don't think it's a problem at all. a) throws away one
of the most common useful cases of on-access VM setting and b) isn't a
viable dependency. The planner can cope with unknown reltuples until
the next analyze. And remember we only set reltuples from 0 -> -1 when
a table was empty when it was last vacuumed or analyzed.

Attached is what I propose to backpatch to 19. It is basically the
same as what you proposed but split up a bit differently.

- Melanie