High-Performing Database Solutions (Task 3.3)

High-Performing Database Solutions

Source: https://docs.aws.amazon.com/wellarchitected/latest/performance-efficiency-pillar/

Database performance is decided at design time by the ACCESS PATTERN — the shape of queries, their ratio, and their latency requirements. Engines are chosen by access pattern, then tuned by three levers: read scaling, caching, and connection management.

Start from the Data Access Pattern

How to reason it: count your query SHAPES. Joins, ACID transactions, ad-hoc filtering → relational, full stop — no NoSQL engine does arbitrary joins cheaply. Known-key lookups ("get this user's cart") at unpredictable scale → DynamoDB, where scale is a capacity number, not a sharding project. Traversal-heavy relationship queries ("friends-of-friends who liked X") → graph. Aggregations over billions of rows → columnar analytics, never OLTP engines.

Real use-case: A social app put feeds in MySQL — the "followers of user X" join crossed 40 tables at celebrity scale. Moving the graph to Neptune made the traversal a native query; DynamoDB took key-addressed timeline reads. The relational engine stayed for exactly the work it is good at: payments and their joins.

Gotchas & interview notes: the exam phrase "query patterns are well-known and fixed" points to DynamoDB; "flexible ad-hoc queries" points to relational. Anti-patterns to reject on sight: analytics on OLTP engines, joins on DynamoDB (do it in app code or re-model), session storage on RDS (cache it).

Aurora performance specifics

MySQL-compatible 5x / PostgreSQL 3x standard throughput; 6 storage copies across 3 AZs (fast parallel writes — the volume of the cluster is written as one, not per-instance); storage auto-grows to 128 TB; Aurora Replicas (up to 15, low-lag, each exposes its own endpoint; cluster endpoint routes writes, reader endpoint load-balances reads); Aurora Serverless v2 scales capacity instantly per workload.

The endpoint nuance: the cluster (writer) endpoint always points to the current writer after failover; the reader endpoint balances across replicas; an instance endpoint pins to one replica (useful for a BI tool that must not be load-balanced mid-query).

Read Scaling and Caching (The Two Big Levers)

How the levers differ: read replicas scale reads of CURRENT data — every SELECT, including complex ones, hits a real database. Caches absorb REPEATED reads of the same hot data, trading staleness (seconds, TTL-bounded) for microsecond latency and zero database load. Both reduce primary load; they compose.

1. Read replicas — for read-heavy relational workloads:

• Asynchronous; up to 15 (RDS) — serve reporting/BI off the primary
• Cross-Region replicas reduce latency for global users AND seed DR
• Application must use the reader endpoint — writes still go to the primary
• RDS Provisioned IOPS (io1/io2): guarantee IOPS for latency-critical DBs; gp3 for general
• DynamoDB: on-demand (spiky/unpredictable — pay per request) vs provisioned with auto scaling (predictable — cheaper) vs reserved capacity (steady)
• Partition key design decides DynamoDB performance: high-cardinality keys spread load; hot partition = throttling even with headroom
• User profiles + friendships: Neptune (graph feed queries)
• Posts/timelines: DynamoDB (partition key = userId; GSI on (userId, timestamp)); DAX in front for celebrity profiles (hot keys)
• Media metadata: Aurora MySQL (joins for analytics), 3 Aurora Replicas behind the reader endpoint for the feed service
• Trending counters: ElastiCache Redis sorted sets, flushed to DynamoDB hourly
• Lambda-heavy media service hitting Aurora through RDS Proxy (connection storms eliminated)
• Nightly analytics: Redshift loads from DynamoDB streams/S3

2. Caching layers:

Real use-case: A product page issues 6 queries (product, price, stock, reviews, similar, rating). The top-100 products are 80% of traffic. Read replicas still execute 6 queries per view; a Redis cache serves the whole page fragment in 1 ms with a 60-second TTL. Replicas scale the CEILING; the cache removes the LOAD — the design uses both.

Gotchas & interview notes: "reduce database load for repeated reads" → cache; "scale reads of fresh data / heavy reporting queries" → replicas. Replica lag means async replicas serve slightly old data — financial-consistency reads go to the primary. Cache invalidation is YOUR job — TTL plus event-driven invalidation on write.

Connections and Proxies

How the problem forms: every connection consumes DB memory (Postgres forks a process per connection); Lambda multiplies containers, each opening its own pool — a 1,000-concurrent function deployment presents 1,000+ connections to a database that considers 200 healthy.

