bloatcheck.sql

Hi PostgreSQL admins,

(PostgreSQL v9.5.9)

The author of this SQL indicates that any output for the hot_update_ratio
that falls below 0.95 indicates that action needs to be taken over and
above the autovacuuming that is setup for this DB based on its levels of
bloat.

My questions are:

1. Is this an accurate statement in your opinion for this and all
PostgreSQL v9.5.9 DB's?

2. Given that about 60 of the tables qualify for this statement, what would
be the best next step(s) to get them to be above 0.95 and remain there at
this point?

3. What specific ongoing/regular step(s) need to occur outside of the
hourly and weekly commands currently being run on the DB for it to remain
in an optimally performant state?

Hourly:
REINDEX TABLE miq_queue
REINDEX TABLE miq_workers
REINDEX TABLE metrics_$(date -u +"%H" --date='+1 hours ' )

Weekly:
\timing
VACUUM full verbose vms ;
vacuum full verbose binary_blob_parts ;
vacuum full verbose binary_blobs ;
vacuum full verbose customization_specs ;
vacuum full verbose firewall_rules ;
Vacuum full verbose hosts ;
vacuum full verbose storages ;
vacuum full verbose miq_schedules ;
vacuum full verbose event_logs ;

vacuum full verbose policy_events ;
vacuum full verbose snapshots ;
vacuum full verbose jobs ;
vacuum full verbose networks ;
vacuum full verbose miq_queue ;
vacuum full verbose miq_request_tasks ;
vacuum full verbose miq_workers;
vacuum full verbose miq_servers;
vacuum full verbose miq_searches;
vacuum full verbose miq_scsi_luns ;
vacuum full verbose miq_scsi_targets;
vacuum full verbose storage_files ;
vacuum full verbose taggings ;
vacuum full verbose vim_performance_states;
--vacuum full verbose ems_events ;

section of postgresql.conf:

#-----------------------------------------------------------
-------------------
# AUTOVACUUM PARAMETERS
#-----------------------------------------------------------
-------------------

autovacuum = on # MIQ Value;
#autovacuum = on # Enable autovacuum subprocess?
'on'
# requires track_counts to also be
on.
log_autovacuum_min_duration = 0 # MIQ Value;
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
and
# their durations, > 0 logs only
# actions running at least this
number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum
subprocesses
# (change requires restart)
autovacuum_naptime = 5min # MIQ Value;
#autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 500 # MIQ Value;
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
autovacuum_analyze_threshold = 500 # MIQ Value;
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
autovacuum_vacuum_scale_factor = 0.05 # MIQ Value;
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

#------------------------------------------------------------------------------

Thanks in advance for your advice on this tuning topic.