Read Replicas

The previous lesson stood on a single primary: the second machine stood by, doing no work until the moment of failover. Looking at the work-order database’s request mix, this waste stands out — most of the stream is reads. Opening the standby copy to reads spreads the load, and in the managed service this is a single switch.

What sits under that switch is a delegated decision: how replication works is no longer visible. That mechanism — synchronous or asynchronous, how many members acknowledge — was measured in the Advanced Data Scaling course and is not repeated here. What gets measured is the one symptom that leaks out of the mechanism: replica lag. And what gets counted is not “how many seconds of lag,” but how many requests the lag touches.

Two Kinds of Stale Read

Not all stale reads carry the same weight. If a field crew sees a work order another crew closed half a second ago still listed as open, that is an inconsistency, but it goes unnoticed. If the same crew sees a work order it just closed still listed as open, that is a bug: the screen has ignored an action just taken, and the action gets retried. These requests are said to carry a read-your-writes requirement, and they need to be counted separately, because their fix is separate too.

Mechanics

MD7 — the stream is a 300-second window: 1,400 requests a second, 78% reads (420,000 requests, 92,400 writes). MD8 — the fictional measurement network holds 2,400 sessions and 50,000 open work-order records; a session is a field crew or an automated collector. MD9 — 31% of writes are read back by the same session 120–900 ms later, because the screen refreshes the list after the action. MD10 — the base replica lag is 0.25 seconds and each added replica adds 0.15 seconds, because the replication stream is pulled from the primary. MD11 — the load unit is fictional: a write is 1.0, a read is 0.4, and per-replica replication delivery is 0.08 of a write. MD12 — the routing rule is session-based: after a session writes, its reads go to the primary for W seconds. MD9 is the direct determinant; if the read-back interval grows, the first table’s right column shrinks in the same direction.

// md/model.mjs — READ REPLICA model. Generates the fictional regional measurement network's
// work-order request stream; replication MECHANICS (measured in M17/K05 and M19/K04) are not
// modeled — only how lag reflects onto reads is. Hand-written generator, seed visible: same stream every run.
export const REQUESTS = 1400, WINDOW = 300, READ_SHARE = 0.78;
export const SESSIONS = 2400, RECORDS = 50000, READBACK_SHARE = 0.31;
export const READBACK_MIN = 0.12, READBACK_MAX = 0.90;

export function generateStream(seed = 20260802) {
  let s = seed % 2147483647;
  const rand = () => (s = (s * 48271) % 2147483647) / 2147483647;
  const total = REQUESTS * WINDOW, writeCount = Math.round(total * (1 - READ_SHARE));
  const events = [];
  for (let i = 0; i < writeCount; i += 1) {                   // work order state change
    const t = rand() * WINDOW, o = Math.floor(rand() * SESSIONS), e = Math.floor(rand() * RECORDS);
    events.push([t, o, e, 1]);                                // 1 = write
    if (rand() < READBACK_SHARE)                               // same session reads its own write
      events.push([t + READBACK_MIN + rand() * (READBACK_MAX - READBACK_MIN), o, e, 0]);
  }
  const remaining = total - events.length;
  for (let i = 0; i < remaining; i += 1)                       // reads of someone else's data
    events.push([rand() * WINDOW, Math.floor(rand() * SESSIONS), Math.floor(rand() * RECORDS), 0]);
  events.sort((a, b) => a[0] - b[0]);
  return events;
}

// Three numbers are derived per read: lag = time since the last write to that record (a
// replica behind this lag is stale), own = did the session that read it also last write it,
// since = time since the reading session's OWN last write (the routing rule knows only this).
export function deriveReads(events) {
  const lastWrite = new Map(), sessionLast = new Map();
  const lag = [], own = [], since = [];
  let writes = 0;
  for (const [t, o, e, kind] of events) {
    if (kind === 1) { lastWrite.set(e, [t, o]); sessionLast.set(o, t); writes += 1; continue; }
    const last = lastWrite.get(e);
    lag.push(last ? t - last[0] : Infinity);
    own.push(last ? last[1] === o : false);
    since.push(sessionLast.has(o) ? t - sessionLast.get(o) : Infinity);
  }
  return { lag, own, since, writes, reads: lag.length };
}

export const stale = (r, L) => r.lag.reduce((n, d) => n + (d < L ? 1 : 0), 0);
export const staleOwn = (r, L) => r.lag.reduce((n, d, i) => n + (d < L && r.own[i] ? 1 : 0), 0);
export const routed = (r, W) => r.since.reduce((n, d) => n + (d < W ? 1 : 0), 0);
export const saved = (r, L, W) =>
  r.lag.reduce((n, d, i) => n + (d < L && r.own[i] && r.since[i] < W ? 1 : 0), 0);

export const LAG_BASE = 0.25, LAG_STEP = 0.15;
export const LOAD_WRITE = 1.0, LOAD_READ = 0.4, LOAD_REPLICATION = 0.08;
export const lagForReplicas = (R) => LAG_BASE + LAG_STEP * R;

// md/measure.mjs — how replica lag reflects onto the application, the routing rule, and diminishing returns
import { generateStream, deriveReads, stale, staleOwn, routed, saved, lagForReplicas,
  REQUESTS, WINDOW, SESSIONS, RECORDS, READBACK_SHARE, LOAD_WRITE, LOAD_READ, LOAD_REPLICATION } from "./model.mjs";

const s = (x, n) => String(x).padStart(n);
const pct = (a, b) => (100 * a / b).toFixed(2) + "%";
const r = deriveReads(generateStream());
console.log(`${REQUESTS * WINDOW} requests / ${WINDOW} s: ${r.writes} writes, ${r.reads} reads. ` +
  `${SESSIONS} sessions, ${RECORDS} work orders. Seed 20260802.`);
console.log(`${100 * READBACK_SHARE}% of writes are read back by the same session.\n`);

console.log("replica lag     | stale reads | stale share | RYW misses                | miss share");
console.log("----------------|-------------|-------------|---------------------------|-----------");
for (const L of [0.1, 0.25, 0.5, 1, 2, 5]) {
  const b = stale(r, L), k = staleOwn(r, L);
  console.log(`${s(L.toFixed(2) + " s", 15)} | ${s(b, 11)} | ${s(pct(b, r.reads), 11)} | ` +
    `${s(k, 25)} | ${s(pct(k, r.reads), 7)}`);
}

const L = 0.55;
const base = staleOwn(r, L);
console.log(`\nrouting rule, replica lag held at ${L} s (${base} requests cannot see their own write):`);
console.log("sticky window W    | reads sent to primary | saved      | remaining | read share shifted to primary");
console.log("-------------------|-----------------------|------------|-----------|------------------------------");
for (const W of [0, 0.25, 0.5, 1, 2, 5, 15]) {
  const sent = routed(r, W), kept = saved(r, L, W);
  console.log(`${s(W.toFixed(2) + " s", 18)} | ${s(sent, 21)} | ${s(kept, 10)} | ${s(base - kept, 9)} | ` +
    `${s(pct(sent, r.reads), 26)}`);
}

console.log(`\nas replica count rises, the replication stream is pulled harder from the primary: lag ` +
  `${lagForReplicas(1).toFixed(2)} s (1 replica) -> ${lagForReplicas(8).toFixed(2)} s (8 replicas).`);
console.log("replicas | lag     | reads/s per replica   | marginal gain of added replica | stale share | primary load (units/s)");
console.log("---------|---------|-----------------------|--------------------------------|-------------|-----------------------");
const W = 1, sentPerSec = routed(r, W) / WINDOW, replicaReads = (r.reads / WINDOW) - sentPerSec;
let previous = null;
for (const R of [1, 2, 3, 4, 5, 6, 7, 8]) {
  const perReplica = replicaReads / R;
  const margin = previous === null ? "-" : (previous - perReplica).toFixed(1) + "/s";
  const b = stale(r, lagForReplicas(R));
  const load = (r.writes / WINDOW) * LOAD_WRITE + sentPerSec * LOAD_READ +
    R * (r.writes / WINDOW) * LOAD_REPLICATION;
  console.log(`${s(R, 8)} | ${s(lagForReplicas(R).toFixed(2) + " s", 7)} | ${s(perReplica.toFixed(1), 21)} | ` +
    `${s(margin, 30)} | ${s(pct(b, r.reads), 11)} | ${s(load.toFixed(1), 23)}`);
  previous = perReplica;
}
console.log(`\nrun-independent: once the sticky window exceeds the replica lag, no requests are left that ` +
  `cannot see their own write; the cost is the number of reads sent to the primary.`);

// Transfer cost: every out-of-region replica carries each write across the region boundary. Fictional unit.
const RECORD_KB = 1.8, MONTH = 2592000, SIZE_GB = 220;
const gbMonth = (r.writes / WINDOW) * RECORD_KB * MONTH / 1048576;
console.log(`\nreplication stream: ${(r.writes / WINDOW).toFixed(0)} writes/s x ${RECORD_KB} KB = ` +
  `${gbMonth.toFixed(0)} GB/month, per replica.`);
console.log("replica placement | unit/GB  | 1 replica | 2 replicas | 3 replicas  (fictional unit/month)");
console.log("------------------|----------|-----------|------------|-----------------------------------");
for (const [name, rate] of [["same region", 0], ["other region", 2]])
  console.log(`${name.padEnd(17)} | ${s(rate, 8)} | ${s((rate * gbMonth).toFixed(0), 9)} | ` +
    `${s((2 * rate * gbMonth).toFixed(0), 10)} | ${s((3 * rate * gbMonth).toFixed(0), 7)}`);
console.log(`irreversible item: a promoted replica does not return to its old replication link; rebuilding ` +
  `the link means a full ${SIZE_GB} GB copy.`);
console.log(`within region at 95 MB/s: ${(SIZE_GB * 1024 / 95 / 3600).toFixed(2)} h; across regions ` +
  `at 30 MB/s: ${(SIZE_GB * 1024 / 30 / 3600).toFixed(2)} h + ${2 * SIZE_GB} fictional units.`);

420000 requests / 300 s: 92400 writes, 327600 reads. 2400 sessions, 50000 work orders. Seed 20260802.
31% of writes are read back by the same session.

replica lag     | stale reads | stale share | RYW misses                | miss share
----------------|-------------|-------------|---------------------------|-----------
         0.10 s |         208 |       0.06% |                         0 |   0.00%
         0.25 s |        5253 |       1.60% |                      4755 |   1.45%
         0.50 s |       14702 |       4.49% |                     13733 |   4.19%
         1.00 s |       30039 |       9.17% |                     28093 |   8.58%
         2.00 s |       31877 |       9.73% |                     28093 |   8.58%
         5.00 s |       37473 |      11.44% |                     28093 |   8.58%

routing rule, replica lag held at 0.55 s (15546 requests cannot see their own write):
sticky window W    | reads sent to primary | saved      | remaining | read share shifted to primary
-------------------|-----------------------|------------|-----------|------------------------------
            0.00 s |                     0 |          0 |     15546 |                      0.00%
            0.25 s |                 14986 |       5078 |     10468 |                      4.57%
            0.50 s |                 33134 |      13843 |      1703 |                     10.11%
            1.00 s |                 64155 |      15546 |         0 |                     19.58%
            2.00 s |                 95648 |      15546 |         0 |                     29.20%
            5.00 s |                168901 |      15546 |         0 |                     51.56%
           15.00 s |                278984 |      15546 |         0 |                     85.16%

as replica count rises, the replication stream is pulled harder from the primary: lag 0.40 s (1 replica) -> 1.45 s (8 replicas).
replicas | lag     | reads/s per replica   | marginal gain of added replica | stale share | primary load (units/s)
---------|---------|-----------------------|--------------------------------|-------------|-----------------------
       1 |  0.40 s |                 878.1 |                              - |       3.34% |                   418.2
       2 |  0.55 s |                 439.1 |                        439.1/s |       5.08% |                   442.8
       3 |  0.70 s |                 292.7 |                        146.4/s |       6.79% |                   467.5
       4 |  0.85 s |                 219.5 |                         73.2/s |       8.53% |                   492.1
       5 |  1.00 s |                 175.6 |                         43.9/s |       9.17% |                   516.7
       6 |  1.15 s |                 146.4 |                         29.3/s |       9.27% |                   541.4
       7 |  1.30 s |                 125.5 |                         20.9/s |       9.35% |                   566.0
       8 |  1.45 s |                 109.8 |                         15.7/s |       9.44% |                   590.7

run-independent: once the sticky window exceeds the replica lag, no requests are left that cannot see their own write; the cost is the number of reads sent to the primary.

replication stream: 308 writes/s x 1.8 KB = 1370 GB/month, per replica.
replica placement | unit/GB  | 1 replica | 2 replicas | 3 replicas  (fictional unit/month)
------------------|----------|-----------|------------|-----------------------------------
same region       |        0 |         0 |          0 |       0
other region      |        2 |      2741 |       5482 |    8223
irreversible item: a promoted replica does not return to its old replication link; rebuilding the link means a full 220 GB copy.
within region at 95 MB/s: 0.66 h; across regions at 30 MB/s: 2.09 h + 440 fictional units.

Lag Is One Number, Its Reflection Is a Curve

The first table shows how lag converts into requests. At 0.1 seconds, only 208 of 327,600 reads are stale; at half a second, 14,702; at one second, 30,039. The curve is not linear, because the stale-read count grows not with the lag itself but with the write density that falls inside the lag window.

The right-hand column carries the real information. Of the 5,253 stale reads at 0.25 seconds of lag, 4,755 — 90% — are requests unable to see their own write. The reason is MD9: read-backs come right after the write, so almost every read that falls inside a narrow lag window is a read-back.

As lag rises from 1 to 5 seconds, total stale reads climb from 30,039 to 37,473, but requests unable to see their own write hold steady at 28,093: since read-backs arrive within 900 ms at the latest, all of them are already stale once lag passes 1 second. Growing the lag further does not produce a new kind of bug — it just piles more inconsistency around the existing one.

The Routing Rule Saves Requests, and Bills the Primary

The second table holds lag fixed at 0.55 seconds and scans the sticky window. The rule rests on the one thing the application knows: after a session writes, its reads go to the primary instead of the replica for W seconds.

At W = 0 there is no rule, and 15,546 requests cannot see their own write. As W rises to 0.25 seconds, 5,078 are saved and 10,468 remain — the window is narrower than the lag, so it is not enough. At W = 0.5, the remainder drops to 1,703. At W = 1 second the remainder is zero, and this line is run-independent: the moment the sticky window exceeds the replica lag, every read touched by the session’s own write goes to the primary.

The cost sits in the right-hand column. The W = 1 setting that zeroes out the remainder shifts 19.58% of reads to the primary: 64,155 of 327,600 reads. The point of opening the replicas was to unload the primary; the rule hands back a fifth of that gain. Pulling W up to 15 seconds for safety pushes the share to 85.16%, and the replicas sit idle. The right setting is not the largest W, it is the W just above the lag; since the lag depends on replica count, the two decisions are tied to each other.

The Diminishing Return of Adding Replicas

The third table raises replica count from 1 to 8. Reads per replica drop from 878.1 to 109.8; that much is expected. The gain column shows the unexpected part: the second replica lifts 439.1 reads/s off the primary, the third 146.4, the fourth 73.2, the eighth 15.7. The seventh and eighth replicas together bring in 8% of what the second replica gained.

Two more columns turn this table on its head. Lag grows with replica count (MD10): 1.45 seconds at 8 replicas, and the stale-read share climbs from 3.34% to 9.44%. Primary load grows too: from 418.2 to 590.7 units, because the replication stream is sent to every replica. By the time the eighth replica is added, the primary carries 41% more load and gains only 15.7 requests/s on the read side. Past this point, adding a replica does not spread the load — it moves it.

Transfer Cost and the Irreversible Item

The last block prices replica placement. The replication stream is 308 writes/s x 1.8 KB, or 1,370 GB per replica each month: 0 fictional units in the same region, 2,741 in another region, 8,223 for three replicas. Spreading replicas across regions looks like a durability decision; on the bill it is a transfer item, and it scales with write rate, not data size.

The irreversible item in this lesson is a subtle one. Once a replica is promoted to primary, it does not return to its old replication link; rebuilding that link means a full 220 GB copy: 0.66 hours within a region, 2.09 hours across regions, and 440 fictional units. Pressing the promote button is a one-second action; undoing it is not.

Summary

• As replica lag rises from 0.1 to 5 seconds, stale reads climb from 208 to 37,473; the curve is not linear, it tracks the write density that falls inside the lag window.
• At narrow lag, 90% of stale reads are requests unable to see their own write; past 1 second of lag this count locks at 28,093, and only the inconsistency around it keeps growing.
• Once the sticky window exceeds the lag (W = 1 s at 0.55 s of lag), requests unable to see their own write drop to zero; the cost is 19.58% of reads shifting to the primary.
• Opening the window to 15 seconds saves the same 15,546 requests but carries 85.16% of reads to the primary: most of the gain from opening the replicas is handed back.
• The return on adding replicas shrinks (439.1 -> 15.7 reads/s), and two costs rise: lag from 0.40 to 1.45 seconds, primary load from 418.2 to 590.7 units.
• An out-of-region replica is a transfer item that scales with write rate (1,370 GB a month, 2,741 fictional units); a promoted replica does not return to its replication link, and rebuilding it is a full 220 GB copy.

Next Step

These two lessons measured the renting of a relational database, and most of the kept decisions turned out to be settings: a badly chosen sticky window can be changed, an excess replica can be closed. The next lesson asks the same question on the key-value side, where a single item turns the table upside down: data lands according to the chosen partition key, and once that key is chosen, which queries can be served is fixed. Three candidate keys will be run against the same real key distribution, the busiest partition’s share and the access patterns the key does not support will be counted, and then the cost of changing the key will be computed in bytes and time.