Day 6 — Relational Data in Production

3h 25m · Phase 2 of 4 · Curriculum

Learning Objectives

By the end of today you can:

1. State precisely what "managed database" removes and what it leaves you holding.
2. Explain the difference between a Multi-AZ standby and a read replica, and why conflating them is a design error.
3. Describe what happens to in-flight transactions and pooled connections during a failover — second by second — and why the JVM makes it worse.
4. Size a connection pool from the database's limit backwards, and explain how a routine deploy can take down a healthy database.
5. Explain what Aurora's decoupled storage layer actually changes about replicas, failover and cost.
6. Retrieve a database credential at runtime so it exists only in memory, and never in config, an image, or a repository.
7. Run a schema migration against a live service without downtime.

The sentence you should be able to say tonight: "Failover is not transparent — it breaks every open connection, and my application's job is to survive that in a bounded number of seconds."

Prerequisites

• Day 2 — private/isolated subnets, Security Groups referencing Security Groups.
• Day 3 — CloudWatch metrics and alarms.
• Day 4 — ECS tasks, and the fact that a deploy transiently doubles task count.
• Day 5 — Lambda concurrency. Today's most interesting failure is 500 concurrent Lambdas meeting a database.
• From the baseline: SQL, transactions, indexes, ACID. If shaky, Foundation § 3.

Why Does This Exist?

You can install PostgreSQL on an EC2 instance in twenty minutes. So why does RDS exist, and why does anyone pay a premium for it?

Because the twenty minutes is not the work. The work is:

RDS takes all of that. What it emphatically does not take is the part developers most often assume it does:

RDS solves database operations. It does not solve database scaling.

A managed relational database still has one writer. Your queries still need indexes. Your connection count still has a hard ceiling. Your schema is still your problem. This is the day's most important idea and one of the nine interview traps.

Beginner Explanation

What "managed" means, drawn as a boundary

flowchart TB
    subgraph YOU["Yours"]
        Y1[Schema design]
        Y2[Indexes and query plans]
        Y3[Connection management]
        Y4[Transaction boundaries]
        Y5[Which queries run]
        Y6[Capacity planning]
    end
    subgraph AWS["AWS's"]
        A1[Host and hypervisor]
        A2[OS patching]
        A3[Engine installation and minor patching]
        A4[Automated backups + PITR]
        A5[Replication plumbing]
        A6[Failover automation]
        A7[Storage provisioning and growth]
    end

The line is drawn so that AWS takes everything that is the same for every customer, and you keep everything that is specific to your application. That is the same shared-responsibility logic as Day 1, applied to a database.

Two kinds of "second copy", and they are not the same

This is the distinction that separates a 4-YOE answer from a 7-YOE one.

flowchart TB
    subgraph MAZ["Multi-AZ standby — for AVAILABILITY"]
        P1[(Primary · AZ a)] ==>|"SYNCHRONOUS<br/>commit waits for the standby"| S1[(Standby · AZ b)]
        APP1[App] --> P1
        APP1 -.->|"cannot read from it.<br/>It serves NO traffic."| S1
    end
    subgraph RR["Read replica — for SCALE"]
        P2[(Primary · AZ a)] -.->|"ASYNCHRONOUS<br/>commit does not wait"| R1[(Replica)]
        APP2[App] -->|writes| P2
        APP2 -->|"reads (possibly stale)"| R1
    end

"We have a read replica, so we're highly available" is wrong: replica promotion is manual and loses any unreplicated writes. "We have Multi-AZ, so we can scale reads" is also wrong: the standby serves nothing. And "Multi-AZ is our disaster recovery" is the third variant of the same confusion — covered under Failure Scenarios and again on Day 12.

Core Concepts

1. RDS fundamentals

Backups and PITR — the distinction that matters at 3am

flowchart LR
    subgraph AUTO["Automated backups (retention 1-35 days)"]
        SNAP[Daily snapshot] --> LOGS["Continuous transaction logs"]
        LOGS --> PITR["Restore to ANY point<br/>in the retention window<br/>(typically within ~5 min of now)"]
    end
    subgraph MAN["Manual snapshots"]
        MS["Point-in-time copy,<br/>kept until deleted,<br/>survives DB deletion"]
    end
    PITR -->|"restores to a NEW instance"| NEW[(new endpoint)]

Three facts that surprise people:

1. A restore always creates a new instance with a new endpoint. Recovery is not "the database comes back"; it is "a new database exists and something must point at it." That is a failover procedure you should have written down.
2. Deleting the database deletes its automated backups (unless you take a final snapshot). Manual snapshots survive.
3. Retention 0 disables automated backups entirely — and therefore PITR. Some non-production databases are created this way by accident.

2. Multi-AZ, replicas, and what failover does to your application

The two Multi-AZ shapes

What failover actually does — the timeline

gantt
    title RDS Multi-AZ failover, from the application's point of view
    dateFormat ss
    axisFormat %S s
    section Failure
    Primary becomes unreachable        :crit, a1, 00, 10s
    section AWS
    Detection                          :a2, after a1, 20s
    Promote standby                    :a3, after a2, 15s
    Update the endpoint DNS record     :a4, after a3, 5s
    section Application
    Existing connections BROKEN        :crit, b1, 00, 50s
    Client DNS cache still stale       :crit, b2, after a4, 30s
    Pool re-establishes connections    :b3, after b2, 10s
    Serving again                      :b4, after b3, 5s

