Scaling Aurora: From Prototype to Production

Scaling Aurora: From Prototype to Production

Amazon Aurora has become one of the most popular managed relational database engines for modern cloud applications. It offers MySQL and PostgreSQL compatibility with a distributed, SSD-backed storage layer that decouples compute from storage. While getting started with Aurora is straightforward—often as simple as pointing your existing application at a new endpoint—scaling it from a single-instance prototype to a resilient, high-throughput production system requires deliberate architecture, careful configuration, and ongoing observability. This tutorial walks through the full journey, covering the mechanics of Aurora scaling, practical implementation patterns, and the operational best practices that separate toy deployments from production-grade systems.

What Aurora Scaling Actually Means

Unlike a traditional self-hosted database where "scaling" usually means buying a bigger box, Aurora scaling happens along several independent axes. Understanding these axes is the foundation for every decision that follows.

• Compute scaling — changing the instance class (CPU and memory) of your primary writer and read replicas.
• Horizontal read scaling — adding read replicas to distribute SELECT workload across multiple nodes.
• Storage scaling — Aurora's storage layer grows automatically in 10 GiB increments up to 128 TiB, with no manual provisioning required.
• Connection scaling — using Aurora Serverless, RDS Proxy, or a connection pooler to absorb large numbers of client connections.
• Throughput scaling — leveraging Aurora Parallel Query, the I/O-Optimized storage tier, or sharding to push past single-cluster limits.

The key insight is that these axes are not interchangeable. A workload that is CPU-bound on the writer will not benefit from adding read replicas. A workload that is connection-bound will not benefit from a larger instance class. The first step in any scaling effort is therefore measurement, which we will return to throughout this tutorial.

Why Scaling Aurora Matters

Prototypes typically run on a single db.r5.large or db.t3.medium instance with default parameters and a single application server connecting through a short-lived connection. This works beautifully at low traffic. As you approach production, several pressures converge simultaneously.

First, availability requirements increase. A single-instance cluster has a failure domain that includes the entire compute layer. Aurora's storage is replicated six ways across three Availability Zones, but the compute node is a single point of failure until you add replicas and enable failover.

Second, read traffic typically grows faster than write traffic. Most web applications have a read-to-write ratio of 10:1 or higher. Pushing all of that through a single writer wastes the most expensive resource in the cluster and creates contention.

Third, connection counts explode. A fleet of twenty application servers each opening fifty connections means a thousand concurrent database connections, which exhausts the memory and connection limits of even moderately sized instances.

Finally, cost becomes a concern. Over-provisioning a large writer "just in case" is expensive. Aurora's pricing model includes charges for compute, storage, I/O, and data transfer, and naive scaling can inflate bills dramatically. Thoughtful scaling keeps both performance and cost in balance.

Starting Point: A Typical Prototype Cluster

Let us begin with a prototype cluster created via Terraform. This represents the starting point that most teams have when they decide to move toward production.

# prototype.tf
resource "aws_rds_cluster" "prototype" {
  cluster_identifier      = "app-prototype"
  engine                  = "aurora-mysql"
  engine_version          = "8.0.mysql_aurora.3.04.0"
  master_username         = "admin"
  master_password         = var.db_password
  database_name           = "appdb"
  db_subnet_group_name    = aws_db_subnet_group.app.name
  vpc_security_group_ids  = [aws_security_group.db.id]
  storage_encrypted       = true
  backup_retention_period = 1
  deletion_protection     = false
}

resource "aws_rds_cluster_instance" "prototype" {
  cluster_identifier   = aws_rds_cluster.prototype.id
  instance_class       = "db.t3.medium"
  engine               = "aurora-mysql"
  identifier           = "app-prototype-1"
  publicly_accessible  = false
}

This configuration is fine for development. It has a single instance, minimal backups, and no replicas. The first step toward production is to add a read replica and enable multi-AZ failover.

Adding Read Replicas and Multi-AZ Failover

