Re: auto truncate/vacuum full

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Tue, 27 Oct 2009, Alvaro Herrera wrote:
>> Now 40 mins walking those pages to figure out that they need to be
>> truncated, I concede that it's too much. Maybe we shouldn't be doing a
>> backwards scan; perhaps this breaks the OS readahead and make it even
>> slower.

> I've watched that take hours before on a large table after purging
> hundreds of gigabytes of old historical data.

Shouldn't autovacuum be getting kicked off the lock if anyone does
something that conflicts? This seems like it should be a nonproblem
if everything is operating as designed.

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.

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.

regards, tom lane