7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ

7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ

Relational Database Management in AWS (RDS Engine Overview)

Amazon Relational Database Service (RDS) is a fully managed database service that automates administrative tasks such as hardware provisioning, database setup, OS/database patching, automated back-ups, and storage scaling. RDS supports Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, Microsoft SQL Server, and IBM Db2. Feature availability differs by engine, version, and Region.

Storage Architecture & Performance Tiers

Amazon RDS volumes run on AWS Elastic Block Store (EBS). Choosing the appropriate EBS storage tier is essential for data engineering pipelines requiring predictable IOPS:

• General Purpose SSD (gp2 / gp3): Baseline performance of 3,000 IOPS and 125 MB/s throughput for gp3. Volume size scales storage independently of IOPS.
• Provisioned IOPS SSD (io1 / io2 Block Express): Designed for high-throughput, low-latency OLTP workloads. Allows provisioning up to 64,000 IOPS per RDS instance (or 256,000 IOPS on io2 Block Express).

---

High Availability (Multi-AZ) vs. Read Scaling (Read Replicas)

A core topic on the AWS Data Engineer exam is distinguishing between Multi-AZ Deployments (for High Availability and Fault Tolerance) and Read Replicas (for Read Scalability and Analytical Offloading).

Multi-AZ:     App ---> [ Primary DB (AZ-A) ] ===(Synchronous Storage Dup)===> [ Standby DB (AZ-B) ]
Read Replica: App ---> [ Primary DB (AZ-A) ] ---(Asynchronous Log Stream)---> [ Read Replica (AZ-C) ] (Readable)

How RDS Multi-AZ Failover Operates

When a primary database instance fails due to hardware loss, network degradation, or AZ outage:

1. The storage layer detects heartbeat loss.
2. RDS flips the canonical domain name record (CNAME) of the database endpoint to point to the IP address of the Standby instance.
3. Applications automatically reconnect to the new Primary instance using the existing endpoint string without code modifications.

Exam Note: Standard RDS Multi-AZ Standby instances cannot serve read queries. However, RDS Multi-AZ with Two Readable Standbys (available for PostgreSQL and MySQL) introduces readable standby instances backed by local NVMe caching, supporting both HA and read offloading.

---

Amazon Aurora Architecture: Decoupled Distributed Storage

Amazon Aurora is AWS's cloud-native, MySQL- and PostgreSQL-compatible relational database engine. Aurora re-architects relational database storage specifically for the cloud, separating compute processing from persistent storage.

                    [ Aurora Compute Layer (Writer / Reader Instances) ]
                                     | (10 Gbps Net)
-----------------------------------------------------------------------------------------
 [ Storage Node 1 ] [ Storage Node 2 ] [ Storage Node 3 ] [ Storage Node 4 ] [ Storage Node 5 ] [ Storage Node 6 ]
 (AZ-A Copy 1)     (AZ-A Copy 2)     (AZ-B Copy 1)     (AZ-B Copy 2)     (AZ-C Copy 1)     (AZ-C Copy 2)

The 6-Way Storage Replication & Quorum Model

Aurora does not write database pages directly to local disks. Instead, an Aurora Cluster Volume is a single virtual storage volume spanning 3 Availability Zones:

• 6 Copies across 3 AZs: Data is automatically replicated in 10 GB chunks (protection units) six times across 3 AZs (2 copies per AZ).
• Write Quorum (4 of 6): A write operation is acknowledged as durable as soon as 4 out of 6 storage nodes confirm log arrival.
• Read Quorum (3 of 6): Reading data requires confirmation from 3 of 6 nodes, ensuring read consistency even if an entire AZ (2 storage nodes) fails.
• Self-Healing Storage: Peer-to-peer gossip protocols automatically detect missing or corrupt storage blocks and repair them asynchronously in the background without affecting database latency.

Log-Is-The-Network Paradigm

Traditional RDBMS engines write both database pages and WAL/redo logs over the network, causing heavy I/O bottlenecks. Aurora streams only log records (redo log records) directly to storage nodes. Storage nodes apply log records to database pages asynchronously in parallel. This eliminates 84% of network traffic compared to standard PostgreSQL/MySQL engines.

---

Aurora Endpoints & Read Replica Auto-Scaling

An Aurora cluster separates connection management using dedicated endpoints:

1. Cluster Endpoint (Writer Endpoint): Connects to the current primary database instance. Accepts INSERT, UPDATE, DELETE, and SELECT statements.
2. Reader Endpoint: Load-balances incoming read-only traffic (SELECT) across all active Aurora Read Replicas in the cluster.
3. Custom Endpoints: Groups specific subset instances for targeted workloads (e.g., routing heavy batch reporting queries to a high-memory instance while keeping low-latency web app queries on smaller replicas).

