Content-Type: text/plain; charset=UTF-8
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: autovacuum blocks the operations of other manual vacuum
From: Alvaro Herrera <alvherre@commandprompt.com>
To: kuopo <spkuo@cs.nctu.edu.tw>
In-reply-to: <AANLkTimNrG6JA6MbMb52T5CVt+hi_hsD_fW6NRDkARgH@mail.gmail.com>
References: <AANLkTimNrG6JA6MbMb52T5CVt+hi_hsD_fW6NRDkARgH@mail.gmail.com>
Date: Tue, 16 Nov 2010 12:26:44 -0300
Message-Id: <1289921093-sup-1911@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit

Excerpts from kuopo's message of vie nov 12 05:01:24 -0300 2010:
> Hi,
> 
> I have a question about the behavior of autovacuum. When I have a big
> table A which is being processed by autovacuum, I also manually use
> (full) vacuum to clean another table B. Then I found that I always got
> something like “found 0 removable, 14283 nonremovable row”. However,
> if I stop the autovacuum functionality and use vacuum on that big
> table A manually, I can clean table B (ex. found 22615 removable, 2049
> nonremovable row).
> 
> Is this correct? Why do vacuum and autovacuum have different actions?

Vacuum full does not assume that it can clean up tuples while other
transactions are running, and that includes the (non full, or "lazy")
vacuum that autovacuum is running.  Autovacuum only runs lazy vacuum;
and that one is aware that other concurrent vacuums can be ignored.

Just don't use vacuum full unless strictly necessary.  It has other
drawbacks.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support