Monolithic Persistence

The five patterns so far measured waste inside a single store and a single process: computation done in the wrong layer, work piled onto the main thread, too many small calls, reading more than what is needed, and an expensive client being re-created. All of them took the store’s singleness as a given. This lesson questions that assumption.

The symptom is this: the end-of-day billing job no longer fills its four-hour window, but in that same window, the work a write does per request as it reaches the store has measurably grown. In K01’s calculation, peak write is 97.22 requests/s, and that number has not changed; what changed is how much work one write generates in the store. Monolithic persistence is all data classes piled into a single store. The “monolithic” in this name is the same word as the monolith in the Architectural Styles course, but a separate thing: there, the single thing is the deployment unit; here, the single thing is the data store.

Same Symptom, Two Causes

Growth in per-write work can have two separate causes, and the two cannot be told apart without measuring.

First cause: the record grew. New fields have been added to the state event; every write carries more bytes and updates more structures. This is the write-side counterpart of the waste the fourth lesson measured.

Second cause: the store is shared. The record has not changed at all. An index has been added to the same table for another data class’s query, and from that day on, every state event write has to also maintain a structure its own query never uses.

Guessing does not help here; both weigh down the write path and both give the same symptom. The distinguishing measurement is this: break down the records an access path touches and the indexes it consults, by class. In the first cause, a write’s work grows, but all of that work serves its own class; in the second cause, the work splits off a share that does not serve its own class. That share is measurable.

Two Layouts

The setup below is not a model but a real measurement: the same logical data is entered into both layouts, and the counts are read from the store itself. The Scaling the Data Layer course’s federation decision is not retold here; the question here is which symptom that decision grows out of.

KK6 — scale. One-thousandth of K01’s daily volume, over a thirty-day period. Rationale: the ratios between classes come from K01’s assumption table (V3: 400,000 shipments per day, V4: 7 events per shipment, V13: 100 items per invoice line); the divisor only shortens the run time, it does not change the ratios. Its sensitivity is given below through the class mix. This assumption is not added to K01’s table.

// persistence/store.mjs — the same logical data set up in two layouts: all classes in one
// table, and each class in its own store. Built for real with node:sqlite; counts are read
// from SQL. The scale is one-thousandth of K01's daily volume (KK6), the period is 30 days.
import { DatabaseSync } from "node:sqlite";

export const KK6 = 1000;                 // scale divisor
export const DAYS = 30;                  // K01 V11: billing period
export const K01 = { shipment: 400_000, eventFactor: 7, invoiceLine: 100, peakWrite: 97.22 };

export const COUNT = {
  shipment: (K01.shipment / KK6) * DAYS,
  event: ((K01.shipment * K01.eventFactor) / KK6) * DAYS,
  period: (K01.shipment / K01.invoiceLine / KK6) * DAYS,
};

// The data is generated once; the same rows go into both layouts.
export function rows() {
  const c = [];
  const daily = { shipment: COUNT.shipment / DAYS, event: COUNT.event / DAYS, period: COUNT.period / DAYS };
  for (let day = 1; day <= DAYS; day += 1) {
    for (let i = 1; i <= daily.shipment; i += 1) {
      const trackingNo = `TK${day}-${i}`;
      c.push({ type: "shipment", key: trackingNo, seller: null, day });
      for (let k = 1; k <= K01.eventFactor; k += 1) c.push({ type: "event", key: trackingNo, seller: null, day });
    }
    for (let s = 1; s <= daily.period; s += 1) c.push({ type: "period", key: null, seller: `S${s}`, day });
  }
  return c;
}

// Monolithic layout: one table, with indexes serving every class's queries.
export function monolithic(data) {
  const d = new DatabaseSync(":memory:");
  d.exec(`CREATE TABLE record(id INTEGER PRIMARY KEY, type TEXT, key TEXT, seller TEXT, day INTEGER, body BLOB);
          CREATE INDEX ix_key ON record(key);
          CREATE INDEX ix_day ON record(day);
          CREATE INDEX ix_seller ON record(seller)`);
  const y = d.prepare("INSERT INTO record(type,key,seller,day,body) VALUES(?,?,?,?,?)");
  d.exec("BEGIN");
  for (const r of data) y.run(r.type, r.key, r.seller, r.day, null);
  d.exec("COMMIT");
  return d;
}

