Rds Aurora

Specialist guidance for Amazon RDS and Aurora. Covers engine selection, instance sizing, high availability, read scaling, security, migration, and operational best practices.

Process

1. Identify the workload characteristics: read/write ratio, latency requirements, data volume, connection count 2. Use the awsknowledge MCP tools (mcp__plugin_aws-dev-toolkit_awsknowledge__aws___search_documentation, mcp__plugin_aws-dev-toolkit_awsknowledge__aws___read_documentation, mcp__plugin_aws-dev-toolkit_awsknowledge__aws___recommend) to verify current RDS/Aurora limits, engine versions, and features 3. Select the appropriate engine and deployment model (RDS single-instance, RDS Multi-AZ, Aurora provisioned, Aurora Serverless v2) 4. Design the high availability and read scaling topology 5. Configure security (encryption, IAM auth, network isolation) 6. Recommend operational best practices (backups, monitoring, maintenance)

Engine Selection Decision Matrix

Aurora vs RDS — Key Differences

Storage Architecture

• RDS: EBS-backed (gp3 or io2), single-AZ storage unless Multi-AZ
• Aurora: Distributed storage layer, 6 copies across 3 AZs, auto-heals, auto-grows to 128 TB
• Aurora survives loss of 2 copies for writes, 3 for reads — without manual intervention

Replication

• RDS: Async read replicas (up to 15 for MySQL, 5 for PostgreSQL), separate storage per replica
• Aurora: Up to 15 read replicas sharing the same storage volume — replica lag typically <20ms, often <10ms
• Aurora replicas can be failover targets with no data loss (same storage)

Failover

• RDS Multi-AZ: 60-120 second failover to synchronous standby
• Aurora: Typically <30 second failover to a read replica (promoted in-place)
• Aurora supports failover priority tiers (0-15) to control which replica gets promoted

Cost Comparison

• Aurora instances cost ~20% more than equivalent RDS instances
• Aurora eliminates separate EBS costs — storage is included in the Aurora pricing model
• For read-heavy workloads, Aurora's shared storage makes replicas cheaper (no storage duplication)
• Aurora Serverless v2 can be more cost-effective for variable workloads than provisioned instances sitting idle

Aurora Serverless v2

• Scales in 0.5 ACU increments (1 ACU ≈ 2 GiB RAM + proportional CPU)
• Minimum: 0.5 ACU; Maximum: 256 ACU per instance
• Scales based on CPU, connections, and memory pressure — not request count
• Can mix Serverless v2 and provisioned instances in the same cluster
• Recommended pattern: Serverless v2 reader for variable read traffic, provisioned writer for consistent write load

When to Use Serverless v2

• Development and staging environments
• Applications with idle periods (nights, weekends)
• Spiky read workloads (reporting, batch queries)
• New applications where traffic patterns are unknown

When to Avoid Serverless v2

• Sustained high-throughput production writers — provisioned is cheaper at steady state
• Latency-sensitive workloads during scale-up (scaling from minimum takes seconds, not instant)

High Availability Configurations

RDS Multi-AZ (Instance)

• Synchronous standby in a different AZ — automatic failover
• Standby is not readable (unlike Aurora replicas)
• Use for: production databases that need simple HA without read scaling

RDS Multi-AZ (Cluster) — db.r6gd Only

• One writer + two readable standbys across 3 AZs
• Uses local NVMe + synchronous replication
• Sub-35-second failover
• Limited to specific instance classes

Aurora Multi-AZ

• Create at least one read replica in a different AZ for HA
• All replicas share storage, so failover has zero data loss
• For production: minimum 2 replicas across 2 AZs (writer + 2 readers = 3 AZs)

Aurora Global Database

• Cross-region replication with <1 second typical lag
• Managed RPO/RTO with automated failover
• Write forwarding lets readers in secondary regions redirect writes to the primary
• Use for: disaster recovery, low-latency global reads

RDS Proxy

• Fully managed connection pooler sitting between applications and the database
• Multiplexes thousands of application connections to a smaller pool of database connections
• Reduces failover time by maintaining open connections to standby
• Essential for Lambda → RDS/Aurora (Lambda creates many short-lived connections)

When to Use RDS Proxy

• Lambda functions connecting to RDS/Aurora (connection exhaustion risk)
• Applications with many short-lived connections
• Reducing failover disruption (proxy pins to new primary automatically)

When to Skip RDS Proxy

• Applications with persistent connection pools (like traditional app servers with HikariCP/pgBouncer)
• Workloads requiring session-level features (prepared statements, temp tables — proxy may pin connections)

Security

Encryption

• At rest: Enable at creation time (cannot be enabled later without snapshot-restore). Use AWS KMS CMK for key control.
• In transit: Enforce SSL via parameter group (rds.force_ssl = 1 for PostgreSQL, require_secure_transport = ON for MySQL)

Network Isolation

• Deploy in private subnets only — never assign a public IP
• Use security groups to restrict ingress to application subnets
• Use VPC endpoints for API calls (rds and rds-data endpoints)

Authentication

• IAM database authentication: Token-based, no passwords stored — good for Lambda and automated access
• Secrets Manager rotation: Automatic password rotation on a schedule — use for traditional username/password auth
• Kerberos/Active Directory: Available for SQL Server and Oracle via AWS Directory Service

Blue/Green Deployments

• Create a "green" copy of the production database with changes applied (engine upgrade, parameter changes, schema changes)
• RDS keeps the green environment in sync via logical replication
• Switchover takes ~1 minute with minimal downtime
• Automatic rollback if health checks fail

Supported Changes

• Major engine version upgrades
• Parameter group changes
• Schema changes on the green environment
• Instance class changes

Limitations

• Not available for Aurora Serverless v1 (v2 supported)
• Requires enough capacity for both environments during the transition

Backup and Recovery

Automated Backups

• Default retention: 7 days (configurable 0-35 days; 0 disables)
• Point-in-time recovery (PITR) to any second within the retention window
• Backups are stored in S3 (managed by AWS, not visible in your bucket)

Manual Snapshots

• Persist indefinitely until deleted
• Can be shared cross-account or copied cross-region
• Use for: pre-change safety nets, archival, cross-region DR

Aurora Backtrack (MySQL only)

• Rewind the database to a specific point in time without restore
• Operates on the same cluster — much faster than PITR
• Configure a backtrack window (up to 72 hours)
• Use for: recovering from bad queries, accidental deletes

Anti-Patterns

• Public subnets for databases. Never place RDS/Aurora in a public subnet. Use private subnets and access through application layer, VPN, or bastion.
• Default parameter groups. Always create custom parameter groups — default ones cannot be modified and make tuning impossible.
• Unencrypted instances. Encryption must be enabled at creation. Retrofitting requires snapshot → copy-encrypted → restore, which means downtime and new endpoints.
• Lambda without RDS Proxy. Lambda creates new connections per invocation. Without a connection pooler, concurrent Lambdas exhaust max_connections within seconds.
• Single-AZ production databases. No HA means any AZ failure takes down the database until manual intervention.
• Oversized instances "just in case". Start with Performance Insights data, right-size based on actual db.load, not guesswork. Graviton (r7g) instances offer better price-performance.
• Ignoring storage IOPS limits. gp3 default is 3,000 IOPS — if the workload exceeds this, provision higher IOPS or move to io2 before hitting throttling.
• Manual password management. Use --manage-master-user-password (Secrets Manager integration) or IAM authentication. Hardcoded passwords in application config are a security incident waiting to happen.
• Not enabling deletion protection on production. A single delete-db-instance call without deletion protection can destroy the production database.

Migration Guidance

For migrating to RDS/Aurora, coordinate with the migration-advisor agent for full assessment workflows.

Common Migration Paths

• Self-managed MySQL/PostgreSQL → Aurora: Use AWS DMS for minimal-downtime migration with CDC
• Oracle/SQL Server → Aurora PostgreSQL: Use AWS SCT (Schema Conversion Tool) + DMS
• RDS MySQL → Aurora MySQL: Use snapshot restore (fastest) or create Aurora read replica of RDS instance then promote

Key Considerations

