Introducing Optimized Reads for Amazon RDS for PostgreSQL

In March 2022, we introduced a new Multi-AZ deployment option for Amazon Relational Database Service (Amazon RDS) for PostgreSQL, called Multi-AZ DB clusters. This deployment option increases read capacity with two readable standby instances, and improves write transaction latency and failover times. The optimization of transaction latency uses local storage on purpose-built Amazon Elastic Compute Cloud (Amazon EC2) instances. These instances are equipped with fast NVMe SSDs for local storage, ideal for high speed and low-latency storage. For more information, refer to Multi-AZ DB cluster deployments.

Database systems like PostgreSQL try to perform operations in memory for optimal performance, but there are situations where they must use a temporary work area on disk. PostgreSQL uses a temporary work area for several operations, including to hold intermediate results to build indexes, and sort or group records for queries. Intermediate results are a temporary dataset required by a SQL operation to compute the actual end result. For example, fetching the top 10 most expensive orders requires a pre-computed sort on the price value of each order in descending order. This sorting is called an intermediate result set. After sending the top 10 expensive orders from the sorted intermediate result set to client, it is discarded. At times, depending on the volume of data, building this intermediate result set requires scanning through hundreds of millions of rows from multiple tables, which can be both time- and resource-consuming.

Today, we are introducing a new Amazon Relational Database Service (Amazon RDS) deployment option, Optimized Reads, with up to twice the read performance for workloads that heavily rely on a temporary work area. Optimized Reads uses the local storage provided by the NVMe SSDs on the underlying instances used in the Multi-AZ DB clusters. By choosing this new option during instance creation or by modifying the DB instance class of your existing RDS instance to the Optimized Reads instance class, Amazon RDS can automatically improve performance for the temporary work area by using local storage instead of Amazon Elastic Block Store (Amazon EBS) for database workloads.

In this two-part series, we journey through how Amazon RDS for PostgreSQL improves performance of SQL operations with Optimized Reads. In this first post, we talk about use cases for Optimized Reads, how Amazon RDS enables local storage for the Optimized Reads feature, how to monitor the performance of local storage, when to consider migrating to an Optimized Reads instance, and the difference in performance of queries between RDS instances that use Amazon EBS and local storage. In Part 2, we discuss why PostgreSQL uses temporary storage and when PostgreSQL performs operations on disk instead of in memory. We also dive deep into analysis and comparison of performance results between Amazon EBS-only backed RDS instances and Optimized Reads RDS instances.

What use cases can benefit from Optimized Reads?

It’s important to optimize performance of queries that use the temporary work area, particularly for high concurrency workloads that heavily rely on it. One way to do this is to use an instance type with higher memory, but this may lead to overprovisioning for your overall workload. Additionally, when Amazon RDS uses Amazon EBS for temporary data processing, it also consumes your provisioned IOPS. Depending on the nature of your workload, this can create contention of IOPS and introduce latencies, which can decrease performance.

With temporary processing in local storage instead of Amazon EBS, the load on your persistent storage (Amazon EBS) can be minimized and used for other read/write operations. The number of IOPS required for temporary processing are provisioned from local storage instead of Amazon EBS. By saving provisioned IOPS, you can increase disk throughput on your RDS instance for persistent operations and improve overall performance. However, this is limited to use cases that rely on temporary storage. As outlined in New – Amazon RDS Optimized Reads and Optimized Writes, the following are some use cases that benefit from using 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
• Index builds that do not fit in maintenance_work_mem

If your workload aligns with one or more of these use cases, the following factors can further influence the benefit of using local storage:

• Concurrency – Because the data transfer for Amazon EBS occurs via network, diverting this data to a local storage results in avoiding network I/O latencies. And if your workload has highly concurrent read/write temporary processing, this greatly helps improve the overall performance of your RDS instance.
• Type of RDS instance – The difference in performance improvement may also depend on whether your RDS instance is Single-AZ or Multi-AZ. Multi-AZ instances with local storage result in better performance in comparison to Amazon EBS-only backed Multi-AZ instances. This is because in an Amazon EBS-only backed RDS Multi-AZ instance, the whole EBS volume is replicated to a secondary instance that also includes temporary data. Because the replication occurs at the storage layer, there is no control whether to exclude certain datasets from being replicated. With Optimized Reads, you avoid this completely because local storage disk is not part of the replication and is not needed for recovery or failover.

