Re: BUG #17399: Dead tuple number stats not updated on long running queries

Hi,

On 2022-02-08 01:40:32 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17399
> Logged by: Soni
> Email address: diptatapa(at)gmail(dot)com
> PostgreSQL version: 13.5
> Operating system: Red Hat Enterprise Linux release 8.5 (Ootpa)
> Description:
>
> Hello All,
> I think I found a bug.
>
> While there are long running queries, a vacuum that start and end during the
> long running queries, the stats of pg_stat_user_tables.n_dead_tup not
> updated. The real dead tuple on the table is cleaned up, but not the
> stats.

Are you saying that pg_stat_user_tables.n_dead_tup is not updated for changes
done by the long running transactions, or that it is not getting updated at
all, even if other transactions that performed modifications commit?

It is correct and expected that the changes by currently running transactions
are not reflected in the stats subsystem - and they can't really. Whether
changes by such transactions end up as "live" or "dead" rows differs between
that transaction committing and aborting.

I just verified that indeed n_dead_tup gets updated after a transaction
commits, even if there are other long running transactions.

> So, if dead tuple percentage on pg_stat_user_tables is above
> autovacuum_vacuum_scale_factor, then the autovacuum keeps triggered during
> the long running queries.

The reason for this likely is not that pg_stat_user_tables.n_dead_tup is not
updated, but that existing dead tuples cannot be yet be removed, because the
longrunning transaction might still see them.

If you enable autovacuum logging, you can see (output differs a bit in older
versions, but the concept is similar) something like this:

2022-02-10 16:27:47.927 PST [61256][autovacuum worker][6/161:0] LOG: automatic vacuum of table "postgres.public.large": index scans: 0
pages: 0 removed, 15610 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 2400000 remain, 1400000 are dead but not yet removable, oldest xmin: 739
...

Note the "1400000 are dead but not yet removable" bit. In this case there are
1.4M dead rows that can't be cleaned up due to the longrunning transaction /
query. Because of that autovacuum will be started again and again, until the
dead rows can actually be cleaned up.

Once the longrunning transaction ends, autovacuum will run once more:

2022-02-10 16:32:04.292 PST [61703][autovacuum worker][6/261:749] LOG: automatic vacuum of table "postgres.public.large": index scans: 1
pages: 2335 removed, 13275 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 1400000 removed, 2000000 remain, 0 are dead but not yet removable, oldest xmin: 749

and because 1.4M tuples were removed, n_dead_tup changes to 0, and everyone is
happy again.

Greetings,

Andres Freund