CTEs and Recursive Queries

Description

Long SQL gets hard to read fast: a subquery inside a subquery inside a join, read from the inside out. WITH fixes that. It lets you name each step of a query - "daily revenue", then "with the previous day", then "only the drops" - so the query reads top to bottom like a short story. These named steps are called common table expressions (CTEs).

WITH has a second, more powerful form. Some questions have no fixed depth: "everyone in Liam's organization, however many levels down", "every category under Electronics", "everyone reachable from this user". No fixed number of joins answers them. WITH RECURSIVE does - a query that keeps applying itself to its own results until nothing new turns up. We'll cover:

1. WITH for readable, step-by-step queries, and how Postgres runs it.
2. WITH RECURSIVE - walking an org chart of any depth, with depth and path.
3. Generating sequences with recursion.
4. Graphs with cycles, and how to stop a recursion that would run forever.
5. Which tool to use for which question.

Examples use seven employees - Ava at the top, Liam and Sofia under her, Liam's team two levels deep - and a small follows graph with a cycle (1 → 2 → 3 → 1, and 3 → 4). Every result was produced by PostgreSQL 18.

WITH: name the steps

A CTE gives a query a name for the length of one statement:

WITH reports AS (
    SELECT manager_id, COUNT(*) AS direct_reports
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
)
SELECT e.name, r.direct_reports
FROM reports r
JOIN employees e ON e.id = r.manager_id
ORDER BY r.direct_reports DESC, e.name;

Each CTE can use the ones before it, so a long analysis reads top to bottom as named steps - "daily revenue", then "with the previous day", then "only the drops" - instead of inside out.

How Postgres runs it. Since PostgreSQL 12, a CTE referenced once (with no side effects) is inlined - planned exactly as if you'd written the subquery in place, so filters from the outer query can be pushed into it. A CTE referenced more than once is materialized: computed once, stored, and read by each reference. You can force either with WITH x AS MATERIALIZED (...) or AS NOT MATERIALIZED - materializing is useful to stop an expensive step running twice, harmful when it prevents the planner from using an index for a filter applied outside.

Writes in CTEs. A WITH step can be INSERT, UPDATE or DELETE ... RETURNING, feeding the rows it touched into the next step - for example WITH moved AS (DELETE FROM orders WHERE ordered_at < '2025-01-01' RETURNING *) INSERT INTO orders_archive SELECT * FROM moved; archives in one atomic statement. All the steps see the same snapshot of the data, so a step can't read another step's changes to a table - only its RETURNING rows.

WITH RECURSIVE

A recursive CTE has two parts joined by UNION ALL (or UNION):

1. The anchor runs once and produces the starting rows.
2. The recursive term refers to the CTE itself. It runs on the rows produced by the previous round, again and again, until a round produces no new rows.

WITH RECURSIVE team AS (
    SELECT id, name, 1 AS depth                -- anchor: Liam's direct reports
    FROM employees
    WHERE manager_id = 2
  UNION ALL
    SELECT e.id, e.name, t.depth + 1           -- step: reports of the rows found so far
    FROM employees e
    JOIN team t ON e.manager_id = t.id
)
SELECT name, depth FROM team ORDER BY depth, name;

