pg15: reltuples stuck at -1 after pg_upgrade and VACUUM

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
Subject: pg15: reltuples stuck at -1 after pg_upgrade and VACUUM
Date: 2023-08-09 03:43:28
Message-ID: ZNMLYAwpqzKWChZv@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Since 3d351d916 (pg14), reltuples -1 means that the rel has never been
vacuumed nor analyzed.

But since 4496020e6d (backpatched to pg15), following pg_upgrade, vacuum
can leave reltuples=-1.

commit 4496020e6dfaffe8217e4d3f85567bb2b6927b45
Author: Peter Geoghegan <pg(at)bowt(dot)ie>
Date: Fri Aug 19 09:26:06 2022 -0700

Avoid reltuples distortion in very small tables.

$ /usr/pgsql-15/bin/initdb -N -D ./pg15.dat2
$ /usr/pgsql-15/bin/initdb -N -D ./pg15.dat3

$ /usr/pgsql-15/bin/postgres -c logging_collector=no -p 5678 -k /tmp -D ./pg15.dat2& # old cluster, pre-upgrade
postgres=# CREATE TABLE t AS SELECT generate_series(1,9999);
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | -1
postgres=# VACUUM FREEZE t;
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | 9999

$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-15/bin -d ./pg15.dat2 -D./pg15.dat3 # -c logging_collector=no -p 5678 -k /tmp&

$ /usr/pgsql-15/bin/postgres -c logging_collector=no -p 5678 -k /tmp -D ./pg15.dat3& # new cluster, post-upgrade
postgres=# VACUUM FREEZE VERBOSE t;
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | -1

The problem isn't that reltuples == -1 after the upgrade (which is
normal). The issue is that if VACUUM skips all the pages, it can leave
reltuples -1. My expectation is that after running "vacuum", no tables
are left in the "never have been vacuumed" state.

If the table was already frozen, then VACUUM (FREEZE) is inadequate to
fix it, and you need to use DISABLE_PAGE_SKIPPING.

--
Justin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Rui Zhao 2023-08-09 03:47:58 Re: pg_upgrade fails with in-place tablespace
Previous Message Robert Treat 2023-08-09 03:13:54 Re: 2023-08-10 release announcement draft