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

On 2014-12-02 11:02:07 -0800, Josh Berkus wrote:
> On 12/02/2014 10:35 AM, Alvaro Herrera wrote:
> > If the table is large, the time window for this to happen is large also;
> > there might never be a time window large enough between two lock
> > acquisitions for one autovacuum run to complete in a table.  This
> > starves the table from vacuuming completely, until things are bad enough
> > that an emergency vacuum is forced.  By then, the bloat is disastrous.
> > 
> > I think it's that suicide that Andres wants to disable.

Correct.

> A much better solution for this ... and one which would solve a *lot* of
> other issues with vacuum and autovacuum ... would be to give vacuum a
> way to track which blocks an incomplete vacuum had already visited.
> This would be even more valuable for freeze.

That's pretty much a different problem. Yes, some more persistent would
be helpful - although it'd need to be *much* more than which pages it
has visited - but you'd still be vulnerable to the same issue.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services