Skip to content
FR

Determine High-Performing Database Solutions

Step 15 of 28

AWS Database Selection: Aurora, DynamoDB, Caching, and RDS Proxy

Where this lesson fits

This lesson covers Domain 3: Design High-Performing Architectures, Task 3.3: Determine high-performing database solutions. Domain 3 represents 24% of the scored content of SAA-C03. AWS database selection starts with access patterns and consistency requirements. Choose the engine, replicas, cache, proxy and capacity mode for the measured workload.

Cost-focused database choices (reserved capacity, serverless pricing trade-offs, snapshot retention) are covered in database capacity and cost optimization, and recovery in Multi-AZ failover and cross-Region disaster recovery. Measure query latency and throughput against the required access pattern.

Learning objectives

After this lesson you will be able to:

  • Choose between Amazon RDS, Amazon Aurora, Amazon DynamoDB, and purpose-built databases from a described access pattern.
  • Distinguish Multi-AZ (availability) from read replicas (read scaling), and know which Aurora and RDS features offer each.
  • Design a caching layer with Amazon ElastiCache or DynamoDB Accelerator (DAX) and select a caching strategy.
  • Use RDS Proxy to absorb connection storms from Lambda and other bursty clients.
  • Plan DynamoDB capacity (on-demand versus provisioned with auto scaling), partition keys, and secondary indexes.
  • Size relational databases using storage types, IOPS, and Aurora capacity models.
  • Recognize when a global or multi-Region data design (Aurora Global Database, DynamoDB global tables) is the performance answer.

1. Choosing the database by access pattern

Start from the required queries and consistency, not a preferred product.

Access pattern Typical choice Why
Relational schema, joins, transactions, existing SQL application Amazon RDS (MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, Db2) or Amazon Aurora (MySQL/PostgreSQL compatible) Full SQL and ACID transactions with managed backups and patching
Relational, very high throughput, fast failover, up to 15 read replicas Amazon Aurora Shared distributed storage volume replicated six ways across three AZs; replicas read the same storage
Key-value or document, single-digit millisecond latency with appropriately distributed keys and capacity, known access keys Amazon DynamoDB Horizontally partitioned, fully managed, no servers or relational connection pool; throughput, partition and account quotas still apply
Microsecond reads in front of a database ElastiCache (Valkey, Redis OSS, Memcached) or DAX for DynamoDB In-memory cache
Wide-column, Cassandra-compatible workloads Amazon Keyspaces Managed Cassandra API
Graph traversals (relationships of relationships) Amazon Neptune Graph engine
Time-series (IoT metrics) Timestream for LiveAnalytics (existing customers only; evaluate InfluxDB for new projects) Time-partitioned storage and time functions
MongoDB-compatible documents Amazon DocumentDB Managed document database with MongoDB compatibility
Durable, Redis-compatible primary database Amazon MemoryDB In-memory speed with multi-AZ durability
Warehouse analytics over petabytes Amazon Redshift Columnar MPP; not an OLTP database

Use a relational engine for joins and SQL transactions; consider DynamoDB for known-key lookups with distributed demand. Minimal-change migrations favor a compatible engine: DynamoDB generally requires a new data model and query code.

1.1 Relational engines on AWS: RDS versus Aurora

Amazon RDS runs a traditional engine on an EC2-like instance with EBS storage. Amazon Aurora separates compute from a purpose-built distributed storage layer that auto-grows in 10 GiB increments up to 256 TiB on supported recent engine versions (128 TiB on older versions), keeps six copies of the data across three Availability Zones, and lets up to 15 Aurora Replicas read the same storage with replica lag typically in the low tens of milliseconds.

Feature RDS (single engine, e.g. MySQL) Aurora
Storage EBS-backed capacity and IOPS options vary by engine/deployment; supported storage autoscaling grows within configured limits Shared cluster volume, grows automatically
Read replicas Up to 15 for MySQL/PostgreSQL/MariaDB (asynchronous, own storage copy) Up to 15 Aurora Replicas on shared storage
Failover (Multi-AZ) Standby in another AZ, typically 60 to 120 seconds Promotes a replica, typically under 30 seconds
Writer scaling Larger instance class Larger instance class, or Aurora serverless capacity
Cross-Region Cross-Region read replicas Aurora Global Database, storage-level replication, typically under one second lag

Evaluate Aurora for compatible high-throughput relational workloads and fast failover. RDS remains appropriate for Oracle, SQL Server or Db2 compatibility, or a standard managed MySQL requirement; compare actual cost and performance.

Aurora serverless. Aurora Serverless v2 (renamed simply Aurora serverless in AWS documentation during 2026) adjusts compute in fractions of an Aurora Capacity Unit (ACU) in place. The supported ACU range depends on engine and platform version: current platforms can reach 256 ACUs, and compatible versions allow a minimum of 0 for auto-pause after an idle interval of 5 minutes to 24 hours. Resume is typically around 15 seconds, not a maximum; after more than 24 hours paused it can take 30 seconds or longer. Set connection timeouts and retries accordingly. Open user sessions, RDS Proxy and some replication configurations prevent auto-pause; storage charges continue while compute is paused. Compare serverless with provisioned capacity for steady workloads.

Worked sizing note. In the conventional single-writer RDS/Aurora models discussed here, replicas scale reads while the writer remains the write bottleneck. If a stem says writes, not reads, are the bottleneck, replicas do not help; the answer is a larger writer, reducing write amplification, sharding at the application layer, or moving a key-value part of the workload to DynamoDB.

2. Read replicas, Multi-AZ, and what each one solves

Mechanism Purpose Replication Can serve reads? Failover target?
RDS Multi-AZ instance deployment Availability Synchronous to a standby No (standby is not readable) Yes, automatic
RDS Multi-AZ DB cluster (MySQL/PostgreSQL) Availability plus read scaling Semi-synchronous to two readable standbys Yes, via reader endpoint Yes, typically under 35 seconds
RDS read replica Read scaling, reporting offload, cross-Region reads Asynchronous Yes Manual promotion
Aurora Replica Read scaling and failover Shared storage Yes, via reader endpoint Yes, automatic promotion by priority tier

Practical consequences:

  • To offload a reporting workload that slows the primary, create a read replica (or Aurora Replica) and point the reports at the replica or reader endpoint.
  • Aurora exposes a cluster endpoint (writer), a reader endpoint (balances new connections across replicas, not individual queries), and optional custom endpoints (for example, a group of large replicas dedicated to analytics queries). With no replicas, the built-in reader endpoint connects to the writer; the endpoint name alone does not ensure isolation or read-only access. Connection pooling can also concentrate work on one replica.
  • Replication to a read replica is asynchronous, so reads can be slightly stale. An application that must read its own just-committed write immediately should read from the writer for that request.
  • A cross-Region read replica lowers read latency for distant users, and can be promoted during a Regional disaster. For Aurora with strict recovery objectives, Aurora Global Database replicates at the storage layer and is the better fit.

3. Caching: ElastiCache and DAX

A cache trades memory for lower read latency and database load; define its correctness requirements.

3.1 Choosing a cache

Option Use when
Amazon ElastiCache for Valkey / Redis OSS Rich data structures (sorted sets, hashes, pub/sub), replication, persistence options, session stores, leaderboards
Amazon ElastiCache for Memcached Simple multithreaded key-value API; node-based clusters have no replica groups or persistence. Serverless Memcached provides managed multi-AZ replication, without Redis/Valkey sorted-set features
ElastiCache Serverless Unpredictable cache load; capacity scales automatically without choosing node types
DynamoDB Accelerator (DAX) Read-heavy DynamoDB tables needing microsecond latency with no application redesign: use a DAX client for supported DynamoDB API calls; changing only an ordinary DynamoDB SDK endpoint is insufficient
Amazon MemoryDB A durable primary database with Redis compatibility, not merely a cache

ElastiCache supports the Valkey engine, the open source fork of Redis; recent releases (Valkey 9.x in 2026) continue to add throughput and features, and Valkey 9.0+ serverless caches now offer optional public endpoints using IAM authentication over TLS 1.3; this does not make every ElastiCache engine or existing VPC cache public. For the exam, remember the principle: Valkey/Redis OSS for rich features and replication, Memcached for the simplest multithreaded cache, DAX for DynamoDB.

3.2 Caching strategies

  • Lazy loading (cache-aside). The application checks the cache first; on a miss it reads the database and writes the result to the cache. Only requested data is cached, and cache failure is survivable, but the first read is slow and data can go stale.
  • Write-through. The application coordinates database writes and cache updates. This can reduce stale reads, but failed/concurrent updates and replica lag still need handling; it adds write latency and can cache unused data.
  • Time to live (TTL). An expiry limits how long an entry remains cached after insertion, not an end-to-end guarantee of data freshness. A lagging database replica can repopulate a stale value. Shorter TTL often trades hit rate for freshness.

