How to prevent autovacuum for a table in Postgres
I have large tables where I only have inserts and selections, so when autovacuum for those tables is running, the system is very slow. I disabled 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 that (after a while) 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 kind of wrapper am I doing to automatically disable auto-learning for these tables?
+3
source to share
1 answer
The key is here:
(to prevent wraparound)
This means that authacacuum has to in order to release transaction IDs .
You cannot completely disable this type of autovacuum, but you can reduce its frequency by adjusting the parameters autovacuum_freeze_max_age
and vacuum_freeze_min_age
.
+6
source to share