Round one finds Noah and Emma; round two finds Lucas (Noah's report); round three finds nobody, and the recursion stops. Counting depth in the recursive step tells you how far below Liam each person sits. Note the anchor starts from Liam's reports - start from WHERE id = 2 instead if Liam should be in the result himself.

Carrying a value down the recursion builds paths - here the reporting chain from the top:

WITH RECURSIVE chain AS (
    SELECT id, name::text AS path FROM employees WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, c.path || ' > ' || e.name
    FROM employees e
    JOIN chain c ON e.manager_id = c.id
)
SELECT path FROM chain ORDER BY path;

Recursion isn't only for trees - it can generate sequences:

WITH RECURSIVE n AS (
    SELECT 1 AS i
  UNION ALL
    SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

(For number and date sequences, generate_series(1, 5) is simpler and faster - the time-series module uses it.)

Cycles

An org chart is a tree, so the recursion ends at the leaves. A general graph - followers, flight routes, a bill of materials with a mistake in it - can loop. With UNION ALL, a cycle produces new rows forever, until the statement timeout kills it. Two guards:

• UNION instead of UNION ALL removes rows already produced, so the recursion stops once nothing new is reached. That works when each row is just "a node":

WITH RECURSIVE reach(node) AS (
    SELECT 1
  UNION                                        -- not UNION ALL: stop when nothing new is found
    SELECT f.b FROM follows f JOIN reach r ON f.a = r.node
)
SELECT node FROM reach ORDER BY node;

• A path array, when rows carry more (depth, path), so rows are never exact duplicates: stop extending a path when the next node is already on it.

WITH RECURSIVE walk AS (
    SELECT 1 AS node, ARRAY[1] AS path
  UNION ALL
    SELECT f.b, w.path || f.b
    FROM walk w
    JOIN follows f ON f.a = w.node
    WHERE NOT f.b = ANY (w.path)               -- never revisit a node on this path
)
SELECT node, path FROM walk ORDER BY cardinality(path), node;

PostgreSQL 14+ also has a CYCLE clause that maintains that path and a cycle flag for you, and a SEARCH clause that orders results depth-first or breadth-first.

Which tool?

Where it breaks

• A recursive CTE over data with a cycle and UNION ALL - runs until the timeout. Guard with UNION, a path check, CYCLE, or a maximum depth.
• Fixed self-joins for a hierarchy - e1 JOIN e2 JOIN e3 handles exactly three levels; the fourth level silently disappears.
• The anchor includes (or excludes) the root by accident - read the requirement: "Liam's organization" may or may not mean Liam.
• A CTE used to "cache" a result that is referenced once - since Postgres 12 it's inlined, not cached.
• Recursing from children to parents vs parents to children - the join direction (e.manager_id = t.id walks down; e.id = t.manager_id walks up) decides whether you get descendants or ancestors.

Tradeoffs

• CTE vs subquery: identical plans in modern Postgres when referenced once; the CTE is easier to read and test step by step.
• Adjacency list + recursive CTE vs other tree encodings: storing just parent_id makes moves and inserts trivial and reads need recursion; a materialized path ('1/2/4', or the ltree extension) or a closure table makes subtree reads a simple indexed lookup at the cost of harder writes. For org charts and categories with thousands of rows, recursion is fast enough.
• UNION vs UNION ALL in recursion: UNION deduplicates each round (cycle-safe for simple rows, but slower); UNION ALL is faster and right for trees.

Going further. The deep dive (next tab) shows the plan of a recursive query and what makes it fast or slow, CTE inlining versus materialization with real plans, the SEARCH and CYCLE clauses, writable CTEs and their snapshot, four ways to store a tree, and why graph queries in SQL can explode.

Check yourself

1. A recursive CTE has an anchor and a recursive term. When does it stop?

2. Why can WITH RECURSIVE over a follows graph run forever with UNION ALL but finish with UNION?

3. You wrote WITH totals AS (...) SELECT ... FROM totals WHERE region = 'EU' to "compute totals once". Is it computed once, before the filter?

Key takeaways

• WITH names query steps for one statement; Postgres 12+ inlines single-use CTEs and materializes multi-use ones (override with MATERIALIZED / NOT MATERIALIZED).
• WITH RECURSIVE = anchor, then a recursive term run on the previous round's rows until a round adds nothing.
• Track depth and path in the recursive term; walk down with child.parent_id = t.id, up with t.parent_id = parent.id.
• Cycles need a guard: UNION, a path array check, the CYCLE clause, or a depth limit.
• Fixed self-joins only handle a fixed depth - hierarchies of unknown depth need recursion.