autovacuum_work_mem - AWS Prescriptive Guidance

autovacuum_work_mem

autovacuum_work_mem is a PostgreSQL configuration parameter that controls the amount of memory used by the autovacuum process when it performs table maintenance tasks such as vacuuming or analysis.

In Aurora and Amazon RDS, you can adjust the value of autovacuum_work_mem to optimize performance.

AWS CLI syntax

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

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

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

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

Default value: GREATEST({DBInstanceClassMemory/32768},131072) KB in Aurora PostgreSQL-Compatible, 64 MB in Amazon RDS for PostgreSQL. However, the default value might vary depending on the specific version of Amazon RDS or Aurora you're using.

Example

Your Amazon RDS for PostgreSQL database has a large table that is frequently updated. Over time, you notice that the database becomes slower, and you suspect that autovacuum is taking too long to complete.

As part of your investigation, you check the system logs, use the pg_stat_activity view to see which queries and processes are currently running, check the pg_stat_user_tables view to see statistics for each table, use the pg_settings view to compare the value of autovacuum_work_mem to the available memory on the system, and monitor memory usage for spikes. After you gather this information, you can set autovacuum_work_mem to the optimal value that your workload needs. To find the right balance between memory usage and performance, you might decide to set it to one fourth of the available memory on the system. After you change the value, you monitor the performance of the database and might see that autovacuum completes much faster than before and your database performs faster overall.