CTEs and Recursive Queries in PostgreSQL

A common table expression is a named subquery you define with WITH and use in the main query. On the surface it's a readability feature, a way to name a step so a 60-line query reads like a sequence of small ideas instead of a wall of nested parentheses. Underneath, CTEs also unlock recursion, which is how you query tree and graph shaped data (org charts, category trees, threaded comments) that plain SQL can't reach.

This is part 8 and the last article of the SQL module in the PostgreSQL Masterclass, following window functions.

The basic CTE: naming a step

WITH lets you define a subquery once and refer to it by name:

WITH active_customers AS (
  SELECT id, name
  FROM customers
  WHERE last_seen_at > now() - interval '30 days'
)
SELECT ac.name, count(o.id) AS recent_orders
FROM active_customers ac
LEFT JOIN orders o ON o.customer_id = ac.id
GROUP BY ac.name;

The query reads top to bottom: first define what an active customer is, then count their orders. Compared to inlining that filter as a nested subquery, the intent is clearer and the name documents itself. You can chain several CTEs, each referring to earlier ones, which is how a genuinely complex report stays readable.

The performance nuance you should know

For years, PostgreSQL treated every CTE as an optimization fence: it computed the CTE fully, materialized the result, then ran the outer query against it. That could be slower than an equivalent subquery, because the planner couldn't push filters down into the CTE.

Postgres 12 changed the default. Now a CTE that's referenced once and has no side effects gets inlined, so it optimizes like a subquery. You can force the old behavior when you want it, which is occasionally useful:

WITH expensive AS MATERIALIZED (
  SELECT ... -- computed once, reused, not re-run per reference
)
SELECT ... FROM expensive a JOIN expensive b ON ...;

Use MATERIALIZED when a CTE is referenced multiple times and recomputing it would be wasteful, or NOT MATERIALIZED to force inlining. For most single-use CTEs on Postgres 12 and later, the default does the right thing and you don't have to think about it.

Recursive CTEs: querying trees

This is the part you can't do any other way. A recursive CTE walks hierarchical data by referring to itself. The shape is always the same: a base case, UNION ALL, and a recursive term that joins back to the CTE.

Take an employees table where each row points at its manager:

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       text NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

To list everyone under a given manager, at any depth:

WITH RECURSIVE reports AS (
  -- base case: the starting manager
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- recursive term: people who report to someone already in `reports`
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT id, name, depth FROM reports ORDER BY depth, id;

It works in rounds. The base case seeds the result with employee 1. Each round then finds employees whose manager is already in reports and adds them, incrementing depth. When a round adds no new rows, recursion stops. The depth column falls out naturally and tells you how many levels down each person sits.

A guard against infinite loops

Recursive queries can loop forever if the data has a cycle, like employee A managing B while B manages A. Real hierarchies shouldn't, but data isn't always clean, so it's worth protecting against. Postgres 14 added a CYCLE clause that detects revisited rows and stops:

WITH RECURSIVE reports AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e JOIN reports r ON e.manager_id = r.id
) CYCLE id SET is_cycle USING path
SELECT id, name FROM reports WHERE NOT is_cycle;

On older versions you track the visited path yourself in an array and filter out any row whose id already appears in it. Either way, a recursive query against untrusted hierarchy data should have a cycle guard, or one bad row takes the query down.

When to use which

A few rules of thumb after all this:

• Reach for a plain CTE when naming a step makes a query readable. That's most of the time, and on Postgres 12 and up there's no performance penalty for single use.
• Use MATERIALIZED deliberately when you reference the same expensive CTE several times.
• Use RECURSIVE for anything tree or graph shaped: hierarchies, bill-of-materials, threaded comments, dependency chains.
• Guard recursive queries with a cycle check whenever the data could contain a loop.

That closes the SQL module. You can now model data with the right types and constraints, query it with joins and aggregates, run analytics with window functions, and walk hierarchies with recursion. That's the full toolkit for asking questions of your data correctly.

The next module shifts from correctness to speed. We start with how PostgreSQL indexes actually work, because every "why is this query slow" conversation eventually lands on indexing.