How does Amazon RDS enable local storage for temporary processing?

In this section, we discuss what allows Amazon RDS to use local storage.

DB instance class

Amazon RDS uses purpose-built Amazon Elastic Compute Cloud (Amazon EC2) instances that provide local NVMe SSD storage. The model name of the instance class has the special notation “d.” For example, db.m5d, db.r6gd, db.r6d, and so on. Amazon RDS provisions the database with capabilities to use these local disks automatically when you choose this instance class either when creating the instance or modifying the instance class of your existing RDS instance using PostgreSQL’s tablespace concept.

Tablespaces

Tablespaces are useful logical structures that are helpful in organizing data among different storage disks. This helps in two ways:

• Offloading or archiving non-critical data to low-cost storage
• Improving performance by putting critical or most-frequently used data on highly optimized storage, such as SSDs

An Optimized Reads instance uses a tablespace to point a temporary work area to local SSDs. The location of the disk is set using the PostgreSQL’s configuration setting, temp_tablespaces. The following queries return the name of the tablespace:

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

When should you use Optimized Reads?

How can you determine if Optimized Reads will benefit your workload? Outside of the general guidance this post discusses, you can use your monitoring data to evaluate if your workload can benefit from Optimized Reads. With the monitoring data from database-level monitoring queries, you can evaluate whether a read optimized RDS instance is a good fit for your workload. If you see a lot of queries that require disk access for temporary processing, an Optimized Reads instance might be a good fit. One way you can evaluate this is by restoring a snapshot of your RDS instance and replaying your workload. Refer to Restoring from a snapshot for more information.

While you are on the restoration page, under the Instance configuration option for DB instance class, you can choose the Optimized Reads instance class that is similar to your current production instance class. For example, if your production’s instance class is db.m5.4xlarge, you can choose db.m5d.4xlarge.

When you have the instance ready with a copy of your latest production snapshot and lazy loading completed, you can run the usual performance tests you do as part of your application performance evaluation. After performance testing, review results from Performance Insights and compare with results from an Amazon EBS-only backed RDS instance. For performance of specific queries, you can compare the runtimes of top queries using pg_stat_statements.total_exec_time or total_time for versions prior to PostgreSQL 13.

Monitoring performance of local storage

Understanding the historical performance data of your RDS database will help in understanding how Optimized Reads may benefit your workload. In this section, we discuss how to monitor the performance of Optimized Reads to help you decide to switch to Optimized Reads or troubleshoot any performance issues you might run into.

Amazon CloudWatch

Amazon RDS for PostgreSQL supports various ways to monitoring the performance of your RDS instance, and Amazon CloudWatch is one of them. For more information, refer to Monitoring Amazon RDS metrics with Amazon CloudWatch. With the introduction of Optimized Reads, we are adding the following additional metrics specific to performance of local storage to CloudWatch. These metrics are only visible to Optimized Reads instances.

Database-level monitoring

At the database level, PostgreSQL provides two catalog views that report statistical information of queries and databases, including temporary usage. However, this can’t differentiate whether a query is writing to Amazon EBS or local storage. You can find whether it is using local storage or Amazon EBS based on the instance class and default setting of temp_tablespaces.

pg_stat_statements

With the pg_stat_statements extension, you can monitor the distribution of temporary space usage between memory and disk for a particular query.

The following query returns the top 10 queries by temporary disk usage. You can adjust the LIMIT to view additional results.

