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

Ah yes, it is the dead tuple cannot be removed, because long running
transactions still see them.
Something that lead to my confusion above is pg_stat_progress_vacuum, each
time autovacuum/manual vacuum launch during long running transaction, it
reports that num_dead_tuples

On Fri, Feb 11, 2022 at 7:32 AM Andres Freund <andres(at)anarazel(dot)de> wrote:

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

--
Regards,

Soni Maula Harriz