Why is the default value of the shared_buffers DB parameter for Aurora PostgreSQL higher than the default value for Amazon RDS PostgreSQL?
Last updated: 2020-01-13
Why is the default value of the shared_buffers DB parameter for Amazon Aurora PostgreSQL higher than the default value on Amazon Relational Database Service (Amazon RDS) PostgreSQL?
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. But for Aurora DB instances, the default value of the DB parameter group is set to 75% of total memory. This is 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.