Executors as Iterator Machines

The plan starts moving

A plan is a promise about how to produce rows. The executor is the machinery that keeps that promise. It opens plan nodes, asks them for tuples, passes those tuples upward, and eventually returns results to the client or applies changes to storage. A Postgre-like executor works as an iterator machine: each node implements a common pattern of initialize, produce the next tuple, and finish. Complex queries become trees of small producers and consumers.

The iterator model works well because it localizes behavior. A sequential scan knows how to walk pages and test row visibility. An index scan knows how to follow index entries and fetch heap tuples. A filter node evaluates a predicate. A nested loop join repeatedly pulls from an outer child and probes an inner child. Parent nodes do not need every detail of their children. They ask for the next tuple.

Streaming until it cannot

This streaming shape keeps memory use low for many queries. If a query scans a table, filters rows, and projects two columns, tuples can flow one at a time. The executor does not need to materialize the entire table before returning the first result. That matters for latency and for queries over large relations. Streaming also fits the protocol, where results can be sent incrementally as rows become available.

Blocking points change the behavior

Not every node can be purely streaming. Sort must often read all input before returning the first row. Hash join must build a hash table for one side before probing with the other. Aggregation may need to hold groups in memory. Materialize nodes may intentionally store intermediate results so they can be reread. The executor is therefore a mix of pipelines and blocking points. Knowing where a plan blocks tells you a lot about memory pressure and first-row latency.

Nested loops and hash joins behave differently once execution begins. A nested loop can produce its first joined row quickly if the outer side produces a row and the inner lookup is cheap. It can be slow if the outer side is large and each inner scan repeats heavy work. A hash join delays output while building the hash table, but then probes efficiently for many rows. The planner chose between them using estimates, and execution shows whether those estimates held.

Expression evaluation is another central executor service. Predicates, computed columns, function calls, casts, and aggregate transitions all run inside expression contexts. These contexts manage short-lived memory so per-tuple allocations can be reset cheaply. Without that discipline, a long scan could leak tiny allocations until the backend bloats. The executor's memory model matters as much as its tuple model.

Execution is more than reads

Visibility checks tie execution back to transactions. A heap scan does not simply return every physical row version it finds. It asks whether the tuple is visible under the current snapshot. An update may create a newer version while an older transaction still needs the old one. The executor must apply MVCC rules consistently, or the query result becomes a mix of incompatible times.

Modification statements use the executor too. An `insert` creates tuples, checks constraints, updates indexes, and emits log records through storage layers. An `update` often creates a new tuple version rather than overwriting in place. A `delete` marks a tuple version as no longer visible to future transactions. The plan may have a scan subtree to find affected rows, followed by a modification node that applies changes. Reads and writes share the same framework because both are tuple-producing operations with side effects controlled by transaction state.

Triggers and constraints add controlled interruptions. A row may pass through before-row triggers, constraint checks, index maintenance, and after-row triggers. Foreign keys can require lookups in other tables. The executor coordinates these behaviors without letting them turn the plan tree into arbitrary application code. Each hook has a defined moment in the tuple lifecycle.

Instrumentation often lives here as well. To explain a plan, the system can count rows produced by each node, loops executed, time spent, buffers touched, and memory used. This feedback is how users find out when estimates were wrong or when a plan shape behaves differently than expected. The executor does the work, and it is also the place where planned work becomes observable.