Data Store Selection

The Application Layer and Service Interaction course made services stateless, let them discover each other, queued the work, and coordinated the workflow — all of it resting on one assumption: the data sat somewhere, and that somewhere was never questioned. The evidence is in the arithmetic: K01’s requests reaching store/s (138.89) and stored data GB (712.48) rows never moved through that entire course.

This course takes up those rows, and the first question is the store’s type. What the relational model gives, what the non-relational families give up, and where schema flexibility actually moves to were established in the Data Modeling and Relational Theory course; data modeling is not taught here. The axis here is different: access pattern and scale. The same data, read through different questions, calls for a different store layout, and this lesson measures that link.

Three Access Patterns

A store decision cannot be defended by looking at the data alone — the defense rests on the shape of the question. The shipment tracking and pricing service carries three patterns, and all three rates come from K01’s arithmetic.

P1 — single lookup by tracking number. The key is known in advance, the response is small (V5: 480 bytes), and behind the edge cache it comes down to 41.67 requests per second. The query is known ahead of time and does not change.

P2 — state event write by carrier. Carrier systems produce seven events per shipment (V4); the peak rate is 97.22 requests/s. Every event is a new record — it never overwrites an existing one.

P3 — periodic batch scan by seller. The pricing job scans thirty days (V11) and reads 833.33 records per second inside a four-hour window. As a request count it is close to zero; as a record count it is the largest of the three.

The first thing the three patterns say together: request rate and record rate are separate measures. P3 touches the store once a day yet reads more records than P1 does across its entire day, which makes sizing a store by request rate alone an incomplete calculation.

The Trace Patterns Leave in the Store

The measurement uses a real engine. Two layouts are set up in memory with node:sqlite: in the single-store layout, the shipment and the event live in separate tables; in the pattern-split layout, P1 also gets a document table holding the shipment’s state and its last three route steps in a single row — the response V5 describes. What is measured is not the query language but the number of tables the engine touches and the number of records it processes, counted with a function the engine calls on every row.

// store/pattern.mjs — the tables touched and records processed by three access patterns
// under two layouts. A real engine runs with node:sqlite; the query language itself is
// not what is measured — that was set up in M17 and is not covered here.
import { DatabaseSync } from "node:sqlite";

const SELLER = 20, DAY = 30, DAILY = 100, EVENTS = 7;   // model scale: 3000 shipments per seller
const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE shipment(trackingNo TEXT PRIMARY KEY, seller INTEGER, day INTEGER);
CREATE INDEX shipment_seller ON shipment(seller, day);
CREATE TABLE event(trackingNo TEXT, seq INTEGER, state TEXT);
CREATE INDEX event_trackingNo ON event(trackingNo);
CREATE TABLE document(trackingNo TEXT PRIMARY KEY, body TEXT);`);

let counter = 0;
db.function("count", { deterministic: false }, (x) => { counter += 1; return x; });
const steps = (s) => db.prepare(`EXPLAIN QUERY PLAN ${s}`).all()
  .flatMap((r) => r.detail.match(/(?:SCAN|SEARCH) \w+/g) ?? []);

const s = db.prepare("INSERT INTO shipment VALUES(?,?,?)"), e = db.prepare("INSERT INTO event VALUES(?,?,?)");
const d = db.prepare("INSERT INTO document VALUES(?,?)");
db.exec("BEGIN");
for (let v = 0; v < SELLER; v += 1) for (let day = 0; day < DAY; day += 1) for (let k = 0; k < DAILY; k += 1) {
  const trackingNo = `TR-${v}-${day}-${k}`;
  s.run(trackingNo, v, day);
  for (let i = 0; i < EVENTS; i += 1) e.run(trackingNo, i, `state${i}`);
  d.run(trackingNo, JSON.stringify({ state: `state${EVENTS - 1}`, steps: [EVENTS - 3, EVENTS - 2, EVENTS - 1] }));
}
db.exec("COMMIT");
console.log(`model: ${SELLER * DAY * DAILY} shipments, ${SELLER * DAY * DAILY * EVENTS} events, ` +
  `${SELLER} sellers, ${DAY} days (${DAY * DAILY} shipments per seller)`);

const READS = [
  ["P1 single lookup by tracking no", "single store", "TR-10-15-50",
    "SELECT count(s.trackingNo), e.state FROM shipment s JOIN event e ON e.trackingNo = s.trackingNo WHERE s.trackingNo = ? ORDER BY e.seq DESC LIMIT 3"],
  ["P1 single lookup by tracking no", "split", "TR-10-15-50", "SELECT count(body) FROM document WHERE trackingNo = ?"],
  ["P3 period scan by seller", "single store", 10,
    "SELECT count(e.trackingNo) FROM event e JOIN shipment s ON s.trackingNo = e.trackingNo WHERE s.seller = ?"],
  ["P3 period scan by seller", "split", 10,
    "SELECT count(trackingNo) FROM shipment WHERE seller = ? AND day BETWEEN 0 AND 29"],
];
console.log(`\n${"pattern".padEnd(33)}${"layout".padEnd(13)}${"table".padStart(6)}` +
  `${"processed".padStart(11)}${"returned".padStart(10)}  engine steps`);
for (const [name, layout, arg, sql] of READS) {
  counter = 0;
  const returned = db.prepare(sql).all(arg).length, st = steps(sql);
  console.log(`${name.padEnd(33)}${layout.padEnd(13)}${String(new Set(st).size).padStart(6)}` +
    `${String(counter).padStart(11)}${String(returned).padStart(10)}  ${st.join(" + ")}`);
}

// P2: length of the write path — how many tables a state event touches
const write = (layout) => {
  db.prepare("INSERT INTO event VALUES(?,?,?)").run("TR-0-0-0", EVENTS, "stateNew");
  if (layout === "single store") return 1;
  db.prepare("UPDATE document SET body = ? WHERE trackingNo = ?")
    .run(JSON.stringify({ state: "stateNew", steps: [EVENTS - 2, EVENTS - 1, EVENTS] }), "TR-0-0-0");
  return 2;
};
console.log(`\nP2 write of a state event arriving from the carrier: single store ${write("single store")} table, ` +
  `split ${write("split")} tables`);

model: 60000 shipments, 420000 events, 20 sellers, 30 days (3000 shipments per seller)

pattern                          layout        table  processed  returned  engine steps
P1 single lookup by tracking no  single store      2          7         3  SEARCH s + SEARCH e
P1 single lookup by tracking no  split             1          1         1  SEARCH document
P3 period scan by seller         single store      2      21000     21000  SEARCH s + SEARCH e
P3 period scan by seller         split             1       3000      3000  SEARCH shipment

P2 write of a state event arriving from the carrier: single store 1 table, split 2 tables

These numbers belong to the measurement class: they come from an engine running on this machine and are deterministic, but the plan the engine picks is its own decision, and it depends on the engine’s version. The model’s scale is also a choice — 3,000 shipments per seller is what thirty days works out to at K01’s rate of 100 shipments/day.

Three rows deserve a close read. P1’s work differs sevenfold between the two layouts. The single store touches two tables, processes seven event records, and returns three; the split layout touches one table and processes one. The sevenfold ratio is not an indexing effect — both layouts index the same way. The difference is whether the data already sits in the shape it is read in.

P3’s ratio is the same sevenfold, but the case runs the opposite way. The pricing job wants the shipment’s commercial fields, not event detail; in the single store the join drags it into 21,000 records, while the split layout holds it at 3,000. The same split that cheapened P1 does this.

P2 pays the cost. A state event writes to one table in the single store; in the split layout it writes to the event table and updates the document, stretching the write path to two tables. Shortening the read path is not free — the write path collects the bill.

Decision Axis

The measurement reduces the choice to four questions. The questions concern pattern and scale, not the data model.

Is the key fixed? P1 always arrives with the same key and wants one record — that is the entirety of a key–value store’s job: no declarative query, no join, no schema constraint.

Is the record read as a single unit? P1’s fields are read together and updated together, a boundary that matches a document store‘s natural unit. P3’s fields do not sit inside that boundary.

Is the query known in advance? Pricing filters on a variable mix of period, seller, tariff, and contract, and its questions grow over time. An unknown-in-advance question calls for a declarative query, which is why the relational model wins here and stays the system of record.

Does the scale exceed a single engine? Writes run at 97.22 requests/s, daily growth is 976 MB, stored data is 712.48 GB — all three still fit on a single engine, which is why this lesson’s decision splits the read path by pattern, not the data itself. Splitting the data comes up once scale grows, and this topic’s later lessons take that up.

The four answers may not point to a single store. The non-relational families — key–value, document, wide-column, and graph stores — do not replace the system of record; they are secondary stores derived from it, and this topic’s last lesson takes up their use cases on their own. The wide-column family appeared in the Data Modeling and Relational Theory course as the “column-family store”; the two are the same family, and that course’s rule — the system of record stays singular — is not changed here.

Back to the Arithmetic

The split’s cost shows up in K01’s rows. The secondary store is only needed for shipments that are still in transit; the tracking page of a delivered shipment is opened rarely.

VD1 — the tracking document is retained for 30 days. Rationale: once a shipment is delivered, tracking queries against it grow rare, while the record itself is kept for 730 days for contract disputes (V12). This assumption is not added to K01’s table; its sensitivity is given below at 90 days.

// store/cost.mjs — the impact of the pattern-split layout on K01's calculated rows
const READ = 41.67, WRITE = 97.22;         // K01: read/s behind the cache, peak write requests/s
const STORE = 138.89, RATIO = 2.33;        // K01: requests/s reaching the store, store write/read ratio
const SCAN = 833.33;                       // K01: batch job scan records/s
const GROWTH = 976, STORED = 712.48;       // K01: daily data growth MB, stored data GB
const V3 = 400_000, V5 = 480;              // K01: daily shipments, tracking response body (bytes)
const VD1 = 30;                            // this lesson's assumption: tracking document retention (days)

// Measured mechanism: P1 is 7 records / 2 tables in single store, 1 record / 1 table split;
// P2 writes to 1 table in single store, 2 tables split; P3 is 21000 / 3000 records.
const D = [["single store", 7, 1, 21000], ["split by pattern", 1, 2, 3000]];
const b = (x) => x.toFixed(2);

console.log(`${"layout".padEnd(24)}${"read records/s".padStart(18)}${"store ops/s".padStart(14)}` +
  `${"write/read".padStart(13)}${"scan records/s".padStart(16)}`);
for (const [name, records, target, scan] of D) {
  const readRecords = READ * records, ops = READ + WRITE * target;
  console.log(`${name.padEnd(24)}${b(readRecords).padStart(18)}${b(ops).padStart(14)}` +
    `${b((WRITE * target) / READ).padStart(13)}${b(SCAN * (scan / 3000)).padStart(16)}`);
}
console.log(`K01 baseline: requests/s reaching the store ${STORE}, write/read ratio ${RATIO}, ` +
  `batch scan ${SCAN} records/s`);

const docDaily = (V3 * V5) / 1e6;                    // MB/day
const docStored = (V3 * V5 * VD1) / 1e9;              // GB
console.log(`\n${"growth line".padEnd(26)}${"K01".padStart(10)}${"split".padStart(11)}${"factor".padStart(8)}`);
console.log(`${"daily written MB".padEnd(26)}${b(GROWTH).padStart(10)}${b(GROWTH + docDaily).padStart(11)}` +
  `${b((GROWTH + docDaily) / GROWTH).padStart(8)}`);
console.log(`${"stored data GB".padEnd(26)}${b(STORED).padStart(10)}${b(STORED + docStored).padStart(11)}` +
  `${b((STORED + docStored) / STORED).padStart(8)}`);
console.log(`tracking document store is ${b(docStored)} GB = ` +
  `1/${(STORED / docStored).toFixed(0)} of stored data; if VD1 were 90 days, ${b((V3 * V5 * 90) / 1e9)} GB`);

layout                      read records/s   store ops/s   write/read  scan records/s
single store                        291.69        138.89         2.33         5833.31
split by pattern                     41.67        236.11         4.67          833.33
K01 baseline: requests/s reaching the store 138.89, write/read ratio 2.33, batch scan 833.33 records/s

growth line                      K01      split  factor
daily written MB              976.00    1168.00    1.20
stored data GB                712.48     718.24    1.01
tracking document store is 5.76 GB = 1/124 of stored data; if VD1 were 90 days, 17.28 GB

These rows belong to the calculation class; their inputs are K01’s arithmetic, the measured record rates, and VD1.

The write/read ratio at store climbs from 2.33 to 4.67, because every state event now writes to two places while the read side, shortened, does not grow. The store operation rate that replaces requests reaching store/s climbs from 138.89 to 236.11 — a 1.70× factor. In exchange, the read side’s processed record rate falls from 291.69 to 41.67, and the batch scan’s falls from 5,833.31 to 833.33: the decision does not erase load, it moves it from the read path to the write path.

The two growth lines split apart too. Daily written bytes rise from 976 to 1,168 MB (1.20×), but stored data moves only from 712.48 to 718.24 GB (1.01×), because the document drops off after thirty days. The payoff is striking: the store serving all of P1’s read load is 1/124 of stored data. If VD1 tripled, it would be 17.28 GB and the ratio would still sit near one part in forty. The store type decision, for that reason, looks not at data volume but at what share of it gets read.

Summary

• A store decision is defended by access pattern: P1 splits out as 41.67 requests/s of single lookup, P2 as 97.22 requests/s of inserts, and P3 as 833.33 records/s of scanning, and request rate and record rate are separate measures.
• The same read touches 2 tables and processes 7 records in the single store, but 1 table and 1 record in the pattern-split layout; the batch scan falls from 21,000 records to 3,000.
• The cost sits on the write path: a state event now writes to two tables instead of one.
• The choice is delivered by four questions — is the key fixed, is the record read as a single unit, is the query known in advance, does the scale exceed a single engine; the relational model stays the record’s system of record because of questions not known in advance.
• Back to K01: the store operation rate climbs from 138.89 to 236.11 operations/s (a 1.70× factor) and the write/read ratio climbs from 2.33 to 4.67; the read side’s processed records fall from 291.69 to 41.67.
• With VD1 = 30 days, the tracking document store is 5.76 GB, or 1/124 of stored data; daily written bytes climb from 976 to 1,168 MB while stored data climbs from 712.48 to 718.24 GB.

Next Step

The store type decision is made and the read path is shorter, but there is still a single copy on the table. That copy takes 236.11 operations per second, carries 718.24 GB, and when it goes down, neither the tracking query nor the pricing job gets an answer. The first way to spread read load across more than one machine is not to split the data but to replicate it — keep the same data in more than one place. The next lesson takes up that decision: how many copies the read splits across in a leader–follower layout, why the write does not split, what write/read ratio each copy ends up seeing, how much the stored data multiplies, and what number accepting writes in more than one place gives rise to.