RDS Setup & Instance Types

Amazon RDS manages relational database infrastructure — provisioning, patching, backups, and scaling. Choosing the right engine, instance class, storage type, and deployment mode affects cost, performance, and availability.

Supported Engines & Instance Classes

# Supported engines:
# MySQL 8.0          — popular open source; good general-purpose choice
# PostgreSQL 16      — most feature-rich open source; JSON, extensions, PL/pgSQL
# MariaDB 10.x       — MySQL fork; XtraDB storage engine
# Oracle             — Enterprise-only features; bring your own license or license-included
# SQL Server         — Windows/.NET stacks; Express/Standard/Enterprise editions
# Aurora MySQL       — MySQL-compatible; up to 5x faster than MySQL; serverless option
# Aurora PostgreSQL  — PG-compatible; up to 3x faster than PostgreSQL; Global Database

# Instance class families:
# db.t3/db.t4g — burstable; cheapest; for dev/test or low-traffic
# db.m5/db.m6i/db.m7g — general purpose; balanced CPU/memory; good baseline
# db.r5/db.r6i/db.r7g — memory optimized; for large databases, in-memory caches
# db.x2g/db.x1e — extreme memory; SAP HANA, large OLAP
# *g suffix = Graviton (ARM) — ~20% better price/performance than Intel equivalent

# Create a PostgreSQL RDS instance
aws rds create-db-instance \
  --db-instance-identifier myapp-prod-db \
  --db-instance-class db.m6g.large \
  --engine postgres \
  --engine-version 16.1 \
  --master-username dbadmin \
  --master-user-password "$(openssl rand -base64 20)" \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-encrypted \
  --multi-az \
  --db-subnet-group-name myapp-db-subnet-group \
  --vpc-security-group-ids sg-0db123 \
  --backup-retention-period 14 \
  --no-publicly-accessible \
  --tags "Key=Env,Value=prod"

# Wait for instance to be available
aws rds wait db-instance-available --db-instance-identifier myapp-prod-db

# Describe instance
aws rds describe-db-instances \
  --db-instance-identifier myapp-prod-db \
  --query "DBInstances[0].{Endpoint:Endpoint.Address,Port:Endpoint.Port,Status:DBInstanceStatus}"

Storage Types & Parameter Groups

# Storage types:
# gp2 — General Purpose SSD; 3 IOPS/GB baseline; burst to 3000 IOPS; being replaced by gp3
# gp3 — General Purpose SSD v2; 3000 IOPS baseline (decoupled from storage size); 125 MiB/s
#        upgrade gp2 → gp3 for ~20% cost savings with same or better performance
# io1/io2 — Provisioned IOPS; for latency-sensitive workloads; up to 256k IOPS (io2 Block Express)
# magnetic — legacy; avoid for new deployments

# Modify storage type and IOPS (no downtime for gp2→gp3)
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --storage-type gp3 \
  --iops 6000 \
  --storage-throughput 250 \
  --apply-immediately

# DB Subnet Group — tells RDS which subnets it can use (must span ≥2 AZs for Multi-AZ)
aws rds create-db-subnet-group \
  --db-subnet-group-name myapp-db-subnet-group \
  --db-subnet-group-description "RDS subnets for myapp" \
  --subnet-ids subnet-private-aaa111 subnet-private-bbb222 subnet-private-ccc333

# Parameter Groups — tune database engine settings without SSH access
# Each engine+version has its own family (e.g. postgres16, mysql8.0)
aws rds create-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --db-parameter-group-family postgres16 \
  --description "Custom params for myapp"

# Modify a parameter
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --parameters "ParameterName=max_connections,ParameterValue=500,ApplyMethod=pending-reboot" \
               "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory/4},ApplyMethod=pending-reboot" \
               "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate"

# Apply parameter group to instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --db-parameter-group-name myapp-postgres16-params

RDS Backups & High Availability

RDS provides automated backups, manual snapshots, and Multi-AZ deployments out of the box. Read replicas extend this further with read scalability and cross-region DR. Aurora adds additional HA capabilities with its storage architecture.

