autovacuum_vacuum_scale_factor - AWS Prescriptive Guidance

autovacuum_vacuum_scale_factor

The autovacuum_vacuum_scale_factor configuration parameter controls how aggressive the autovacuum process should be when vacuuming a table.

The vacuum scale factor is a fraction of the total number of tuples in a table that must be modified before autovacuum cleans the table. The default value is 0.1 (that is, 10 percent of the tuples must be modified). For example, if a table has 1,000,000 tuples, and 100,000 of those tuples are marked as dead or deleted, autovacuum vacuums the table, depending on the value of autovacuum_vacuum_threshold as a controlling factor.

The autovacuum_vacuum_scale_factor parameter helps you control how frequently the vacuum process runs. If a table receives a lot of write operations, you might want to lower the vacuum scale factor so autovacuum runs more frequently, and keep the table smaller. Conversely, if a table receives few write operations, you might want to raise the vacuum scale factor so autovacuum runs less frequently, and save resources.

AWS CLI syntax

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

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

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

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

Default value: 0.1

The autovacuum_vacuum_scale_factor parameter works in conjunction with the autovacuum_vacuum_threshold, autovacuum_vacuum_cost_limit, and autovacuum_naptime parameters. For additional information about this parameter, see the AWS blog post Understanding autovacuum in Amazon RDS for PostgreSQL environments.