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.
ONE 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.
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.