Skip to content

Avoid autovacuum for entire databases

We have a database with production data for a platform and another database with a production database dump for testing queries. We also have a test database but this one has slightly different DDL because it's for developing new features. Now autovacuum is running on this server and keeps vacuuming this test database.
A simple way for stopping autovacuum from even checking this db at all is adding an entry for every table in pg_autovacuum:

INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age)
SELECT oid,false,-1,-1,-1,-1,-1,-1,-1,-1 FROM pg_class WHERE relkind='r';

This adds a stop entry for every table in your database, even for system tables. If you want the autovacuum daemon to just ignore tables from a specific schema, add a "relnamespace=scheme_oid" in the WHERE clause.