Autovacuum is cleaning very less dead tuples - Mailing list pgsql-performance

Hi,

As part of vacuum tuning, We have set the below set of parameters.

> select relname,reloptions, pg_namespace.nspname from pg_class join pg_namespace on pg_namespace.oid=pg_class.relnamespace where relname IN('process_instance') and pg_namespace.nspname='public';
   relname    |                                                                      reloptions                                                                      | nspname
--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+---------
 process_instance | {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=20000,autovacuum_vacuum_cost_limit=1000,autovacuum_vacuum_cost_delay=10}               | public



autovaccumm threshold was set for 20,000. However after the vacuuming, it is cleaning up less than 2,000 tuples only. And also vacuuming frequncy was increased as it is becoming eligible for the autovacuuming.
However n_dead_tup value from pg_stat_user_tables was always showing very high value. Most of the time, it is greater than 100K dead tuples.  

Overall, we couldn't able to correlate on why autovacuum was able to cleanup only < 2K tuples, even though there are mode dead tuples based on the statistics ?  Can you please explain on why we are notcing huge difference and what steps needs to taken to minimize the gap ?


Log message

 2019-09-25 00:06:31 UTC::@:[80487]:LOG:  automatic vacuum of table "fc_db_web_2.public.process_instance": index scans: 1
pages: 0 removed, 854445 remain, 0 skipped due to pins, 774350 skipped frozen
tuples: 1376 removed, 16819201 remain, 21 are dead but not yet removable
buffer usage: 553118 hits, 9070720 misses, 14175 dirtied
avg read rate: 13.926 MB/s, avg write rate: 0.022 MB/s
system usage: CPU 44.57s/33.04u sec elapsed 5088.65 sec


Table Information