bloatcheck.sql - Mailing list pgsql-admin

(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.