public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Stephen Froehlich <[email protected]>
Cc: Greg Rychlewski (LCL) <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Does cancelling autovacuum make you lose all the work it did?
Date: Fri, 12 Jun 2020 16:33:46 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwZc7YjsLXZwMws9+wLA828RkYbiABfDtMJuqNL==F34Dg@mail.gmail.com>
References: <[email protected]>
<CH2PR06MB6470E2A863D94EAE85B4B6F2E5810@CH2PR06MB6470.namprd06.prod.outlook.com>
<CAKFQuwZc7YjsLXZwMws9+wLA828RkYbiABfDtMJuqNL==F34Dg@mail.gmail.com>
"David G. Johnston" <[email protected]> writes:
> To answer the original question: vacuum's effects are basically immediate
> so work is not lost if it gets cancelled.
Mmm ... not entirely true. vacuum does a cycle like this:
1. scan table looking for removable rows; remember their TIDs
2. when memory for said TIDs is full, make a pass over the table's
indexes to find and delete index entries pointing at those TIDs
3. then, go back to the heap pages and actually remove the rows
4. if we didn't reach the end of the table yet, return to step 1.
If we get cancelled at any point, the data structure is still
consistent; there may be heap rows that don't have a full set
of index entries, but that doesn't matter because nobody cares
about finding those entries from the indexes. However, when
you redo the vacuum, it'll have to redo some of the work from
the current cycle, depending on exactly how far along it was
when you cancelled it. At the very least it's going to be
repeating some heap-scanning work, though that's the cheapest
part of this because it's basically read-only. The most
expensive parts are the actual index and heap tuple removals,
and any one of those won't need to be done over.
The size of the cycles depends on maintenance_work_mem; so if
you have that set really large, you can lose more time than
if it's not so large.
TBH, though, are you sure the vacuum is doing work and not
just blocked waiting for somebody else? An autovac can be
blocked indefinitely by some other query holding a table-level
or page-level lock. Check its state in pg_stat_activity.
regards, tom lane
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Does cancelling autovacuum make you lose all the work it did?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox