Recursive CTE in PostgreSQL

A recursive CTE uses the WITH RECURSIVE syntax to define a query that references itself. It is the standard SQL approach for traversing hierarchical and graph-structured data — org charts, category trees, bill-of-materials, file systems — without requiring procedural code or multiple round trips to the database.

Syntax

WITH RECURSIVE cte_name (col1, col2, ...) AS (
  -- Anchor member: runs once, produces the base result set
  SELECT select_list FROM table_name
  WHERE base_condition

  UNION [ALL]

  -- Recursive member: references cte_name, runs until empty
  SELECT select_list FROM table_name
  INNER JOIN cte_name ON cte_name.id = table_name.parent_id
)
SELECT * FROM cte_name;

PostgreSQL executes a recursive CTE as follows:

1. Run the anchor member once to produce the initial result set R0.
2. Run the recursive member using the previous result as input, producing R1.
3. Repeat step 2 until the recursive member returns an empty set.
4. Return the final result — the UNION (or UNION ALL) of R0, R1, R2, …

Practical Example

Create a product category tree where each category may have a parent:

CREATE TABLE categories (
  category_id   SERIAL PRIMARY KEY,
  name          VARCHAR NOT NULL,
  parent_id     INT REFERENCES categories (category_id)
);

INSERT INTO categories (category_id, name, parent_id) VALUES
  (1, 'Electronics',    NULL),
  (2, 'Computers',      1),
  (3, 'Laptops',        2),
  (4, 'Gaming Laptops', 3),
  (5, 'Tablets',        2),
  (6, 'Audio',          1),
  (7, 'Headphones',     6),
  (8, 'Earbuds',        6);

Find all descendants of the “Computers” category (category_id = 2):

WITH RECURSIVE category_tree AS (
  -- Anchor: start with Computers
  SELECT category_id, name, parent_id, 1 AS depth
  FROM categories
  WHERE category_id = 2

  UNION ALL

  -- Recursive: find children of every node in the current result
  SELECT c.category_id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON ct.category_id = c.parent_id
)
SELECT * FROM category_tree ORDER BY depth, name;

Result:

 category_id |     name      | parent_id | depth
-------------+---------------+-----------+-------
           2 | Computers     |         1 |     1
           3 | Laptops       |         2 |     2
           5 | Tablets       |         2 |     2
           4 | Gaming Laptops|         3 |     3

Building a breadcrumb path

Accumulate the path as an array to build breadcrumbs or full slugs:

WITH RECURSIVE breadcrumb AS (
  SELECT category_id, name, parent_id,
         ARRAY[name] AS path
  FROM categories
  WHERE parent_id IS NULL   -- start from root

  UNION ALL

  SELECT c.category_id, c.name, c.parent_id,
         b.path || c.name
  FROM categories c
  INNER JOIN breadcrumb b ON b.category_id = c.parent_id
)
SELECT
  category_id,
  name,
  array_to_string(path, ' > ') AS full_path
FROM breadcrumb
ORDER BY full_path;

Result:

 category_id |     name       |                full_path
-------------+----------------+------------------------------------------
           1 | Electronics    | Electronics
           2 | Computers      | Electronics > Computers
           6 | Audio          | Electronics > Audio
           3 | Laptops        | Electronics > Computers > Laptops
           5 | Tablets        | Electronics > Computers > Tablets
           7 | Headphones     | Electronics > Audio > Headphones
           8 | Earbuds        | Electronics > Audio > Earbuds
           4 | Gaming Laptops | Electronics > Computers > Laptops > Gaming Laptops

Guarding against cycles in graph data

For general graphs (not pure trees) where cycles are possible, use UNION instead of UNION ALL and track visited nodes:

WITH RECURSIVE reachable AS (
  SELECT node_id, ARRAY[node_id] AS visited
  FROM graph_nodes
  WHERE node_id = 1

  UNION

  SELECT gn.node_id, r.visited || gn.node_id
  FROM graph_edges ge
  INNER JOIN graph_nodes gn ON gn.node_id = ge.target_id
  INNER JOIN reachable r    ON r.node_id  = ge.source_id
  WHERE NOT gn.node_id = ANY(r.visited)   -- skip already-visited nodes
)
SELECT node_id FROM reachable;

UNION automatically deduplicates rows at each iteration, which helps prevent cycles. The visited array check adds an explicit guard that prevents revisiting any node.

UNION vs UNION ALL in Recursive CTEs

For tree structures where cycles are impossible (true parent-child hierarchies), UNION ALL is faster and correct. For graphs, use UNION or an explicit visited-node check.

Testing with Vela

Recursive CTEs that traverse large hierarchies can run for a long time and consume significant memory. Use Vela’s database branching to run EXPLAIN ANALYZE on your recursive CTE against a production-data branch before deploying it. You can safely test depth limits, verify path-building logic, and tune termination conditions on a clone of real data without any risk to the live database.

Production Tips

• Always add a termination guard to recursive CTEs that traverse potentially unbounded data. A depth counter (WHERE depth < 50) or a visited-node array prevents runaway recursion.
• PostgreSQL enforces max_recursive_depth (default 1000) as a hard safety limit. You can lower it with SET max_recursive_depth = 100 in a session if you want an earlier error for pathological queries.
• Use UNION ALL for tree structures where cycles cannot exist — it is faster because it skips per-iteration deduplication.
• Use UNION or a visited-node array for general graph traversal where cycles are possible.
• The EXPLAIN ANALYZE plan for a recursive CTE shows the total number of recursive iterations as “Cycles” in the WorkTable Scan node. Use this to spot unexpectedly deep recursion during development.
• Recursive CTEs that build path arrays consume memory proportional to the path length times the number of nodes. For very deep trees, consider a stored procedure that iterates in application code and materializes the path in a temporary table.