Managing Amazon Aurora PostgreSQL

The following section discusses managing performance and scaling for an Amazon Aurora PostgreSQL DB cluster. It also includes information about other maintenance tasks.

Topics

• Scaling Aurora PostgreSQL DB instances
• Maximum connections to an Aurora PostgreSQL DB instance
• Temporary storage limits for Aurora PostgreSQL
• Testing Amazon Aurora PostgreSQL by using fault injection queries
• Displaying volume status for an Aurora PostgreSQL DB cluster
• Specifying the RAM disk for the stats_temp_directory
• Scheduling maintenance with the PostgreSQL pg_cron extension

Scaling Aurora PostgreSQL DB instances

You can scale Aurora PostgreSQL DB instances in two ways, instance scaling and read scaling. For more information about read scaling, see Read scaling.

You can scale your Aurora PostgreSQL DB cluster by modifying the DB instance class for each DB instance in the DB cluster. Aurora PostgreSQL supports several DB instance classes optimized for Aurora. Don't use db.t2 or db.t3 instance classes for larger Aurora clusters of size greater than 40 terabytes (TB). For detailed specifications of the DB instance classes supported by Aurora PostgreSQL, see Supported DB engines for DB instance classes.

Maximum connections to an Aurora PostgreSQL DB instance

An Aurora DB cluster allocates resources based on the DB instance class and its available memory. The maximum number of connections allowed by an Aurora PostgreSQL DB instance is determined by the max_connections parameter value specified in the parameter group for that DB instance.

Keep the following factors in mind before you try to change the max_connections parameter setting.

• If the max_connections value is too low, the Aurora PostgreSQL DB instance might not have sufficient connections available when clients attempt to connect.

• If the max_connections value exceeds the number of connections that are actually needed, the unused connections can cause performance to degrade.

The ideal setting for the max_connections parameter is one that supports all the client connections your application needs, without an excess of unused connections, plus at least 3 more connections to support AWS automation.

The value of max_connections in the default DB parameter group for Aurora PostgreSQL is set to the lower of two values derived from the following Aurora PostgreSQL LEAST function:

LEAST({DBInstanceClassMemory/9531392},5000)

Although you can't change values in default parameter groups, you can create your own custom DB cluster parameter group and modify your Aurora PostgreSQL DB cluster to use it. If you do this, be sure that you reboot the DB cluster after applying your custom parameter group. For more information, see Amazon Aurora PostgreSQL parameters and Creating a DB cluster parameter group. To learn more about Aurora DB cluster and DB parameter groups, see Working with DB parameter groups and DB cluster parameter groups.

Following, you can find a table that lists the highest value that should ever be used for max_connections for each DB instance class that can be used with Aurora PostgreSQL.

Instance class	max_connections default value
db.x2g.16xlarge	5000
db.x2g.12xlarge	5000
db.x2g.8xlarge	5000
db.x2g.4xlarge	5000
db.x2g.2xlarge	5000
db.x2g.xlarge	5000
db.x2g.large	3479
db.r6g.16xlarge	5000
db.r6g.12xlarge	5000
db.r6g.8xlarge	5000
db.r6g.4xlarge	5000
db.r6g.2xlarge	5000
db.r6g.xlarge	3479
db.r6g.large	1722
db.r5.24xlarge	5000
db.r5.16xlarge	5000
db.r5.12xlarge	5000
db.r5.8xlarge	5000
db.r5.4xlarge	5000
db.r5.2xlarge	5000
db.r5.xlarge	3300
db.r5.large	1600
db.r4.16xlarge	5000
db.r4.8xlarge	5000
db.r4.4xlarge	5000
db.r4.2xlarge	5000
db.r4.xlarge	3200
db.r4.large	1600
db.t4g.large	844
db.t4g.medium	405
db.t3.large	844
db.t3.medium	420

If your application needs more connections than the number listed for your DB instance class, consider the following alternatives.

• Choose a DB instance class that has more memory. If your connection requirements are too high for the DB instance class supporting your Aurora PostgreSQL DB cluster, you can potentially overload your database and decrease performance. For list of DB instance classes for Aurora PostgreSQL, see Supported DB engines for DB instance classes. For the amount of memory for each DB instance class, Hardware specifications for DB instance classes for Aurora.

• Pool connections by using RDS Proxy with your Aurora PostgreSQL DB cluster. For more information, see Using Amazon RDS Proxy.

Temporary storage limits for Aurora PostgreSQL

Aurora PostgreSQL stores tables and indexes in the Aurora storage subsystem. Aurora PostgreSQL uses separate temporary storage for non-persistent temporary files. This includes files that are used for such purposes as sorting large datasets during query processing or for index build operations. For more about storage, see Amazon Aurora storage and reliability.

The following table shows the maximum amount of temporary storage available for each Aurora PostgreSQL DB instance class.

DB instance class	Maximum temporary storage available (GiB)
db.r6g.16xlarge	1008
db.r6g.12xlarge	756
db.r6g.8xlarge	504
db.r6g.4xlarge	252
db.r6g.2xlarge	126
db.r6g.xlarge	63
db.r6g.large	32
db.r5.24xlarge	1500
db.r5.16xlarge	1008
db.r5.12xlarge	748
db.r5.8xlarge	504
db.r5.4xlarge	249
db.r5.2xlarge	124
db.r5.xlarge	62
db.r5.large	31
db.r4.16xlarge	960
db.r4.8xlarge	480
db.r4.4xlarge	240
db.r4.2xlarge	120
db.r4.xlarge	60
db.r4.large	30
db.t4g.large	16.5
db.t4g.medium	8.13
db.t3.large	16
db.t3.medium	7.5

You can monitor the temporary storage available for a DB instance with the FreeLocalStorage CloudWatch metric, described in Amazon Aurora metrics.

For some workloads, you can reduce the amount of temporary storage by allocating more memory to the processes that are performing the operation. To increase the memory available to an operation, increasing the values of the work_mem or maintenance_work_mem PostgreSQL parameters.