Re: auto vacuum

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Herouth Maoz wrote:
>> We found out that the table's response depends on the rate of ANALYZE being performed. We have tuned the values in pg_autovacuum so that we have around one analyze per minute.
>>
>> What is bothering me is that sometimes the auto vacuum daemon decides to perform a vacuum analyze rather than just analyze. If it just does a vacuum independent of the analyze, we don't see much impact on performance. But if it does vacuum analyze, it means that until vacuum is over, it doesn't do another analyze, and this may take about five minutes, in which our performance under load conditions might deteriorate.

> Hmm, this is an use case we never thought about. I don't think there's
> any way to coerce autovacuum into doing what you want it to do. I
> suggest you turn analyze off for this table (say by setting a very large
> scale factor), and analyze it manually through cron or something.

Hmm. Given the churn rate on the table, I'm having a very hard time
believing that you don't need to vacuum it pretty dang often. Maybe the
direction you need to be moving is to persuade autovac to vacuum it
*more* often, not less often, so that the time needed to finish each
vacuum is small enough.

regards, tom lane