Autovacuum is cleaning very less dead tuples

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*

*SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM
pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <>
'i' AND nspname !~ '^pg_toast' AND relname='process_instance';
relation | total_size
---------------------+------------ public.process_instance | 77 GB*

*Live and Dead tuples*

*select relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE
relname='process_instance'; relname | n_live_tup | n_dead_tup
--------------+------------+------------ conversation | 16841596 |
144202*

show track_counts;
track_counts
--------------
on

show default_statistics_target;
default_statistics_target
---------------------------
100

*Version*

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit

Thanks in advance.

Regards, Amarendra