Recursive CTEs: when to use them and how to keep them sane

WITH RECURSIVE has two parts: an anchor query and a recursive query that references the CTE itself. Most bugs come from forgetting one of three things.

1. Always pin a termination condition

If your recursive step does not eventually return zero rows, the query never ends. Filter on a depth counter or a cycle-detection set.

2. Cap depth defensively

Even valid hierarchies can have bad data. WHERE depth < 100 is cheap insurance against a single corrupted parent pointer.

3. ORDER inside the recursion is not respected

ORDER BY only applies to the final SELECT. If you need ordered traversal, carry a path string across iterations.

WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
  WHERE o.depth < 50
)
SELECT * FROM org;

See the org-chart example for how lineage tools render the recursive stage as a self-referencing node.