CTE Materialization

Materializing a common table expression means computing it once and holding the result while the statement runs. Inlining means substituting the CTE’s query text at each place it is referenced, so a CTE referenced twice may be computed twice. For the deterministic SELECT example, both strategies produce the same result; they differ in how much work the engine repeats and how much memory it holds.

Which one you get is a planner decision that varies by product and version. Before PostgreSQL 12, SELECT CTEs generally acted as optimization fences. From 12, a nonrecursive, side-effect-free SELECT CTE referenced once is normally folded into its parent, unless AS MATERIALIZED prevents folding. AS NOT MATERIALIZED asks for the opposite. DuckDB accepts both keywords as hints. Other engines expose neither and decide on their own.

The distinction matters in two situations. When a CTE is expensive and referenced several times, materialization can avoid repeating the work; when it is cheap and inlining would let the optimizer push a filter down into it, inlining can avoid scanning far more rows than the final query needs. Inlining does not by itself give each reference a new database snapshot. Visibility of concurrent writes follows the engine’s isolation rules. Volatile expressions require separate care: PostgreSQL does not fold CTEs containing volatile functions, and NOT MATERIALIZED is ignored when folding is ineligible.

Because this is a planning question, claims about it need evidence from your own engine and data: read the plan with EXPLAIN, compare timings on realistic volumes, and re-check after a version upgrade. Keyword semantics are engine-specific; accepting a keyword or matching counts is not proof of the chosen plan, and a CTE never becomes a durable object no matter which strategy is chosen. For reuse across statements, a temporary table reuses stored rows while a view reuses a query definition.

References: PostgreSQL WITH queries, DuckDB WITH clause. See it in use in CTEs and Temporary Tables.