Re: Why is my table not autovacuuming?

On Mon, Aug 28, 2017 at 8:43 AM, Don Seiler <don(at)seiler(dot)us> wrote:

> PostgreSQL 9.2.18. I have a table with 71M rows (via select count). In
> pg_stat_activity, n_live_tups is 170K, n_dead_tups is slightly more than
> that (203K). The autovacuum parameter is on, the scale factor is 0.2
> (default) and threshold is 50 (default), autovacuum_max_workers is 3. There
> are no custom autovacuum thresholds set for this table. The last_autovacuum
> field is null.
>
> Obviously I would expect autovacuum/autoanalyze to kick in for this table
> so the stats aren't getting so far out of whack from reality. When I query
> pg_stat_activity, I never see any autovacuum jobs running, so it isn't a
> matter of too much work from what I can tell.
>
>
​autovacuum <> autoanalyze - even though the same underlying executable
performs both tasks. If you are questioning why autoanalyze is not running
you need to inspect the autoanalyze fields, not the autovacuum ones (both
the indicators and the source data elements). Seeing an auto-analyze in
pg_stat_activity would be pretty unlikely given how quickly they tend to
run.

In addition:

https://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#VACUUM-FOR-STATISTICS

"​The autovacuum daemon, if enabled, will automatically issue ANALYZE
commands whenever the content of a table has changed sufficiently."
...
"The daemon schedules ANALYZE strictly as a function of the number of rows
inserted or updated;"

Depending on your findings in those columns you may want to ensure that you
haven't accidentally disabled the autovacuum daemon process.

https://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html

The question I have is how did this table get to be 71M records large? One
bulk load or 1M records inserted a day for 2 months? How often do those
records change once inserted?

I'm done for now but will leave with my own thought. The statistical
counts can be reset, and I assume analyze must reset the counts that it
uses as input once it has completed. But I can imagine a well-meaning but
misguided activity monitoring script that wants to see (e.g. daily) volume
choosing to issue a "pg_stat_reset()" daily just after capturing the count
from the previous day. For sufficiently large tables its likely
auto-analyze would never be run again because the magnitude of the counts
never gets high enough.

David J.