Automated Backups & Snapshots

# Automated backups:
# - Enabled by default (retention: 1-35 days, default 7)
# - Full daily snapshot + transaction logs → enables point-in-time recovery (PITR)
# - Backup window: 30-min window when backup runs (choose low-traffic time)
# - Stored in S3 (not visible in console, only accessible via PITR)
# - Deleted when instance is deleted (unless you take a final snapshot)

# Modify backup settings
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --backup-retention-period 14 \
  --preferred-backup-window "02:00-02:30" \
  --apply-immediately

# Manual snapshots — persist indefinitely, survive instance deletion
aws rds create-db-snapshot \
  --db-instance-identifier myapp-prod-db \
  --db-snapshot-identifier myapp-prod-db-before-migration-2024-01

# Wait for snapshot to complete
aws rds wait db-snapshot-available \
  --db-snapshot-identifier myapp-prod-db-before-migration-2024-01

# List snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier myapp-prod-db \
  --query "DBSnapshots[*].{ID:DBSnapshotIdentifier,Status:Status,Created:SnapshotCreateTime,Size:AllocatedStorage}"

# Point-in-time recovery — restore to any second within retention window
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier myapp-prod-db \
  --target-db-instance-identifier myapp-prod-db-restored \
  --restore-time 2024-01-15T03:30:00Z \
  --db-instance-class db.m6g.large

# Restore from snapshot to a new instance
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier myapp-prod-db-clone \
  --db-snapshot-identifier myapp-prod-db-before-migration-2024-01 \
  --db-instance-class db.m6g.large

Multi-AZ & Read Replicas

# Multi-AZ (standard RDS):
# - Synchronous replication to standby in different AZ
# - Standby is NOT readable (unlike Aurora)
# - Automatic failover on primary failure (60-120 sec; DNS CNAME flips)
# - Enable for production — costs 2x (you pay for the standby)
# - Maintenance and backups happen on standby first (less impact on primary)

# Enable Multi-AZ on existing instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --multi-az \
  --apply-immediately

# Trigger a failover (for testing, maintenance)
aws rds reboot-db-instance \
  --db-instance-identifier myapp-prod-db \
  --force-failover

# Read Replicas — asynchronous replication; replicas are readable
# Use to: offload read-heavy workloads, analytics queries, reporting
# Up to 15 replicas for Aurora, 5 for standard RDS

# Create a read replica
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-prod-db-replica-1 \
  --source-db-instance-identifier myapp-prod-db \
  --db-instance-class db.r6g.xlarge \
  --availability-zone us-east-1b

# Create cross-region read replica (for DR)
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-prod-db-dr-replica \
  --source-db-instance-identifier arn:aws:rds:us-east-1:123456789012:db:myapp-prod-db \
  --db-instance-class db.m6g.large \
  --region us-west-2

# Promote read replica to standalone (for failover / migration)
aws rds promote-read-replica \
  --db-instance-identifier myapp-prod-db-replica-1
# After promotion: replica becomes an independent primary
# Update your app to point to new instance endpoint

Aurora Serverless v2

# Aurora Serverless v2 — capacity scales continuously in 0.5 ACU increments
# ACU (Aurora Capacity Unit) ≈ 2 GB RAM + proportional CPU
# Min: 0.5 ACU ($0.06/ACU-hr), Max: 128 ACU
# Scales within seconds — suitable for variable/unpredictable workloads
# NOTE: v2 does NOT scale to 0 (use v1 for true zero-cost idle workloads)

# Create Aurora Serverless v2 cluster
aws rds create-db-cluster \
  --db-cluster-identifier myapp-aurora-cluster \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --master-username dbadmin \
  --master-user-password "$(openssl rand -base64 20)" \
  --serverless-v2-scaling-configuration "MinCapacity=0.5,MaxCapacity=16" \
  --db-subnet-group-name myapp-db-subnet-group \
  --vpc-security-group-ids sg-0db123

