autovacuum_vacuum_cost_limit - AWS Prescriptive Guidance

autovacuum_vacuum_cost_limit

The autovacuum_vacuum_cost_limit parameter controls the amount of CPU and I/O resources that an autovacuum worker can consume.

Limiting the resource usage of autovacuum processes can help prevent them from consuming too much CPU or disk I/O, which might impact the performance of other queries that run on the same system. The parameter specifies a cost limit, which is a unit of work that the worker is allowed to perform before it must pause and check to see if it's still under the limit. For example, if the parameter is set to 2,000, a worker is allowed to process 2,000 units of work before pausing.

You can set the autovacuum_vacuum_cost_limit parameter by using the SET command in a PostgreSQL session, or use an AWS CLI command.

AWS CLI syntax

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

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

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

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

Default value: GREATEST({log(DBInstanceClassMemory/21474836480)*600},200) units of work

If you set the value of autovacuum_vacuum_cost_limit too high, the autovacuum process might consume too many resources and slow down other queries. If you set it too low, the autovacuum process might not reclaim enough space, which causes the table to become larger over time. It's essential to find the right balance that works for your system.

This parameter affects only the autovacuum process, not the manual VACUUM commands. Also, it only applies to the autovacuum processes for VACUUM but not for ANALYZE.