PostgreSQL recursive CTEs let a query repeatedly use its own output to traverse a hierarchy or graph. They are useful for folder trees, reporting structures, dependency relationships, and other connected data. The difficult part is not writing WITH RECURSIVE; it is deciding what one result row means and why the traversal will stop.
A dependable query has an explicit starting set, a controlled recursive step, and a documented policy for duplicates, cycles, and ordering. Those choices determine whether the output is meaningful and whether the database can produce it within an acceptable work budget.
Define nodes, edges, and roots
Begin with the data model. A node identifies an entity; an edge describes a relationship. A single-parent tree and a general graph have different traversal behavior, especially when several paths can reach the same node.
Specify the starting root or roots using parameters and the application’s normal access checks. A client-supplied identifier should not allow traversal into another tenant’s hierarchy merely because the SQL is syntactically valid.
Decide whether the root itself appears in the result. Also define how missing roots, disconnected nodes, and multiple roots are reported. A result that silently omits an invalid starting point can look like a legitimate empty hierarchy.
Separate the anchor from the recursive step
A recursive CTE contains a nonrecursive term and a recursive term combined with UNION or UNION ALL. PostgreSQL evaluates the anchor and then iteratively processes working rows until no more rows are produced.
This bounded example demonstrates the structure without requiring a table:
WITH RECURSIVE numbers(n) AS (
VALUES (1)
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n FROM numbers ORDER BY n;The stopping condition is part of the recursive term. The outer ordering defines presentation. A real graph query needs similarly explicit decisions, even if its stopping condition depends on edges rather than a numeric bound.
Decide whether rows represent nodes or paths
If two routes reach the same node, should the query return that node once or return each route? Both can be valid, but they support different questions.
An access review might need unique reachable entities. A dependency analysis might need every path that explains how one component depends on another. Counting path rows as though they were unique entities can inflate totals.
Carry only the columns needed for the intended meaning. Adding depth or a path array changes row identity and can change how duplicate elimination behaves. Review that effect before treating UNION as a universal deduplication solution.
Use UNION deliberately
UNION discards duplicate result rows, while UNION ALL retains them. Duplicate elimination can help some traversals terminate, but it compares complete rows rather than a business concept of node identity.
If the same node reappears with an increasing depth value, the row is different on every visit. Switching from UNION ALL to UNION will not automatically stop that cycle.
Choose a cycle policy based on the key that identifies a visited node or state. When identity includes a tenant or another dimension, tracking only a local numeric ID can incorrectly treat distinct nodes as the same one.
Detect cycles explicitly
For graph data that can contain cycles, use supported cycle detection or an explicit visited-path representation. PostgreSQL’s documented CYCLE clause can mark repeated keys and maintain a path column.
Decide whether a cycle-marked row should remain visible as diagnostic evidence or be filtered from the final business result. Do not hide the fact that the source data contains a cycle if that violates the model.
A depth ceiling can provide an additional work boundary, but it is not equivalent to cycle detection. It may truncate a valid deep hierarchy. Report truncation distinctly from successful complete traversal.
Order results instead of assuming visit order
Recursive evaluation order is not a presentation contract. Use an explicit final ORDER BY based on the output the consumer needs.
The supported SEARCH syntax can compute an ordering column for depth-first or breadth-first presentation. It does not turn undocumented physical evaluation order into an application guarantee.
Add stable tie rules where several nodes share an ordering value. If the result is paginated or used in a reproducible report, nondeterministic ties can change which rows appear on each page even though the graph itself has not changed.
Keep tenant and authorization rules in every step
Filtering the anchor is not enough if the recursive join can cross into edges outside the approved scope. Apply the appropriate tenant or access conditions consistently throughout traversal.
Test adversarial relationships, such as an edge referencing a node belonging to another tenant. The application should not depend solely on a convention that imported data always uses correct relationships.
Row security and constraints can support the design, but verify the query under the actual application role. An administrator’s successful test is not evidence that the deployed access boundary behaves identically.
Bound work beyond the row count
A traversal can consume substantial resources even when its final result is small. High branching, long paths, repeated joins, and growing path arrays all affect work and memory.
Use appropriate indexes for edge lookups and examine the plan on representative graphs. Test narrow deep data and wide shallow data separately; a pleasant demonstration tree rarely represents the worst production case.
Apply suitable statement timeouts and operational monitoring. An outer LIMIT can be helpful during controlled experimentation, but it is not a dependable production termination mechanism when sorting or other operations require the full recursive result.
Review aggregates and duplicate influence
When calculating quantities across a graph, decide whether repeated reachability should multiply a contribution. A bill-of-materials calculation may intentionally aggregate along multiple paths, whereas an inventory listing may not.
Validate the arithmetic with a small hand-checkable fixture containing a diamond shape: one root, two intermediate nodes, and a shared descendant. That reveals whether the query counts shared descendants once or once per route.
Also test a self-loop, a multi-node cycle, a missing child, and a disconnected component. These fixtures expose semantic mistakes more reliably than a large result whose totals are hard to inspect.
Deliver an honest result contract
Document whether output includes roots, unique nodes, paths, cycle diagnostics, and truncation indicators. Explain the ordering and the scope the query traverses.
If traversal stops because of an operational limit, do not label the partial result complete. Return a controlled failure or an explicitly partial response according to the application contract.
Recursive CTEs are most useful when their graph semantics are visible. Clear identity and termination rules make the query easier to review, optimize, and trust.
Frequently asked questions
Does UNION always prevent infinite recursion?
No. It removes duplicate complete rows. Changing depth or path fields can make every repeated visit distinct.
Does SEARCH control physical evaluation order?
Use it to compute presentation ordering information and order the final result explicitly, rather than relying on execution order.
Is a depth cap sufficient for correctness?
It bounds one dimension of traversal but can truncate legitimate data. Treat it as a documented limit, not proof that all relationships were explored.
See the PostgreSQL WITH query documentation for recursive evaluation, search ordering, and cycle syntax.
For a complementary workflow, read PostgreSQL EXPLAIN ANALYZE: 7 Checks for Safer Query Tuning.
