Amazon RDS (Relational Database Service)

Managed relational databases on AWS — PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, plus the Aurora variants. AWS handles backups, patching, minor version upgrades, failover; you handle schema, indexes, queries, and pool sizing.

What you get vs self-managed Postgres on EC2

You give up: root access, custom extensions not on the supported list, kernel tuning, sometimes the latest minor version.

Storage and instance types

• gp3 (general purpose SSD) — default; provisioned IOPS and throughput independent of size.
• io1 / io2 — high IOPS workloads; expensive.
• Aurora uses its own storage engine — see the Aurora doc.

Instance classes: db.r6g, db.m6i, db.t3 (burstable, avoid in prod). Right-sizing matters — memory is king for Postgres (shared_buffers, OS page cache).

Multi-AZ

A synchronous standby in another AZ. Failover takes 60-120s, automatic on instance failure. Standby is NOT a read replica — you cannot read from it. Failover triggers:

• Primary becomes unreachable.
• Primary’s AZ becomes unavailable.
• Manual reboot with failover.
• Instance class change (you can pre-resize standby to minimize downtime).

aws rds modify-db-instance \
  --db-instance-identifier orders-db \
  --multi-az \
  --apply-immediately

Multi-AZ doubles your storage cost. Standby is hot but not visible.

Read replicas

Asynchronous logical replication. For Postgres, uses native streaming replication. Use cases:

• Offload read-heavy queries (reporting dashboards, ETL).
• Cross-region replicas for DR or latency.
• Major-version upgrade: spin up replica on new version, switch.

Lag is real (usually < 1s, can spike). Read-after-write consistency is NOT guaranteed if you read from replica.

Promote a replica to standalone master in a DR scenario:

aws rds promote-read-replica --db-instance-identifier orders-replica

Connection pooling: RDS Proxy

Postgres connections are expensive (a process per connection + memory). At Lambda scale or k8s with many pods, your DB drowns in connections before CPU is saturated.

RDS Proxy is managed pgbouncer-like pooling in front of RDS:

• Multiplexes thousands of client connections to a smaller pool of DB connections.
• Survives DB failovers transparently — clients reconnect to proxy, proxy reconnects to new primary.
• Handles IAM auth or Secrets Manager creds.
• Costs ~$0.015/h per vCPU of the underlying DB.

Gotcha: RDS Proxy pins connections (won’t multiplex) when you use prepared statements, session-level features (SET, advisory locks), or LISTEN/NOTIFY. With pinning, you lose the multiplexing benefit.

For app-side pooling: see backend/08_databases/sql/09_connection_pooling.md.

Backups and PITR

• Automated backups — daily snapshot + transaction logs for point-in-time recovery (PITR). Retention 1-35 days.
• Manual snapshots — keep forever, can copy across regions.
• PITR restores to any second in the retention window — creates a NEW DB instance, doesn’t modify the existing one.

PITR is slow (10s of minutes to hours depending on size). Plan recovery procedures accordingly — RTO ≠ RPO.

Performance Insights

The thing you’ll actually use in production. Visual breakdown of “what’s loading the DB right now”, grouped by query, user, host, wait event. Free for 7 days retention.

Top use case: finding the query causing CPU/IO spikes. pg_stat_statements extension shows aggregate; Performance Insights shows the time series.

IAM authentication

Instead of passwords, use short-lived IAM tokens to connect:

import boto3, psycopg
rds = boto3.client("rds")
token = rds.generate_db_auth_token(
    DBHostname="orders.cluster-xxx.us-east-1.rds.amazonaws.com",
    Port=5432, DBUsername="app",
)
conn = psycopg.connect(
    host="...", user="app", password=token,
    sslmode="require",
)

Token TTL is 15 min. Combine with IRSA (EKS) for keyless DB auth.

Throughput cap: IAM auth supports ~200 connections/sec — fine for human users, brittle for high-churn workloads. Use Secrets Manager + RDS Proxy at scale.

Cost optimizations

• Reserved Instances — 1 or 3 year commit; ~30-60% off on-demand. Steady-state workloads win.
• gp3 over io1 unless you need > 16k IOPS.
• Stop dev/staging DBs after hours — RDS supports stopping for up to 7 days; storage still costs.
• Right-size based on Performance Insights, not vibes.

Common gotchas

• t3 burstable instances in prod. Run out of CPU credits, hard throttle, look fine in metrics until they don’t. Use only for dev.
• Default max_connections scales with instance class. Going to a smaller instance drops your connection ceiling.
• Major version upgrades require maintenance window + downtime; rehearse on a clone.
• Aurora vs RDS confusion. Aurora is its own animal — different storage, different scaling, different connection model. Don’t assume RDS knowledge applies 1:1.
• Multi-AZ standby isn’t a read replica. Many candidates fail this on interviews.
• Free tier db.t3.micro runs out fast. Don’t build a product roadmap around it.

Interview angle

• “RDS vs running Postgres on EC2 yourself?” — RDS gives managed backups, patching, failover, monitoring, IAM auth, and read replicas. You lose: root access, certain extensions, ability to run latest minor versions before AWS supports them. For 95% of teams, the trade is worth it.
• “Multi-AZ standby vs read replica?” — Multi-AZ is synchronous failover within a region; standby is invisible (you cannot read from it). Read replica is asynchronous; you can read from it but expect lag and not read-after-write consistency.
• “How do you handle connection pooling on RDS at Lambda scale?” — RDS Proxy. Many Lambdas each open connections → DB drowns. Proxy multiplexes thousands of client conns to a small DB pool, handles failover transparently. Watch for pinning when prepared statements / session state / LISTEN-NOTIFY are used.
• “What’s PITR and how does it work?” — Point-In-Time Recovery; daily snapshot + transaction logs for the retention window. Restores create a new DB instance at the requested timestamp. RTO is slow (minutes to hours); plan accordingly.
• “How do you do a major version upgrade with minimum downtime?” — provision a replica on the new version (cross-version replication where supported), let it catch up, switch traffic via a connection string change or DNS, promote, decommission old. Test thoroughly on a clone first.