Shelf · AWS · Chapter 5

Chapter 5: Databases on AWS

The managed database layer is where your ETL pipelines land and where most production outages actually start. This chapter builds the mental model you need to pick the right engine, configure it without killing connections, survive a failover, and migrate data between engines without losing rows.

Difficulty
Priority
aws-05-01Card 1 of 6

RDS versus Aurora PostgreSQL

MediumP3 · Role-dependentTalk about it12 min
rdsaurorapostgresqlmanaged-databasesfailoverhigh-availability

Why you care

Every Python ETL job you write eventually writes to a database. In a product company that database is almost always managed — RDS or Aurora. Picking the wrong one is not a one-time decision: you will pay for it in failover downtime, read-replica costs, or a storage ceiling hit at the worst possible moment. Interviewers ask "RDS or Aurora, and why?" because the answer reveals whether you understand the failure modes or whether you just picked the console default. Half the "job ran but data is missing" incidents in managed Postgres come from not understanding what happens to an open connection during a failover.

Explain like I'm 5

RDS is a managed delivery truck: one driver makes deliveries, a spare driver waits on the side doing nothing. If the first driver has a heart attack, the spare takes fifteen minutes to learn the route, start the truck and get going. Aurora is a shared warehouse: the shelves exist in three buildings at once, every forklift can read from any building, and if the head forklift breaks down, another one is already parked in front of the same shelves and takes over in seconds because the shelves never moved.

Core concept

The fundamental split is storage architecture.

  • RDS PostgreSQL uses an EBS volume attached to the instance. In Multi-AZ mode, AWS keeps a synchronous hot standby in a second AZ with its own EBS copy. The standby does nothing until failover — it cannot serve reads. Failover is DNS-based: the standby is promoted, the DNS record is updated, and application connections must reconnect. This typically takes 60–120 seconds.
  • Aurora PostgreSQL separates compute from storage. The cluster volume stores six copies of every write across three AZs automatically, regardless of how many DB instances you run. There is no separate EBS per instance; every writer and reader accesses the same distributed volume. Read replicas are endpoints, not independent data copies. Failover promotes an existing replica to writer; because the storage never moves, this typically takes under 30 seconds (or under 10 minutes if there are no replicas — always provision at least one).

Key numbers (checked against AWS docs, September 2026; AWS has raised this ceiling before, so confirm the current limit for your engine version before publishing or relying on it):

  • Aurora max cluster volume: 128 TiB on older engine versions; up to 256 TiB on newer ones (Aurora PostgreSQL 15.13+/16.9+/17.5+, Aurora MySQL 3.10+). Auto-grows; no pre-provisioning. Check your cluster's engine version against the current size-limits page before treating either number as a hard ceiling.
  • Aurora max read replicas: 15 per cluster.
  • RDS read replicas: 15 per source (quota page; adjustable).
  • Aurora failover with replica: typically < 30 seconds. Without replica: typically < 10 minutes.
  • RDS Multi-AZ failover: typically 60–120 seconds.

Endpoints matter. Aurora gives you two stable endpoints: the cluster endpoint (always the current writer) and the reader endpoint (round-robins across all read replicas). Always point ETL writers at the cluster endpoint. Never hardcode a replica's instance endpoint — it does not survive a promotion.

Use RDS when cost is the constraint, one-minute failover is acceptable, and you need at most a handful of read replicas. Use Aurora when you need sub-30-second failover, more than five readable replicas, or you are approaching storage limits.

Under the hood

Aurora's quorum write: the writer node sends each 10 GB chunk (a "protection group segment") to six storage nodes. The write is acknowledged after four of the six nodes confirm. This means Aurora can lose an entire AZ (two storage nodes) and still write without pausing. There is no "replica lag" at the storage layer — the data is durable before the writer acknowledges. Read replicas do experience a brief metadata propagation delay (buffer-cache invalidation messages) that can reach low single-digit seconds on very busy writers. That is why a read-after-write check sent to the reader endpoint can return stale data — see Replicas, failover and recovery for how to manage this.

Minimal example RDS vs Aurora decision tree

PythonPick the engine from hard requirementsruns
def recommend_engine(failover_budget_s: int, read_replicas: int, storage_tib: float) -> tuple:
    reasons = []
    if failover_budget_s < 60:
        reasons.append("sub-60-s failover -> Aurora")
    if read_replicas > 5:
        reasons.append(f"{read_replicas} read replicas -> Aurora (max 15)")
    if storage_tib > 64:
        reasons.append(f"{storage_tib} TiB storage -> Aurora (128-256 TiB depending on engine version)")
    if reasons:
        return "Aurora", reasons
    return "RDS", ["no hard constraint; lower cost wins"]


for args in [
    (30, 2, 10),
    (120, 12, 20),
    (120, 3, 70),
    (120, 3, 10),
]:
    engine, why = recommend_engine(*args)
    print(f"failover={args[0]}s replicas={args[1]} storage={args[2]}TiB => {engine}: {why[0]}")
Output
failover=30s replicas=2 storage=10TiB => Aurora: sub-60-s failover -> Aurora
failover=120s replicas=12 storage=20TiB => Aurora: 12 read replicas -> Aurora (max 15)
failover=120s replicas=3 storage=70TiB => Aurora: 70 TiB storage -> Aurora (128-256 TiB depending on engine version)
failover=120s replicas=3 storage=10TiB => RDS: no hard constraint; lower cost wins

In production Endpoint routing for ETL and reporting

PythonDetect writer vs reader endpoint from the hostnameruns
import urllib.parse


def build_dsn(host: str, port: int, db: str, user: str, pw: str) -> str:
    pw_enc = urllib.parse.quote(pw, safe="")
    return f"postgresql://{user}:{pw_enc}@{host}:{port}/{db}?sslmode=require"


def endpoint_role(host: str) -> str:
    if ".cluster-ro-" in host:
        return "aurora-reader"
    if ".cluster-" in host:
        return "aurora-writer"
    return "rds-instance"


# Illustrative hostnames; replace with your cluster endpoints
ENDPOINTS = {
    "etl_write":   "mydb.cluster-abc123.ap-south-1.rds.amazonaws.com",
    "report_read": "mydb.cluster-ro-abc123.ap-south-1.rds.amazonaws.com",
    "rds_primary": "mydb.abc123.ap-south-1.rds.amazonaws.com",
}

for label, host in ENDPOINTS.items():
    dsn = build_dsn(host, 5432, "finance", "etl", "s3cr3t")
    role = endpoint_role(host)
    print(f"{label:12s}: role={role}")
Output
etl_write   : role=aurora-writer
report_read : role=aurora-reader
rds_primary : role=rds-instance

See it happen Aurora storage vs RDS Multi-AZ on failover

sync replicationmetadatametadataRDS primaryEBS copy (standby)RDS standby (idle)-- not present ---- not present --
  1. Aurora writer commits by getting a quorum from the distributed storage layer; replicas get metadata updates after.
  2. Both replicas can serve SELECT queries; the reader endpoint round-robins across them.
  3. Writer fails. Aurora promotes Reader A (highest priority tier). Storage never moves; failover takes under 30 seconds.
  4. Compare RDS Multi-AZ: standby has its own EBS copy, is idle, and becomes available after DNS propagation (60-120 s).
Notice: Aurora storage is shared — replicas are endpoints over the same volume. RDS standby is an independent copy that sits idle.
Scenario A

In your world

The situation

Your Jenkins ETL job connects to an RDS PostgreSQL Multi-AZ instance, truncates the staging table, and loads 4 million rows. The DBA schedules a maintenance window that triggers a failover test at 02:00, while the job runs.

What actually breaks

Failover takes 90 seconds. Your connection pool has a connect timeout of 30 seconds and no retry logic. The truncate commits; the load dies halfway through. Staging is empty. The downstream finance report runs at 06:00 and returns zeros, not an error.

What you'd do on Monday

Add exponential-backoff retry around the load loop. For the long term, switch to Aurora with one replica: the failover finishes in under 30 seconds, before most timeouts expire. Set JDBC connectTimeout=60 and socketTimeout=300.

PythonExponential backoff on connect (needs psycopg + a real DB)
import time
import psycopg2

def connect_with_retry(dsn: str, max_attempts: int = 5) -> psycopg2.extensions.connection:
    delay = 2.0
    for attempt in range(1, max_attempts + 1):
        try:
            return psycopg2.connect(dsn, connect_timeout=60)
        except psycopg2.OperationalError as e:
            if attempt == max_attempts:
                raise
            print(f"attempt {attempt} failed: {e}. Retry in {delay:.0f}s")
            time.sleep(delay)
            delay = min(delay * 2, 30.0)
Scenario B

Imagine you work at Paytm

2M payment rows per night

The numbers

Illustrative: 2M rows × ~500 bytes ≈ 1 GB nightly load per pipeline, plus two reconciliation queries running concurrently from a reporting team. A single RDS Multi-AZ writer handles all three workloads; a long query from reconciliation during the ETL load saturates CPU.

What the Scenario-A answer does here

The retry fix protects the ETL job against a failover, but the reporting traffic stays on the primary writer. Promoting the Multi-AZ standby after a failover gives back one writer — no read offload.

What flips at this scale

At this scale Aurora's reader endpoint pays for itself. Reconciliation queries move to replicas; the ETL writer gets the full CPU. RDS Proxy in front of the cluster endpoint absorbs connection spikes and hides DNS propagation delays from the application.

The trade-off you'd defend

I would choose Aurora over RDS Multi-AZ for any payment pipeline running concurrent analytics against the same DB, accepting the higher per-instance-hour cost in exchange for read-endpoint isolation and sub-30-second failover. The retry logic is identical either way.

Illustrative figures chosen for the arithmetic; not Paytm-published numbers.

Gotchas

Hardcoding the replica's instance endpoint

Symptom

After a failover, writes start failing with "cannot write to a read-only instance."

Cause

The job connects to a specific replica's instance endpoint, which became the writer after promotion. The code is pointed at the wrong role.

Fix

Always use the Aurora cluster endpoint for writes. The cluster endpoint follows the current writer automatically.

RDS Multi-AZ standby treated as a read replica

Symptom

Attempt to read from the standby endpoint returns a connection error or nothing.

Cause

RDS Multi-AZ standby is a failover target only. It does not accept connections for reads. Read offload on RDS requires a separately created read replica.

Fix

Create a read replica explicitly and point reporting traffic at its endpoint, or migrate to Aurora to get the reader endpoint.

Aurora storage growing unexpectedly

Symptom

Storage costs spike even though the data size should be stable.

Cause

Aurora auto-grows the cluster volume but does not auto-shrink below the high-water mark in older engine versions. Large deletes or DROP TABLE free logical space but not billing space until the cluster is optimised or rebuilt.

Fix

Use Aurora I/O-Optimised storage if write-heavy, or run an Aurora clone and switch to reduce the billing volume size. Check the current engine version's shrink behaviour before planning.

Tricky questions

Can the Aurora reader endpoint become unavailable during a failover?
Most people say

No, the reader endpoint always works because replicas are available.

Actually

During the promotion of a replica to writer, that specific replica is temporarily unavailable as a reader. The reader endpoint stops routing to it briefly. If your cluster has only one replica, the reader endpoint has no target during the promotion window. Provision at least two replicas to ensure the reader endpoint stays available.

Is RDS Multi-AZ failover instantaneous once the standby is in sync?
Most people say

Yes, the standby is always in sync so failover is immediate.

Actually

The DNS record must propagate, and application connection pools must reconnect. Even with an already-synced standby, the application sees a 60–120 second window where new connections fail. Retry logic is mandatory.

Scenario question

You're handed a report saying a Python ETL job failed three times last quarter due to database failovers. The job runs 45 minutes. The RDS instance is Multi-AZ. You cannot change the job code. How do you fix the failure rate?

Model answer

I would migrate the endpoint to Aurora PostgreSQL and provision at least one read replica. The failover time drops from 60-120 seconds to under 30 seconds, and the Aurora cluster endpoint follows the promotion automatically. Even without touching the job code, the shorter failover window means a standard 60-second JDBC connect timeout survives. I would also add an RDS Proxy in front of the cluster endpoint, because the proxy maintains a pooled connection on behalf of the application and shields it from DNS propagation during failover. The trade-off is cost: Aurora and the proxy both bill per instance-hour. If the budget disallows it, the minimum fix is to increase the JDBC socketTimeout to 90 seconds, which buys survival for the current 60-120s window at the risk of slower error detection on genuine query problems.

Interview corner

CommonWhat is the key architectural difference between RDS PostgreSQL and Aurora?
“I'd say RDS uses EBS per instance with a separate standby copy in Multi-AZ. Aurora uses a shared distributed storage layer, six copies across three AZs, so all compute nodes access the same volume. That is why Aurora failover takes under 30 seconds and RDS takes 60-120: the storage never moves.”
TrickYou point your ETL job at an Aurora replica's instance endpoint. After a failover it starts reporting "read-only instance." Why?
“I'd say the replica was promoted to writer. Its instance endpoint stayed the same, but now it accepts writes. The previous writer's endpoint is gone. The real fix is to always use the Aurora cluster endpoint for writes, never a replica's instance endpoint.”
Experience checkA failover happened during your ETL job. How long was the data gap?
“With RDS Multi-AZ, I'd estimate the job was down for 60-120 seconds. Without retry logic, the job failed and staging was empty. After adding backoff retry, the job reconnected and the gap was near zero because the transaction was rolled back before the retry.”
DesignWhen would you choose RDS over Aurora for PostgreSQL?
“I'd choose RDS when the workload is small, a 60-second failover is acceptable, and I want to keep costs down. Aurora's storage pricing is per GB-month of the distributed volume, which costs more than provisioned EBS for small databases. For a dev environment or a low-traffic internal tool, RDS Multi-AZ is the right answer.”

Memory hook

The RDS spare driver has to learn the route before it can drive; the Aurora forklift is already parked at the same shelves. That is the whole difference in failover time.

If you remember only one thing:

Use the Aurora cluster endpoint for writes. A replica's instance endpoint does not survive promotion.

Self-check

  1. How many storage copies does Aurora maintain, and how are they distributed?

    Six copies across three Availability Zones, stored in the cluster volume regardless of how many instances you run.

  2. What is the typical Aurora failover time with at least one replica versus without any replica?

    Under 30 seconds with a replica; up to 10 minutes without one, because Aurora must recreate the primary instance.

  3. Can the RDS Multi-AZ standby serve read queries?

    No. It is a failover target only. To offload reads from RDS, you need a separately created read replica.

Prove it

Using the AWS pricing calculator (https://calculator.aws), find the hourly price for a db.r7g.large Aurora PostgreSQL instance versus a db.r7g.large RDS PostgreSQL Multi-AZ instance in ap-south-1. Calculate the annual cost difference for one writer plus one read replica (Aurora) versus one Multi-AZ pair (RDS). Report both totals.

aws-05-02Card 2 of 6

Parameter groups and connection behavior

HardP3 · Role-dependentTalk about it13 min
rdsparameter-groupsmax-connectionsconnection-poolingrds-proxy

Why you care

The parameter group is the one knob that can silently break a database without any infrastructure change. A DBA changes shared_preload_libraries in a parameter group, the AWS console says "pending-reboot," and nobody reboots. Three weeks later a planned maintenance reboot applies the change and suddenly every connection fails because pg_stat_statements could not load. The more dangerous version: a well-intentioned developer increases max_connections to stop FATAL: remaining connection slots are reserved, and the instance runs out of memory an hour later. Interviewers ask about parameter groups because the answers separate people who have been burned from those who read the docs once.

Explain like I'm 5

A parameter group is the settings file for your managed database, but AWS separates it from the instance. Think of it as a recipe card pinned to the fridge: some recipe changes (like swapping oil for butter) take effect the next time you cook; others (like replacing the oven type) only apply when you buy a new oven. Dynamic parameters are oil-for-butter changes — they apply without a restart. Static parameters are oven-type changes — the instance must reboot to read the new value.

Core concept

Parameter groups come in two scopes:

  • DB parameter group: applies to a single RDS instance or an individual Aurora DB instance.
  • DB cluster parameter group: applies to all instances in an Aurora cluster. Session-level settings go here; instance-specific memory settings stay in the DB parameter group.

Parameters have an apply type:

  • Dynamic: changes take effect immediately when you apply the group (no reboot). Examples: work_mem, log_min_duration_statement, statement_timeout, lock_timeout.
  • Static: changes are staged as pending-reboot. The instance continues running with the old value until it restarts. Examples: max_connections, shared_preload_libraries, max_worker_processes, wal_level.

The max_connections formula for RDS PostgreSQL is documented by AWS as: LEAST(DBInstanceClassMemory / 9531392, 5000) where DBInstanceClassMemory is in bytes, subtracted by OS and RDS management overhead. AWS does not publish the exact overhead percentage per instance class, so treat any number here as an estimate, not the authoritative value. Using a working assumption of roughly 50% overhead on very small instances and roughly 15% on larger ones (my own illustrative estimate, not an AWS-published figure — see the calculator below), a db.t3.micro (1 GiB) lands around 60 connections and a db.m5.large (8 GiB) lands around 766. The formula itself is exact; the overhead applied to raw instance RAM is the approximation. Always verify the actual value with SHOW max_connections after a reboot. Setting max_connections higher than the formula allows causes incompatible-parameters status.

Connection pressure: each Postgres connection consumes roughly 5-10 MB of memory (stack + backend process). At 400 connections on a db.m5.large, you are committing 2-4 GB of RAM to connection overhead before a single query runs. The solution is RDS Proxy or PgBouncer: a connection pooler that multiplexes thousands of application connections onto a small pool of real DB connections.

The parameter group change workflow: create a custom group → modify parameters → associate it with the instance → check apply type:

  • Dynamic: the console says "in-sync" immediately.
  • Static: the console says "pending-reboot"; apply via a maintenance window or a blue/green deployment to avoid unplanned downtime.

Under the hood

Aurora cluster parameter groups have a subtlety: some parameters that are static in RDS are static at the cluster level in Aurora. When you change a cluster parameter group on a running Aurora cluster, only the writer picks up the change immediately for dynamic parameters; reader instances re-read the group independently. For static parameters, each instance needs its own reboot. This can leave the cluster in a mixed state where the writer has the new value and a reader has the old one until all nodes are individually restarted. AWS blue/green deployments handle this atomically: a new cluster is provisioned with the new parameter group, tested, and swapped behind the endpoint.

Minimal example Static vs dynamic parameter check

PythonWhich parameter changes need a reboot?runs
STATIC_PARAMS = {
    "max_connections", "max_worker_processes",
    "shared_preload_libraries", "wal_level",
    "shared_buffers",
}
DYNAMIC_PARAMS = {
    "work_mem", "log_min_duration_statement", "statement_timeout",
    "lock_timeout", "effective_cache_size", "log_connections",
}

def check_apply_mode(changes: dict) -> dict:
    needs_reboot, immediate, unknown = [], [], []
    for param in changes:
        if param in STATIC_PARAMS:
            needs_reboot.append(param)
        elif param in DYNAMIC_PARAMS:
            immediate.append(param)
        else:
            unknown.append(param + " (verify apply type in docs)")
    return {"needs_reboot": needs_reboot, "immediate": immediate, "unknown": unknown}

proposed = {
    "log_min_duration_statement": 500,
    "work_mem": "64MB",
    "shared_preload_libraries": "pg_stat_statements",
    "max_connections": 300,
    "idle_in_transaction_session_timeout": "30000",
}
result = check_apply_mode(proposed)
for category, params in result.items():
    if params:
        print(f"{category}: {params}")
Output
needs_reboot: ['shared_preload_libraries', 'max_connections']
immediate: ['log_min_duration_statement', 'work_mem']
unknown: ['idle_in_transaction_session_timeout (verify apply type in docs)']

In production Connection pressure calculator

PythonConnections vs instance memory: spot the pressure pointruns
import math

def approx_max_connections(gib: float) -> int:
    """
    Applies the AWS-documented formula LEAST(DBInstanceClassMemory/9531392, 5000).
    DBInstanceClassMemory subtracts OS and RDS overhead from raw instance RAM,
    but AWS does not publish the exact overhead percentage per instance class.
    The 0.50 / 0.15 split below is MY OWN illustrative estimate, not an AWS
    figure, chosen only to make small instances show proportionally larger
    overhead than large ones. Use SHOW max_connections on a live instance for
    the authoritative value.
    """
    overhead = 0.50 if gib <= 2 else 0.15   # illustrative estimate, not AWS-published
    available = int(gib * 1024**3 * (1 - overhead))
    return min(available // 9531392, 5000)

def pressure(workers: int, conns_each: int, db_gib: float) -> str:
    total = workers * conns_each
    limit = approx_max_connections(db_gib)
    mem_mb = total * 7  # ~7 MB per Postgres backend, illustrative
    flag = "OVER LIMIT" if total > limit else f"headroom {limit - total}"
    return f"{workers}w x {conns_each} = {total} conns | limit~{limit} | {flag} | ~{mem_mb} MB overhead"

instances = [
    (4,   10,  2, "db.t3.small ETL (baseline)"),
    (20,  20,  2, "db.t3.small ETL (scaled up)"),
    (20,  20,  8, "db.m5.large ETL (scaled up)"),
    (200, 10,  8, "db.m5.large spike (100 app pods)"),
]
for workers, conns, gib, label in instances:
    print(f"{label}: {pressure(workers, conns, gib)}")
Output
db.t3.small ETL (baseline): 4w x 10 = 40 conns | limit~112 | headroom 72 | ~280 MB overhead
db.t3.small ETL (scaled up): 20w x 20 = 400 conns | limit~112 | OVER LIMIT | ~2800 MB overhead
db.m5.large ETL (scaled up): 20w x 20 = 400 conns | limit~766 | headroom 366 | ~2800 MB overhead
db.m5.large spike (100 app pods): 200w x 10 = 2000 conns | limit~766 | OVER LIMIT | ~14000 MB overhead

See it happen Parameter change lifecycle

DBAAWS ConsoleDB InstanceModify parameter groupApply group (static param)Status: pending-rebootTrigger maintenance rebootmax_connections now 400Modify parameter: work_memApply immediatelywork_mem live immediately
  1. DBA changes max_connections from 200 to 400 in the custom parameter group.
  2. AWS applies the group; the instance stores the change as pending-reboot. The running DB still uses max_connections = 200.
  3. The console shows 'pending-reboot'. Applications are unaffected right now.
  4. Two weeks later, planned maintenance reboots the instance.
  5. After reboot the new value is live. If 400 exceeds the formula's ceiling, the instance enters incompatible-parameters and refuses to start.
  6. DBA also changes work_mem (dynamic parameter) in the same group.
  7. Dynamic parameters take effect within minutes, no reboot needed. Status shows in-sync.
  8. Sessions opened after the apply see the new work_mem. Existing sessions see it after reconnecting.
Notice: static and dynamic parameters can coexist in one group change. Only static ones are pending-reboot; you can accidentally apply a mixed change and be surprised when the reboot changes more than you expected.
Scenario A

In your world

The situation

Your ETL Postgres connections suddenly start failing with FATAL: remaining connection slots are reserved for non-replication superuser connections. The DBA checks and sees max_connections = 100. Your Jenkins job runs four parallel workers, each opening 10 connections, plus the monitoring agent and the application server, totalling 85. The DBA bumps max_connections to 250 in the parameter group and hits Apply.

What actually breaks

The change is static. The console says "pending-reboot." Your connections still fail. Three hours later the DBA reboots the instance. Connections recover, but an hour after that the instance shows high memory and the DBA gets an alert: the new 250 connections at ~7 MB each is eating into the memory headroom the query planner needs, and long queries start swapping.

What you'd do on Monday

Short term: add an RDS Proxy in front of the instance. The proxy pools connections and exposes a single endpoint to the application; the actual database sees 10-20 connections at a time, regardless of how many app threads connect. Long term: audit which workers actually need persistent connections versus short-lived ones, and use pg_stat_activity to confirm idle connections are being returned to the pool.

PythonCheck live connection pressure via pg_stat_activity (needs psycopg + DB)
import psycopg2

def connection_audit(dsn: str) -> None:
    with psycopg2.connect(dsn) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT state, count(*) AS n
                FROM pg_stat_activity
                WHERE datname = current_database()
                GROUP BY state
                ORDER BY n DESC
            """)
            for state, count in cur.fetchall():
                flag = " <-- idle connections to close" if state == "idle" else ""
                print(f"  {state or 'null':20s}: {count}{flag}")
Scenario B

Imagine you work at BigBasket

3,000 concurrent connections at checkout peak

The numbers

Illustrative: BigBasket's checkout peak drives ~3,000 concurrent application connections from ~150 app server pods, 20 connections each. A db.r6g.xlarge (32 GiB) has max_connections of roughly 3,100 by the RDS formula. At 3,000 connections the DB backend processes consume ~21 GB of RAM, leaving ~11 GB for shared_buffers and query memory. A burst above 3,000 triggers FATAL: sorry, too many clients and checkout fails.

What the Scenario-A answer does here

The Scenario A approach (RDS Proxy) limits the DB to 50-100 connections regardless of how many pods connect; the proxy queues the rest. This works, but the checkout latency spikes when the proxy queue backs up and requests wait for a DB connection.

What flips at this scale

At this scale you combine two techniques: RDS Proxy with a configured MaxConnectionsPercent (say, 70%) to leave headroom for monitoring and replication, and application-level connection pooling (PgBouncer or SQLAlchemy pool) with a short checkout timeout so backed-up requests fail fast rather than queuing indefinitely.

The trade-off you'd defend

I would defend RDS Proxy plus a 5-second application-level checkout timeout, trading slightly more complex connection configuration for a hard ceiling on DB memory consumption and fast failure signalling when the pool is exhausted. Silently queuing connections indefinitely is worse than a fast error that the load balancer can retry on another pod.

Illustrative numbers to make the arithmetic visible; not BigBasket-published figures.

Gotchas

Applying a parameter group change and expecting it to be immediate

Symptom

You applied a new parameter group with a higher max_connections, but connections still fail with the same limit.

Cause

max_connections is a static parameter; it is pending-reboot until the instance restarts.

Fix

Check the parameter group status. If it says pending-reboot, schedule a maintenance window or use blue/green deployment. max_connections is a static parameter and cannot be overridden at the session level — a reboot or a connection proxy in front of the instance are your only options for relief before the next maintenance window.

Setting max_connections higher than the formula allows

Symptom

After a reboot the instance enters incompatible-parameters status and refuses to start.

Cause

The requested max_connections requires more memory than the formula's ceiling allows.

Fix

Reduce max_connections in the parameter group and reboot again, or move to a larger instance class. Use the formula LEAST(DBInstanceClassMemory/9531392, 5000) as a guide before applying.

Using a default parameter group on production

Symptom

A parameter change is rejected: "You can't modify default parameter groups."

Cause

Default parameter groups are AWS-managed and read-only. Changes must be made to a custom copy.

Fix

Create a custom parameter group from the same family (e.g., aurora-postgresql15), copy the default values, apply your changes, and associate it with the instance before the next reboot.

Tricky questions

Can you apply a static parameter change without a reboot on Aurora?
Most people say

Yes, Aurora has live rolling update for parameter groups.

Actually

No. Static parameters on Aurora still require each DB instance to reboot. Aurora's blue/green deployment can rotate the reboot across nodes to minimise downtime, but a reboot is unavoidable.

Is work_mem applied per query or per connection?
Most people say

Per connection, so 200 connections × 64 MB = 12.8 GB.

Actually

Per sort or hash operation, per query. A single complex query can allocate work_mem multiple times (once per sort node in the plan). At 200 connections each running a query with two sort nodes at 64 MB each, the ceiling is 200 × 2 × 64 MB = 25.6 GB. Set work_mem conservatively for multi-tenant workloads and use SET LOCAL work_mem = '256MB' for specific heavy analytical queries.

Scenario question

You're handed a Postgres RDS instance that intermittently fails with "too many clients." The max_connections is 200, current active connections rarely exceed 150, but the spikes hit 220. You cannot change the application code.

Model answer

I would place RDS Proxy in front of the instance. The proxy pools connections at the DB level: regardless of how many application connections open, the DB sees a bounded pool. I would set MaxConnectionsPercent to 70% (140 connections), leaving 60 for superuser, monitoring, and replication. The proxy handles the 150-to-220 spike by queuing the excess requests with a configurable borrow timeout. If the queue timeout is too long, I would reduce it to fail fast and surface the problem rather than hiding it. The trade-off is an extra network hop through the proxy: latency for short queries increases by a few milliseconds. For this ETL use case that is acceptable; for sub-millisecond OLTP it would need measuring.

Interview corner

CommonWhat is the difference between a static and a dynamic RDS parameter?
“I'd say dynamic parameters take effect immediately when you apply the group; static ones are pending-reboot and the DB continues running with the old value until the instance restarts. max_connections is static, which is why a parameter group change doesn't fix 'too many clients' until a reboot.”
Experience checkHave you ever hit the max_connections ceiling?
“Yes, on an ETL job that scaled from 4 workers to 20. The connection limit was 100 on a db.t3.small and we hit it immediately. The fix was to add an RDS Proxy, which pooled 400 application connections down to 20 real DB connections. We also right-sized the instance after measuring how much RAM the connections actually consumed.”
TrickYou increase max_connections in the parameter group and the DB goes into incompatible-parameters on the next reboot. Why?
“I'd say the new value exceeded what the formula allows for that instance class. The formula is LEAST(DBInstanceClassMemory/9531392, 5000), and DBInstanceClassMemory is smaller than the physical RAM after OS and RDS overhead. The fix is to lower max_connections below the ceiling or move to a larger instance class before rebooting.”
DesignWhen would you choose RDS Proxy over a self-managed PgBouncer?
“I'd choose RDS Proxy when I want a fully-managed solution with IAM authentication, automatic failover connection reuse, and no extra EC2 instance to maintain. PgBouncer gives more control over transaction versus session pooling mode and costs less at high connection counts, but I have to operate it. For a small team on AWS, RDS Proxy is the easier path.”

Memory hook

The recipe card on the fridge can say "butter" all it wants — the oven-type change only happens the next time you buy a new oven. A pending-reboot parameter is that unread recipe card.

If you remember only one thing:

max_connections is static. A parameter group change does not take effect until the next reboot.

Self-check

  1. What does "pending-reboot" mean in the RDS parameter group status?

    The parameter value has been changed in the group but the running DB instance has not yet picked it up. The old value is still in use until the next restart.

  2. What is the approximate max_connections for a PostgreSQL RDS instance with 8 GiB of RAM?

    Roughly 766, using LEAST(DBInstanceClassMemory/9531392, 5000) with an illustrative 15% overhead deduction for OS and RDS management processes. AWS does not publish the exact overhead figure, so this is an estimate — verify with SHOW max_connections on the actual instance.

  3. Why is setting work_mem to 256 MB risky on a high-connection instance?

    Each sort or hash operation in a query allocates up to work_mem. A busy 200-connection instance running complex queries can commit hundreds of times 256 MB, exhausting available RAM and causing swapping or OOM.

Prove it

On a running RDS or Aurora PostgreSQL instance: change log_min_duration_statement to 100 (dynamic) and shared_preload_libraries to add pg_stat_statements (static). Screenshot or copy the parameter group status page showing one parameter as "in-sync" and the other as "pending-reboot." Then run SHOW log_min_duration_statement to confirm the dynamic change is live. Report the values.

aws-05-03Card 3 of 6

Replicas, failover and recovery

HardP3 · Role-dependentTalk about it13 min
rdsauroraread-replicasfailoverlagrecovery

Why you care

A database replica that you have never tested is a liability, not an asset. Replica lag, connection recovery, and failover sequencing are things you can only know if you have measured them. A read replica that is 300 seconds behind the writer is invisible in CloudWatch unless you specifically watch ReplicaLag. A failover that completes in 25 seconds on Aurora can still produce an outage if your application does not retry reconnects — the DNS TTL on your connection pool is cached for 5 minutes. Interviewers ask about replica lag because it is the most common cause of "consistent read that returns stale data" bugs in ETL audit checks.

Explain like I'm 5

A read replica is a copy that follows the writer like a shadow — the shadow always shows where you were a moment ago, not exactly where you are right now. On a very busy day the shadow can fall behind by minutes. If you check your shadow to see if your wallet is in your pocket (a read-after-write check), and the shadow is showing you from ten minutes ago, it will say "wallet is there" even if you just handed it to someone. Always check the real you (the writer) for something you just changed.

Core concept

Three topologies, and what each one does:

  • RDS read replica: an asynchronous replica of an RDS instance. It runs as a separate DB instance, has its own endpoint, and lags behind the writer. The lag is visible in CloudWatch metric ReplicaLag. Under heavy write load, lag can reach minutes. Read replicas on RDS can be promoted to a standalone instance manually, but not automatically on failover — that requires Multi-AZ.
  • RDS Multi-AZ: a synchronous hot standby in a second AZ. It cannot be read from. It is promoted automatically on writer failure. The failover is DNS-based: the CNAME for the primary endpoint switches. Typical time: 60–120 seconds.
  • Aurora replicas: asynchronous replicas sharing the cluster volume. Up to 15 per cluster. Promoted to writer automatically on primary failure in order of priority tier (0 = highest; 15 = lowest; ties break by replica size). Typical failover time with at least one replica: under 30 seconds, often under 20 seconds. Aurora replicas serve reads via the reader endpoint.

Replica lag in Aurora is not the same as in RDS. Aurora's storage layer is shared, so the data is durable before the replica is notified. The lag you observe (AuroraReplicaLag in CloudWatch) is the buffer-cache invalidation propagation delay: the time between the writer committing and the replica refreshing its cache. This is typically under 100 milliseconds, but can reach several seconds on very busy writers.

Connection recovery after failover requires:

  1. Short DNS TTL respect: disable JVM DNS caching (networkaddress.cache.ttl=5) or use RDS Proxy to avoid DNS propagation delays entirely.
  2. Connection retry with backoff in the application.
  3. Aurora: reconnect to the cluster endpoint, not the instance endpoint.

Restore testing: a backup that has never been restored is a promise, not a guarantee. Test a point-in-time restore quarterly in a non-production account and verify that the application can actually start against the restored instance.

Under the hood

Aurora failover sequencing (from AWS docs): when the writer becomes unavailable, the following happens in order. Aurora detects the failure (typically seconds). It selects the highest-priority replica (lowest tier number). It updates the DNS CNAME for the cluster endpoint to point at the promoted replica. The promoted replica's role changes from reader to writer. All other replicas continue serving reads. The demoted-to-failed instance is replaced asynchronously. This is why the reader endpoint briefly loses the promoted replica during the transition — and why you need at least two replicas to keep the reader endpoint continuously available.

Minimal example Lag-based routing decision

PythonRoute reads based on measured replica lagruns
def route_read(lag_seconds: float, max_lag_s: float = 5.0, read_after_write: bool = False) -> str:
    """
    Decide where to route a read query.
    read_after_write=True means the caller just wrote and needs its own write visible.
    """
    if read_after_write:
        return "writer (read-after-write required)"
    if lag_seconds > max_lag_s * 10:
        return "writer (replica too far behind)"
    if lag_seconds > max_lag_s:
        return f"writer (lag {lag_seconds:.1f}s > threshold {max_lag_s}s)"
    return f"replica OK (lag {lag_seconds:.1f}s)"

scenarios = [
    (0.08, False),
    (3.50, False),
    (12.0, False),
    (180.0, False),
    (0.08, True),
]
for lag, raw in scenarios:
    print(route_read(lag, read_after_write=raw))
Output
replica OK (lag 0.1s)
replica OK (lag 3.5s)
writer (lag 12.0s > threshold 5.0s)
writer (replica too far behind)
writer (read-after-write required)

In production Reconnect with backoff after failover

PythonExponential backoff reconnect simulationruns
import time


class FakeDatabase:
    """Simulates a DB that fails then recovers after 2 attempts."""
    def __init__(self):
        self._attempt = 0

    def connect(self):
        self._attempt += 1
        if self._attempt <= 2:
            raise ConnectionRefusedError(f"connection refused (attempt {self._attempt})")
        # sqlite3 as a stand-in for a real Postgres connection
        import sqlite3
        return sqlite3.connect(":memory:")


def connect_with_backoff(db, max_attempts: int = 5, base_delay: float = 0.05):
    delay = base_delay
    for attempt in range(1, max_attempts + 1):
        try:
            conn = db.connect()
            print(f"connected on attempt {attempt}")
            return conn
        except ConnectionRefusedError as e:
            if attempt == max_attempts:
                raise
            print(f"attempt {attempt}: {e}. retry in {delay:.2f}s")
            time.sleep(delay)
            delay = min(delay * 2, 2.0)
    return None  # unreachable


conn = connect_with_backoff(FakeDatabase())
result = conn.execute("SELECT 42").fetchone()
print(f"query result: {result[0]}")
Output
attempt 1: connection refused (attempt 1). retry in 0.05s
attempt 2: connection refused (attempt 2). retry in 0.10s
connected on attempt 3
query result: 42

See it happen Aurora failover sequence

AppCluster endpointWriterReplica Aconnect (write)route to writerFATAL: server closed connectionpromote Replica Areconnect (backoff)route to new writerconnection OK
  1. The application connects through the cluster endpoint, which resolves to the current writer.
  2. All writes land on the writer instance.
  3. Writer fails. In-flight connections receive an error immediately.
  4. Aurora detects failure and promotes the highest-priority replica. Storage does not move.
  5. Application retry fires. The cluster endpoint DNS is already updated to Replica A.
  6. Replica A is now the writer. The connection succeeds, typically within 20-30 seconds of the failure.
  7. The application resumes. No data was lost because Aurora's storage was durable before the failure.
Notice: the cluster endpoint is the only thing the application needs to know. The DNS update happens inside Aurora; the application just needs to retry.
Scenario A

In your world

The situation

Your ETL job reads from an Aurora reader endpoint to generate a daily reconciliation report. The query is SELECT count(*) FROM orders WHERE settled_at >= CURRENT_DATE. The load job runs at 23:55 and finishes at 00:02. The reconciliation job runs at 00:05. Finance raises a ticket: the reconciliation count is 47 lower than the ledger.

What actually breaks

The reconciliation query is hitting the reader endpoint. AuroraReplicaLag is 180 seconds at the time it runs, because a large month-end batch wrote heavily between 23:55 and 00:10. The reader is 180 seconds behind; 47 rows committed after midnight in the real writer are not yet visible in the replica's cache.

What you'd do on Monday

For a read-after-write check that must see the latest data, always route to the writer endpoint (cluster endpoint). Reserve the reader endpoint for queries where eventual consistency is acceptable, such as analytics on yesterday's data.

PythonRead-after-write to writer, analytics to reader (needs psycopg + Aurora)
import psycopg2

WRITER_DSN = "postgresql://user:pw@mydb.cluster-abc.ap-south-1.rds.amazonaws.com/finance"
READER_DSN = "postgresql://user:pw@mydb.cluster-ro-abc.ap-south-1.rds.amazonaws.com/finance"

def get_reconciliation_count(date_str: str) -> int:
    """Critical: use writer to avoid replica lag."""
    with psycopg2.connect(WRITER_DSN) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT count(*) FROM orders WHERE settled_at::date = %s", (date_str,)
            )
            return cur.fetchone()[0]

def get_analytics_summary(date_str: str) -> dict:
    """Non-critical: reader is acceptable, eventual consistency OK."""
    with psycopg2.connect(READER_DSN) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT sum(amount), avg(amount) FROM orders WHERE settled_at::date = %s",
                (date_str,)
            )
            total, avg = cur.fetchone()
            return {"total": total, "avg": avg}
Scenario B

Imagine you work at Grab

50M ride events per day

The numbers

Illustrative: 50M events / 86,400 s ≈ 578 events/s average; peak at 2,000/s during surge. Each event is ~1 KB. At 2,000 writes/s × 1 KB × replication overhead ≈ 2 MB/s of write throughput. Under this load, AuroraReplicaLag can reach 5-15 seconds.

What the Scenario-A answer does here

The Scenario A fix (route reconciliation to the writer) works for critical reads, but at 2,000 writes/s the single writer is the bottleneck. Adding more readers does not help writes. Read-heavy dashboard queries still pile onto the two replicas via the reader endpoint, and at 15 s lag, sessions that require recent data start failing the lag check.

What flips at this scale

At this scale, you separate the access patterns further. Critical read-after-write queries hit the writer. Analytics with a known staleness SLA (e.g., "as of 60 seconds ago is fine") use the reader endpoint with a lag check. If even 60 seconds is too stale, that query belongs on a warehouse (Redshift Serverless) fed by a CDC pipeline, not on an Aurora replica. The Aurora cluster is optimised for transactional writes; analytics belong downstream Redshift Serverless and lakehouse boundaries.

The trade-off you'd defend

I would defend a three-tier read strategy: writer for critical reads, reader endpoint (with lag guard) for semi-real-time reads, and a warehouse for analytics — accepting operational complexity in exchange for protecting the writer from analytical load.

Illustrative figures for the arithmetic; not Grab-published numbers.

Gotchas

Directing a read-after-write check to the reader endpoint

Symptom

A reconciliation query returns a count lower than the source, even though the load job succeeded.

Cause

The reader is behind the writer by seconds to minutes depending on write load. The committed rows are not yet in the replica's buffer cache.

Fix

Route any query that must see data you just wrote to the cluster endpoint (writer). Use the reader endpoint only for eventually-consistent analytics.

Relying on Aurora failover without testing it

Symptom

Aurora promotes a replica in 25 seconds, but the application shows a 5-minute outage.

Cause

The JVM cached the old DNS IP for 300 seconds (JVM default networkaddress.cache.ttl = 0 means "cache forever" in older JDK versions). The connection pool reconnected to the old IP, which was gone.

Fix

Set networkaddress.cache.ttl=5 in the JVM security policy. Alternatively, use RDS Proxy, which maintains persistent connections to the cluster and hides DNS changes from applications entirely.

Restoring a backup without verifying the application starts

Symptom

A point-in-time restore completes successfully but the application cannot start because a required extension is missing or the schema is from an incompatible version.

Cause

A backup is a copy of the data; it does not test that the application stack can use it. Schema migrations applied after the backup point may not be present.

Fix

Restore quarterly to a non-production instance and run the application startup smoke test against it. Document the recovery runbook. Backup strategy and restore evidence covers backup strategy in detail.

Tricky questions

Does Aurora replica lag mean the data is lost?
Most people say

Yes, if a replica is 180 seconds behind, those 180 seconds of data are lost on failover.

Actually

No. Aurora's storage is durable the moment the writer acknowledges a commit — the lag is only the buffer-cache propagation delay to the replica's read cache. All committed data is in the shared storage layer and is available to the promoted writer immediately. No data is lost on failover.

Is RDS Multi-AZ the same as RDS with a read replica?
Most people say

Yes, both are standby instances in another AZ.

Actually

Multi-AZ is a synchronous hot standby for failover only; it cannot serve reads. A read replica is an asynchronous copy that can serve reads but must be manually promoted and is not an automatic failover target. They are separate features that can be combined.

Scenario question

You're handed an Aurora PostgreSQL cluster where a nightly ETL job is failing with "connection reset by peer" at 03:00 every Sunday. The DBA says a routine maintenance reboot happens at 03:00 on Sundays.

Model answer

I would first check whether the cluster uses a Multi-AZ Aurora setup (at least one replica). If it does, maintenance reboots should be rolling — the replica is rebooted first, then the writer fails over to it, then the old writer (now a replica) is rebooted. The job should survive because the failover takes under 30 seconds and the retry logic should absorb it. If the job has no retry logic, that is the root cause: add exponential-backoff reconnect. If the cluster has no replica, the maintenance reboot is a cold restart and the whole cluster is unavailable — the fix is to add a replica. I would also set networkaddress.cache.ttl=5 in the JVM to ensure the reconnect goes to the new writer after failover.

Interview corner

CommonWhat is the difference between an Aurora read replica and an RDS Multi-AZ standby?
“I'd say an Aurora replica shares the cluster storage and serves reads; it is promoted to writer automatically on failure. An RDS Multi-AZ standby has its own EBS copy, serves no reads, and is promoted automatically on failure. They both give you failover, but only the Aurora replica gives you a read endpoint.”
Experience checkHave you ever been bitten by replica lag?
“I hit this on a reconciliation check that ran after an ETL load. It was reading from the Aurora reader endpoint and under heavy load the replica was 180 seconds behind. The check returned counts lower than the ledger. I routed that specific query to the cluster endpoint instead — the reader is only safe for queries where eventual consistency is explicitly acceptable.”
TrickAurora failover takes 25 seconds but the app shows a 5-minute outage. Why?
“I'd suspect DNS caching. The JVM or the connection pool cached the old IP for 300 seconds. Even though Aurora updated the cluster endpoint DNS in 25 seconds, the app kept trying the old IP. The fix is to set networkaddress.cache.ttl=5 in the JVM security policy, or use RDS Proxy which hides DNS changes entirely.”
DesignHow would you test that your Aurora cluster can actually recover?
“I'd use the AWS RDS console to trigger a manual failover, then measure how long the application shows errors and whether the reconnect retry fires correctly. I'd do this in staging first, then in production during a low-traffic window. I'd also run a quarterly restore test of the automated backup to a new cluster and verify the application can start against it.”

Memory hook

Your shadow shows where you stood a moment ago, not where you stand now. Never ask the shadow about something you just did.

If you remember only one thing:

A read-after-write check sent to the reader endpoint can return stale data. Route it to the writer.

Self-check

  1. Why does Aurora failover preserve all committed data even though replicas have lag?

    Because Aurora's distributed storage is durable at the storage layer the moment the writer commits. Replica lag is only the buffer-cache propagation delay; the data is already in the shared volume.

  2. What is the fastest Aurora failover path?

    Promoting an existing replica in the highest priority tier (tier 0). Without any replica, Aurora must create a new primary instance, which takes up to 10 minutes.

  3. What setting in the JVM prevents it from caching the Aurora cluster endpoint's DNS indefinitely?

    networkaddress.cache.ttl=5 in the JVM security policy, which limits DNS caching to 5 seconds.

Prove it

Using the AWS console on a test Aurora cluster: add a second replica, then trigger a manual failover. Record the time from the first connection error to the first successful reconnect by tailing your application logs. Confirm the time is under 30 seconds. Then remove all replicas and trigger another failover: confirm it now takes minutes. Paste the timestamps from both tests.

aws-05-04Card 4 of 6

DynamoDB operational design

HardP3 · Role-dependentTalk about it13 min
dynamodbcapacitywcurcuhot-partitionon-demandprovisioned
Needs: IAM policy evaluation, DynamoDB keys: access-pattern-first single-table modelling

Why you care

DynamoDB will silently throttle you if you overload a single partition, and no alarm fires by default. The per-partition limit is 3,000 RCU and 1,000 WCU — not the table limit, the partition limit. If all your writes land on one partition key (because you used a date as the key, or a fixed status field), you hit that limit at 1,000 writes per second regardless of how many WCUs you provisioned at the table level. Interviewers ask about DynamoDB capacity because it is one of the few AWS services where the failure mode is not an error but a silent ProvisionedThroughputExceededException that shows up as a latency spike in the application, with the data appearing fine in the console.

Explain like I'm 5

A DynamoDB table is a building with many floors (partitions). The elevator (capacity) can carry a certain number of people per minute across the whole building, but each floor also has its own staircase that only holds 1,000 people per minute. You can double the building's total elevator capacity and still find one floor's staircase jammed if everyone decides to visit that floor. Choosing a good partition key means making sure people spread across all the floors, not queue for the same one.

Core concept

DynamoDB table capacity has two modes:

  • On-demand mode (default, recommended by AWS): pay per request. DynamoDB scales to handle any traffic pattern automatically. Use this for unpredictable workloads, new tables, or workloads with large spikes. No capacity planning required.
  • Provisioned mode: you specify RCU and WCU. Cheaper for steady predictable workloads. You can set auto-scaling policies. If traffic exceeds provisioned capacity, DynamoDB throttles with ProvisionedThroughputExceededException.

Capacity units (sourced from AWS DynamoDB documentation, September 2026):

  • 1 WCU: one write of up to 1 KB per second (strongly consistent). Items larger than 1 KB consume ceil(item_size / 1024) WCUs.
  • 1 RCU: one strongly consistent read of up to 4 KB per second, or two eventually consistent reads. Items larger than 4 KB consume ceil(item_size / 4096) RCUs per strongly consistent read.

Per-partition limits (sourced from AWS DynamoDB documentation, September 2026):

  • 3,000 RCU and 1,000 WCU per partition, regardless of total table capacity.

Item size limit: 400 KB per item (check the current DynamoDB service quotas page for the latest).

Hot partition diagnosis: enable CloudWatch Contributor Insights on the table. This shows which partition keys are receiving the most requests and which are being throttled, without any query changes.

A hot partition happens when:

  1. The partition key has low cardinality (e.g., a status field with three values, or a date that everyone writes to).
  2. A power user concentrates traffic on one key.
  3. A single item is updated extremely frequently (status updates on a high-traffic order ID).

Fix strategies: add a random suffix to the partition key (write sharding), composite key with a scatter-gather read, or move the hot item to a separate table or ElastiCache.

IAM: every Lambda or EC2 role that touches DynamoDB needs dynamodb:PutItem, dynamodb:GetItem, dynamodb:UpdateItem, etc. Never dynamodb:* in production. Use resource ARNs scoped to the specific table: arn:aws:dynamodb:REGION:ACCOUNT:table/TABLE_NAME.

Under the hood

DynamoDB partitions data by hashing the partition key. The hash space is divided into N partitions; each partition handles a slice of the hash space. When you exceed 10 GB in a partition or the table's throughput forces a split, DynamoDB adds more partitions (only on-demand or provisioned with auto-scaling). Partitions are never merged back. This is why a table that was heavily written and then quietened down can have more partitions than it needs — you provisioned 10,000 WCU at peak, the table split into 10 partitions, and now at 200 WCU you only use 2 partitions but still have 10. Adaptive capacity in provisioned mode moves unused capacity from underloaded partitions to hot ones, but it cannot exceed the 1,000 WCU per-partition hard limit.

Minimal example WCU and RCU capacity calculator

PythonCompute required capacity units with per-partition checksruns
import math

PER_PARTITION_WCU = 1000   # AWS documented limit (2026-09)
PER_PARTITION_RCU = 3000   # AWS documented limit (2026-09)

def wcu_needed(item_bytes: int, writes_per_second: int) -> int:
    per_item = math.ceil(item_bytes / 1024)
    return writes_per_second * per_item

def rcu_strong(item_bytes: int, reads_per_second: int) -> int:
    per_item = math.ceil(item_bytes / (4 * 1024))
    return reads_per_second * per_item

def rcu_eventual(item_bytes: int, reads_per_second: int) -> int:
    return math.ceil(rcu_strong(item_bytes, reads_per_second) / 2)

cases = [
    ("trade 500B  100w/s",   500,   100, "w"),
    ("trade 500B 1200w/s",   500,  1200, "w"),
    ("order 3KB  200r/s sc", 3072,  200, "rs"),
    ("user  6KB  400r/s ev", 6144,  400, "re"),
]
for label, size, rate, mode in cases:
    if mode == "w":
        needed, limit, unit = wcu_needed(size, rate), PER_PARTITION_WCU, "WCU"
    elif mode == "rs":
        needed, limit, unit = rcu_strong(size, rate), PER_PARTITION_RCU, "RCU(strong)"
    else:
        needed, limit, unit = rcu_eventual(size, rate), PER_PARTITION_RCU, "RCU(eventual)"
    hot = " *** SINGLE-PARTITION HOT KEY RISK" if needed > limit else ""
    print(f"{label}: {needed} {unit}/s{hot}")
Output
trade 500B  100w/s: 100 WCU/s
trade 500B 1200w/s: 1200 WCU/s *** SINGLE-PARTITION HOT KEY RISK
order 3KB  200r/s sc: 200 RCU(strong)/s
user  6KB  400r/s ev: 400 RCU(eventual)/s

In production IAM-scoped policy builder and hot-key shard calculator

PythonGenerate table ARN and compute write-sharding suffix countruns
import math


def table_arn(region: str, account: str, table: str) -> str:
    return f"arn:aws:dynamodb:{region}:{account}:table/{table}"

def index_arn(region: str, account: str, table: str, index: str) -> str:
    return f"{table_arn(region, account, table)}/index/{index}"

def shards_needed(peak_wps: int, per_partition_limit: int = 1000) -> int:
    """Minimum write shards to stay below per-partition WCU limit."""
    return math.ceil(peak_wps / per_partition_limit)

REGION, ACCOUNT, TABLE = "ap-south-1", "123456789012", "trades"
print("Table ARN:", table_arn(REGION, ACCOUNT, TABLE))
print("GSI ARN:  ", index_arn(REGION, ACCOUNT, TABLE, "by-status"))

for peak in [500, 1200, 4500, 9000]:
    shards = shards_needed(peak)
    print(f"peak {peak} writes/s: need at least {shards} shards "
          f"(e.g. append '#{{0..{shards-1}}}' suffix to partition key)")
Output
Table ARN: arn:aws:dynamodb:ap-south-1:123456789012:table/trades
GSI ARN:   arn:aws:dynamodb:ap-south-1:123456789012:table/trades/index/by-status
peak 500 writes/s: need at least 1 shards (e.g. append '#{0..0}' suffix to partition key)
peak 1200 writes/s: need at least 2 shards (e.g. append '#{0..1}' suffix to partition key)
peak 4500 writes/s: need at least 5 shards (e.g. append '#{0..4}' suffix to partition key)
peak 9000 writes/s: need at least 9 shards (e.g. append '#{0..8}' suffix to partition key)

See it happen DynamoDB partition heat and hot-key throttle

partition key2026-09-26#02026-09-26#12026-09-26#22026-09-26#3WCU/s used300300300300statusokokokok
  1. Traffic is evenly distributed across four partitions, all well below the 1,000 WCU per-partition limit.
  2. A new ETL job uses trade_date as the partition key. All writes for today land on one partition.
  3. Traffic spikes and the hot partition exceeds 1,000 WCU/s. DynamoDB returns ProvisionedThroughputExceededException for that partition. The table's total WCU quota is fine.
  4. Write sharding: append a random suffix 0-3 to the date key. Each shard handles 300 WCU/s, all under the limit.
Notice: the table-level capacity quota is not the binding constraint — the per-partition limit is. Contributor Insights in CloudWatch is the fastest way to diagnose which partition is hot.
Scenario A

In your world

The situation

Your ETL job writes processed payment events to a DynamoDB table partitioned by payment_date. It runs at 00:01 each day and writes all of yesterday's 800,000 events in about 12 minutes, peaking at 1,100 writes/s during the first two minutes.

What actually breaks

The writes for payment_date = 2026-09-25 all land on the same partition. At 1,100 writes/s you exceed the per-partition WCU limit of 1,000. DynamoDB throttles the excess with ProvisionedThroughputExceededException. The SDK retries with jitter, but some events are delayed or dropped. The job reports success but 847 events are missing from DynamoDB, which you discover only when a downstream analytics query returns a lower total than the RDS ledger.

What you'd do on Monday

Add a write-shard suffix to the partition key: payment_date#X where X is random.randint(0, 3). Reads must then scatter-gather across all four suffixes for a given date. For analytics, push the scatter-gather into the access pattern or move large-range queries to Athena on S3 rather than DynamoDB.

PythonWrite-sharding partition key generatorruns
import random


def sharded_pk(date_str: str, shards: int = 4) -> str:
    """Append a random shard suffix to distribute writes across partitions."""
    shard = random.randint(0, shards - 1)
    return f"{date_str}#{shard}"

def all_sharded_pks(date_str: str, shards: int = 4) -> list:
    """All partition keys to query for a given date (scatter-gather)."""
    return [f"{date_str}#{i}" for i in range(shards)]

random.seed(42)
for _ in range(5):
    print(sharded_pk("2026-09-25"))

print()
print("Scatter-gather keys:", all_sharded_pks("2026-09-25"))
Output
2026-09-25#0
2026-09-25#0
2026-09-25#2
2026-09-25#1
2026-09-25#1

Scatter-gather keys: ['2026-09-25#0', '2026-09-25#1', '2026-09-25#2', '2026-09-25#3']
Scenario B

Imagine you work at Coinbase

10,000 trade writes per second at market open

The numbers

Illustrative: 10,000 trade writes/s × ~500 bytes/trade = 5 MB/s of writes. Without sharding, all trades for the current minute land on one partition and immediately exceed the 1,000 WCU per-partition limit. With 10 shards, each partition handles 1,000 WCU/s — exactly at the limit, which is risky. A safe design uses 15 shards (667 WCU/s each), leaving headroom for retries.

What the Scenario-A answer does here

The Scenario A fix (4 shards at 1,100 WCU/s peak) works for the ETL job's daily 800k writes. At 10,000/s, 4 shards give 2,500 WCU/s each — two and a half times the per-partition limit. The throttling returns and the SDK retries queue up, adding latency at exactly the moment the market is most active.

What flips at this scale

At exchange-scale write rates, you also need to reconsider whether DynamoDB is the right store for real-time trade ingestion at all. Kinesis Data Streams absorbs 10,000 writes/s per shard without per-partition semantics, and a Lambda consumer can fan out to DynamoDB with controlled batching. DynamoDB excels at low-latency point lookups; it is not the natural home for a high-throughput time-series write path. Kinesis versus Firehose and queues covers Kinesis as an alternative.

The trade-off you'd defend

I would defend 15 shards with on-demand capacity for the initial design, accepting scatter-gather reads, and move to Kinesis + DynamoDB if the write latency at market open is unacceptable. On-demand removes the provisioned-capacity guessing game during market-hours spikes.

Illustrative figures; not Coinbase-published numbers.

Gotchas

Using a low-cardinality attribute as the partition key

Symptom

DynamoDB throttles even though the table has plenty of provisioned WCU.

Cause

All writes concentrate on a few partitions (e.g., status = "pending"), each of which hits the 1,000 WCU limit.

Fix

Choose a high-cardinality attribute as the partition key (user ID, order ID) or add a shard suffix. Move range scans to a GSI or a warehouse.

Assuming ProvisionedThroughputExceededException means the table is too small

Symptom

You double the table-level WCU and throttling continues.

Cause

The issue is per-partition concentration, not table-level capacity. Adaptive capacity may mask it temporarily.

Fix

Enable Contributor Insights to identify the hot partition keys, then reshard or redesign the access pattern.

Provisioned capacity decrease limit

Symptom

Attempt to decrease WCU fails with a quota error mid-day.

Cause

DynamoDB allows up to 27 provisioned-capacity decreases per table per day (4 available at start of day, +1 per hour, max 4 banked). Exceeding this triggers an error.

Fix

Plan large decreases for low-traffic periods. On-demand mode has no decrease quotas.

Tricky questions

Does on-demand mode have no per-partition limits?
Most people say

Yes, on-demand scales infinitely because you pay per request.

Actually

On-demand still has per-partition limits (3,000 RCU and 1,000 WCU per partition). What on-demand removes is the table-level provisioned ceiling. A hot-partition design will still throttle on on-demand, just at a different ceiling. The per-table default quota in on-demand mode is 40,000 RCU and 40,000 WCU (check the current service quotas page).

Is a DynamoDB item size limit 4 KB?
Most people say

Yes, the same as one RCU.

Actually

The RCU unit is 4 KB — but the maximum item size is 400 KB. Items between 4 KB and 400 KB are valid; they just consume multiple RCUs per read. The 400 KB limit is on the item, not the read unit.

Scenario question

You're handed a DynamoDB table with trade_date as the partition key. Writes are 1,500/s at market open and 50/s the rest of the day. Reads are a mix of lookups by trade ID (need to be fast) and scans of all trades for a date range (for analytics). The table is throttling at market open and analytics queries are timing out.

Model answer

I would split the access patterns. For the write-heavy market-open window, I would add a shard suffix #0–#4 to trade_date, spreading 1,500/s across 5 partitions at 300 WCU/s each, well under the 1,000 limit. For the trade ID lookup, I would add a GSI with trade_id as the partition key — the GSI is cheap because lookups are infrequent. For the date-range analytics, I would not use DynamoDB at all: enable DynamoDB Streams, write events to S3 via Firehose, and query with Athena. DynamoDB is optimised for point access; analytics scans belong in a data lake. The trade-off is that the scattered partition keys require a scatter-gather for any date-level aggregation outside of Athena.

Interview corner

CommonWhat is the per-partition throughput limit in DynamoDB?
“I'd say 3,000 RCU and 1,000 WCU per partition, from the AWS documentation. This is separate from the table-level provisioned capacity. A hot partition key can hit this limit even if the table has 100,000 WCU provisioned.”
TrickYou doubled your DynamoDB provisioned WCU but throttling continues. What do you check?
“I'd enable Contributor Insights to see which partition keys are hot. Doubling the table-level capacity does not help if traffic concentrates on one partition and hits the per-partition 1,000 WCU limit. The fix is write sharding or a better partition key.”
Experience checkHow have you avoided hot partitions in real ETL work?
“I used a trade_date as the partition key once, and everything landing on today's key hit the per-partition limit immediately. I added a random 0-3 suffix to the key, which spread writes across four partitions. Reads then needed a scatter-gather across the four keys for any date query, which was acceptable for our access pattern.”
DesignWhen would you choose DynamoDB on-demand versus provisioned?
“I'd choose on-demand for unpredictable or spiky workloads, new tables where I don't know the access rate, and any table that has significant peaks. Provisioned is cheaper when I can forecast steady throughput and set auto-scaling, but I still hit per-partition limits regardless of mode. On-demand removes the table-level ceiling but not the partition-level one.”

Memory hook

Doubling the building's elevator capacity does nothing for the one floor everyone is queuing on. DynamoDB throttles the staircase, not the building.

If you remember only one thing:

Per-partition limit: 3,000 RCU and 1,000 WCU. All-on-one-key designs always lose here.

Self-check

  1. What is the per-partition WCU and RCU limit in DynamoDB?

    1,000 WCU per second and 3,000 RCU per second per partition (AWS documentation, September 2026; check the current service quotas page).

  2. How many WCUs does a 3 KB item write consume?

    ceil(3072 / 1024) = 3 WCUs per write.

  3. What AWS tool shows which DynamoDB partition keys are receiving the most traffic?

    CloudWatch Contributor Insights, enabled on the table.

Prove it

Create a DynamoDB table (on-demand) with date as the partition key. Write 1,200 items in a tight loop using the same date value. Enable CloudWatch Contributor Insights and take a screenshot showing the hot partition. Then repeat with a sharded key (date#0 through date#3, random suffix) and show the Contributor Insights graph is now flat. Report the throttle count from both runs.

aws-05-05Card 5 of 6

ElastiCache and cache failure

MediumP3 · Role-dependentTalk about it14 min
elasticacheredisvalkeycachingfailovercache-loss
Needs: RDS versus Aurora PostgreSQL, Cache patterns and invalidation contracts

Why you care

ElastiCache is the managed Redis-compatible layer in front of RDS that keeps your ETL's read-heavy reporting queries from hammering the database. The mistake is treating it as a set-and-forget component. Managed Redis has two failure modes that are invisible without a test: a primary node failure, during which every cache read is a miss and your database absorbs 100% of the traffic until a replica is promoted; and a cluster restart that flushes all keys, causing a cold-cache stampede that can overwhelm the database. Interviewers ask about ElastiCache failure because it is the one managed service where "it was cached" and "it was unavailable" look identical from the application's point of view.

Explain like I'm 5

ElastiCache is a whiteboard next to your database. Instead of digging through the database filing cabinet every time someone asks "what is the current price of item 42?", you write the answer on the whiteboard. If the question comes in again in the next five minutes, you read from the whiteboard — fast. But if someone erases the whiteboard (cache flush) or the whiteboard gets stolen (node failure), every question hits the filing cabinet again simultaneously. If the filing cabinet cannot handle that many simultaneous requests, it falls over too.

Core concept

ElastiCache offers two operating models:

  • ElastiCache Serverless: create a cache in under a minute, no node sizing, no cluster design. Auto-scales on memory, compute and network. Compatible with Valkey 7.2+ and Redis OSS 7.1+ (AWS documentation, September 2026). Priced per ECU (ElastiCache Compute Unit) and per GB-hour of data stored. Use this for new workloads or variable traffic.
  • Node-based cluster: choose node type, number of primaries and replicas, AZ placement. More control over cost and topology. Supports Valkey, Redis OSS and Memcached. For Valkey node-based clusters, you can enable durability — data is persisted in a distributed transaction log and replicas recover independently after a node failure.

Valkey vs Redis OSS: Amazon ElastiCache now supports Valkey (the open-source Redis fork maintained by the Linux Foundation) as the primary Redis-compatible engine. For new deployments, AWS recommends Valkey. Existing Redis OSS clusters continue to work. The APIs are compatible.

Cluster mode (sharded mode): partitions the keyspace across multiple shards (primary+replicas). Horizontal scaling. More complex than a single-shard setup; requires the client to be cluster-aware.

Single-shard with replicas: one primary, up to 5 read replicas. With Multi-AZ enabled, a primary failure promotes the replica with the least replication lag, and AWS documents writes resuming "typically just a few seconds" later — not a published SLA, so verify with a TestFailover run on your own cluster. Without Multi-AZ, ElastiCache recreates the primary from scratch instead, which takes considerably longer.

Access controls: ElastiCache for Valkey/Redis supports:

  • Network isolation: place the cache in a private subnet, not accessible from the internet.
  • VPC security groups: allow only the application's security group on port 6379.
  • Auth tokens (password) or IAM authentication (Valkey/Redis OSS 7.x): prefer IAM.
  • Encryption in transit (TLS) and at rest.

Cache failure handling: your application must implement a fallback to the database when the cache is unavailable. A cache miss and a cache error should both trigger a database read, not a 500 error. The pattern is: try cache → on miss or error, read DB → populate cache before returning. Cache patterns and invalidation contracts covers cache patterns in detail.

Under the hood

ElastiCache Multi-AZ failover uses asynchronous replication between the primary and replicas, similar to Aurora read replicas but with Redis semantics. When the primary fails, the replica with the smallest replication backlog is promoted. Unlike Aurora, there is no shared storage layer, so data written to the primary after the last replication ACK is lost. For Redis/Valkey, this is accepted trade-off: the cache is a performance optimisation, not the source of truth. If your cache holds data that cannot be recomputed (e.g., user sessions), enable Redis persistence (AOF or RDB snapshots) and test recovery from a snapshot. For pure cache use cases, cache loss is expected and the DB fallback is the recovery path.

Minimal example TTL expiry and stale-hit simulation

PythonCache get/set with TTL expiryruns
import time


class TtlCache:
    """Simulates ElastiCache TTL behaviour without a network."""
    def __init__(self):
        self._store: dict = {}

    def set(self, key: str, value, ttl_seconds: float) -> None:
        self._store[key] = (value, time.monotonic() + ttl_seconds)

    def get(self, key: str):
        entry = self._store.get(key)
        if entry is None:
            return None, "miss"
        value, expires_at = entry
        if time.monotonic() > expires_at:
            del self._store[key]
            return None, "expired"
        return value, "hit"


cache = TtlCache()
cache.set("price:item42", 299.0, ttl_seconds=0.1)

v, status = cache.get("price:item42")
print(f"immediate: {status} value={v}")

time.sleep(0.15)

v, status = cache.get("price:item42")
print(f"after ttl: {status} value={v}")

v, status = cache.get("price:item42")
print(f"2nd miss:  {status} value={v}")
Output
immediate: hit value=299.0
after ttl: expired value=None
2nd miss:  miss value=None

In production Cache-aside with DB fallback

PythonCache-aside pattern with error-safe DB fallback (sqlite stands in for Postgres)runs
import sqlite3


class CacheAside:
    """Cache-aside with a fallback to the DB on cache miss or error."""
    def __init__(self, cache: dict, db):
        self._cache = cache
        self._db = db
        self.hits = self.misses = self.errors = 0

    def get_price(self, item_id: int, ttl_seconds: float = 300.0) -> float:
        cache_key = f"price:{item_id}"
        try:
            if cache_key in self._cache:
                self.hits += 1
                return self._cache[cache_key]
        except Exception:
            self.errors += 1   # cache unavailable — fall through to DB

        # DB fallback
        row = self._db.execute(
            "SELECT price FROM items WHERE id = ?", (item_id,)
        ).fetchone()
        price = float(row[0]) if row else 0.0
        try:
            self._cache[cache_key] = price   # populate cache for next caller
        except Exception:
            pass  # cache still unavailable; serve from DB anyway
        self.misses += 1
        return price


# Set up a tiny SQLite "database"
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE items (id INTEGER PRIMARY KEY, price REAL)")
db.executemany("INSERT INTO items VALUES (?, ?)", [(1, 199.0), (2, 499.0)])

cache = {}
service = CacheAside(cache, db)

print(service.get_price(1))   # miss -> populates cache
print(service.get_price(1))   # hit
print(service.get_price(2))   # miss
print(f"hits={service.hits} misses={service.misses}")
Output
199.0
199.0
499.0
hits=1 misses=2

See it happen Cache failure and cold-cache stampede

GET price:*cached valueasync replicateDB fallbackpromoteApp servers(50 pods)ElastiCacheprimaryElastiCachereplicaRDS Proxy shields DB
  1. Normal operation: all 50 pods read from the cache primary. DB sees <5% of requests.
  2. Cache primary fails. All 50 pods miss simultaneously and fall back to the DB.
  3. Cold-cache stampede: 50 pods × 10 req/s = 500 req/s suddenly hitting RDS. If RDS max_connections = 200, connections are refused. Outage cascades.
  4. Failover: the replica is promoted. AWS documents this as typically just a few seconds with Multi-AZ, though it is not a fixed SLA. Cache returns empty. DB absorbs full load until the cache warms up.
  5. With RDS Proxy: connection pooling limits DB connections to a safe pool regardless of how many pods miss simultaneously. Stampede is absorbed.
Notice: the cold-cache stampede is not a cache problem — it is a database capacity problem triggered by a cache event. RDS Proxy or a connection pooler is the right mitigation.
Scenario A

In your world

The situation

Your ETL pipeline caches daily price snapshots in ElastiCache with a 24-hour TTL. Each pod queries GET price:{item_id} before writing to Postgres. There are 200,000 unique item IDs. At 08:00 every morning, all TTLs expire simultaneously (because the cache was warm-loaded at 08:00 yesterday), and 50 pods simultaneously miss.

What actually breaks

All 50 pods fall back to Postgres at 08:00. The DB handles 8,000 queries in the first 30 seconds. CPU spikes to 100%, queries queue, and ETL jobs start timing out. The cache is cold again until the pods warm it up organically, which takes 20 minutes.

What you'd do on Monday

Use cache stampede jitter: when writing to the cache, add a random 0–3600 second jitter to the TTL so that keys expire throughout the day, not all at once. Also cap the fallback with a circuit breaker: if the DB is slow, return a stale value (if available) rather than queuing more queries.

PythonTTL jitter to avoid synchronized expiryruns
import random


def jittered_ttl(base_ttl_seconds: int = 86400, jitter_seconds: int = 3600) -> int:
    """Add a random jitter to distribute cache expiry across a time window."""
    return base_ttl_seconds + random.randint(0, jitter_seconds)

random.seed(99)
ttls = [jittered_ttl() for _ in range(5)]
for t in ttls:
    print(f"TTL = {t}s ({t / 3600:.2f} hours)")
Output
TTL = 88054s (24.46 hours)
TTL = 87959s (24.43 hours)
TTL = 87219s (24.23 hours)
TTL = 88855s (24.68 hours)
TTL = 87132s (24.20 hours)
Scenario B

Imagine you work at Urban Company

500k booking lookups per hour

The numbers

Illustrative: 500k lookups/hour ≈ 139/s average; peak at 500/s during evening booking surge. Without a cache, 500 DB reads/s on db.t3.medium (2 GiB, ~120 max_connections) saturates connections within seconds. With a 5-minute TTL cache and 90% hit rate, DB load drops to 50/s — well within limits.

What the Scenario-A answer does here

The Scenario A fix (TTL jitter) distributes expiry, but a single ElastiCache primary node going down still flushes all keys. The 500/s surge at failover moment hits the DB at 100%. With a small instance, max_connections is the first casualty.

What flips at this scale

At this scale you need both TTL jitter for expiry distribution and RDS Proxy with MaxConnectionsPercent capped at 70%, so the DB never accepts more connections than it can handle regardless of cache state. Add at least one ElastiCache replica with Multi-AZ so failover is automatic — AWS documents promotion as typically a few seconds, not a guaranteed SLA — and still plan for reads falling back to the DB during that window.

The trade-off you'd defend

I would defend ElastiCache Multi-AZ plus RDS Proxy as the baseline production setup, accepting the additional per-node cost of a replica and the proxy, in exchange for an automatic failover path and a hard DB connection ceiling. I would not rely on the cache alone to protect the DB.

Illustrative figures; not Urban Company-published numbers.

Gotchas

Cache failure causing a database cascade

Symptom

ElastiCache failover triggers a database outage. The DB was fine before.

Cause

The cold-cache stampede: all pods fall back to the DB simultaneously, exhausting connections or CPU.

Fix

RDS Proxy with a bounded MaxConnectionsPercent, plus circuit-breaker logic that returns stale values or a graceful error when the DB is slow, rather than queuing indefinitely.

No authentication on ElastiCache

Symptom

ElastiCache is reachable from any EC2 instance in the VPC, not just the application.

Cause

The cluster was created without a security group restriction or auth token.

Fix

Use a VPC security group that allows port 6379 only from the application's security group. Enable TLS and IAM authentication (Valkey 7.2+ or Redis OSS 7.x). Never allow public access.

Storing non-cacheable state in ElastiCache (sessions with no persistence)

Symptom

Users are logged out after a node failure or cluster restart.

Cause

Session tokens were stored only in ElastiCache with no persistence. A cluster restart or failover flushes all keys.

Fix

For sessions that must survive a cache restart, enable Valkey durability (node-based cluster) or persist sessions to RDS/DynamoDB and use ElastiCache only as a performance layer, not the source of truth.

Tricky questions

Does ElastiCache Multi-AZ mean zero data loss on failover?
Most people say

Yes, Multi-AZ replicates synchronously so no data is lost.

Actually

No. ElastiCache replication is asynchronous. Data written to the primary after the last replication acknowledgement is lost when the primary fails. For a pure cache, this is acceptable — the DB is the source of truth. For sessions or rate-limit counters, this data loss can cause user-visible issues.

Does ElastiCache Serverless support all Redis commands?
Most people say

Yes, it is fully compatible.

Actually

ElastiCache Serverless supports a large but not complete subset of Redis commands. Certain commands (like KEYS *, SCAN on large keyspaces, SORT) behave differently or are discouraged. Check the current AWS documentation for the supported command list before migrating a workload that uses advanced Redis features.

Scenario question

You're handed a Python ETL service that uses ElastiCache to cache RDS query results for 1 hour. In the last month, there have been two ElastiCache-related incidents: one where the primary failed and the DB was overwhelmed, and one where all TTLs expired at 06:00 simultaneously because the cache was warm-loaded at 05:00 yesterday. How do you fix both incidents?

Model answer

I would address the two independently. For the TTL-expiry stampede, I would add random jitter of up to 30 minutes to every TTL so keys expire throughout the day, not simultaneously. For the primary-failure stampede, the real problem is that the DB has no protection against a sudden 100% cache miss. I would add an RDS Proxy with MaxConnectionsPercent set to 70%, hard-limiting DB connections regardless of how many pods retry. I would also enable Multi-AZ on ElastiCache — AWS documents promotion as typically a few seconds, not a published SLA — and run TestFailover against the actual cluster to measure the real window before deciding whether it is acceptable. If it is not, I would consider ElastiCache Serverless, trading higher steady-state cost for an abstracted failover path.

Interview corner

CommonWhat is the cold-cache stampede and how do you prevent it?
“I'd describe it as the moment after a cache flush or failover when all clients miss simultaneously and fall back to the database, which was sized for cache-hit traffic. Prevention: an RDS Proxy or connection pooler that caps DB connections so the burst is absorbed rather than passed through. For expiry stampedes, TTL jitter distributes the expiry across time.”
TrickElastiCache Multi-AZ replication is synchronous, right, so no data is lost on failover?
“I'd say it's actually asynchronous, like Aurora read replicas. The data written after the last replication ACK can be lost when the primary fails. For a cache that's backed by a DB, that's fine — you just re-read from the DB. For sessions stored only in ElastiCache, that's a user-visible logout, which is why I'd use Valkey durability or store sessions in RDS instead.”
Experience checkHave you handled a cache failure in production?
“Yes. An ElastiCache primary was terminated by a spot interruption. Our pods fell through to the DB, and within 10 seconds the DB was at max_connections. The fix we put in place: RDS Proxy with a 70% connection cap, and a circuit breaker that returns a cached stale value for up to 60 seconds during DB slowness. After that incident we also enabled Multi-AZ on ElastiCache.”
DesignWhen would you use ElastiCache Serverless versus a node-based cluster?
“I'd use Serverless for new workloads where I can't predict the traffic pattern, or for variable traffic that has large spikes. It removes node sizing and failover management entirely. I'd use a node-based cluster when I have a predictable steady throughput and want to control costs tightly, or when I need Valkey durability features that Serverless doesn't yet expose. Check the current feature parity before choosing.”

Memory hook

When the whiteboard gets erased, every question goes straight to the filing cabinet at once. Size the filing cabinet for that day, not just for the quiet ones.

If you remember only one thing:

Always fall back to the database on a cache error. Never let a cache outage cascade into a 500.

Self-check

  1. What is the approximate failover time for an ElastiCache Multi-AZ cluster when the primary fails?

    AWS documents this as "typically just a few seconds" once the replica promotion completes — it is not a published SLA. During that window, write operations fail and read misses fall back to the database. Without Multi-AZ, ElastiCache must recreate the primary from scratch, which takes considerably longer.

  2. What causes a cold-cache stampede?

    A cache flush, failover, or simultaneous TTL expiry causes all clients to miss at the same time and fall back to the database, which may not be sized for that load.

  3. What ElastiCache engine versions support ElastiCache Serverless?

    Valkey 7.2 and higher, and Redis OSS 7.1 and higher (AWS documentation, September 2026; check the current page for updates).

Prove it

Using a local Redis instance (or a short-TTL ElastiCache test cluster), write 100 keys with the same TTL and measure the miss rate at expiry versus 100 keys with TTL jitter. Plot the miss rate per second for both scenarios in a simple text histogram. Then write a circuit breaker function that returns a stale value if the cache is unavailable, and show it works by deleting all keys and calling get_price with and without the circuit breaker.

aws-05-06Card 6 of 6

DMS and database selection

HardP3 · Role-dependentTalk about it14 min
dmsmigrationcdcdatabase-selectioncutover

Why you care

AWS Database Migration Service moves data between database engines without a full outage — but it has one failure mode that catches every team the first time: the full load and CDC (change data capture) phases are separate, and application writes during the full load can leave the target subtly inconsistent. Row counts match; the data is wrong. That is why DMS has built-in validation, and why no team should cut over without running it. Interviewers also ask "which database would you use here?" because the RDS, Aurora, DynamoDB or ElastiCache choice sets your operational model for years.

Explain like I'm 5

Migrating a database with DMS is like moving a library while the library is still open. Phase one: you photocopy every book and move the copies to the new building (full load). Phase two: while you are copying, new books arrive and old ones are returned — DMS tracks those changes (CDC) and applies them to the new building too. Before you open the new building to readers (cutover), you check that every book in the old and new buildings matches. If even one page is different, you keep the old building open until you find out why.

Core concept

DMS has three required components:

  1. Replication instance: an EC2-like managed compute node that runs the migration. Size it for your data volume; a dms.t3.medium can saturate on a large LOB (large object) migration.
  2. Source endpoint: the connection DMS uses to read from the source. At least one endpoint must be on an AWS service.
  3. Target endpoint: the connection to the destination.

Task types:

  • Full load: copies all existing data from source to target. No changes captured during the copy.
  • Full load + CDC: copies existing data, then switches to reading the source's change log (binary log for MySQL, logical replication slot for PostgreSQL) and applies changes continuously. This is the standard migration pattern.
  • CDC only: assumes the target already has a consistent baseline; only applies changes. Used for ongoing replication after a full load.

PostgreSQL CDC requirement: enable logical replication on the source (wal_level = logical). This is a static RDS parameter — requires a reboot of the source before DMS can start CDC. Plan this reboot into the migration schedule.

Data validation: DMS can compare row counts and spot-check values between source and target. Enable it on the task. Do not skip validation before cutover. Common validation failures: timezone differences (source in UTC+5:30, target in UTC), character encoding mismatches, truncated NUMERIC values.

Cutover: stop writes to the source, wait for CDC lag to reach zero (all changes applied), run a final row-count check, then switch the application connection string to the target. The outage window is the time from stopping writes to the connection string switch — typically minutes if CDC lag is near zero.

Database selection guide (when to use each):

NeedEngine
Relational, complex queries, ACID, Postgres-compatibleRDS PostgreSQL or Aurora PostgreSQL
Same as above, sub-30s failover, >5 replicas, 128-256 TiB (engine-dependent)Aurora PostgreSQL
Single-digit millisecond point lookups at any scale, no joinsDynamoDB
Read-through cache, sub-millisecond, ephemeralElastiCache (Valkey/Redis OSS)
Cache that must survive restarts, sessionsElastiCache with Valkey durability or DynamoDB
Analytical scans, TB-scale reportingRedshift Serverless Redshift Serverless and lakehouse boundaries or Athena Athena, Parquet and partition economics

Under the hood

DMS reads PostgreSQL changes via logical replication slots. A replication slot is a cursor in the WAL stream that DMS holds open. If DMS is down or slow, the slot accumulates WAL segments on the source, consuming disk. On a busy source, an idle or slow DMS task can fill the disk within hours. Monitor pg_replication_slots on the source and set max_replication_slots in the parameter group to cover your migration plus any existing replicas. Drop the slot immediately after cutover — leaving it open is a disk bomb.

Minimal example DMS full-load time and row-count validation

PythonEstimate DMS duration and validate row countsruns
import math


def estimate_full_load(rows: int, rows_per_second: int = 5000) -> dict:
    """
    Illustrative estimate. Real throughput depends on instance class,
    LOB columns, indexes on the target, and network. Check AWS DMS sizing
    guide and benchmark on a test instance.
    """
    seconds = math.ceil(rows / rows_per_second)
    return {"rows": rows, "seconds": seconds, "minutes": round(seconds / 60, 1)}


def validate_counts(source: int, target: int) -> str:
    if source == target:
        return f"PASS: {source} rows"
    delta = abs(source - target)
    pct = delta / source * 100
    return f"FAIL: delta {delta} rows ({pct:.2f}%) — do not cut over"


for label, rows in [
    ("small  (100k)",  100_000),
    ("medium (10M)",  10_000_000),
    ("large  (500M)", 500_000_000),
]:
    e = estimate_full_load(rows)
    print(f"{label}: ~{e['minutes']} min at 5k rows/s")

print()
for src, tgt in [(1_000_000, 1_000_000), (1_000_000, 998_000), (1_000_000, 950_000)]:
    print(validate_counts(src, tgt))
Output
small  (100k): ~0.3 min at 5k rows/s
medium (10M): ~33.3 min at 5k rows/s
large  (500M): ~1666.7 min at 5k rows/s

PASS: 1000000 rows
FAIL: delta 2000 rows (0.20%) — do not cut over
FAIL: delta 50000 rows (5.00%) — do not cut over

In production Database selection decision tree

PythonPick the right AWS database engine for a given requirement setruns
def select_database(
    needs_sql: bool,
    max_latency_ms: float,
    writes_per_second: int,
    needs_joins: bool,
    data_is_ephemeral: bool,
    size_tib: float,
) -> str:
    if data_is_ephemeral and max_latency_ms < 5:
        return "ElastiCache (Valkey/Redis OSS) — sub-ms, ephemeral OK"
    if not needs_sql and not needs_joins and max_latency_ms < 10:
        return "DynamoDB — point lookups, any scale, no joins"
    if needs_sql and size_tib > 64:
        return "Aurora PostgreSQL — shared storage, 128-256 TiB depending on engine version"
    if needs_sql and writes_per_second > 5000:
        return "Aurora PostgreSQL — high-write relational workload"
    if needs_sql:
        return "RDS PostgreSQL — standard relational workload"
    return "Re-clarify requirements"


cases = [
    ("Session store",             False, 0.5,  5000, False, True,  0.01),
    ("Trade ledger (point read)", False, 5.0, 10000, False, False, 1.0),
    ("Payment reconciliation",    True,  20.0,  500,  True,  False, 5.0),
    ("Large analytics DB",        True,  200.0, 200,  True,  False, 100.0),
    ("ETL staging Postgres",      True,  50.0,  300,  True,  False, 10.0),
]
for label, sql, lat, wps, joins, ephemeral, size in cases:
    rec = select_database(sql, lat, wps, joins, ephemeral, size)
    print(f"{label:30s}: {rec}")
Output
Session store                 : ElastiCache (Valkey/Redis OSS) — sub-ms, ephemeral OK
Trade ledger (point read)     : DynamoDB — point lookups, any scale, no joins
Payment reconciliation        : RDS PostgreSQL — standard relational workload
Large analytics DB            : Aurora PostgreSQL — shared storage, 128-256 TiB depending on engine version
ETL staging Postgres          : RDS PostgreSQL — standard relational workload

See it happen DMS full-load + CDC migration sequence

Source RDSDMS TaskTarget Aurorafull load startbulk insert rowsCDC: changes during loadapply buffered changesCDC: ongoing changesrun data validationapplication writes STOPPEDfinal changes applied
  1. DMS reads all existing rows from source tables. Application writes continue on source during this phase.
  2. DMS inserts rows into the target. The target is not yet receiving application traffic.
  3. While the full load runs, DMS buffers all change events (INSERT, UPDATE, DELETE) from the source WAL.
  4. After bulk load completes, DMS applies the buffered changes to bring the target up to date.
  5. DMS enters ongoing CDC mode: every commit on the source is applied to the target within seconds.
  6. DMS validation compares row counts and spot-checks values. Do not cut over until this passes.
  7. Cutover: the application stops writing to the source. CDC lag drains to zero.
  8. Target is fully consistent with source. Application connection string is updated. Migration complete.
Notice: the CDC lag (time from a source commit to its application on the target) determines your cutover window. Monitor it in the DMS CloudWatch metric CDCLatencySource.
Scenario A

In your world

The situation

Your TCS team is migrating a 10M-row Postgres ETL staging database from an on-premises server to RDS. The on-premises server runs PostgreSQL 13. The migration must have a cutover window under 30 minutes (Saturday 02:00-02:30). The DBA is going to use DMS full load + CDC.

What actually breaks

The DBA runs DMS but forgets to enable logical replication (wal_level = logical) on the source before the task starts. DMS falls back to full-load-only mode without CDC. The full load takes 35 minutes. The Saturday cutover window closes at 02:30. At 02:28 the DBA panics, switches the connection string anyway, and 14 minutes of writes are missing from the target.

What you'd do on Monday

Before starting DMS: verify wal_level = logical on the source (SHOW wal_level). For RDS sources, set wal_level = logical in the parameter group (requires reboot) and set max_replication_slots >= 2. Run the DMS task in development first and measure the full-load time. If the full load takes longer than the cutover window, either extend the window or migrate the table in parallel partial loads.

PythonValidate source prerequisites before starting DMS (needs psycopg + source DB)
import psycopg2

PREREQS = {
    "wal_level": "logical",
    "max_replication_slots": lambda v: int(v) >= 2,
    "max_wal_senders": lambda v: int(v) >= 2,
}

def check_dms_prereqs(dsn: str) -> bool:
    ok = True
    with psycopg2.connect(dsn) as conn:
        with conn.cursor() as cur:
            for param, expected in PREREQS.items():
                cur.execute("SHOW %s" % param)
                value = cur.fetchone()[0]
                passed = expected(value) if callable(expected) else value == expected
                status = "OK" if passed else "FAIL"
                print(f"  {param}: {value} [{status}]")
                if not passed:
                    ok = False
    return ok
Scenario B

Imagine you work at Robinhood

500M trade records, migrate RDS → DynamoDB

The numbers

Illustrative: 500M records × ~500 bytes ≈ 250 GB. DMS full load at 5,000 rows/s ≈ 27 hours for a dms.r5.large. CDC lag must drain to near zero before cutover. At peak trading, the source receives 5,000 writes/s; the CDC stream must keep pace or lag grows.

What the Scenario-A answer does here

The Scenario A approach (single DMS task, validate, cut over) works for 10M rows in 35 minutes. At 500M rows, the full-load phase takes more than a day during which CDC backlog accumulates. A dms.t3.medium cannot keep up with 5,000 writes/s CDC from a busy trading source.

What flips at this scale

At this scale you parallelise the full load across multiple DMS tasks partitioned by date range with a larger replication instance (dms.r5.large), schedule the migration for a weekend off-hours window to minimise CDC write rate, monitor CDC lag via a CloudWatch alarm (alert if lag > 60 s), and run a 48-hour shadow validation before the hard cutover.

The trade-off you'd defend

I would defend shadow validation plus a 48-hour parallel-run period, accepting that both systems run simultaneously and developers write to both, in exchange for confidence that DynamoDB returns identical results to RDS for every access pattern before the cutover. The migration risk at 500M records is not the data transfer — it is the undiscovered access pattern difference that only surfaces in production.

Illustrative throughput; not Robinhood-published numbers.

Gotchas

Leaving a PostgreSQL logical replication slot open after migration

Symptom

The source RDS disk fills up weeks after the migration. The instance goes unavailable.

Cause

The DMS replication slot on the source keeps accumulating WAL segments because nobody dropped it after cutover.

Fix

After successful cutover, drop the DMS replication slot on the source immediately: SELECT pg_drop_replication_slot('your_slot_name'). Verify with SELECT * FROM pg_replication_slots.

Row counts match but data is wrong (timezone mismatch)

Symptom

DMS validation passes (row counts match), but queries return incorrect timestamps after migration.

Cause

The source stored timestamps in a local timezone; the target interprets them as UTC. DMS does not convert timezone-aware types.

Fix

Run a spot-check query comparing source and target timestamp values for 100 rows before completing validation. Normalise timestamps to UTC on the source before migration if possible.

DMS task fails on large object (LOB) columns

Symptom

DMS task enters error state with "LOB column data exceeds maximum LOB size."

Cause

The default maximum LOB size in DMS is set too low for the actual column data.

Fix

Increase the MaxLobSize parameter on the DMS task, or set the task to "limited LOB mode" with a higher cap. Test with your largest LOB values before the production migration.

Tricky questions

Can DMS migrate from on-premises to another on-premises database?
Most people say

Yes, DMS connects to any source and any target.

Actually

No. DMS requires that at least one endpoint (source or target) is an AWS service. You cannot use DMS to migrate between two on-premises databases.

Does DMS data validation guarantee the data is correct?
Most people say

Yes, if validation passes, the data is identical.

Actually

DMS validation checks row counts and spot-checks a sample of rows. It does not compare every value in every row. Validation can pass with subtle differences in NUMERIC precision, timezone offsets, or binary data encoding. Always run application-level smoke tests after validation, and shadow-run for critical workloads.

Scenario question

You're handed a requirement to migrate a 50M-row PostgreSQL database from on-premises to Aurora PostgreSQL with a cutover window of 2 hours on a Saturday. The database receives 200 writes/s during the week and essentially zero writes on Saturday morning.

Model answer

I would use DMS full load + CDC. First, I'd verify logical replication is enabled on the source and configure the required parameters (wal_level = logical, max_replication_slots >= 2) — if the source is RDS, this needs a reboot scheduled before the migration weekend. I'd run the full load on Thursday night to estimate the actual duration at 5,000 rows/s: 50M rows ≈ 2.8 hours. During Friday and Saturday pre-cutover, DMS runs in CDC mode, keeping lag near zero. At 01:30 Saturday I stop application writes, watch CDC lag drain to zero in CloudWatch, run DMS validation, and at 01:45 switch the connection string to Aurora. The 2-hour window covers a 15-minute cut-over procedure with 90 minutes of buffer. The Saturday near-zero write rate means the CDC lag before cutover will be negligible. The trade-off: if DMS validation fails, I must revert to the source and reschedule — so I test the full DMS pipeline in a non-production environment the week before with a copy of production data.

Interview corner

CommonWhat are the three phases of a DMS full load + CDC migration?
“I'd say: full load copies the existing data, CDC captures changes made during and after the load, and validation compares source and target before cutover. The application keeps writing to the source throughout, and the target catches up via CDC before the cutover window.”
Experience checkWhat went wrong in a DMS migration you were involved in?
“In one migration, the DBA didn't enable wal_level = logical on the source before starting the task. DMS ran as full-load only and we didn't notice until the cutover window was half over and 14 minutes of writes were missing. Now I always run a pre-flight check: verify wal_level, max_replication_slots, and do a test run with a sample of tables a week before the real migration.”
TrickDMS validation passes but production queries return wrong results. What do you check?
“I'd check timezone and encoding. DMS validation checks row counts and a sample; it doesn't compare every byte. Timestamps stored in a local timezone on the source can look correct to a row-count check but return wrong values on the target if the target interprets them as UTC. I'd run a spot-check query comparing 100 rows from source and target on every timestamp column.”
DesignWhich database would you choose for a requirement like: 10,000 payment-status lookups per second by payment ID, sub-10ms latency, no joins needed?
“I'd choose DynamoDB with payment ID as the partition key. At 10,000 point lookups per second, DynamoDB on-demand scales to handle it without provisioning decisions, and with no joins needed the single-table NoSQL model is a natural fit. The sub-10ms requirement rules out analytical stores. I'd make sure the partition key has high cardinality — payment IDs are UUIDs, so hot partitions are not a concern.”

Memory hook

Photocopying every book proves nothing about whether the pages came out readable. Check the new building's shelves before you send readers there.

If you remember only one thing:

Always run DMS data validation and a query-level spot-check before cutting over. Row counts matching is not enough.

Self-check

  1. What does CDC stand for in DMS, and what does it capture?

    Change Data Capture: it reads the source database's transaction log (WAL for PostgreSQL) and applies inserts, updates and deletes to the target in near real time.

  2. What PostgreSQL parameter must be set to logical before DMS CDC can work?

    wal_level. This is a static parameter that requires a reboot on RDS.

  3. What happens to the PostgreSQL replication slot if DMS is paused for 12 hours?

    WAL segments accumulate on the source and cannot be freed. On a busy source, this can fill the disk and make the instance unavailable. Drop the replication slot immediately after cutover or if DMS is stopped permanently.

Prove it

Set up a DMS task (or simulate with a PostgreSQL logical replication slot locally) migrating a 1M-row table to a second database. While the full load runs, insert 1,000 rows on the source. After full load completes, verify via CDC that all 1,001,000 rows exist on the target. Then run DMS validation and report the row count result. Finally, drop the replication slot and confirm pg_replication_slots is empty.