RDS: Managed Relational Databases

When to use

Run PostgreSQL / MySQL / MariaDB / Oracle / SQL Server without managing OS / backups / patching.

Analogy

RDS is like leasing a fully serviced office - landlord handles the building; you handle the work and the keys to your desk.

Data-flow diagram

  Primary DB (writer)
       |  sync (Multi-AZ) ---> Standby
       |  async             --> Read Replica(s)
       +-------- Automated Backups to S3
        (daily + continuous WAL archive  -> PITR)

Deep explanation

RDS supports 6 engines (PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, Aurora). Multi-AZ creates a synchronous standby; automatic failover (~60-120s). Up to 5 Read Replicas for read scaling (async). Automated backups run daily + WAL continuously - PITR (point-in-time restore) lets you rewind to any second in the retention window (default 7d, max 35d). Storage auto-grows; Performance Insights surfaces DB load.

Examples

Example 1

aws rds create-db-instance \
  --db-instance-identifier mydb \
  --engine postgres --engine-version 16.3 \
  --db-instance-class db.t4g.medium \
  --allocated-storage 100 --storage-type gp3 \
  --multi-az --db-subnet-group-name my-private

Multi-AZ Postgres 16 with GP3 storage, placed in private subnets only.

Example 2

aws rds create-db-snapshot \
  --db-instance-identifier mydb \
  --db-snapshot-identifier mydb-pre-migration

manual snapshot before a risky migration; durable named checkpoint.

Example 3

aws rds restore-db-instance-to-point-in-time \
  --target-db-instance-identifier mydb-restore \
  --source-db-instance-identifier mydb \
  --restore-time 2024-05-10T14:30:00Z

rewinds the DB to a specific second by replaying WAL.

Common mistake

Treating RDS as auto-scale infinitely - storage autoscales only UP, and modifying the instance class requires a brief outage. Also: plaintext master password in Terraform.

Key takeaway

Multi-AZ in production; Subnet Group must be private subnets only; Secrets Manager for credentials; Performance Insights ON; backup retention >= 7 days.

Production Failure Playbook

Failure scenario 1: lost-connection-during-failover

• Symptom: After Multi-AZ failover, app saw 30s of 5xx because connection pool didn’t drain.
• Detection: RDS events; CloudWatch failover event; error rate spike in APM.
• Root cause: Failover swapped DNS (CNAME) but the connection pool didn’t re-establish.
• Mitigation: use PgBouncer or RDS Proxy; library retry-on-failover; shorter connect_timeout.
• Prevention: ADR mandates RDS Proxy; load test enables forced failover via reboot-db-instance --force-failover.

Failure scenario 2: engine-version-end-of-support

• Symptom: AWS emailed ‘engine version 11 will be retired on 2024-09-30’; production on Postgres 11.
• Detection: AWS Health event; RDS console ‘engine version end-of-support’ banner.
• Root cause: no one monitored RDS engine version lifecycle; RDS auto-upgrades minor but major needs manual.
• Mitigation: plan major upgrade; test on staging; aws rds modify-db-instance --engine-version 16 --allow-major-engine-upgrade.
• Prevention: AWS Config rule checks engine version in supported list; quarterly upgrade plan.