# Add a Serverless v2 writer instance to the cluster
aws rds create-db-instance \
  --db-instance-identifier myapp-aurora-writer \
  --db-cluster-identifier myapp-aurora-cluster \
  --db-instance-class db.serverless \
  --engine aurora-postgresql

# Aurora storage: shared cluster volume, replicated 6 ways across 3 AZs
# Data survives even if all Aurora instances are deleted (unlike standard RDS)
# Aurora I/O-Optimized: no I/O charges — better for I/O-intensive workloads > 25% utilization

# Aurora Global Database — active-write primary, low-latency replicas in up to 5 regions
# Sub-second replication lag; RPO ≈ 1s, RTO ≈ 1min (managed failover)
aws rds create-global-cluster \
  --global-cluster-identifier myapp-global \
  --source-db-cluster-identifier arn:aws:rds:us-east-1:123:cluster:myapp-aurora-cluster

RDS Security & Connections

RDS security relies on VPC isolation, security groups, IAM database authentication, and Secrets Manager for credentials. RDS Proxy solves connection pooling at scale — especially critical for Lambda + RDS architectures.

IAM Authentication & Secrets Manager

# IAM database authentication — authenticate with an IAM token instead of a password
# Supported for: MySQL and PostgreSQL
# Token is valid for 15 minutes, generated using AWS credentials

# Enable IAM auth on instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --enable-iam-database-authentication \
  --apply-immediately

# Create a DB user for IAM auth (PostgreSQL example)
# (Connect with master user first)
CREATE USER iam_app;
GRANT rds_iam TO iam_app;
GRANT CONNECT ON DATABASE myapp TO iam_app;
GRANT USAGE ON SCHEMA public TO iam_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO iam_app;

# Generate auth token from CLI
TOKEN=$(aws rds generate-db-auth-token \
  --hostname myapp-prod-db.xxx.us-east-1.rds.amazonaws.com \
  --port 5432 \
  --username iam_app \
  --region us-east-1)

# Connect using the token as password
PGPASSWORD="$TOKEN" psql \
  "host=myapp-prod-db.xxx.us-east-1.rds.amazonaws.com \
   user=iam_app dbname=myapp sslmode=verify-full \
   sslrootcert=us-east-1-bundle.pem"

# Secrets Manager — store and auto-rotate DB credentials
# Create a secret for RDS
aws secretsmanager create-secret \
  --name prod/myapp/postgres \
  --description "RDS credentials for myapp prod" \
  --secret-string '{"username":"dbadmin","password":"mysecretpw","host":"myapp-prod-db.xxx.rds.amazonaws.com","port":5432,"dbname":"myapp"}'

# Enable automatic rotation (every 30 days)
aws secretsmanager rotate-secret \
  --secret-id prod/myapp/postgres \
  --rotation-lambda-arn arn:aws:lambda:us-east-1:123:function:SecretsManagerRDSPostgreSQLRotationSingleUser \
  --rotation-rules AutomaticallyAfterDays=30

# Retrieve secret in application code
aws secretsmanager get-secret-value \
  --secret-id prod/myapp/postgres \
  --query SecretString --output text | jq -r .password

SSL/TLS & Encryption

# Encryption at rest — enable at creation time (cannot enable on existing instance)
# Uses KMS key; encrypts storage, backups, read replicas, snapshots
# To encrypt an existing unencrypted instance:
# 1. Take snapshot, 2. Copy snapshot with encryption, 3. Restore from encrypted snapshot

# Create encrypted copy of snapshot
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier myapp-unencrypted-snapshot \
  --target-db-snapshot-identifier myapp-encrypted-snapshot \
  --kms-key-id arn:aws:kms:us-east-1:123:key/mrk-abc123

# SSL/TLS in transit — RDS supports SSL connections for all engines
# Download AWS RDS CA bundle
wget https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem

# Force SSL for all PostgreSQL connections (via parameter group)
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=immediate"

