autovacuum

You can set autovacuum globally by using the autovacuum configuration parameter, or you can change it on a per-table basis by setting the autovacuum_enabled column of the pg_class table to true or false for a specific table.

When you enable autovacuum on a table, the database server periodically scans the table for dead rows and tuples and removes them in the background, without any intervention from the database administrator. This helps to keep the table small, improve query performance, and reduce the size of backups.

AWS CLI syntax

The following command enables autovacuum for a specific DB parameter group. This change applies to all instances or clusters that use the parameter group.

# Modify autovacuum on a DB parameter group
aws rds modify-db-parameter-group \
  --db-parameter-group-name <parameter_group_name> \
  --parameters "ParameterName=autovacuum,ParameterValue=true,ApplyMethod=immediate"

# Modify autovacuum on a DB cluster parameter group
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name <parameter_group_name> \
  --parameters "ParameterName=autovacuum,ParameterValue=true,ApplyMethod=immediate"

Type: Dynamic (changes are applied immediately if you set ApplyMethod=immediate)

Default value: Enabled

You can also disable or enable autovacuum on a specific table by using psql:

ALTER TABLE <table_name> SET (autovacuum_enabled = true);

Too much vacuuming can affect performance, so it's important to monitor the performance of the autovacuum process as well as the performance of your database, and adjust the settings as needed.

Example

Your PostgreSQL database has a table that receives a high volume of write and delete operations. Without autovacuum, this table would eventually become filled with dead rows (that is, rows that have been marked for deletion but haven't yet been physically removed from the table). These dead rows would take up space on the disk, slow down queries, and increase the size of backups. You can enable autovacuum on the table to automatically scan for dead rows and remove them to mitigate these issues.