Secondary Indexes and Streams

The previous lesson found meter_id to be the best-balanced partition key candidate, and left a debt on the same line: three of the six access patterns — the region’s daily total, the threshold scan, the last hour’s readings — could never fix the key, so they went entirely unserved: 30% of requests. Since changing the key is a door that closed at 518.4 thousand fictional units, only one path remains: placing the same data once more, under a different key.

This is called a secondary index; building the index, keeping it updated, and partitioning it are delegated decisions. What is not delegated is the choice itself: which key, which attributes, how many indexes.

Mechanics

MD19 — the base table is built on the previous lesson’s meter_id | time key; the three unserved patterns are A2 at 17%, A4 at 8%, and A6 at 5%. MD20 — three secondary index candidates: region | day#meter_id, day | reading_value, and day#hour | meter_id; the second is a sparse index and carries only items past the threshold (4 in 1,000 of the items). MD21 — the index updates asynchronously, with 0.8 seconds of lag. MD22 — the projection options are 48, 96, and 180 bytes per item; the fictional storage price is 0.9 unit/GB-month, a write unit is 5 times a read, and the per-request price is 0.25 unit/1,000 requests, same as the previous lesson. MD23 — the change stream has 16 shards and 24 hours of retention; the event rate equals the write rate (44.4 events/s), and consumer capacity is 55 events/s. MD24 — the consumer checkpoints its position every C seconds, and the network averages 0.4 crashes a day. The capacity margin in MD23 is the determinant; write 45 instead of 55 and the catch-up-time column blows up.

// md/model.mjs — SECONDARY INDEX AND CHANGE STREAM model. Builds on the previous lesson's base
// table (partition key meter_id); the three unserved patterns found there are the input. The
// randomness lives in the event generator, hand-written, seed visible.
export const ITEMS = 345_600_000, DAILY_WRITES = 3_840_000, DAILY_REQUESTS = 103_700_000;
export const WRITE_FACTOR = 5, PRICE_REQUEST = 0.25, PRICE_GB_MONTH = 0.9, ITEM_BYTES = 180;
export const LAMBDA = DAILY_WRITES / 86400;              // events/s = writes/s

// [name, pattern recovered, pattern weight, indexed item fraction, writes/s hitting that range]
export const INDEX = [
  ["region | day#meter_id", "A2 region daily total", 17, 1, LAMBDA * 0.27],
  ["day | reading_value", "A4 threshold-exceeding readings", 8, 0.004, LAMBDA * 0.004],
  ["day#hour | meter_id", "A6 last hour", 5, 1, LAMBDA],
];
export const PROJECTION = [["key only", 48], ["selected attributes", 96], ["all attributes", 180]];

export const writeCostMonth = (fraction) =>
  DAILY_WRITES * fraction * 30 * WRITE_FACTOR / 1000 * PRICE_REQUEST;
export const storageCostMonth = (fraction, bytes) => ITEMS * fraction * bytes / 1e9 * PRICE_GB_MONTH;
export const staleItems = (writeRate, L) => writeRate * L;
export const staleQueryShare = (writeRate, L) => 1 - Math.exp(-writeRate * L);

// Change stream: every write is an event. Event ORDER is preserved only within the same
// partition key; when the consumer reads shards in turn, the overall time order breaks.
export function readStream({ count = 60000, shards = 16, batch = 64, seed = 20260804 } = {}) {
  let s = seed % 2147483647;
  const rand = () => (s = (s * 48271) % 2147483647) / 2147483647;
  const hash = (x) => { let h = 2166136261;
    for (const c of String(x)) h = Math.imul(h ^ c.charCodeAt(0), 16777619); return (h >>> 0) % shards; };
  const buckets = Array.from({ length: shards }, () => []);
  let t = 0;
  for (let i = 0; i < count; i += 1) {                    // events are generated in time order
    t += rand() * 2 / LAMBDA;
    const meter = Math.floor(rand() * 40000);
    buckets[hash(meter)].push({ t, meter });
  }
  const consumed = [];                                    // consumer: `batch` events per shard
  for (let d = 0; consumed.length < count; d += 1)
    for (let p = 0; p < shards; p += 1)
      for (const o of buckets[p].slice(d * batch, (d + 1) * batch)) consumed.push(o);
  let outOfOrder = 0, maxSkew = 0, highWatermark = -Infinity;
  const lastByKey = new Map();
  let withinKeyOutOfOrder = 0;
  for (const o of consumed) {
    if (o.t < highWatermark) { outOfOrder += 1; maxSkew = Math.max(maxSkew, highWatermark - o.t); }
    highWatermark = Math.max(highWatermark, o.t);
    if ((lastByKey.get(o.meter) ?? -Infinity) > o.t) withinKeyOutOfOrder += 1;
    lastByKey.set(o.meter, o.t);
  }
  return { count, shards, outOfOrder, maxSkew, withinKeyOutOfOrder, duration: t };
}

// Pause: the consumer stops for P seconds, then catches up at K events/s. The stream's
// retention is R; every second beyond P > R has its events fall off the stream and NEVER RETURN.
export const K = 55, R = 24 * 3600, CRASH_RATE = 0.4;
export function pause(P) {
  const backlog = LAMBDA * P;
  const catchup = K > LAMBDA ? backlog / (K - LAMBDA) : Infinity;
  return { backlog, catchup, missed: LAMBDA * Math.max(0, P - R), total: P + catchup };
}
export const reprocessed = (C) => LAMBDA * C * CRASH_RATE;

// md/measure.mjs — the query and cost a secondary index recovers; the change stream's consumer side
import { INDEX, PROJECTION, writeCostMonth, storageCostMonth, staleItems, staleQueryShare, readStream,
  pause, reprocessed, ITEMS, ITEM_BYTES, DAILY_WRITES, DAILY_REQUESTS, LAMBDA, K, R, CRASH_RATE } from "./model.mjs";

const s = (x, n) => String(x).padStart(n);
const L = 0.8;
console.log(`base table: ${(ITEMS / 1e6).toFixed(1)}M items, ${(DAILY_WRITES / 1e6).toFixed(2)}M writes/day, ` +
  `${(DAILY_REQUESTS / 1e6).toFixed(1)}M requests/day. Unserved patterns: A2 17%, A4 8%, A6 5% (30% total).`);
console.log(`index lag ${L} s, event rate ${LAMBDA.toFixed(1)}/s.\n`);
console.log("secondary index       | recovered req/day    | items indexed  | extra writes (unit/mo) | extra storage (unit/mo) | stale items | stale result");
console.log("----------------------|----------------------|----------------|------------------------|-------------------------|-------------|-------------");
let recoveredT = 0, writeT = 0, storageT = 0;
for (const [name, , weight, fraction, rate] of INDEX) {
  const recovered = DAILY_REQUESTS * weight / 100;
  const write = writeCostMonth(fraction), storage = storageCostMonth(fraction, 180);
  recoveredT += recovered; writeT += write; storageT += storage;
  console.log(`${name.padEnd(21)} | ${s((recovered / 1e6).toFixed(1) + "M (" + weight + "%)", 20)} | ` +
    `${s((ITEMS * fraction / 1e6).toFixed(1) + "M", 14)} | ${s(write.toFixed(0), 22)} | ${s(storage.toFixed(1), 23)} | ` +
    `${s(staleItems(rate, L).toFixed(1), 11)} | ${s((100 * staleQueryShare(rate, L)).toFixed(1) + "%", 12)}`);
}
console.log(`total: ${(recoveredT / 1e6).toFixed(1)}M requests recovered a day (unserved 30% -> 0%); ` +
  `cost ${(writeT + storageT).toFixed(0)} fictional units/month.`);
console.log(`extra writes are ${(writeT / storageT).toFixed(0)}x extra storage: the secondary index's ` +
  `cost is not the bytes it holds, it is every write happening a second time.`);

console.log("\nprojection choice (for the A2 index only, 345.6M items):");
console.log("projection           | bytes/item      | index size   | storage (unit/mo)  | round trip to base ");
console.log("---------------------|-----------------|--------------|--------------------|--------------------");
for (const [name, bytes] of PROJECTION)
  console.log(`${name.padEnd(20)} | ${s(bytes, 15)} | ${s((ITEMS * bytes / 1e9).toFixed(1) + " GB", 12)} | ` +
    `${s(storageCostMonth(1, bytes).toFixed(0), 18)} | ${s(bytes === 180 ? "not needed" : "1 read per item", 19)}`);

const a = readStream({ batch: 24 });
console.log(`\nchange stream: ${a.count} events, ${a.shards} shards, a ${a.duration.toFixed(0)} s window. Seed 20260804.`);
console.log(`events out of order in global time: ${a.outOfOrder} (${(100 * a.outOfOrder / a.count).toFixed(1)}%), ` +
  `largest time skew ${a.maxSkew.toFixed(2)} s.`);
