Precompute vs. compute-on-read
Where you meet it: materialized views, ELT vs. ETL, schema-on-read.
What it is
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.
From Cost Mechanics › Materialization
The difference between a materialized view (MV) and a cache is not the mechanism. It is who decided what gets stored. A cache discovers by watching reads. An MV is told at design time. Every other difference follows from this, which makes MVs the clean end of a continuum whose other end is an LRU.
From Caching › Materialized views
Where it appears
In 14 mechanisms. Each says what the mechanism is about, then how this trade-off shows up there, in its own words.
- Storage: Physical Organization
What should sit next to what, given that I must decide at write time and find out at read time?
This property makes MoR both powerful and dangerous: MoR converts a bounded, known-count cost into an unbounded, unknown-count one.
Read it inDeletes, versions and reclaiming spaceMutation strategies: what happens when a value changesPartitions, buckets, row groups and zone maps
- Persistence & Durability
When a system says "yes, your data is safe", what exactly has it promised, and what did it have to do to keep that promise?
As a rule: every incremental scheme has a chain length that trades capture cost against reconstruction cost; periodic re-basing is the only cure, and its interval is the real knob.
- Core Data Structures
How do we choose a data structure when the real cost is set by the hardware, not by Big-O?
An interval is not a point, so pruning is a stabbing query, and pruning power is a function of interval overlap, which is why physical clustering is an indexing decision.
Read it inZone maps and min/max indexesInterval trees and stabbing queries
- Encoding, Serialization & Compression
What do we turn our values into when we turn them into bytes, and what does that choice cost?
This gives a pattern that keeps being reinvented: write hot data at level 1–3, and rewrite it at level 9–19 during compaction.
Read it inBlock size, level and trained dictionariesRow order dominates the codec
- Indexing
When does a separate "where is it?" structure pay for itself, and what does it cost to keep it true?
That is the real identity of this mechanism: a covering index is a materialized projection with a sort order attached.
Read it inCovering indexes: the index becomes a narrow copy of the tableDensity: sparse indexes and zone mapsTime: the nearly redundant index
- Query Processing
Why can one question be answered by plans whose costs differ by a factor of ten thousand, and what does each operator force the engine to do?
This is a write-time investment being cashed in.
Read it inBroadcast, shuffle and runtime filtersA sort produces a property, not an answerFrom what to how: why a query has many plans
- Query Planning & Optimization
How does a system choose a plan for a question when the facts that decide the best plan are unknown until the plan has run?
They are useless, and the failure is total, not gradual.
Read it inWhy min/max skipping works or fails completelyCaching and memoization of subplansCommon subexpression elimination
- Memory & Resource Management
Who is allowed to hold how much memory, for how long, and what happens to the work when the answer is less than it asked for?
The structurally important fact is this: B and P are substitutes, and c·g is not a substitute for anything.
Read it inThe partition of RAM
- Caching
What happens when we deliberately keep a second copy of data that no protocol forces to be correct?
This leads to a result that is surprising and correct: a well-sized cache should prefetch less aggressively than a badly sized one.
Read it inWarming and prefetch are two mechanisms, not oneA derived entry has a dependency set
- Fault Tolerance & Delivery Semantics
What is the cheapest thing that can be safely done twice?
Materialized shuffle files are a checkpoint placed at exactly the point where recomputation would otherwise become total.
Read it inCheckpoints and lineage: save the state or the recipeCopy, log or recipe: the nine ways to recover
- Cost Mechanics
What is metered, and what is the smallest increment you are charged for?
The break-even lives in the ratio of read rate to write rate.
Read it inMaterialization break-evenStorage: an integral over time
- Change Data Capture & Incremental Processing
What does it cost to know what changed, and is that cheaper than not needing to know?
The change is small, but the operands it joins against are not.
Read it inIncremental view maintenanceThe arithmetic that governs everythingA derivative from a system that did not plan to be read
- Data Modeling
What does one row mean, who is allowed to change that answer, and what breaks downstream when they do?
Precompute versus compute on read (do the work at write time or at query time).
Read it inA layer with no oracleOne Big TableFact table typesMedallion layering
- Specialized Processing Models
What do you build when a query predicate cannot be reduced to a range over an ordered key?
Every index is directional; the reverse question costs a second copy.
Read it inPhrase queries and faceting
Related
Made on the same pages most often.