AWS RDS: MySQL, PostgreSQL, Aurora — Setup and Best Practices (2026)

Amazon RDS (Relational Database Service) removes the heavy lifting of managing database servers — provisioning, patching, backups and failover are handled by AWS. This guide covers everything from choosing the right RDS engine to production-grade Multi-AZ, Read Replicas, RDS Proxy and Aurora Serverless v2 configurations.

1. Engine Selection

RDS supports six engines. Pick based on workload:

2. Creating an RDS Instance

Use the AWS CLI to create a production-ready PostgreSQL instance:

aws rds create-db-instance \
  --db-instance-identifier myapp-prod \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 16.2 \
  --master-username adminuser \
  --master-user-password "$(aws secretsmanager get-secret-value \
      --secret-id myapp/db/password --query SecretString --output text)" \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-encrypted \
  --vpc-security-group-ids sg-0abc1234 \
  --db-subnet-group-name myapp-subnet-group \
  --backup-retention-period 7 \
  --no-publicly-accessible \
  --multi-az \
  --deletion-protection \
  --tags Key=Environment,Value=production

Key parameters explained:

• storage-type gp3: Newer generation, cheaper than gp2, allows independent IOPS tuning
• storage-encrypted: Always enable — uses KMS, no performance impact
• multi-az: Synchronous standby in another AZ — essential for production
• deletion-protection: Prevents accidental deletion
• no-publicly-accessible: Never expose RDS to the internet

3. Multi-AZ vs Read Replicas

These two features serve different purposes and are often confused:

Creating a Read Replica:

aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-read-replica-1 \
  --source-db-instance-identifier myapp-prod \
  --db-instance-class db.t3.medium \
  --availability-zone us-east-1b

4. Amazon Aurora

Aurora is AWS's cloud-native relational database — compatible with MySQL and PostgreSQL but built on a distributed storage system that automatically grows in 10 GB increments up to 128 TB.

Aurora vs Standard RDS

• Storage: Aurora uses a distributed log-structured storage with 6 copies across 3 AZs. Standard RDS uses EBS attached to a single instance.
• Replicas: Aurora supports up to 15 read replicas with sub-10ms replication lag (vs ~seconds for RDS)
• Failover: Aurora promotes a read replica in ~30 seconds vs 60–120 seconds for Multi-AZ RDS

Aurora Serverless v2

Serverless v2 scales capacity in fine-grained increments (0.5 ACU steps) within seconds, handling unpredictable traffic without pre-provisioning:

aws rds create-db-cluster \
  --db-cluster-identifier myapp-aurora \
  --engine aurora-postgresql \
  --engine-version 15.4 \
  --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16 \
  --master-username adminuser \
  --manage-master-user-password \
  --storage-encrypted \
  --vpc-security-group-ids sg-0abc1234 \
  --db-subnet-group-name myapp-subnet-group

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

5. RDS Proxy

Lambda functions and containerised workloads open and close database connections frequently — this overwhelms RDS connection limits. RDS Proxy pools and shares connections:

aws rds create-db-proxy \
  --db-proxy-name myapp-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:us-east-1:123456789:secret:myapp/db","IAMAuth":"REQUIRED"}]' \
  --role-arn arn:aws:iam::123456789:role/rds-proxy-role \
  --vpc-subnet-ids subnet-abc subnet-def \
  --vpc-security-group-ids sg-proxy123

6. Performance Insights

Performance Insights shows a real-time dashboard of database load, broken down by wait events, SQL queries, users and hosts. Enable it on any instance:

aws rds modify-db-instance \
  --db-instance-identifier myapp-prod \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --apply-immediately

Key metrics to watch in Performance Insights:

• db load: Should stay below your vCPU count most of the time
• Wait events: IO:DataFileRead = disk-bound, Lock:relation = locking contention, CPU = compute-bound queries
• Top SQL: Sort by "Load" not just execution time — a fast query running millions of times is worse than a slow query running once

7. Backups and Snapshots

RDS provides two backup mechanisms:

• Automated backups: Daily snapshots + transaction logs. Enable with --backup-retention-period 7 (up to 35 days). Enables point-in-time restore to any second within the retention window.
• Manual snapshots: Persist until you delete them. Take one before major schema changes or migrations.

# Create a manual snapshot before a risky migration
aws rds create-db-snapshot \
  --db-instance-identifier myapp-prod \
  --db-snapshot-identifier myapp-before-migration-20260605

# Restore to a point in time
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier myapp-prod \
  --target-db-instance-identifier myapp-restored \
  --restore-time 2026-06-04T10:30:00Z

8. Security Configuration

RDS security checklist:

• Never public: Set PubliclyAccessible: false. Access via bastion host, SSM Session Manager port-forward, or VPC-internal services only.
• Security groups: Allow port 5432/3306 only from your application's security group — not from 0.0.0.0/0
• Encryption at rest: Always enable KMS encryption at instance creation (cannot be enabled after)
• SSL in transit: Force SSL with a parameter group setting: rds.force_ssl = 1 (PostgreSQL) or require_secure_transport = ON (MySQL)
• Secrets Manager: Rotate database passwords automatically with the RDS-managed rotation Lambda

# Force SSL on PostgreSQL RDS via parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name myapp-pg-params \
  --db-parameter-group-family postgres16 \
  --description "Production PostgreSQL parameters"

aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-pg-params \
  --parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=immediate"

Frequently Asked Questions

What is the difference between Aurora and RDS PostgreSQL?

Aurora PostgreSQL uses a distributed storage engine with 6-way replication across 3 AZs built in. It delivers higher throughput, faster failover (~30s vs 60–120s), up to 15 low-latency read replicas, and Global Database support. Standard RDS PostgreSQL is simpler and cheaper for small workloads.

How do I reduce RDS costs?

Use Reserved Instances (1 or 3 year) for steady-state workloads (up to 60% savings), rightsize with CloudWatch metrics, use gp3 storage over gp2, enable Aurora Serverless v2 for variable workloads, and delete idle read replicas.

Can I use RDS with Lambda?

Yes, but always use RDS Proxy between Lambda and RDS. Lambda functions can scale to thousands of concurrent executions, each opening a new database connection. RDS Proxy pools those connections to prevent overwhelming the database's max_connections limit.

What happens during a Multi-AZ failover?

AWS detects primary failure, promotes the synchronous standby, and updates the DNS CNAME for your endpoint. Total time is typically 60–120 seconds. Applications must handle reconnection — ensure your JDBC/psycopg connection pool has retry logic and a short socket timeout.

How do I migrate from self-managed PostgreSQL to RDS?

Use AWS Database Migration Service (DMS) for minimal downtime migration. DMS can run full load + ongoing CDC replication while your source stays live, then you cut over with a short outage window. For small databases, pg_dump/pg_restore also works during a maintenance window.