How to Enable/Disable autovacuum on PostgreSQL

Introduced in PostgreSQL 8.1, the AUTOVACUUM daemon is an optional feature that automatically vacuums the database so that you don't have to manually run the VACUUM statement. The AUTOVACUUM daemon is enabled in the default configuration.

The AUTOVACUUM daemon is made up of multiple processes that reclaim storage by removing obsolete data or tuples from the database. It checks for tables that have a significant number of inserted, updated, or deleted records and vacuums these tables based on the configuration settings below.

Default Settings

The AUTOVACUUM settings for PostgreSQL can be found in the postgresql.conf file and control when/how the daemon runs. The following is an example of these settings:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on                        # Enable autovacuum subprocess?  'on'
                                       # requires track_counts to also be on.

log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
                                       # their durations, > 0 logs only
                                       # actions running at least this number
                                       # of milliseconds.

autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                       # (change requires restart)

autovacuum_naptime = 1min              # time between autovacuum runs

autovacuum_vacuum_threshold = 50       # min number of row updates before vacuum

autovacuum_analyze_threshold = 50      # min number of row updates before analyze

autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum

autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze

autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                                       # (change requires restart)

autovacuum_multixact_freeze_max_age = 400000000
                                       # maximum multixact age
                                       # before forced vacuum
                                       # (change requires restart)

autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                       # autovacuum, in milliseconds;
                                       # -1 means use vacuum_cost_delay

autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                       # autovacuum, -1 means use
                                       # vacuum_cost_limit

Current Settings

You can view the AUTOVACUUM settings by one of two ways. You can either open the postgresql.conf file and view the AUTOVACUUM parameters (like above). Or if you are logged into the database, you can run the following query:

SELECT *
FROM pg_settings 
WHERE name LIKE 'autovacuum%'

This query will return the current system settings for the AUTOVACUUM daemon, but it is important to note that you can not update these settings using a query.

Update Settings

To change the settings for the AUTOVACUUM daemon, you will need to find and edit the settings stored within the postgresql.conf file. The location of the postgresql.conf file will vary depending on the system that you are on.

Once you have edited the settings within the postgresql.conf file, you will be required to restart the database for the changes to take effect.

Disable AUTOVACUUM on a Table

When the system settings for AUTOVACUUM are turned on, you can disable the autovacuum for a specific table, if you choose. This is done by running a query within the database.

The syntax to disable the autovacuum for a table in PostgreSQL is:

ALTER TABLE table_name SET (autovacuum_enabled = false);

table_name

The table that you do not wish to autovacuum.

For example:

ALTER TABLE products SET (autovacuum_enabled = false);

In this example, the AUTOVACUUM daemon would be overriden so that the products table is not vacuumed automatically.

If you are not sure whether a table's AUTOVACUUM feature has been disabled, you can run the following query:

SELECT reloptions
FROM pg_class
WHERE relname = 'products';

This would return the AUTOVACUUM setting for the products table. If AUTOVACUUM has been disabled, your query will return something like this:

         reloptions         
----------------------------
 {autovacuum_enabled=false}
(1 row)

In this example, the products table has autovacuum_enabled set to false. This means that the AUTOVACUUM daemon will not try to vacuum the products table.