--- title: 'Secondary Indexes and Streams' source: 'https://academia.sh/en/courses/cloud-storage/secondary-indexes-and-streams' course: 'Storage and Data in the Cloud' language: en updated: '2026-09-13T05:07:36+00:00' license: 'CC BY-SA 4.0' objectives: - 'Counts how many of the unserved patterns three secondary indexes recover, and how many requests a day each brings back' - "Separates an index's cost into extra writes, extra storage, and the stale-result share caused by index lag" - 'Compares the projection choice between index size and the number of round trips to the base table' - 'Models a change stream with a consumer and measures event order, reprocessed events, and the missed window' --- # Secondary Indexes and Streams Recovering the previous lesson's three unserved access patterns with a secondary index, and counting what that costs in return: each index's recovered requests, the extra write cost it adds, the stale-result share caused by storage and index lag, the trade-off between projection choice and round trips to the base table, and a change stream modeled with a consumer — measuring event order, reprocessed events, and the missed window once the stream's retention runs out. 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. ```js // 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; ``` ```js // 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.