Re: auto truncate/vacuum full

On Tue, 27 Oct 2009, Tom Lane wrote:

> The issue I can see is that we might never be able to complete any
> truncation if there's a lot of potentially removable pages and a pretty
> steady flow of conflicting lock attempts. But that would result in
> failure to remove bloat, not stoppage of conflicting queries.

That's exactly it. It's possible to get into a situation where it becomes
increasingly difficult to even catch up bloat once you get behind by a
certain amount. All it takes is one giant deletion and you can get stuck
here until there's a window to take the database down altogether, and you
could end up down for hours waiting for that to execute.

> It might be worth allowing autovacuum to execute the truncation every
> few hundred pages, so as to ensure that progress can be made even if it
> never gets a very long uninterrupted lock on the table.

That was the sort of thing I was alluding to, moving in that direction
would seem much more valuable than trying to optimize the existing
approach better.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD