Busy Database

The previous course defended twenty patterns with the arithmetic of two separate days, but all of them stood on the same assumption: the failure would be noticed. A circuit breaker would know a threshold had been crossed; a failover would know a replica had gone down. How the noticing itself would happen was never designed. This course starts there, and not from failure but from the ordinary day itself, the one without any failure at all: a system can degrade without ever breaking, and degradation cannot be fixed until it has a name.

This lesson, and the nine that follow it, run a three-step path. The symptom is a number; “slow” is not a symptom. Differentiation is a measurement that separates at least two causes producing the same symptom; without a measurement, what is done is a guess, and this course calls it exactly that. The fix removes the pattern and counts what grows in return. Until all three are complete, an antipattern has not been diagnosed. And no antipattern is wrong at every scale; each lesson bounds, with a number, the condition under which the same shape is correct.

Symptom: One Layer Busy, the Other Idle

The symptom shows up in the end-of-day pricing flow. K01’s rough sizing calculation wrote this job as scanning 12,000,000 records over thirty days within a four-hour window: 833.33 records per second. The job does not fit the window. Two utilization readings exist at the same time and they point in opposite directions: the store layer’s processor utilization peaks, while the application nodes’ utilization stays low. Utilization was defined in M19/K01 fundamental-properties/01 and is not redefined here.

The first reflex is a guess, and because it is a guess, it gets named as one: “the store is not enough, a bigger machine is needed.” The danger in that sentence is not that it is wrong — it is that it is directionless. The two causes below produce the same symptom and their fixes are opposites; half of every intervention made without picking a direction makes the symptom worse.

Cause A — non-reducing work in the store. The store is doing work that never changes the result’s record count: per-row string generation, formatting, decoding for presentation. Input records equal output records; the processor work spent does not shave a single byte off the data crossing the boundary.

Cause B — reducing work in the app. The application pulls raw records and does the aggregation itself. The store does no per-row computation at all, but it still has to read, serialize, and send twelve million records. The store is still busy — just with a different kind of work.

Busy database is the shared name for these two causes: computation done in the wrong layer. This is where the name alone turns out to be useless — it covers both causes, while the fix requires knowing which one applies.

Four Plans, One Rig

For differentiation, the same end-of-day job runs under four plans. There are two requests: the seller’s itemized statement (one row per shipment) and the invoice line (one row per seller-day, K01’s V13 = 100-item ratio). Each request is written with two layer choices.

The measurement runs on a real engine and the model scale is M19/K04’s scale: 20 sellers, 30 days, 100 shipments a day. Three things are counted — records crossing the boundary, row work done in each of the two layers (the number of computations run per row, counted with a counter function registered on the engine), and bytes crossing the boundary. Duration is not measured: duration depends on this particular machine, and it is not one of the three quantities being counted.

// layer/plan.mjs — the same end-of-day job in four plans: records crossing the boundary,
// boundary ratio, row work done in each layer, and bytes moved. node:sqlite runs a real engine.
import { DatabaseSync } from "node:sqlite";

const SELLER = 20, DAY = 30, DAILY = 100, TARIFF = 40, ZONE = 12;     // M19/K04 model scale
const N = SELLER * DAY * DAILY, INVOICE = SELLER * DAY;               // 60000 shipments, 600 invoice lines
const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE shipment(no TEXT PRIMARY KEY, seller INT, day INT, state INT,
  weight REAL, rate REAL, coefficient REAL, discount REAL);`);
db.exec("BEGIN");
const insert = db.prepare("INSERT INTO shipment VALUES(?,?,?,?,?,?,?,?)");
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;
  insert.run(`TR-${s}-${d}-${k}`, s, d, k % 5, 1 + (k % 30),
    12 + (ti % 7) * 0.5, 1 + (bi % 5) * 0.1, (s % 9) * 0.01);
}
db.exec("COMMIT");

const AMOUNT = "weight * rate * coefficient * (1 - discount)";
const STATE = "CASE state WHEN 0 THEN 'accepted' WHEN 1 THEN 'transferred' WHEN 2 THEN 'out-for-delivery' " +
  "WHEN 3 THEN 'delivered' ELSE 'pending' END";
const NAME = ["accepted", "transferred", "out-for-delivery", "delivered", "pending"];
const SQL = (i) => ({                                    // i: wrapper that counts row work
  D1: `SELECT no, ${i(`printf('%s / day %02d', no, day)`)} AS title,
    ${i(`printf('%.2f', ${i(AMOUNT)})`)} AS amount, ${i(`printf('%.1f kg', weight)`)} AS weight,
    ${i(STATE)} AS state FROM shipment`,
  D2: `SELECT no, day, ${i(AMOUNT)} AS amount, weight, state FROM shipment`,
  F1: `SELECT seller, day, count(*) AS items, sum(${i(AMOUNT)}) AS amount FROM shipment GROUP BY seller, day`,
  F2: `SELECT seller, day, weight, rate, coefficient, discount FROM shipment`,
});
const APP = {                                             // row work done in the app layer
  D1: (r) => 0,
  D2: (r) => { let c = 0; for (const x of r) { c += 4;
    void [`${x.no} / day ${String(x.day).padStart(2, "0")}`, x.amount.toFixed(2),
      `${x.weight.toFixed(1)} kg`, NAME[x.state]]; } return c; },
  F1: (r) => { let c = 0; for (const x of r) { c += 2; void [x.amount.toFixed(2), `${x.items} items`]; }
    return c; },
  F2: (r) => { const t = new Map(); let c = 0;
    for (const x of r) { c += 1; const k = `${x.seller}/${x.day}`;
      const v = t.get(k) ?? { items: 0, amount: 0 }; v.items += 1;
      v.amount += x.weight * x.rate * x.coefficient * (1 - x.discount); t.set(k, v); }
    for (const v of t.values()) { c += 2; void [v.amount.toFixed(2), `${v.items} items`]; } return c; },
};
const NEEDED = { D1: N, D2: N, F1: INVOICE, F2: INVOICE };   // record count in the request's result

let counter = 0;
db.function("i", (x) => { counter += 1; return x; });
const measure = {};
for (const [name, query] of Object.entries(SQL((e) => `i(${e})`))) {
  counter = 0;
  const rows = db.prepare(query).all();
  const store = counter;
  const bytes = Buffer.byteLength(JSON.stringify(rows.map((r) => Object.values(r))));
  measure[name] = { crossed: rows.length, needed: NEEDED[name], ratio: rows.length / NEEDED[name],
    store, app: APP[name](rows), bytes };
}

const PLAN = ["D1", "D2", "F1", "F2"];
const LABEL = { D1: "D1 statement, formatting in store", D2: "D2 statement, formatting in app",
  F1: "F1 invoice, aggregation in store", F2: "F2 invoice, aggregation in app" };
const say = (x) => x.toLocaleString("en-US");
console.log(`model: ${say(N)} shipments, ${SELLER} sellers, ${DAY} days; the statement request returns ` +
  `${say(N)} rows, the invoice request returns ${INVOICE} rows; the records scanned are ${say(N)} in all four plans`);
console.log(`\n${"plan".padEnd(35)}${"records crossed".padStart(17)}${"needed".padStart(10)}` +
  `${"boundary ratio".padStart(16)}${"store row work".padStart(16)}${"app".padStart(12)}` +
  `${"bytes crossed".padStart(15)}${"per record".padStart(13)}`);
for (const p of PLAN) {
  const o = measure[p];
  console.log(LABEL[p].padEnd(35) + say(o.crossed).padStart(17) + say(o.needed).padStart(10) +
    o.ratio.toFixed(2).padStart(16) + say(o.store).padStart(16) + say(o.app).padStart(12) +
    say(o.bytes).padStart(15) + (o.bytes / o.crossed).toFixed(1).padStart(13));
}

const SCALE = 12_000_000 / N, WINDOW = 4 * 3600;        // K01: 12,000,000 records over 30 days, 4 hours
console.log(`\nat K01 scale (scale ${SCALE}, window ${WINDOW} s, scan rate ` +
  `${(12e6 / WINDOW).toFixed(2)} records/s); boundary bandwidth is scaled so F2 equals K01's 6 Mbit/s`);
console.log(`${"plan".padEnd(35)}${"store row work/s".padStart(19)}${"app/s".padStart(14)}` +
  `${"boundary bandwidth Mbit/s".padStart(27)}`);
for (const p of PLAN) {
  const o = measure[p];
  console.log(LABEL[p].padEnd(35) + ((o.store * SCALE) / WINDOW).toFixed(2).padStart(19) +
    ((o.app * SCALE) / WINDOW).toFixed(2).padStart(14) +
    ((6 * o.bytes) / measure.F2.bytes).toFixed(3).padStart(27));
}

console.log(`\nplan-pair selection under KK1 (a unit of store row work costs k times an app unit)`);
console.log(`${"plan pair".padEnd(14)}${"k=2".padStart(12)}${"k=6".padStart(12)}${"k=12".padStart(12)}` +
  `${"records crossed".padStart(18)}${"boundary bandwidth Mbit/s".padStart(27)}`);
for (const [a, b] of [["D1", "F1"], ["D2", "F1"], ["D1", "F2"], ["D2", "F2"]]) {
  const store = measure[a].store + measure[b].store, app = measure[a].app + measure[b].app;
  const bytes = measure[a].bytes + measure[b].bytes;
  console.log(`${a} + ${b}`.padEnd(14) + [2, 6, 12].map((k) => say(store * k + app).padStart(12)).join("") +
    say(measure[a].crossed + measure[b].crossed).padStart(18) + ((6 * bytes) / measure.F2.bytes).toFixed(3).padStart(27));
}

model: 60,000 shipments, 20 sellers, 30 days; the statement request returns 60,000 rows, the invoice request returns 600 rows; the records scanned are 60,000 in all four plans

plan                                 records crossed    needed  boundary ratio  store row work         app  bytes crossed   per record
D1 statement, formatting in store             60,000    60,000            1.00         300,000           0      4,123,401         68.7
D2 statement, formatting in app               60,000    60,000            1.00          60,000     240,000      1,988,049         33.1
F1 invoice, aggregation in store                 600       600            1.00          60,000       1,200         13,407         22.3
F2 invoice, aggregation in app                60,000       600          100.00               0      61,200      1,302,519         21.7

at K01 scale (scale 200, window 14400 s, scan rate 833.33 records/s); boundary bandwidth is scaled so F2 equals K01's 6 Mbit/s
plan                                  store row work/s         app/s  boundary bandwidth Mbit/s
D1 statement, formatting in store              4166.67          0.00                     18.994
D2 statement, formatting in app                 833.33       3333.33                      9.158
F1 invoice, aggregation in store                833.33         16.67                      0.062
F2 invoice, aggregation in app                    0.00        850.00                      6.000

plan-pair selection under KK1 (a unit of store row work costs k times an app unit)
plan pair              k=2         k=6        k=12   records crossed  boundary bandwidth Mbit/s
D1 + F1            721,200   2,161,200   4,321,200            60,600                     19.056
D2 + F1            481,200     961,200   1,681,200            60,600                      9.220
D1 + F2            661,200   1,861,200   3,661,200           120,000                     24.994
D2 + F2            421,200     661,200   1,021,200           120,000                     15.158

The numbers in the table belong to the measurement class; the rows converted to K01 scale belong to the calculation class. The boundary bandwidth column carries no absolute claim about bytes: the model’s raw row is narrower than K01’s 900-byte shipment record, so the bandwidth was scaled by pinning plan F2 to the 6 Mbit/s that K01 wrote down. What travels across scales is the ratio between plans, not the absolute figure.

The Boundary Ratio Picks the Direction

The third column is the diagnosis itself. The boundary ratio is the number of records a request pushes across the boundary, divided by the number of records actually present in that request’s result. Three of the four plans show 1.00; F2 shows 100.00.

When the ratio is 1.00, the store has already reduced, and if it is still busy, the guilty party is non-reducing work. D1 shows this exactly: the boundary ratio is 1.00, but store row work is 300,000 — five per record. D2 produces the same result and the store’s work drops to 60,000, one per record. The difference is formatting work, and that work does not shave off even one crossed record — it actually increases it: bytes crossed drop from 4,123,401 to 1,988,049, from 68.7 bytes per record to 33.1. The strings the store produces are wider than the numbers they’re produced from. The direction of the fix is up.

When the ratio is far above 1, the reduction is happening in the wrong layer. In F2, store row work is zero — the store does no computation at all — but it still reads, serializes, and sends 60,000 records, even though the request’s result is 600 rows. F1 delivers the same result with 600 records and cuts boundary bandwidth from 6.000 to 0.062 Mbit/s: 96.8 times. The direction of the fix is down.

Both causes produce the “store busy, app idle” symptom, but one calls for moving work up and the other for moving it down. The boundary ratio separates these two cases with a single number, and what it separates is the direction of the fix.

One Number Does Not Diagnose

Finding the direction is not enough to pick a plan. The last table compares plan pairs under one assumption.

KK1 — a unit of row work done in the store costs $k$ times a unit of row work done in the app; the baseline value is $k = 6$. Rationale: the application layer is stateless and scales horizontally by adding nodes (M19/K01 fundamental-properties/03), while in the store layer writes are single-pointed and adding cores means changing machines. A unit of work in the two layers is not bought at the same price. Sensitivity is given in the $k = 2$ and $k = 12$ columns.

The weighted row-work measure recommends the same pair at all three $k$ values: D2 + F2, at 661,200 units (k = 6). This recommendation is wrong. That same row shows 120,000 records crossing the boundary and 15.158 Mbit/s of boundary bandwidth; the D2 + F1 pair shows 60,600 records and 9.220 Mbit/s. The row-work measure thinks F2 is cheap, because the work F2 loads onto the store is not row work at all — it is reading, serializing, and moving data, and none of that shows up in that column.

This is the course’s first rule: a symptom is not explained by a single number. The store is busy in two separate currencies — computation per row and bytes crossing the boundary — and if a plan is chosen by looking at only one, the other grows. The correct choice is D2 + F1: unnecessary work has been pulled out of the layer for both requests.

The Condition Where Staying in the Store Is Correct

Computation being done in the store is not forbidden; F1 does exactly that and is the cheapest of the four plans. The condition is measurable: work done in the store is justified to the extent that it reduces the record count crossing the boundary. In F1, 60,000 units of row work cut the crossed record count from 60,000 to 600 — a reduction of 100 units per record, which is exactly K01’s V13 = 100 ratio. In D1, 240,000 extra units of row work do not reduce the crossed record count at all; they push bytes up to 2.07 times.

The condition is bounded by a scale. If the number of items per invoice line dropped from 100 to 1 — that is, if every shipment produced its own invoice line — the 96.8-times bandwidth gap between F1 and F2 would vanish, and doing the aggregation in the store would have no payoff left. The same shape, in the same system, crosses to the wrong side at a different breakdown ratio.

What Grows in Return for the Fix

The move from D1 to D2 cut store row work from 300,000 to 60,000; app row work rose from 0 to 240,000. At K01 scale, that means dropping from 4166.67 units of row work per second in the store to 833.33, and rising from 0 to 3333.33 in the app. At KK1’s $k = 6$, the trade pays off; it would not at $k = 1$ — meaning the fix is justified by the layers’ scaling asymmetry, not by the work itself.

The second payoff is in the code, and it is not counted, but it is visible: once the formatting rule leaves the store, every consumer of the query has to rewrite it on its own side. A rule that lived in one place in the store now lives in as many places as there are consumers in the app. This cost does not make D2 wrong; failing to name the cost does.

Summary

• Diagnosis is three steps: the symptom is a number, differentiation is a measurement that separates two causes of the same symptom, and the fix counts what grows in return. What is done without a measurement is a guess.
• Busy database has two causes that produce the same symptom: non-reducing work in the store (D1) and reducing work in the app (F2). Their fixes are opposites.
• The boundary ratio picks the direction: at 1.00, work moves up (D1 → D2, bytes crossed 4,123,401 → 1,988,049); far above 1, work moves down (F2 → F1, bandwidth 6.000 → 0.062 Mbit/s, 96.8 times).
• A single number does not diagnose: the weighted row-work measure recommends D2 + F2 (661,200, k = 6), yet that pair crosses the boundary with 120,000 records and 15.158 Mbit/s; the correct choice is D2 + F1.
• Computation staying in the store is justified to the extent that it reduces the crossed record count: in F1, 60,000 units of row work cut the record count by a factor of 100. At a breakdown ratio of 1, the same choice would flip to wrong.
• The payoff of the fix grows in the app layer: row work goes from 0 to 240,000 (0 to 3333.33 per second at K01 scale), and the formatting rule spreads from one place to as many places as there are consumers.

Next Step

The boundary ratio settled which of two layers should do the work: store or app. But there is one more end of the chain, and it has never been measured. Plan D2 pulled formatting out of the store and put it in the app; the same work can go one step further and land on the client — the server sends the raw record, the browser formats it. This is the third layer of the same trade, and there the name of the measurement changes: work in the app layer is distributed by adding nodes; on the client, there is no node to add. The next lesson takes on that layer: how the load the server sends grows the work on the client, the measurement that tells apart a server-caused slowdown from a client-caused one, and what grows on the server in return for shrinking the load.