# Connect with SSL (psql)
psql "host=myapp-prod-db.xxx.us-east-1.rds.amazonaws.com \
      user=dbadmin dbname=myapp \
      sslmode=verify-full sslrootcert=us-east-1-bundle.pem"

# Node.js connection with SSL
# const pool = new Pool({
#   host: process.env.DB_HOST,
#   ssl: {
#     ca: fs.readFileSync("us-east-1-bundle.pem").toString(),
#     rejectUnauthorized: true,
#   },
# });

# Security groups for RDS:
# Only allow traffic from application tier security group, not 0.0.0.0/0
aws ec2 authorize-security-group-ingress \
  --group-id sg-0db123 \
  --protocol tcp \
  --port 5432 \
  --source-group sg-app456   # Only allow from application server SG

RDS Proxy

# RDS Proxy — fully managed connection pooler
# Critical for Lambda: Lambda can scale to hundreds of concurrent instances,
# each opening DB connections. Without a proxy, this exhausts max_connections.
# RDS Proxy maintains a pool and multiplexes Lambda connections into fewer DB connections.

# Create an RDS Proxy
aws rds create-db-proxy \
  --db-proxy-name myapp-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:us-east-1:123:secret:prod/myapp/postgres","IAMAuth":"REQUIRED"}]' \
  --role-arn arn:aws:iam::123:role/rds-proxy-role \
  --vpc-subnet-ids subnet-private-aaa111 subnet-private-bbb222 \
  --vpc-security-group-ids sg-proxy123

# Register DB instance with the proxy
aws rds register-db-proxy-targets \
  --db-proxy-name myapp-proxy \
  --db-instance-identifiers myapp-prod-db

# Get proxy endpoint (use this in your app instead of the instance endpoint)
aws rds describe-db-proxies \
  --db-proxy-name myapp-proxy \
  --query "DBProxies[0].Endpoint"

# RDS Proxy IAM auth — connect from Lambda without password (uses IAM token)
# Lambda IAM role must have: rds-db:connect permission on the proxy
# {
#   "Effect": "Allow",
#   "Action": "rds-db:connect",
#   "Resource": "arn:aws:rds-db:us-east-1:123:dbuser:prx-abc123/iam_app"
# }

# RDS Proxy benefits:
# - Connection pooling: reduces DB connections from O(lambda-instances) to O(small-pool)
# - Failover: maintains connections during Multi-AZ failover (app sees <1s interruption vs 30-120s)
# - IAM auth passthrough to DB
# - Secrets Manager rotation without app restart

RDS Performance & Monitoring

RDS Performance Insights, Enhanced Monitoring, slow query logs, and CloudWatch metrics together give a complete picture of database health. Knowing which knobs to turn — from storage autoscaling to index recommendations — separates reactive from proactive operations.

Performance Insights & Enhanced Monitoring

# Performance Insights — visualize database load, identify top SQL
# Shows DB Load (in vCPUs) broken down by wait events, SQL, users, hosts
# Free tier: 7 days retention; paid: up to 2 years
# Enable on existing instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --apply-immediately

