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@xxxxxxxxxxxxxx
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.klaver@xxxxxxxxxxx
