PostgreSQL CTEs and Recursive Queries

Nearly every backend developer has wrestled with hierarchical data. Finding all of an employee's reports, including their reports' reports; rendering multilevel menus; retrieving the entire reply tree for a comment; or analyzing the transitive chain of package dependencies: these problems share one defining trait: the depth is unknown and may span N levels.

Your first instinct may be JOIN. But JOIN has a fixed depth: querying three levels requires three JOINs, and ten levels will drive you mad. What about querying the database in a loop at the application layer? That is the classic N+1 query, with predictably disastrous performance.

PostgreSQL's answer is the recursive CTE: one SQL statement can traverse a tree or graph of any depth. This article explains CTE use cases, recursive syntax, practical pitfalls, and version-related changes.

1. What Is a CTE? A Name for a Subquery

A CTE, or Common Table Expression, is the WITH clause. Its first purpose is not recursion but readability: name a subquery and reference it repeatedly from the main query, much like a local variable in SQL.

Consider a non-recursive example that finds the highest-paid employee in each department:

WITH dept_max AS (
  SELECT department_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.department_id, e.salary
FROM employees e
JOIN dept_max d
  ON e.department_id = d.department_id
 AND e.salary = d.max_salary;

dept_max is the named subquery. It offers three benefits: SQL is no longer a mass of nested expressions, the result can be referenced in multiple places, and the intermediate result has a meaningful name. As a rule of thumb, use a CTE when subqueries are nested more than two levels deep or when the same subquery must be referenced more than once. It does not necessarily change the execution plan; it is simply a win for syntax and maintainability.

2. Recursive CTEs: The Two-Part Anchor + Recursive Pattern

A recursive CTE is an advanced CTE designed for self-referential hierarchical, tree, and graph data. Its syntax follows this template:

WITH RECURSIVE name AS (
  -- anchor: base query, the starting point for recursion
  SELECT ...
  UNION ALL
  -- recursive term: self-reference, expanded iteratively
  SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

It has two key parts:

• anchor (base case): The initial query, which does not reference itself. Recursion starts here, just like a function's base case.
• recursive term: FROM name references the CTE itself, using the previous iteration's output as input to generate the next level. UNION ALL combines the result from every iteration.

The mechanism is iteration, not actual recursive function calls: run the anchor to produce level 0, feed level 0 into the recursive term to produce level 1, feed that result back in to produce level 2, and continue until an iteration produces no new rows. The iteration then stops, and all levels are combined with UNION ALL and returned. A recursive CTE therefore will not recurse forever, provided the data has a terminating endpoint; it converges on its own.

3. Practical Example 1: Find Every Level of a Person's Reports in an Org Chart

This is the classic use case. The table is self-referential, with manager_id pointing to an employee's manager:

CREATE TABLE employees (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

Task: given a manager ID, find everyone beneath that manager, including direct reports and all of their descendants at any depth, and label each level.

WITH RECURSIVE subordinates AS (
  -- anchor: start with this manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- recursive: treat the previous iteration's people as managers and find their direct reports
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth, id;

The depth column identifies each person's level and can be used directly when the frontend renders the org tree. Want to reverse direction and find all ancestors? Change the anchor to the lowest-level employee and change the recursive term's JOIN condition to JOIN subordinates s ON e.id = s.manager_id. The JOIN condition determines the direction, which is the easiest part of a recursive CTE to get turned around.

4. Practical Example 2: Expanding a Comment Tree by Level

Comments under a post form a tree: post → reply → reply to a reply. In the table, parent_id points to the parent comment:

CREATE TABLE comments (
  id        SERIAL PRIMARY KEY,
  post_id   INTEGER NOT NULL,
  parent_id INTEGER REFERENCES comments(id),
  author    TEXT,
  content   TEXT
);

Retrieve the entire comment tree for a post and order it so that every parent comment is immediately followed by all of its descendants, with comments at the same level ordered by ID:

WITH RECURSIVE comment_tree AS (
  SELECT id, post_id, parent_id, author, content,
         0      AS depth,
         ARRAY[id] AS path
  FROM comments
  WHERE post_id = 42 AND parent_id IS NULL   -- root comments

  UNION ALL

  SELECT c.id, c.post_id, c.parent_id, c.author, c.content,
         ct.depth + 1,
         ct.path || c.id                       -- append to the path
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY path;

Here is a useful technique worth remembering: ARRAY[id] maintains the path from the root to the current node, while path || c.id appends the current ID to the parent's path. A single final ORDER BY path then produces an order where every parent is followed by all of its descendants and siblings are sorted by ID, making indented frontend rendering almost free. Without a path column, ordering across levels takes considerably more work.

5. Other Use Cases: Category Trees and Dependency Chains

The pattern remains nearly identical; changing the table name gives you a new feature:

• Multilevel category trees: For product categories in category(parent_id), select top-level categories with parent_id IS NULL in the anchor, expand downward through parent_id in the recursive term, and render the result as a menu.
• Dependency chains: For packages in package(depends_on), A depends on B and B depends on C. Start with A in the anchor, follow depends_on to the next hop in the recursive term, and stop when there are no more dependencies. Again, use a path column to record the complete chain and make circular dependencies immediately visible.

Whenever data points back to itself, a recursive CTE is almost always an option.

6. MATERIALIZED and the Inlining Change in PG 12+

This is a version boundary every CTE user must know. Inlining was introduced in PostgreSQL 12 and became stable behavior by 14+, which the community often refers to as the PG 14+ default behavior.

• Before PG 12: CTEs were materialized (MATERIALIZED). The entire CTE ran once, its result was stored as a temporary table, and the outer query then read it. This acted as an optimization barrier, preventing outer WHERE conditions from being pushed into the CTE.
• Starting with PG 12: CTEs are inlined by default. The optimizer expands them like views, allowing predicate pushdown, query merging, and better plan selection. This is faster in most cases.

Sometimes, however, you specifically want materialization, such as when an expensive CTE is referenced multiple times and should be computed only once. In that case, add the keyword explicitly:

WITH expensive AS MATERIALIZED (
  SELECT ... -- complex aggregation that should run only once
),
cheap AS NOT MATERIALIZED (
  SELECT ... -- simple filtering that benefits from inlining
)
SELECT ... FROM expensive e1, expensive e2 WHERE ...;

MATERIALIZED forces materialization, while NOT MATERIALIZED forces inlining, and the optimizer follows your instruction. If a CTE's query plan regresses after upgrading to 12+, first consider whether you should specify MATERIALIZED manually.

7. Three Pitfalls: Infinite Loops, Performance, and Depth

A recursive CTE does not detect cycles automatically. If your data contains a cycle, such as A reporting to B while B reports to A, the recursion will keep generating new rows until it exhausts resources. Safeguards include:

1. A path column plus a cycle guard: Use the path array from the comment-tree example and add WHERE NOT c.id = ANY(ct.path) to the recursive term, or negate path && ARRAY[c.id]. Nodes already visited are not admitted again. This is the most practical cycle-prevention pattern in production.
2. Add LIMIT: During debugging, use SELECT * FROM ... LIMIT 1000 to contain the problem first.
3. Guarantee acyclic data at the data layer: Org charts and category trees should already be DAGs or trees by business definition, so enforcing the rule at the source is the cleanest solution.

Set realistic performance expectations: recursive CTEs are not a silver bullet. When the hierarchy is extremely deep and the table is enormous, every iteration requires another scan, and the total cost may exceed that of batch queries at the application layer followed by constructing the tree in memory. The decision rule is simple: inspect actual row counts and execution time with EXPLAIN ANALYZE instead of relying on intuition.

8. CTEs vs. Subqueries vs. Window Functions

These three are often conflated, but they serve very different roles:

• CTEs: Best for readability, reuse, and recursion. They are the first choice for decomposing complex queries and traversing hierarchies.
• Subqueries: For simple one-off filtering or aggregation, such as WHERE id IN (...), a CTE is unnecessary and a direct subquery is cleaner.
• Window functions: Calculate rankings, running totals, and per-group Top-N values across rows with expressions such as ROW_NUMBER() OVER (...). They solve relationships between rows, not hierarchies.

In short: use a CTE for recursion or decomposing a complex query, a subquery for simple filtering, and a window function for cross-row aggregation. Do not try to use a window function for recursion, and do not force a one-line subquery into a WITH clause.

Summary

• A CTE is essentially a named, reusable subquery. Non-recursive CTEs improve readability, while recursive CTEs traverse hierarchies.
• A recursive CTE = anchor + UNION ALL + a self-referencing recursive term. Its underlying mechanism is iterative convergence, not stack-based recursion.
• The JOIN condition determines direction. Changing the JOIN switches between finding descendants downward and finding ancestors upward.
• Maintain a path array column. It both prevents cycles and produces hierarchical ordering in a single operation, making it almost standard in production.
• Inlining is the default starting with PG 12 and is stable default behavior in 14+. Add MATERIALIZED to force materialization, and investigate this first when an upgrade degrades the execution plan.

Memorable line: JOIN is a fixed-depth drill, while a recursive CTE can keep drilling downward on its own: give SQL a little memory, and it can traverse the entire tree.