Read that carefully, because four separate things are happening:

1. Every open connection is broken. There is no connection migration. In-flight transactions are lost — uncommitted work is rolled back, and a commit that was in flight has an unknown outcome.
2. The endpoint is a DNS name, and failover works by repointing it. So recovery speed depends on DNS caching in your client, which AWS does not control.
3. Your connection pool is full of dead sockets. It must detect them and rebuild.
4. Your application must retry. A database failover that the application does not handle is an application outage, even though the database recovered in 40 seconds.

The JVM DNS caching trap — a genuine, specific, costly bug

The JVM caches DNS resolutions. When a SecurityManager is installed, the default is to cache forever. Without one, the OpenJDK default is around 30 seconds — still long enough to extend a 40-second failover into 70+ seconds of errors, and long enough that people conclude "Multi-AZ doesn't work."

// Set this BEFORE any name resolution happens — ideally as a JVM flag,
// because a static initializer elsewhere may resolve a name first.
java.security.Security.setProperty("networkaddress.cache.ttl", "5");
java.security.Security.setProperty("networkaddress.cache.negative.ttl", "1");

# The reliable way: set it at launch, in the container/task definition.
JAVA_TOOL_OPTIONS="-Dnetworkaddress.cache.ttl=5 -Dsun.net.inetaddr.ttl=5"

This is a "verify it yourself" item. The exact defaults are implementation- and version-specific. The action is the same regardless: set it explicitly to a small value, and confirm with a real failover test — which is what Lab 8 does.

Read replicas and the read-your-writes problem

A read replica is asynchronous. Therefore, immediately after a write:

sequenceDiagram
    autonumber
    participant U as User
    participant A as App
    participant P as Primary
    participant R as Replica
    U->>A: POST /orders
    A->>P: INSERT order o-123
    P-->>A: committed
    A-->>U: 201 Created
    P-.->R: replicate (async, ~50ms-∞)
    U->>A: GET /orders
    A->>R: SELECT ... (routed to replica)
    R-->>A: (o-123 not here yet)
    A-->>U: 200 [] — "where is my order?"

This is S13, and it is one of the most common "the app is buggy" reports in systems with read replicas. Four ways out, in order of preference:

Alarm on replica lag. ReplicaLag climbing is a leading indicator of both correctness bugs and an impending capacity problem.

3. Aurora

Aurora is not "RDS but faster." It has a structurally different storage architecture, and the interesting consequences all follow from that.

flowchart TB
    subgraph COMPUTE["Compute layer — stateless, replaceable"]
        W[(Writer instance)]
        R1[(Reader 1)]
        R2[(Reader 2)]
        R3[(Reader up to 15)]
    end
    subgraph STORAGE["Shared distributed storage — 6 copies across 3 AZs"]
        S1["AZ a: 2 copies"]
        S2["AZ b: 2 copies"]
        S3["AZ c: 2 copies"]
    end
    W -->|"writes: quorum of 4 of 6"| S1 & S2 & S3
    R1 & R2 & R3 -->|"read the SAME storage —<br/>no replication to them at all"| S1 & S2 & S3

Because readers share the writer's storage rather than receiving a replicated copy:

Endpoints

A very common bug: pointing the application at the instance endpoint. It works, until a failover, and then it points at a reader and every write fails with a read-only error.

Aurora Serverless v2

Capacity measured in ACUs (≈2 GiB of memory plus proportional CPU), scaling in fine increments between a min and max you set — in-place, in seconds, without dropping connections.

Aurora vs RDS

Aurora costs more per instance-hour and charges for storage and I/O (or a flat higher rate with I/O-Optimized). For a small, steady database RDS is genuinely cheaper; for anything with real read scale or availability requirements, Aurora usually wins.

4. The application side — where the real bugs live

This section contains the most operationally valuable material of the day.

Connection pool arithmetic, done backwards

Start from the database's ceiling, not from your application's convenience.

PostgreSQL on RDS: max_connections defaults to roughly
    LEAST({DBInstanceClassMemory / 9531392}, 5000)

db.r6g.large  (16 GiB)  →  ~1,600
db.r6g.xlarge (32 GiB)  →  ~3,400

But the practical limit is much lower than the configured one: each PostgreSQL connection is a backend process costing memory, and hundreds of mostly-idle connections degrade the whole instance. A working budget:

                     max_connections            1,600
  − superuser_reserved_connections                 -3
  − monitoring / Performance Insights / admin     -20
  − headroom for a deploy (tasks transiently 2×)  -50%
                                                ──────
  usable steady-state budget                     ≈ 760

  pool_size_per_task  =  760 / max_tasks_during_deploy

With a max of 20 tasks that becomes 40 during a deploy → pool size ≈ 19. Round down to 15 and you have margin.

The counter-intuitive truth: a small pool is usually faster. A pool of 10 connections serving 200 concurrent requests will outperform a pool of 200, because the database is not made faster by being asked 200 things at once — it is made slower by context switching and lock contention. Queueing in the application is cheaper than thrashing in the database.

