2.4.1.1. Designing for Relational Database Workloads (Scalability, HA, DR)

2.4.1.1. Designing for Relational Database Workloads (Scalability, HA, DR)

💡 First Principle: Relational databases must be architected to maintain transactional integrity ("ACID") while providing high availability, effective read scaling, and robust disaster recovery to support critical "OLTP" workloads.

Scenario: A critical online banking application uses "Amazon RDS for PostgreSQL". The application experiences frequent read spikes during business hours, and there's a strict requirement for high availability with minimal downtime in case of database instance failure. Additionally, a disaster recovery plan is needed in a separate "AWS Region" with minimal data loss.

Relational databases remain central for many applications, especially those requiring strong transactional consistency ("ACID" properties).

• Scalability:
  • Vertical Scaling: Increasing compute/memory (e.g., moving to a larger "RDS instance type"). Simple but limited.
  • Read Replicas ("RDS"/"Aurora"): Asynchronous replication of data to separate instances for read-heavy workloads, offloading the primary. Improves read performance and can be cross-"AZ" or cross-"Region" for "DR".
  • Aurora Scaling: "Aurora" automatically scales its storage and leverages up to 15 read replicas, including "Aurora Serverless" for on-demand capacity.
  • Sharding (Application-level): Partitioning data across multiple database instances, managed by the application. Complex to implement but offers extreme horizontal scaling.
• High Availability ("HA"):
  • "Multi-AZ Deployment (RDS/Aurora)": Synchronously replicates data to a standby instance in a different "AZ". Provides automatic failover (minutes) in case of primary instance failure or "AZ" outage. No data loss ("RPO=0").
• Disaster Recovery ("DR"):
  • Cross-Region Read Replicas ("RDS"/"Aurora"): Asynchronously replicate data to a read replica in a different "AWS Region". In a regional disaster, this replica can be promoted to a standalone primary, providing a robust "DR" strategy. "RPO" > 0, "RTO" in minutes.
  • Snapshots: Automated and manual snapshots of "RDS"/"Aurora" instances stored in "S3", can be copied cross-"Region". Higher "RTO"/"RPO" than active replication.

Visual: RDS Scalability, HA, DR Design

⚠️ Common Pitfall: Using a "Read Replica" for high availability. A "Read Replica" is for read scaling. If the primary database fails, you must manually promote the replica, which causes downtime and potential data loss due to asynchronous replication lag. A "Multi-AZ" deployment is the correct solution for automatic, synchronous failover.

Aurora and RDS Specifics the Exam Tests:

• Making an existing Single-AZ RDS instance highly available: Modify the instance and enable Multi-AZ. That is the simplest fix; read replicas, frequent snapshots, or DMS copies do not give automatic failover.
• Aurora storage and in-Region HA: The cluster volume is replicated six ways across three AZs. Adding Aurora Replicas in other AZs (up to 15) provides both read scale-out (via the reader endpoint) and automatic failover to a replica (service is typically restored in under 60 seconds, often under 30). Aurora does not need RDS Multi-AZ standbys layered on top.
• Aurora Global Database (cross-Region DR): Replicates at the storage layer to secondary Regions with typically under one second of lag (RPO of about a second), supports low-latency local reads in each Region, and lets you promote a secondary Region in about a minute. It is the answer for "sub-second RPO and fast cross-Region recovery"; a cross-Region read replica uses slower, logical (binlog) replication, and snapshots or DMS give a much larger RPO.
• Aurora cloning: A clone is created with a copy-on-write protocol, so it is available in minutes regardless of size, shares storage with the source until data diverges, and does not affect production performance. Use it for heavy ad-hoc analysis or test copies; restoring a snapshot or running DMS is much slower, and a replica shares load with production.
• Aurora Serverless v2: Scales capacity (ACUs) up and down automatically in fine increments and can scale to 0 ACUs (automatic pause) when the minimum capacity is set to 0, so an idle, spiky workload stops paying for compute. Provisioned Aurora and Multi-AZ RDS keep paying for idle capacity.
• Relational requirements checklist: ACID transactions, multi-table joins, Multi-AZ failover and automatic minor version upgrades (a maintenance-window setting) describe RDS and Aurora. DynamoDB (even with transactions) has no joins, Redshift is analytical, and ElastiCache is a cache.
• Credential-free database access: IAM database authentication (RDS and Aurora MySQL/PostgreSQL) lets a caller with rds-db:connect permission obtain a short-lived (15-minute) token mapped to a database user instead of storing a password; per-user database permissions then enforce schema-level isolation. Secrets Manager still hands the application a credential; RDS Proxy pools connections and does not provide per-account isolation by itself.

Key Trade-Offs:

• High Availability ("Multi-AZ") vs. Read Scaling ("Read Replicas"): "Multi-AZ" is for failover and doesn't improve read performance (the standby is not readable). "Read Replicas" are for offloading read traffic and are not a primary "HA" solution.

Reflection Question: How would you combine "Amazon RDS Read Replicas" and "Multi-AZ" deployment (including cross-region) to meet the scalability, high availability, and disaster recovery requirements for a critical online banking application that experiences frequent read spikes and demands minimal downtime?