Re: Diagnosing outdated auto analyze dates...

On Wed, Apr 21, 2021 at 4:06 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:

> 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.
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>

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

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com