Amey-Thakur Recursive queries
A recursive CTE repeats a step until it produces nothing new, which is how you follow a parent chain or expand a dependency graph in one query. The two failure modes are a recursion that never terminates and one that revisits the same node forever.
Method
- Write the anchor and the step separately. The anchor selects the starting rows and the recursive part joins the CTE back to the table. Getting the anchor wrong produces an empty or enormous result immediately.
- Track depth and cap it. Carrying a level column lets you both report depth and stop at a maximum, which protects against unexpectedly deep or malformed data.
- Detect cycles explicitly. Accumulate the visited path and exclude nodes already in it. Real hierarchies contain accidental cycles more often than schemas admit.
- Return the path when it is the point. Building a breadcrumb of ancestors as you descend is usually the reason for the query and costs almost nothing to carry.
- Choose direction by the question. Descendants start from a node and follow children; ancestors start from a node and follow parents. The shape is the same and the join direction reverses.
- Consider a materialised path or closure table for hot reads. Recursion is elegant and not always fast, and denormalising the hierarchy is a legitimate trade when reads dominate (see database-normalization).
Boundaries
- Recursive CTEs suit modest hierarchies; large graph traversal belongs in a graph database or an application algorithm.
- Performance degrades quickly with breadth, since each level joins against everything found so far.
- Syntax and the RECURSIVE keyword requirement differ between engines.