spring:
  datasource:
    hikari:
      maximum-pool-size: 15
      minimum-idle: 5
      # Fail fast rather than blocking a request thread for 30 s.
      connection-timeout: 3000
      # MUST be shorter than any network idle timeout between app and DB,
      # or you hold sockets the network has already discarded.
      max-lifetime: 900000          # 15 min
      idle-timeout: 300000          # 5 min
      # Detect a dead connection (post-failover) quickly.
      validation-timeout: 1000
      keepalive-time: 60000
      pool-name: orders-pool
      register-mbeans: true          # so you can see pending threads

The metric to watch is hikaricp_connections_pending — the number of request threads waiting for a connection. Non-zero means the pool is the bottleneck, and no database metric will tell you that. The database will look idle.

RDS Proxy

flowchart LR
    subgraph MANY["Many short-lived clients"]
        L1[Lambda] & L2[Lambda] & L3["×500"]
        T1[Fargate task] & T2["×40"]
    end
    MANY --> PROXY["RDS Proxy<br/>pools and MULTIPLEXES<br/>thousands of client connections<br/>onto a few DB connections"]
    PROXY --> DB[(Aurora / RDS)]
    PROXY -.->|"holds client connections<br/>through failover"| DB

The problem it solves is connection storms: 500 concurrent Lambdas each opening a connection, or a Fargate deploy transiently doubling pool count. The database cannot distinguish that from an attack.

When you need it: Lambda talking to a relational database; very high task counts; bursty scale-out. When you don't: a stable set of long-lived Fargate tasks with sane pools — that is what pools are for.

Timeouts, at every layer

An unbounded database call is how one slow query becomes a full outage. Set all of these:

spring:
  datasource:
    hikari:
      connection-timeout: 3000          # waiting for a pool slot
      data-source-properties:
        socketTimeout: 10               # seconds — PostgreSQL JDBC
        connectTimeout: 3
        # Cancel the statement server-side too, so the DB stops working on it.
        options: "-c statement_timeout=8000 -c idle_in_transaction_session_timeout=15000"
  jpa:
    properties:
      jakarta.persistence.query.timeout: 8000

statement_timeout is the important one, and the one people forget. A client-side socket timeout abandons the result but the database keeps executing the query, holding locks and burning CPU. Server-side cancellation is what actually protects the database.

Schema migrations against a live service

The rule: at any moment during a deploy, both the old and new versions of your code are running. So every migration must be compatible with both. That is the expand/contract pattern.

flowchart LR
    A["1 EXPAND<br/>add the new nullable column.<br/>Old code ignores it."] --> B["2 Deploy code that<br/>WRITES both, READS old"]
    B --> C["3 BACKFILL<br/>in batches, throttled"]
    C --> D["4 Deploy code that<br/>READS new"]
    D --> E["5 Deploy code that<br/>stops writing old"]
    E --> F["6 CONTRACT<br/>drop the old column"]

Rules worth memorizing:

Flyway/Liquibase make migrations versioned and repeatable. They do not make them safe — that is design.

Where do migrations run? Not in every task on startup (N tasks racing, though Flyway does lock). Better: a dedicated one-shot ECS task or CI step that runs before the new version deploys, so a failed migration blocks the deploy rather than crash-looping the fleet.

5. Secrets

Day 3 said "never put a credential in config." Here is the machinery.

The rule of thumb: if it rotates, Secrets Manager. If it is configuration that happens to be sensitive, Parameter Store. A platform with 400 config values and 6 secrets should not pay $160/month to store its config.

A third option worth knowing: IAM database authentication. The application requests a short-lived (15-minute) token from IAM instead of using a password. No password exists at all. The caveats are real — a limited rate of new connections, and IAM-authenticated logins are heavier — so it is best suited to low-connection-rate clients, or used behind RDS Proxy.

Architecture

High-level

flowchart LR
    C[Client] --> ALB[ALB] --> APP[Order Service · ECS]
    APP --> W[(Aurora writer)]
    APP --> R[(Aurora reader)]
    SM[Secrets Manager] -.->|"credential at runtime"| APP

Detailed

flowchart TB
    subgraph VPC["VPC 10.0.0.0/16"]
        subgraph PUB["public-a / public-b"]
            ALB[ALB]
        end
        subgraph PRIV["private-a / private-b"]
            T1["Task · app-sg<br/>Hikari pool: 15"]
            T2["Task · app-sg"]
        end
        subgraph DATA["data-a / data-b — NO route to the Internet"]
            W[("Aurora writer<br/>db.r6g.large · AZ a")]
            R[("Aurora reader<br/>db.r6g.large · AZ b")]
            STORE["Shared storage:<br/>6 copies across 3 AZs"]
        end
        EP["Interface endpoint:<br/>secretsmanager"]
    end
    ALB --> T1 & T2
    T1 & T2 -->|"writer endpoint :5432<br/>db-sg allows app-sg"| W
    T1 & T2 -->|"reader endpoint"| R
    W --- STORE
    R --- STORE
    T1 & T2 -->|"GetSecretValue, privately"| EP
    SM[Secrets Manager] -.-> EP
    ROT["Rotation Lambda"] -.->|"rotates every 30 days"| SM
    ROT -.-> W
    W --> CW["CloudWatch:<br/>connections, replica lag,<br/>CPU, free storage,<br/>Performance Insights"]

Note the data tier: isolated subnets with no default route (Day 2). The database cannot reach the Internet at all, and nothing from the Internet has a path to it. The application reaches Secrets Manager over an interface endpoint rather than through NAT.

Failure flow

flowchart TB
    APP["App · 15 pooled connections"] --> W[("Writer · AZ a")]
    W -->|"host failure"| X["❌ unreachable"]
    X --> D1["All 15 connections broken.<br/>In-flight transactions lost.<br/>Commit outcomes UNKNOWN."]
    D1 --> D2["Aurora promotes a reader<br/>(~30 s) and repoints the<br/>writer endpoint DNS"]
    D2 --> D3{"Is the JVM's DNS<br/>TTL small?"}
    D3 -->|"no — cached 30 s+"| SLOW["Errors continue well past<br/>the actual recovery.<br/>'Multi-AZ doesn't work.'"]
    D3 -->|"yes — 5 s"| D4["Pool discards dead connections,<br/>reconnects to the new writer"]
    D4 --> D5{"Does the app retry<br/>the failed operations?"}
    D5 -->|no| LOST["User-visible failures for<br/>every request in the window"]
    D5 -->|"yes, with backoff + jitter"| OK["Bounded error window,<br/>then normal service"]
    D5 -.->|"⚠️ retrying a commit<br/>of unknown outcome"| DUP["Possible duplicate write<br/>→ needs idempotency (Day 8)"]

That bottom-right box is the subtle one. If a COMMIT was in flight when the connection broke, you do not know whether it committed. Retrying may duplicate the write. The only robust answer is idempotency — a unique constraint or a conditional write on a business key. Day 8 makes this a discipline.

How It Works Internally

An RDS connection, from pool to query

sequenceDiagram
    autonumber
    participant APP as Application thread
    participant POOL as HikariCP
    participant DNS as VPC resolver
    participant DB as Aurora writer
    APP->>POOL: getConnection()
    alt pool has an idle connection
        POOL-->>APP: existing connection (microseconds)
    else pool must create one
        POOL->>DNS: resolve cluster endpoint
        DNS-->>POOL: writer instance IP
        POOL->>DB: TCP connect :5432
        POOL->>DB: TLS handshake
        POOL->>DB: authenticate (password or IAM token)
        DB-->>POOL: session established (a backend PROCESS in PostgreSQL)
        Note over POOL,DB: 20-80 ms — which is exactly<br/>why you pool
    else pool exhausted
        POOL->>POOL: block up to connection-timeout
        POOL-->>APP: SQLTransientConnectionException<br/>"Connection is not available"
    end
    APP->>DB: BEGIN; SELECT ...; COMMIT
    DB-->>APP: rows
    APP->>POOL: close() — RETURNS to pool, does not disconnect

What to take from this:

• A new connection costs 20–80 ms, and in PostgreSQL it forks a backend process. That is the entire justification for pooling.
• Pool exhaustion surfaces as an application exception, not a database error. The database may be completely idle. This is scenario S11, and it is why hikaricp_connections_pending is the metric that actually diagnoses it.
• close() returns the connection to the pool. A leaked connection (not closed on an exception path) permanently shrinks the pool, and the symptom appears hours later under load.

Aurora's write path

sequenceDiagram
    autonumber
    participant APP as Application
    participant W as Writer instance
    participant S as Storage nodes (6, across 3 AZs)
    participant R as Readers
    APP->>W: INSERT ...; COMMIT
    W->>S: send redo log records (not data pages)
    Note over W,S: Aurora ships the LOG, not pages —<br/>far less write amplification than<br/>traditional replication
    S-->>W: acks
    Note over W,S: commit needs a QUORUM of 4 of 6
    W-->>APP: committed
    S-.->R: readers read the same storage;<br/>no replication to them at all
    Note over R: replica lag is typically<br/>single-digit milliseconds

This is why Aurora's numbers are different: readers are not fed by replication, so adding one is nearly free and lag is tiny; and a 4-of-6 quorum means two storage copies can be unavailable with no impact on commits.

Code

Dependencies

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-database-postgresql</artifactId>
</dependency>
<dependency>
  <groupId>software.amazon.awssdk</groupId>
  <artifactId>secretsmanager</artifactId>   <!-- BOM-managed -->
</dependency>

Fetching the credential at runtime, with caching and rotation handling

package com.acme.config;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import software.amazon.awssdk.services.secretsmanager.SecretsManagerClient;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueRequest;

import java.time.Duration;
import java.time.Instant;

/**
 * Fetches the database credential from Secrets Manager.
 *
 * Two non-obvious requirements:
 *
 * 1. CACHE IT. GetSecretValue is billed per 10k calls and rate-limited. A
 *    naive implementation that fetches per connection will both cost money
 *    and throttle during a scale-out event.
 *
 * 2. HANDLE ROTATION. When Secrets Manager rotates an RDS credential, the old
 *    password stops working. The pool then fails to create new connections.
 *    We refresh on a TTL and, critically, force a refresh on an auth failure.
 */
public class RotatingDbCredentials {

    private static final Logger log = LoggerFactory.getLogger(RotatingDbCredentials.class);
    private static final ObjectMapper MAPPER = new ObjectMapper();
    private static final Duration TTL = Duration.ofMinutes(10);

    private final SecretsManagerClient client;
    private final String secretId;

    private volatile Credentials cached;
    private volatile Instant fetchedAt = Instant.EPOCH;

    public RotatingDbCredentials(SecretsManagerClient client, String secretId) {
        this.client = client;
        this.secretId = secretId;
    }

