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

On Fri, Nov 13, 2026 at 2:08 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> On Fri, 13 Nov 2026, Melanie Plageman <melanieplageman(at)gmail(dot)com> wrote:
> > So I did mean for a sentinel/unknown value to be returned here.
> > However, it looks like the value should be 0 and not -1.
>
> Sorry if that wasn't clear from my email, I didn't question the value
> changing, only the part where the -1 is visible to users, as that
> seems like a preexisting bug that now got one more case.
>
> > So, to clamp it, we should save the return value of
> > vac_estimate_reltuples() in a double and clamp it to 0 before
> > assigning to stat->tuple_count.
>
> Yes, either that or tuple_count should be signed, but the current way
> doesn't seem to be good.

Here's a reproducer for how you can get -1 for approx_tuple_count even
in versions <= 18

BEGIN;
CREATE TABLE t(id int);
COPY t FROM PROGRAM 'printf "1\n"' WITH (FREEZE);
COMMIT;
SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t');

But the documentation doesn't say anything about how "unknown" is
represented. In that sense, there is no documented contract about
whether 0 or -1 means unknown. I would find it much clearer to return
-1 when it is unknown, but we do not want to change this in
backbranches. We only want to make it consistent. Though, people may
be getting -1 today, as we can see from the repro. From the code it's
clear that the intent was for the value to be clamped to 0. None of
the other pgstattuple_approx() fields return -1 when we scanned 0 or 1
pages. They do a variety of other things that are inconsistent and
confusing, but they don't return -1.

As for other places in Postgres, reltuples in pg_class is -1 when
unknown, but cumulative relation stat counters return 0 when there
isn't yet a stats entry. But these are not stats functions like
pgstattuple_approx().

My current inclination is to patch 15-master so pgstattuple_approx()
returns 0 instead of -1, which is consistent with what the code was
trying to do and document this. Then propose returning either NULL or
-1 starting in master when we didn't scan enough pages to get good
estimates but to do so for all the relevant return values from
pgstattuple_approx().

> One more nit for the original v2-0001:
>
> + * value when at most one page was scanned. When the relation size has
> + * changed, use the old density to account for the added or removed pages
> + * instead of preserving a stale tuple count.
> *
> * (Note: we might be returning -1 here.)
> */
>
> That note is now stale, with the condition moved before it.

Thanks! I can fix this when I commit the final patch touching
vac_estimate_reltuples().

- Melanie