Re: Autovaccum and Full vacuum

On Wed, May 5, 2010 at 12:01 AM, Shoaib Mir <shoaibmir(at)gmail(dot)com> wrote:
> On Wed, May 5, 2010 at 2:46 PM, Tony Day <tonyd(at)panztel(dot)com> wrote:
>>
>> Hi Shoaib
>>
>> Thanks for the response.
>>
>> Sorry - I forgot to mention that I am using PostgreSQL 8.3.9 on Ubuntu
>> 9.04.
>> And just to be clear - I am not suggesting that there is a bug in
>> Postgresql :-)
>> I am just trying to clarify what it does and doesn't do.
>>
>>
>
> You can find on docs autovacuum
> at http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM
> It uses VACUUM and not VACUUM FULL, there shouldn't be a need of VACUUM FULL
> if you have autovacuum enabled with proper thresholds. There can be an odd
> case where you might have to make auto-vacuum a little more aggressive for a
> specific table.

autovacuum never executes VACUUM FULL, but...

in 8.3, sometimes it finds a page that is empty so it truncate it to
release space, and that needs an stronger lock than the normal it uses
(but i don't remember exactly what type of lock) and when the
autovacuum is executing for anti wraparound problems it cannot be
cancelled...

so, if an autovacuum execute vacuum for anti wraparound problems and
it finds empty pages that should be truncated then you get a situation
where a table is locked and you can't terminate the vacuum, if the
table is big there are problems... no idea if this happens in 8.4

--
Jaime Casanova www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL