Databases: RDS, Aurora, DynamoDB & ElastiCache

Managed vs you-manage

Compare to self-managed PostgreSQL on EC2: you gain operational hours back but lose root access and some exotic extensions. For a typical Spring Boot + JPA workload, RDS is the default starting point.

Graviton instance classes (db.*)

ARM-based Graviton instances deliver 20–40% better price/performance for PostgreSQL and MySQL compared to equivalent Intel/AMD classes. Use the db.t4g.* burstable family for dev/staging and low-traffic services; db.r6g.* / db.r7g.* for memory-heavy production; db.m6g.* for balanced CPU/memory. Always verify your JDBC driver and any native extensions support ARM — most Java stacks do without changes.

• db.t4g.* — burstable Graviton for dev/staging (~$0.065–0.129/hr)
• db.r6g.large — 2 vCPU / 16 GiB; common production OLTP baseline (~$0.137/hr)
• db.r6g.xlarge — 4 vCPU / 32 GiB; heavy JPA + large HikariCP pools (~$0.274/hr)

Multi-AZ vs read replicas

These solve different problems and are often confused on exams and in architecture reviews.

• Multi-AZ (synchronous standby) — a hidden standby in another AZ receives every commit synchronously. On primary failure, RDS promotes the standby and updates the DNS endpoint (~60 seconds). Same endpoint URL; apps reconnect. You pay roughly 2× instance + 2× storage. No read traffic to standby.
• Read replica (asynchronous) — separate endpoint for read scaling and reporting. Lag is typically sub-second but not zero. Replicas can be promoted to standalone primary (manual failover, minutes). Cross-region replicas exist for DR read access, not automatic failover.
• Both together — production pattern: Multi-AZ primary for HA + 1–2 read replicas for @Transactional(readOnly = true) query paths or BI tools.

RDS Proxy for Lambda and ECS

Serverless functions and container fleets open many short-lived connections. PostgreSQL has a hard limit on concurrent connections (~max_connections, often a few hundred on smaller instances). RDS Proxy sits between your app and the database, pooling and multiplexing connections.

• Lambda: each invocation would otherwise open/close a TCP+TLS+auth handshake — Proxy amortizes that cost
• ECS/Fargate: scale tasks without linear connection growth on the DB
• IAM authentication: Proxy can enforce IAM DB auth, eliminating static passwords in task definitions
• Failover awareness: Proxy tracks primary changes during Multi-AZ failover and drains in-flight transactions

Parameter groups

DB parameters live in DB parameter groups (postgresql.conf as an API resource). Dynamic params apply immediately; static params need a reboot (e.g. shared_preload_libraries). Create a custom group per environment — never edit the default.

Backups and snapshots

Automated backups (1–35 day retention) enable PITR to any second within the window. Manual snapshots persist until deleted — take one before major upgrades. Cross-region snapshot copy is the standard DR pattern; skip final snapshot only on ephemeral dev databases.

Provision PostgreSQL Multi-AZ (production baseline)

aws rds create-db-instance \
  --db-instance-identifier order-db-prod \
  --engine postgres \
  --engine-version 16.4 \
  --db-instance-class db.r6g.large \
  --allocated-storage 100 \
  --storage-type gp3 \
  --multi-az \
  --master-username dbadmin \
  --manage-master-user-password \
  --master-user-secret-kms-key-id alias/aws/secretsmanager \
  --vpc-security-group-ids sg-0abc123 \
  --db-subnet-group-name private-db-subnets \
  --backup-retention-period 14 \
  --preferred-backup-window "03:00-04:00" \
  --storage-encrypted \
  --enable-performance-insights \
  --performance-insights-retention-period 7 \
  --deletion-protection

resource "aws_db_instance" "orders" {
  identifier     = "order-db-prod"
  engine         = "postgres"
  engine_version = "16.4"
  instance_class = "db.r6g.large"

  allocated_storage = 100
  storage_type      = "gp3"
  multi_az          = true

  db_subnet_group_name   = aws_db_subnet_group.private.name
  vpc_security_group_ids = [aws_security_group.db.id]

  username                            = "dbadmin"
  manage_master_user_password         = true
  master_user_secret_kms_key_id       = aws_kms_key.secrets.arn

  backup_retention_period = 14
  backup_window           = "03:00-04:00"
  maintenance_window      = "sun:04:00-sun:05:00"

  storage_encrypted               = true
  performance_insights_enabled    = true
  performance_insights_retention_period = 7
  deletion_protection             = true

  parameter_group_name = aws_db_parameter_group.postgres16.name
}

