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 <samims...@gmail.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) >
# Test for log messages emitted by VACUUM and ANALYZE when a specified
# relation is concurrently dropped.
#
# This also verifies that log messages are not emitted for concurrently
# dropped relations that were not specified in the VACUUM or ANALYZE
# command.
setup
{
CREATE TABLE stats (a INT);
INSERT INTO stats SELECT generate_series(1, 10);
}
teardown
{
DROP TABLE IF EXISTS stats;
}
session s1
step s1_query
{
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM pg_attribute;
}
step s1_commit
{
COMMIT;
}
session s2
step s2_update { UPDATE stats SET a=1 WHERE a > 6; }
step s2_vac_full { VACUUM FULL stats; }
step s2_reltuples { SELECT reltuples FROM pg_class WHERE relname = 'stats'; }
step s2_create_index { CREATE INDEX ON stats (a); }
permutation s2_vac_full s2_reltuples s1_query s2_update s2_reltuples s2_vac_full s1_commit s2_reltuples
permutation s2_create_index s2_vac_full s2_reltuples s1_query s2_update s2_reltuples s2_vac_full s1_commit s2_reltuples
vacuum-full-stats.out
Description: Binary data