Read replicas in Aurora share the same storage volume as the writer. This means there is no replication lag for the data itself—replicas are updated through the storage layer, not by shipping binlogs over the network. There can still be lag in applying changes to the replica's in-memory buffer pool, but it is typically measured in milliseconds.

Here is the updated Terraform configuration with two read replicas in different Availability Zones.

# production.tf
resource "aws_rds_cluster" "main" {
  cluster_identifier      = "app-prod"
  engine                  = "aurora-mysql"
  engine_version          = "8.0.mysql_aurora.3.04.0"
  master_username         = "admin"
  master_password         = var.db_password
  database_name           = "appdb"
  db_subnet_group_name    = aws_db_subnet_group.app.name
  vpc_security_group_ids  = [aws_security_group.db.id]
  storage_encrypted       = true
  backup_retention_period = 14
  preferred_backup_window = "03:00-04:00"
  preferred_maintenance_window = "sun:04:00-sun:05:00"
  deletion_protection     = true
  enabled_cloudwatch_logs_exports = ["error", "general", "slowquery"]
}

resource "aws_rds_cluster_instance" "writer" {
  cluster_identifier   = aws_rds_cluster.main.id
  instance_class       = "db.r6g.2xlarge"
  engine               = "aurora-mysql"
  identifier           = "app-prod-writer"
  instance_role        = "writer"
  publicly_accessible  = false
  promotion_tier       = 0
}

resource "aws_rds_cluster_instance" "reader_a" {
  cluster_identifier   = aws_rds_cluster.main.id
  instance_class       = "db.r6g.2xlarge"
  engine               = "aurora-mysql"
  identifier           = "app-prod-reader-a"
  instance_role        = "reader"
  publicly_accessible  = false
  promotion_tier       = 1
}

resource "aws_rds_cluster_instance" "reader_b" {
  cluster_identifier   = aws_rds_cluster.main.id
  instance_class       = "db.r6g.2xlarge"
  engine               = "aurora-mysql"
  identifier           = "app-prod-reader-b"
  instance_role        = "reader"
  publicly_accessible  = false
  promotion_tier       = 2
}

The promotion_tier parameter controls failover priority. Tier 0 is promoted first, then tier 1, and so on. By setting the writer to tier 0 and the first reader to tier 1, we ensure deterministic failover behavior. The second reader at tier 2 acts as a warm standby that can absorb read traffic even during a failover event.

Using the Reader Endpoint Correctly

Aurora provides two cluster endpoints out of the box: the writer endpoint, which always resolves to the current primary, and the reader endpoint, which load-balances across all read replicas. The most common mistake teams make is pointing all application traffic at the writer endpoint and ignoring the reader endpoint entirely.

The correct pattern is to split your application's database access into two logical connections: one for writes and reads that require strict consistency, and one for reads that can tolerate eventual consistency. Here is an example using Python and SQLAlchemy.

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session
from contextlib import contextmanager

# Writer endpoint: use for INSERT, UPDATE, DELETE,
# and SELECTs that must see the latest committed data.
writer_engine = create_engine(
    "mysql+pymysql://app_user:secret@app-prod.cluster-abc123.us-east-1.rds.amazonaws.com/appdb",
    pool_size=20,
    max_overflow=10,
    pool_recycle=1800,
    pool_pre_ping=True,
)

# Reader endpoint: use for reporting, list views,
# search, and any read that tolerates replica lag.
reader_engine = create_engine(
    "mysql+pymysql://app_user:secret@app-prod.cluster-ro-abc123.us-east-1.rds.amazonaws.com/appdb",
    pool_size=40,
    max_overflow=20,
    pool_recycle=1800,
    pool_pre_ping=True,
)

WriterSession = sessionmaker(bind=writer_engine)
ReaderSession = sessionmaker(bind=reader_engine)

@contextmanager
def write_session() -> Session:
    session = WriterSession()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

@contextmanager
def read_session() -> Session:
    session = ReaderSession()
    try:
        yield session
    finally:
        session.close()

With this structure, your application code explicitly chooses the appropriate session. A user profile update uses write_session(), while a paginated product listing uses read_session(). This simple separation can cut writer CPU utilization in half for typical web workloads.

Handling Connection Scaling with RDS Proxy

As your application fleet grows, the number of database connections becomes a bottleneck long before CPU or memory does. Each connection consumes memory on the database instance for its session state, buffer pool references, and temporary tables. A db.r6g.2xlarge instance can handle roughly 2,000 to 4,000 concurrent connections before memory pressure becomes problematic, but performance degrades well before that point.

Amazon RDS Proxy sits between your application and Aurora, pooling and multiplexing connections. Your application opens many short-lived connections to the proxy, and the proxy maintains a smaller number of long-lived connections to the database. This is especially valuable for serverless environments like AWS Lambda, where each concurrent invocation might otherwise open its own database connection.

Here is how to add RDS Proxy to your cluster.

# proxy.tf
resource "aws_secretsmanager_secret" "db_credentials" {
  name = "app-prod-db-credentials"
}

resource "aws_secretsmanager_secret_version" "db_credentials" {
  secret_id = aws_secretsmanager_secret.db_credentials.id
  secret_string = jsonencode({
    username = "admin"
    password = var.db_password
    engine   = "mysql"
    host     = aws_rds_cluster.main.endpoint
    port     = 3306
    dbname   = "appdb"
  })
}

resource "aws_iam_role" "proxy_role" {
  name = "rds-proxy-role"
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = "sts:AssumeRole"
      Effect = "Allow"
      Principal = { Service = "rds.amazonaws.com" }
    }]
  })
}

resource "aws_iam_role_policy" "proxy_policy" {
  name = "rds-proxy-policy"
  role = aws_iam_role.proxy_role.id
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect = "Allow"
      Action = [
        "secretsmanager:GetSecretValue",
        "secretsmanager:GetResourcePolicy",
        "secretsmanager:DescribeSecret",
        "secretsmanager:ListSecretVersionIds"
      ]
      Resource = [aws_secretsmanager_secret.db_credentials.arn]
    }]
  })
}

resource "aws_db_proxy" "main" {
  name                   = "app-prod-proxy"
  debug_logging          = false
  engine_family          = "MYSQL"
  idle_client_timeout    = 1800
  require_tls            = true
  role_arn               = aws_iam_role.proxy_role.arn
  vpc_subnet_ids         = data.aws_subnets.private.ids
  vpc_security_group_ids = [aws_security_group.proxy.id]

  auth {
    description = "Aurora auth"
    auth_scheme = "SECRETS"
    iam_auth    = "DISABLED"
    secret_arn  = aws_secretsmanager_secret.db_credentials.arn
  }

  target_role_arn = aws_iam_role.proxy_role.arn

  depends_on = [aws_rds_cluster.main]
}

resource "aws_db_proxy_default_target_group" "default" {
  db_proxy_name = aws_db_proxy.main.name

  connection_pool_config {
    connection_borrow_timeout    = 120
    max_connections_percent      = 90
    max_idle_connections_percent = 50
  }
}

resource "aws_db_proxy_target" "cluster" {
  db_proxy_name          = aws_db_proxy.main.name
  target_group_name      = aws_db_proxy_default_target_group.default.name
  db_cluster_identifier  = aws_rds_cluster.main.id
}

Once the proxy is in place, update your application to connect to the proxy endpoint instead of the cluster endpoint directly. The proxy automatically routes write queries to the writer and read queries to replicas when you configure it with the cluster as the target.

# database.py with RDS Proxy
import os

PROXY_ENDPOINT = os.environ.get(
    "DB_PROXY_ENDPOINT",
    "app-prod-proxy.proxy-abc123.us-east-1.rds.amazonaws.com"
)

writer_engine = create_engine(
    f"mysql+pymysql://app_user:secret@{PROXY_ENDPOINT}/appdb",
    pool_size=10,
    max_overflow=5,
    pool_recycle=1800,
    pool_pre_ping=True,
    connect_args={"ssl": {"ca": "/etc/ssl/certs/rds-ca.pem"}},
)

Notice that the pool size is now smaller. Because the proxy multiplexes connections, you do not need a large pool on the application side. This reduces memory usage on your application servers and reduces the total connection count hitting the database.

Choosing the Right Instance Class

Instance class selection should be driven by measurement, not guesswork. The general guidance is as follows.

• db.t3 / db.t4g — Burstable instances suitable for development and low-traffic production. They accumulate CPU credits during idle periods and spend them during bursts. Sustained high CPU will exhaust credits and throttle performance.
• db.r6g / db.r6i — Memory-optimized instances, ideal for Aurora because the buffer pool is the single most important performance factor. Most production workloads should start here.
• db.x2g / db.x2iezn — Extreme memory instances for very large working sets that must fit in RAM. Use these only when you have confirmed that buffer pool misses are the bottleneck.

A practical approach is to start with db.r6g.2xlarge (8 vCPUs, 64 GiB RAM) for both writer and readers, monitor for two weeks under realistic load, and then right-size. The CloudWatch metrics that matter most are CPUUtilization, DatabaseConnections, BufferCacheHitRatio, FreeableMemory, and VolumeReadIOPs.

# alarms.tf
resource "aws_cloudwatch_metric_alarm" "writer_cpu" {
  alarm_name          = "aurora-writer-high-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 75
  alarm_description   = "Writer CPU above 75% for 15 minutes"
  dimensions = {
    DBClusterIdentifier = aws_rds_cluster.main.id
    Role                = "WRITER"
  }
  alarm_actions = [aws_sns_topic.alerts.arn]
}

resource "aws_cloudwatch_metric_alarm" "buffer_cache_hit_ratio" {
  alarm_name          = "aurora-low-buffer-cache-hit-ratio"
  comparison_operator = "LessThanThreshold"
  evaluation_periods  = 2
  metric_name         = "BufferCacheHitRatio"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 95
  alarm_description   = "Buffer cache hit ratio below 95% - consider larger instance"
  dimensions = {
    DBClusterIdentifier = aws_rds_cluster.main.id
  }
  alarm_actions = [aws_sns_topic.alerts.arn]
}

resource "aws_cloudwatch_metric_alarm" "replica_lag" {
  alarm_name          = "aurora-replica-lag-high"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "AuroraReplicaLag"
  namespace           = "AWS/RDS"
  period              = 60
  statistic           = "Average"
  threshold           = 1000
  alarm_description   = "Replica lag above 1 second for 3 minutes"
  dimensions = {
    DBClusterIdentifier = aws_rds_cluster.main.id
    Role                = "READER"
  }
  alarm_actions = [aws_sns_topic.alerts.arn]
}

# Switching to I/O-Optimized storage
resource "aws_rds_cluster" "main" {
  cluster_identifier      = "app-prod"
  engine                  = "aurora-mysql"
  engine_version          = "8.0.mysql_aurora.3.04.0"
  # ... other config ...
  storage_type            = "aurora-iopt1"
}

# shard_router.py
import hashlib
from dataclasses import dataclass
from typing import Dict

@dataclass
class ClusterConfig:
    name: str
    writer_endpoint: str
    reader_endpoint: str

class ShardRouter:
    def __init__(self, clusters: list[ClusterConfig]):
        self.clusters = clusters
        self.engines: Dict[str, tuple] = {}
        for cluster in clusters:
            writer = create_engine(
                f"mysql+pymysql://app_user:secret@{cluster.writer_endpoint}/appdb",
                pool_size=10, pool_pre_ping=True
            )
            reader = create_engine(
                f"mysql+pymysql://app_user:secret@{cluster.reader_endpoint}/appdb",
                pool_size=20, pool_pre_ping=True
            )
            self.engines[cluster.name] = (writer, reader)

    def get_shard(self, tenant_id: str) -> str:
        """Deterministically map a tenant to a shard."""
        hash_val = int(hashlib.md5(tenant_id.encode()).hexdigest(), 16)
        shard_index = hash_val % len(self.clusters)
        return self.clusters[shard_index].name

    def get_writer(self, tenant_id: str):
        shard = self.get_shard(tenant_id)
        return self.engines[shard][0]

    def get_reader(self, tenant_id: str):
        shard = self.get_shard(tenant_id)
        return self.engines[shard][1]

