autovacuum_naptime - AWS Prescriptive Guidance

autovacuum_naptime

The autovacuum_naptime parameter controls the time interval between successive runs of the autovacuum process. The default value is 15 seconds for Amazon RDS for PostgreSQL and 5 seconds for Aurora PostgreSQL-Compatible.

For example, let's say that your Amazon RDS for PostgreSQL database has a table that receives a high volume of write and delete operations. If you keep the default setting, the frequent autovacuum scans will be disruptive to this highly transactive table. If you set this parameter to a high value, there will be a longer interval between successive scans, and dead rows will be removed less frequently.

You can use autovacuum_naptime to manage the load caused by the vacuum process, especially if you have a busy server that already has a high CPU or I/O load. The longer you set the nap time, the less frequently autovacuum runs, which reduces the load on the server. However, setting autovacuum_naptime to a very high value can cause your PostgreSQL tables to grow and dead rows to accumulate, leading to a performance decrease. We recommend that you monitor the performance of the autovacuum process and adjust the autovacuum_naptime setting as needed.

AWS CLI syntax

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

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

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

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

Default value: 15 seconds (Amazon RDS for PostgreSQL), 5 seconds (Aurora PostgreSQL-Compatible)