Relational Database Design

Enterprise relational database systems on AWS demand architectural decisions that balance transactional consistency, fault tolerance, read scalability, and cost efficiency. The ability to match workload characteristics to the correct database operating model separates competent architects from those who default to a single pattern. This lesson builds a layered understanding of Amazon RDS high availability, Aurora's distributed storage, serverless compute scaling, and connection optimization, culminating in a decision framework for enterprise-grade relational design.

Designing highly available RDS architectures

Building highly available relational databases on AWS begins with understanding the failure domains that threaten transactional systems. Amazon RDS provides managed database engines spanning MySQL, PostgreSQL, MariaDB, Oracle, and SQL Server, abstracting patching, backups, and infrastructure management. High availability in this context means surviving compute failures, storage corruption, or entire Availability Zone disruptions without data loss or extended downtime.

Multi-AZ deployments and failover semantics

RDS Multi-AZ deployments serve as the primary mechanism for achieving high availability within a single AWS Region. When Multi-AZ is enabled, RDS provisions a synchronous standby replicaa secondary database instance in a different Availability Zone that receives every write transaction in lockstep with the primary, ensuring zero data loss during failover. If the primary instance experiences a compute failure, storage failure, or its Availability Zone becomes unreachable, RDS automatically updates the DNS endpoint to point to the standby, typically completing failover within 60 to 120 seconds.

A critical distinction applies here. Multi-AZ improves durability and failover capability, but it does not improve read throughput. The standby replica does not serve any read traffic; it exists solely as a failover target.

Read Replicas for read scaling

Read Replicas address the read-scaling gap through asynchronous replication. Unlike Multi-AZ standby instances, Read Replicas actively serve read queries from applications. Key architectural characteristics include the following:

• Asynchronous replication means Read Replicas may exhibit a slight replication lag, making them unsuitable for reads that require strict transactional consistency.

• Cross-Region deployment enables Read Replicas to reduce read latency for geographically distributed users and serve as warm disaster recovery targets.

• Manual promotion ...