CTEs — Non-Recursive vs Recursive Hierarchies
WITH clauses stage complex analytics. Recursive CTEs walk trees such as org charts. Interviews probe readability versus materialization, and how you stop a cycle.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
How you stage a multi-step query
Prefer
WITH for names, hints only when measured
filtered, then agg, then the outer predicate. Readable in review. Inline when you need predicate pushdown. Materialize when the same expensive set is referenced twice.
- Default since Postgres 12 can inline a side-effect-free CTE.
- MATERIALIZED computes once. That fence can help or block pushdown.
- Confirm with EXPLAIN. Do not recite scan types here.
Alternative
Deep subqueries or a temp table by habit
A derived table is the same relation with worse names once nesting stacks. A temp table can carry statistics, and it is session overhead plus a procedural step.
- Temp tables are imperative. Fine when you truly need a second plan.
- UNION instead of UNION ALL dedups every recursive round.
- No cycle guard on an org chart is a runaway query.
Overview
WITH stages complex analytics. Recursive CTEs walk trees: org charts, category graphs, bill of materials. Interviews probe readability versus materialization, and how you stop cycles.
Non-recursive CTE vs subquery
WITH filtered AS (
SELECT * FROM events WHERE ds >= CURRENT_DATE - 7
),
agg AS (
SELECT user_id, COUNT(*) AS c FROM filtered GROUP BY user_id
)
SELECT * FROM agg WHERE c >= 10;- CTE: named steps, reusable in the query, good for review. It may still be inlined.
- Subquery / derived table: sometimes clearer nesting. Deep nesting hurts review.
- Temp table: explicit stats are possible, with session overhead. More imperative.
MATERIALIZED and NOT MATERIALIZED
Postgres 12 and later:
WITH mid AS MATERIALIZED (
SELECT expensive_id, payload FROM big_source
),
q AS NOT MATERIALIZED (
SELECT expensive_id FROM big_source WHERE flag
)
SELECT * FROM mid JOIN q USING (expensive_id);- Default: the optimizer may inline a non-recursive, side-effect-free CTE, especially when it is referenced once.
- MATERIALIZED: compute once. Helps when the CTE is referenced many times, or you want a fence.
- NOT MATERIALIZED: force inline. Helps predicate pushdown into the CTE.
- Always confirm with
EXPLAINon EXPLAIN and EXPLAIN ANALYZE. Multiple references are a reason to look, not a proof it materialized.
Flow
- 1
1 Parse the WITH clauses
- next2 Inline or materialize
- 2
2 Inline or materialize
- next3 Anchor, then recursive step
- 3
3 Anchor, then recursive step
- next4 Stop on cycle or depth
- 4
4 Stop on cycle or depth
- next5 Join and filter the result
- 5
5 Join and filter the result
Lesson map
CTEs — Non-Recursive vs Recursive Hierarchies
WITH clauses stage complex analytics. Recursive CTEs walk trees such as org charts. Interviews probe readability versus materialization, and how you stop a cycle.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1 Parse the WITH clauses"] b["2 Inline or materialize"] c["3 Anchor, then recursive step"] d["4 Stop on cycle or depth"] a -->|1 Parse the WITH clauses| b b -->|2 Inline or materialize to 3| c c -->|3 Anchor, then recursive step| d
Non-recursive queries stop after the inline-or-materialize choice. Recursive queries take the anchor path. The diagram is one column so both stories stay readable. The branch is the decision above, not a second swimlane.
Recursive hierarchies
WITH RECURSIVE org AS (
SELECT id, manager_id, name, 1 AS depth, ARRAY[id] AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, o.depth + 1, o.path || e.id
FROM employees e
JOIN org o ON e.manager_id = o.id
WHERE NOT e.id = ANY (o.path)
AND o.depth < 50
)
SELECT * FROM org;- Anchor: seed rows. Here, people with no manager.
- Recursive member: join the base table to the name
org, which means “rows already found.” - Cycle guard:
e.idis not already onpath. - Depth guard: stop at 50 even if the data is messy.
- Newer Postgres also has a
CYCLEclause. Know it exists. The path array still interviews well.
Use UNION ALL. Plain UNION deduplicates every round.
One recursive step
- 1
Seed
Roots where manager_id is null. depth 1. path is ARRAY[id]. - 2
Expand
Children whose manager_id equals a row already in org. - 3
Reject a cycle
Skip a child already on the path. - 4
Cap depth
depth less than 50. No unbounded work table. - 5
Order at the end
Iteration is breadth-like. ORDER BY the final SELECT if the UI needs a sort.
When not to recurse
| Problem | Prefer |
|---|---|
| Tree walk or BOM | WITH RECURSIVE |
| Linear streaks and islands | Windows. See the islands page |
| Read-heavy nested set or closure table | Precomputed structure. Pay at write time |
| App-side BFS | Rare. N+1 unless the tree is cached |
Search order of the work table is breadth-like. If the consumer needs a specific order, ORDER BY the outer query.
Deep dive · EXPLAIN is the materialization check
Inlining, a materialize node, and a recursive work table are plan facts. Read them on EXPLAIN and EXPLAIN ANALYZE. The indexes hub is Indexes, cardinality, and EXPLAIN. This page does not choose access methods.
Sandboxes
A queue walk mirrors the recursive CTE: seed the root, expand children, skip ids already on the path.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Pitfalls
Add an edge from employee 4 back to 2. Show the path-array guard rejecting it, and a depth cap of 50. Then say why a streak of active days should not use this query.
Interview Q&A
CTE vs subquery?
Answer
Clarity and reuse inside one statement. Performance is not automatic. A deep derived table is the same plan with worse names.
What changed for materialization in Postgres 12?
Answer
Non-recursive CTEs can be inlined by default. MATERIALIZED and NOT MATERIALIZED are the overrides. Check EXPLAIN.
Anchor vs recursive member?
Answer
The anchor seeds rows. The recursive member UNION ALLs a step that joins the previous iteration.
How do you prevent infinite recursion?
Answer
Path membership, a depth cap, and the CYCLE clause where the server has it.
UNION vs UNION ALL?
Answer
Almost always UNION ALL. UNION deduplicates on every round.
When should you not recurse?
Answer
Prefer windows for sequences. Prefer a closure table when reads dominate and you can pay at write time.
What if several CTEs reference the same one?
Answer
That may trigger materialization. It is a hint to read the plan, not a guarantee. EXPLAIN.
What is the search order?
Answer
Work-table iteration is breadth-like. Put ORDER BY on the final SELECT if the caller needs an order.
Can recursion appear in DML?
Answer
Some data-modifying WITH forms exist. Keep them transactional and small. Do not invent a graph engine out of them.