Autovacuum is not running even after configuring it on

Question

I am using aurora-postgresql version 9.6.8. and I have one main instance , one writer and one reader replica.

I am looking for below information:

How I can check on a particular instance when autovacuum was executed when I am trying to use "pg_stat_user_tables"?

This show lot of dead tuples. But when we see max last autovacuum executed date its one month back. Seems like autovacuum not executed from last one month on any of the table. In my environment we have huge insert update and delete every day.

Thank you in advance for any suggestion!!!

Below is my auto vacuum configuration:

name    setting
autovacuum_analyze_scale_factor 0.05 

autovacuum_analyze_threshold    50

autovacuum_freeze_max_age   200000000

autovacuum_max_workers  4

autovacuum_naptime  5

autovacuum_vacuum_cost_delay    5

autovacuum_vacuum_cost_limit    -1

autovacuum_vacuum_scale_factor  0.1

autovacuum_vacuum_threshold 50

maintenance_work_mem    4122624

vacuum_cost_limit   200

vacuum_freeze_min_age   50000000

Answer 1

Did you contact AWS support? The version 9.6.8 aurora-postgresql has a metadata corruption bug that could lead to this symptom that you have described. You probably need to upgrade.

I have recently encountered this problem twice in production, and AWS support was helpful in each event.