autovacuum_vacuum_threshold
The autovacuum_vacuum_threshold
parameter controls the minimum number of
tuple update or delete operations that must occur on a table before autovacuum vacuums it.
This setting can be useful to prevent unnecessary vacuuming on tables that do not 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
autovacuum_vacuum_threshold
is set to 50. If the table receives only 49 updates
or deletes, autovacuum won't vacuum it. If the table receives 50 or more updates or deletes,
autovacuum will vacuum it, depending on the value of
autovacuum_vacuum_scale_factor
multiplied by the number of table rows as a
controlling factor.
Setting this parameter too high can cause the table to grow and dead rows to accumulate, which can affect performance.
AWS CLI syntax
The following command changes autovacuum_vacuum_threshold
for a specific DB
parameter group. This change applies to all instances or clusters that use the parameter
group.
# Modify autovacuum_vacuum_threshold on a DB parameter group aws rds modify-db-parameter-group \ --db-parameter-group-name <parameter_group_name> \ --parameters "ParameterName=autovacuum_vacuum_threshold,ParameterValue=<new_value>,ApplyMethod=immediate" # Modify autovacuum_vacuum_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_vacuum_threshold,ParameterValue=<new_value>,ApplyMethod=immediate"
Type: Dynamic (changes are applied immediately if you set
ApplyMethod=immediate
)
Default value: 50 operations
The autovacuum_vacuum_threshold
parameter works in conjunction with the
autovacuum_vacuum_scale_factor
, autovacuum_vacuum_cost_limit, and
autovacuum_naptime
parameters. The optimal settings depend on the specific
requirements of your database and table size.
For additional information about this parameter, see the AWS blog post Understanding autovacuum in Amazon RDS for PostgreSQL environments