Materialization and Pipelining
Two query-execution strategies: materialization writes each operator's full output to disk before the next reads it, while pipelining streams tuples operator-to-operator without intermediate storage.
Why it exists
A query plan is a tree of operators such as scans, joins, filters, and sorts. The engine must decide how data moves between them. Materialization and pipelining are the two fundamental answers, trading off intermediate I/O against memory and the ability to stream results, and the right mix largely determines query latency.
The mental model
Think of an assembly line. Pipelining is a continuous belt: each station hands a single part to the next immediately, so the product flows through end to end. Materialization is batch processing: each station finishes its entire batch and sets it down in a holding area before the next station starts. Pipelining keeps everything in motion with little storage; materialization stages complete intermediate results.
How it works
Pipelining is commonly implemented with the iterator or Volcano model, where each operator exposes a next() call and pulls one tuple at a time from its child, so a tuple can pass through several operators without ever being written down. This minimizes I/O and lets the query emit early rows. Materialization computes an operator's full output and stores it, usually in a temporary table or spill file, which the parent then scans. It is required for blocking operators that cannot produce output until they have consumed all input, such as a full sort or the build side of a hash join. Real systems combine the two, defining pipeline breakers at those blocking points.
When it matters
Pipelining shines for queries where you want the first rows fast, like a LIMIT, or to avoid spilling large intermediates. Materialization matters when an operator is inherently blocking, when an intermediate result is reused multiple times, or when memory is too tight to keep a pipeline alive.
A concrete example
A query filters a table then sorts the result. The filter pipelines, passing qualifying rows straight into the sort. But the sort is a pipeline breaker: it must materialize all filtered rows before it can emit the first sorted one, so the plan pipelines up to the sort and materializes there.
Interview question
Why must a full sort operator materialize its input rather than purely pipeline it?
- a.Sorting uses less memory when results are written to disk first
- b.It is a blocking operator that cannot emit its first output until it has seen all inputCorrect
- c.Pipelining is incompatible with the iterator model
- d.Materialization is always faster than pipelining
Why? this is the answer
A sort cannot know the first output row until every input row is seen, making it a pipeline breaker that must materialize. Pipelining is in fact built on the iterator model, and materialization is not universally faster.
Just read this? Test yourself on what you have been reading.
- #query-execution
- #pipelining
- #materialization
- #databases
- #query-optimization
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles