Is Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL a better choice for me?

If you’re planning to move your self-managed PostgreSQL database or refactoring your commercial databases to PostgreSQL on AWS, you have to decide which database service best suits your needs. Amazon Relational Database Service (Amazon RDS) supports two types of Postgres databases: Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition.

In this post, we discuss how to determine the best option between Amazon RDS for PostgreSQL and Aurora PostgreSQL for your workloads and business requirements. We analyze the differences in performance, scalability, failover, storage, high availability, backup, and database versions.

Overview

Amazon RDS for PostgreSQL and Aurora PostgreSQL are fully managed open-source PostgreSQL database services. They both offer provisioning various types of database instances, multiple PostgreSQL versions, managing backups, point-in-time recovery (PITR), replication, monitoring, Multi-AZ support, and Storage Auto Scaling.

Amazon RDS for PostgreSQL supports up to 64 TiB of storage and recent PostgreSQL versions.

DB instances for Amazon RDS for PostgreSQL use Amazon Elastic Block Store (Amazon EBS) volumes for database and log storage.

Aurora PostgreSQL uses a high-performance storage subsystem customized to take advantage of fast distributed storage. The underlying storage grows automatically in chunks of 10 GiB, up to 128 TiB. Aurora improves upon PostgreSQL for massive throughput and highly concurrent workloads. The combination of PostgreSQL compatibility with Aurora enterprise database capabilities provides an ideal target for commercial database migrations.

The following diagram illustrates the differences between the Multi-AZ solutions for Amazon RDS for PostgreSQL and Aurora PostgreSQL.

The following sections discuss some of the key components to compare Amazon RDS for PostgreSQL and Aurora PostgreSQL.

Scalability

Amazon RDS read replicas help reduce the load on the primary DB instance by offloading your read workloads to the read replica DB instances. It makes it easy to scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads. With Amazon RDS for PostgreSQL, you can create up to five replicas of a given source DB instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput. You can also promote read replicas when needed to become standalone DB instances. It also supports five cross-Region read replicas. Replicas are synced with the source instance using PostgreSQL streaming replication. PostgreSQL records any modifications in data in write-ahead log (WAL) files. Streaming replication continuously ships and applies the WAL records to replicas in order to keep them current. This asynchronous replication is based on transactional logs, so high write activity at source instance, storage type mismatch, and instance class mismatch can cause high replication lag. This lag can be up to several minutes. With optimal configurations and workload, in Amazon RDS for PostgreSQL the replica lag is typically a few seconds.

Aurora PostgreSQL supports up to 15 readers for scaling out read workloads and high availability within a single Region. Aurora provides this by scaling storage across three Availability Zones in the Region. It writes the log records to six copies in three Availability Zones. Unlike Amazon RDS for PostgreSQL, where high write workloads can severely impact replication, Aurora uses shared storage for writer and readers. As a result, all Aurora replicas are synced with the writer instance with minimal replica lag. The lag can be different for different replicas. Usually, this replica lag is a few hundred milliseconds. In some cases, this lag could go up to 30 seconds, and automatically restarts the reader so the lag doesn’t get higher.

If the read workload can’t afford high replication lag and requires more than five read replicas, Aurora PostgreSQL is a better choice. If replication lag is acceptable from seconds to minutes and up to five replicas are enough for your read workload, Amazon RDS for PostgreSQL is a better choice.

Crash recovery

In Amazon RDS for PostgreSQL, during crash recovery, it replays the transaction logs since the last checkpoint. A checkpoint flushes the current in-memory modified database blocks and WAL information from memory to disk, which provides durability in case the database crashes for obvious reasons. When the database crashes and needs to perform crash recovery, the transaction logs are replayed so that the database is brought up to date. By default, checkpoints are 5 minutes apart. During checkpoint, it writes all the dirty pages from the memory to storage. If checkpoints occur frequently, crash recovery time is reduced. The downside is checkpoints can slow down the database performance because it’s an I/O intensive operation.

Aurora PostgreSQL doesn’t perform checkpoints because the storage system takes the log records it receives from the database node and applies them to the database pages in the storage nodes. This means performing checkpoints isn’t done in the database node—it’s done in the storage nodes. Aurora performs crash recovery asynchronously using parallel threads to quickly make the database available after a crash.

