Common Table Expressions and Recursion

WITH gives the stages of a query names. A report built from three levels of nested subqueries becomes four labelled steps that read top to bottom, and an intermediate result computed once can be referenced twice. That is the readability argument, it is real, and it is not the interesting part.

The interesting part is what the planner does with the names. Through PostgreSQL 11 a CTE was an optimization fence: it was always evaluated separately, before the rest of the query, and no predicate from outside could reach into it. Version 12 changed that. Advice written in 2018 and advice written now therefore contradict each other, and both sound confident. Then there is WITH RECURSIVE, which is a loop with a working table rather than recursion in any usual sense.

WITH as the Query's Table of Contents

A CTE is a named subquery attached to the front of a statement. Syntactically it costs nothing and it changes nothing about what the query means. What it changes is whether a human can review the thing: a filter step, an aggregation step and a join step written as three named blocks are three claims that can each be checked, where the same logic nested three deep is one claim that gets checked whole or not at all.

WITH recent AS (
    SELECT id, customer_id, total
      FROM orders
     WHERE placed_at >= now() - interval '30 days'
), per_customer AS (
    SELECT customer_id, count(*) AS order_count, sum(total) AS spend
      FROM recent
     GROUP BY customer_id
)
SELECT c.city, count(*) AS customers, sum(p.spend) AS spend
  FROM per_customer p
  JOIN customers c ON c.id = p.customer_id
 GROUP BY c.city
 ORDER BY spend DESC;

Each block names one step: the last thirty days of orders, the per-customer totals over them, the per-city roll-up of those. A reviewer can read the middle block and say whether the aggregation is right without holding the other two in their head. Later blocks may reference earlier ones, but not the other way round, and the main query may reference any of them.

The Fence That Was Removed in 12

Before 12, Postgres always materialized a CTE: it ran the block, stored the result, and only then ran the rest of the query against that stored copy. A predicate in the outer query could not be pushed down into the block, because by the time the predicate was evaluated the block had already finished. Since 12, a WITH query is folded into the parent query when three conditions hold — it is not recursive, it is side-effect-free (a SELECT containing no volatile functions), and the parent references it exactly once. Then the two levels are planned together.

WITH all_orders AS (SELECT * FROM orders)
SELECT * FROM all_orders WHERE id = 4471029;

-- on 18: inlined, and the plan is an index scan on orders_pkey
-- on 11: a CTE Scan over a full sequential scan of 40 million rows

On 11 that query read the whole table to find one row, and the shape of the query gave no hint that it would. On 18 it plans exactly as if the CTE had been written as a subquery, because that is what the rewriter turns it into. The tell in a plan is the CTE Scan node — Chapter 9 reads it in context. When it is there, the block was computed separately, and everything the outer query knows was unavailable to it.

When Materializing Is What You Want

The choice can also be written down. AS MATERIALIZED forces separate calculation even for a single-reference block. AS NOT MATERIALIZED forces folding even when the block is referenced more than once, which risks computing it several times but pays when each use needs only a small slice of its output. A block the parent query references more than once is materialized by default, and one containing a data-modifying statement or a volatile function is always materialized — there is no version of Postgres that runs your UPDATE twice because it was written inside a WITH.

WITH day_totals AS MATERIALIZED (
    SELECT date_trunc('day', placed_at) AS day, sum(total) AS revenue
      FROM orders
     WHERE placed_at >= '2026-07-01'
     GROUP BY 1
)
SELECT t.day, t.revenue, t.revenue - y.revenue AS change_on_yesterday
  FROM day_totals t
  LEFT JOIN day_totals y ON y.day = t.day - interval '1 day';

The aggregation over six weeks of orders is the expensive part and it is wanted once, not twice. Reach for NOT MATERIALIZED in the mirror case: a block referenced twice where each reference filters it down to a handful of rows, and folding lets each of those filters reach an index. Two references already make materialization the default, so the keyword is not changing today's plan — it is documenting the requirement, so that a later edit which drops one of the two references does not convert one aggregation into an inlined scan per reference.

WITH RECURSIVE, Mechanically

A recursive CTE has three parts: a non-recursive seed term, UNION ALL (or UNION), and a recursive term that references the CTE's own name. Postgres evaluates it iteratively, and the algorithm is short enough to memorize. Run the seed; put its rows in the result and also in a working table. Then, as long as the working table is not empty, evaluate the recursive term with the working table substituted for the self-reference, add those rows to the result and to an intermediate table, and replace the working table with the intermediate one. When a round produces nothing, the loop ends.

WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth
      FROM product_categories
     WHERE id = 12                        -- the seed: 'Fresh produce'
  UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1
      FROM product_categories c
      JOIN subtree s ON c.parent_id = s.id
     WHERE s.depth < 10                   -- the termination story
)
SELECT depth, name FROM subtree ORDER BY depth, name;

Round one produces the one seed row. Round two joins the categories table against that row only and produces its direct children. Round three sees only the children, and produces the grandchildren. Any logic that needs the full accumulated set — a running path, a visited list — has to be carried forward in a column, and the depth counter here does exactly that. The self-reference inside the recursive term does not mean "everything found so far"; it means "the rows the previous round produced".

Cycles and Termination

A tree terminates on its own; a graph does not. If some category's ancestor chain loops back on itself, the working table never empties, and the query keeps producing rows until the temporary files it is spilling into exhaust the disk. The error message then names disk space, which sends the investigation to the wrong place entirely. Setting temp_file_limit converts that outage into a failed query, but it is a seatbelt, not a fix.

UNION instead of UNION ALL discards rows that duplicate any previous result row, which stops a simple cycle at the cost of a duplicate check on every round — worth nothing on an acyclic tree and charged anyway. A depth counter carried in a column and bounded in the recursive term's WHERE clause is crude and effective. And since 14 there is a proper mechanism.

WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name FROM product_categories WHERE id = 12
  UNION ALL
    SELECT c.id, c.parent_id, c.name
      FROM product_categories c
      JOIN subtree s ON c.parent_id = s.id
) SEARCH DEPTH FIRST BY name SET ordercol
  CYCLE id SET is_cycle USING path
SELECT id, name, path FROM subtree WHERE NOT is_cycle ORDER BY ordercol;

The CYCLE clause names the columns that identify a row, adds a boolean column that turns true the moment a row repeats on the current path, and adds an array column holding the path itself — so the query stops expanding a branch when it closes a loop, and the offending chain is visible in the output rather than inferred from a crash. SEARCH adds a sort column implementing depth-first or breadth-first order. Both clauses arrived in 14 and both are SQL-standard. The evaluation algorithm does emit rows breadth-first in practice, and nothing in the specification promises it will keep doing so.

What Recursion Is Actually For

Walking a hierarchy in one round trip instead of one query per level is where the category tree lives. Expanding a chain of references — a delivery route, a chain of substitutions — covers the case where the number of steps is not known when the query is written. And generating a series where each element is derived from the last is how you build a dense axis with an irregular step, so that hours with zero deliveries appear on the dashboard as zeros rather than as absences.

For a constant step, that last one has a much shorter answer: generate_series produces the same rows as a function call, and a later topic in this chapter builds a reporting axis out of it. The recursive form earns its place when the step depends on the previous value, or when the series is the traversal of a structure rather than an interval. Everything here still leaves one gap in the Cartwheel story: the warehouse feed that replays and has to write rows without knowing whether they already exist.