Improving query performance for Aurora PostgreSQL with Aurora Optimized Reads

You can achieve faster query processing for Aurora PostgreSQL with Aurora Optimized Reads. An Aurora PostgreSQL DB instance that uses Aurora Optimized Reads delivers up to 8x improved query latency and up to 30% cost savings for applications with large datasets, that exceed the memory capacity of a DB instance.

Overview of Aurora Optimized Reads in PostgreSQL

Aurora Optimized Reads is available by default when you create a DB cluster that uses Graviton-based R6gd and Intel-based R6id instances with non-volatile memory express (NVMe) storage. It is available from the following PostgreSQL versions:

Aurora Optimized Reads supports two capabilities: tiered cache and temporary objects.

Optimized Reads-enabled tiered cache - Using tiered cache, you can extend your DB instance caching capacity by up to 5x the instance memory. This automatically maintains the cache to contain the most recent, transactionally consistent data, freeing applications from the overhead of managing the data currency of external result-set based caching solutions. It offers up to 8x better latency for queries that were previously fetching data from Aurora storage.

In Aurora, the value for shared_buffers in the default parameter group is usually set to around 75% of the available memory. However, for the r6gd and r6id instance types, Aurora will reduce the shared_buffers space by 4.5% to host the metadata for the Optimized Reads cache.

Optimized Reads-enabled temporary objects - Using temporary objects, you can achieve faster query processing by placing the temporary files that are generated by PostgreSQL on the local NVMe storage. This reduces the traffic to Elastic Block Storage (EBS) over the network. It offers up to 2x better latency and throughput for advanced queries that sort, join, or merge large volumes of data that do not fit within the memory capacity available on a DB instance.

On an Aurora I/O-Optimized cluster, Optimized Reads makes use of both tiered cache and temporary objects on NVMe storage. With Optimized Reads-enabled tiered cache capability, Aurora allocates 2x the instance memory for temporary objects, approximately 10% of the storage for internal operations and the remaining storage as tiered cache. On an Aurora Standard cluster, Optimized Reads makes use of only temporary objects.

In Aurora PostgreSQL, use the temp_tablespaces parameter to configure the table space where the temporary objects are stored.

To check whether the temporary objects are configured, use the following command:

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

The aurora_temp_tablespace is a tablespace configured by Aurora that points to the NVMe local storage. You can't modify this parameter or switch back to Amazon EBS storage.

To check whether optimized reads cache is turned on, use the following command:

postgres=> show shared_preload_libraries;
                 shared_preload_libraries
--------------------------------------------------------
rdsutils,pg_stat_statements,aurora_optimized_reads_cache    

Using Aurora Optimized Reads

When you provision an Aurora PostgreSQL DB instance with one of the NVMe-based DB instances, the DB instance automatically uses Aurora Optimized Reads.

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

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

To switch back to a non-optimized reads Aurora instance, modify the DB instance class of your Aurora instance to the similar instance class without NVMe ephemeral 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 Aurora DB instance.

Use cases for Aurora Optimized Reads

Optimized Reads-enabled tiered cache

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

Optimized Reads-enabled temporary objects

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

Monitoring DB instances that use Aurora Optimized Reads

You can monitor your queries that use Optimized Reads-enabled tiered cache with the EXPLAIN command as shown in the following example:

Postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT c FROM sbtest15 WHERE id=100000000                   

QUERY PLAN
--------------------------------------------------------------------------------------
 Index Scan using sbtest15_pkey on sbtest15  (cost=0.57..8.59 rows=1 width=121) (actual time=0.287..0.288 rows=1 loops=1)
   Index Cond: (id = 100000000)
   Buffers: shared hit=3 read=2 aurora_orcache_hit=2
   I/O Timings: shared/local read=0.264
 Planning:
   Buffers: shared hit=33 read=6 aurora_orcache_hit=6
   I/O Timings: shared/local read=0.607
 Planning Time: 0.929 ms
 Execution Time: 0.303 ms
(9 rows)
Time: 2.028 ms
            
        

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

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

You can also use the pg_proctab extension to monitor NVMe storage.

postgres=>select * from pg_diskusage();

major | minor |       devname       | reads_completed | reads_merged | sectors_read | readtime | writes_completed | writes_merged | sectors_written | writetime | current_io | iotime  | totaliotime
------+-------+---------------------+-----------------+--------------+--------------+----------+------------------+---------------+-----------------+-----------+------------+---------+-------------
      |       | rdstemp             |           23264 |            0 |       191450 |    11670 |          1750892 |             0 |        24540576 |    819350 |          0 | 3847580 |      831020
      |       | rdsephemeralstorage |           23271 |            0 |       193098 |     2620 |           114961 |             0 |        13845120 |    130770 |          0 |  215010 |      133410
(2 rows)    

Best practices for Aurora Optimized Reads

Use the following best practices for Aurora Optimized Reads: