A last_autoanalyze date in pg_stat_all_tables for a table we update every day is 3/26, another is 3/13 -- I feel strongly somehow these tables should be auto-analyzed and auto-vacuumed more often, and am wondering where to look first.
My autovacuum_analyze_threshold is 10, my autovacuum_vacuum_threshold is 25.
How can I better understand why these tables are not more frequently being auto-analyzed and vacuumed?
Thank you.
The threshold settings are only part of the calculation that determines when autovacuum kicks in. There's also autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor. The scale factors say that when this fraction of the table has been changed (updates/deletes) then run the relevant operation (vacuum or analyze).
The threshold values are added on top of the scale factor values. So, what determines whether your analyze kicks in is actually
(autovacuum_analyze_scale_factor * total row count) + autovacuum_analyze_threshold
The reason it's like this is to avoid autovacuum kicking in excessively on very small tables when a small % changes. Ex. 10 rows on a 100 row table constantly kicking in autovacuum when scale factor is set to .10. So add on 100-500 for the threshold so autovacuum kicks in when a good number of rows actually changes.
If you want a specific number of row changes to cause autovacuum to kick in, you can set the scale factor to zero for that specific table. I only really find that necessary myself on rather large tables though (10s of millions of rows).
--