# Query Performance Insights API for top SQL (last 1 hour)
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-ABC123DEFGHIJKLMNOPQRST \
  --start-time $(date -u -d "1 hour ago" +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 60 \
  --metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.sql","Limit":10}}]'

# Enhanced Monitoring — OS-level metrics at 1-60 second granularity
# Metrics: CPU steal, file system I/O, process list — not available in CloudWatch
# Requires an IAM role with the AmazonRDSEnhancedMonitoringRole managed policy
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --monitoring-interval 15 \
  --monitoring-role-arn arn:aws:iam::123:role/rds-enhanced-monitoring-role

# Metrics appear in CloudWatch under RDS → Per-Second Metrics
# View in Logs Insights: log group /aws/rds/instance/<id>/osmetrics

CloudWatch Metrics & Slow Query Log

# Key CloudWatch metrics for RDS (namespace: AWS/RDS)
# CPUUtilization          — % CPU; alert > 80% for sustained period
# DatabaseConnections     — current open connections; alert if approaching max_connections
# FreeStorageSpace        — bytes; alert < 10% of allocated storage
# FreeableMemory          — bytes; low memory causes paging and performance degradation
# ReadIOPS / WriteIOPS    — I/O operations per second
# ReadLatency/WriteLatency — avg per-operation latency in seconds; alert > 20ms
# ReplicaLag              — seconds behind primary (for read replicas)
# BurstBalance            — % of gp2 I/O burst credits remaining (gp3 has fixed baseline)

# Set CloudWatch alarm for low storage
aws cloudwatch put-metric-alarm \
  --alarm-name "rds-low-storage-myapp" \
  --alarm-description "Free storage below 10 GB" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --statistic Average \
  --dimensions "Name=DBInstanceIdentifier,Value=myapp-prod-db" \
  --period 300 \
  --threshold 10737418240 \
  --comparison-operator LessThanThreshold \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:us-east-1:123:ops-alerts

# Enable slow query log (PostgreSQL via parameter group)
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --parameters \
    "ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=none,ApplyMethod=immediate" \
    "ParameterName=log_lock_waits,ParameterValue=1,ApplyMethod=immediate"

# View slow query logs
aws rds describe-db-log-files \
  --db-instance-identifier myapp-prod-db \
  --filename-contains "postgresql"

aws rds download-db-log-file-portion \
  --db-instance-identifier myapp-prod-db \
  --log-file-name error/postgresql.log.2024-01-15.15 \
  --starting-token 0 \
  --output text

Storage Autoscaling & Parameter Tuning

# Storage autoscaling — automatically increases storage when free space is low
# Triggers when: free space < 10% of allocated AND < 5 GB AND low for 5 min
aws rds modify-db-instance \
  --db-instance-identifier myapp-prod-db \
  --max-allocated-storage 1000 \
  --apply-immediately
# Set max-allocated-storage to cap runaway scaling costs

# Key PostgreSQL parameter tuning (rule of thumb values)
# shared_buffers        = 25% of instance RAM (cache frequently accessed data)
# effective_cache_size  = 75% of instance RAM (hint for query planner)
# work_mem              = RAM / (max_connections * 4) (per-sort/hash operation)
# maintenance_work_mem  = 10% of RAM (for VACUUM, CREATE INDEX)
# max_connections       = depends on app; use RDS Proxy to keep this low (100-200)
# wal_buffers           = 16MB (WAL write buffer)
# checkpoint_completion_target = 0.9 (spread checkpoint writes over 90% of checkpoint interval)
# random_page_cost      = 1.1 for SSDs (default 4.0 is for HDDs)

# Check current parameter values
aws rds describe-db-parameters \
  --db-parameter-group-name myapp-postgres16-params \
  --query "Parameters[?ParameterName=='max_connections' || ParameterName=='shared_buffers']"

# Index recommendations — use pg_stat_user_tables to find missing indexes
# (Run directly in PostgreSQL)
# SELECT schemaname, tablename, seq_scan, idx_scan,
#        seq_tup_read, idx_tup_fetch
# FROM pg_stat_user_tables
# WHERE seq_scan > idx_scan
# ORDER BY seq_tup_read DESC
# LIMIT 20;

# Find slow queries in pg_stat_statements
# SELECT query, calls, total_exec_time/calls AS avg_ms,
#        rows/calls AS avg_rows
# FROM pg_stat_statements
# ORDER BY avg_ms DESC
# LIMIT 20;

# VACUUM and ANALYZE (PostgreSQL maintenance)
# RDS runs autovacuum — check its status:
# SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
# FROM pg_stat_user_tables
# ORDER BY n_dead_tup DESC;

AWS RDS: Fundamentals & Supported Engines

Amazon RDS (Relational Database Service) is a managed relational database service. AWS handles provisioning, patching, backups, and failover. You focus on schema, queries, and application logic.

RDS vs Self-Managed on EC2

• RDS: automated backups, Multi-AZ, read replicas, patching, monitoring — you give up OS access

• EC2 self-managed: full control (custom extensions, OS tuning), but you own all ops work

• RDS vs Aurora: Aurora is AWS-proprietary, MySQL/PostgreSQL-compatible, 5x faster than MySQL, auto-scales storage, costs more

• Choose RDS when you need standard PostgreSQL/MySQL features without Aurora pricing

Supported Engines

• PostgreSQL 11–17 (most feature-rich, recommended for new projects)

• MySQL 8.0 / MariaDB 10.6+ (popular, wide tooling support)

• Oracle — bring your own license or license-included (expensive)

• Microsoft SQL Server — Standard/Enterprise editions, Windows Auth support

• Db2 — IBM database, added 2023

Instance Classes

Family     | vCPU | RAM    | Use Case
-----------|------|--------|---------------------------
db.t3/t4g  | 2-8  | 1-32GB | Dev/test, burstable (cheap)
db.m6g/m7g | 2-96 | 8-384GB| General purpose production
db.r6g/r7g | 2-96 | 16-768GB| Memory-intensive (large caches)
db.x2g     | 4-128| 61-980GB| SAP, very large in-memory workloads

Storage Types

• gp3 (General Purpose SSD): baseline 3000 IOPS + 125 MB/s, configurable up to 16000 IOPS — default choice

• io1/io2 (Provisioned IOPS): up to 64000 IOPS, for OLTP with consistent low latency

• Storage autoscaling: set max threshold, RDS expands automatically when 10% free

Creating an RDS Instance

# Create PostgreSQL instance
aws rds create-db-instance \
  --db-instance-identifier my-postgres \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 16.1 \
  --master-username admin \
  --master-user-password "SecureP@ssw0rd" \
  --allocated-storage 100 \
  --storage-type gp3 \
  --db-subnet-group-name my-db-subnet-group \
  --vpc-security-group-ids sg-abc123 \
  --backup-retention-period 7 \
  --multi-az \
  --storage-encrypted \
  --no-publicly-accessible

# Wait for it to be available
aws rds wait db-instance-available --db-instance-identifier my-postgres

# Get endpoint
aws rds describe-db-instances \
  --db-instance-identifier my-postgres \
  --query 'DBInstances[0].Endpoint'

Connecting

# PostgreSQL
psql -h my-postgres.abc123.eu-west-1.rds.amazonaws.com \
  -U admin -d mydb -p 5432

# MySQL
mysql -h my-mysql.abc123.eu-west-1.rds.amazonaws.com \
  -u admin -p --port 3306 mydb

# From application (connection string)
# postgresql://admin:password@host:5432/mydb
# mysql://admin:password@host:3306/mydb

Parameter Groups & Option Groups

# Create custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name my-pg16-params \
  --db-parameter-group-family postgres16 \
  --description "Custom PostgreSQL 16 parameters"

# Tune key parameters
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-pg16-params \
  --parameters \
    "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory/4},ApplyMethod=pending-reboot" \
    "ParameterName=max_connections,ParameterValue=200,ApplyMethod=pending-reboot" \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate"

# Apply to instance
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --db-parameter-group-name my-pg16-params \
  --apply-immediately

RDS: Multi-AZ, Read Replicas & Scaling

Multi-AZ Deployment

Multi-AZ maintains a synchronous standby replica in a different Availability Zone. Failover happens automatically in 60–120 seconds when AWS detects an issue. The standby is not readable — it exists only for HA.

• Synchronous replication: every write is confirmed on standby before ACK to application

• Automatic failover: DNS endpoint switches to standby — application just reconnects

• Failover triggers: primary AZ outage, primary instance failure, manual failover, OS patching

• RTO: typically 60–120 seconds; RPO: 0 (synchronous replication)

• Multi-AZ does NOT improve read performance — use Read Replicas for that

# Enable Multi-AZ on existing instance
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --multi-az \
  --apply-immediately   # or --no-apply-immediately for next maintenance window

# Manually trigger failover (for testing)
aws rds reboot-db-instance \
  --db-instance-identifier my-postgres \
  --force-failover

Multi-AZ DB Cluster (newer option)

Multi-AZ DB Cluster uses 3 DB instances across 3 AZs with semi-synchronous replication. One writer, two readers. Readers ARE accessible for reads. Faster failover than classic Multi-AZ (~35 seconds). Available for PostgreSQL and MySQL.

Read Replicas

Read replicas use asynchronous replication. They are readable, reducing load on the primary. You can have up to 15 replicas (PostgreSQL). Promote a replica to a standalone instance in disaster scenarios.

# Create read replica (same region)
aws rds create-db-instance-read-replica \
  --db-instance-identifier my-postgres-replica-1 \
  --source-db-instance-identifier my-postgres \
  --db-instance-class db.t3.medium

# Cross-region read replica
aws rds create-db-instance-read-replica \
  --db-instance-identifier my-postgres-us \
  --source-db-instance-identifier arn:aws:rds:eu-west-1:123456789012:db:my-postgres \
  --db-instance-class db.t3.medium \
  --region us-east-1

# Promote replica to standalone (for disaster recovery or migration)
aws rds promote-read-replica \
  --db-instance-identifier my-postgres-replica-1

# Monitor replication lag
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name ReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=my-postgres-replica-1 \
  --start-time 2024-01-01T00:00:00Z \
  --end-time 2024-01-01T01:00:00Z \
  --period 60 \
  --statistics Average

RDS Proxy

RDS Proxy sits between your application and RDS. It pools and reuses database connections, dramatically reducing connection overhead for Lambda functions and high-concurrency apps. Supports IAM auth and Secrets Manager.

• Reduces connection overhead: Lambda can open thousands of concurrent "connections" — Proxy multiplexes them

• Faster failover: Proxy maintains warm connections to standby, reduces failover impact on app

• IAM database authentication: authenticate with IAM token instead of password

• Automatic secrets rotation: works with Secrets Manager, no app restarts needed

# Create RDS Proxy
aws rds create-db-proxy \
  --db-proxy-name my-postgres-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:eu-west-1:123:secret:rds-creds","IAMAuth":"REQUIRED"}]' \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-abc subnet-def \
  --vpc-security-group-ids sg-proxy

# Application uses proxy endpoint instead of RDS endpoint
# proxy-endpoint: my-postgres-proxy.proxy-abc.eu-west-1.rds.amazonaws.com

Vertical Scaling

# Change instance class (requires brief downtime, use maintenance window)
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --db-instance-class db.m6g.large \
  --apply-immediately   # or schedule for maintenance window

Blue/Green Deployments

Blue/Green creates a staging environment (Green) with your changes (new engine version, parameter changes). Traffic switches instantly. Rollback is just switching back. Zero-downtime upgrades for minor versions; brief cutover for major.

# Create Blue/Green deployment
aws rds create-blue-green-deployment \
  --blue-green-deployment-name my-postgres-upgrade \
  --source arn:aws:rds:eu-west-1:123456789012:db:my-postgres \
  --target-engine-version 16.1

# After testing, switch over
aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier bgd-abc123

RDS: Security, Backup & Monitoring

Network Security

# RDS should be in private subnets — never publicly accessible
# DB subnet group spans multiple AZs for Multi-AZ support
aws rds create-db-subnet-group \
  --db-subnet-group-name my-db-subnets \
  --db-subnet-group-description "Private DB subnets" \
  --subnet-ids subnet-private-1a subnet-private-1b subnet-private-1c

# Security group: allow only app servers (or specific CIDR)
aws ec2 create-security-group \
  --group-name rds-sg \
  --description "RDS access"

aws ec2 authorize-security-group-ingress \
  --group-id sg-rds123 \
  --protocol tcp \
  --port 5432 \
  --source-group sg-app123   # App server SG only

Encryption at Rest & In Transit

• Encryption at rest: enabled at creation (KMS key), cannot be added to existing instance

• Workaround for adding encryption: snapshot → encrypted copy → restore from encrypted snapshot

• In transit: SSL/TLS required via rds.force_ssl=1 (MySQL) or ssl=true connection param

• KMS: use custom CMK for compliance (vs AWS-managed key)

# Enforce SSL in PostgreSQL via parameter group
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-pg-params \
  --parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=immediate"

# Connection with SSL (PostgreSQL)
psql "host=my-postgres.abc.eu-west-1.rds.amazonaws.com \
  dbname=mydb user=admin sslmode=require \
  sslrootcert=global-bundle.pem"

IAM Database Authentication

With IAM authentication, you authenticate using an IAM token instead of a password. Tokens expire after 15 minutes. Supported for MySQL and PostgreSQL.

# Enable IAM auth on instance
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --enable-iam-database-authentication \
  --apply-immediately

# Create IAM user in DB (PostgreSQL)
# GRANT rds_iam TO myapp_user;

# Generate auth token (valid 15 min)
TOKEN=$(aws rds generate-db-auth-token \
  --hostname my-postgres.abc.eu-west-1.rds.amazonaws.com \
  --port 5432 \
  --region eu-west-1 \
  --username myapp_user)

# Connect using token as password
PGPASSWORD=$TOKEN psql -h my-postgres.abc.eu-west-1.rds.amazonaws.com \
  -U myapp_user -d mydb --ssl

Secrets Manager Integration

# Create secret for RDS credentials
aws secretsmanager create-secret \
  --name rds/my-postgres/master \
  --secret-string '{"username":"admin","password":"SecureP@ss","host":"my-postgres.abc.eu-west-1.rds.amazonaws.com","port":5432,"dbname":"mydb"}'

# Enable automatic rotation (Lambda rotates password every 30 days)
aws secretsmanager rotate-secret \
  --secret-id rds/my-postgres/master \
  --rotation-rules AutomaticallyAfterDays=30

Automated Backups & Snapshots

# Automated backups: retention 1-35 days, daily backup window
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --backup-retention-period 14 \
  --preferred-backup-window "02:00-03:00"

# Point-in-time restore (within retention window)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier my-postgres \
  --target-db-instance-identifier my-postgres-restored \
  --restore-time 2024-01-15T10:30:00Z

# Manual snapshot (persists until deleted)
aws rds create-db-snapshot \
  --db-instance-identifier my-postgres \
  --db-snapshot-identifier my-postgres-before-migration

# Restore from snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier my-postgres-restored \
  --db-snapshot-identifier my-postgres-before-migration

# Copy snapshot cross-region
aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:eu-west-1:123:snapshot:my-snap \
  --target-db-snapshot-identifier my-snap-us-copy \
  --region us-east-1

CloudWatch Metrics & Alarms

# Key metrics to monitor:
# CPUUtilization      — alert >80% sustained
# FreeStorageSpace    — alert <20% of allocated
# DatabaseConnections — alert approaching max_connections
# ReadIOPS/WriteIOPS  — watch for I/O saturation
# ReadLatency/WriteLatency — p99 latency
# ReplicaLag         — for read replicas

# Create alarm for low free storage
aws cloudwatch put-metric-alarm \
  --alarm-name rds-low-storage \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=my-postgres \
  --statistic Average \
  --period 300 \
  --threshold 5368709120 \
  --comparison-operator LessThanThreshold \
  --evaluation-periods 2 \
  --alarm-actions arn:aws:sns:eu-west-1:123:my-alerts

Performance Insights

• Performance Insights: query-level visibility into DB load. Free tier available.

• Shows top SQL by load (db.load.avg), wait events, and active sessions

• Retention: 7 days free, up to 2 years with paid tier

• Enhanced Monitoring: OS-level metrics (memory/CPU/disk/processes) at 1-60 second granularity

# Enable Performance Insights
aws rds modify-db-instance \
  --db-instance-identifier my-postgres \
  --enable-performance-insights \
  --performance-insights-retention-period 7