[BUG / PATCH] PG19: VACUUM misestimates reltuples (often 0) after on-access VM setting
Hi hackers,
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.
1. Root cause
-------------
vac_estimate_reltuples() assumes that a relation with no prior tuple-density
estimate (old_rel_tuples = -1, or old_rel_pages = 0) cannot have most of its
pages marked all-visible in the visibility map, and therefore scales the
tuple
density of scanned_pages across total_pages whenever scanned_pages > 1.
Since commits b46e1e54d ("Allow on-access pruning to set pages
all-visible"),
378a216187a ("Set pd_prune_xid on insert"), and 4a2f5533f3c ("Correct
on-access VM setting heuristic"), a read-only query after an ordinary COPY,
INSERT, or CREATE TABLE AS marks all full pages all-visible in the VM before
VACUUM ever runs. When the first VACUUM runs, it skips nearly the entire
relation via the VM and scans only the tail end:
* at most the last, partially filled data page, plus
* any uninitialized (PageIsNew / all-zeroes) pages left behind at the end
of
the relation by bulk extension (up to 64 blocks).
lazy_scan_new_or_empty()
intentionally does not mark PageIsNew pages all-visible in the VM, so
every VACUUM scans them and counts them in vacrel->scanned_pages with 0
tuples.
This produces four failure modes (plus a pgstat side effect):
Mode 1 (Never-vacuumed / unanalyzed table -> reltuples = 0 or tiny
fraction):
When a newly loaded table is read before its first VACUUM, VACUUM skips all
the all-visible data pages and scans only the tail page(s) and any trailing
PageIsNew blocks from bulk extension. vac_estimate_reltuples() then
extrapolates that zero-heavy tail sample across the entire relation, setting
reltuples to 0 (or a tiny fraction of the true row count). Because
old_rel_pages then matches total_pages, subsequent VACUUMs hit the < 2% /
<= 1 page guard and preserve the wrong reltuples value indefinitely.
Mode 2 (Table vacuumed while empty [relpages=0, reltuples=0] -> reltuples =
0
and n_live_tup reset to 0):
If a table was previously vacuumed while empty and is later populated and
read, VACUUM either extrapolates 0 from the PageIsNew tail or hits the
`scanned_pages <= 1` rule and returns `old_rel_tuples` (0). Furthermore,
whenever vac_estimate_reltuples() returns -1 (for instance, when
scanned_pages == 1), heap_vacuum_rel() clamps `Max(vacrel->new_live_tuples,
0)`
when calling pgstat_report_vacuum(), wiping pg_stat_all_tables.n_live_tup
back to 0.
Mode 3 (Table with valid ANALYZE stats -> reltuples drops sharply on
VACUUM):
Even when ANALYZE is run right after a bulk load, ANALYZE includes any
trailing PageIsNew blocks in old_rel_pages when recording old_rel_tuples.
When a subsequent read marks the populated pages all-visible, VACUUM skips
the data pages and scans only the trailing PageIsNew blocks (which contain 0
tuples and can easily exceed 2% of total_pages, defeating the 2% stability
guard). vac_estimate_reltuples() then applies the diluted old_density to
only the unscanned data pages and adds 0 scanned tuples -- effectively
double-counting the empty PageIsNew pages and sharply reducing the reltuples
estimate that ANALYZE just computed.
Mode 4 (Table with valid ANALYZE stats grows -> reltuples stays stale):
When a table with prior statistics grows and a read-only query marks the
newly added pages all-visible before VACUUM runs, VACUUM may scan only a
single page. Because `if (scanned_pages <= 1) return old_rel_tuples;` does
not check whether `old_rel_pages == total_pages`, VACUUM leaves reltuples
unchanged at the pre-growth value instead of scaling by old_density.
2. Minimal reproduction
-----------------------
-- 1. Never-vacuumed table (4,000 rows): reltuples becomes 0 (PG18: 4000)
CREATE TABLE repro_new WITH (autovacuum_enabled = off) AS
SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
SELECT count(*) FROM repro_new;
VACUUM repro_new;
SELECT relpages, reltuples FROM pg_class WHERE oid = 'repro_new'::regclass;
-- PG19: relpages = 69, reltuples = 0 (EXPLAIN estimates rows=1)
-- PG18: relpages = 69, reltuples = 4000
-- 2. Empty-vacuumed table: reltuples and n_live_tup both become 0 (PG18:
4000)
CREATE TABLE repro_empty (a int, b text) WITH (autovacuum_enabled = off);
VACUUM repro_empty;
INSERT INTO repro_empty
SELECT g, repeat('x', 100) FROM generate_series(1, 4000) g;
SELECT count(*) FROM repro_empty;
SELECT pg_stat_force_next_flush();
VACUUM repro_empty;
SELECT pg_stat_force_next_flush();
SELECT c.relpages, c.reltuples, s.n_live_tup
FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.oid = 'repro_empty'::regclass;
-- PG19: relpages = 69, reltuples = 0, n_live_tup = 0
-- PG18: relpages = 69, reltuples = 4000, n_live_tup = 4000
-- 3. Post-ANALYZE drift: reltuples drops from 4000 to 2156 (PG18: 4000)
CREATE TABLE repro_analyzed WITH (autovacuum_enabled = off) AS
SELECT g AS a, repeat('x', 100) AS b FROM generate_series(1, 4000) g;
ANALYZE repro_analyzed;
SELECT count(*) FROM repro_analyzed;
VACUUM repro_analyzed;
SELECT relpages, reltuples FROM pg_class WHERE oid =
'repro_analyzed'::regclass;
-- PG19: relpages = 69, reltuples = 2156
-- PG18: relpages = 69, reltuples = 4000
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.
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.
Thoughts on whether returning `-1` (as in the attached patch) is preferred,
or
whether we should also do (a) or (b)? If either (a) or (b) is needed, I can
share separate patches for them.
Thanks,
Vaibhav Jain
On Wed, Oct 21, 2026 at 2:37 AM Vaibhav Jain <jainva@google.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
Attachments:
t254309_2v2-0001-Avoid-unreliable-VACUUM-reltuples-estimates.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Avoid-unreliable-VACUUM-reltuples-estimates.patchDownload+22-13
v2-0002-Exclude-new-pages-from-VACUUM-reltuples-estimates.patchtext/x-patch; charset=US-ASCII; name=v2-0002-Exclude-new-pages-from-VACUUM-reltuples-estimates.patchDownload+205-22
On Wed, Nov 11, 2026 at 6:51 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Wed, Oct 21, 2026 at 2:37 AM Vaibhav Jain <jainva@google.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.
...
I think 2-4 can be done in a preliminary commit and 1 in its own commit.
I've pushed several preliminary commits to 19 and master and left only
the commit excluding new pages from the estimate used to extrapolate
reltuples. That is the one that changes the API for
vac_estimate_reltuples(). I'm going to check with RMT (copied here)
before pushing that but because code freeze for 19 GA isn't until
Saturday, I think we could possibly push this as a change to
vac_estimate_reltuples instead of adding a wrapper function and
leaving the existing API.
RMT, are you okay with me pushing a function signature change (adding
a parameter) to vac_estimate_reltuples() in a commit tomorrow before
the 19.0 freeze?
- Melanie
Hello
+ /*
+ * With no prior density, a tiny non-random sample is not sufficient to
+ * estimate the relation's tuple count. This can happen when on-access
+ * pruning marks most pages all-visible before the first VACUUM.
+ */
+ if (old_rel_tuples < 0 || old_rel_pages == 0)
+ {
+ if (scanned_pages <= 1 ||
+ scanned_pages < (double) total_pages * 0.02)
+ return -1;
+
+ return floor((scanned_tuples / scanned_pages) * total_pages + 0.5);
+ }
and
stat->tuple_count = vac_estimate_reltuples(rel, nblocks, p.scanned,
+ p.scanned_new,
stat->tuple_count);
Is -1 a valid value for approx_tuple_count?
CREATE EXTENSION pgstattuple;
CREATE TABLE t (id int, pad text) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT g, repeat('x', 100) FROM generate_series(1, 40000) g;
SELECT count(*) FROM t;
UPDATE t SET pad = 'y' WHERE id IN (1, 20000);
SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t');
Before 1a001d930ee (the already committed part) this shows approx_tuple_count 35420 to me, after -1.
Without the UPDATE, it also returns -1 and already did so before, so this is not entirely new, but I don't see this being mentioned anywhere, and this suggests that it is unintended:
/* It's not clear if we could get -1 here, but be safe. */
stat->tuple_count = Max(stat->tuple_count, 0);
On Thu, Nov 12, 2026 at 5:51 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
Is -1 a valid value for approx_tuple_count?
CREATE EXTENSION pgstattuple;
CREATE TABLE t (id int, pad text) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT g, repeat('x', 100) FROM generate_series(1, 40000) g;
SELECT count(*) FROM t;
UPDATE t SET pad = 'y' WHERE id IN (1, 20000);
SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t');Before 1a001d930ee (the already committed part) this shows
approx_tuple_count 35420 to me, after -1.
...
Without the UPDATE, it also returns -1 and already did so before, so
this is not entirely new, but I don't see this being mentioned
anywhere
The pgstattuple_approx() after the UPDATE only scans 2 pages (if run
before the update, it scans 0 or 1 pages) because the count(*) sets
everything all-visible in the VM and pgstattuple skips those pages.
The intent of 1a001d930ee was to avoid extrapolating from such small
samples, which, after my work to set the VM on-access, end up
happening more often because pgstattuple and vacuum skip all-visible
pages. 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.
(In fact, if you go back and try this on PG 18, you'll notice that
approx_tuple_count is 40000 before or after the UPDATE because the
count(*) doesn't set the pages all-visible)
I didn't see the result of vac_estimate_reltuples() (which returns a
double) was being stored in a uint64 in statapprox_heap(). This seems
like it was already wrong since it was always possible for
vac_estimate_reltuples() to return -1. That was then getting stored as
UINT64_MAX in stat->tuple_count and then SQL expects bigint so I guess
it was converting it back to -1. An altogether undesirable
signed/unsigned conversion mess.
This ill-fated part you pointed out:
/* It's not clear if we could get -1 here, but be safe. */
stat->tuple_count = Max(stat->tuple_count, 0);
happens after we have already converted the value to unsigned (too
late). 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.
- Melanie
On Thu, Nov 12, 2026 at 8:51 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
Attached is what I propose to backpatch to 19. It is basically the
same as what you proposed but split up a bit differently.
+ if (old_rel_tuples < 0 || old_rel_pages == 0)
Should this use old_rel_tuples <= 0?
After DELETE followed by VACUUM (TRUNCATE false), an empty table can
have reltuples = 0 and relpages > 0. After repopulating it, on-access
pruning can mark most pages all-visible, and VACUUM can preserve the
old zero estimate even though the table contains rows.
For example:
---------------------------------
SET vacuum_max_eager_freeze_failure_rate = 0;
CREATE TABLE repro (v text) WITH (autovacuum_enabled = false);
INSERT INTO repro SELECT repeat('x', 300) FROM generate_series(1, 100000);
DELETE FROM repro;
VACUUM (TRUNCATE false) repro;
SELECT relpages, reltuples FROM pg_class WHERE oid = 'repro'::regclass;
INSERT INTO repro SELECT repeat('x', 300) FROM generate_series(1, 100000);
SELECT count(*) FROM repro;
VACUUM repro;
SELECT relpages, reltuples FROM pg_class WHERE oid = 'repro'::regclass;
---------------------------------
Regards,
--
Fujii Masao
On Fri, 13 Nov 2026, Melanie Plageman <melanieplageman@gmail.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.
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.
On Fri, Nov 13, 2026 at 2:08 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
On Fri, 13 Nov 2026, Melanie Plageman <melanieplageman@gmail.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
On Thu, Nov 12, 2026 at 8:00 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Thu, Nov 12, 2026 at 8:51 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:+ if (old_rel_tuples < 0 || old_rel_pages == 0)
Should this use old_rel_tuples <= 0?
This condition is meant to catch the case where pg_class.reltuples or
pg_class.relpages have not been changed from the defaults (-1 and 0
respectively). The test condition is the same as it previously was
if (old_rel_tuples < 0 || old_rel_pages == 0)
but with additional logic in the if statement.
After DELETE followed by VACUUM (TRUNCATE false), an empty table can
have reltuples = 0 and relpages > 0. After repopulating it, on-access
pruning can mark most pages all-visible, and VACUUM can preserve the
old zero estimate even though the table contains rows.
This case works the same as before the commits I made yesterday. The
first vacuum initializes reltuples and relpages, so the second vacuum
after repopulation doesn't hit the code block for when they are
uninitialized. The second vacuum scans too few pages to extrapolate
with and the number of pages hasn't changed, so it just uses the old
value. In this case, that's zero. Even if we did change the conditions
as you suggest, that would only allow it to set reltuples to -1,
meaning unknown. Vacuum hasn't scanned enough pages to make a new
estimate for reltuples.
- Melanie
On Fri, Nov 13, 2026 at 12:16 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
Here's a reproducer for how you can get -1 for approx_tuple_count even
in versions <= 18BEGIN;
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.
- Melanie
Attachments:
t254309_10v1-0001-Fix-pgstattuple_approx-handling-of-unknown-tuple-.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Fix-pgstattuple_approx-handling-of-unknown-tuple-.patchDownload+30-7
Hi,
On Fri, Nov 13, 2026 at 2:13 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Fri, Nov 13, 2026 at 12:16 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:Here's a reproducer for how you can get -1 for approx_tuple_count even
in versions <= 18BEGIN;
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
On Fri, 13 Nov 2026, Melanie Plageman <melanieplageman@gmail.com> wrote:
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.
+1, the patch looks good to me.
vacuumlazy.c:1606 also uses 0 clamping correctly for new_rel_tuples (that's a double), so at least those two will be consistent with this. However, there it's used as a part of a sum, so I don't think we can adjust that even on master.
On Fri, Nov 13, 2026 at 7:15 PM Lukas Fittl <lukas@fittl.com> wrote:
On Fri, Nov 13, 2026 at 2:13 PM Melanie Plageman
<melanieplageman@gmail.com> wrote: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.
Thanks for the review! Unfortunately, I didn't read this last night as
I hadn't much hope of getting replies and so I didn't get this in
before the freeze :( My bad.
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.
A quick LLM survey of other places we store or return counts of things
shows that while pg_class.reltuples is -1 when it doesn't have info
yet, SQL functions like pg_relation_size() return NULL when the table
is unavailable to get a size. Though cumulative stats counters that
are valid combinations but for which we don't yet have values are 0.
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.
It seems like NULL makes the most sense, as long as it isn't too
annoying for client programs.
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.
Yea, I think we should change master only, so there is time to roll it
back before releasing it if people don't like it.
Otherwise, +1 on the patch as is. I think the documentation change
looks good as well.
I'll commit this specific patch after the 19 freeze is over to go out
with the next set of minors. Thanks again for the timely review and
apologies for not getting it out in time. I can then apply the change
to make it NULL on master afterward, assuming we think that's the
right thing to do.
- Melanie
On Sat, Nov 14, 2026 at 11:23 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
I'll commit this specific patch after the 19 freeze is over to go out
with the next set of minors. Thanks again for the timely review and
apologies for not getting it out in time. I can then apply the change
to make it NULL on master afterward, assuming we think that's the
right thing to do.
I've committed the fix and backpatched it. The question is whether we
want master to return NULL when there was not enough information to
make an estimate for approx_tuple_count.
- Melanie
On Wed, Nov 18, 2026 at 8:06 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Sat, Nov 14, 2026 at 11:23 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:I'll commit this specific patch after the 19 freeze is over to go out
with the next set of minors. Thanks again for the timely review and
apologies for not getting it out in time. I can then apply the change
to make it NULL on master afterward, assuming we think that's the
right thing to do.I've committed the fix and backpatched it. The question is whether we
want master to return NULL when there was not enough information to
make an estimate for approx_tuple_count.
Personally I still think returning NULL is the better choice on master
- its easy enough to update for a client program, and I think 0 is
ambiguous (so we should try to get away from that ambiguity over
time).
Thanks,
Lukas
--
Lukas Fittl
On Wed, Nov 18, 2026 at 12:21 PM Lukas Fittl <lukas@fittl.com> wrote:
On Wed, Nov 18, 2026 at 8:06 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:I've committed the fix and backpatched it. The question is whether we
want master to return NULL when there was not enough information to
make an estimate for approx_tuple_count.Personally I still think returning NULL is the better choice on master
- its easy enough to update for a client program, and I think 0 is
ambiguous (so we should try to get away from that ambiguity over
time).
Cool. I'll commit the attached tomorrow then.
- Melanie
Attachments:
t254309_16v1-0001-Return-NULL-for-unknown-pgstattuple-estimate.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Return-NULL-for-unknown-pgstattuple-estimate.patchDownload+13-7
On Wed, Nov 18, 2026 at 4:38 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Wed, Nov 18, 2026 at 12:21 PM Lukas Fittl <lukas@fittl.com> wrote:
Personally I still think returning NULL is the better choice on master
- its easy enough to update for a client program, and I think 0 is
ambiguous (so we should try to get away from that ambiguity over
time).Cool. I'll commit the attached tomorrow then.
Done.
- Melanie