You need every descendant of a category, or the full management chain above an employee, or all dependencies of a package. The obvious approach queries one level, then queries the next using those results, and repeats.
That is N round trips for a depth of N, and round trips dominate. A recursive common table expression does the whole traversal inside the database and returns the result once.
What changed in 2026
- Support became universal. Recursive CTEs are available across mainstream databases, so portability concerns largely disappeared.
- Cycle safety got better tooling. Some engines added explicit cycle-detection syntax rather than requiring manual path tracking.
- Graph databases stopped being the default answer. Teams recognised that moderate hierarchical traversal in a relational database is usually adequate.
- Depth limits became standard practice. Capping recursion depth moved from a nice-to-have to something reviewers ask about.
The structure
A recursive CTE has two parts joined by a union.
The anchor selects the starting rows — the root of the tree, or the node you are querying from.
The recursive term references the CTE itself, selecting rows that connect to what the previous iteration produced. It runs repeatedly until it returns no new rows.
That final condition is the whole termination story: the query stops when an iteration adds nothing. If your data contains a cycle — a category that is somehow its own ancestor, a dependency loop — every iteration keeps finding rows and the query never stops.
Guaranteeing termination
Two safeguards, and you want at least the first.
A depth counter. Add a column starting at zero in the anchor and incrementing in the recursive term, with a condition capping it. Every real hierarchy has a sensible maximum depth, and a cap well above it costs nothing and turns an infinite loop into a bounded query.
Path tracking. Accumulate the visited node identifiers in an array and exclude rows already present. This detects genuine cycles rather than merely bounding them, at the cost of carrying the array through every row.
| Safeguard |
Prevents |
Cost |
| Depth cap |
Infinite loops |
Negligible |
| Path array |
Cycles specifically |
Array per row |
| Engine cycle syntax |
Cycles |
Varies; check support |
| Nothing |
— |
Query runs forever |
Use the depth cap always. Add path tracking when cycles are genuinely possible in your data rather than merely conceivable.
Where it costs
A recursive CTE is a loop inside the database, and each iteration is a join against the working set. Performance depends almost entirely on whether that join is indexed.
Traversing a parent-child relationship requires an index on the parent column, or every iteration scans the table. That is the single most common cause of a recursive query being unexpectedly slow, and it is easy to miss because the query looks like one statement rather than a loop — see database indexing explained.
Breadth matters as much as depth. A tree with a high branching factor produces a working set that grows quickly, and a traversal from the root of a large hierarchy can return an enormous number of rows. Filtering inside the recursive term, rather than after the CTE, prunes branches before they expand.
For very deep or very broad traversals run frequently, materialising the closure — precomputing ancestor-descendant pairs in a table — trades write cost for read speed. Worth it only when the read pattern justifies it.
Common mistakes
- No depth cap. A single cyclic row hangs the query.
- Missing index on the join column. Each iteration scans.
- Filtering after the CTE rather than inside it. Expands branches you then discard.
- Using it for fixed shallow depth. Two joins are clearer.
- Selecting more columns than needed. Carried through every iteration.
- Assuming a cycle is impossible. Data quality issues create them.
- Not testing on realistic depth and breadth. Small test data hides the cost.
FAQ
How deep can recursion go?
Engines have configurable limits, and hitting a limit is a symptom rather than a constraint to raise. If a legitimate traversal is that deep, reconsider the data model.
Are these portable across databases?
Broadly yes for the standard form. Cycle-detection syntax and recursion limits vary, so check those specifically.
Should I use a graph database instead?
For occasional hierarchical traversal, no — a recursive CTE is adequate and avoids another system. For traversal-heavy workloads with complex path queries, a purpose-built store earns its place.
How do I make one faster?
Index the join column, filter inside the recursive term, select fewer columns, and consider materialising the closure if the same traversal runs constantly — see what is a materialized view.
Where to go next
For the indexes recursive traversal depends on, read database indexing explained. For precomputing expensive traversals, what is a materialized view, and for reading the plan, query planners.