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

database postgresql autovacuum
Henley picture Henley · Aug 5, 2013 · Viewed 19.9k times · Source

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;

Answer

bma picture bma · Aug 5, 2013

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.

Related questions

postgresql autovacuum not working although process is running

I have PostgreSQL 9.2 running on Ubuntu. The autovacuum process is running on the server. The database has heavy load, 1000's of additions and 100's of deletions daily and yet it seems that autovacuum isn't firing up. There is nothing relating …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
What is this Javascript "require"?

I'm trying to get Javascript to read/write to a PostgreSQL database. I found this project on github. I was able to get the following sample code to run in node. var pg = require('pg'); //native libpq bindings = `var pg = …

Read More