PostgreSQL에서 CTE와 재귀 쿼리는 어떻게 동작하나요?

Question

Accepted Answer

**CTE**(Common Table Expression, 공통 테이블 표현식)는 **`WITH`**로 정의되며, 복잡한 쿼리를 읽기 쉽게 만드는 명명된 임시 결과 집합입니다. PostgreSQL은 계층/트리 데이터를 순회하기 위한 **재귀** CTE(`WITH RECURSIVE`)를 포함하여 CTE를 완전히 지원합니다.

## 기본 CTE — 읽기 쉬운 쿼리 구성

```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;   -- 명명된 단계를 깔끔하게 구성
```

CTE는 복잡한 쿼리를 명명되고 읽기 쉬운 단계(연쇄되며 각각 이전 것을 참조)로 나눕니다 — 중첩 서브쿼리보다 훨씬 명확합니다.

## 재귀 CTE — 계층 순회

```sql
-- 직원 1 아래의 모든 부하 직원 찾기 (조직도 계층)
WITH RECURSIVE subordinates 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 subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- 직원 1 아래의 전체 트리
```

**재귀 CTE**는 기본 케이스(시작 행)와 재귀 단계(`UNION ALL`로 CTE 자체에 다시 조인)를 가지며, 새 행이 없을 때까지 반복합니다 — **트리와 계층**(조직도, 카테고리 트리, 자재 명세서, 그래프 경로)에 완벽합니다.

## CTE materialization에 대한 참고 (Postgres 특화)

```sql
-- Postgres 12+는 기본적으로 CTE를 INLINE(최적화)할 수 있음; 필요 시 동작 강제:
WITH x AS MATERIALIZED (...)      -- materialization 강제 (한 번 계산, 저장)
WITH x AS NOT MATERIALIZED (...)  -- inline 강제 (메인 쿼리로 최적화)
```

Postgres 12 이전에는 CTE가 항상 "최적화 펜스"(materialized)였습니다; 이제 더 나은 성능을 위해 inline될 수 있습니다 — `MATERIALIZED`/`NOT MATERIALIZED`가 이를 제어합니다.

## 왜 중요한가

CTE와 재귀 쿼리는 **가독성과 계층 데이터**를 위한 가치 있는 PostgreSQL 기능이므로, 이를 이해하는 것은 유지보수 가능한 복잡한 쿼리를 작성하고 트리 구조 데이터를 처리하는 데 유용합니다.

일상적 이점은 **가독성**입니다: 여러 서브쿼리 수준의 복잡한 쿼리는 유지보수가 어려워지는 반면, CTE는 그것을 명명되고 순차적인 논리적 단계(위에서 아래로 읽음)로 분해하게 해주어 명확성을 극적으로 개선합니다 — CTE를 유지보수 가능한 SQL 작성의 중요한 도구로 만듭니다.

두드러진 능력은 **재귀 CTE**(`WITH RECURSIVE`)로, **계층 및 트리 구조 데이터**(조직도, 카테고리 트리, 자재 명세서, 스레드 댓글, 그래프 순회)를 쿼리하게 해줍니다 — 그렇지 않으면 SQL에서 매우 어려운 쿼리 부류로, 재귀 CTE를 계층 순회의 표준 솔루션으로 만듭니다.

재귀 CTE 구조(기본 케이스 + 자신에게 다시 조인하는 재귀 단계)를 이해하는 것은 이 흔한 필요에 핵심 지식입니다.

유용한 Postgres 특화 세부사항은 **CTE materialization** 동작(Postgres 12+는 더 나은 성능을 위해 CTE를 inline할 수 있고, `MATERIALIZED`/`NOT MATERIALIZED`로 제어 가능)으로 — 최적화에 관련됩니다.

복잡한 쿼리는 흔하고(가독성이 유지보수에 중요), 계층 데이터(재귀 CTE가 독특하게 잘 처리)가 자주 나타나므로(조직 구조, 중첩 카테고리), CTE와 재귀 쿼리를 이해하는 것은 쿼리 유지보수성을 개선하고 계층 쿼리를 열어주는 가치 있고 실용적으로 관련된 PostgreSQL 지식이며 — 깔끔하고 정교한 SQL을 위한 자주 관련되는 능력이자 흔한 주제이고, 특히 트리 구조 데이터를 쿼리하는 반복적 필요에 재귀 형태가 그렇습니다.