How to prevent autovacuum for table in Postgres

postgresql postgresql-9.2 autovacuum
clarent picture clarent · Nov 4, 2014 · Viewed 8.1k times · Source

I have big tables in which I have only inserts and selects, so when autovacuum for this tables is running - system is very slow. I have switch off autovacuum for specific tables:

ALTER TABLE ag_event_20141004_20141009  SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
ALTER TABLE ag_event_20141014_20141019  SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

After this (some time after) I see:

select pid, waiting, xact_start, query_start,query from pg_stat_activity order by query_start;

 18092 | f       | 2014-11-04 22:21:05.95512+03  | 2014-11-04 22:21:05.95512+03  | autovacuum: VACUUM public.ag_event_20141004_20141009 (to prevent wraparound)
 19877 | f       | 2014-11-04 22:22:05.889182+03 | 2014-11-04 22:22:05.889182+03 | autovacuum: VACUUM public.ag_event_20141014_20141019 (to prevent wraparound)

What shell I do to switch autovacuuming for this tables at all ??

Answer

Andomar picture Andomar · Nov 4, 2014

The key here is:

(to prevent wraparound)

This means Postgres must autovacuum in order to free up transaction identifiers.

You can not entirely disable this type of autovacuum, but you can reduce its frequency by tuning the autovacuum_freeze_max_age and vacuum_freeze_min_age parameters.

Related questions

Permission denied for relation

I tried to run simple sql command: select * from site_adzone; and I got this error ERROR: permission denied for relation site_adzone What could be the problem here? I tried also to do select for other tables and got …

Read More
Export and import table dump (.sql) using pgAdmin

I have pgAdmin version 1.16.1 So, for exporting table dumm I do: Right click on table, then in menu click on backup, then in Format choice Plain and save file as some_name.sql Then I remove table. Ok, now I …

Read More
Postgres DB Size Command

What is the command to find the size of all the databases? I am able to find the size of a specific database by using following command: select pg_database_size('databaseName');

Read More