• Always run SCT assessment report before cross-engine migrations — it quantifies conversion effort
• Test with DMS validation tasks to verify data integrity post-migration
• Plan for endpoint changes — Aurora uses cluster endpoints (writer) and reader endpoints

Additional Resources

Reference Files

For detailed operational guidance, consult:

• `references/instance-sizing.md` — Instance family comparison, Graviton recommendations, memory-to-connections ratios, ACU sizing, storage types, and cost optimization patterns
• `references/parameter-tuning.md` — PostgreSQL and MySQL parameter recommendations, Aurora-specific parameters, and safe change procedures
• `references/monitoring-operations.md` — CloudWatch alarm thresholds, Performance Insights wait event analysis, Enhanced Monitoring, backup verification, failover testing, connection diagnostics, and common CLI commands

Related Skills

• `migration-advisor` (agent) — Full migration assessment workflows (DMS, SCT, migration waves)
• `cost-check` — Detailed cost analysis and Reserved Instance recommendations
• `security-review` — IAM, network, and encryption audit for database configurations
• `networking` — VPC design, subnet planning, and security group configuration

Output Format

When recommending a database design, include:

Include estimated monthly cost range using the cost-check skill.

RDS/Aurora Instance Sizing Guide

Instance Family Selection

Graviton (ARM) — Default Recommendation

Graviton-based instances (r7g, r6g, m7g, t4g) offer ~20% better price-performance than Intel equivalents. Default to Graviton unless the workload requires x86-specific extensions.

When to Use Each Family

• r7g (default): Most production OLTP workloads. Memory-heavy databases benefit from the 8:1 memory-to-vCPU ratio.
• m7g: Workloads that are CPU-bound rather than memory-bound. Lower memory-to-vCPU ratio (4:1) at lower cost.
• t4g: Development, staging, low-traffic production. Burstable CPU is fine when utilization is <40% average. Enable unlimited mode for production to avoid CPU throttling.
• x2g: Data warehousing workloads, very large working sets that must fit in buffer pool to avoid disk I/O.

Memory-to-Connections Ratios

PostgreSQL

Each PostgreSQL connection consumes approximately 5-10 MB of memory at baseline. Under heavy query load, connections can consume 50-200 MB each (work_mem allocations).

Rule of thumb: Reserve 25% of memory for shared_buffers, 10% for OS/overhead, and allocate remaining memory across max_connections assuming 10-20 MB per connection under load.

MySQL

MySQL connections are lighter (~1-5 MB each at baseline) but InnoDB buffer pool should claim 75% of memory.

Rule of thumb: innodb_buffer_pool_size = 75% of memory. Remaining 25% for connections, temp tables, sort buffers, and OS.

Aurora Serverless v2 ACU Sizing

1 ACU = approximately 2 GiB RAM + proportional CPU + networking.

Sizing approach: Start with min=0.5, max=16 for new workloads. Monitor ServerlessDatabaseCapacity and ACUUtilization metrics for 2 weeks, then tighten the range. Set max ACU high enough that the database never throttles — it only costs more when it scales up.

Right-Sizing Process

1. Enable Performance Insights (free tier: 7-day retention) 2. Run production workload for at least 1 week 3. Check db.load — if average load < 1.0 and max load < vCPU count, the instance is oversized 4. Check FreeableMemory — if consistently >50% of total memory, consider downsizing 5. Check CPUUtilization — if average <30%, consider smaller instance or Graviton migration 6. For Aurora Serverless v2: check ServerlessDatabaseCapacity — if min ACU is never reached, lower it

Storage Sizing

RDS (EBS-Backed)

gp3 tips:

• Free IOPS/throughput increase: gp3 baseline is 3,000 IOPS / 125 MiB/s regardless of volume size
• Provision additional IOPS only when CloudWatch shows VolumeReadOps + VolumeWriteOps consistently approaching 3,000/sec
• Storage auto-scaling: enable and set max threshold to avoid running out of space

Aurora (Managed Storage)

• Storage auto-grows in 10 GiB increments up to 128 TiB
• No IOPS provisioning needed — Aurora handles I/O distribution
• I/O-Optimized cluster option: eliminates per-I/O charges for I/O-heavy workloads (>25% of database cost is I/O)
• Standard pricing includes I/O charges per million requests — suitable for most workloads

Cost Optimization Patterns

Reserved Instances

• 1-year all-upfront: ~30-40% savings over on-demand
• 3-year all-upfront: ~50-60% savings over on-demand
• Apply to the writer instance (always running); use Serverless v2 for variable readers

Graviton Migration

• Direct ~20% cost reduction with no application changes for most workloads
• MySQL and PostgreSQL are fully compatible
• Use blue/green deployment for zero-downtime migration from Intel to Graviton

Aurora I/O-Optimized vs Standard

• Calculate: if I/O costs > 25% of total Aurora bill, switch to I/O-Optimized
• I/O-Optimized eliminates per-I/O charges but increases instance and storage cost by ~30%
• Check with cost-check skill for specific workload analysis

RDS/Aurora Monitoring and Operations Reference

CloudWatch Metrics

Critical Metrics — Monitor with Alarms

Important Metrics — Review Weekly

Performance Insights

Setup

• Enable at instance creation or via modify-db-instance --enable-performance-insights
• Free tier: 7 days retention (sufficient for most troubleshooting)
• Paid: up to 24 months retention ($0.068/vCPU/month) — use for trend analysis

Key Concepts

db.load: The average number of active sessions. Compare to vCPU count:

• db.load < vCPU count → database is not CPU-constrained
• db.load > vCPU count → queries are waiting (bottleneck)
• db.load >> vCPU count → significant contention, immediate action needed

Wait Events (what queries are waiting on):

Top SQL Analysis

1. Sort by db.load contribution to find the most resource-consuming queries 2. Check execution plan with EXPLAIN (ANALYZE, BUFFERS) for the top offenders 3. Look for sequential scans on large tables, nested loops with large row counts, and sort operations spilling to disk 4. Use pg_stat_statements (PostgreSQL) or performance_schema (MySQL) for aggregated query stats

Enhanced Monitoring

• Provides OS-level metrics at 1-60 second granularity
• Separate from CloudWatch metrics — requires an IAM role for the RDS instance
• Essential for distinguishing database issues from OS/instance issues

Key OS Metrics

Operational Procedures

Maintenance Windows

• Schedule during lowest-traffic period (review CloudWatch metrics to identify)
• Enable auto_minor_version_upgrade for security patches
• For major version upgrades: use blue/green deployments, never in-place on production
• Aurora: minor patches apply with zero-downtime patching (ZDP) when possible

Backup Verification

Quarterly backup verification procedure: 1. Restore from the latest automated backup to a test instance 2. Run application smoke tests against the restored instance 3. Verify point-in-time recovery (PITR) works by restoring to a specific timestamp 4. Document restore time — this is the actual RTO 5. Delete the test instance after verification

Connection Management

Diagnosing Connection Issues

-- PostgreSQL: active connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- PostgreSQL: long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle';

-- PostgreSQL: idle-in-transaction connections (lock holders)
SELECT pid, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND (now() - xact_start) > interval '1 minute';

-- MySQL: connection overview
SHOW STATUS LIKE 'Threads_%';
SHOW PROCESSLIST;

-- MySQL: long-running queries
SELECT * FROM information_schema.processlist
WHERE TIME > 300 AND COMMAND != 'Sleep';

Connection Leak Prevention

• Set idle_in_transaction_session_timeout (PostgreSQL) or wait_timeout (MySQL) to kill idle connections
• Monitor DatabaseConnections metric trend — steady increase indicates a leak
• Use RDS Proxy to absorb connection spikes and multiplex connections

Failover Testing

Quarterly failover drill: 1. Initiate failover via aws rds failover-db-cluster (Aurora) or aws rds reboot-db-instance --force-failover (RDS Multi-AZ) 2. Measure actual failover time (Aurora target: <30s, RDS Multi-AZ target: <120s) 3. Verify application reconnects without manual intervention 4. Check that monitoring alerts fired as expected 5. Document actual RTO for DR planning

