Improving query performance for RDS for PostgreSQL with Amazon RDS Optimized Reads - Amazon Relational Database Service
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Improving query performance for RDS for PostgreSQL with Amazon RDS Optimized Reads

You can achieve faster query processing for RDS for PostgreSQL with Amazon RDS Optimized Reads. An RDS for PostgreSQL DB instance or Multi-AZ DB cluster that uses RDS Optimized Reads can achieve up to 50% faster query processing compared to one that doesn't use it.

Overview of RDS Optimized Reads in PostgreSQL

Optimized Reads is available by default on RDS for PostgreSQL versions 15.2 and higher, 14.7 and higher, and 13.10 and higher.

When you use an RDS for PostgreSQL DB instance or Multi-AZ DB cluster that has RDS Optimized Reads turned on, it achieves up to 50% faster query performance using the local Non-Volatile Memory Express (NVMe) based solid state drive (SSD) block-level storage. You can achieve faster query processing by placing the temporary tables that are generated by PostgreSQL on the local storage, which reduces the traffic to Elastic Block Storage (EBS) over the network.

In PostgreSQL, temporary objects are assigned to a temporary namespace that drops automatically at the end of the session. The temporary namespace while dropping removes any objects that are session-dependent, including schema-qualified objects, such as tables, functions, operators, or even extensions.

In RDS for PostgreSQL, the temp_tablespaces parameter is configured for this temporary work area where the temporary objects are stored.

The following queries return the name of the tablespace and its location.

postgres=> show temp_tablespaces; temp_tablespaces --------------------- rds_temp_tablespace (1 row)

The rds_temp_tablespace is a tablespace configured by RDS that points to the NVMe local storage. You can always switch back to Amazon EBS storage by modifying this parameter in the Parameter group using the Amazon Web Services Management Console to point to any tablespace other than rds_temp_tablespace. For more information, see Modifying parameters in a DB parameter group. You can also use the SET command to modify the value of the temp_tablespaces parameter to pg_default at the session level using SET command. Modifying the parameter redirects the temporary work area to Amazon EBS. Switching back to Amazon EBS helps when the local storage for your RDS instance or cluster isn't sufficient to perform a specific SQL operation.

postgres=> SET temp_tablespaces TO 'pg_default'; SET
postgres=> show temp_tablespaces; temp_tablespaces ------------------ pg_default

Use cases for RDS Optimized Reads

The following are some use cases that can benefit from Optimized Reads:

  • Analytical queries that include Common Table Expressions (CTEs), derived tables, and grouping operations.

  • Read replicas that handle the unoptimized queries for an application.

  • On-demand or dynamic reporting queries with complex operations such as GROUP BY and ORDER BY that can't always use appropriate indexes.

  • Other workloads that use internal temporary tables.

  • CREATE INDEX or REINDEX operations for sorting.

Best practices for RDS Optimized Reads

Use the following best practices for RDS Optimized Reads:

  • Add retry logic for read-only queries in case they fail because the instance store is full during the execution.

  • Monitor the storage space available on the instance store with the CloudWatch metric FreeLocalStorage. If the instance store is reaching its limit because of the workload on the DB instance or Multi-AZ DB cluster, modify it to use a larger DB instance class.

Using RDS Optimized Reads

When you provision an RDS for PostgreSQL DB instance with one of the NVMe based DB instance classes in a Single-AZ DB instance deployment, Multi-AZ DB instance deployment, or Multi-AZ DB cluster deployment, the DB instance automatically uses RDS Optimized Reads.

For more information about Multi-AZ deployment, see Configuring and managing a Multi-AZ deployment.

To turn on RDS Optimized Reads, do one of the following:

  • Create an RDS for PostgreSQL DB instance or Multi-AZ DB cluster using one of the NVMe based DB instance classes. For more information, see Creating an Amazon RDS DB instance.

  • Modify an existing RDS for PostgreSQL DB instance or Multi-AZ DB cluster to use one of the NVMe based DB instance classes. For more information, see Modifying an Amazon RDS DB instance.

RDS Optimized Reads is available in all Amazon Web Services Regions where one or more of the DB instance classes with local NVMe SSD storage are supported. For more information, see DB instance classes.

To switch back to a non-optimized reads RDS instance, modify the DB instance class of your RDS instance or cluster to the similar instance class that only supports EBS storage for your database workloads. For example, if the current DB instance class is db.r6gd.4xlarge, choose db.r6g.4xlarge to switch back. For more information, see Modifying an Amazon RDS DB instance.

Monitoring DB instances that use RDS Optimized Reads

You can monitor DB instances that use RDS Optimized Reads using the following CloudWatch metrics:

  • FreeLocalStorage

  • ReadIOPSLocalStorage

  • ReadLatencyLocalStorage

  • ReadThroughputLocalStorage

  • WriteIOPSLocalStorage

  • WriteLatencyLocalStorage

  • WriteThroughputLocalStorage

These metrics provide data about available instance store storage, IOPS, and throughput. For more information about these metrics, see Amazon CloudWatch instance-level metrics for Amazon RDS.

To monitor current usage of your local storage, log in to your database using the following query:

SELECT spcname AS "Name", pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "size" FROM pg_catalog.pg_tablespace WHERE spcname IN ('rds_temp_tablespace');

For more information about the temporary files and their usage, see Managing temporary files with PostgreSQL.

Limitations for RDS Optimized Reads in PostgreSQL

The following limitation apply to RDS Optimized Reads in PostgreSQL:

  • Transactions can fail when the instance store is full.