# Usage
router = ShardRouter([
    ClusterConfig("shard-0", "shard-0.cluster-a.rds.amazonaws.com",
                  "shard-0.cluster-ro-a.rds.amazonaws.com"),
    ClusterConfig("shard-1", "shard-1.cluster-b.rds.amazonaws.com",
                  "shard-1.cluster-ro-b.rds.amazonaws.com"),
    ClusterConfig("shard-2", "shard-2.cluster-c.rds.amazonaws.com",
                  "shard-2.cluster-ro-c.rds.amazonaws.com"),
])

# All operations for tenant "acme-corp" go to the same shard
engine = router.get_writer("acme-corp")
with engine.connect() as conn:
    conn.execute("UPDATE accounts SET balance = balance + 100 WHERE tenant_id = %s",
                 ("acme-corp",))

# serverless.tf
resource "aws_rds_cluster" "serverless" {
  cluster_identifier      = "app-serverless"
  engine                  = "aurora-mysql"
  engine_version          = "8.0.mysql_aurora.3.04.0"
  master_username         = "admin"
  master_password         = var.db_password
  database_name           = "appdb"
  db_subnet_group_name    = aws_db_subnet_group.app.name
  vpc_security_group_ids  = [aws_security_group.db.id]
  serverless_v2_scaling_configuration {
    min_capacity = 0.5
    max_capacity = 16
  }
}

resource "aws_rds_cluster_instance" "serverless_writer" {
  cluster_identifier = aws_rds_cluster.serverless.id
  engine             = "aurora-mysql"
  instance_class     = "db.serverless"
  identifier         = "app-serverless-writer"
}

resource "aws_rds_cluster_instance" "serverless_reader" {
  cluster_identifier = aws_rds_cluster.serverless.id
  engine             = "aurora-mysql"
  instance_class     = "db.serverless"
  identifier         = "app-serverless-reader"
}

# parameter_group.tf
resource "aws_rds_cluster_parameter_group" "production" {
  name        = "app-prod-params"
  family      = "aurora-mysql8.0"
  description = "Production parameter group for app-prod"

  parameter {
    name  = "max_connections"
    value = "1600"
  }

  parameter {
    name  = "innodb_buffer_pool_size"
    value = "{DBInstanceClassMemory*3/4}"
  }

  parameter {
    name  = "innodb_log_file_size"
    value = "1342177280"
  }

  parameter {
    name  = "innodb_flush_log_at_trx_commit"
    value = "1"
  }

  parameter {
    name  = "sync_binlog"
    value = "1"
  }

  parameter {
    name  = "long_query_time"
    value = "0.5"
  }

  parameter {
    name  = "slow_query_log"
    value = "1"
  }

  parameter {
    name  = "aurora_enable_repl_bin_log_filter"
    value = "1"
  }

  parameter {
    name  = "aurora_enable_zdr"
    value = "0"
  }

  lifecycle {
    ignore_changes = [parameter]
  }
}

# monitoring.tf
resource "aws_rds_cluster" "main" {
  # ... existing config ...

  enabled_cloudwatch_logs_exports = ["error", "general", "slowquery"]
  deletion_protection             = true
}

resource "aws_rds_cluster_instance" "writer" {
  # ... existing config ...
  performance_insights_enabled          = true
  performance_insights_retention_period = 7
  monitoring_interval                   = 30
  monitoring_role_arn                   = aws_iam_role.rds_enhanced.arn
}