Amazon RDS Proxy: fully managed connection pooler — multiplexes thousands of client connections onto few DB connections; also smooths Multi-AZ failovers (buffers requests during the DNS switch instead of dropping them); credentials pulled from Secrets Manager.

Real use-case: A serverless checkout API intermittently threw "too many connections" during flash sales. RDS Proxy in front of Aurora cut real DB connections from ~800 to ~40 steady; the same traffic now fails over between AZs without the Lambda fleet seeing connection errors at all.

Gotchas & interview notes: "Lambda + RDS" scenarios → RDS Proxy is nearly always in the answer. It also lets you MULTIPLY max_connections for serverless fleets AND survive failovers — two answers in one service.

Capacity Planning

• Asynchronous; up to 15 (RDS) — serve reporting/BI off the primary
• Cross-Region replicas reduce latency for global users AND seed DR
• Application must use the reader endpoint — writes still go to the primary
• RDS Provisioned IOPS (io1/io2): guarantee IOPS for latency-critical DBs; gp3 for general
• DynamoDB: on-demand (spiky/unpredictable — pay per request) vs provisioned with auto scaling (predictable — cheaper) vs reserved capacity (steady)
• Partition key design decides DynamoDB performance: high-cardinality keys spread load; hot partition = throttling even with headroom
• User profiles + friendships: Neptune (graph feed queries)
• Posts/timelines: DynamoDB (partition key = userId; GSI on (userId, timestamp)); DAX in front for celebrity profiles (hot keys)
• Media metadata: Aurora MySQL (joins for analytics), 3 Aurora Replicas behind the reader endpoint for the feed service
• Trending counters: ElastiCache Redis sorted sets, flushed to DynamoDB hourly
• Lambda-heavy media service hitting Aurora through RDS Proxy (connection storms eliminated)
• Nightly analytics: Redshift loads from DynamoDB streams/S3

How partition keys decide everything: DynamoDB spreads items across partitions BY KEY HASH; all items with one key value live in one partition with a 3,000 RCU / 1,000 WCU ceiling. A low-cardinality or celebrity-hot key ("status=active") concentrates load — the whole table throttles at 1,000 WCU while 90% of capacity sits idle. Fixes: high-cardinality composite keys, write sharding (append a random suffix), or GSI with a different key shape.

Real use-case: A leaderboard wrote every score update keyed by gameId — one hot game throttled the table. Re-keying to gameId#userId spread writes across partitions; the leaderboard rebuilt from a GSI. Same capacity, 40x the sustainable write rate.

Gotchas & interview notes: DynamoDB throttling with "headroom" in the account = hot key, always. On-demand mode removes planning but costs more at steady volume. RDS IOPS: io2 when latency is contractual; gp3 + sufficient baseline otherwise.

Migration Performance Note

Heterogeneous engines (Oracle → PostgreSQL): SCT converts schema/code, DMS replicates data with CDC; homogeneous: DMS native or snapshot restore.

Worked Example: Social App Database Stack

• Asynchronous; up to 15 (RDS) — serve reporting/BI off the primary
• Cross-Region replicas reduce latency for global users AND seed DR
• Application must use the reader endpoint — writes still go to the primary
• RDS Provisioned IOPS (io1/io2): guarantee IOPS for latency-critical DBs; gp3 for general
• DynamoDB: on-demand (spiky/unpredictable — pay per request) vs provisioned with auto scaling (predictable — cheaper) vs reserved capacity (steady)
• Partition key design decides DynamoDB performance: high-cardinality keys spread load; hot partition = throttling even with headroom
• User profiles + friendships: Neptune (graph feed queries)
• Posts/timelines: DynamoDB (partition key = userId; GSI on (userId, timestamp)); DAX in front for celebrity profiles (hot keys)
• Media metadata: Aurora MySQL (joins for analytics), 3 Aurora Replicas behind the reader endpoint for the feed service
• Trending counters: ElastiCache Redis sorted sets, flushed to DynamoDB hourly
• Lambda-heavy media service hitting Aurora through RDS Proxy (connection storms eliminated)
• Nightly analytics: Redshift loads from DynamoDB streams/S3

The senior summary: engine by access pattern, reads by replica-or-cache (fresh vs repeated), connections by proxy, and DynamoDB keys designed like the workload's heat map.