Diagnostic CLI Commands

Resource creation and modification belong in IaC (CDK, CloudFormation, Terraform). Use the iac-scaffold skill for templates. The CLI commands below are for diagnostics, investigation, and operational procedures only.

# Describe cluster (endpoints, status, instances, engine version)
aws rds describe-db-clusters --db-cluster-identifier my-cluster

# Describe a specific instance (class, AZ, storage, parameter group)
aws rds describe-db-instances --db-instance-identifier my-instance

# List all instances in the account
aws rds describe-db-instances --query "DBInstances[].{ID:DBInstanceIdentifier,Class:DBInstanceClass,Engine:Engine,Status:DBInstanceStatus,AZ:AvailabilityZone}" --output table

# Check current parameter values
aws rds describe-db-parameters --db-parameter-group-name my-param-group \
  --query "Parameters[?ParameterName=='max_connections']"

# List all parameter groups
aws rds describe-db-parameter-groups --query "DBParameterGroups[].{Name:DBParameterGroupName,Family:DBParameterGroupFamily}" --output table

# View pending maintenance actions
aws rds describe-pending-maintenance-actions

# List snapshots for a cluster
aws rds describe-db-cluster-snapshots --db-cluster-identifier my-cluster \
  --query "DBClusterSnapshots[].{ID:DBClusterSnapshotIdentifier,Status:Status,Created:SnapshotCreateTime}" --output table

# Check events (last 24 hours)
aws rds describe-events --duration 1440 --source-type db-cluster

# View Performance Insights metrics (requires PI enabled)
aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-XXXXX \
  --metric-queries '[{"Metric":"db.load.avg"}]' \
  --start-time $(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ) \
  --period-in-seconds 60

# Initiate failover drill (Aurora) — use during planned DR testing
aws rds failover-db-cluster --db-cluster-identifier my-cluster

# Initiate failover drill (RDS Multi-AZ) — use during planned DR testing
aws rds reboot-db-instance --db-instance-identifier my-rds-instance --force-failover

RDS/Aurora Parameter Tuning Reference

Parameter Group Strategy

• Never modify the default parameter group — create a custom one
• Use separate parameter groups for writer and reader instances when tuning differs
• Aurora cluster parameter groups apply to all instances; instance parameter groups override per-instance
• Changes to static parameters require a reboot; dynamic parameters apply immediately
• Always test parameter changes in staging before production — use blue/green deployments for risky changes

PostgreSQL Parameters

Memory and Buffers

Connections and Logging

Query Performance

WAL and Checkpoints (RDS only — Aurora handles this)

Vacuum and Autovacuum

Transaction ID wraparound prevention: Monitor age(datfrozenxid) — if approaching 1 billion, autovacuum is not keeping up. Increase autovacuum_max_workers and lower autovacuum_vacuum_cost_delay.

MySQL Parameters

InnoDB Buffer Pool

Connections and Threads

Logging and Slow Queries

InnoDB I/O and Durability

Replication (RDS Read Replicas)

Aurora-Specific Parameters

Aurora manages many parameters automatically. Avoid overriding these unless there is a specific, measured need:

• shared_buffers / innodb_buffer_pool_size — Aurora manages buffer allocation
• WAL/redo log settings — Aurora's distributed storage handles this
• Checkpoint settings — Aurora's storage layer handles persistence

Aurora Parameters Worth Tuning

Applying Parameter Changes

Dynamic Parameters (No Reboot Required)

Apply immediately with modify-db-parameter-group or modify-db-cluster-parameter-group.

Common dynamic parameters: max_connections, work_mem, log_min_duration_statement, slow_query_log, long_query_time

Static Parameters (Reboot Required)

Change takes effect after the next reboot or during the maintenance window.

Common static parameters: shared_buffers, max_worker_processes, innodb_buffer_pool_size

Safe Change Process

1. Change parameters in staging, monitor for 24-48 hours 2. For production: use blue/green deployment for static parameters to minimize downtime 3. For dynamic parameters: apply during low-traffic periods and monitor immediately 4. Always record parameter changes and rationale — use parameter group descriptions or tags