autovacuum_analyze_threshold

The autovacuum_analyze_threshold parameter is similar to autovacuum_vacuum_threshold. It controls the minimum number of tuple inserts, updates, or deletes that must occur on a table before autovacuum analyzes it. This setting can be useful to prevent unnecessary vacuuming on tables that don't have a high rate of these operations. The default value is 50, which is the PostgreSQL engine default, for both Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible.

For example, let's say you have a table with 100,000 rows and you keep the autovacuum_analyze_threshold default at 50. If the table receives only 49 inserts, updates, or deletes, autovacuum won't analyzes it. If the table receives 50 or more inserts, updates, or deletes, autovacuum will analyze it, keeping the value of autovacuum_analyze_scale_factor multiplied by the number of table rows as a controlling factor.

AWS CLI syntax

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

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

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

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

Default value: 50 operations

This parameter works in conjunction with the autovacuum_analyze_scale_factor parameter, so take both settings into account when you configure autovacuum.

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. Setting autovacuum_analyze_threshold too high can cause the statistics to become stale, leading to poor performance. We recommend that you monitor the performance of the autovacuum process and adjust the settings as needed.

For additional information about this parameter, see the AWS blog post Understanding autovacuum in Amazon RDS for PostgreSQL environments.