Improving query performance for Aurora PostgreSQL with Aurora Optimized Reads - Amazon Aurora

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:

  • 16.1 and all higher versions

  • 15.4 and higher versions

  • 14.9 and higher 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.

Engine Cluster storage configuration Optimized Reads-enabled temporary objects Optimized Reads-enabled tiered cache Versions supported
Aurora PostgreSQL-Compatible Edition Standard Yes No Aurora PostgreSQL version 16.1 and all higher versions, 15.4 and higher, version 14.9 and higher
I/O-Optimized Yes Yes

A switch between IO-Optimized and Standard clusters on a NVMe-based DB instance class causes an immediate database engine restart.

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 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:

  • Internet scale applications such as payments processing, billing, e-commerce with strict performance SLAs.

  • Real-time reporting dashboards that run hundreds of point queries for metrics/data collection.

  • Generative AI applications with the pgvector extension to search exact or nearest neighbors across millions of vector embeddings.

Optimized Reads-enabled temporary objects

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

  • 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.

  • CREATE INDEX or REINDEX operations for sorting.

  • Other workloads that use internal temporary tables.

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

aurora_orcache_hit and aurora_storage_read fields in the Buffers section of the explain plan are shown only when Optimized Reads is turned on and their values are greater than zero. The read field is the total of the aurora_orcache_hit and aurora_storage_read fields.

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

  • AuroraOptimizedReadsCacheHitRatio

  • FreeEphemeralStorage

  • ReadIOPSEphemeralStorage

  • ReadLatencyEphemeralStorage

  • ReadThroughputEphemeralStorage

  • WriteIOPSEphemeralStorage

  • WriteLatencyEphemeralStorage

  • WriteThroughputEphemeralStorage

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:

  • Monitor the storage space available on the instance store with the CloudWatch metric FreeEphemeralStorage. If the instance store is reaching its limit because of the workload on the DB instance, tune the concurrency and queries which heavily use temporary objects or modify it to use a larger DB instance class.

  • Monitor the CloudWatch metric for the Optimized Reads cache hit rate. Operations like VACUUM modify large numbers of blocks very quickly. This can cause a temporary drop in the hit ratio. The pg_prewarm extension can be used to load data into the buffer cache that allows Aurora to proactively write some of those blocks to the Optimized Reads cache.

  • You can enable cluster cache management (CCM) to warm up the buffer cache and tiered cache on a tier-0 reader, which will be used as a failover target. When CCM is enabled, the buffer cache is periodically scanned to write pages eligible for eviction in tiered cache. For more information on CCM, see Fast recovery after failover with cluster cache management for Aurora PostgreSQL.