console.log(`events out of order within the same partition key: ${a.withinKeyOutOfOrder}. The guarantee lives here, not in the global order.`);
console.log(`reprocessing (checkpoint interval C, ${CRASH_RATE} crashes a day):`);
console.log("C     | 1 s  | 5 s  | 15 s  | 60 s  | 300 s ");
console.log("event | " + [1, 5, 15, 60, 300].map((C) => s(reprocessed(C).toFixed(0), C < 10 ? 4 : C < 100 ? 5 : 6)).join(" | "));

console.log(`\npause: consumer capacity ${K} events/s, event rate ${LAMBDA.toFixed(1)}/s ` +
  `-> catch-up factor ${(K / (K - LAMBDA)).toFixed(2)}. Stream retention ${R / 3600} h.`);
console.log("pause     | backlog       | catch-up       | outage to full catch-up  | missed        ");
console.log("----------|---------------|----------------|--------------------------|----------------");
for (const P of [900, 3600, 4 * 3600, 12 * 3600, 24 * 3600, 30 * 3600]) {
  const d = pause(P);
  console.log(`${s((P / 3600).toFixed(2) + " h", 9)} | ${s((d.backlog / 1e3).toFixed(0) + "K", 13)} | ` +
    `${s((d.catchup / 3600).toFixed(2) + " h", 14)} | ${s((d.total / 3600).toFixed(2) + " h", 24)} | ` +
    `${s(d.missed === 0 ? "0" : (d.missed / 1e3).toFixed(0) + "K", 14)}`);
}
console.log(`run-independent: events are missed only once the pause exceeds the stream's retention ` +
  `(${R / 3600} h) and cannot be recovered; catch-up time does not change this, it only lengthens it.`);

// Transfer cost: if the consumer is in another region, every event crosses the region boundary
// (an event = the item's before and after image, i.e. twice the item size). Fictional unit.
const gbMonth = DAILY_WRITES * 2 * ITEM_BYTES * 30 / 1e9;
console.log(`\ntransfer: ${gbMonth.toFixed(1)} GB/month of event traffic. Consumer in the same region: 0, ` +
  `another region: ${(gbMonth * 2).toFixed(0)}, outside the region: ${(gbMonth * 9).toFixed(0)} fictional units/month.`);

base table: 345.6M items, 3.84M writes/day, 103.7M requests/day. Unserved patterns: A2 17%, A4 8%, A6 5% (30% total).
index lag 0.8 s, event rate 44.4/s.

secondary index       | recovered req/day    | items indexed  | extra writes (unit/mo) | extra storage (unit/mo) | stale items | stale result
----------------------|----------------------|----------------|------------------------|-------------------------|-------------|-------------
region | day#meter_id |          17.6M (17%) |         345.6M |                 144000 |                    56.0 |         9.6 |       100.0%
day | reading_value   |            8.3M (8%) |           1.4M |                    576 |                     0.2 |         0.1 |        13.3%
day#hour | meter_id   |            5.2M (5%) |         345.6M |                 144000 |                    56.0 |        35.6 |       100.0%
total: 31.1M requests recovered a day (unserved 30% -> 0%); cost 288688 fictional units/month.
extra writes are 2572x extra storage: the secondary index's cost is not the bytes it holds, it is every write happening a second time.

projection choice (for the A2 index only, 345.6M items):
projection           | bytes/item      | index size   | storage (unit/mo)  | round trip to base 
---------------------|-----------------|--------------|--------------------|--------------------
key only             |              48 |      16.6 GB |                 15 |     1 read per item
selected attributes  |              96 |      33.2 GB |                 30 |     1 read per item
all attributes       |             180 |      62.2 GB |                 56 |          not needed

change stream: 60000 events, 16 shards, a 1349 s window. Seed 20260804.
events out of order in global time: 56199 (93.7%), largest time skew 94.73 s.
events out of order within the same partition key: 0. The guarantee lives here, not in the global order.
reprocessing (checkpoint interval C, 0.4 crashes a day):
C     | 1 s  | 5 s  | 15 s  | 60 s  | 300 s 
event |   18 |   89 |   267 |  1067 |   5333

pause: consumer capacity 55 events/s, event rate 44.4/s -> catch-up factor 5.21. Stream retention 24 h.
pause     | backlog       | catch-up       | outage to full catch-up  | missed        
----------|---------------|----------------|--------------------------|----------------
   0.25 h |           40K |         1.05 h |                   1.30 h |              0
   1.00 h |          160K |         4.21 h |                   5.21 h |              0
   4.00 h |          640K |        16.84 h |                  20.84 h |              0
  12.00 h |         1920K |        50.53 h |                  62.53 h |              0
  24.00 h |         3840K |       101.05 h |                 125.05 h |              0
  30.00 h |         4800K |       126.32 h |                 156.32 h |           960K
