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

Hi,

On Fri, Nov 13, 2026 at 2:13 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> On Fri, Nov 13, 2026 at 12:16 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
> >
> > 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().
>
> I've pushed the last patch in the original series (the one excluding
> new pages from the extrapolation calculation), however, I'm hesitant
> to make this change (the -1/0 change) until we have consensus on the
> desired behavior and review of the docs change. I'm pretty sure the
> right immediate step is to make the 0 clamp work, though. The downside
> of not committing this now is that in 19, getting -1 from
> pgstat_approx() is more common than in older releases and 19 GA code
> freeze is tomorrow. If we wait and commit the fix later, it will
> change in 19.1 which might be confusing. However, it's probably better
> to wait and get consensus and not change it twice in two versions.
>
> Attached is what I propose doing and backpatching through 15.

I haven't chimed in on this thread previously, but I assume that a
review is still welcome to help get this addressed before GA.

Reading back through the thread, and specifically this issue, I agree
with your earlier sentiment that if we could, NULL or -1 is the better
result here, but I see why we can't do that on the released back
branches.

Specifically, as someone who writes software that reads from
statistics views and related functions in a Go program, suddenly
getting NULL where it was an integer before would cause an error in
the client program, so we shouldn't do that. I do think we should
return NULL on master though, if we can - at least to me that's the
most logical value.

Theoretically, one could argue that we should fix this in 19 so we
return NULL - its not a practical problem there in terms of breaking
client programs (just yet), and the code change is small - but not
sure if that is too much to ask for at this point.

Otherwise, +1 on the patch as is. I think the documentation change
looks good as well.

Thanks,
Lukas

--
Lukas Fittl