WITH q AS (
    SELECT
        SUBSTR(query, 1, 100) query,
        calls number_of_executions,
        round(mean_exec_time::numeric / 1000 / 60, 2) avg_exec_time_in_minutes,
        (local_blks_read + local_blks_written + temp_blks_read + temp_blks_written) * 8 * 1024 AS total_disk_temp_io,
        ((temp_blks_read + temp_blks_written) * 8 * 1024) AS disk_temp_io_queries_indexes,
        ((temp_blks_written) * 8 * 1024) AS disk_temp_write_io_queries_indexes,
        ((temp_blks_read) * 8 * 1024) AS disk_temp_read_io_queries_indexes,
        ((local_blks_read + local_blks_written) * 8 * 1024) AS disk_temp_io_temp_tables,
        ((local_blks_written) * 8 * 1024) AS disk_temp_write_io_temp_tables,
        ((local_blks_read) * 8 * 1024) AS disk_temp_read_io_temp_tables,
        round(total_exec_time::numeric, 2) / 1000 / 60 AS total_exec_time_msec
    FROM (
        SELECT
            *
        FROM
            pg_stat_statements
        WHERE (local_blks_read + local_blks_written + temp_blks_read + temp_blks_written) > 0) q
ORDER BY
    total_disk_temp_io DESC
LIMIT 10
)
SELECT
    query,
    number_of_executions,
    avg_exec_time_in_minutes,
    pg_size_pretty(total_disk_temp_io) AS total_disk_temp_io,
    pg_size_pretty(disk_temp_io_queries_indexes) AS disk_temp_io_queries_indexes,
    pg_size_pretty(disk_temp_write_io_queries_indexes) AS disk_temp_write_io_queries_indexes,
    pg_size_pretty(disk_temp_read_io_queries_indexes) AS disk_temp_read_io_queries_indexes,
    pg_size_pretty(disk_temp_io_temp_tables) AS disk_temp_io_temp_tables,
    pg_size_pretty(disk_temp_write_io_temp_tables) AS disk_temp_write_io_temp_tables,
    pg_size_pretty(disk_temp_read_io_temp_tables) AS disk_temp_read_io_temp_tables
FROM
    q
ORDER BY
    avg_exec_time_in_minutes DESC;

Note that for versions prior to PostgreSQL 13, you need to use total_time instead of total_exec_time.

pg_stat_database

With pg_stat_database, you can monitor database-level temporary space usage.

The following query returns temporary disk usage per database:

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes)
FROM
    pg_stat_database
ORDER BY temp_files DESC;

Review Managing temporary files with PostgreSQL for more information.

Comparison of performance

Let’s examine the difference in performance between an Amazon EBS-only backed RDS instance and an Optimized Reads RDS instance.

Building on the discussion on how concurrency can impact read performance, let’s see how an increase in concurrency of a workload that relies on temporary storage influences the performance on a Single-AZ RDS instance.

Before we get into reviewing the results of these use cases, let’s look at the setup behind the evaluation.

Concurrency

Concurrency is defined as the number of actively running clients or sessions in parallel with concurrent read/write requests to storage subsystems, including local storage. Because our goal is learning about local storage, we focus more on use cases that heavily rely on temporary processing. The following are the various levels of concurrency used for this evaluation: 4, 8, 12, 16.

Infrastructure

The following table summarizes the infrastructure details of each instance. The “d” in instance class denotes support of local storage, which is an Optimized Reads RDS instance.

We also don’t want to exceed the number of vCPUs for the maximum of number of clients running in parallel, so that maximum concurrency is 16.

Schema

For this experiment, we use sysbench to create a schema with a scale of 1,000. Sysbench deploys three tables with 100 million records each. The build script is as follows:

sysbench --db-driver=pgsql --report-interval=2 --oltp-table-size=100000000 --oltp-tables-count=3 --threads=3 --time=60 --pgsql-host=$PGHOST --pgsql-port=$PGPORT --pgsql-user=$PGUSER --pgsql-db=$BMDB /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua run

Check the sysbench GitHub page for instructions on installation and usage.

To verify the number of records after initializing data using sysbench, use the following code:

 sbtest=> SELECT relname AS table_name,TO_CHAR(reltuples::bigint,'fm999G999G999') AS rows FROM pg_class WHERE relname iLIKE '%sbtest%' AND relkind = 'r' ORDER BY relname;
 table_name |    rows
------------+-------------
 sbtest1    | 100,000,008
 sbtest2    | 100,000,008
 sbtest3    | 100,000,008
(3 rows)

Database parameters

The following table lists the core parameters that influence the performance of temporary processing. We use the default settings, and some of these defaults are based on RDS instance class.

PostgreSQL’s degree of parallelism

