### Why picking the wrong database is an expensive mistake It is 3 AM at a fintech startup in Bengaluru. The on-call engineer gets paged. The trading ledger table has grown to 40 million rows and every SELECT with a JOIN across three tables now takes 8 seconds. The team chose DynamoDB two years ago because "NoSQL scales better." Nobody asked whether the access pattern needed joins and transactions in the first place. It did. Now a migration that should have taken a week at the start takes a quarter, live, under load. This is not a rare story. Database selection mistakes are some of the most expensive mistakes in system design because they surface late, after the schema is baked into every service that touches it. * Wrong choice at design time -> works fine at low scale -> breaks at real scale * Fixing it later means a live migration, not a config change * The fix always costs more the longer you wait to make it > 📌 **Remember:** The question is never "which database is best." It is "what is the shape of my data, and how will it be accessed." ### The three questions that decide your database Before opening the AWS console, answer these for your workload: * **Does the data have relationships that need joins?** A trading ledger, an orders-to-line-items structure, anything with foreign keys pointing at other tables -> relational. * **What is the access pattern?** Single-key lookups at massive scale (get user by ID) behave completely differently from ad-hoc reporting queries (sum revenue by region by month). * **What are the consistency and latency requirements?** A bank balance needs strong consistency. A social media like-counter can be eventually consistent and nobody notices. Data has joins + needs transactions? -> RDS or Aurora Key-value or JSON, single-key lookups, massive scale? -> DynamoDB Need to reduce load on an existing database with caching? -> ElastiCache MongoDB-compatible documents, complex nested queries? -> DocumentDB Relationships between entities matter more than the entities themselves? -> Neptune Every service covered in this module maps to one of these five answers. Get the answer right and the rest of your architecture gets simpler. Get it wrong and every layer above the database inherits the pain. The single question underneath all of this: **what are the application's access patterns?** Everything else - schema design, index choices, which service to pick - flows from that answer. | Requirement | Likely choice | |:---|:---| | SQL, joins, ACID transactions | RDS or Aurora | | Managed relational, higher performance, less ops overhead | Aurora | | Key-value or JSON, massive scale, single-key lookups | DynamoDB | | Shared cache or session store in front of an existing database | ElastiCache | | DynamoDB-specific item and query caching, no code changes | DAX | | MongoDB-compatible document workload | DocumentDB | | Deep relationship or graph traversal | Neptune | ### How this connects to the bigger picture A production system rarely uses just one database. A Swiggy-style food delivery platform might run RDS for the orders and billing ledger (needs transactions), DynamoDB for real-time delivery partner location updates (needs massive write throughput at low latency), and ElastiCache for the restaurant menu cache (needs microsecond reads on data that barely changes). Choosing per-workload, not per-company, is the senior engineer mindset this module builds toward. ---
### What RDS actually manages for you A team spins up PostgreSQL directly on an EC2 instance. Six months in, the engineer who set it up has left. Nobody knows the backup schedule, the failover has never been tested, and a disk-full error at 2 AM takes the whole app down because there was no standby. **RDS (Relational Database Service)** is a managed service for standard SQL databases. AWS handles provisioning, OS patching, backups, monitoring, and failover. You manage the schema and the queries. You cannot SSH into an RDS instance - that boundary is the whole point. * **Supported engines:** PostgreSQL, MySQL, MariaDB, Oracle, Microsoft SQL Server, IBM DB2, and Aurora * Storage Auto Scaling grows your volume automatically when free space runs low * Automated backups with point-in-time restore up to 35 days * Encryption at rest with KMS; TLS/SSL is available and recommended for connections in transit, not automatically enforced on every connection by default * IAM Authentication and integration with Secrets Manager for credential rotation > 📌 **Remember:** RDS gives up OS-level access in exchange for AWS managing patching, backups, and failover. If you need OS access on Oracle or SQL Server specifically, RDS Custom exists for that narrow case. ### Why Read Replicas and Multi-AZ solve different problems This is the single most confused pair of concepts in RDS, and it shows up constantly in interviews. A **Read Replica** is an asynchronous copy of your primary database. It exists to take read traffic off the primary so writes stay fast. * Up to 15 Read Replicas per instance, same AZ, different AZ, or different Region * Replication is ASYNC - there is a small lag, replicas are eventually consistent * Replicas serve SELECT only, never INSERT, UPDATE, or DELETE * Same-region replicas have no data transfer cost; cross-region replicas do **Multi-AZ** is a synchronous standby in a different Availability Zone that exists purely for disaster recovery. It serves no traffic during normal operation. Read Replicas -> scale READ performance (ASYNC, up to 15 replicas) Multi-AZ -> disaster recovery and failover (SYNC, standby only) > 🔴 **Common Mistake:** Engineers assume Multi-AZ improves read performance because there is a "second instance sitting there." It does not serve any traffic under normal conditions - it exists only to fail over to. If you need both read scaling and disaster recovery, you need Read Replicas AND Multi-AZ together, not one instead of the other. ### Backups - automated versus manual Automated backups: Daily full backup during the maintenance window Transaction logs backed up every 5 minutes Restore to any point in time from 5 minutes ago to 35 days back Retention: 1 to 35 days (0 disables PITR entirely) Manual snapshots: Triggered by you, retained until you delete them Take one before any schema migration - if it fails, restore and retry > ⚠️ **Security:** Setting backup retention to 0 does not just skip daily backups - it disables point-in-time recovery completely. For production, choose a retention period based on your recovery point and recovery time objectives (RPO/RTO); 7 days is a reasonable starting point for many workloads, not a universal rule. ### Managing connections at scale with RDS Proxy A team moves their backend to Lambda. Traffic spikes and Lambda scales from 0 to a thousand concurrent executions in seconds. Each invocation opens its own database connection. A `db.t3.micro` supports roughly 85 concurrent connections. The database starts rejecting connections and the app goes down - not because the database is overloaded on queries, but because it ran out of connection slots. **RDS Proxy** sits between your application and the database and pools connections, so many application-side connections share a much smaller number of actual database connections. ```text Without RDS Proxy: 1,000 Lambda invocations -> 1,000 DB connections -> limit exceeded -> failures With RDS Proxy: 1,000 Lambda invocations -> connect to Proxy -> Proxy holds ~20 DB connections -> database is never overwhelmed ``` * Reduces failover impact - the Proxy can keep serving already-pooled connections while the database fails over behind it * Enforces IAM authentication and pulls credentials from Secrets Manager instead of hardcoding them in application code * Never publicly accessible - only reachable from inside the VPC * Supports MySQL, PostgreSQL, MariaDB, and SQL Server on RDS, plus MySQL and PostgreSQL on Aurora > 📌 **Remember:** RDS Proxy solves a connection-count problem, not a query-performance problem. If your database is slow because of bad queries or undersized instances, adding a Proxy will not fix that - it only helps when the bottleneck is too many simultaneous connections, which is exactly the pattern Lambda and other serverless compute produce. ---
### Why Aurora exists when RDS already works A team is running PostgreSQL on RDS. It works, but failover takes almost two minutes during an incident, and replica lag under load stretches into seconds - long enough that a user who just placed an order does not see it on the confirmation page. **Aurora** is AWS's own database engine, built from scratch for the cloud, compatible with PostgreSQL and MySQL wire protocols so existing drivers and queries work unchanged. Aurora vs Standard RDS: AWS benchmarks claim up to 5x MySQL throughput and 3x PostgreSQL throughput versus standard RDS under specific tested workloads Typically lower replica lag and faster failover than standard RDS Multi-AZ, though exact numbers depend on workload and configuration Storage auto-grows 10 GB at a time up to 128 TB 6 copies of data across 3 AZs, self-healing at the storage layer Up to 15 Read Replicas Typically priced higher than equivalent standard RDS instances AWS positions Aurora as offering higher performance and faster failover than many comparable standard RDS deployments, but actual results depend heavily on instance size, query design, concurrency, and region - treat any specific multiplier as a benchmark claim under a specific workload, not a guarantee for your workload. **Aurora's storage architecture** is what makes this possible: 6 copies spread across 3 AZs, where only 4 of 6 copies are needed to confirm a write and 3 of 6 are needed to serve a read. That means Aurora tolerates losing entire AZs' worth of copies and keeps running. ### Writer and Reader Endpoints - why connection strings never change Aurora gives every cluster two stable DNS endpoints that your application connects to once and never has to update, even after a failover. Writer Endpoint -> always points to the current master After failover, the SAME DNS name resolves to the new master Your app writes here - always Reader Endpoint -> load balances across all Read Replicas Your app reads here - always Add more replicas -> Reader Endpoint automatically includes them > 💡 **Tip:** If your cluster has replicas of very different sizes - small ones for app traffic, large ones for a nightly analytics job - define a Custom Endpoint pointing only at the large replicas. This isolates the analytics team's heavy queries from your production read path completely. ### Aurora Serverless, Global Aurora, and cloning **Aurora Serverless v2** removes instance sizing entirely. It automatically adjusts capacity based on demand and bills per second, and can use auto-pause to reduce capacity to zero when configured and supported by your engine version - a good fit for a dev environment used only during business hours, or a new product with an unknown traffic pattern. **Aurora Global Database** replicates to up to 5 secondary regions with under 1 second of lag, and can promote a secondary to primary in under a minute during a regional failure - this is what a company like Zomato would use to keep the app usable in one region while another recovers. **Aurora Database Cloning** creates a full staging copy of production in minutes using copy-on-write - the clone shares the same storage as production until it diverges, so creating it is nearly instant and free until changes are made. ```text Aurora Cloning flow: Production cluster (unchanged, still serving live traffic) -> Clone created (shares same storage volume initially) -> Staging team runs migration test on the clone -> Only changed blocks are copied - production untouched throughout ``` > **Note:** Copy-on-write means the clone and the original point at the same physical data until either one writes new data. Only the changed pages get duplicated. This is why cloning a multi-terabyte Aurora cluster takes minutes instead of hours. ---
### What makes DynamoDB fundamentally different from RDS A gaming company built a leaderboard on RDS. At 50,000 concurrent players, writes started queuing and latency spiked past 200ms. The access pattern was simple - get and update a score by player ID - but the relational engine was paying overhead for consistency guarantees the workload did not need. **DynamoDB** is AWS's fully managed NoSQL database. No fixed schema, no joins, single-digit millisecond performance regardless of table size, and it scales to millions of requests per second without you provisioning anything. RDS (relational): Every row must share the same columns Relationships defined through joins and foreign keys Schema is fixed and hard to change later DynamoDB (NoSQL): Each item can have completely different attributes No joins between tables New attributes just appear on new items - no ALTER TABLE, ever ### Partition keys, sort keys, and why the choice cannot be undone Every DynamoDB table needs a **Primary Key**, chosen at creation and never changed afterward. * **Partition Key only** - one unique value per item, for example `UserID` * **Partition Key + Sort Key** - the combination must be unique, for example `UserID` + `GameID`, which lets one user have many items in the same table ```text Table: GameScores Partition Key Sort Key Score Result (UserID) (GameID) rahul-001 game-42 92 Win priya-002 game-19 14 Lose priya-002 game-42 77 Win ``` `priya-002` appears twice because she played two different games - the UserID + GameID combination is still unique, so both items are valid in the same table. > 🔴 **Common Mistake:** Choosing a low-cardinality partition key - like a boolean flag or a status field with only 3 possible values - creates a hot partition. All the traffic for that value lands on one physical partition while the rest sit idle, and no amount of read/write capacity fixes an architectural key choice. Pick a high-cardinality attribute like `UserID` or `OrderID`. ### GSI, LSI, and capacity modes A **Global Secondary Index (GSI)** lets you query by an attribute other than the primary key - for example, finding all orders by `CustomerEmail` when the table's primary key is `OrderID`. A **Local Secondary Index (LSI)** shares the same partition key as the base table but uses a different sort key, and must be defined at table creation time. On-Demand capacity: Pay per request, scales automatically, zero planning Use for unpredictable or spiky traffic Provisioned capacity: Set Read/Write Capacity Units upfront, add Auto Scaling on top Cheaper per request for steady, predictable traffic > 💡 **Tip:** Start new applications on On-Demand. Once you have a few weeks of real traffic data, switch to Provisioned with Auto Scaling if the pattern is steady - it is meaningfully cheaper at scale. ### Streams, Global Tables, and TTL **DynamoDB Streams** captures every insert, update, and delete as an ordered, time-limited changelog. It is the foundation for two important features: * Trigger a Lambda function in real time when an item changes - for example, sending a welcome email the moment a user record is created * Power **Global Tables** replication, which uses Streams internally to propagate changes between regions **Global Tables** give you active-active multi-region replication - unlike RDS Multi-AZ where only one region is ever active, every region in a Global Table accepts both reads and writes, with changes propagating to all other regions within seconds. **TTL (Time To Live)** automatically deletes items after a timestamp you set, at zero extra cost - perfect for session tokens that should expire, without a scheduled cleanup job. > 📌 **Remember:** DynamoDB Global Tables use DynamoDB Streams internally as the underlying replication mechanism. Understanding that Streams is the engine behind cross-region propagation matters more than memorizing exact setup ordering, which the console and current APIs largely manage for you. ---
### The problem DAX solves that ElastiCache does not A product catalogue page gets hit by the same `GetItem` call for the top 20 products thousands of times a minute. DynamoDB responds in single-digit milliseconds every time - fast, but those repeated identical reads add up in cost and latency for data that barely changes. **DAX** is a fully managed in-memory cache that sits directly in front of DynamoDB and requires zero application code changes - point your SDK at the DAX endpoint instead of DynamoDB directly, and it speaks the exact same API. ```text Application -> DAX Cluster In cache? -> return in microseconds Not in cache? -> fetch from DynamoDB -> cache it -> return Default cache TTL: 5 minutes ``` ### DAX versus ElastiCache - a decision that trips up a lot of engineers Both are in-memory caches, but they solve different layers of the same problem. | Feature | DAX | ElastiCache | |:---|:---|:---| | Works with | DynamoDB only | Any data source | | Code changes needed | None - same API | Yes - manual cache logic | | Caches | Raw item lookups | Computed or aggregated results | DAX use case: Repeated "get user where UserID = 101" calls No code change - just point the SDK at DAX ElastiCache use case: "Total sales for all users this month" - requires summing many records DynamoDB cannot compute this itself You calculate once, store in ElastiCache, read from cache after Before reaching for either cache, work through this order: confirm caching is actually needed (is the read volume genuinely a problem, or a premature optimization), check whether the access pattern or index design could solve it without a cache, then decide between DAX and ElastiCache based on data source and whether your application's consistency requirements are compatible with DAX's eventual-consistency cache behavior. > 💡 **Tip:** Once caching is genuinely justified - too many repeated identical reads hitting DynamoDB specifically, with a workload DAX's consistency model fits -> DAX is usually the simpler fit. Need to cache a value that required computation across many records, or the source is not DynamoDB at all -> that is ElastiCache's job. ---
### Why caching is a code change, not a checkbox A team enables ElastiCache expecting their RDS load to drop immediately. Nothing changes. The database is still taking every single request because the application was never modified to check the cache first. **ElastiCache** is a managed in-memory data store - Redis or Memcached - that delivers sub-millisecond latency and takes read load off your primary database. But it only helps if your application code checks the cache before the database. > 🔴 **Common Mistake:** Standing up an ElastiCache cluster and expecting automatic benefit. The application must explicitly check the cache first, and on a miss, query the database and write the result back to the cache. This is application logic, not infrastructure configuration. ### Choosing Redis over Memcached | Feature | Redis | Memcached | |:---|:---|:---| | Multi-AZ auto-failover | Yes | No | | Data persistence | Yes (AOF) | No, lost on restart | | Data structures | Strings, Sets, Sorted Sets, Hashes | String only | | Multi-threaded | No | Yes | Redis is generally the more feature-rich choice when you need persistence, richer data structures, replication with automatic failover, or advanced caching patterns like sorted-set leaderboards. Memcached can be a reasonable fit for simple, ephemeral caching where those capabilities are not required and its multi-threaded architecture is a better match for the workload. ### The two caching patterns every engineer should know **Lazy Loading** - check the cache first on every read. A hit returns instantly. A miss queries the database, stores the result in the cache, then returns it. Only data that is actually requested ever gets cached. **Session Store** - write user session data to ElastiCache instead of local EC2 memory. Any instance behind a load balancer can then read any user's session, which removes the need for sticky sessions entirely. ```text Without ElastiCache session store: Session lives in EC2 instance memory Load balancer must route the same user to the same instance every time (sticky) One instance gets overloaded, others sit idle With ElastiCache session store: Session lives in Redis, shared by every instance Load balancer routes freely - true even distribution ``` > **Note:** Redis Sorted Sets deserve a specific mention here - a real-time leaderboard for millions of players (score, rank, top-10 queries) is a workload relational databases handle badly at scale but Redis handles natively with `ZADD` and `ZRANGE` commands running in near-constant time. ---
Why picking the wrong database is an expensive mistake It is 3 AM at a fintech startup in Bengaluru. The on-call enginee...
What RDS actually manages for you A team spins up PostgreSQL directly on an EC2 instance. Six months in, the engineer wh...
Why Aurora exists when RDS already works A team is running PostgreSQL on RDS. It works, but failover takes almost two mi...
What makes DynamoDB fundamentally different from RDS A gaming company built a leaderboard on RDS. At 50,000 concurrent p...
The problem DAX solves that ElastiCache does not A product catalogue page gets hit by the same GetItem call for the top ...
Why caching is a code change, not a checkbox A team enables ElastiCache expecting their RDS load to drop immediately. No...
DocumentDB - when your team already thinks in MongoDB A content management team already runs MongoDB on-premises with de...
A structured approach beats guessing An EC2 instance in a private subnet cannot reach its RDS database. Panic-clicking t...
This lab builds a small but realistic setup: an RDS primary with a Read Replica, an ElastiCache cluster in front of it, ...
Database selection at a glance: Need Service SQL with joins, ACID transactions RDS Same as RDS, need more speed, less op...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.