Re: Autovacuum lock conflict

Christophe Escobar wrote:
> 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..?)
>
> 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.

It must be an anti-wraparound VACUUM, because a normal autovacuum would
terminate after about 1 second ("deadlock_timeout" parameter).

> 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?

Yes, that is ugly and not a good idea. Unless done very carefully,
this has the potential to harm your database. VACUUM is important.

I would make sure that all tables are vacuumed in a timely fashion.

- Avoid explicit table locks or other operations that would
block autovacuum (and so prevent it, because it will terminate).

- For tables that receive UPDATEs and DELETEs, make sure that
"autovacuum_vacuum_cost_delay" is small enough so that autovacuum
finishes quickly.

- For tables that receive only INSERTs, schedule a regular VACUUM
with "cron" or similar. Unfortunately, PostgreSQL isn't very smart
about vacuuming insert-only tables.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com