Materialized View

An expensive join, recomputed on every single page load

A dashboard showing "total sales per region, joined with product category" might require aggregating across millions of order rows. Running that full aggregation query every time someone loads the dashboard is wasteful when the underlying data only changes a few times a minute.

Compute once, store the result, refresh on a schedule or on change

Instead of querying source tables directly at read time, a materialized view stores the precomputed result shape. It's refreshed periodically, or updated incrementally as source data changes — reads then hit the cheap, pre-shaped view instead of the expensive source query.

A view refreshed from source tables, read cheaply thereafter

`refreshSalesByRegion` does the expensive aggregation once; `getSalesByRegion` afterward is a cheap lookup, no matter how many times it's called.

const orders = [
  { region: "west", amount: 100 }, { region: "west", amount: 50 },
  { region: "east", amount: 75 },
];

let salesByRegionView = null; // the materialized view

function refreshSalesByRegion() {
  console.log("recomputing aggregation over all orders...");
  const totals = {};
  for (const order of orders) {
    totals[order.region] = (totals[order.region] ?? 0) + order.amount;
  }
  salesByRegionView = totals;
}

function getSalesByRegion() {
  return salesByRegionView; // cheap read, no recomputation
}

refreshSalesByRegion(); // run on a schedule, or triggered when orders change
console.log(getSalesByRegion());
console.log(getSalesByRegion()); // second call: no recomputation at all

Trade-offs

• Reads become cheap and fast — Expensive joins or aggregations are paid once at refresh time, not on every read.
• The view can be stale — Data is only as fresh as the last refresh — dashboards need to be honest about that lag, or refresh needs to be frequent enough that it doesn't matter.
• Pairs naturally with CQRS — A materialized view is often exactly what the "read model" in CQRS actually is.