Combine lazy loading with TTL for most read-heavy catalogs; add coordinated write-through or invalidation to reduce staleness. For prices requiring authoritative consistency, validate against the database and handle failed/concurrent writes; a cache strategy alone is not an unconditional guarantee.

3.3 Where caching helps (and where it does not)

Cache repeated, expensive reads when stale results are acceptable, such as a catalog page or computed leaderboard. Unique queries and write-heavy access benefit less. For DynamoDB, DAX offers compatible calls through its own client; ElastiCache requires application cache logic. DAX query results expire by TTL rather than write invalidation, and strongly consistent reads bypass the cache. Check supported calls and freshness requirements.

4. RDS Proxy: connection pooling

Database connections consume memory. Thousands of short-lived Lambda invocations, or a fleet that opens a connection per request, can exhaust max_connections or overload CPU with connection setup, even when the query load is small.

Amazon RDS Proxy is a managed, highly available proxy for RDS and Aurora (MySQL, PostgreSQL, MariaDB, and SQL Server) that:

  • Pools and multiplexes connections so many client connections share a smaller set of database connections.
  • Can preserve eligible client connections and reconnect to a new writer during failover. In-flight transactions and pinned sessions can still fail; applications need reconnect/retry logic. In 2026 AWS also added support for RDS Blue/Green Deployments so switchovers do not wait for DNS propagation.
  • Can require client IAM authentication and use Secrets Manager credentials or supported end-to-end IAM database authentication. Availability depends on engine/version; SQL Server does not support end-to-end IAM through the proxy.
  • Runs inside your VPC; Lambda functions that use it must be VPC-attached.

Monitor connection borrow timeouts and DatabaseConnectionsCurrentlySessionPinned: session state can pin a client to one backend connection, reducing multiplexing. A proxy does not provide unlimited database capacity.

Choose RDS Proxy for: Lambda with a relational database, unpredictable connection counts, applications that cannot implement connection pooling, and workloads that must ride through failover smoothly. Do not choose it for a database that is limited by CPU on heavy queries (it pools connections, it does not speed queries) or for DynamoDB (which has no connections to pool).

5. DynamoDB performance design

5.1 Capacity modes

Mode How it works Use when
On-demand Pay per request; new tables support an initial 4,000 writes/s and 12,000 reads/s. Instant scaling covers up to twice the previous peak; faster growth can throttle New or unpredictable traffic, spiky workloads
Provisioned with auto scaling You set read capacity units (RCU) and write capacity units (WCU); auto scaling adjusts targets Steady, predictable traffic; lowest cost at high utilization

Capacity arithmetic. One RCU supports one strongly consistent read per second of an item up to 4 KB, or two eventually consistent reads. One WCU supports one write per second of an item up to 1 KB. Transactional operations cost twice as much.

Before a major launch, check warm throughput, table/GSI maximum settings and account quotas. If demand exceeds twice the previous peak within 30 minutes, pre-warm capacity or ramp traffic over time. On-demand removes provisioning of RCUs/WCUs, not every scaling constraint.

5.2 Partition keys and hot partitions

DynamoDB distributes data by hashing the partition key. Each partition can serve roughly 3,000 read units and 1,000 write units per second. A low-cardinality key (such as status or a date) concentrates traffic in a small key range and can cause hot-partition throttling despite spare table capacity. Adaptive capacity and splitting can help some patterns, but are not substitutes for a suitable key design.

