Re: Why is my table not autovacuuming?

So the autovacuum daemon knows that there are 71M rows, even though
pg_stat_all_tables said only 170K? Is it using pg_class.reltuples? Is
pg_stat_all_tables not really a reliable source of stats information?

Don.

On Mon, Aug 28, 2017 at 10:55 AM, Phil Frost <phil(at)postmates(dot)com> wrote:

> With a scale factor of 0.2, autovacuum won't kick in until there are an
> estimated 0.2 * 71M = 14.2M dead tuples. For such large tables, it may be
> prudent to adjust the autovacuum parameters to have a lower scale factor.
> You can also set the scale factor to zero, and then the threshold alone
> determines when autovacuum runs: you'd set the threshold to some number
> much larger than the default 50.
>
> https://blog.2ndquadrant.com/autovacuum-tuning-basics/
> https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html
>
> On Mon, Aug 28, 2017 at 11:45 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.
>>
>> Previously I had tables not being autovacuumed because of index
>> corruption (which would also cause manual vacuum to fail), but I was just
>> able to successfully vacuum this table manually and now pg_stat_activity
>> show the accurate info.
>>
>> Wondering why my autovacuum daemon appears to be acting lazily?
>>
>> Don.
>>
>> --
>> Don Seiler
>> www.seiler.us
>>
>

--
Don Seiler
www.seiler.us