숏컷 코드
WITH RECURSIVE category_tree AS (
-- anchor: 시작점 (최상위 카테고리)
SELECT id, parent_id, name, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- recursive member: 이전 단계 결과를 다음 단계 입력으로
SELECT c.id, c.parent_id, c.name, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT *
FROM category_tree
ORDER BY depth, name;문법
Recursive CTE 는 "현재 결과를 다음 입력으로 되먹임"하는 반복 쿼리다
일반 CTE 와 달리 WITH RECURSIVE 는 두 부분으로 구성됩니다. 첫 번째는 anchor query(시작점)로 재귀 없이 실행되는 기본 결과 집합이고, 두 번째는 recursive member 로 anchor 결과를 포함한 지금까지의 누적 결과를 다시 참조해 다음 단계를 만들어 냅니다. PostgreSQL 은 이 결과가 비어질 때까지 recursive member 를 반복 실행합니다. UNION ALL 이 두 부분을 연결하며, UNION(중복 제거)은 재귀 종료 판단이 어려워져 일반적으로 사용하지 않습니다.
-- 특정 노드의 모든 하위 계층 추적
WITH RECURSIVE subtree AS (
SELECT id, parent_id, name, 0 AS depth
FROM categories
WHERE id = 5 -- 시작 노드
UNION ALL
SELECT c.id, c.parent_id, c.name, s.depth + 1
FROM categories c
JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;recursive CTE는 anchor와 recursive member 두 부분으로 읽으면 된다
재귀 CTE는 시작점(anchor)과 반복 부분(recursive member)으로 나뉩니다. 먼저 어디서 출발하는지, 그다음 한 단계씩 어떻게 확장하는지 순서대로 읽으면 구조가 단순해집니다.
WITH RECURSIVE subtree AS (
-- anchor
SELECT id, parent_id, name, 0 AS depth
FROM categories
WHERE id = 5
UNION ALL
-- recursive member
SELECT c.id, c.parent_id, c.name, s.depth + 1
FROM categories c
JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;depth 추적과 종료 조건이 없으면 순환 데이터에서 무한 루프가 발생한다
데이터에 순환 참조(A → B → A)가 존재하면 recursive member 가 영원히 반복됩니다. PostgreSQL 은 기본적으로 무한 재귀를 막는 안전장치가 없으므로, depth 컬럼으로 최대 깊이를 제한하거나 방문한 경로(path 배열)를 추적해 이미 방문한 노드를 걸러내는 패턴을 써야 합니다. 운영 환경에서는 데이터 무결성과 함께 쿼리 수준 방어가 모두 필요합니다.
-- depth 제한으로 안전망 추가
WITH RECURSIVE org_chart AS (
SELECT id, manager_id, name, 1 AS depth,
ARRAY[id] AS visited
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, oc.depth + 1,
oc.visited || e.id
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
WHERE oc.depth < 10 -- 최대 깊이 제한
AND NOT e.id = ANY(oc.visited) -- 순환 참조 차단
)
SELECT * FROM org_chart;recursive CTE와 ltree는 계층을 다루는 방식이 다르다
WITH RECURSIVE는 현재 인접 관계를 따라가며 매번 계산하는 방식이고, ltree는 경로를 컬럼에 저장해 하위 트리를 바로 찾는 방식입니다. 계층이 자주 바뀌고 깊이가 크지 않으면 recursive CTE가 단순하고, 하위 경로 조회가 반복되는 읽기 중심 구조라면 ltree가 더 직접적일 수 있습니다.
-- 인접 리스트 순회
WITH RECURSIVE subtree AS (
SELECT id, parent_id, name
FROM categories
WHERE id = 10
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories AS c
JOIN subtree AS s ON c.parent_id = s.id
)
SELECT * FROM subtree;Recursive CTE 의 실행 계획은 WorkTable Scan 으로 나타난다
EXPLAIN 으로 recursive CTE 를 확인하면 WorkTable Scan 노드가 나타납니다. 이는 각 반복 단계에서 이전 단계 결과를 임시 작업 테이블(work table)에서 읽는다는 의미입니다. 인덱스는 anchor query 와 recursive member 의 base table 접근에만 사용될 수 있고, work table 자체는 인덱스를 지원하지 않습니다. 따라서 조인 대상 컬럼(parent_id)에 인덱스가 없으면 각 반복마다 Sequential Scan 이 발생합니다.
-- 조인 키 인덱스 필수
CREATE INDEX idx_categories_parent_id ON categories (parent_id);
-- 실행 계획 확인
EXPLAIN ANALYZE
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name, 1 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;Ltree 확장이나 JSONB 계층 표현이 더 적합한 케이스도 있다
Recursive CTE 는 SQL 만으로 계층을 순회하는 강력한 도구이지만, 계층이 매우 깊거나 계층 구조 자체가 핵심 쿼리 패턴이라면 PostgreSQL 의 ltree 확장을 고려할 수 있습니다. ltree 는 경로 문자열(A.B.C)로 계층을 저장하고 GiST 인덱스로 하위 트리 조회를 단일 쿼리로 처리합니다. 반면 계층이 3~5단계 이내로 얕고 자주 변경된다면 Recursive CTE 가 충분합니다.
-- ltree 확장 활성화 후 경로 컬럼으로 계층 표현
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE categories_ltree (
id BIGSERIAL PRIMARY KEY,
path ltree NOT NULL
);
-- 특정 경로 하위 모든 노드 조회 (인덱스 활용)
CREATE INDEX idx_categories_path ON categories_ltree USING GIST (path);
SELECT * FROM categories_ltree
WHERE path <@ 'root.electronics';선택 기준
| 상황 | 적합한 선택 |
|---|---|
| 계층이 3~10단계 이내이고 SQL 만 쓸 때 | WITH RECURSIVE CTE |
| 계층이 매우 깊거나 하위 트리 조회가 잦을 때 | ltree 확장 |
| 순환 참조 가능성이 있는 데이터 | depth 제한 + 방문 경로 배열 추적 |
| 재귀 조인 성능이 느릴 때 | parent_id 컬럼에 인덱스 추가 |
| 실행 계획 확인 | EXPLAIN ANALYZE 에서 WorkTable Scan 확인 |
주의할 점
순환 참조가 있거나 종료 조건이 불분명한 데이터에서 WITH RECURSIVE 를 사용하면 무한 루프가 발생할 수 있습니다.
PostgreSQL 은 기본적으로 자동 종료 안전장치가 없으므로 depth 컬럼으로 최대 깊이를 제한하고,
방문한 노드 ID 배열로 순환 참조를 명시적으로 차단해야 합니다.
또한 recursive member 의 조인 컬럼(parent_id 등)에 인덱스가 없으면 각 반복마다 전체 스캔이 반복됩니다.
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;순환 데이터가 있을 때 깊이 제한이나 방문 경로 추적이 없으면, 이 구조는 끝나지 않을 수 있습니다.
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, ct.depth + 1
FROM categories AS c
JOIN category_tree AS ct ON c.parent_id = ct.id
WHERE ct.depth < 3
)
SELECT * FROM category_tree;깊이 제한을 너무 낮게 두면 무한 루프는 막아도 결과가 조용히 잘립니다. "안전장치"와 "업무상 실제 최대 깊이"는 다른 문제이므로, 값 하나로 둘을 동시에 해결하려고 하면 누락된 하위 노드를 놓치기 쉽습니다.
참고 링크
1 sources