ReviseAlgo Logo

Common Table Expressions (CTE)

Recursive CTEs and Hierarchy Queries

Traversing tree hierarchy data sets like manager-employee tables.

Last Updated: June 12, 2026 • 15 min read

A recursive CTE uses WITH RECURSIVE to reference itself, enabling traversal of tree-structured data of arbitrary depth. Org charts, category trees, threaded comments, bill-of-materials — any parent-child hierarchy requires recursive CTEs to walk the full tree in a single query.

1. Introduction

A self join can traverse one level of a hierarchy (employee → manager). But what if you need to walk an entire org chart from CEO to intern — an unknown number of levels? Recursive CTEs solve this by combining a base case (the root nodes) with a recursive step (find children of the previous level) that repeats until no more children are found.

2. Why It Matters

  • Arbitrary depth traversal: Walk any tree structure regardless of depth — no need to know in advance how many levels exist.
  • Single query solution: Replaces application-level loops or multiple round-trips to the database.
  • Real-world applications: Org charts, file systems, category trees, dependency graphs, route finding, and threaded discussions.
  • Interview favorite: "Write a query to display the full management chain from CEO to a specific employee" is a classic recursive CTE question.

3. Real-World Analogy

Imagine tracing your family tree. You start with yourself (base case), then find your parents (recursive step), then find your parents' parents, and so on until there are no more ancestors to find. Each generation is one iteration of the recursion. A recursive CTE does exactly this with data — it keeps walking up (or down) the tree until it hits a dead end.

4. How It Works

  • Three parts: (1) Base case — the starting point (root nodes). (2) UNION ALL — combines base and recursive results. (3) Recursive member — joins the CTE to itself to find the next level.
  • Iteration: PostgreSQL evaluates the base case, then repeatedly runs the recursive member against the previous iteration's results until no new rows are produced.
  • Cycle protection: Add a CYCLE clause or track visited nodes to prevent infinite loops in graphs with cycles.
  • Depth tracking: Add a level column (incremented in the recursive member) to show tree depth and enable indentation.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Full org chart with indentation:

Category tree (bottom-up):

8. Common Mistakes

Infinite recursion from cycles

If your data has cycles (A→B→C→A), the recursion never terminates. Use PostgreSQL's CYCLE id SET is_cycle clause or track visited IDs in an array to detect and break cycles.

UNION instead of UNION ALL

UNION removes duplicates across iterations, which is slower and can cause unexpected behavior. Always use UNION ALL — the recursive step naturally avoids duplicates when traversing a proper tree.

No depth limit

Without a WHERE level < N guard, a corrupted tree with cycles can run forever. Always add a safety limit in development and testing.

Interview Insight

"How do you prevent infinite loops in a recursive CTE?" — Use the CYCLE clause (PostgreSQL 14+), track visited nodes in an array, or add a WHERE level < max_depth guard. Mention that proper tree data shouldn't have cycles, but defensive coding is essential.

9. Quick Quiz

Q1: What's the difference between UNION and UNION ALL in a recursive CTE?

Answer: UNION ALL appends rows without deduplication (standard for recursive CTEs). UNION deduplicates across iterations, which is slower and can mask bugs. Always prefer UNION ALL for recursive CTEs.

Q2: Can a recursive CTE have multiple base cases?

Answer: Yes. The base case can return multiple rows (e.g., all root nodes). You can also UNION multiple base queries. For example, starting from multiple leaf categories simultaneously.

10. Scenario-Based Challenge

Challenge: Bill of Materials Explosion

A product has components, and some components have sub-components (recursive). Given a parts(part_id, name, parent_part_id, quantity, unit_cost) table:

  1. Write a recursive CTE to explode the full bill of materials for a specific product.
  2. Calculate the total cost by multiplying quantities through each level.
  3. Add a level column and indent the output to show the hierarchy visually.

Advanced: detect cycles where part A contains part B which contains part A.

11. Debugging Exercise

Find the bug in this recursive CTE:

Issues:

  1. UNION instead of UNION ALL: UNION causes deduplication overhead. Use UNION ALL for recursive CTEs.
  2. Wrong join direction: e.id = c.manager_id finds people whose ID matches the current row's manager — it goes UP the tree (to managers). If the goal is to show the employee's subordinates, use e.manager_id = c.id.
  3. No depth tracking: Add a level column to visualize the tree depth and enable safety limits.

12. Interview Questions

Q1: Write a recursive CTE to generate numbers 1 through 10.

A: WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM nums WHERE n < 10) SELECT * FROM nums. This pattern is the simplest recursive CTE and demonstrates the base + recursive step structure.

Q2: How does PostgreSQL know when to stop recursing?

A: When the recursive member produces zero new rows, the recursion terminates. If the data has cycles, it may never stop — hence the need for CYCLE clauses or depth limits.

Q3: Can you use recursive CTEs for graph traversal (not just trees)?

A: Yes — shortest path finding, connected components, and dependency resolution all use recursive CTEs. But graphs can have cycles, so you must track visited nodes (e.g., using arrays) to prevent infinite loops.

13. Production Considerations

  • Always add depth limits in development: WHERE level < 100 prevents runaway recursion from corrupted data.
  • Use CYCLE clause (PostgreSQL 14+): CYCLE id SET is_cycle TO '1' DEFAULT '0' USING path automatically detects and breaks cycles.
  • Index parent columns: The recursive step joins on parent_id = id. Index both columns for fast lookups at each iteration.
  • Materialized path for display: Build a path column (concatenated names) during recursion for readable tree display and correct sorting.
  • Consider ltree extension: For static hierarchies, PostgreSQL's ltree extension stores the full path as a single indexed column — faster than recursive CTEs for read-heavy workloads.
  • Set statement_timeout: In production, configure a timeout to catch unexpected deep recursions: SET statement_timeout = '30s'.