Good partition keys have high cardinality and even access: customerId, deviceId, orderId. For one extremely hot key, write sharding appends a random or calculated suffix (device-42#07) and fans reads out across suffixes.

5.3 Indexes

Index Key schema Consistency Notes
Local secondary index (LSI) Same partition key, different sort key Supports strong consistency Must be created with the table; 10 GB limit per item collection
Global secondary index (GSI) Partition key and optional sort key can differ from the base table Eventually consistent only Can be added anytime; has its own capacity; use for alternative access patterns

A GSI is the answer to "we must query orders by customerEmail instead of orderId". A table scan is almost always the wrong answer for a latency-sensitive query.

5.4 Global tables and DAX

Global tables support MREC (multi-Region eventual consistency, asynchronous replication and last-writer-wins conflict resolution) and MRSC (multi-Region strong consistency with synchronous coordination and zero RPO for acknowledged writes). Check supported Region groups and feature constraints before choosing MRSC. DAX caches repeated item/query reads with eventual freshness; it cannot turn a strongly consistent read into a cache hit. Choose consistency first, then a compatible cache strategy.

6. Worked example: slow storefront during a flash sale

Scenario. ShopRiver’s Aurora MySQL catalog is 95% reads. During sales, writer CPU reaches 95% and reads exceed two seconds. Lambda checkout reports Too many connections. Keep the database and minimize application changes.

Step 1: classify. Repeated SELECT traffic and connection exhaustion are separate bottlenecks.

Step 2: offload reads. Add two Aurora Replicas and point the catalog service at the reader endpoint. Reads now spread across replicas that share the same storage volume, and the writer keeps its CPU for checkout writes.

Step 3: cache the hot data. The catalog changes a few times an hour but is read by thousands per minute. Add ElastiCache for Valkey with lazy loading and a 5-minute TTL for product pages. A measured 90% hit rate would remove most repeated reads; it is a target to validate, not an assumed result. Invalidate price data on update and read the authoritative writer during checkout when the current price is required.

Step 4: fix connections. Place RDS Proxy between the Lambda checkout functions and the Aurora cluster, with credentials in Secrets Manager. Lambda connects to the proxy endpoint, which reuses backend connections when session state permits. Tune pool limits, monitor pinning and load-test the connection/authentication change.

Step 5: verify. This read-heavy design does not scale writer throughput. A write bottleneck requires writer tuning/sizing or redesigning suitable key-based state.

Why other answers are worse. DynamoDB requires migration and query changes. A larger writer retains repeated read contention; higher max_connections consumes memory without pooling. A classic RDS Multi-AZ instance standby is not readable; Aurora uses a different replica model.

7. Comparison: scaling tools at a glance

Symptom Best tool Not this
Heavy repeated reads on SQL database Read replicas + ElastiCache Multi-AZ standby
Reporting queries slow transactional workload Read replica or Aurora custom endpoint Larger writer only
DynamoDB reads need microseconds DAX ElastiCache (needs app logic)
Lambda exhausts DB connections RDS Proxy Higher max_connections
Global users need local reads, relational Aurora Global Database / cross-Region replica Single-Region replicas
Global users need local reads and writes, key-value DynamoDB global tables Cross-Region RDS replica (read-only)
Spiky unpredictable traffic on DynamoDB On-demand capacity Fixed provisioned capacity
Hot partition throttling Higher-cardinality key or write sharding Increase table-level WCU only
Unpredictable relational load Aurora serverless Fixed oversized instance

8. Common pitfalls

  • Treating the Multi-AZ standby of an RDS instance deployment as a read endpoint; it is not readable.
  • Expecting read replicas to scale writes.
  • Forgetting replica lag: asynchronous replicas can return stale data.
  • Putting a cache in front of write-heavy or unique-query workloads, where hit rates are low.
  • Choosing ElastiCache for a DynamoDB table when DAX meets the requirement with no code redesign.
  • Choosing a low-cardinality partition key such as a status or date.
  • Using scans instead of a GSI for a new access pattern.
  • Using DAX for strongly consistent reads; they pass through to DynamoDB.
  • Using RDS Proxy to fix slow queries; it addresses connection management, not execution cost.
  • Selecting DynamoDB for ad hoc relational joins and reporting.
  • Using Redshift for OLTP.

9. Exam tips

  • "Relational, MySQL or PostgreSQL compatible, high performance, fast failover, fewest operations" → Aurora.
  • "Offload reporting from the primary" → read replica (or Aurora reader or custom endpoint).
  • "High availability only" → Multi-AZ; "read scaling" → replicas. The two are separate goals.
  • "Lambda → relational database, too many connections" → RDS Proxy.
  • "Repeated identical reads, database CPU high" → ElastiCache (lazy loading plus TTL).
  • "DynamoDB, microsecond repeated reads, limited changes using a DAX client" → DAX.
  • "DynamoDB with unpredictable traffic" → on-demand; "predictable, steady" → provisioned with auto scaling.
  • "Query DynamoDB by a different attribute" → GSI.
  • "Global low-latency reads/writes on DynamoDB" → global tables; "global relational reads" → Aurora Global Database.
  • "Graph / time-series / Cassandra / MongoDB" → Neptune / Timestream / Keyspaces / DocumentDB.

10. Mini case study (with solution)

Scenario. FleetPulse tracks 2 million delivery vehicles. Each vehicle sends a location update every 5 seconds, and mobile apps look up the latest position of a vehicle by vehicleId with a target of single-digit milliseconds. A back-office team runs complex SQL reports joining deliveries, drivers, and invoices. The reports run on the same MySQL database that stores the transactional data, and they slow order entry every afternoon. The mobile API runs on Lambda and occasionally fails with connection errors against MySQL. The company serves customers in North America and Europe and wants low read latency in both.

Questions. Propose a database architecture, covering the location data, the reports, the Lambda connections, and the global read latency.

Solution.

  1. Location data: use DynamoDB with vehicleId as a distributed key and one latest-position item per vehicle; use a sequence/timestamp condition to reject stale, out-of-order updates in the writer Region. Assign each vehicle a home writer Region; MREC conditions are Regional, so cross-Region writer failover needs ordering/reconciliation. Store history separately if needed. Two million vehicles / 5 seconds = 400,000 writes/s; at up to 1 KiB per nontransactional item that is about 400,000 write units/s before indexes and replication. Request sufficient quotas and pre-warm each target Region; the default table limit and initial warm capacity are insufficient. Use on-demand capacity if fleet activity is uneven, or provisioned with auto scaling if it is predictable. The known key lookup fits DynamoDB without SQL joins. Add DAX only if the extra stale-read window is acceptable; a latest-position requirement does not automatically justify caching. For North America and Europe, use MREC global tables with local Regional endpoints and tolerate/monitor replication lag. MRSC cannot span the documented US and EU Region sets.
  2. Reports and transactions: use Aurora MySQL with an Aurora Replica and a custom endpoint restricted to reporting readers for the back-office reporting tool to separate report compute from the writer, while monitoring shared-storage contention and lag. If retaining RDS MySQL instead, use an RDS read replica and its endpoint; Aurora custom endpoints do not apply to RDS instances.
  3. Lambda connection errors: add RDS Proxy in front of the relational database for the Lambda functions.
  4. European read latency for relational data: use an Aurora Global Database secondary Region or cross-Region read replica for reports and reads in Europe, writing in the primary Region.

Why not alternatives. Location lookups gain little from relational joins. A larger writer retains report contention; a classic RDS standby cannot offload it. A separate cache adds consistency work when direct DynamoDB reads already meet latency needs.

11. Glossary

  • Aurora Replica: read-only instance sharing the Aurora cluster volume; also a failover target.
  • Reader endpoint: Aurora DNS name that load balances connections across replicas.
  • Read replica (RDS): asynchronously replicated, readable copy of an RDS database.
  • Multi-AZ: resources distributed across Availability Zones; classic RDS instances use a synchronous standby, while cluster deployments differ.
  • ElastiCache: managed in-memory cache for Valkey, Redis OSS, and Memcached.
  • DAX: DynamoDB Accelerator, an in-memory, API-compatible cache for DynamoDB.
  • Lazy loading: cache-aside; load data into the cache on a miss.
  • Write-through: write to the cache whenever the database is written.
  • RDS Proxy: managed connection pool and proxy for RDS and Aurora.
  • RCU / WCU: read/write capacity units in DynamoDB provisioned mode.
  • GSI / LSI: global/local secondary index in DynamoDB.
  • Hot partition: a partition receiving disproportionate traffic and throttling.
  • Global table: multi-Region, multi-active DynamoDB replication.
  • ACU: Aurora Capacity Unit, the unit of Aurora serverless compute.

12. Summary checklist

  • I can choose between RDS, Aurora, DynamoDB, and purpose-built databases from an access pattern.
  • I can state the difference between Multi-AZ (availability) and read replicas (read scaling).
  • I know Aurora features: shared storage, up to 15 replicas, endpoints, Global Database, serverless capacity.
  • I can choose ElastiCache engines, DAX, and a caching strategy (lazy loading, write-through, TTL).
  • I can explain what RDS Proxy fixes and what it does not.
  • I can compute simple DynamoDB RCU/WCU needs and pick on-demand or provisioned mode.
  • I can design partition keys and GSIs to avoid hot partitions and scans.
  • I know when global tables or Aurora Global Database are the right multi-Region answer.

For the encryption and retention controls around this data tier, review AWS database encryption and data protection.

Sources