Postgres Common Table Expressions: Patterns That Fit and the Ones That Hurt

Common Table Expressions are one of those SQL features that feels like a productivity unlock the first time you use one. Instead of nested subqueries that scroll off the screen, you write a sequence of named intermediate results that read top-to-bottom. The syntax is clean, the intent is obvious, and the resulting query is documentation. Then you discover that some CTEs make queries dramatically slower than the equivalent subquery, and the reasons are not obvious from the syntax.

We use CTEs across all four products: DocuMint for invoice line-item rollups, CronPing for monitor history aggregations, FlagBit for flag evaluation traces, and WebhookVault for delivery-attempt summaries. Each forced a learning moment about when CTEs help and when they hurt.

The optimization fence

Before Postgres 12, every CTE was an optimization fence. The planner evaluated the CTE separately, materialized the result into a temporary working set, and then queried that set in the main query. This had two consequences. First, predicates from the outer query could not be pushed into the CTE: if you joined a 10-million-row CTE with a single-row filter from the outer query, Postgres would compute all 10 million rows of the CTE and then filter to one. Second, repeated CTE references were evaluated only once, which was sometimes a feature and sometimes a problem.

Postgres 12 changed this. By default, CTEs are now inlined when they are referenced exactly once and have no side effects. This makes most CTEs equivalent to the corresponding subquery for performance purposes, while keeping the readability win. You can still force the old behavior with WITH ... AS MATERIALIZED, or force inlining with WITH ... AS NOT MATERIALIZED.

The implication is that on Postgres 11 and earlier, every CTE deserves a moment of "should this be a subquery instead?" thinking. On Postgres 12+, the question is mostly settled, but the explicit MATERIALIZED hint becomes a useful tool when you genuinely want the optimization fence behavior, for example to compute an expensive aggregation once and reference it in multiple places.

The patterns that fit

Three patterns are where CTEs reliably earn their keep. First, multi-step transformations where each step depends on the previous: filter to active rows, then join with a lookup table, then aggregate, then rank. The CTE chain reads like a pipeline and is much easier to debug step-by-step than the equivalent nested subquery, since you can comment out the final SELECT and replace it with a SELECT against the last CTE name to inspect the intermediate state.

Second, cases where the same intermediate result is referenced multiple times, especially with MATERIALIZED hint. Computing an expensive aggregation once and joining it with itself or with multiple downstream queries avoids planner mistakes around when to memoize. The classic example is a percentile-rank query where you need both the value and the rank for downstream comparisons.

Third, recursive CTEs. There is no other reasonable way to express graph traversals or tree walks in SQL. The recursive CTE is one of the genuinely high-leverage features of modern SQL, and it has no clean substitute.

The patterns that hurt

Two patterns reliably underperform. First, the over-decomposed CTE, where every CTE is a single SELECT or join that the optimizer would have planned better if given the whole query at once. The readability cost is real (a 200-line CTE chain is not actually clearer than a 100-line nested query), and on older Postgres versions the materialization cost is also real.

Second, the recursive CTE that is actually iterating over a small fixed depth. If your tree is at most 5 levels deep, a recursive CTE can be slower than the equivalent five LEFT JOINs because the planner has more freedom with explicit joins. The crossover depends on data distribution, but the rule of thumb is: if you know the depth is bounded and small, write the joins; if the depth is unbounded or large, write the recursive CTE.

Recursive CTE patterns worth knowing

The basic recursive CTE has two parts: the anchor query that produces the seed rows, and the recursive query that produces additional rows by joining with the CTE itself. Postgres evaluates this iteratively until no new rows are produced.

The standard tree-walk pattern joins the recursive query against the CTE on parent_id = id and accumulates a path string for sorting and depth tracking. The standard graph-walk pattern adds a visited-set check (usually via array-append and the ANY operator) to prevent infinite loops in graphs with cycles. The standard transitive-closure pattern omits the depth limit and lets Postgres run until the working set is empty.

The performance gotcha is that recursive CTEs do not preserve indexes on the working set. Each iteration starts from scratch, scanning the previous iteration's output. For deep trees with millions of nodes, this can become quadratic. The mitigation is to project only the columns you actually need into the recursive working set, since narrower rows mean faster scans.

The DML CTE

Postgres allows INSERT, UPDATE, and DELETE inside CTEs, with the result feeding into the main query. This is one of the most powerful and underused SQL features. The classic use case is moving rows between tables in a single statement: WITH moved AS (DELETE FROM source WHERE ... RETURNING *) INSERT INTO destination SELECT * FROM moved. This is atomic, transactional, and impossible to express cleanly without DML CTEs.

The DML CTE is also useful for upserts with side effects, log-and-update patterns, and cascade-delete operations that need to capture data for audit before destruction. The discipline is to keep the chain short (more than two DML CTEs becomes hard to reason about) and to document the data-flow direction explicitly in comments.

The deeper observation

CTEs are an example of a feature that improves readability but has performance characteristics that the syntax does not surface. The right discipline is to learn the optimization fence rules for your Postgres version, default to inlined CTEs for clarity, and reach for MATERIALIZED only when you have a specific reason. The rare cases where CTEs underperform are usually obvious in EXPLAIN ANALYZE: look for "CTE Scan" rows with high row counts that should have been filtered earlier in the plan.

The deeper lesson is that SQL is a declarative language with a non-trivial cost model, and writing fast SQL means understanding both the language and the planner. CTEs make the language part easier; they do not change the planner part. The skill of reading EXPLAIN output is what bridges the gap, and it compounds across every database query you write for the rest of your career.