// Separated layout: each class in its own store, with only its own query's index.
export function separated(data) {
  const shipment = new DatabaseSync(":memory:");
  const event = new DatabaseSync(":memory:");
  const period = new DatabaseSync(":memory:");
  shipment.exec("CREATE TABLE shipment(key TEXT PRIMARY KEY, day INTEGER, body BLOB)");
  event.exec(`CREATE TABLE event(id INTEGER PRIMARY KEY, key TEXT, day INTEGER, body BLOB);
             CREATE INDEX ix_event_key ON event(key)`);
  period.exec("CREATE TABLE period(seller TEXT, day INTEGER, body BLOB, PRIMARY KEY(seller,day)) WITHOUT ROWID");
  const ys = shipment.prepare("INSERT INTO shipment VALUES(?,?,?)");
  const ye = event.prepare("INSERT INTO event(key,day,body) VALUES(?,?,?)");
  const yp = period.prepare("INSERT INTO period VALUES(?,?,?)");
  for (const d of [shipment, event, period]) d.exec("BEGIN");
  for (const r of data) {
    if (r.type === "shipment") ys.run(r.key, r.day, null);
    else if (r.type === "event") ye.run(r.key, r.day, null);
    else yp.run(r.seller, r.day, null);
  }
  for (const d of [shipment, event, period]) d.exec("COMMIT");
  return { shipment, event, period };
}

// The number of indexes consulted while writing to a table is read from SQL (PRAGMA index_list).
export function indexCount(d, table) {
  return d.prepare(`SELECT count(*) n FROM pragma_index_list(?)`).get(table).n;
}

// persistence/measurement.mjs — the record and index count three access paths touch in two layouts
import { KK6, DAYS, K01, COUNT, rows, monolithic, separated, indexCount } from "./store.mjs";

const data = rows();
const mono = monolithic(data);
const sep = separated(data);
const q = (d, s, ...p) => d.prepare(s).get(...p).n;

console.log(`scale 1/${KK6}, period ${DAYS} days -> shipment ${COUNT.shipment}, event ${COUNT.event}, period ${COUNT.period}`);
console.log(`monolithic store record count = ${q(mono, "SELECT count(*) n FROM record")}`);
console.log(`separated stores' record count = ${q(sep.shipment, "SELECT count(*) n FROM shipment")}` +
  ` / ${q(sep.event, "SELECT count(*) n FROM event")} / ${q(sep.period, "SELECT count(*) n FROM period")}\n`);

// S1 tracking read: one tracking number's shipment record and its events.
const TRACKING_NO = "TK15-100";
const s1Mono = q(mono, "SELECT count(*) n FROM record WHERE key=?", TRACKING_NO);
const s1Sep = q(sep.shipment, "SELECT count(*) n FROM shipment WHERE key=?", TRACKING_NO) +
  q(sep.event, "SELECT count(*) n FROM event WHERE key=?", TRACKING_NO);

// S2 period scan: 30 days of seller-day records; the shared day index walks every class.
const s2Mono = q(mono, "SELECT count(*) n FROM record WHERE day BETWEEN 1 AND ?", DAYS);
const s2Sep = q(sep.period, "SELECT count(*) n FROM period WHERE day BETWEEN 1 AND ?", DAYS);

console.log("path                  monolithic  separated  ratio  needed");
console.log("--------------------- ----------- --------- ----- --------");
const row = (name, a, b, needed) => console.log(`${name.padEnd(21)} ${String(a).padStart(11)} ${String(b).padStart(9)} ` +
  `${(a / b).toFixed(2).padStart(5)} ${String(needed).padStart(8)}`);
row("S1 tracking read", s1Mono, s1Sep, 4);
row("S2 period scan", s2Mono, s2Sep, COUNT.period);

// S3 state event write: indexes consulted, and the index serving its own query.
const dMono = indexCount(mono, "record");
const dSep = indexCount(sep.event, "event");
console.log(`\nS3 state event write: monolithic consults ${dMono} indexes, separated store consults ${dSep}`);
console.log(`the share serving their own class's query: 1 / ${dMono} = ${(1 / dMono).toFixed(2)}` +
  ` and 1 / ${dSep} = ${(1 / dSep).toFixed(2)}`);
console.log(`K01 peak write ${K01.peakWrite} requests/s -> index maintenance ${(K01.peakWrite * dMono).toFixed(2)}/s and` +
  ` ${(K01.peakWrite * dSep).toFixed(2)}/s, difference ${(K01.peakWrite * (dMono - dSep)).toFixed(2)}/s`);

// What splitting costs: the write path now spans two stores (the event log + the shipment's latest state).
const splitWrites = 2, monoWrites = 1;
console.log(`\nwhat splitting costs: one state event is written to ${monoWrites} store instead of ${splitWrites}` +
  ` -> ${(K01.peakWrite * splitWrites).toFixed(2)} store writes/s (${(K01.peakWrite * monoWrites).toFixed(2)} in the monolithic store)`);
console.log(`stores operated 1 -> 3; the write path is no longer atomic at ${K01.peakWrite.toFixed(2)} requests/s`);

// Retention rule: one rule applies to every class (K01 V12 = 730 days, the period only needs 30 days).
const [RETENTION, PERIOD_NEEDED] = [730, DAYS];
const periodDaily = COUNT.period / DAYS;
console.log(`\nretention: one rule, 730 days -> period records are held ${RETENTION} days, needed ${PERIOD_NEEDED} days`);
console.log(`at scale, period records held ${periodDaily * RETENTION} instead of ${periodDaily * PERIOD_NEEDED}` +
  ` -> ${(RETENTION / PERIOD_NEEDED).toFixed(2)}x too many`);

// KK6's sensitivity: if the class mix changes, what does S2's crossing factor become.
console.log(`\nclass mix (K01 V13 = items per invoice line) -> S2 crossing factor`);
for (const item of [100, 20, 5, 1]) {
  const d = (K01.shipment / item / KK6) * DAYS;
  const total = COUNT.shipment + COUNT.event + d;
  console.log(`  items ${String(item).padStart(3)} -> period records ${String(d).padStart(6)},` +
    ` period share ${(d / total).toFixed(4)}, factor ${(total / d).toFixed(2)}`);
}

scale 1/1000, period 30 days -> shipment 12000, event 84000, period 120
monolithic store record count = 96120
separated stores' record count = 12000 / 84000 / 120

path                  monolithic  separated  ratio  needed
--------------------- ----------- --------- ----- --------
S1 tracking read                8         8  1.00        4
S2 period scan              96120       120 801.00      120

S3 state event write: monolithic consults 3 indexes, separated store consults 1
the share serving their own class's query: 1 / 3 = 0.33 and 1 / 1 = 1.00
K01 peak write 97.22 requests/s -> index maintenance 291.66/s and 97.22/s, difference 194.44/s

what splitting costs: one state event is written to 1 store instead of 2 -> 194.44 store writes/s (97.22 in the monolithic store)
stores operated 1 -> 3; the write path is no longer atomic at 97.22 requests/s

retention: one rule, 730 days -> period records are held 730 days, needed 30 days
at scale, period records held 2920 instead of 120 -> 24.33x too many

class mix (K01 V13 = items per invoice line) -> S2 crossing factor
  items 100 -> period records    120, period share 0.0012, factor 801.00
  items  20 -> period records    600, period share 0.0062, factor 161.00
  items   5 -> period records   2400, period share 0.0244, factor 41.00
  items   1 -> period records  12000, period share 0.1111, factor 9.00

Record and index counts are in the measurement class: they were read from the store. Rows converted to K01 scale are computed; scale and class mix are in the assumption class.

The Read Path Shows Nothing

The table’s first row is the most important part of the diagnosis. S1’s tracking read touches 8 records in both layouts; the ratio is 1.00. Monolithic persistence is invisible on this path. Someone who tries to build a diagnosis from the tracking query’s measurement alone concludes that the store being single has no cost, and is wrong. Both layouts read more than needed (8 records, 4 needed) — but that is the fourth lesson’s subject, not this one’s.

