AWS Databases — RDS¶

RDS DB Instance = EC2 instance (hidden) + EBS volume + RDS management layer

┌─────────────────────────────────────────────┐
│              RDS DB Instance                │
│  ┌─────────────────────────────────────┐    │
│  │  DB Engine (MySQL / PostgreSQL / …) │    │
│  └────────────────┬────────────────────┘    │
│                   │ reads/writes            │
│  ┌────────────────▼────────────────────┐    │
│  │         EBS Storage (gp3/io2)       │    │
│  └─────────────────────────────────────┘    │
│                                             │
│  Security Group → controls port access      │
│  VPC Subnet     → network placement         │
│  IAM Role       → access to AWS services    │
│  Parameter Group→ DB engine config          │
└─────────────────────────────────────────────┘

Enable max storage threshold (e.g., 1 TB max)
  → RDS automatically increases storage when:
     - Free storage < 10% of total
     - Low storage lasts > 5 minutes
     - 6 hours since last auto-scale
  → No downtime for storage increase

Requirements:
  - Must span at least 2 AZs
  - Use private subnets (no public access to DB)
  - Separate from application subnets (best practice)

Example:
  subnet-db-1a (10.0.10.0/24) AZ-1a
  subnet-db-1b (10.0.11.0/24) AZ-1b
  subnet-db-1c (10.0.12.0/24) AZ-1c

RDS SG inbound rules:
  Allow TCP 3306 (MySQL) from App-SG   ← reference SG ID, not IP
  Allow TCP 5432 (PostgreSQL) from App-SG

Never:
  Allow TCP 3306 from 0.0.0.0/0        ← never expose DB to internet

Read Replica use case:
  App → write → Primary DB
  App → read  → Read Replica (offload reads)
  Reporting tools → Read Replica (heavy queries don't affect primary)

Multi-AZ use case:
  Primary fails (AZ down, hardware failure)
  → RDS flips DNS CNAME to standby
  → Application reconnects to same endpoint — no code change
  → Failover: ~1–2 minutes (Multi-AZ Instance) [aws.amazon](https://aws.amazon.com/blogs/database/choose-the-right-amazon-rds-deployment-option-single-az-instance-multi-az-instance-or-multi-az-database-cluster/)

[Primary Instance] ← all reads + writes
One AZ — instance failure = downtime until manual recovery
Use: Dev/test only

[Primary Instance] ──sync replication──→ [Standby Instance]
Same Region, different AZ              ← passive, no traffic
Failover time: ~1–2 minutes [aws.amazon](https://aws.amazon.com/blogs/database/choose-the-right-amazon-rds-deployment-option-single-az-instance-multi-az-instance-or-multi-az-database-cluster/)
RPO (data loss): ~0 (synchronous)
RTO (downtime): ~1–2 minutes

[Writer Instance]
    ├── sync replication ──→ [Reader Instance 1] AZ-2  ← serves reads
    └── sync replication ──→ [Reader Instance 2] AZ-3  ← serves reads
Failover time: ~25–75 seconds [aws.amazon](https://aws.amazon.com/blogs/database/choose-the-right-amazon-rds-deployment-option-single-az-instance-multi-az-instance-or-multi-az-database-cluster/)
Benefit over Multi-AZ Instance: readers serve traffic (not passive)

Retention: 1–35 days (default: 7 days; set to 0 to disable)
Backup window: daily snapshot during configured time window
Transaction logs: backed up every 5 minutes → enables PITR to any second

Point-in-Time Recovery (PITR):
  Restore DB to any second within retention window
  Creates NEW DB instance (does not overwrite existing)

User-triggered
Retained indefinitely (not affected by retention period)
Can copy to another region → cross-region DR
Can share with another AWS account
Restore → creates new DB instance with restored data

Enabled at creation with KMS key (cannot encrypt existing unencrypted instance)
Encrypts: DB instance + automated backups + read replicas + snapshots

To encrypt existing unencrypted DB:
  1. Create snapshot
  2. Copy snapshot with encryption enabled
  3. Restore from encrypted snapshot
  4. Point application to new instance

SSL/TLS enforced by parameter group setting:
  MySQL: rds.force_ssl = 1
  PostgreSQL: rds.force_ssl = 1

IAM Authentication flow:
  1. EC2 calls aws rds generate-db-auth-token (requires IAM permission)
  2. Token valid for 15 minutes
  3. Use token as password in DB connection
  4. RDS validates token against IAM — no password stored anywhere ✅

Problem:
  Lambda scales to 1,000 concurrent functions
  Each opens a DB connection
  MySQL max connections: ~100–200 for small instances
  → Connection errors, DB overload ❌

With RDS Proxy:
  Lambda → RDS Proxy (connection pool: 100 connections to DB)
  → Proxy multiplexes 1,000 Lambda connections onto 100 DB connections ✅
  → DB sees manageable connection count

Also:
  - IAM authentication enforced at proxy level
  - Secrets Manager integration (credentials never in Lambda code)
  - Failover faster: ~66% reduction in failover time (proxy handles reconnection)

Performance Insights:
  Free: 7 days retention
  Paid: up to 2 years
  Shows: which SQL queries are causing load, what they're waiting for
  Use: identify slow queries without needing slow query log

Standard RDS (MySQL):                    Aurora:
  [Compute] → [EBS Volume]               [Compute (Primary + 15 Replicas)]
  One EBS per instance                       ↓
  Replication copies data                [Shared Cluster Volume]
  Replica lag: seconds                    6 copies of data across 3 AZs
                                          Replicas share storage — no copy needed
                                          Replica lag: milliseconds

Aurora Endpoints:
  Writer endpoint:  my-cluster.cluster-xyz.us-east-1.rds.amazonaws.com
    → Always points to primary (even after failover)

  Reader endpoint:  my-cluster.cluster-ro-xyz.us-east-1.rds.amazonaws.com
    → Load-balances reads across all Aurora Replicas

Automatically scales compute in fine-grained increments (0.5 ACU units)
Pay per ACU-second used — no idle cost
Scales from minimum to maximum ACU in ~seconds
Use case: unpredictable workloads, dev/test, multitenant apps

One primary Region + up to 5 secondary read-only Regions
Replication lag: < 1 second (storage-based replication, no DB impact)

Use case:
  Global apps needing low latency reads in multiple continents
  DR: promote secondary to primary in < 1 minute if primary region fails

Failover types:
  Managed failover: RDS orchestrates promotion (~1 min)
  Manual failover: you control the switch