Depending on the query’s run plan chosen by the optimizer and the configuration set by max_parallel_workers_per_gather, it’s possible that multiple workers are assigned to processing a particular query. The default number of parallel threads a query can afford (max_parallel_workers_per_gather) is 2. Due to this, the number of sessions PostgreSQL creates to process a query is ([max_parallel_workers_per_gather * concurrency] + number of parent processes per each max_parallel_workers_per_gather). Each parallel processing query has one parent process.

For example, for concurrency 16, ([2*16] + 16) = 48 sessions are created in total.

Queries

We used the following queries to evaluate performance testing. Each query is run in parallel with multiple sessions running the same query; the number of sessions defines the level of concurrency.

• Query 1 – This query aims at getting unique values. DISTINCT requires sorting in PostgreSQL, so it creates disk spills when work_mem is not sufficient.

  EXPLAIN(analyze, buffers)
  SELECT DISTINCT padFROM sbtest1 limit 65000000;

• Query 2 – This query combines GROUP BY and ORDER BY operations. Both result in temporary disk usage typically when work_mem is not sufficient.

  EXPLAIN (analyze, buffers )
   SELECT c1.pad
   ,sum(c1.c)
   ,avg(c1.k)
   FROM (
   SELECT pad
   ,length(c) AS c
   , k
   FROM sbtest2 limit 100000000
   ) AS c1 GROUP BY c1.pad ORDER BY 3;

• Query 3 – This query solely focuses on ORDER BY on a non-indexed column:

  EXPLAIN(ANALYZE, BUFFERS)
   SELECT k
   , length(pad)
   FROM sbtest3 ORDER BY pad LIMIT 50000000;

Results

The following table summarizes the average time per each level of concurrency, amount of disk space used, and percentage of improvement by local storage in comparison to Amazon EBS for each concurrency level.

The following figure is a graphical view of the summary table.

From the results, we see how a high concurrency workload that runs queries requiring temporary storage shows improvement in runtimes using Optimized Reads. We see it took an Amazon EBS-only RDS instance around 66 minutes to process a concurrency test of 16, whereas the Optimized Reads instance took only 32 minutes to process the same workload with the same concurrency. It also shows that with increased concurrency, the amount of disk space usage and the time to run increase. This is natural, but its impact on the performance of Amazon EBS-only backed RDS instances is significantly higher in comparison to Optimized Reads instances, which is a 106.9% improvement. This is due to the fact that Amazon EBS is a storage over network and is bound by the network bandwidth capacity, Amazon EBS bandwidth capacity, and storage configuration, such as the amount of storage for a general purpose Amazon EBS storage type, and number of IOPS for a provisioned IOPS storage type.

With storage directly attached locally on an Optimized Reads instance, these constraints don’t matter to local storage, with the exception of the instance’s limitations that effect both Amazon EBS and local disks.

Conclusion

In this post, we introduced Optimized Reads, discussed the importance of a temporary work area, and showed how to monitor temporary usage in a local disk using CloudWatch and at the database level using PostgreSQL’s system tables. We also compared query performance between an Optimized Reads instance and an Amazon EBS-only backed RDS instance, and discussed how consider migrating to an Optimized Reads instance.

The performance of queries that rely on a temporary work area is critical for highly concurrent workloads. With Optimized Reads, you can improve the performance of your SQL operations that require intermediate processing by simply choosing a similar instance class that supports Optimized Reads. The benefit of the Optimized Reads instance doesn’t just stop there: by offloading I/O usage to local disk, you can plan to optimally balance spared Amazon EBS resources for other persistent operations, improve disk throughput, and improve the overall performance situation of the database and applications.

The Optimized Reads feature is now available for RDS Single-AZ and Multi-AZ instances, including RDS read replicas, along with Multi-AZ DB clusters on Amazon RDS for PostgreSQL versions 15.2 and higher, 14.7 and higher, and 13.10 and higher. You can start benefiting from it today!

Stay tuned for the second post in this series for more deeper insights into Optimized Reads.

If you have any questions, let us know in the comments.

About the authors

Naga is a Database Engineer at Amazon Web Services and Subject Matter Expert for RDS PostgreSQL, he has over a decade of experience in working with relational databases. At AWS, He is mainly focused on PostgreSQL deployments, he works with developers in bringing new features and assist customers in resolving critical issues with Aurora and RDS PostgreSQL Database systems.