Re: Autovacuum lock conflict

On 9/23/19 8:09 AM, Christophe Escobar wrote:
> Hi,
>
> I am using PostgreSQL 9.6.13.
>
> I have some questions about auto vacuums.
>
> I find it quite hard to have some info about vacuum locking in the
> documentation,
> but from what I found running the command myself is that simple vacuum
> will hold a Share Update Exclusive lock.
>
> We are using auto vacuums on our database and recently we had an issue
> when rolling out a new version of our solution.
> The new version included a migration creating a new index for a big table.
>
> We do try to have no maintenance when migrating, thus we generally use
> CREATE INDEX CONCURRENTLY to avoid locking a table for reading and
> writing.
>
> We went across an issue with our index creation being locked by an
> auto vacuum, as both processes hold a Share Update Exclusive lock.
>
> Thankfully for us, we run our database migration with a small lock
> timeout so it was not too dangerous, but we are left with some doubt
> about our "smooth" process.
>
> In these cases, we want to have our migration run in priority as it
> blocks our release and everything that goes along.
>
> I've read - but I cannot find it anywhere - that a vacuum process
> "should" kill itself if it finds out that it is blocking any other
> query;
> but only if the notice "to prevent wraparound" is not present. (have I
> missed official documentation somewhere..?)

It will be, courtesy of PostgreSQL Weekly News - September 22, 2019:

https://www.postgresql.org/message-id/20190922194743.GA24367%40fetter.org

Amit Kapila pushed:

- Doc: document autovacuum interruption. It's important users be able to
know
(without looking at the source code) that running DDL or DDL-like
commands can
interrupt autovacuum which can lead to a lot of dead tuples and hence
slower
database operations. Reported-by: James Coleman Author: James Coleman
Reviewed-by: Amit Kapila Backpatch-through: 9.4 Discussion:

https://postgr.es/m/CAAaqYe-XYyNwML1=f=gnd0qWg46PnvD=BDrCZ5-L94B887XVxQ@mail.gmail.com

https://git.postgresql.org/pg/commitdiff/82fa3ff867219a212a467317a77011df29cb5903

And the diff:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=70377cf4c6bf4eb4b2d1209752a300d5f3571145

>
> We would like to have inputs on how to cleanly prevent these issues in
> the future.
>
> 1) What's the actual answer about a vacuum killing itself if it blocks
> a query? Is that true?
> How fast is it to kill itself? As we have a 2s lock timeout, if it
> takes 5s to check that it should kill itself, we won't benefit from
> this behaviour.
>
> 2) What can we do to prevent any concurrent index creation from being
> locked out by auto vacuums?
> We have thought of a dirty solution consisting of:
> - disabling auto vacuums before a migration
> - kill current pids with a vacuum
> - do our migration
> - enable back auto vacuum.
> We find this solution awful, error-prone and "complex" to have.
> Are there any other way except having manuals vacuum being played
> during the night?
>
> Thanks in advance for your help,
>
> Regards,
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com