run-independent: events are missed only once the pause exceeds the stream's retention (24 h) and cannot be recovered; catch-up time does not change this, it only lengthens it.

transfer: 41.5 GB/month of event traffic. Consumer in the same region: 0, another region: 83, outside the region: 373 fictional units/month.

The Index Recovers the Query, and Bills the Write

The three indexes recover all three unserved patterns: 31.1M requests come back a day, and the unserved share drops from 30% to zero. The bill lands on a single column.

Extra writes are 2,572 times extra storage. A full index brings 144,000 fictional units of extra writes a month, while the same index’s 62.2 GB of storage costs only 56 units. The reason is that an index is less a copy than a second write path: every item that goes to the base table is also written to the index. A design that adds two full indexes triples the data layer’s write cost.

The sparse index is the exception to this rule. day | reading_value carries only the items past the threshold — 4 in 1,000 — and recovers the same requests for 576 units a month: one two-hundred-fiftieth of a full index. What sets an index’s cost is not how many queries it recovers, but how many writes it touches.

The last two columns count the result of asynchronous updates. Because the index sits 0.8 seconds behind, at any moment the region index is missing 9.6 items and the hour index 35.6; the region-total and last-hour queries come back missing something on almost every run (100%), but the missing share is small. The real warning is in the middle row: 13.3% of threshold scans miss a reading that just crossed the threshold — one scan in eight misses a fresh alert.

The projection table gives the second trade-off: the key-only index runs 16.6 GB and 15 units a month, the all-attributes index 62.2 GB and 56 units. The 41-unit gap in between is the price of not making an extra read to the base table for every item the index returns, and at a scale of 17.6M queries a day, those reads are far more expensive.

The Change Stream’s Three Numbers

The same data has a second consumer: the change stream, which emits every write as an event. The consumer that produces billing summaries feeds on this stream, and it has to know three things.

The order guarantee lives within the partition key, not across the whole stream. 56,199 of 60,000 events — 93.7% — appear out of order to the consumer in global time, with a maximum time skew of 94.73 seconds. Within the same meter’s events, the out-of-order count is zero: that is where the guarantee lives. A consumer that assumes two different meters’ events arrive in time order is wrong.

Delivery is at least once, not exactly once. When the consumer crashes, it resumes from the last saved position; at a 1-second checkpoint interval, 18 events a day are processed twice, at 300 seconds, 5,333. This count is not a bug — it is the range across which the consumer needs to be idempotent: processing the same event a second time should not add a second line item to the bill.

A missed window cannot be recovered. The last table stops the consumer for P seconds. A 12-hour pause piles up 1.92M events, and the consumer’s catch-up takes 50.53 hours — 62.53 hours from outage to fully caught up. Against that, missed events are zero, because the consumer always reads the oldest event first and no event waits longer than the pause itself. The limit sits in exactly one place: the moment the pause exceeds the stream’s retention. At a 30-hour pause, 960K events fall off the stream and never come back. This adds a new line to the course’s list of irreversible items — and for the first time, what cannot be reversed is not a decision, it is a span of time.

The transfer line is short: 41.5 GB/month of event traffic is free in the same region, 83 in another region, 373 fictional units outside the region — an item that scales with write rate.

Summary

• Three secondary indexes recover all three unserved patterns (31.1M requests a day, 30% down to 0%), and the cost is writes, not storage: extra writes run 2,572 times extra storage (144,000 against 56 fictional units/month); the sparse index recovers the same queries for 576 units.
• The asynchronous index sits 0.8 seconds behind: 9.6 items are missing in the region index, 35.6 in the hour index, and 13.3% of threshold scans miss a reading that just crossed the threshold.
• Projection is a trade-off between storage and reads: key-only runs 16.6 GB and 15 units, all attributes 62.2 GB and 56 units — but the first adds one base-table read for every result item.
• In the change stream, order is preserved only within the partition key: 93.7% of events appear out of order in the global order (largest skew 94.73 s), zero out-of-order pairs within a key.
• The missed window is the one irreversible item: no loss until the pause exceeds retention, and past that (30 h) 960K events fall off and never return.

Next Step

These four lessons measured both ends of the data layer, and both showed the same thing: handing off a decision is cheap, reversing it is expensive. But every request still hit disk. Yet the measurement network’s most frequent question comes from a very narrow set — the latest reading, the meter registry, the open-alarm list — and reading those from the base table every time means producing the same answer thousands of times over the course of a day. The next lesson takes on the managed cache service, which moves that repetition into an in-memory layer, and counts only what being a service brings: which decision is delegated, what the scaling unit is, and how wide the data-loss window opens when a node is lost.