AWS Database Services (Task 3.4)

AWS Database Services

Source: https://docs.aws.amazon.com/whitepapers/latest/aws-overview/database-services.html

The exam matches workloads to database engines. Senior engineers match workloads to ACCESS PATTERNS first (read/write ratio, query shape, consistency needs, scale axis), then pick the engine — and they know the managed-vs-self-hosted trade at a contractual level.

EC2-Hosted vs AWS Managed Databases

Real use-case: A team needs an exotic Postgres extension that RDS does not support, with root access to tune the WAL — they self-host on EC2 and accept owning backups and failover scripts. Six months later the extension requirement disappears and they migrate to RDS to shed the operational load — the control was a cost, not a feature.

Gotchas & interview notes: choose EC2 only when you need OS-level access, exotic engines, or full tuning freedom. Otherwise managed wins on toil AND reliability (a Multi-AZ toggle vs your hand-built failover script is not a fair fight).

Relational: Amazon RDS and Aurora

Brief: RDS manages MySQL, PostgreSQL, MariaDB, Oracle, SQL Server. Aurora is AWS-built, MySQL/PostgreSQL-compatible, with a cloud-native storage layer.

How it works: RDS Multi-AZ keeps a synchronous standby in another AZ — failover 60–120 seconds, standby NOT readable (it exists for failover, not scale). Read replicas are asynchronous copies (up to 15) for read scaling — eventual consistency, can be cross-Region, promotable in a DR. Aurora writes 6 copies across 3 AZs (self-healing storage that auto-grows to 128 TB), delivering up to 5x MySQL / 3x PostgreSQL throughput; Aurora Serverless v2 scales capacity per-statement.

Example: reporting queries slow the production OLTP database → add a read replica and point BI tools at its endpoint (read scaling), leaving the primary free for writes.

Real use-case: A SaaS company runs Aurora MySQL Multi-AZ with 2 read replicas; a nightly reporting burst hits the replicas while the primary stays sub-10ms for transactions. When a replica lags during heavy analysis, the app's read path degrades gracefully — by design, not accident.

Gotchas & interview notes: the exam's most common DB trap: "scale READS" → read replicas; "survive AZ failure" → Multi-AZ. Multi-AZ standby cannot serve reads — answering "both" is wrong. Aurora replicates across AZs at the STORAGE layer (no replica lag for the primary's writes), and Aurora Global Database replicates cross-Region in <1 second.

Non-Relational and Purpose-Built

DynamoDB (deep enough to defend)

Brief: Serverless key-value/document database with single-digit-millisecond latency at ANY scale.

How it works: You define a partition key (and optional sort key); DynamoDB hashes items across partitions and spreads hot traffic automatically. Capacity modes: on-demand (pay per request — spiky/unpredictable) or provisioned with auto scaling (steady, cheapest at volume). A hot partition key (one celebrity's row in a fan-out) is the classic performance bug — fix with a composite/salted key design.

Real use-case: A game's player-inventory table sustains 500k reads/sec on Black Friday with zero capacity planning — on-demand pricing absorbed a 40x traffic spike that would have page-alarmed an RDS deployment.

Gotchas & interview notes: DynamoDB has no joins and no ad-hoc SQL — model access patterns UP FRONT. Exam trigger: "single-digit milliseconds at any scale" or "key-value at scale" → DynamoDB. ElastiCache/MemoryDB are the "memory-based" answers — the entire dataset lives in RAM.

Database Migration Tools

• AWS DMS (Database Migration Service): moves data between like or unlike engines (Oracle → Aurora) with minimal downtime — the source keeps serving while changes replicate (full load + change data capture).
• AWS SCT (Schema Conversion Tool): converts the schema and code (stored procedures, data types, PL/SQL → PL/pgSQL) when engines differ. Rule: SCT first (convert schema), then DMS (copy data).
• Homogeneous migration (MySQL → MySQL on RDS): DMS alone usually suffices.
• Profiles/history → Aurora PostgreSQL (Multi-AZ)
• Activity feed → DynamoDB (partition key = userId, single-digit ms at any scale)
• Social graph → Neptune
• Leaderboard top-100 → ElastiCache for Redis sorted set
• Migrating the old on-prem MySQL: SCT validates schema compatibility (none needed, same engine), DMS replicates with 10 minutes of cutover downtime.

Real use-case: An Oracle-to-Aurora migration: SCT reports 92% of the 400 stored procedures convert automatically, flags the rest for manual rewrite, then DMS runs a 6-hour full load plus continuous CDC — the final cutover window is 12 minutes of read-only mode.

Worked Example: Choosing Databases for an App

A social fitness app needs: user profiles + workout history (relational), a global activity feed at millions of reads/sec (key-value), friend-of-friend queries (graph), and sub-millisecond leaderboard lookups (in-memory).

• AWS DMS (Database Migration Service): moves data between like or unlike engines (Oracle → Aurora) with minimal downtime — the source keeps serving while changes replicate (full load + change data capture).
• AWS SCT (Schema Conversion Tool): converts the schema and code (stored procedures, data types, PL/SQL → PL/pgSQL) when engines differ. Rule: SCT first (convert schema), then DMS (copy data).
• Homogeneous migration (MySQL → MySQL on RDS): DMS alone usually suffices.
• Profiles/history → Aurora PostgreSQL (Multi-AZ)
• Activity feed → DynamoDB (partition key = userId, single-digit ms at any scale)
• Social graph → Neptune
• Leaderboard top-100 → ElastiCache for Redis sorted set
• Migrating the old on-prem MySQL: SCT validates schema compatibility (none needed, same engine), DMS replicates with 10 minutes of cutover downtime.