Best practices with Amazon Aurora PostgreSQL - Amazon Aurora
Troubleshooting storage issuesAvoiding slow performance, automatic restart, and failover for Aurora PostgreSQL DB instances

Best practices with Amazon Aurora PostgreSQL

Following, you can find several best practices for managing your Amazon Aurora PostgreSQL DB cluster. Be sure to also review basic maintenance tasks. For more information, see Managing Amazon Aurora PostgreSQL.

Topics

Troubleshooting storage issues

If the amount of working memory needed for sort or index-creation operations exceeds the amount allocated by the work_mem parameter, Aurora PostgreSQL writes the excess data to temporary disk files. When it writes the data, Aurora PostgreSQL uses the same storage space that it uses for storing error and message logs, that is, local storage. Each instance in your Aurora PostgreSQL DB cluster has an amount of local storage available. The amount of storage is based on its DB instance class. To increase the amount of local storage, you need to modify the instance to use a larger DB instance class. For DB instance class specifications, see Hardware specifications for DB instance classes for Aurora.

You can monitor your Aurora PostgreSQL DB cluster's local storage space by watching the Amazon CloudWatch metric for FreeLocalStorage. This metric reports the amount of storage available to each DB instance in the Aurora DB cluster for temporary tables and logs. For more information, see Monitoring Amazon Aurora metrics with Amazon CloudWatch.

Sorting, indexing, and grouping operations start in working memory but often must be offloaded to local storage. If your Aurora PostgreSQL DB cluster runs out of local storage because of these types of operations, you can resolve the issue by taking one of the following actions.

  • Increase the amount of working memory. This reduces the need to use local storage. By default, PostgreSQL allocates 4 MB for each sort, group, and index operation. To check the current working memory value for your Aurora PostgreSQL DB cluster's writer instance, connect to the instance using psql and run the following command.

    postgres=> SHOW work_mem;
work_mem
----------
 4MB
(1 row)

    You can increase the working memory at the session level before sort, group, and other operations, as follows.

    SET work_mem TO '1 GB';

    For more information about working memory, see Resource Consumption in the PostgreSQL documentation.

  • Change the log retention period so that logs are stored for shorter timeframes. To learn how, see Aurora PostgreSQL database log files.

For Aurora PostgreSQL DB clusters larger than 40 TB, don't use db.t2, db.t3, or db.t4g instance classes. We recommend using the T DB instance classes only for development and test servers, or other non-production servers. For more information, see DB instance class types.

Avoiding slow performance, automatic restart, and failover for Aurora PostgreSQL DB instances

If you're running a heavy workload or workloads that spike beyond the allocated resources of your DB instance, you can exhaust the resources on which you're running your application and Aurora database. To get metrics on your database instance such as CPU utilization, memory usage, and number of database connections used, you can refer to the metrics provided by Amazon CloudWatch, Performance Insights, and Enhanced Monitoring. For more information on monitoring your DB instance, see Monitoring metrics in an Amazon Aurora cluster.

If your workload exhausts the resources you're using, your DB instance might slow down, restart, or even fail over to another DB instance. To avoid this, monitor your resource utilization, examine the workload running on your DB instance, and make optimizations where necessary. If optimizations don't improve the instance metrics and mitigate the resource exhaustion, consider scaling up your DB instance before you reach its limits. For more information on available DB instance classes and their specifications, see Aurora DB instance classes.