Re: Relstats after VACUUM FULL and CLUSTER

Hi Sami,

You need a concurrent transaction to recreate the situation. I am attaching
an isolation test to show the behavior, along with its output file. I ran
it on PostgreSQL 17.4.

The test has two permutations, the first one runs on a table without an
index and the second permutation with an index added. In the output file
you can see that the VACUUM FULL on the index-less table produces reltuples
count that includes all updated tuples + the old/garbage tuples. In other
words, it counts all tuples visible to any currently ongoing transaction.
If the table has an index the behavior is different because the reindex
that happens as the last step of vacuum full overwrites the first reltuples
count with the "correct" number (as visible by the transaction snapshot).

Best,
Erik

On Thu, May 22, 2025 at 5:04 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:

> > Does this seem like a bug or is it intentional?
>
> pg_class.reltuples/relpages are only an estimate as per documentation.
>
> However, I cannot reproduce the situation you are talking about on HEAD.
> In the below example, I create a table without indexes, then insert and
> delete some rows. run vacuum to update the pg_class.reltuples, then run
> another delete to generate some more "recent" dead tuples.
>
> The result shows pg_class.reltuples with the expected value,
> but maybe I did not repro the same way you did?
>
> ( I am surprised that n_live_tup, n_dead_tup is off and also that
> VACUUM FULL does not appear to update the stats in pg_stat_all_tables)
>
> ```
> postgres=# drop table if exists t;
> create table t ( id int );
> alter table t set (autovacuum_enabled = off);
> insert into t select n from generate_series(1, 1000000) as n;
> delete from t where id between 1 and 5000;
> vacuum t;
> delete from t where id between 5001 and 10000;
> select reltuples::int from pg_class where relname = 't';
> -- might take a bit of time for n_dead_tup to be set
> select n_dead_tup, n_live_tup from pg_stat_all_tables where relname = 't';
> DROP TABLE
> CREATE TABLE
> ALTER TABLE
> INSERT 0 1000000
> DELETE 5000
> VACUUM
> DELETE 5000
> reltuples
> -----------
> 995000
> (1 row)
>
> n_dead_tup | n_live_tup
> ------------+------------
> 10000 | 985000
> (1 row)
>
> postgres=# VACUUM (verbose, full) t;
> INFO: vacuuming "public.t"
> INFO: "public.t": found 5000 removable, 990000 nonremovable row
> versions in 4425 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> CPU: user: 0.79 s, system: 0.02 s, elapsed: 0.86 s.
> VACUUM
>
> select reltuples::int from pg_class where relname = 't';
> select n_dead_tup from pg_stat_all_tables where relname = 't';
>
> postgres=# select reltuples::int from pg_class where relname = 't';
> select n_dead_tup, n_live_tup from pg_stat_all_tables where relname = 't';
> reltuples
> -----------
> 990000
> (1 row)
>
> postgres=# select n_dead_tup, n_live_tup from pg_stat_all_tables where
> relname = 't';
> n_dead_tup | n_live_tup
> ------------+------------
> 10000 | 985000
> (1 row)
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>