Iterator pipelines and materialization

In an open-next-close style interface, initialization prepares operator state, next requests another result, and close releases resources. A filter may call its child repeatedly until it finds a qualifying row or reaches end-of-input. A projection can transform that row and pass it upward without assembling a complete intermediate table. This composition is a central idea in the Volcano paper and remains useful for understanding iterator engines. It describes an interface and control flow, not a promise that each next call always performs the same amount of work.

A parent asking for one row might trigger a chain of child requests, including many rejected candidates. Conversely, a LIMIT can stop a compatible streaming pipeline before every source row is visited. Cancellation, end-of-input, and exceptions must release scans, buffers, and temporary state correctly. An execution object also needs well-defined ownership of returned storage: a pointer or batch may remain valid only until a later call. Consuming a row after its producer reused the buffer is a correctness bug regardless of how elegant the pipeline appears.

A full sort generally must inspect its input before it can guarantee the globally first result. A hash join must build suitable state from one side before normal probing yields results. A hash aggregate may need input exhaustion before final group values are known, while an aggregate over correctly grouped ordered input can emit completed groups earlier. These distinctions locate pipeline boundaries. The word aggregate or join alone is not enough: inspect the physical method, required order, and whether partial or final results are semantically permitted.

Materialization stores an intermediate result so it can be revisited without rerunning its producer. It can help a repeated inner input, decouple a consumer, or support rewind behavior. Storage may be bounded in memory and spill when necessary. That stored result has a lifetime and a scope: a PostgreSQL Materialize node is not automatically a persistent result cache shared across unrelated queries. Nor is every SQL common-table expression necessarily materialized; documented folding rules, reference patterns, volatility, and explicit directives influence the actual plan.

Compare the complete costs over the required lifetime. Retaining an expensive small producer result can save work, but materializing a large one-use stream can add copies and delay output. A parameterized subplan may produce different answers for different outer keys, so one stored result cannot be reused indiscriminately. Snapshot and visibility rules still apply. A trustworthy explanation names what is reused, by whom, how often, and which memory or I/O budget retains it. Draw the lifetime overlap when several materialized results coexist.

The cache lab illustrates a small reuse boundary, not materialization inside a database. Reset, set both C=3 and s=4, predict zero hits in the initial sixteen requests, then Play or Step. Separately Reset, set both C=4 and s=4, predict twelve initial hits, then Play or Step. This fixed trace cycles over addresses 0,4,8,12 and starts with an empty fully associative LRU cache before its sixteen initial requests. It neither allocates a SQL spool nor measures iterator overhead. Use it to explain why retaining less than the repeatedly needed set can lose the intended reuse.