Some Update
After several times repeatedly autovacuum is launched on the table, then it stops. After the long running queries finished, the postgres service restarted, analyzed the table, the n_dead_tup still the same, then vacuum again, but now the vacuum process detects again the dead row it has cleaned previously.
It seems the concurrency control in the vacuum process.
The following bug has been logged on the website:
Bug reference: 17399
Logged by: Soni
Email address: diptatapa@gmail.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.
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.
--
Regards,
Soni Maula Harriz