autovacuum_analyze_scale_factor

The autovacuum_analyze_scale_factor parameter controls how aggressive the autovacuum process should be when analyzing (collecting statistics about the distribution of data in a table.

The autovacuum process uses this parameter to calculate a threshold based on the number of tuples in a table. If the number of tuple inserts, updates, or deletes exceeds this threshold, autovacuum analyzes the table. The default value is 0.05 (that is, 5 percent of the tuples must be modified) for both Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible.

For example, let's say that your table has 1,000,000 tuples and you keep the default autovacuum_analyze_scale_factor value at 0.05. If the table receives 50,000 or more updates or deletes, autovacuum vacuums it, depending on the autovacuum_analyze_threshold value and adding the number of table rows as a controlling factor.

AWS CLI syntax

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

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

# Modify autovacuum_analyze_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_analyze_scale_factor,ParameterValue=<new_value>,ApplyMethod=immediate"

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

Default value: 0.05 (5 percent)

It's essential for the query planner to collect statistics in order to make informed decisions, such as how to access the data and how to organize it, so we recommend that you monitor the performance of the autovacuum process and adjust the settings as needed to ensure that statistics are up to date.

The autovacuum_analyze_scale_factor parameter works in conjunction with the autovacuum_analyze_threshold, autovacuum_analyze_cost_limit, and autovacuum_naptime parameters. The optimal setting depends on the specific requirements of your database and table size and the frequency of updates. For additional information about this parameter, see the AWS blog post Understanding autovacuum in Amazon RDS for PostgreSQL environments.