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.