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.