Denormalization

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.

// 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.

// 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.