If your business requirements need quick database crash recovery, Aurora PostgreSQL is a better choice. If crash recovery time isn’t a major concern, Amazon RDS for PostgreSQL is a viable choice.

Failover

Amazon RDS for PostgreSQL handles failover automatically so that applications can resume database operations as quickly as possible. The failover time is typically around 60–120 seconds. This consists of failure detection, DNS propagation, and crash recovery. The failover time mainly depends on how long it takes to perform crash recovery.

In Aurora PostgreSQL, the failover time is typically within 30 seconds, which consists of failure detection, DNS propagation, and recovery. Failure detection takes nearly 15–20 seconds to confirm the node failure. DNS propagation and recovery happens in parallel. DNS propagation takes around 10–15 seconds, whereas recovery time is fast, typically 3–10 seconds.

If applications can’t tolerate longer failover times, Aurora is a better choice. If applications can accept more than 60 seconds of downtime during failover, Amazon RDS for PostgreSQL is a viable solution.

Storage

Amazon RDS for PostgreSQL supports Amazon EBS solid state drive (SSD)-based storage types: General Purpose SSD and Provisioned IOPS. General Purpose SSD storage delivers a consistent baseline of 3 IOPS per provisioned GB and can burst up to 3,000 IOPS. The baseline IOPS limit for General Purpose SSD is 16,000 IOPS. Provisioned IOPS SSD delivers IOPS in the 1,000–80,000 range. Amazon RDS for PostgreSQL supports Storage Auto Scaling. This feature automatically increases instance storage size in chunks of 5 GiB, or 10% of the currently allocated storage.

Aurora PostgreSQL uses a single, virtual cluster volume that uses SSDs. A cluster volume consists of copies of the data across multiple Availability Zones in a single Region. Aurora storage automatically increases the size of the database volume as the database storage grows. The storage volume grows in increments of 10 GiB up to a maximum of 128 TiB. Because the data is automatically replicated across three Availability Zones, data is highly available and durable. Although there is no IOPS limitation based on the storage size, you may need to scale up your instance to support workloads requiring higher IOPS. The following image shows the Aurora PostgreSQL storage architecture with shared storage volume and nodes.

If your database workload reaches the Amazon RDS max limit of 80,000 IOPS, and it requires additional IOPS, Aurora PostgreSQL is the preferred database solution. If database workload requires less than 80,000 IOPS, you can choose either Amazon RDS for PostgreSQL or Aurora PostgreSQL based on supported features.

High availability and disaster recovery

Amazon RDS for PostgreSQL provides high availability and disaster recovery (DR) features by using Multi-AZ deployments, read replicas, and sharing snapshots. Multi-AZ configuration synchronously replicates data to a standby instance in a different Availability Zone. Automated backups are taken from the standby instance. Incidents such as primary instance failure, storage failure, instance scale-up, and network failure trigger failover to make the secondary instance a new primary instance. Amazon RDS for PostgreSQL also supports replicas in the same Region as well as cross-Region. This replication is based on database transaction logs, and replication lag can increase depending on the workload at the source instance. You can copy and share Amazon RDS snapshots across AWS accounts and Regions for DR purposes.

Aurora PostgreSQL also supports Multi-AZ deployment. It synchronously sends the data across Availability Zones to six storage nodes associated with the cluster volume. This synchronization occurs at the storage level, resulting in a lag typically in milliseconds. All instances are active and automated backups are taken from the shared storage layer. Aurora promotes one of the readers when a problem is detected on the primary instance or storage layer. If a failure occurs and no Aurora replica has been provisioned, it attempts to create a new database instance automatically. Amazon Aurora Global Database offers cross-Region replication. The typical cross-Region replication latency is below 1 second. You can also copy and share Aurora cluster snapshots across AWS accounts and Regions for DR purposes.

If you fulfil the high availability requirement of your data by replicating in two Availability Zones, and cross-Region replication lag is acceptable, Amazon RDS for PostgreSQL should meet your requirements. For higher data availability and to design a DR solution with minimal Recovery Point Objective (RPO) and Recovery Time Objective (RTO), Aurora PostgreSQL is the preferred choice.

Backup

