AWS RDS & Aurora Managed Database Setup and Optimization

AWS RDS & Aurora Managed Database Setup and Optimization

We configure RDS and Aurora so they work for you, not the other way around. RDS and Aurora handle operational tasks: OS patching, backups, failover, replication. But "managed" does not mean "self-tuning." The default RDS configuration is designed for compatibility, not performance. Without proper Parameter Group tuning, correct instance type selection, and Multi-AZ architecture, you end up with a managed database that has unmanaged problems. After our configuration, you can forget about slowdowns and unexpected failures.

RDS vs Aurora: When to Use Which

RDS — when you need a specific PostgreSQL or MySQL version with predictable behavior. Simpler to understand, fewer surprises. Storage grows in 5–10 GB increments via autoscaling.

Aurora PostgreSQL / MySQL — cluster architecture with separate storage. Storage automatically scales up to 128 TB, failover occurs within 30 seconds (RDS takes 1–2 minutes). Aurora Global Database for cross-region replication with RPO < 1 second.

However, Aurora is 20–30% more expensive on comparable instances. For small databases or dev environments, standard RDS works fine. For production environments with high availability and read scalability requirements, choose Aurora.

What Must Be Changed in Parameter Groups?

Default parameter groups cannot be modified — you need a custom copy. For PostgreSQL, critical parameters:

shared_buffers = {DBInstanceClassMemory/4} # 25% RAM
work_mem = 16384 # 16 MB (careful: multiplied by max_connections)
maintenance_work_mem = 524288 # 512 MB for autovacuum
effective_cache_size = {DBInstanceClassMemory*3/4} # 75% RAM
wal_buffers = 16384 # 16 MB
checkpoint_completion_target = 0.9
max_connections = 200 # depends on instance
log_min_duration_statement = 1000 # log queries > 1s
log_lock_waits = 1
auto_explain.log_min_duration = 3000 # explain for slow queries

work_mem requires special attention. If 200 connections each perform sort/hash joins, actual memory consumption = work_mem × connections × operations per query. For m5.xlarge (16 GB) with 200 connections, work_mem above 32 MB is risky.

Multi-AZ and Read Replicas: How to Configure?

Multi-AZ — synchronous replication to a standby instance in another Availability Zone. On primary failure, automatic failover updates DNS to the standby. This improves availability, not read performance.

Read Replicas — asynchronous replication, up to 5 replicas for RDS, up to 15 for Aurora. Offload read-heavy queries (analytics, reporting) from the primary. Monitor replica lag via the ReplicaLag metric in CloudWatch.

A common mistake is sending analytical queries directly to the primary without using read replicas. A single heavy SELECT COUNT(*) FROM orders WHERE ... with a full table scan can degrade OLTP performance for minutes.

For Aurora, the Reader Endpoint automatically balances traffic across all read replicas. Write to the Writer Endpoint, read from the Reader Endpoint — this is the basic rule.

Backups and Point-in-Time Recovery

Automated backups with retention of 7–35 days are mandatory. This provides Point-in-Time Recovery: restore to any point within the retention window with a precision of up to 5 minutes.

Take a snapshot before any significant change (schema migration, major version upgrade) — it is not optional but a required procedure. Snapshots do not cause downtime.

For Aurora, Backtrack allows you to "rewind" the cluster several hours without restoring from a backup. It is faster than PITR but has a limited window (up to 72 hours).

resource "aws_rds_cluster" "aurora" {
  cluster_identifier = "myapp-aurora"
  engine = "aurora-postgresql"
  engine_version = "15.4"
  database_name = "myapp"
  master_username = "myapp_admin"
  master_password = var.db_password # из Secrets Manager
  backup_retention_period = 14
  preferred_backup_window = "03:00-04:00" # UTC, когда нагрузка минимальна
  backtrack_window = 259200 # 72 часа
  db_subnet_group_name = aws_db_subnet_group.main.name
  vpc_security_group_ids = [aws_security_group.rds.id]
  deletion_protection = true # защита от случайного удаления
  enabled_cloudwatch_logs_exports = ["postgresql"]
}

Secrets Manager Integration

The database password must not be stored in Terraform state or application environment variables. The correct approach:

1. RDS generates the master password via Secrets Manager (use manage_master_user_password option in Terraform provider).
2. The secret is automatically rotated on schedule.
3. The application retrieves credentials via the Secrets Manager API or through RDS Proxy.

RDS Proxy — recommended for Lambda and other serverless/highly-concurrent environments. The proxy pools connections to RDS, which is critical: with 1000 concurrent invocations, Lambda would attempt to open 1000 connections to RDS — the database would hit max_connections exceeded. The proxy maintains a limited pool and multiplexes connections.

Monitoring: What to Watch

Enable Enhanced Monitoring (1-second granularity) and Performance Insights for production. Performance Insights shows top SQL queries by load, wait events, and connections — invaluable for performance debugging.

CloudWatch metrics of first priority:

What's Included in Our Service

• Detailed audit of existing RDS/Aurora configuration with recommendations
• Custom Parameter Groups tuned to your workload (guaranteed performance improvement of 30–50%)
• Multi-AZ setup with automated failover (99.95% uptime SLA)
• Read Replicas configuration for read scaling
• Automated backups with Point-in-Time Recovery (7–35 days retention)
• Integration with AWS Secrets Manager for password rotation
• Optional RDS Proxy setup for connection pooling
• CloudWatch monitoring dashboards and alarms
• Documentation: architecture diagram, runbook, and operational procedures
• Team training: 2-hour session on RDS operations and troubleshooting
• 30 days of post-launch support (email/chat)

How to Set Up Parameter Groups in 5 Steps

1. Create a custom parameter group for your DB engine.
2. Set shared_buffers to 25% of RAM, effective_cache_size to 75%.
3. Adjust work_mem based on max_connections (e.g., 16 MB for 200 connections).
4. Enable logging: log_min_duration_statement = 1000, log_lock_waits = 1.
5. Apply the group to your DB instance and reboot if necessary.

Pricing

Basic setup (single instance, Multi-AZ, Parameter Group, backups, monitoring) starts at $500. Full configuration (Terraform, Secrets Manager, RDS Proxy, Performance Insights, training) ranges from $1,500 to $3,000 depending on complexity. We guarantee transparent pricing and a 20% reduction in database response times within the first week.

Our Experience

Over 5 years and 50+ successful DevOps projects. We are AWS certified (Solutions Architect, DevOps Engineer) and have deep expertise in AWS RDS and Aurora. Our track record includes a 40% latency reduction for a high-load SaaS platform and elimination of timeout issues. See our case study below.

For further details, refer to Official AWS RDS Documentation.