Re: Behaviour when autovacuum is canceled

[Martín Fernández <fmartin91@xxxxxxxxx>]

David,

Your last comment applies for cleaning up indexes as well ? We performed a simple test in our production database to understand behaviour and we got a result that surprised us based on your last comment.

We basically started a VACUUM on a given table, waited for one index to process (captured cleaned rows count) and cancel the VACUUM. When we run another VACUUM on the same table the dead rows removed from the first index was a number slightly higher than the value logged on the first VACUUM. This behaviour made us feel that the work done to clean dead tuples on the first index was performed again. 

Thanks!

Martín

On Thu, Sep 13th, 2018 at 8:0 PM, "Martín Fernández" <fmartin91@xxxxxxxxx> wrote:

David,

Thanks a lot for the quick reply. 

I clearly misunderstood the references in the code. 

Best,

Martín

On Thu, Sep 13th, 2018 at 7:55 PM, "David G. Johnston" <david.g.johnston@xxxxxxxxx> wrote:

On Thu, Sep 13, 2018 at 3:45 PM, Martín Fernández <fmartin91@xxxxxxxxx> wrote:

From what I could understand (that can be totally wrong), the vacuum process is split in multiple small transactions. If the autovacuum is canceled, could it be possible that only the latest transaction work be lost 

From the docs:

"VACUUM cannot be executed inside a transaction block."

As it is non-transactional any work it performs is live immediately and irrevocably as it occurs.

David J.