Materialization break-even
When does a materialized view pay for itself, and what flips the answer?
A materialized view is a saved query result that you refresh when the base data changes. Is it worth building? Costs are the build cost B, the refresh cost R per change, and storage S over the horizon T. Savings are the scan reduction per query times the number of queries. The break-even count is
- B + R × changes + S × T
- — paid once: a sort, a compilation, a plan
- savings per query
- — what each later query, row or execution saves
Three results fall out.
- The break-even lives in the ratio of read rate to write rate. This is the same ratio that decides between a B-tree (which favors reads) and an LSM tree (which favors writes). "Precompute versus compute on read" and "read-optimized versus write-optimized" are the same variable. Here it is expressed in money.
- On a high-churn table the refresh term grows with the change rate, so the materialization may never break even at any query volume.
- Incremental refresh changes R from O(table size) to O(delta, the changed part). If the table is 10 TB and the daily change is 100 GB, R falls by about 100×, which moves the break-even by orders of magnitude. This is the economic reason incremental processing exists, independent of its correctness arguments.
The unpriced term
Every materialization is a cache, and every cache has an invalidation obligation: you must know when its inputs changed. So the true cost includes a correctness liability that appears on no bill and is paid by whoever debugs the difference between the view and the base table. A cost is conserved: it moved onto a different party.
The meter again
The break-even depends on the meter, not just on the workload. Under occupancy pricing, on a cluster you already own with idle capacity you already bought, a marginal materialization is nearly free. Under scan pricing it is billed at the full rate. Identical design, opposite verdict.