Why is the default value of the shared_buffers DB parameter for Aurora PostgreSQL higher than the default value for Amazon RDS PostgreSQL?

2 minute read
0

I want to understand the differences between default values of DB parameter groups for Amazon Aurora PostgreSQL and Amazon Relational Database Service (Amazon RDS) PostgreSQL?

Resolution

PostgreSQL recommends using a reasonable starting value for shared_buffers of 25% of the memory in your system. However, you can change this value depending on your workload requirements. To find an appropriate value for shared_buffers for your workload, see the PostgreSQL documentation for Resource Consumption.

PostgreSQL relies heavily on the operating system for its caching (double buffering). As a result, allocating more than 40% of RAM to shared_buffers is unlikely to perform better than a smaller value.

For RDS DB instances, the default value of the DB parameter group is set to 25% of total memory. For Aurora DB instances, the default value of the DB parameter group is set between 50% and 75%, depending on the instance class. Default values for Aurora DB instances are higher because Aurora PostgreSQL eliminates double buffering and doesn't utilize file system cache. As a result, Aurora PostgreSQL can increase shared_buffers to improve performance. It's a best practice to use the default value of 75% for the shared_buffers DB parameter when using Aurora PostgreSQL. A smaller value can degrade performance by reducing the available memory to the data pages while also increasing I/O on the Aurora storage subsystem. For more information, see Amazon Aurora PostgreSQL reference.

Note: You might need to reduce the value of the shared_buffers parameter for some workloads. For example, you might need to reduce the value if your workload accepts a large number of connections that is higher than the default value, or if you frequently use larger work_mem, maintenance_work_mem, and so on. However, a smaller value for shared_buffers means that the cache hit ratio is also lower. As a result, you might see an increase in access latency for database pages because of frequent storage access.


Related information

Working with parameter groups

AWS OFFICIAL
AWS OFFICIALUpdated a year ago