    public synchronized Credentials get() {
        if (cached == null || Instant.now().isAfter(fetchedAt.plus(TTL))) {
            refresh();
        }
        return cached;
    }

    /** Call this when the database rejects the credential — rotation just happened. */
    public synchronized Credentials forceRefresh() {
        log.warn("Forcing secret refresh — likely a rotation");
        refresh();
        return cached;
    }

    private void refresh() {
        try {
            String json = client.getSecretValue(
                    GetSecretValueRequest.builder().secretId(secretId).build()).secretString();
            JsonNode n = MAPPER.readTree(json);
            // RDS-managed secrets have this exact shape.
            cached = new Credentials(n.get("username").asText(), n.get("password").asText());
            fetchedAt = Instant.now();
        } catch (Exception e) {
            if (cached != null) {
                // Degrade gracefully: a Secrets Manager blip must not take the app down
                // while we still hold a credential that works.
                log.error("Secret refresh failed; continuing with the cached credential", e);
                return;
            }
            throw new IllegalStateException("Cannot obtain database credentials", e);
        }
    }

    public record Credentials(String username, String password) {}
}

The DataSource

package com.acme.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import software.amazon.awssdk.services.secretsmanager.SecretsManagerClient;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    SecretsManagerClient secretsManagerClient() {
        return SecretsManagerClient.create();
    }

    @Bean
    RotatingDbCredentials dbCredentials(SecretsManagerClient sm, DbProperties props) {
        return new RotatingDbCredentials(sm, props.secretId());
    }

    @Bean
    DataSource dataSource(RotatingDbCredentials creds, DbProperties props) {
        var c = creds.get();

        HikariConfig cfg = new HikariConfig();
        // The CLUSTER (writer) endpoint — never an instance endpoint, which
        // becomes a reader after failover and rejects every write.
        cfg.setJdbcUrl("jdbc:postgresql://%s:%d/%s".formatted(
                props.writerEndpoint(), props.port(), props.database()));
        cfg.setUsername(c.username());
        cfg.setPassword(c.password());

        // Sized backwards from max_connections; see the arithmetic above.
        cfg.setMaximumPoolSize(props.poolSize());
        cfg.setMinimumIdle(5);
        cfg.setConnectionTimeout(3_000);     // fail fast, don't hold a request thread
        cfg.setMaxLifetime(900_000);         // recycle before any network idle timeout
        cfg.setIdleTimeout(300_000);
        cfg.setValidationTimeout(1_000);
        cfg.setKeepaliveTime(60_000);        // detect dead sockets after a failover
        cfg.setPoolName("orders-pool");
        cfg.setRegisterMbeans(true);

        // Server-side guards. statement_timeout is the one that actually protects
        // the DATABASE; a client socket timeout only protects the client.
        cfg.addDataSourceProperty("socketTimeout", "10");
        cfg.addDataSourceProperty("connectTimeout", "3");
        cfg.addDataSourceProperty("ApplicationName", "order-service");
        cfg.addDataSourceProperty("options",
                "-c statement_timeout=8000 -c idle_in_transaction_session_timeout=15000");
        cfg.addDataSourceProperty("sslmode", "verify-full");
        cfg.addDataSourceProperty("sslrootcert", "/opt/certs/rds-global-bundle.pem");

        return new HikariDataSource(cfg);
    }
}

sslmode=verify-full with the RDS CA bundle, not require. require encrypts but does not verify the server's identity, which leaves you open to an in-VPC man-in-the-middle. This is a small change that materially improves the security posture, and almost nobody makes it.

Routing reads to the reader endpoint, deliberately

package com.acme.orders;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderQueryService {

    private final OrderRepository repo;

    OrderQueryService(OrderRepository repo) { this.repo = repo; }

    /**
     * readOnly=true lets Spring/Hibernate skip dirty checking and, with a
     * routing DataSource, directs this to the READER endpoint.
     *
     * Safe here: a customer's order history tolerates a few hundred
     * milliseconds of staleness.
     */
    @Transactional(readOnly = true)
    public List<Order> history(String customerId) {
        return repo.findByCustomerIdOrderByCreatedAtDesc(customerId);
    }

    /**
     * NOT readOnly, even though it only reads.
     *
     * This runs immediately after a write in the same user interaction, so it
     * MUST see that write. Routing it to a replica produces the
     * "where is my order?" bug (S13). The cost is load on the writer;
     * the alternative is a correctness bug.
     */
    @Transactional
    public Order justCreated(String orderId) {
        return repo.findById(orderId).orElseThrow();
    }
}

Expand/contract migrations

-- V3__add_currency_expand.sql   STEP 1: EXPAND
-- Nullable, with a default. Old code ignores it; new code can write it.
ALTER TABLE orders ADD COLUMN currency VARCHAR(3);
-- CONCURRENTLY: no write lock on a large table. Cannot run inside a transaction,
-- so Flyway needs this script marked as non-transactional.
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

-- V4__backfill_currency.sql     STEP 3: BACKFILL, in batches
-- One big UPDATE on 200M rows locks, bloats and blocks. Batch it.
DO $$
DECLARE rows_updated INTEGER;
BEGIN
  LOOP
    UPDATE orders SET currency = 'GBP'
    WHERE id IN (SELECT id FROM orders WHERE currency IS NULL LIMIT 10000);
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    COMMIT;              -- release locks between batches
    PERFORM pg_sleep(0.1);  -- be kind to the serving workload
  END LOOP;
END $$;

-- V5__enforce_currency_contract.sql   STEP 6: CONTRACT — only after ALL
-- deployed code writes the column.
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;

Hands-on

Lab 8 — Spring Boot + Aurora PostgreSQL, then trigger a failover (30 min)

Objective: a service persisting to Aurora in isolated subnets, credentials from Secrets Manager, Flyway migration — and then you fail it over and measure what your application does.

Prerequisites: Day 2 VPC with data-tier subnets, the Day 4 ECS service (or run locally through an SSM tunnel).

⚠️ Billable and the most expensive lab in the course. Two db.t4g.medium Aurora instances ≈ $0.15/hr combined, plus storage and I/O. A 2-hour lab is well under $1. Delete the cluster at the end — left running it is ~$110/month.

Steps:

1. Create a DB subnet group from data-a and data-b.
2. Create db-sg allowing 5432 from app-sg only (Day 2's pattern).
3. Create the Aurora PostgreSQL cluster with "Manage master credentials in AWS Secrets Manager" — AWS creates and rotates the secret for you, and the password never exists in your terminal history.

aws rds create-db-cluster \
  --db-cluster-identifier course-orders \
  --engine aurora-postgresql --engine-version 16.4 \
  --master-username orders_admin \
  --manage-master-user-password \
  --db-subnet-group-name course-data-subnets \
  --vpc-security-group-ids "$DB_SG" \
  --backup-retention-period 7 \
  --storage-encrypted \
  --enable-cloudwatch-logs-exports postgresql

# Writer
aws rds create-db-instance --db-instance-identifier course-orders-1 \
  --db-cluster-identifier course-orders --engine aurora-postgresql \
  --db-instance-class db.t4g.medium --availability-zone eu-west-1a \
  --enable-performance-insights
# Reader in another AZ — this is what gets promoted on failover
aws rds create-db-instance --db-instance-identifier course-orders-2 \
  --db-cluster-identifier course-orders --engine aurora-postgresql \
  --db-instance-class db.t4g.medium --availability-zone eu-west-1b

aws rds wait db-instance-available --db-instance-identifier course-orders-1

4. Grant the task role secretsmanager:GetSecretValue on that secret's ARN only, and kms:Decrypt on the key that encrypts it.
5. Add the Flyway migration and the Order entity. Deploy with writerEndpoint and readerEndpoint from:

aws rds describe-db-cluster --db-cluster-identifier course-orders \
  --query 'DBClusters[0].{writer:Endpoint,reader:ReaderEndpoint}'

6. Create some orders through the API and read them back.

Verification — and step (b) is the reason this lab exists:

# (a) The credential is nowhere in the application's configuration
aws ecs describe-task-definition --task-definition order-service \
  --query 'taskDefinition.containerDefinitions[0].environment'
# → only DB_SECRET_ID, DB_WRITER_ENDPOINT, etc. No password.

# (b) THE FAILOVER TEST.
# Run a continuous write loop, then fail over, and measure the error window.
( while true; do
    ts=$(date +%s.%N)
    code=$(curl -s -o /dev/null -w '%{http_code}' --max-time 5 \
      -XPOST "http://${ALB_DNS}/orders" -H 'Content-Type: application/json' \
      -d '{"customerId":"c-1","amount":10.00}')
    echo "$ts $code"
    sleep 0.25
  done ) > /tmp/failover.log 2>&1 &
LOOP=$!

sleep 10
echo "=== FAILOVER at $(date +%s) ==="
aws rds failover-db-cluster --db-cluster-identifier course-orders \
  --target-db-instance-identifier course-orders-2

sleep 180
kill $LOOP

# How long were we broken?
FIRST=$(awk '$2!=201 {print $1; exit}' /tmp/failover.log)
LAST=$(awk '$2!=201 {t=$1} END {print t}' /tmp/failover.log)
echo "Error window: $(echo "$LAST - $FIRST" | bc) seconds"
echo "Failed requests: $(awk '$2!=201' /tmp/failover.log | wc -l)"

Then explain your number. This is the analytical core of the day:

error window  ≈  AWS detection + promotion (~20-35 s)
              +  your JVM's DNS cache TTL
              +  pool detecting and discarding dead connections
              +  your retry backoff

Now reduce it and re-measure:

# 1. Shrink the JVM DNS cache
JAVA_TOOL_OPTIONS="-Dnetworkaddress.cache.ttl=5"
# 2. Add retry-with-backoff around the write
# 3. (Optional) Put RDS Proxy in front and measure again

A well-configured application should get this to roughly 30–45 seconds. A badly-configured one sits at 2–5 minutes. That delta is entirely yours.

# (c) Prove read replicas are stale
psql "$WRITER" -c "INSERT INTO orders (id, customer_id, amount) VALUES ('probe','c-1',1);"
psql "$READER" -c "SELECT count(*) FROM orders WHERE id='probe';"
# Run the reader query immediately and repeatedly. On Aurora you will usually
# see it within milliseconds — which is exactly why Aurora's replica lag is a
# selling point. Then watch ReplicaLag in CloudWatch under write load.

# (d) Exhaust the pool deliberately, and see where the symptom appears
hey -n 2000 -c 300 "http://${ALB_DNS}/orders/slow-query"
# Application log: "Connection is not available, request timed out after 3000ms"
# RDS CPU: low.  DatabaseConnections: flat at your pool size.
# THE DATABASE LOOKS HEALTHY. The bottleneck is entirely in your pool.

Cleanup — do this, it is the expensive one:

aws rds delete-db-instance --db-instance-identifier course-orders-2 --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier course-orders-1 --skip-final-snapshot
aws rds delete-db-cluster --db-cluster-identifier course-orders --skip-final-snapshot
aws rds wait db-cluster-deleted --db-cluster-identifier course-orders
# The managed secret is deleted with a recovery window; force it if you want it gone:
aws secretsmanager delete-secret --secret-id "$SECRET_ARN" --force-delete-without-recovery

Common errors:

Production relevance: this is the shape of a production relational tier — private subnets, rotating credentials, a tuned pool, a tested failover. The failover test is the part most teams never do, and it is why so many teams believe Multi-AZ does not work.

Production Considerations

Failure Scenarios

S11 — 200 tasks × 20 connections meet a 500-connection database

A service scaled from 50 to 200 Fargate tasks over a quarter. Pool size stayed at 20. Then a routine deploy took the database down.

The discriminating signal: DatabaseConnections pinned at max_connections while RDS CPU is low. The database is not busy; it is full. And hikaricp_connections_pending is high in the application — the metric that tells you the truth.

The fixes, in order:

1. Size the pool backwards from the limit, including the deploy doubling. Usually the pool was never resized as the fleet grew.
2. RDS Proxy, which multiplexes thousands of client connections onto a few dozen database ones. This is the structural fix.
3. Alarm on DatabaseConnections at 70% of max_connections, so you see it a quarter before it bites.
4. Reserve connections for administration (superuser_reserved_connections) so you can always get in.

The senior observation: this failure is created by a successful deploy, on a database with no problem, and the error message points at the database. It is one of the best examples in this course of a limit that is invisible from where the symptom appears.

S12 — Failover takes 40 seconds of errors; the team expected zero

Covered in detail in the failure flow and Lab 8(b).

What "Multi-AZ" actually promises: the database returns in tens of seconds without data loss. It promises nothing about your application. The gap is entirely yours to close: DNS TTL, pool validation, retry with backoff and jitter, and idempotent writes.

S13 — Reporting queries return orders that "don't exist yet"

Reads were moved to a replica to relieve the primary. Support tickets follow.

The framing for an interview: "A read replica is a consistency decision disguised as a performance decision." Adding one silently changes what your application can promise its users, and the change is not visible in any code diff.

Security

Database-specific practice:

• Encryption at rest must be decided at creation. The retrofit is a snapshot-restore, i.e. a migration. Turn it on always.
• Separate application and admin credentials. The application's database user should not own the schema or be able to DROP TABLE. Migrations run as a different, more privileged user, from a different place.
• Never a public database. publiclyAccessible: false, isolated subnets, no IGW route. A publicly-accessible RDS instance with a guessable password is found by scanners within hours.
• Audit logging to CloudWatch for regulated workloads — but be aware of the log volume and its cost (Day 3's lesson).
• Rotation must be tested. A rotation that breaks the application at 03:00 on a Sunday, because nobody handled the auth-failure refresh path, is a self-inflicted outage. That is why forceRefresh() exists in the code above.

Performance

Cost

Lab 8 for 2 hours (eu-west-1, illustrative — verify current pricing):

Production-scale comparison for the flagship order system:

Four cost facts worth carrying:

1. Multi-AZ doubles the instance cost for a standby that serves nothing. That is what availability costs, stated plainly. It is almost always worth it for production.
2. The database is usually the largest single line item in a backend AWS bill, and it runs 24/7 whether or not anyone uses it.
3. Aurora I/O can surprise you. A write-heavy workload can spend more on I/O than on instances. I/O-Optimized makes it predictable and is often cheaper above roughly 25% I/O share.
4. Reserved Instances / Savings Plans apply to RDS, typically ~30–40% off for a one-year commitment. A production database is the most predictable workload you own, which makes it the best possible commitment candidate.

Alternatives

Trade-offs

Common Mistakes

1. Believing RDS solves scaling. It solves operations. One writer is still one writer.
2. Confusing a Multi-AZ standby with a read replica. Different purpose, different replication, different promotion behaviour.
3. Calling Multi-AZ "disaster recovery." A bad deploy or a dropped table replicates to the standby instantly.
4. Never testing failover. Then discovering the JVM DNS cache during a real incident.
5. Pool size set once and never revisited as the fleet grew from 10 tasks to 200.
6. Forgetting the deploy doubles task count, and therefore connections.
7. Connecting to an instance endpoint. Works until failover, then every write fails read-only.
8. No statement_timeout. A client timeout abandons the result; the database keeps working.
9. Routing all reads to a replica and then fixing the resulting bugs one ticket at a time.
10. A credential in application.yml, an env var, or an image layer. Runtime retrieval, always.
11. Not handling rotation. Everything works for 29 days.
12. Renaming a column in one migration. The old version of your code is still running.
13. Storage encryption not enabled at creation. The retrofit is a migration.
14. sslmode=require instead of verify-full. Encrypted, unauthenticated.

Interview Questions

2–3 YOE

1. What does RDS manage for you, and what does it not?
2. What is the difference between a Multi-AZ standby and a read replica?
3. What is a connection pool, and why is one connection per request a bad idea?
4. Where should a database's password live?
5. What is point-in-time recovery, and what does a restore actually produce?
6. Why should a database never be in a public subnet?

4–5 YOE

1. Your application throws Connection is not available, request timed out while RDS CPU sits at 12%. What is wrong?
2. Why does a failover cause 3 minutes of errors when AWS says it takes 40 seconds?
3. You moved reads to a replica and users report that new orders "don't appear." Explain and fix.
4. How do you add a NOT NULL column to a 200-million-row table used by a live service?
5. When would you add RDS Proxy, and what does it actually solve?
6. What is the difference between a client socket timeout and statement_timeout, and why does it matter?

Senior-Level Questions

6–7 YOE

1. A service on 200 Fargate tasks must talk to one Aurora cluster. Work through the connection budget out loud, including deploy-time behaviour.
2. Design the read strategy for a system where 90% of reads tolerate 2 seconds of staleness and 10% must be strongly consistent. How do you enforce the distinction in code so it cannot be got wrong by accident?
3. How do you do a zero-downtime major version upgrade of a production database, and what is your rollback?
4. Your database is the bottleneck. Give four options in the order you would try them, and say what each costs.
5. Design credential management for 40 services sharing an Aurora cluster, with rotation, least privilege, and no shared credentials.

8–10 YOE

1. Where exactly is the consistency boundary in an "ECS → Aurora writer + reader" architecture, and what can a user observe inside it? Be specific about what they see and when.
2. Argue for and against a shared database across 12 microservices. Then decide, and name what would change your mind.
3. An in-flight COMMIT is interrupted by a failover. Walk through every option for the client, and say which you would implement and why.
4. Your Aurora bill is 40% I/O. Describe the investigation and the changes you would consider, including the ones you would reject.
5. When is "just use a bigger instance" the correct senior answer for a database, and how do you defend it against a team that wants to shard?

Day-End Revision

The five sentences

1. RDS solves database operations — backups, PITR, patching, failover — not database scaling; one writer remains one writer.
2. A Multi-AZ standby is synchronous, serves nothing, and is promoted automatically; a read replica is asynchronous, serves reads, and is promoted manually.
3. Failover breaks every connection and has an unknown outcome for in-flight commits; the error window your users see is AWS's recovery time plus your DNS TTL, your pool behaviour, and your retry policy.
4. Size the connection pool backwards from max_connections, including the deploy-time doubling; pool exhaustion appears as an application error on an idle database.
5. Credentials come from Secrets Manager at runtime, are rotated, and your code must survive the rotation.

The diagram to redraw from memory: the failover timeline, with the four contributing delays labelled.

The numbers

Today's trap: "We have Multi-AZ, so we have disaster recovery." Multi-AZ is high availability within a Region. DR must survive Region loss and logical failures — a bad deploy or a dropped table replicates to your standby in milliseconds.

Tomorrow needs: the connection-limit and scaling ceilings you just hit (DynamoDB's answer is different in kind), the read-replica staleness idea (eventual consistency, generalized), and the observation that a cache often beats a read replica.

Mini Assignment

Time: 50–60 minutes. Capstone contribution: Aurora persistence with rotating credentials.

1. In day-06/, add Aurora persistence to the Order Service:
   • orders table via Flyway, with a composite index on (customer_id, created_at DESC);
   • credentials from Secrets Manager with caching and a force-refresh on auth failure;
   • pool sized from arithmetic you write down in NOTES;
   • all four timeouts set, including statement_timeout;
   • readOnly query methods routed to the reader endpoint, and one deliberately routed to the writer with a comment explaining why.
2. Run the failover test from Lab 8(b). Record your error window and failed-request count.
3. Reduce it. Set the JVM DNS TTL, add retry with exponential backoff and jitter, and re-measure. Report before/after.
4. Exhaust the pool on purpose. Show the application error and the flat DatabaseConnections metric side by side, and explain why the database looks healthy.
5. Do a live expand/contract migration while the failover loop runs: add a currency column, backfill in batches, enforce NOT NULL. Zero failed requests throughout.
6. Prove rotation works. Trigger rotate-secret and confirm the application keeps serving. If it does not, fix the refresh path — that is the assignment.
7. In day-06/NOTES.md:
   • Your connection budget arithmetic, with the deploy-time doubling shown.
   • Failover error window before and after your changes, with the delta attributed to each fix.
   • Which of your read endpoints must hit the writer, and what a user would see if they did not.
   • Monthly cost of this tier at db.r6g.large Multi-AZ, and one change that would cut 30% with the trade-off named.
   • An in-flight COMMIT is interrupted by failover. What does your code do, and is it correct?
8. Delete the cluster. Verify in Cost Explorer. Commit.

Success criterion: your failover error window is under 60 seconds and you can attribute each remaining second to a specific cause.

AWS Documentation

• RDS User Guide · Multi-AZ deployments · Read replicas · Backups and PITR
• Aurora User Guide · Aurora storage and reliability · Endpoints · Serverless v2
• RDS Proxy · IAM database authentication
• Setting the JVM TTL for DNS name lookups
• Secrets Manager rotation · RDS-managed master passwords
• HikariCP pool sizing · Flyway
• Performance Insights

Previous: Day 5 — Serverless Compute and the API Layer · Next: Day 7 — DynamoDB and Caching