Materialized Views

The copy dropped the number of records the read pulls but never touched the number scanned. The seller’s invoice line still reads and sums three thousand shipments one by one; at K01’s scale that is 833.33 records/s within a four-hour window. The sum is redone on every request, even though a closed day’s sum never changes again. The same arithmetic can be done once a day and stored.

A materialized view is a query’s result, computed and stored. The view built in the Views lesson of the SQL Fundamentals course holds no data — it stores the query and reruns it on every call; a materialized view holds the result, so reads are cheap while accuracy depends on time. This lesson does not teach query language; what it measures is the record count a query scans, the cost of refreshing, and how far the stored result can fall behind.

What the Grain Determines

The result to be stored requires a choice: at what level to aggregate. When the grain is coarse, row count drops, scanned records shrink, but the result answers fewer questions. Two grains are measured: seller-day-tariff and seller-day. The source is at shipment grain.

The model scale is again lesson 01’s scale. A seller has 100 shipments a day across eight distinct tariffs; the number eight is a model choice, not a real measurement.

// view/refresh.mjs — the same report read from the source and from two materialized-view
// grains. A real engine runs with node:sqlite; what is measured is records scanned,
// bytes stored, and the rows a refresh scans and writes.
import { DatabaseSync } from "node:sqlite";

const SELLER = 20, DAY = 30, DAILY = 100, TARIFF = 40, TARIFF_DAY = 8;   // lesson 01's scale
const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE shipment(id TEXT PRIMARY KEY, seller INTEGER, day INTEGER,
  tariff_id INTEGER, amount REAL);
CREATE INDEX shipment_seller ON shipment(seller, day);
CREATE INDEX shipment_day ON shipment(day);
CREATE TABLE summary_day_tariff(seller INTEGER, day INTEGER, tariff_id INTEGER,
  record INTEGER, amount REAL, PRIMARY KEY(seller, day, tariff_id));
CREATE TABLE summary_day(seller INTEGER, day INTEGER, record INTEGER, amount REAL,
  PRIMARY KEY(seller, day));`);
db.exec("BEGIN");
const g = 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)
    g.run(`TR-${s}-${d}-${k}`, s, d, (s * 7 + d * 3 + (k % TARIFF_DAY)) % TARIFF, 12 + (k % 30) * 0.5);
db.exec("COMMIT");

let touches = 0;
db.function("touch", (x) => { touches += 1; return x; });
const measure = (sql, ...a) => { touches = 0; const r = db.prepare(sql).all(...a); return [touches, r.length]; };

const REFRESH = {                        // incremental refresh: only the given day's records
  summary_day_tariff: `INSERT OR REPLACE INTO summary_day_tariff
    SELECT seller, day, tariff_id, count(*), sum(amount) FROM shipment
    WHERE day = ? GROUP BY seller, day, tariff_id`,
  summary_day: `INSERT OR REPLACE INTO summary_day
    SELECT seller, day, count(*), sum(amount) FROM shipment WHERE day = ? GROUP BY seller, day`,
};
db.exec("BEGIN");
for (let d = 0; d < DAY; d += 1) for (const s of Object.values(REFRESH)) db.prepare(s).run(d);
db.exec("COMMIT");

const REPORT = {
  shipment: ["shipment | grain: shipment",
    `SELECT tariff_id, sum(touch(amount)) FROM shipment
     WHERE seller = ? AND day BETWEEN 0 AND 29 GROUP BY tariff_id`],
  summary_day_tariff: ["summary | grain: seller-day-tariff",
    `SELECT tariff_id, sum(touch(amount)) FROM summary_day_tariff
     WHERE seller = ? AND day BETWEEN 0 AND 29 GROUP BY tariff_id`],
  summary_day: ["summary | grain: seller-day",
    `SELECT sum(touch(amount)) FROM summary_day WHERE seller = ? AND day BETWEEN 0 AND 29`],
};
const bytesOf = (t) => db.prepare("SELECT sum(pgsize) AS b FROM dbstat WHERE name = ?").get(t).b;
const rowCount = (t) => db.prepare(`SELECT count(*) AS n FROM ${t}`).get().n;

console.log(`model: ${SELLER * DAY * DAILY} shipments, ${SELLER} sellers, ${DAY} days, ` +
  `${TARIFF} tariffs, ${TARIFF_DAY} tariffs per seller-day`);
console.log(`\n${"table read".padEnd(34)}${"rows".padStart(8)}${"scanned".padStart(9)}` +
  `${"returned".padStart(9)}${"x".padStart(8)}${"bytes".padStart(10)}${"bytes/row".padStart(12)}`);
let base = 0;
for (const [table, [label, sql]] of Object.entries(REPORT)) {
  const [scanned, returned] = measure(sql, 10);
  if (base === 0) base = scanned;
  const n = rowCount(table), b = bytesOf(table);
  console.log(label.padEnd(34) + n.toLocaleString("en-US").padStart(8) + String(scanned).padStart(9) +
    String(returned).padStart(9) + (base / scanned).toFixed(2).padStart(8) +
    b.toLocaleString("en-US").padStart(10) + (b / n).toFixed(1).padStart(12));
}

console.log(`\n${"refresh".padEnd(18)}${"incremental scanned".padStart(20)}${"incremental written".padStart(20)}` +
  `${"full scanned".padStart(15)}${"full written".padStart(15)}`);
for (const [table, sql] of Object.entries(REFRESH)) {
  const written = db.prepare(sql).run(DAY - 1).changes;
  console.log(table.padEnd(18) + String(SELLER * DAILY).padStart(20) + String(written).padStart(20) +
    String(SELLER * DAY * DAILY).padStart(15) + String(rowCount(table)).padStart(15));
}

model: 60000 shipments, 20 sellers, 30 days, 40 tariffs, 8 tariffs per seller-day

table read                            rows  scanned returned       x     bytes   bytes/row
shipment | grain: shipment          60,000     3000       40    1.00 1,761,280        29.4
summary | grain: seller-day-tariff   4,800      240       40   12.50    94,208        19.6
summary | grain: seller-day            600       30        1  100.00    16,384        27.3

refresh            incremental scanned incremental written   full scanned   full written
summary_day_tariff                2000                 160          60000           4800
summary_day                       2000                  20          60000            600

These numbers belong to the measurement class; the bytes column depends on page fill factor and looks large when row count is small because bytes are spread over few rows.

The three rows give a gradient. Reading the report from the source scans 3000 records; at the seller-day-tariff grain, 240; at the seller-day grain, 30. As the grain coarsens, scanned records drop by 12.5 and 100 times.

The fourth column states the cost. The seller-day-tariff grain preserves the report’s breakdown: the same 40 rows return as the source. At the seller-day grain, returned rows drop to 1 — that summary does not carry the tariff breakdown, so the question “how much from which tariff” cannot be asked of it. Grain selection is not a performance tweak; it is a design decision that narrows the set of answerable questions.

The table below separates two forms of refresh. Incremental refresh scans only the changed day’s records: it reads 2000 records, writes 160 rows for seller-day-tariff, 20 for seller-day. Full recompute scans all 60,000 records every time and rewrites the entire summary. The ratio between them is thirty times on both the scanned and written sides, and it grows with retention length, because incremental refresh’s scanned count does not depend on the number of days.

Back to the Numbers

The measured grain ratios and bytes per row are converted into K01’s rows. The scaling is direct: the seller-day grain’s period row count is 4,000 sellers times 30 days, the same 120,000 rows K01 already computed as period seller-days.

// view/window.mjs — the effect of the measured grain ratios on K01's rows and the trade-off
// between the refresh interval's staleness window and write load. All of it is arithmetic.
const V3 = 400_000, V10 = 4, V12 = 730, PERIOD = 30;          // K01 assumptions
const SELLER = 4000, TARIFF_DAY = 8;                          // K01: daily invoice lines; model measurement
const SCAN = 833.33, READ_GB = 10.80, STORED = 712.48;        // K01 computed values
const DAY_SECONDS = 86_400, WINDOW = V10 * 3600;
const READ = (V3 * PERIOD * 0.1) / DAY_SECONDS, WRITE = (V3 * 7) / DAY_SECONDS;   // K01 averages
const BYTES = { "seller-day-tariff": 19.6, "seller-day": 27.3 };   // view/refresh.mjs measurement

const period = { shipment: PERIOD * V3, "seller-day-tariff": SELLER * PERIOD * TARIFF_DAY,
  "seller-day": SELLER * PERIOD };
console.log(`${"table read".padEnd(20)}${"period rows".padStart(14)}${"scan records/s".padStart(16)}` +
  `${"x".padStart(8)}${"read MB".padStart(12)}${"storage GB".padStart(12)}${"x".padStart(8)}`);
for (const [label, n] of Object.entries(period)) {
  const bytes = BYTES[label] ?? (READ_GB * 1e9) / period.shipment;
  const stored = label === "shipment" ? STORED : STORED + (n / PERIOD) * V12 * bytes / 1e9;
  console.log(label.padEnd(20) + n.toLocaleString("en-US").padStart(14) +
    (n / WINDOW).toFixed(2).padStart(16) + (period.shipment / n).toFixed(2).padStart(8) +
    ((n * bytes) / 1e6).toFixed(2).padStart(12) + stored.toFixed(2).padStart(12) +
    (stored / STORED).toFixed(4).padStart(8));
}
console.log(`K01 baseline: batch job scan ${SCAN} records/s, read ${READ_GB} GB, ` +
  `stored ${STORED} GB`);

console.log(`\nrefresh interval against the staleness window and write load (incremental refresh)`);
console.log(`${"interval".padEnd(12)}${"staleness".padStart(11)}` +
  `${"seller-day writes/s".padStart(21)}${"ratio".padStart(7)}` +
  `${"seller-day-tariff writes/s".padStart(28)}${"ratio".padStart(7)}`);
for (const [label, T] of [["once a day", DAY_SECONDS], ["once an hour", 3600], ["5 minutes", 300], ["1 minute", 60]]) {
  const cell = [SELLER, SELLER * TARIFF_DAY].map((rows) => {
    const y = rows / T;
    return [y.toFixed(2), ((WRITE + y) / READ).toFixed(2)];
  });
  const duration = T >= 3600 ? `${T / 3600} h` : `${T / 60} min`;
  console.log(label.padEnd(12) + duration.padStart(11) + cell[0][0].padStart(21) +
    cell[0][1].padStart(7) + cell[1][0].padStart(28) + cell[1][1].padStart(7));
}
console.log(`K01 baseline: average write ${WRITE.toFixed(2)}/s, read ${READ.toFixed(2)}/s, ratio ` +
  `${(WRITE / READ).toFixed(2)}`);
for (const T of [DAY_SECONDS, 300]) {
  const full = (DAY_SECONDS / T) * period.shipment;
  console.log(`  ${T === DAY_SECONDS ? "once a day" : "5 minutes"} refresh, daily scanned records: ` +
    `incremental ${V3.toLocaleString("en-US")}, full recompute ${full.toLocaleString("en-US")}`);
}

table read             period rows  scan records/s       x     read MB  storage GB       x
shipment                12,000,000          833.33    1.00    10800.00      712.48  1.0000
seller-day-tariff          960,000           66.67   12.50       18.82      712.94  1.0006
seller-day                 120,000            8.33  100.00        3.28      712.56  1.0001
K01 baseline: batch job scan 833.33 records/s, read 10.8 GB, stored 712.48 GB

refresh interval against the staleness window and write load (incremental refresh)
interval      staleness  seller-day writes/s  ratio  seller-day-tariff writes/s  ratio
once a day         24 h                 0.05   2.34                        0.37   2.36
once an hour        1 h                 1.11   2.41                        8.89   2.97
5 minutes         5 min                13.33   3.29                      106.67  10.01
1 minute          1 min                66.67   7.13                      533.33  40.73
K01 baseline: average write 32.41/s, read 13.89/s, ratio 2.33
  once a day refresh, daily scanned records: incremental 400,000, full recompute 12,000,000
  5 minutes refresh, daily scanned records: incremental 400,000, full recompute 3,456,000,000

The upper table breaks down K01’s largest read item. The end-of-day job scans 833.33 records/s and reads 10,800 MB when reading from the source; reading from the seller-day summary, it scans 8.33 records/s and reads 3.28 MB. The bandwidth side reverses K01’s most striking finding: there, the end-of-day job demanded 3.75 times the peak read egress in bandwidth; here that item drops to three-thousandths of it.

The storage column shows a cost close to nothing. The seller-day summary, kept for seven hundred thirty days, raises stored data from 712.48 GB to 712.56 GB; the seller-day-tariff summary, to 712.94 GB. Both are under one part in a thousand. The cost of a precomputed result is not on disk.

The Staleness Window

The table below says where the cost is. The stored result falls behind until it is refreshed, and this lag is called the staleness window; under incremental refresh, the window is the refresh interval itself.

As the interval shortens, the window shrinks and write load grows. At the seller-day grain, refreshing once a day raises write/read ratio at store from 2.33 to 2.34 — a cost too small to measure. Refreshing every five minutes gives a ratio of 3.29; every minute, 7.13. The same curve is steeper at a finer grain: the seller-day-tariff summary’s ratio is 10.01 refreshed every five minutes, 40.73 refreshed every minute. A fine grain gains little on reads and makes refreshing far more expensive.

The decision follows from one of K01’s assumptions. V10 sets the end-of-day job’s window at four hours: the seller’s report is requested in the morning. A twenty-four-hour staleness window meets that requirement, since the report already covers closed days. For pricing, the right interval is once a day, at the cost of the ratio moving from 2.33 to 2.34. If the same summary is wanted live on a seller dashboard instead, the window drops to minutes and the cost is the ratio climbing three to seven times; that is a second, separate use of the summary, and one interval cannot serve both.

The last two lines mark the limit of the refresh style. Incremental refresh scans 400,000 records a day regardless of interval — each shipment once. Full recompute scans 12,000,000 running once a day, 3,456,000,000 running every five minutes. Shortening the interval is only an option if the refresh is incremental.

Summary

• A materialized view stores the result, not the query: a stored query reruns on every call, a stored result lags until refreshed.
• Grain determines the answerable questions: the seller-day-tariff summary dropped scanned records from 3000 to 240 while preserving the breakdown; the seller-day summary dropped it to 30 but can no longer answer the tariff question.
• Back to K01: the end-of-day job’s scan drops from 833.33 records/s and 10,800 MB to 8.33 records/s and 3.28 MB — a hundred times.
• The storage cost is negligible: the 730-day summary raises stored data from 712.48 GB to 712.56 GB.
• The cost is in the refresh: write/read ratio at store climbs from 2.33 to 2.34 refreshing once a day, to 3.29 every five minutes, to 7.13 every minute; the same intervals give 2.36, 10.01, and 40.73 at the finer grain.
• Shortening the interval is only an option under incremental refresh: incremental refresh scans 400,000 records a day, while full recompute scans 3,456,000,000 records at a five-minute interval.

Next Step

Every decision in this topic has kept one assumption fixed: the data sits in a table with rows and columns. The three access patterns measured were all served by that same store shape — a single-record read by tracking number, the state event write, and the periodic scan. Yet these three patterns want very different things: one wants a single record by a single key, one wants continuous appends, one wants a sum over a wide range. The next lesson measures the same three patterns across four store types: in a key-value, document, wide-column, and graph store, what record count is touched and what query count is required for each pattern, and which pattern the structure that cheapens one makes more expensive.