Danger in killing autovacuum: VACUUM queries (to prevent wraparound)

Question

There is an autovacuum query that is taking a very long time to run, and preventing alter queries to run.

What is the danger is killing this autovacuum process before it's done?

PID      QUERY
16967 | autovacuum: VACUUM public.articles (to prevent wraparound)

Here is how I am killing it:

select pg_terminate_backend(16967) from pg_stat_activity;

Sicklebill

Answer 1

You can issue pg_cancel_backend(16967) rather than "pg_terminate_backend()" (not quite as severe is my understanding). Once you kill that autovacuum process, it will start back up again as you have probably noticed, particularly because it was launched for the reason stated (which was to prevent wraparound). If you issue VACUUM public.articles manually, the vacuum will complete faster at the expense of higher disk I/O. That's a generalized answer, but is usually how it turns out.

Statvolt

Answer 2

It is better you do the vaccum before getting the situation worse. At one time or on other, you will have to do that to prevent the data loss. You may wonder where all the datas are gone when wraparound id failure happens. The data will be still in the db, but it will be hidden and can not be accessed until the vaccum process is done. So let it be done either by autovaccum or manual vaccum.

Lateral

Answer 3

If you need to do an alter table just one time, there is probably no harm in killing it this one time. You should submit the cancel and the alter table on the same line in psql, so that the alter table has a chance to start before another autovacuum kicks off and blocks it again.

There is a small chance that cancelling the autovacuum will cause txid wrap-around and an emergency shutdown of your database which will take a bit of work and downtime to clean up. But if this happens, you were almost certainly in a death-race already anyway.

If you do this often, then you will be storing up massive problems for yourself, including the aforementioned death-race and emergency shutdown.

By the way, your select pg_terminate_backend(16967) should not have a FROM clause on it.

Mephistopheles