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
> 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)
