Postgres CTEs as Optimization Fences: When the Old Behavior Was a Feature

Before Postgres 12, common table expressions were optimization fences. The planner would materialize the CTE result into a temporary structure and then read from it as a separate operation, regardless of whether inlining the CTE into the surrounding query would produce a better plan. This was undocumented but widely known, and teams used it deliberately to force specific query shapes when the planner was making bad choices.

Postgres 12 changed the default. CTEs are now inlineable by default when referenced exactly once and when they have no side effects. The change was almost always an improvement, because most CTEs were written for readability rather than for the fence behavior, and most queries with materialized CTEs had been quietly paying a temp-table cost for no benefit. But the change broke a small number of production queries where the fence had been load-bearing, and the cases where the old behavior was actually correct are worth understanding.

What CTEs do now

A CTE in Postgres 12 or later is treated as an inlineable view by default. If you write a query that references the CTE once, the planner can fold the CTE expression into the surrounding query and optimize across the boundary. The result is often a substantially better plan, especially when the CTE filters or joins with information from the outer query.

The opt-in syntax for the old behavior is WITH foo AS MATERIALIZED (...). This forces the planner to compute the CTE as a separate operation, store the result in memory or a temp table depending on size, and read from that result in the outer query. The NOT MATERIALIZED opposite is also available for explicit inlining when you want to override the default heuristic.

The decision rule the planner uses is conservative: inline if referenced exactly once and side-effect-free, materialize if referenced multiple times or if the CTE contains DML (INSERT, UPDATE, DELETE) or volatile functions. Multiple references default to materialized because the alternative would recompute the CTE for each reference, which is usually worse than the temp-table cost.

The cases where MATERIALIZED earns its cost

The first case is multi-reference CTEs. If the CTE is referenced two or more times in the outer query, materializing once and reading multiple times is cheaper than recomputing the CTE expression for each reference. The planner handles this case automatically, but it is worth noting that the rewrite that wraps a single-reference CTE into a subquery would not get the same benefit if the subquery is repeated in the outer query.

The second case is CTEs that contain expensive aggregations followed by self-joins. If the CTE computes a result set with significant work and the outer query joins that result set with itself or with itself plus another table, materializing the aggregation once and using it as a fixed input for the join can be substantially cheaper than letting the planner inline and potentially recompute parts of the aggregation under different join orders.

The third case is queries where the planner has bad statistics about an inlined CTE and produces a worse plan than the materialized version. This is the rare case where the old fence behavior was actively useful. If the CTE filters a large table down to a small number of rows but the planner does not know this (because the underlying statistics do not capture the correlation), an inlined CTE may lead to a join order that assumes the CTE is large and produces a slow plan. Materializing the CTE forces the planner to compute the actual row count and use it for downstream planning.

The fourth case is data-modifying CTEs. INSERT, UPDATE, and DELETE statements inside CTEs are always materialized because the DML must execute exactly once. The CTE syntax is the standard way to write multi-statement DML in a single query with intermediate result reuse, and the materialization is correct.

The patterns that benefit from inlining

The most common case is CTEs written for readability that filter or transform a table for use in a single outer reference. Before Postgres 12, this pattern would silently materialize the CTE result and walk over it again in the outer query. After the change, the planner can fold the CTE into the outer query and choose a plan that may push predicates down, eliminate unnecessary columns, and combine operations.

The performance improvement from the change can be substantial for queries that filter or aggregate against indexes that the planner could not exploit through the old fence. The pattern of "write a CTE for the readable shape, get the optimization as a free bonus" is one of the quiet wins from upgrading old code to Postgres 12 or later.

The diagnostic for which case applies

The way to tell whether a specific CTE benefits from materialization is to read the EXPLAIN ANALYZE output with both forms. Write the query with the default (inlineable) and with explicit MATERIALIZED, and compare the actual execution times and plan shapes. The planner is usually right but not always, and the choice can be reversed by changing one keyword in the query.

The patterns to watch for in the explain output are: large recompute operations that indicate the CTE was computed multiple times despite a single reference (rare but possible with complex queries); join orders that assume the wrong cardinality for the CTE result (most common with selective predicates that the statistics do not capture); and temp-table operations that suggest a materialization happened even when the query was written without the explicit keyword (this is the multi-reference case).

What did not change

The recursive CTE syntax (WITH RECURSIVE) is not affected by the change. Recursive CTEs are always materialized because the recursion requires an intermediate result that the recursion step reads from. The recursive case is genuinely different from the non-recursive case and the optimization-fence question does not apply.

The behavior of CTEs that contain DML is also unchanged. INSERT, UPDATE, and DELETE inside CTEs always execute exactly once and produce materialized results that the outer query can read.

The PostgreSQL documentation as of version 17 is clear about the change and the opt-in syntax. The migration path for code that depended on the old fence behavior is to add MATERIALIZED to the affected CTEs explicitly. The migration path for code that did not need the fence is to upgrade and benefit from the better plans.

The connection to our work

Our four products (DocuMint for PDF invoice generation, CronPing for cron job monitoring, FlagBit for feature flags, and WebhookVault for webhook debugging) are SQLite-based at our current scale and do not use the Postgres CTE machinery. SQLite has its own CTE implementation with different optimization behavior: SQLite CTEs are always materialized as of version 3.35 and the optimization fence is the documented behavior. The semantics divergence between Postgres and SQLite on CTEs is one of the small things to be aware of when writing portable SQL.

Our planned migration to Postgres at scale will inherit the Postgres CTE semantics, which means existing SQLite-tuned queries with CTEs may produce different plans after migration. The discipline we plan to follow is to read EXPLAIN ANALYZE for any query that uses CTEs after the migration, identify queries where the inlining produces a substantially better or worse plan, and add MATERIALIZED selectively where the old SQLite behavior was relied on.

Three observations

First: the CTE optimization-fence change is one of the small details about Postgres that does not get the attention it deserves. The change was substantively a performance improvement for the typical case, but it also broke a small number of production queries silently. Teams that upgraded from Postgres 11 to 12 without reading the changelog encountered slower queries in the few cases where the fence had been load-bearing, and the diagnosis required understanding both the old and new behavior.

Second: the syntax for opting in to the old behavior is the same syntax the SQL standard uses for explicit materialization. The Postgres 12 change brought Postgres in line with the standard behavior, which is that CTEs are inlineable views unless the SQL author explicitly requests materialization. The pre-12 Postgres behavior was actually non-standard, and the change made Postgres more portable to standard-compliant databases.

Third: the cases where MATERIALIZED earns its cost are typically about overriding the planner when its statistics are wrong. The patterns where this happens (selective predicates that the statistics do not capture, complex aggregations with downstream joins, multi-reference CTEs) are diagnosable through EXPLAIN ANALYZE and should be addressed explicitly rather than relied on as silent default behavior. The keyword exists for the cases that need it, and the cases that need it should be marked.

The deeper observation is that the migration from "this is the way Postgres works" to "this is the standard SQL behavior" is one of the slower transitions in the Postgres ecosystem. The CTE change was made in 2019 and many production code bases still have CTEs that were written for the old behavior, either deliberately or accidentally. Reading the changelog when upgrading is one of the small disciplines that prevents these silent regressions. The cost of doing this is low and the cost of missing it can be substantial. As with most operational details about a long-lived database, the discipline of paying attention to small behavioral changes compounds over years.