shared_buffers

The shared_buffers parameter controls the amount of memory that PostgreSQL uses to cache data in memory. Setting this parameter to an appropriate value can help improve query performance.

For Amazon RDS, the default value for shared_buffers is set to {DBInstanceClassMemory/32768} bytes, based on the available memory for the DB instance. For Aurora, the default value is set to {DBInstanceClassMemory/12038,-50003}, based on the available memory for the DB instance. The optimal value for this parameter depends on several factors, including the size of the database, the number of concurrent connections, and the available instance memory.

AWS CLI syntax

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

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

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

Type: Static (applying changes requires a reboot)

Default value: {DBInstanceClassMemory/32768} bytes in Amazon RDS for PostgreSQL, {DBInstanceClassMemory/12038,-50003} in Aurora PostgreSQL-Compatible. In most cases, this equation works out to be about 25 percent of your system's memory. Following this guideline, the shared_buffers setting in the parameter group is set up by using PostgreSQL's default units of 8K buffers instead of bytes or kilobytes.

The shared_buffers parameter setting can have a significant impact on performance, so we recommend that you test your changes thoroughly to ensure that the value is appropriate for your workload.

Example

Let's say you have a financial services application that is running a PostgreSQL database on Amazon RDS or Aurora. This database is used to store customer transaction data. It has a large number of tables and is accessed by multiple applications on a large number of servers. The application is experiencing slow query performance and high CPU usage. You determine that tuning the shared_buffers parameter might help improve performance.

In Amazon RDS for PostgreSQL, the default value of shared_buffers is set to {DBInstanceClassMemory/32768} bytes of available memory in db.r5.xlarge (for example, 3 GB). To determine the appropriate value for shared_buffers, you run a series of tests with varying values of shared_buffers, starting with the default value of available memory and increasing the value gradually. For each test, you measure the query performance and CPU usage of the database.

Based on the test results, you determine that setting the value of shared_buffers to 8 GB results in the best overall query performance and CPU usage for your workload. The value is determined through a combination of testing and analysis of workload characteristics, including the size of the database, the number and complexity of queries, the number of concurrent users, and available system resources. After you make the change, your monitoring systems check the performance of the database to ensure that the new value is appropriate for your workload. You can then fine-tune additional parameters as necessary to further improve performance.