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.