Quick Reference
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;WITH RECURSIVE는 시작 행(anchor)에서 출발해 이전 단계의 결과로 다음 행을 찾습니다. 계층 조회에는 UNION ALL과 parent_id 조인이 기본이고, 순환 가능성이 있으면 방문 경로 또는 CYCLE 절로 종료를 보장해야 합니다. 결과 순서는 평가 순서가 아니므로 마지막 SELECT에서 명시합니다.
문법
Recursive CTE 는 "현재 결과를 다음 입력으로 되먹임"하는 반복 쿼리다
일반 CTE와 달리 WITH RECURSIVE는 두 부분으로 구성됩니다. 첫 번째는 재귀 없이 시작 행을 만드는 anchor query이고, 두 번째는 이전 단계 결과를 참조해 다음 행을 만드는 recursive member입니다. PostgreSQL은 새로 만들어지는 작업 행이 없어질 때까지 이를 반복합니다. 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;결과 순서는 마지막 SELECT에서 명시한다
PostgreSQL의 재귀 평가는 내부적으로 반복 방식이며, 행이 어떤 순서로 평가·반환되는지는 결과 계약이 아닙니다. 너비 우선이나 깊이 우선으로 보여야 한다면 정렬용 열을 만들거나 SEARCH 절로 정렬 키를 만들고 마지막 SELECT에서 ORDER BY를 지정합니다.
WITH RECURSIVE subtree AS (
SELECT id, parent_id, name
FROM categories
WHERE id = 5
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories c
JOIN subtree s ON c.parent_id = s.id
)
SEARCH DEPTH FIRST BY id SET visit_order
SELECT * FROM subtree
ORDER BY visit_order;순환 가능성은 깊이 제한이 아니라 경로로 감지한다
데이터에 순환 참조(A → B → A)가 있으면 recursive member가 끝없이 새 행을 만들 수 있습니다. depth 제한은 조회 결과를 잘라내는 안전장치일 뿐 순환을 정확히 감지하지 못합니다. 업무상 최대 깊이가 정해진 경우에만 그 값을 별도 조건으로 두고, 순환 자체는 방문한 ID 경로를 추적하거나 CYCLE 절로 막습니다. 애초에 관계 테이블의 무결성을 지키는 것과 쿼리에서 방어하는 것은 모두 필요합니다.
-- 업무상 최대 깊이가 정해진 경우에만 별도 제한을 둔다
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 < :max_depth
AND NOT e.id = ANY(oc.visited) -- 순환 참조 차단
)
SELECT * FROM org_chart;CYCLE 절은 방문 경로 배열을 직접 쓰는 코드를 줄인다
PostgreSQL은 순환 여부와 경로를 자동으로 추가하는 CYCLE 절을 지원합니다. is_cycle이 참인 행은 결과에서 확인할 수 있고, 반복은 계속 확장되지 않습니다. 경로를 직접 가공해야 하거나 오래된 호환성이 필요할 때는 앞의 배열 패턴을 사용합니다.
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name
FROM categories
WHERE id = 5
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories AS c
JOIN category_tree AS ct ON c.parent_id = ct.id
)
CYCLE id SET is_cycle USING path
SELECT id, parent_id, name, is_cycle
FROM category_tree;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 노드가 나타날 수 있습니다. 이는 이전 단계 결과를 작업 테이블에서 읽는다는 뜻입니다. 인덱스는 anchor query와 recursive member가 원본 테이블에 접근할 때에만 후보가 됩니다. parent_id를 반복해서 찾아야 하고 테이블이 충분히 크다면 인덱스를 검토하되, 실제 계획이 항상 Index Scan이 되거나 인덱스 하나로 재귀 비용이 해결된다고 보지는 않습니다.
-- 재귀 단계에서 parent_id를 반복 탐색하는 경우의 후보
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 인덱스로 하위 트리 조회를 표현합니다. 반면 인접 관계를 자주 수정하거나 필요한 조회가 현재 노드에서의 순회라면 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';선택 기준
| 상황 | 적합한 선택 |
|---|---|
| 인접 관계를 따라 계층을 한 번 조회할 때 | WITH RECURSIVE CTE |
| 계층이 매우 깊거나 하위 트리 조회가 잦을 때 | ltree 확장 |
| 순환 참조 가능성이 있는 데이터 | CYCLE 절 또는 방문 경로 배열 추적 |
| 재귀 조인 성능이 느릴 때 | parent_id 인덱스 후보를 계획과 함께 검토 |
| 실행 계획 확인 | EXPLAIN ANALYZE 에서 WorkTable Scan 확인 |
주의할 점
순환 참조가 있거나 종료 조건이 불분명한 데이터에서 WITH RECURSIVE 를 사용하면 무한 루프가 발생할 수 있습니다.
PostgreSQL은 순환을 자동으로 알아내지 않으므로 방문한 노드 ID 배열 또는 CYCLE 절로 순환을 명시적으로 차단해야 합니다.
depth 제한은 업무상 허용된 깊이를 표현할 때만 사용합니다. 또한 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 < :max_depth
)
SELECT * FROM category_tree;깊이 제한을 너무 낮게 두면 무한 루프는 막아도 결과가 조용히 잘립니다. "안전장치"와 "업무상 실제 최대 깊이"는 다른 문제이므로, 값 하나로 둘을 동시에 해결하려고 하면 누락된 하위 노드를 놓치기 쉽습니다.
참고 링크
1 sources