Amazon RDS automatically takes daily backups of PostgreSQL instances during a backup window. There is a slight performance impact when the backup initiates for single Availability Zone deployments. In addition, it also backs up transaction logs (WALs). For PITR, the full backup is restored first, followed up by replaying WALs until the desired time. In write-intensive RDS for PostgreSQL instances, replaying transaction logs may take a long time. Frequently taking manual backups can reduce PITR duration.

Aurora PostgreSQL backs up cluster volume automatically and retains backups for the length of the defined retention period. Aurora backups are incremental, so it can be quickly restored to a point within the backup retention period. There is no performance impact or interruption of database service during backups. For PITR, a new copy of the database cluster is created from the backup of the database at any point in time within the backup retention period. The continuous and incremental nature of backup improves the PITR restore time.

If you’re looking for a database solution with lower RPO and RTO, and if your workloads are sensitive to performance degradation during backups, Aurora PostgreSQL is a better solution. If momentary performance degradation in a single Availability Zone configuration during backup and higher RPO and RTO aren’t business critical, Amazon RDS for PostgreSQL can meet your requirements. In Amazon RDS for PostgreSQL, you can modify the automated backup window to avoid performance degradation during peak hours.In Multi-AZ configuration, the backups are taken from the standby instance.

Database instance classes

Amazon RDS for PostgreSQL supports several instance classes, including general purpose T2, T3, M3, M4, M5 instances, and memory optimized R3, R4, R5 instances. Aurora PostgreSQL supports limited instance classes, including T3, R4, and R5 instances. In many Regions, Amazon RDS for PostgreSQL and Aurora PostgreSQL support AWS Graviton2 based instances. Graviton2 processors are custom built by AWS using 64-bit Arm Neoverse cores to deliver the best price performance for your workloads.

Amazon RDS for PostgreSQL offers more instance types so you can choose the appropriate size for your workload. If your database workload requires a smaller instance class that isn’t available with Aurora PostgreSQL, Amazon RDS for PostgreSQL is a better choice.

Additional features

Some of the additional features Aurora PostgreSQL supports are fast database cloning, query plan management, cluster cache management, serverless, and a global database.

With database cloning, you can quickly create clones of all databases in the cluster. This is faster than restoring Amazon RDS for PostgreSQL from a snapshot.

With query plan management for Aurora PostgreSQL, you can control how and when query plans change. In Amazon RDS for PostgreSQL, you don’t have similar control.

The cluster cache management feature improves the performance of the new writer instance after failover. You can designate a specific replica as the failover target. With cluster cache management, data in the designated replica cache is synchronized with the cache in the writer instance. In Amazon RDS for PostgreSQL, after failover, the buffer is built from scratch. This degrades the performance because the database instance has to read the data from the disk instead of buffer cache.

Aurora Serverless DB cluster deployment automatically starts, scales, and shuts down an Aurora database based on application needs. It’s a suitable option for infrequent, intermittent, or unpredictable workloads. As of this writing, Amazon RDS for PostgreSQL doesn’t support a serverless feature.

With Aurora Global Database, a single Aurora database can span across multiple Regions to enable fast local reads and quick disaster recovery. Global Database uses storage-based replication to replicate a database across multiple Regions with typical latency of less than 1 second. Amazon RDS for PostgreSQL supports cross-Region replication that can have higher replication lag.

If your requirements include quicker database refresh or modern database innovations such as query plan management, cluster cache management, serverless databases, or least replication lag across Regions, Aurora PostgreSQL is better choice.

Conclusion

Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition both have their own use cases and optimization techniques, and require application optimization accordingly. It should be carefully determined whether Amazon RDS for PostgreSQL or Aurora PostgreSQL is best suited for your business requirements.

Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price.

For more information about migrating to Amazon RDS for PostgreSQL or Aurora PostgreSQL, see the following resources:

If you have any questions or suggestions about this post, leave a comment.

About the authors

Vivek Singh is a Senior Database Specialist with AWS focusing on Amazon RDS/Aurora PostgreSQL engines. He works with enterprise customers providing technical assistance on PostgreSQL operational performance and sharing database best practices.

Sagar Patel is a Senior Database Specialty Architect with the Professional Services team at Amazon Web Services. He works as a database migration specialist to provide technical guidance and help Amazon customers to migrate their on-premises databases to AWS.