Re: How about a option to disable autovacuum cancellation on lock conflict?

On Nov 29, 2014 9:23 AM, "Andres Freund" <andres(at)2ndquadrant(dot)com> wrote:
>
> Hi,
>
> I've more than once seen that autovacuums on certain tables never
> succeed because regular exclusive (or similar) lockers cause it to give
> way/up before finishing. Usually if some part of the application uses
> explicit exclusive locks.
>
> In general I think it's quite imortant that autovacuum bheaves that
> way. But I think it might be worhtwile to offer an option to disable
> that behaviour. If some piece of application logic requires exclusive
> locks and that leads to complete starvation of autovacuum, there's
> really nothing that can be done but to manually schedule vacuums right
> now.
>
> I can see two possible solutions:
>
> 1) Add a reloption that allows to configure whether autovacuum gives way
> to locks acquired by user backends.
> 2) Add a second set of autovacuum_*_scale_factor variables that governs
> a threshhold after which autovacuum doesn't get cancelled anymore.
>
> Opinions?

I definitely think having such a tunable would be very useful, in edge
cases (so as you say the default should definitely be what it is today).

Another "trigger option" could be to say "you may terminate autovaccum this
many times before forcing one through", rather than triggers on tuple
count. But tuples is probably a better choice, as it gives more dynamics -
unless we want to do both.

/Magnus