Re: admin control over cancelling autovacuum when blocked by a lock

As I remember, it used to be "in the old days" that autovacuum processes
acted like primary ones blocking user requests.  But now, I think PG got
it right making them act like secondary type processes that must be
"outed" when conflicting with user requests.

What I find is the best vacuuming solution is a combination of 3 things:
1. separate autovacuum tuning for high and low load times (triggered via
crontab)
2. ALTER table commands to specify autovacuum parms at the table level
based on sql loads on particular tables.
3. Scheduled manual vacuums to help take the stress off of autovacuum
daemons.

Regards,
Michael Vitale

Rui DeSousa wrote on 4/30/2019 10:12 AM:
>
>
>> On Apr 30, 2019, at 9:27 AM, John Lumby <johnlumby(at)hotmail(dot)com
>> <mailto:johnlumby(at)hotmail(dot)com>> wrote:
>>
>> it does do ANALYZE  -  in this context is that "DDL"?
>
> I would consider that DDL and the application should avoid doing it;
> otherwise, you end up with blocking issues where both readers and
> writers are blocked unnecessarily.  The application issuing analyze on
> its own temp tables is fine and non blocking.  If you must analyze
> tables then I would limit which processes have access to those tables
> to avoid blocking issues.
>
> I would look at your auto vacuum/analyze settings as oppose to the
> application issuing analyze commands as those are going to be a non
> blocking option.
>