Read Replica Scaling

Aurora supports up to 15 Aurora Replicas per cluster with sub-10 millisecond replication lag (since all replicas share the exact same underlying virtual storage volume). Aurora Auto Scaling can dynamically add or remove read replicas based on CPU utilization or average connection metrics.

---

Aurora Global Databases & Aurora Serverless v2

Aurora Global Databases

Designed for globally distributed applications and cross-region disaster recovery:

• Architecture: A primary database cluster in one AWS region and read-only secondary clusters in up to 10 AWS regions.
• Storage-Level Physical Replication: Replication occurs at the storage layer without consuming compute resources on database nodes.
• Performance: Typical cross-region replication latency is under 1 second.
• Disaster Recovery: A secondary Region can be promoted after a regional failure, but recovery time and possible data loss depend on topology and measured replication lag. Planned switchover and unplanned failover have different guarantees.

Aurora Serverless v2

Aurora Serverless v2 scales database compute capacity up and down instantly in response to application traffic:

• Aurora Capacity Units (ACUs): Compute capacity is measured in ACUs. 1 ACU provides approximately 2 GiB of RAM, corresponding CPU, and networking.
• Granular Scaling: Scales in fractional ACUs. Supported ranges vary by engine version; current versions can support scale-to-zero and maxima above older 128-ACU limits.
• Managed scaling: Adjusts capacity without replacing the cluster, while applications should still use resilient connections and test scaling behavior for their engine and workload.

RDS Proxy for Serverless Architectures

When serverless applications (such as AWS Lambda functions) connect directly to relational databases, short-lived function invocations can rapidly exhaust database connection limits. Amazon RDS Proxy is a managed database proxy that maintains a pool of persistent database connections, sharing connections across thousands of Lambda invocations and helping applications reuse connections and recover them more predictably during database failover.

---

Transactions, MVCC, and Lock Management

Relational loads should keep transactions short and deliberate. In RDS engines that use multi-version concurrency control (MVCC), readers can often see a consistent snapshot while writers create new row versions, but MVCC does not eliminate locks. Updates can take row or table locks; DDL commonly takes stronger locks; two transactions that acquire resources in different orders can deadlock. Commit or roll back promptly, update keys in a consistent order, index predicates so updates touch fewer rows, monitor engine lock/wait views or Performance Insights, and retry only the transaction the engine aborts. Exact isolation and lock behavior are engine-specific.

Amazon Redshift also manages transaction isolation and table locks for warehouse operations. Long DDL/DML transactions, overlapping loads, or sessions left open can block later work. Use system views such as SVV_TRANSACTIONS and STV_LOCKS to identify the waiting and holding sessions, then resolve the application transaction rather than indiscriminately terminating queries. Schedule conflicting maintenance and loads, stage data before a short merge, and make retries idempotent.

Code Example: Managing RDS Multi-AZ Failover & Aurora Serverless Endpoint via AWS CLI & Boto3

import boto3

# Initialize RDS client
rds_client = boto3.client('rds', region_name='us-east-1')

def trigger_multi_az_reboot_failover(db_instance_identifier: str):
    """
    Forces a Multi-AZ failover reboot on an RDS primary instance for testing DR procedures.
    """
    try:
        response = rds_client.reboot_db_instance(
            DBInstanceIdentifier=db_instance_identifier,
            ForceFailover=True # Forces RDS to swap CNAME to Standby instance
        )
        status = response['DBInstance']['DBInstanceStatus']
        print(f"Forced failover initiated for '{db_instance_identifier}'. Status: {status}")
        return status
    except Exception as e:
        print(f"Error initiating failover reboot: {str(e)}")
        raise e

def create_aurora_serverless_v2_cluster(cluster_id: str, db_name: str, admin_user: str):
    """Create the cluster and the required db.serverless writer instance."""
    try:
        response = rds_client.create_db_cluster(
            DBClusterIdentifier=cluster_id,
            Engine='aurora-postgresql',
            DatabaseName=db_name,
            MasterUsername=admin_user,
            ManageMasterUserPassword=True,
            ServerlessV2ScalingConfiguration={
                'MinCapacity': 0.5,
                'MaxCapacity': 16.0
            },
            EnableHttpEndpoint=True
        )
        rds_client.create_db_instance(
            DBInstanceIdentifier=f"{cluster_id}-writer",
            DBClusterIdentifier=cluster_id,
            Engine='aurora-postgresql',
            DBInstanceClass='db.serverless'
        )
        print(f"Aurora Serverless v2 cluster '{cluster_id}' and writer creation started.")
        return response['DBCluster']['DBClusterArn']
    except Exception as e:
        print(f"Error creating Aurora cluster: {str(e)}")
        raise e