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.