resource "aws_db_parameter_group" "postgres16" {
  family = "postgres16"
  name   = "orders-pg16-prod"

  parameter {
    name  = "log_min_duration_statement"
    value = "1000" # log queries > 1s
  }
}

import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as rds from 'aws-cdk-lib/aws-rds';

const pgParams = new rds.ParameterGroup(this, 'Pg16Params', {
  engine: rds.DatabaseInstanceEngine.postgres({
    version: rds.PostgresEngineVersion.VER_16_4,
  }),
  parameters: { log_min_duration_statement: '1000' },
});

new rds.DatabaseInstance(this, 'OrderDb', {
  engine: rds.DatabaseInstanceEngine.postgres({
    version: rds.PostgresEngineVersion.VER_16_4,
  }),
  instanceType: ec2.InstanceType.of(ec2.InstanceClass.R6G, ec2.InstanceSize.LARGE),
  vpc,
  vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS },
  multiAz: true,
  allocatedStorage: 100,
  storageType: rds.StorageType.GP3,
  credentials: rds.Credentials.fromGeneratedSecret('dbadmin'),
  parameterGroup: pgParams,
  backupRetention: cdk.Duration.days(14),
  deletionProtection: true,
  storageEncrypted: true,
  enablePerformanceInsights: true,
});

Shared storage architecture

Unlike RDS where each instance has its own EBS volume, Aurora decouples compute (DB instances) from storage (a distributed, 6-way replicated volume across 3 AZs). Writes go to the storage layer which handles replication — compute nodes are stateless relative to storage.

flowchart TB
  subgraph compute["Aurora cluster (compute)"]
    WRITER["Writer instance\n(primary)"]
    R1["Reader replica 1"]
    R2["Reader replica 2"]
    RN["… up to 15 readers"]
  end

  subgraph storage["Shared storage volume (auto 6× replicated across 3 AZs)"]
    SEG1["Storage segment"]
    SEG2["Storage segment"]
    SEG3["Storage segment"]
  end

  APP["Spring Boot / Lambda"] --> WRITER
  APP --> R1
  WRITER --> storage
  R1 --> storage
  R2 --> storage
  RN --> storage

  storage -->|"continuous backup to S3"| S3["S3 (backup)"]

• Storage auto-scales in 10 GB increments up to 128 TiB — no pre-provisioning
• 6 copies across 3 AZs; Aurora can tolerate loss of 2 copies without write interruption
• Storage is billed separately from compute (~$0.10/GB-month for Aurora storage)
• Backups are continuous and incremental to S3 — no I/O penalty on the writer like snapshot-based systems

Replicas — up to 15

Aurora clusters have exactly one writer and up to 15 reader instances in the same region:

• Cluster endpoint — always points to the current writer; use for writes and transactional reads
• Reader endpoint — load-balances across all readers; use for read-only queries
• Custom endpoints — route specific app tiers to specific replica subsets (e.g. reporting vs API reads)
• Promotion — manual or automatic (Aurora Auto Scaling for readers; failover for writer)

Aurora Serverless v2 (ACU)

Serverless v2 scales compute in ACUs (1 ACU ≈ 2 GiB RAM). Set min/max (e.g. 0.5–16 ACU); Aurora scales in ~15 seconds. Use for dev/test (min 0.5 ACU) and spiky SaaS traffic; skip for steady OLTP where provisioned instances are cheaper.

Aurora Global Database

One primary region (read/write) + up to 5 secondary regions (read-only, typically <1 second lag). Cross-region replication uses dedicated infrastructure — not PostgreSQL logical replication you manage. Failover to a secondary region (managed or manual) completes in ~1 minute with RPO typically <1 second. Use for multi-region active-passive DR, not active-active writes (writes still go to one primary region).

MySQL vs PostgreSQL compatibility

Both support IAM database authentication, Performance Insights, and backtrack (MySQL only — undo recent changes without restore). JDBC connection strings use the cluster endpoint: jdbc:postgresql://my-cluster.cluster-abc123.eu-west-1.rds.amazonaws.com:5432/orders

Partition key design

Every item lives in exactly one partition, determined by the partition key (HASH). Optionally add a sort key (RANGE) to store multiple items per partition and enable range queries (BETWEEN, begins_with).

• High cardinality — partition keys should spread load evenly (userId, orderId, tenantId+date)
• Composite keys — PK=USER#123, SK=ORDER#2024-06-10#456 enables query by prefix
• Avoid — status flags, boolean fields, or dates alone as partition keys (hot partitions)
• Write sharding — append a random suffix (userId#shard-{0..9}) for write-heavy monotonic keys, query all shards in parallel

GSI and LSI

GSIs have their own throughput capacity (provisioned mode) or share on-demand billing. Every GSI duplicates projected attributes — watch storage and write amplification (one write to table = one write per GSI).

On-demand vs provisioned capacity

• On-demand — pay per request; no capacity planning; scales instantly; ~5× more expensive at steady high throughput
• Provisioned — set RCU/WCU (or use auto scaling); cheaper at predictable load; requires monitoring
• Reserved capacity — 1–3 year commit for provisioned mode; significant discount for known baselines

DAX (DynamoDB Accelerator)

In-memory cache cluster co-located with DynamoDB — microsecond reads for eventually consistent GetItem/Query/Scan. Write-through cache: writes go to DynamoDB and DAX together. Use for read-heavy, latency-sensitive paths where ElastiCache would require you to manage cache invalidation logic. Not a replacement for ElastiCache session stores — DAX only accelerates DynamoDB API calls.

DynamoDB Streams

Ordered change log per partition (INSERT, MODIFY, REMOVE). Consumers: Lambda (event source mapping), Kinesis Client Library, or custom. Enable NEW_AND_OLD_IMAGES when you need the previous state for audit or CDC to OpenSearch. Streams are the backbone of single-table design denormalization — update a GSI projection asynchronously via Lambda.

Single-table design basics

Store multiple entity types in one table using overloaded keys and GSIs — one Query fetches a user's orders, profile, and preferences without joins:

{
  "PK": "USER#usr_abc123",
  "SK": "PROFILE",
  "email": "[email protected]",
  "name": "Alex Chen"
}
{
  "PK": "USER#usr_abc123",
  "SK": "ORDER#2024-06-10#ord_789",
  "total": 149.99,
  "status": "SHIPPED"
}
{
  "PK": "ORDER#ord_789",
  "SK": "METADATA",
  "GSI1PK": "STATUS#SHIPPED",
  "GSI1SK": "2024-06-10#ord_789"
}

Design all access patterns on paper first (AP1: get user by ID, AP2: list orders by status, …), then derive PK/SK/GSI keys. Adding a new access pattern later often requires a new GSI or migration — relational-style schema evolution doesn't apply.

Table with GSI (orders by status)

aws dynamodb create-table \
  --table-name Orders \
  --billing-mode PAY_PER_REQUEST \
  --attribute-definitions \
    AttributeName=PK,AttributeType=S \
    AttributeName=SK,AttributeType=S \
    AttributeName=GSI1PK,AttributeType=S \
    AttributeName=GSI1SK,AttributeType=S \
  --key-schema \
    AttributeName=PK,KeyType=HASH \
    AttributeName=SK,KeyType=RANGE \
  --global-secondary-indexes '[
    {
      "IndexName": "GSI1-StatusDate",
      "KeySchema": [
        {"AttributeName": "GSI1PK", "KeyType": "HASH"},
        {"AttributeName": "GSI1SK", "KeyType": "RANGE"}
      ],
      "Projection": {"ProjectionType": "ALL"}
    }
  ]' \
  --stream-specification StreamEnabled=true,StreamViewType=NEW_AND_OLD_IMAGES \
  --sse-specification Enabled=true,SSEType=KMS \
  --tags Key=Service,Value=order-api

resource "aws_dynamodb_table" "orders" {
  name         = "Orders"
  billing_mode = "PAY_PER_REQUEST"
  hash_key     = "PK"
  range_key    = "SK"

  attribute {
    name = "PK"
    type = "S"
  }
  attribute {
    name = "SK"
    type = "S"
  }
  attribute {
    name = "GSI1PK"
    type = "S"
  }
  attribute {
    name = "GSI1SK"
    type = "S"
  }

  global_secondary_index {
    name            = "GSI1-StatusDate"
    hash_key        = "GSI1PK"
    range_key       = "GSI1SK"
    projection_type = "ALL"
  }

  stream_enabled   = true
  stream_view_type = "NEW_AND_OLD_IMAGES"

  server_side_encryption {
    enabled     = true
    kms_key_arn = aws_kms_key.app.arn
  }

  point_in_time_recovery {
    enabled = true
  }

  tags = { Service = "order-api" }
}

import * as dynamodb from 'aws-cdk-lib/aws-dynamodb';

const table = new dynamodb.Table(this, 'Orders', {
  partitionKey: { name: 'PK', type: dynamodb.AttributeType.STRING },
  sortKey: { name: 'SK', type: dynamodb.AttributeType.STRING },
  billingMode: dynamodb.BillingMode.PAY_PER_REQUEST,
  encryption: dynamodb.TableEncryption.CUSTOMER_MANAGED,
  encryptionKey: kmsKey,
  stream: dynamodb.StreamViewType.NEW_AND_OLD_IMAGES,
  pointInTimeRecovery: true,
});

table.addGlobalSecondaryIndex({
  indexName: 'GSI1-StatusDate',
  partitionKey: { name: 'GSI1PK', type: dynamodb.AttributeType.STRING },
  sortKey: { name: 'GSI1SK', type: dynamodb.AttributeType.STRING },
  projectionType: dynamodb.ProjectionType.ALL,
});

Redis vs Memcached

Default to Redis for new projects — Memcached only when you need simple horizontal scale of identical cache nodes with no persistence requirements and already have client-side sharding logic.

Cluster mode

Redis cluster mode shards data across multiple node groups (shards), each with a primary and optional replica. The cluster endpoint handles slot routing — clients like Lettuce (Spring Data Redis default) discover topology automatically.

• Disabled cluster mode — single shard, one primary + up to 5 replicas; simpler; limited to one node's RAM
• Enabled cluster mode — horizontal scale beyond ~100 GiB; required for >250k ops/sec
• Replica per shard — enable for read scaling and Multi-AZ failover within the replication group

Multi-AZ and failover

Enable Multi-AZ on a replication group: AWS automatically fails over to a replica in another AZ when the primary fails (~30–60 seconds). Automatic failover requires at least one replica per shard. Application clients must support reconnect — Spring Lettuce handles this with spring.data.redis.lettuce.cluster.refresh.adaptive=true.

Common patterns

Session cache

Store Spring Session data in Redis instead of sticky sessions on ALB. Enables rolling deploys and autoscaling without session loss. Set TTL to match session timeout; use Redis hashes for compact storage.

Rate limiting

Token bucket or sliding window counters in Redis sorted sets. Example: limit API calls to 100/minute per userId with ZADD + ZREMRANGEBYSCORE + ZCARD. Works across all ECS tasks — unlike in-memory Guava rate limiters.

Spring Cache integration

spring:
  data:
    redis:
      cluster:
        nodes:
          - orders-cache.abc123.clustercfg.euw1.cache.amazonaws.com:6379
      lettuce:
        cluster:
          refresh:
            adaptive: true
  cache:
    type: redis
    redis:
      time-to-live: 300000  # 5 min — tune per cache name

# application code:
# @Cacheable(value = "products", key = "#sku")
# public Product findBySku(String sku) { ... }

Provision ElastiCache Redis cluster

aws elasticache create-replication-group \
  --replication-group-id orders-cache \
  --replication-group-description "Order API session + cache" \
  --engine redis \
  --engine-version 7.1 \
  --cache-node-type cache.r7g.large \
  --num-node-groups 2 \
  --replicas-per-node-group 1 \
  --automatic-failover enabled \
  --multi-az-enabled \
  --cache-subnet-group-name private-cache-subnets \
  --security-group-ids sg-0def456 \
  --at-rest-encryption-enabled \
  --transit-encryption-enabled \
  --auth-token "$(openssl rand -base64 32)"

resource "aws_elasticache_replication_group" "orders" {
  replication_group_id = "orders-cache"
  description          = "Order API session + cache"
  engine               = "redis"
  engine_version       = "7.1"
  node_type            = "cache.r7g.large"

  num_node_groups         = 2
  replicas_per_node_group = 1

  automatic_failover_enabled = true
  multi_az_enabled           = true

  subnet_group_name  = aws_elasticache_subnet_group.private.name
  security_group_ids = [aws_security_group.cache.id]

  at_rest_encryption_enabled = true
  transit_encryption_enabled = true
  auth_token                 = random_password.redis_auth.result

  parameter_group_name = aws_elasticache_parameter_group.redis7.name
}

resource "aws_elasticache_parameter_group" "redis7" {
  family = "redis7"
  name   = "orders-redis7"

  parameter {
    name  = "maxmemory-policy"
    value = "volatile-lru"
  }
}

import * as ec2 from 'aws-cdk-lib/aws-ec2';
import * as elasticache from 'aws-cdk-lib/aws-elasticache';

const subnetGroup = new elasticache.CfnSubnetGroup(this, 'CacheSubnets', {
  description: 'Private cache subnets',
  subnetIds: vpc.privateSubnets.map(s => s.subnetId),
  cacheSubnetGroupName: 'private-cache-subnets',
});

new elasticache.CfnReplicationGroup(this, 'OrdersCache', {
  replicationGroupId: 'orders-cache',
  replicationGroupDescription: 'Order API session + cache',
  engine: 'redis',
  engineVersion: '7.1',
  cacheNodeType: 'cache.r7g.large',
  numNodeGroups: 2,
  replicasPerNodeGroup: 1,
  automaticFailoverEnabled: true,
  multiAzEnabled: true,
  cacheSubnetGroupName: subnetGroup.cacheSubnetGroupName!,
  securityGroupIds: [cacheSg.securityGroupId],
  atRestEncryptionEnabled: true,
  transitEncryptionEnabled: true,
  authToken: redisAuth.secretValue.unsafeUnwrap(),
});

RDS vs Aurora vs DynamoDB decision matrix

Spring Boot + RDS/Aurora guidance

Dependencies and configuration

# build.gradle: implementation 'org.postgresql:postgresql'
#                  implementation 'org.flywaydb:flyway-database-postgresql'

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST}:5432/orders?sslmode=require
    username: ${DB_USER}
    password: ${DB_PASSWORD}  # from Secrets Manager in prod — never commit
    hikari:
      maximum-pool-size: 20          # tune: (CPU cores * 2) + spindle count rule is wrong for RDS
      minimum-idle: 5
      connection-timeout: 5000
      idle-timeout: 600000
      max-lifetime: 1800000          # < RDS idle timeout (often 30 min default)
      connection-test-query: SELECT 1
  jpa:
    hibernate:
      ddl-auto: validate             # Flyway owns schema — never update/create in prod
    properties:
      hibernate:
        jdbc:
          batch_size: 50
        order_inserts: true
        order_updates: true
  flyway:
    enabled: true
    baseline-on-migrate: true

Connection math with RDS Proxy

If you have 20 ECS tasks each with HikariCP pool size 20, that's 400 connections to a db.r6g.large that supports ~500–800 max. Put RDS Proxy in front: tasks connect to the proxy endpoint; proxy maintains a smaller pool to the database (~50–100 connections). Set HikariCP maximum-pool-size per task lower (5–10) when using Proxy.

Read replica routing

Spring doesn't route reads automatically. Options:

• Manual — separate @Bean DataSource for reader endpoint; use in @Transactional(readOnly=true) routing aspect
• Aurora reader endpoint — JDBC URL with -ro cluster endpoint for read-only repository methods
• Avoid — routing writes to replicas (replication lag causes stale reads or conflicts)

Observability checklist

• Enable Performance Insights on RDS/Aurora — top SQL, wait events, DB load
• CloudWatch alarms: CPUUtilization > 80%, FreeableMemory low, DatabaseConnections near max
• Log slow queries via parameter group (log_min_duration_statement)
• Trace JDBC with X-Ray or OpenTelemetry JDBC instrumentation