Amazon Relational Database Service (RDS) manages database administration — provisioning, patching, backups, and failover — so you focus on schema design and queries. RDS supports PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and Amazon Aurora.

Why RDS Over Self-Managed DB on EC2?

Task Self-Managed EC2 RDS
OS/database patching You AWS (maintenance windows)
Automated backups You configure Daily snapshots, point-in-time recovery
Multi-AZ failover You build Automatic (~60 seconds)
Read scaling Manual replication Read replicas in clicks
Monitoring Install agents Enhanced Monitoring + Performance Insights

Create a PostgreSQL Instance 

  aws rds create-db-instance \
    --db-instance-identifier myapp-postgres \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --engine-version 16.1 \
    --master-username dbadmin \
    --master-user-password 'ChangeMeInProduction!' \
    --allocated-storage 20 \
    --storage-type gp3 \
    --vpc-security-group-ids sg-database \
    --db-subnet-group-name my-db-subnet-group \
    --backup-retention-period 7 \
    --multi-az \
    --storage-encrypted \
    --tags Key=Environment,Value=production

Console: RDS → Create database → PostgreSQL → Free tier or Production template → Configure VPC, security group, and credentials.

Connect to RDS

RDS instances in private subnets are not publicly accessible (recommended):

  # From EC2 in same VPC
psql -h myapp-postgres.xxxx.us-east-1.rds.amazonaws.com \
     -U dbadmin -d postgres

# Connection string for applications
DATABASE_URL=postgresql://dbadmin:[email protected]:5432/myapp

Use Secrets Manager to rotate credentials automatically instead of hardcoding passwords.

Engine Comparison

Engine Best For RDS Feature Notes
PostgreSQL Modern apps, JSON, extensions Most popular open-source choice
MySQL/MariaDB WordPress, legacy LAMP Wide compatibility
Aurora PostgreSQL/MySQL High throughput, auto-scaling storage Up to 15 read replicas, serverless option
SQL Server .NET enterprise apps License included or BYOL
Oracle Enterprise Oracle workloads BYOL required

Multi-AZ vs Read Replicas

Feature Multi-AZ Read Replica
Purpose High availability Read scaling
Standby Synchronous, automatic failover Asynchronous, manual promotion
Endpoint Same writer endpoint Separate read endpoint
Use case Production HA Analytics, reporting queries 
  # Create read replica
aws rds create-db-instance-read-replica \
    --db-instance-identifier myapp-postgres-replica \
    --source-db-instance-identifier myapp-postgres \
    --db-instance-class db.t3.micro

Backups and Recovery

RDS performs automated daily snapshots and maintains transaction logs for point-in-time recovery (PITR):

  # Manual snapshot
aws rds create-db-snapshot \
    --db-instance-identifier myapp-postgres \
    --db-snapshot-identifier myapp-postgres-manual-2024-06-13

# Restore to point in time
aws rds restore-db-instance-to-point-in-time \
    --source-db-instance-identifier myapp-postgres \
    --target-db-instance-identifier myapp-postgres-restored \
    --restore-time 2024-06-13T10:30:00Z

Set backup-retention-period to 7–35 days based on compliance requirements.

Parameter Groups

Tune database settings without editing config files directly:

  aws rds create-db-parameter-group \
    --db-parameter-group-name postgres16-custom \
    --db-parameter-group-family postgres16 \
    --description "Custom PostgreSQL 16 parameters"

aws rds modify-db-parameter-group \
    --db-parameter-group-name postgres16-custom \
    --parameters "ParameterName=max_connections,ParameterValue=200,ApplyMethod=pending-reboot"

aws rds modify-db-instance \
    --db-instance-identifier myapp-postgres \
    --db-parameter-group-name postgres16-custom \
    --apply-immediately

Common tuning parameters:

  • PostgreSQL: shared_buffers, work_mem, max_connections
  • MySQL: innodb_buffer_pool_size, max_connections

Performance Insights and Monitoring 

  # Enable Performance Insights (free tier: 7 days retention)
aws rds modify-db-instance \
    --db-instance-identifier myapp-postgres \
    --enable-performance-insights \
    --performance-insights-retention-period 7

Key CloudWatch metrics:

  • CPUUtilization — alert > 80%
  • FreeableMemory — alert < 256 MB
  • DatabaseConnections — compare to max_connections
  • ReadLatency / WriteLatency — alert on spikes

Security 

  # Security group — allow only app tier
aws ec2 authorize-security-group-ingress \
    --group-id sg-database \
    --protocol tcp --port 5432 \
    --source-group sg-app-servers

# Encryption at rest (enable at creation — cannot enable on existing unencrypted)
# Encryption in transit — require SSL in parameter group
# rds.force_ssl = 1 (PostgreSQL)

Store credentials in AWS Secrets Manager:

  aws secretsmanager create-secret \
    --name prod/myapp/database \
    --secret-string '{"username":"dbadmin","password":"...","host":"myapp-postgres.xxxx.rds.amazonaws.com","port":5432,"dbname":"myapp"}'

Real-World Scenario: E-Commerce Database

Component Configuration
Primary db.r6g.large, Multi-AZ, PostgreSQL 16
Read replica 1× db.r6g.large for reporting dashboards
Backups 14-day retention, cross-region copy to us-west-2
Subnet Private subnets in 2 AZs via DB subnet group
Access App servers via SG; admins via bastion + SSM
Secrets Secrets Manager with 30-day rotation

Aurora vs Standard RDS

Feature RDS PostgreSQL Aurora PostgreSQL
Storage Fixed allocation, up to 64 TiB Auto-scales up to 128 TiB
Replicas Up to 5 Up to 15
Failover 60–120 seconds ~30 seconds
Cost Lower for small workloads Better $/IOP at scale
Serverless No Aurora Serverless v2

Common Mistakes

  1. Publicly accessible RDS — keep in private subnets
  2. db.t3.micro in production — insufficient CPU and connections under load
  3. No Multi-AZ for production — single AZ failure = downtime
  4. Default parameter group — tune for your workload
  5. Running migrations without snapshots — always snapshot before schema changes
  6. Ignoring connection pooling — use RDS Proxy or PgBouncer for serverless/Lambda

Troubleshooting

Issue Diagnosis Fix
Connection timeout SG, NACL, wrong subnet Verify SG allows app SG on DB port
too many connections App leaking connections Enable RDS Proxy; tune pool size
High CPU Missing indexes, slow queries Performance Insights → top SQL
Storage full No autoscaling Enable storage autoscaling (max threshold)
Failover took longer Large buffer cache flush Expected; test failover in maintenance window

Best Practices

  • Deploy in private subnets with Multi-AZ for production
  • Use gp3 storage with provisioned IOPS for predictable performance
  • Enable Enhanced Monitoring (1-second granularity)
  • Automate credential rotation with Secrets Manager
  • Use RDS Proxy for Lambda and connection-heavy apps
  • Test restore procedures quarterly — backups you can’t restore are worthless
  • Tag instances for cost allocation

Next: VPC — Networking.