How do CTEs and recursive queries work in PostgreSQL?

Question

Accepted Answer

A **CTE** (Common Table Expression), defined with **`WITH`**, is a named temporary result set that makes complex queries readable. PostgreSQL fully supports CTEs, including **recursive** CTEs (`WITH RECURSIVE`) for traversing hierarchical/tree data.

## Basic CTEs — readable query building

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEs break a complex query into named, readable steps (chained, each referencing prior ones) — far clearer than nested subqueries.

## Recursive CTEs — traverse hierarchies

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

A **recursive CTE** has a base case (the starting rows) and a recursive step (`UNION ALL` joining back to the CTE itself), iterating until no new rows — perfect for **trees and hierarchies** (org charts, category trees, bill-of-materials, graph paths).

## A note on CTE materialization (Postgres-specific)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Before Postgres 12, CTEs were always an "optimization fence" (materialized); now they can be inlined for better performance — `MATERIALIZED`/`NOT MATERIALIZED` controls this.

## Why it matters

CTEs and recursive queries are valuable PostgreSQL features for **readability and hierarchical data**, so understanding them is useful for writing maintainable complex queries and handling tree-structured data.

The everyday benefit is **readability**: complex queries with multiple subquery levels become hard to maintain, while CTEs let you decompose them into named, sequential logical steps (read top-to-bottom), dramatically improving clarity — making CTEs an important tool for writing maintainable SQL.

The standout capability is **recursive CTEs** (`WITH RECURSIVE`), which enable querying **hierarchical and tree-structured data** (organizational charts, category trees, bill-of-materials, threaded comments, graph traversal) — a class of queries otherwise very difficult in SQL, making recursive CTEs the standard solution for hierarchical traversal.

Understanding the recursive CTE structure (base case + recursive step joining back to itself) is key knowledge for this common need.

A useful Postgres-specific detail is **CTE materialization** behavior (Postgres 12+ can inline CTEs for better performance, controllable with `MATERIALIZED`/`NOT MATERIALIZED`) — relevant for optimization.

Since complex queries are common (and readability matters for maintenance), and since hierarchical data (which recursive CTEs uniquely handle well) appears frequently (org structures, nested categories), understanding CTEs and recursive queries is valuable, practically-relevant PostgreSQL knowledge that improves query maintainability and unlocks hierarchical querying — a frequently-relevant capability for clean, sophisticated SQL and a common topic, especially the recursive form for the recurring need to query tree-structured data.