--- title: Denormalization source: 'https://academia.sh/en/courses/scaling-the-data/denormalization' course: 'Scaling the Data Layer' language: en updated: '2026-08-23T07:01:33+00:00' license: 'CC BY-SA 4.0' --- # Denormalization Shortening the read path as a scaling decision: how the narrow and wide copy drive secondary-table records toward zero, why the number of tables touched is a misleading measure, measuring the bytes the copy adds to a shipment record in a real engine, and how far the scope the copy has to track the source over pushes the store's write/read ratio past 2.33. Partitioning strategy settled where data goes, not what shape it stands in. The 1,500,137 records the lookup table scanned was the count read to produce an answer, and not all of it came from the shipment table. The seller's invoice line multiplies weight by tariff, tariff by zone factor, and the result by contract discount; the three factors sit in three separate tables. The read path spans more than one table even within a single partition, and crosses the partition boundary too when partitions are spread across separate stores. **Denormalization** is deliberately reintroducing redundancy into a normalized schema for read performance. The definition, the normal forms, and the consistency rule for a derived column were established in the Denormalization lesson of the Data Modeling and Relational Theory course, which showed that every write path touching the source must fill the copy and that an audit job recomputing from the source is required. None of that is retold here. This lesson's question is a scaling question: **how many records** the copy drops from the read, **how many bytes** it adds to the shipment record, and **how far** it pushes K01's write/read ratio once it must track the source. ## Narrow Copy and Wide Copy The copy's width is a separate decision. The **narrow copy** carries only the numbers the calculation needs: tariff rate, zone factor, contract discount. The **wide copy** also carries the names the report displays: tariff name and zone name. Both target the same read, but what they store and must track differ. The measurement runs on a real engine, at lesson 01's model scale — 20 sellers, 30 days, 100 shipments a day — so the numbers compare with that lesson's. Three things are measured: the query's **records pulled from secondary tables**, the shipment table's **bytes occupied**, and the **rows rewritten** when a source field changes. ```js // schema/copy.mjs — the records the same invoice query pulls, the bytes it occupies, and // the rows rewritten when a source changes, across three schemas. A real engine runs with // node:sqlite; SQL is not taught, what is measured is record count and bytes. import { DatabaseSync } from "node:sqlite"; const SELLER = 20, DAY = 30, DAILY = 100, TARIFF = 40, ZONE = 12; // lesson 01's model scale const tariffName = (i) => `TARIFF-${String(i).padStart(3, "0")} domestic express`; const zoneName = (i) => `ZONE-${String(i).padStart(2, "0")} west anatolia`; const EXTRA = { normal: "", narrow: ", rate REAL, factor REAL, discount REAL", wide: ", rate REAL, factor REAL, discount REAL, tariff_name TEXT, zone_name TEXT" }; function setup(schema) { const db = new DatabaseSync(":memory:"); db.exec(`CREATE TABLE tariff(id INTEGER PRIMARY KEY, name TEXT, rate REAL); CREATE TABLE zone(id INTEGER PRIMARY KEY, name TEXT, factor REAL); CREATE TABLE contract(seller INTEGER PRIMARY KEY, discount REAL); CREATE TABLE shipment(id TEXT PRIMARY KEY, seller INTEGER, day INTEGER, tariff_id INTEGER, zone_id INTEGER, weight REAL${EXTRA[schema]}); CREATE INDEX shipment_seller ON shipment(seller, day);`); db.exec("BEGIN"); for (let i = 0; i < TARIFF; i += 1) db.prepare("INSERT INTO tariff VALUES(?,?,?)").run(i, tariffName(i), 12 + (i % 7) * 0.5); for (let i = 0; i < ZONE; i += 1) db.prepare("INSERT INTO zone VALUES(?,?,?)").run(i, zoneName(i), 1 + (i % 5) * 0.1); for (let i = 0; i < SELLER; i += 1) db.prepare("INSERT INTO contract VALUES(?,?)").run(i, (i % 9) * 0.01); const columns = { normal: 6, narrow: 9, wide: 11 }[schema]; const g = db.prepare(`INSERT INTO shipment VALUES(${"?,".repeat(columns - 1)}?)`); for (let s = 0; s < SELLER; s += 1) for (let d = 0; d < DAY; d += 1) for (let k = 0; k < DAILY; k += 1) { const ti = (s * 7 + d + k) % TARIFF, bi = (s + k) % ZONE; const t = [`TR-${s}-${d}-${k}`, s, d, ti, bi, 1 + (k % 30)]; const n = [12 + (ti % 7) * 0.5, 1 + (bi % 5) * 0.1, (s % 9) * 0.01]; g.run(...(schema === "normal" ? t : schema === "narrow" ? [...t, ...n] : [...t, ...n, tariffName(ti), zoneName(bi)])); } db.exec("COMMIT"); return db; } const QUERY = { // a seller's 30-day invoice line, broken down by tariff normal: `SELECT t.name, sum(g.weight * touch(t.rate) * touch(b.factor) * (1 - touch(s.discount))) FROM shipment g JOIN tariff t ON t.id = g.tariff_id JOIN zone b ON b.id = g.zone_id JOIN contract s ON s.seller = g.seller WHERE g.seller = ? AND g.day BETWEEN 0 AND 29 GROUP BY t.name`, narrow: `SELECT touch(t.name), x.amount FROM (SELECT tariff_id, sum(weight * rate * factor * (1 - discount)) AS amount FROM shipment WHERE seller = ? AND day BETWEEN 0 AND 29 GROUP BY tariff_id) x JOIN tariff t ON t.id = x.tariff_id`, wide: `SELECT tariff_name, sum(weight * rate * factor * (1 - discount)) FROM shipment WHERE seller = ? AND day BETWEEN 0 AND 29 GROUP BY tariff_name`, }; console.log(`model: ${SELLER * DAY * DAILY} shipments, ${SELLER} sellers, ${DAY} days, ` + `${TARIFF} tariffs, ${ZONE} zones; invoice line is broken down by tariff`); const measure = {}; let base = 0; for (const schema of ["normal", "narrow", "wide"]) { const db = setup(schema); let touches = 0; db.function("touch", (x) => { touches += 1; return x; }); db.prepare(QUERY[schema]).all(10); const plan = db.prepare(`EXPLAIN QUERY PLAN ${QUERY[schema]}`).all() .flatMap((r) => r.detail.match(/(?:SCAN|SEARCH) \w+/g) ?? []); const bytes = db.prepare("SELECT sum(pgsize) AS b FROM dbstat WHERE name LIKE 'shipment%'").get().b; if (base === 0) base = bytes; const changes = (sql, ...a) => db.prepare(sql).run(...a).changes; const display = changes("UPDATE tariff SET name = 'TARIFF-003 new name' WHERE id = 3") + (schema === "wide" ? changes("UPDATE shipment SET tariff_name = 'TARIFF-003 new name' WHERE tariff_id = 3") : 0); const computed = changes("UPDATE contract SET discount = 0.05 WHERE seller = 10") + (schema === "normal" ? 0 : changes("UPDATE shipment SET discount = 0.05 WHERE seller = 10")); measure[schema] = { tables: new Set(plan).size, secondary: touches, bytes, extra: (bytes - base) / (SELLER * DAY * DAILY), display, computed }; } const report = (title, fields) => { console.log(`\n${title.padEnd(26)}${["normal", "narrow", "wide"].map((s) => s.padStart(12)).join("")}`); for (const [name, f, b] of fields) console.log(name.padEnd(26) + ["normal", "narrow", "wide"] .map((s) => (b ? b(measure[s][f]) : String(measure[s][f])).padStart(12)).join("")); }; report("read path", [["tables touched", "tables"], ["secondary-table records", "secondary"], ["shipment table bytes", "bytes", (x) => x.toLocaleString("en-US")], ["extra bytes per shipment", "extra", (x) => x.toFixed(2)]]); report("write path (rows)", [["tariff name change", "display"], ["contract discount change", "computed"]]); console.log(`\nrecords read from the main table are ${DAY * DAILY} in all three schemas`); ``` ``` model: 60000 shipments, 20 sellers, 30 days, 40 tariffs, 12 zones; invoice line is broken down by tariff read path normal narrow wide tables touched 4 3 1 secondary-table records 9000 40 0 shipment table bytes 2,437,120 3,641,344 6,709,248 extra bytes per shipment 0.00 20.07 71.20 write path (rows) normal narrow wide tariff name change 1 1 1498 contract discount change 1 3001 3001 records read from the main table are 3000 in all three schemas ``` These numbers belong to the measurement class: they come from an engine running on this machine, they are deterministic, but page fill factor and plan selection are the engine's own decisions. ## Tables Touched Is the Wrong Measure Read alone, the first row makes the narrow copy look useless: tables touched drops from four to three, and three is still more than one. The second row corrects that. In the normalized schema, the engine pulls **9000 records** from secondary tables — for each of three thousand shipments, the tariff, zone, and contract rows are looked up separately. In the narrow copy, the same number is **40**: the calculation finishes from a single table, and tariff names are looked up **after** summing, once per breakdown. So the narrow copy lowered tables touched by one, and records pulled by 225 times. The result is a reading rule: **a read's cost is measured not by the number of tables it touches, but by the number of records it pulls from secondary tables.** Looking once per breakdown at a small, rarely changing table is not the same thing as looking once per row. This distinction changes character in a partitioned layer. The Partitioning Strategies lesson spread partitions across separate stores; in the normalized schema, each of the 9000 lookups is a cross-partition access unless the tariff and zone tables were copied to every partition. The narrow copy drives those crossings to zero, and the remaining 40 are served from a small table copied once per partition. The wide copy drops records pulled from 40 to 0. The gain is 40 records; its cost sits two rows below. ## Where the Write Path Stretches The table below separates two kinds of source change, and they are not the same thing. **A tariff name change** is a display field. In the normalized and narrow schemas, a single row is updated. In the wide copy, 1498 rows are updated, because the name was copied into the relevant breakdown of sixty thousand shipments. This is the cost of the wide copy's 40-record read gain. **A contract discount change** is a computed field, and in both copy schemas, 3001 rows are updated. The difference comes not from the schema but from the **contract**: whether the discount applies retroactively. In pricing, a past invoice is frozen — an issued invoice does not change afterward — so the copy can be defined as a **snapshot**, and under that definition propagation drops to zero. If the same copy must track the source instead, propagation runs for as long as the record is retained. Which one a copy is cannot be read off the schema; it is a written decision. ## Back to the Numbers The measured extra bytes and propagation figures are converted into K01's rows. Propagation's scope needs an assumption, and that assumption is **not added** to K01's table. **D5 — daily source field changes: 1 tariff, 5 contract.** Rationale: tariff and contract fields are tied to commercial agreements and change rarely within a day. Sensitivity is given at ten times. ```js // schema/cost.mjs — the effect of the measured extra bytes and write propagation on K01's rows const V3 = 400_000, V4 = 7, V6 = 220, V7 = 900, V12 = 730; // K01 assumptions const GROWTH = 976, STORED = 712.48, RATIO = 2.33; // K01 computed values const DAY_SECONDS = 86_400; const READ = (V3 * 30 * 0.1) / DAY_SECONDS; // K01: reads behind cache, average const WRITE = (V3 * V4) / DAY_SECONDS; // K01: state event write, average const EXTRA = { normal: 0, narrow: 20.07, wide: 71.20 }; // schema/copy.mjs measurement const TARIFF_SHARE = 1497 / 60_000; // schema/copy.mjs: rate bound to one tariff const SELLER_DAILY = V3 / 4000; // K01: daily invoice lines 4000 const D5 = { tariff: 1, contract: 5 }; // this lesson's assumption const growth = (extra) => (V3 * (V7 + extra) + V3 * V4 * V6) / 1e6; console.log(`average store reads ${READ.toFixed(2)}/s, writes ${WRITE.toFixed(2)}/s, ` + `ratio ${(WRITE / READ).toFixed(2)} (K01: ${RATIO})`); console.log(`\n${"schema".padEnd(8)}${"record bytes".padStart(14)}${"daily MB".padStart(12)}` + `${"x".padStart(7)}${"stored GB".padStart(13)}${"x".padStart(7)}`); for (const [name, extra] of Object.entries(EXTRA)) { const a = growth(extra), s = (a * V12) / 1000; console.log(name.padEnd(8) + (V7 + extra).toFixed(2).padStart(14) + a.toFixed(2).padStart(12) + (a / GROWTH).toFixed(3).padStart(7) + s.toFixed(2).padStart(13) + (s / STORED).toFixed(3).padStart(7)); } console.log(`\npropagation of a source change in the wide copy (D5: ${D5.tariff} tariff field, ` + `${D5.contract} contract fields per day)`); console.log(`${"propagation scope".padEnd(20)}${"tariff records".padStart(16)}${"contract records".padStart(18)}` + `${"rewrite/s".padStart(17)}${"write/read".padStart(13)}`); for (const [name, g] of [["history, 730 days", V12], ["period, 30 days", 30], ["open shipment, 5 days", 5]]) { const t = g * V3 * TARIFF_SHARE, s = g * SELLER_DAILY; const rewrite = (D5.tariff * t + D5.contract * s) / DAY_SECONDS; console.log(name.padEnd(20) + Math.round(t).toLocaleString("en-US").padStart(16) + Math.round(s).toLocaleString("en-US").padStart(18) + rewrite.toFixed(2).padStart(17) + ((WRITE + rewrite) / READ).toFixed(2).padStart(13)); } const scope = 30, t = scope * V3 * TARIFF_SHARE, s = scope * SELLER_DAILY; for (const factor of [1, 10]) { const y = factor * (D5.tariff * t + D5.contract * s) / DAY_SECONDS; console.log(`D5 sensitivity (30 days, ${factor}x): rewrite ${y.toFixed(2)}/s, ` + `write/read ${((WRITE + y) / READ).toFixed(2)}`); } ``` ``` average store reads 13.89/s, writes 32.41/s, ratio 2.33 (K01: 2.33) schema record bytes daily MB x stored GB x normal 900.00 976.00 1.000 712.48 1.000 narrow 920.07 984.03 1.008 718.34 1.008 wide 971.20 1004.48 1.029 733.27 1.029 propagation of a source change in the wide copy (D5: 1 tariff field, 5 contract fields per day) propagation scope tariff records contract records rewrite/s write/read history, 730 days 7,285,400 73,000 88.55 8.71 period, 30 days 299,400 3,000 3.64 2.60 open shipment, 5 days 49,900 500 0.61 2.38 D5 sensitivity (30 days, 1x): rewrite 3.64/s, write/read 2.60 D5 sensitivity (30 days, 10x): rewrite 36.39/s, write/read 4.95 ``` The first line is a check: the write/read ratio from the average rates is 2.33, the same ratio K01 derives from peak rates. Because both rates carry the same peak factor, the ratio does not change, and this shows the following rows are comparable with K01. **The storage cost is small.** The narrow copy raises the shipment record from 900 bytes to 920.07 bytes: daily growth from 976 MB to 984.03 MB, stored data from 712.48 GB to 718.34 GB. Even in the wide copy, the ratio is 1.029. Denormalization's expense is not disk. **The write cost depends on scope, and the range is wide.** If the copy propagates back through history, a tariff change rewrites 7,285,400 records; under D5 the daily rewrite rate is 88.55 records/s, and `write/read ratio at store` climbs from 2.33 to **8.71**. Tracking only period records gives 2.60; only open shipments, 2.38. **The copy's width determines the read gain, its scope determines the write cost, and the two are separate decisions.** The sensitivity rows say scope matters more than frequency: when D5 goes to ten times, the ratio in the thirty-day scope moves from 2.60 to 4.95, but the scope that propagates through history is already at 8.71 at a single multiple. Misjudging frequency is cheaper than choosing the wrong scope. ## Summary - Denormalization is a scaling decision here: its measure is the records the copy drops from the read, the bytes it adds to the record, and the scope over which it tracks the source. - A read's cost is not measured by tables touched: the narrow copy dropped that from four to three while dropping records pulled from secondary tables from 9000 to 40. - In a partitioned layer, those 9000 lookups are cross-partition accesses; the narrow copy zeroes them out, and the remaining 40 lookups are served from a small table copied per partition. - The storage cost is small: the shipment record climbs from 900 bytes to 920.07 (narrow) and 971.20 bytes (wide), stored data from 712.48 GB to 718.34 and 733.27 GB. - The real cost is in propagation's scope: `write/read ratio at store` climbs from 2.33 to 8.71 propagating through history, to 2.60 in the period scope, to 2.38 in the open-shipment scope. - The wide copy writes 1498 rows when a display field changes; in a copy defined as a snapshot, the same change stays a single row. ## Next Step The copy dropped the number of records the read pulls, but never touched the number the read **scans**: the seller's invoice line still reads and sums three thousand shipments one by one, which at K01's scale is 833.33 records/s within a four-hour window. The sum is redone on every request, even though the same period's sum does not change during the day. The next lesson takes on this repetition: precomputing and storing the sum, how far the scanned-record count drops, what refreshing the stored result costs, and how the refresh interval sets the accepted staleness window.