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