Thanks very much, Scott, Alvaro!
From what you said, I have another question:
-
Note that if you cancel the autovacuum the manual one will then proceed, and the next autovacuum will have to wait
behindthat. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy
load.
-
If manual vacuum of a table is in progress, and autovacuum for the same table starts:
a. Does autovacuum abort for that table and move on to the next table in it list. - or -
b. Does autovacuum wait until the manual vacuum is completed (however long that takes), and then starts autovaccum on
thattable.
Thanks again,
Mahesh
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, June 1, 2017 12:21 PM
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Mahesh Jhala <mjhala@carrentals.com>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Can manual vacuuming conflict with auto vacuuming?
On Thu, Jun 1, 2017 at 1:16 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Mahesh Jhala wrote:
>> Hello All,
>>
>> If autovacuum of a large PostgreSQL table is in progress, and I start
>> a manual vacuum on the table, is there likely to be a conflict
>> (locking etc.) between the two vacuums?
>
> It depends on the autovacuum being for-wraparound or not. If not,
> then the conflict will be detected within one second and autovacuum
> will cancel itself, so the regular vacuum would be able to proceed.
> So there is a one-second period of time during which you would see the
> manual vacuum as blocked by autovacuum.
>
> If autovacuum is for wraparound protection, then it won't cancel
> itself, and the regular vacuum will just wait until autovacuum is finished.
>
>
> There is a final stage in vacuuming (both auto and regular) during
> which the table is truncated (the last few empty pages, if any).
> There's some heuristics to handle that case nowadays which I don't
> remember very clearly.
Note that if you cancel the autovacuum the manual one will then proceed, and the next autovacuum will have to wait
behindthat. This may be necessary if your autovacuum is not aggressive enough to keep up with some particularly heavy
load.
