AWS RDS — Managed Relational Database Complete Guide

Introduction

Why This Matters

AWS RDS removes the operational burden of database administration — patching, backups, replication, and failover — so teams can focus on application logic. It supports PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and Amazon Aurora. Understanding RDS configuration options, Multi-AZ vs Read Replicas, and the connection management model is critical for building databases that perform reliably under production workloads.

Creating RDS Instances

# Create a PostgreSQL RDS instance
aws rds create-db-instance \
  --db-instance-identifier myapp-db \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 16.1 \
  --master-username appuser \
  --master-user-password MySecurePassword123! \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-encrypted \
  --multi-az \
  --backup-retention-period 7 \
  --preferred-backup-window "02:00-03:00" \
  --preferred-maintenance-window "sun:04:00-sun:05:00" \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --db-subnet-group-name myapp-subnet-group \
  --deletion-protection \
  --tags Key=Environment,Value=production Key=Project,Value=myapp
 
# Create MySQL instance
aws rds create-db-instance \
  --db-instance-identifier myapp-mysql \
  --db-instance-class db.m7g.large \
  --engine mysql \
  --engine-version 8.0.35 \
  --master-username admin \
  --master-user-password SecurePassword123! \
  --allocated-storage 200 \
  --storage-type gp3
 
# Get connection endpoint
aws rds describe-db-instances \
  --db-instance-identifier myapp-db \
  --query 'DBInstances[0].Endpoint'

Subnet Groups and Security

# Create DB subnet group (spans multiple AZs)
aws rds create-db-subnet-group \
  --db-subnet-group-name myapp-subnet-group \
  --db-subnet-group-description "Subnet group for myapp databases" \
  --subnet-ids subnet-private-1a subnet-private-1b subnet-private-1c
 
# Security group allowing app servers to connect
aws ec2 authorize-security-group-ingress \
  --group-id sg-db \
  --protocol tcp \
  --port 5432 \
  --source-group sg-app
 
# Connect to RDS (from within VPC or via bastion host)
psql -h myapp-db.abc123.us-east-1.rds.amazonaws.com \
  -U appuser -d myapp

Multi-AZ and Read Replicas

# Enable Multi-AZ on existing instance (automatic failover)
aws rds modify-db-instance \
  --db-instance-identifier myapp-db \
  --multi-az \
  --apply-immediately
 
# Multi-AZ provides:
# - Synchronous replication to standby in another AZ
# - Automatic failover in 1-2 minutes if primary fails
# - Standby is NOT accessible for reads (only failover)
 
# Create Read Replica (for read scaling)
aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-db-read \
  --source-db-instance-identifier myapp-db \
  --db-instance-class db.t3.medium
 
# Read replicas provide:
# - Asynchronous replication from primary
# - Readable endpoint for SELECT queries
# - Can be promoted to standalone DB
# - Can be in same or different region

Backups and Restoration

# Automated backups are enabled by default (1-35 day retention)
# Point-in-time recovery available within retention window
 
# Create manual snapshot
aws rds create-db-snapshot \
  --db-instance-identifier myapp-db \
  --db-snapshot-identifier myapp-db-snapshot-v1
 
# List snapshots
aws rds describe-db-snapshots \
  --db-instance-identifier myapp-db \
  --query 'DBSnapshots[*].[DBSnapshotIdentifier,SnapshotCreateTime,Status]' \
  --output table
 
# Restore from snapshot to new instance
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier myapp-db-restored \
  --db-snapshot-identifier myapp-db-snapshot-v1 \
  --db-instance-class db.t3.medium
 
# Point-in-time restore (within backup retention window)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier myapp-db \
  --target-db-instance-identifier myapp-db-pitr \
  --restore-time 2024-01-15T12:00:00Z

Parameter Groups

# Create custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name myapp-pg16 \
  --db-parameter-group-family postgres16 \
  --description "Custom parameters for myapp"
 
# Tune PostgreSQL parameters
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-pg16 \
  --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' \
    'ParameterName=log_slow_admin_statements,ParameterValue=1,ApplyMethod=immediate'
 
# Apply parameter group to instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-db \
  --db-parameter-group-name myapp-pg16

Connection Pooling with RDS Proxy

# Create RDS Proxy to manage connection pooling
aws rds create-db-proxy \
  --db-proxy-name myapp-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:...","IAMAuth":"DISABLED"}]' \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-1 subnet-2 subnet-3 \
  --vpc-security-group-ids sg-proxy
 
# Connect application to proxy endpoint instead of DB endpoint
# RDS Proxy benefits:
# - Reduces DB connections (Lambda serverless use case)
# - Faster failover (connection reuse)
# - IAM authentication support

Common Mistakes

Not enabling Multi-AZ for production databases — a single-AZ RDS instance is a single point of failure
Placing RDS instances in public subnets — always use private subnets with security groups limiting access
Using the master user for application connections — create a dedicated application user with minimum required permissions
Not monitoring DatabaseConnections metric — connection exhaustion causes application errors that look like database failures
Disabling automated backups — without them, point-in-time recovery is impossible

Best Practices

Use RDS Proxy for serverless (Lambda) workloads to prevent connection exhaustion
Enable Enhanced Monitoring (1-second granularity) for detailed OS-level metrics
Enable Performance Insights to identify slow queries and wait events
Use gp3 storage type — it provides better price/performance than gp2 and allows independent IOPS scaling
Store database credentials in AWS Secrets Manager and rotate them automatically
Test failover regularly by rebooting with failover to validate Multi-AZ behavior

Key Takeaways

RDS automates patching, backups, replication, and failover — the managed overhead reduction justifies the cost premium over self-hosted databases
Multi-AZ provides synchronous replication and automatic failover in 1-2 minutes — the standby is not readable
Read Replicas use asynchronous replication and are readable — they scale read throughput but may lag behind the primary
Point-in-time recovery allows restoring to any second within the backup retention window (up to 35 days)
Parameter groups customize database engine settings — changes to static parameters require a reboot to take effect
RDS Proxy pools and multiplexes connections — essential for Lambda and microservices with high connection churn
Always place RDS in private subnets and use security groups to allow access only from application security groups
Performance Insights provides a graphical view of database load, wait events, and top SQL statements at no additional cost for most instance classes

Introduction#

Why This Matters#

Creating RDS Instances#

Subnet Groups and Security#

Multi-AZ and Read Replicas#

Backups and Restoration#

Parameter Groups#

Connection Pooling with RDS Proxy#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading