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

On 12/02/2014 11:08 AM, Andres Freund wrote:
> 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.

If we're trying to solve the problem that vacuums of large, high-update
tables never complete, it's solving the same problem. And in a much
better way.

And yeah, doing a vacuum placeholder wouldn't be simple, but it's the
only solution I can think of that's worthwhile. Just disabling the
vacuum releases sharelock behavior puts the user in the situation of
deciding between maintenance and uptime.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com