Deep Dive on Amazon Aurora and Amazon RDS for PostgreSQL Architecture and Features

Deep Dive on Amazon Aurora and Amazon RDS for PostgreSQL Architecture and Features ## Introduction ## Overview ## Key Differences ## Architecture Comparison ## Aurora PostgreSQL Architecture ## RDS PostgreSQL Architecture ## Feature Comparison Table ## Detailed Feature Analysis ## Storage ## Aurora PostgreSQL Storage ## RDS for PostgreSQL Storage ## Backup ## Aurora PostgreSQL Backup ## RDS for PostgreSQL Backup ## Scalability ## Aurora PostgreSQL Scalability ## RDS for PostgreSQL Scalability ## Crash Recovery ## Aurora PostgreSQL ## RDS for PostgreSQL ## Failover ## Aurora PostgreSQL Failover ## RDS for PostgreSQL Failover ## RDS Proxy Benefits ## High Availability and Disaster Recovery ## Aurora PostgreSQL HA/DR ## RDS for PostgreSQL HA/DR ## Additional Aurora Features ## Fast Database Cloning ## Query Plan Management (QPM) ## Cluster Cache Management ## Aurora Serverless v2 ## Aurora Machine Learning ## Conclusion ## Additional Resources If you're considering migrating your self-hosted PostgreSQL database or transitioning your commercial databases to PostgreSQL on AWS, you'll need to choose the database service that best aligns with your requirements. AWS offers two managed PostgreSQL database options: This post delves into the architecture and features of Aurora PostgreSQL and RDS PostgreSQL, analyzing their performance, scalability, failover capabilities, storage options, high availability, and disaster recovery mechanisms. Both Aurora PostgreSQL and RDS for PostgreSQL are fully managed PostgreSQL database services offering: Aurora PostgreSQL uses a high-performance storage subsystem customized for fast distributed storage. The underlying storage grows automatically in segments of 10 GiB, up to 128 TiB. Aurora improves upon PostgreSQL for massive throughput and highly concurrent workloads. RDS for PostgreSQL supports up to 64 TiB of storage and uses Amazon Elastic Block Store (Amazon EBS) volumes for database and log storage. RDS manages PostgreSQL installation, upgrades, storage management, replication, and backups. Both services support Amazon RDS Proxy for: Aurora PostgreSQL provides several value-add features: This comprehensive analysis has explored the architectural details and feature sets of Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL. Key takeaways: Both services provide robust solutions for managed PostgreSQL deployments on AWS, each with distinct strengths suited to different use cases. For further guidance on migrating to Aurora PostgreSQL or RDS for PostgreSQL: Have questions or suggestions? Please leave a comment below. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse - Amazon Aurora PostgreSQL-Compatible Edition - Amazon Relational Database Service (Amazon RDS) for PostgreSQL - Provisioning various classes of DB instances - Multiple PostgreSQL-compatible versions - Managing backups and point-in-time recovery (PITR) - Replication and monitoring - Multi-AZ support - Storage auto scaling - Single virtual cluster volume supported by storage nodes using locally attached SSDs - Data automatically replicated across three Availability Zones - Shared storage model for writer and readers - Classic Multi-AZ with single standby instance - Multi-AZ DB cluster with two readable standby DB instances (semi-synchronous) - Three separate Availability Zones for increased read capacity - Single virtual cluster volume supported by storage nodes using locally attached SSDs - Automatic growth in 10 GiB increments up to 128 TiB - Dynamic reduction when data is deleted - Triple replication across three Availability Zones automatically - No IOPS limitation based on storage size (may need to scale DB instance) - Separate I/O charges applied per usage - I/O-Optimized configuration provides up to 40% cost savings when I/O spend exceeds 25% of Aurora database spend - Amazon EBS SSD-based storage types: General Purpose SSD (gp2): 3 IOPS per provisioned GiB, burst up to 3,000 IOPS General Purpose SSD (gp3): Customized performance independent of size Baseline: 3,000 IOPS and 125 MiB/s for <400 GiB storage Provisioned IOPS (io1, io2): 1,000–256,000 IOPS range - General Purpose SSD (gp2): 3 IOPS per provisioned GiB, burst up to 3,000 IOPS - General Purpose SSD (gp3): Customized performance independent of size - Baseline: 3,000 IOPS and 125 MiB/s for <400 GiB storage - Provisioned IOPS (io1, io2): 1,000–256,000 IOPS range - Storage auto scaling in chunks of 10 GiB or 10% of current storage (whichever is greater) - General Purpose SSD (gp2): 3 IOPS per provisioned GiB, burst up to 3,000 IOPS - General Purpose SSD (gp3): Customized performance independent of size - Baseline: 3,000 IOPS and 125 MiB/s for <400 GiB storage - Provisioned IOPS (io1, io2): 1,000–256,000 IOPS range - Continuous and incremental automated backups - No performance impact or interruption during backups - Fast PITR due to incremental nature - Restore time depends on volume size and transaction log count - Daily automated backups during backup window - Slight performance impact on single-AZ deployments when backup initiates - Continuous WAL archiving - PITR process: Restore full backup + replay WALs to desired time - Slower for write-intensive workloads (long WAL replay time) - Tip: Frequent manual snapshots reduce PITR duration - Up to 15 readers for read scaling and high availability - Shared storage model minimizes impact of high write workloads on replication - Minimal replica lag (few hundred milliseconds, occasionally up to 60s) - Auto-restart of readers if lag exceeds threshold - Write capacity limited by single writer instance - Up to 155 read replicas (5 per instance, 3 cascading levels) - Cascading architecture reduces overhead on source instance - Progressive replication lag with more intermediaries in cascade - Read replica promotion to standalone instances - 5 cross-Region read replicas - Streaming replication via PostgreSQL WAL records - Higher replica lag risk with high write activity, storage/instance class mismatch - Two readable standbys in Multi-AZ three-AZ deployment serve both HA and scalability - No traditional checkpoints (storage system applies log records directly) - Parallel and asynchronous redo record replay per storage segment - Immediate availability after crash - Replays transaction logs since last checkpoint (default: 5 minutes apart) - Checkpoint process writes dirty pages from memory to storage - Trade-off: Frequent checkpoints reduce recovery time but may slow performance (I/O intensive) - Typical failover time: 30 seconds DNS propagation: 10-15 seconds Recovery: 3-10 seconds (parallel with DNS) - DNS propagation: 10-15 seconds - Recovery: 3-10 seconds (parallel with DNS) - Automatic promotion of reader to primary - DNS propagation: 10-15 seconds - Recovery: 3-10 seconds (parallel with DNS) - Typical failover time: 1-2 minutes Includes DNS propagation and crash recovery - Includes DNS propagation and crash recovery - Depends on: Crash recovery time, DNS propagation, TTL settings - Multi-AZ with two standbys: Under 35 seconds, 2x faster transaction commit latency - Includes DNS propagation and crash recovery - Connection pooling and sharing - Faster failover recovery - Automatic connection to new primary - Maintained idle connections during failover - Storage-compute separation architecture - Six storage nodes across multiple Availability Zones - All readers accessible via instance or reader endpoints - Minimal replica lag (typically <100ms) - Aurora Global Database for cross-Region replication (<1 second latency) - Automatic DB snapshot sharing across accounts and regions - AWS Backup integration for cross-region backup sharing - Multi-AZ deployment options: One standby (synchronous replication) Two readable standbys (semi-synchronous) - One standby (synchronous replication) - Two readable standbys (semi-synchronous) - Local storage for transaction logs (WAL logs) - Write-then-flush pattern for reduced failover time and faster commits - Automated backups from standby in classic Multi-AZ - Cross-Region and same-Region replicas (transaction log-based) - Snapshot sharing across accounts and regions - One standby (synchronous replication) - Two readable standbys (semi-synchronous) - Quick cloning of all databases in DB cluster - Faster than RDS snapshot restore - Ideal for: Testing schema/parameter changes, analytics on near-production data - Control query plan changes to avoid performance degradation - Maintain optimal plans despite table/index statistics changes - Warm cache synchronization between designated replica and writer - Immediate cache availability after failover - Sustained performance post-failover - On-demand autoscaling configuration - Full Aurora feature set (cloning, global database, Multi-AZ, multiple readers) - Automatic scaling: Starts up, shuts down, scales capacity based on needs - Instant scaling: Hundreds to thousands of transactions in seconds - Fine-grained capacity adjustments - ML-based predictions via SQL - Integrated with: Amazon SageMaker Amazon Bedrock (generative AI) Amazon Comprehend (sentiment analysis) - Amazon SageMaker - Amazon Bedrock (generative AI) - Amazon Comprehend (sentiment analysis) - No custom integrations or data movement required - Amazon SageMaker - Amazon Bedrock (generative AI) - Amazon Comprehend (sentiment analysis) - Aurora PostgreSQL excels in massive throughput, highly concurrent workloads, and provides enterprise database capabilities with minimal operational overhead - RDS for PostgreSQL offers flexibility with storage types, extensive read replica options, and cost-effective solutions for standard workloads - Migrate an on-premises PostgreSQL database to Amazon RDS for PostgreSQL - Migrating data to Amazon Aurora with PostgreSQL compatibility