The second row gives the separation. The period scan touches 120 records in the separated store — exactly as many as needed. In the monolithic store, the shared day index sorts every class’s rows into the same order, so the scan crosses 96,120 records: 801 times its own class. This number does not come from the store being slow; it comes from the scan having to pass over records that do not belong to its own class.

The third measurement meets the symptom directly. In the monolithic store, a state event write consults three indexes; in the separated store, one. Of these three indexes, only one (ix_key) serves the event’s own query — the other two are there for the period class: the service ratio is 0.33. At K01’s peak write of 97.22 requests/s, this means 291.66 index maintenance operations per second; in the separated layout, 97.22. The difference — 194.44 — is work no query uses.

The number points to the second cause, not the first: the record has not grown at all; only the share that does not serve its own class has grown. This is the measurement that makes the separation.

The Condition Where a Single Store Is Correct

A single store is not wrong at every scale. The last table gives the boundary with a number: the crossing factor depends on the class mix. When period records are 0.0012 of the total, the factor is 801; at 0.0244, it is 41; at 0.1111, it drops to 9. So a single store is a defensible choice as long as no class’s share disappears next to the others’, and as the factor approaches one, splitting has nothing left to gain.

The second condition is on the index side. If all three indexes served the same class, the service ratio would be 1.00 and there would be no cost paid on the write side. The cost comes not from the index count, but from the count of indexes placed for another class.

The third condition is retention. K01’s V12 assumption keeps every record for 730 days, while the period record’s job ends once the thirty-day period closes. Because the single store carries one retention rule, period records are held 24.33 times longer than needed. If the classes’ retention periods were equal, this condition would drop out too.

When all three hold at once — shares close to each other, indexes shared, retention single — a single store is not just correct, it is also cheap.

What Grows in Exchange for Splitting

Splitting is not free, and its cost is on the write path. In a single store, a state event is one write: it is appended to the event log and the shipment’s latest state is updated in the same transaction. Once the classes are split, the same event is written to two stores; at K01’s peak write, this means 194.44 store writes per second instead of 97.22. What is heavier is not the count — it is that the two writes are no longer a single transaction. The consistency in between is handed off to the arrangement built in the Resilience and Reliability course’s Compensating Transactions and Idempotent Operations lessons — a write path of 97.22 requests per second loses its atomicity.

The second cost is operational surface: instead of one store, three stores get backed up, versioned, monitored, and put on call. This is the direct input to the load the next topic will count.

Summary

• The symptom is per-write work growing; it has two causes — the record has grown, or the store is shared with another class. The distinguishing measurement is the share of work that serves its own class.
• The tracking read touches 8 records in both layouts (ratio 1.00): monolithic persistence is invisible on the read path, and a diagnosis cannot be made from that path alone.
• The period scan passes over 120 records in the separated store and 96,120 in the monolithic store — 801 times its own class; the factor’s source is the class mix.
• A state event write consults 3 indexes in the monolithic store and only 1 of them serves its own query (0.33); at K01’s 97.22 requests/s write, 194.44 maintenance operations per second serve no query at all.
• A single store is correct when three conditions hold: class shares are close to each other (a factor of 9, not 801), indexes are shared (service ratio 1.00), and retention is single (period records are not held 24.33 times too long).
• What splitting costs is a longer write path: 194.44 store writes per second instead of 97.22, and a write path that loses its atomicity; the number of stores operated goes from 1 to 3.

Next Step

This lesson questioned the store’s structure and counted the share of a write that does not serve its own class. The next question is on the read side, and at first looks already answered: Scaling the Data Layer’s caching topic measured where caching is not needed — it showed that putting a cache in front of a scan with a repeat ratio of 1.00 costs 2.00 extra touches per request. The next lesson is that measurement’s mirror image: where caching is needed but was not put in place. But what gets measured is not the same thing. There, a repeated read was counted; here, a repeated computation is counted, and the two can diverge within the same stream: a stream with a low read repeat can have a very high computation repeat. The next lesson measures this distinction, counts the computation’s repeat ratio through the computation key, and shows what choosing an incomplete key costs.