PostgreSQL CTE 재귀 쿼리 심화

CTE(Common Table Expression)란?

CTE는 WITH 절로 정의하는 임시 명명된 결과 집합이다. 서브쿼리를 이름 붙여 재사용하고, 복잡한 쿼리를 단계별로 분리해 가독성을 높인다. PostgreSQL은 재귀 CTE, Materialized CTE, CTE 내 DML까지 지원하며, 이는 MySQL 대비 훨씬 강력하다.

기본 CTE 문법

-- 기본 CTE: 월별 매출 집계 후 필터링
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS total,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    total,
    order_count,
    ROUND(total / order_count, 2) AS avg_order_value
FROM monthly_sales
WHERE total > 1000000
ORDER BY month DESC;

-- 다중 CTE: 단계별 분리
WITH
active_members AS (
    SELECT id, name, tier
    FROM member
    WHERE status = 'ACTIVE'
      AND last_login_at > NOW() - INTERVAL '30 days'
),
member_orders AS (
    SELECT
        m.id AS member_id,
        m.name,
        m.tier,
        COUNT(o.id) AS order_count,
        SUM(o.amount) AS total_spent
    FROM active_members m
    JOIN orders o ON m.id = o.member_id
    WHERE o.created_at > NOW() - INTERVAL '90 days'
    GROUP BY m.id, m.name, m.tier
)
SELECT *
FROM member_orders
WHERE total_spent > 500000
ORDER BY total_spent DESC;

다중 CTE는 이전 CTE를 참조할 수 있어, 파이프라인처럼 단계별로 데이터를 가공한다. 각 단계가 명확히 분리되므로 디버깅과 유지보수가 쉽다.

재귀 CTE: 계층 구조 탐색

재귀 CTE는 트리, 그래프, 계층 구조를 SQL로 탐색하는 강력한 도구다. 조직도, 카테고리 트리, 댓글 스레드 등에 활용된다.

-- 조직도: 특정 매니저의 모든 부하직원 조회
WITH RECURSIVE org_tree AS (
    -- 기저 조건 (Anchor): 시작점
    SELECT id, name, manager_id, 1 AS depth, 
           ARRAY[name] AS path
    FROM employee
    WHERE id = 1  -- CEO부터 시작

    UNION ALL

    -- 재귀 조건: 하위 직원 탐색
    SELECT e.id, e.name, e.manager_id, t.depth + 1,
           t.path || e.name
    FROM employee e
    JOIN org_tree t ON e.manager_id = t.id
    WHERE t.depth < 10  -- 무한 루프 방지
)
SELECT
    id,
    REPEAT('  ', depth - 1) || name AS hierarchy,
    depth,
    ARRAY_TO_STRING(path, ' → ') AS full_path
FROM org_tree
ORDER BY path;

-- 결과:
-- id | hierarchy          | depth | full_path
-- 1  | CEO 김대표          | 1     | CEO 김대표
-- 2  |   CTO 박기술        | 2     | CEO 김대표 → CTO 박기술
-- 5  |     팀장 이개발      | 3     | CEO 김대표 → CTO 박기술 → 팀장 이개발
-- 8  |       시니어 최코딩   | 4     | CEO 김대표 → CTO 박기술 → 팀장 이개발 → 시니어 최코딩

재귀 CTE: 카테고리 트리

-- 테이블 구조
CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES category(id),
    sort_order INTEGER DEFAULT 0
);

-- 특정 카테고리의 모든 하위 카테고리 (하향 탐색)
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth,
           ARRAY[sort_order] AS sort_path
    FROM category
    WHERE id = 1  -- '전자제품' 루트

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           ct.sort_path || c.sort_order
    FROM category c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, REPEAT('├─ ', depth) || name AS tree, depth
FROM category_tree
ORDER BY sort_path;

-- 특정 카테고리의 모든 상위 카테고리 (상향 탐색 = 빵부스러기)
WITH RECURSIVE breadcrumb AS (
    SELECT id, name, parent_id
    FROM category
    WHERE id = 42  -- '무선 이어폰'

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM category c
    JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT STRING_AGG(name, ' > ' ORDER BY id) AS breadcrumb
FROM breadcrumb;
-- 결과: 전자제품 > 음향기기 > 이어폰 > 무선 이어폰

JPA의 @ManyToOne(fetch = LAZY) 자기참조로는 N+1 쿼리가 발생하지만, 재귀 CTE는 한 번의 쿼리로 전체 트리를 조회한다. TypeORM의 Tree Entity 패턴과 비교는 TypeORM Tree Entity 심화 글을 참고하자.

재귀 CTE: 그래프 탐색

-- 소셜 네트워크: N촌 관계 탐색
CREATE TABLE friendship (
    user_id INTEGER NOT NULL,
    friend_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, friend_id)
);

-- 3촌까지의 모든 연결 찾기
WITH RECURSIVE network AS (
    SELECT friend_id AS user_id, 1 AS degree,
           ARRAY[1, friend_id] AS visited  -- 순환 방지
    FROM friendship
    WHERE user_id = 1

    UNION ALL

    SELECT f.friend_id, n.degree + 1,
           n.visited || f.friend_id
    FROM friendship f
    JOIN network n ON f.user_id = n.user_id
    WHERE n.degree < 3
      AND f.friend_id != ALL(n.visited)  -- 순환 방지
)
SELECT DISTINCT user_id, MIN(degree) AS shortest_degree
FROM network
GROUP BY user_id
ORDER BY shortest_degree, user_id;

-- PostgreSQL 14+: CYCLE 감지 내장 문법
WITH RECURSIVE network AS (
    SELECT friend_id AS user_id, 1 AS degree
    FROM friendship WHERE user_id = 1

    UNION ALL

    SELECT f.friend_id, n.degree + 1
    FROM friendship f
    JOIN network n ON f.user_id = n.user_id
    WHERE n.degree < 3
)
CYCLE user_id SET is_cycle USING path  -- 자동 순환 감지
SELECT user_id, degree
FROM network
WHERE NOT is_cycle;

PostgreSQL 14의 CYCLE 절은 재귀 CTE에서 순환 참조를 자동으로 감지하고 중단한다. 수동으로 ARRAY와 ANY를 관리할 필요가 없다.

CTE 내 DML: Writeable CTE

-- INSERT + SELECT를 하나의 쿼리로: 아카이브 후 삭제
WITH archived AS (
    INSERT INTO orders_archive (id, member_id, amount, status, created_at)
    SELECT id, member_id, amount, status, created_at
    FROM orders
    WHERE created_at < NOW() - INTERVAL '1 year'
      AND status IN ('COMPLETED', 'CANCELLED')
    RETURNING id
),
deleted AS (
    DELETE FROM orders
    WHERE id IN (SELECT id FROM archived)
    RETURNING id
)
SELECT COUNT(*) AS archived_count FROM deleted;

-- UPSERT + 결과 활용
WITH upserted AS (
    INSERT INTO product_stats (product_id, view_count, last_viewed_at)
    VALUES (42, 1, NOW())
    ON CONFLICT (product_id) DO UPDATE SET
        view_count = product_stats.view_count + 1,
        last_viewed_at = NOW()
    RETURNING product_id, view_count
)
SELECT p.name, u.view_count
FROM upserted u
JOIN product p ON p.id = u.product_id;

Writeable CTE는 여러 DML을 하나의 원자적 쿼리로 실행한다. 아카이빙·마이그레이션·집계 갱신에서 트랜잭션 관리 없이 일관성을 보장한다.

MATERIALIZED vs NOT MATERIALIZED

-- PostgreSQL 12+: CTE 최적화 제어
-- NOT MATERIALIZED: CTE를 인라인 서브쿼리로 최적화 (기본값 변경됨)
WITH recent_orders AS NOT MATERIALIZED (
    SELECT * FROM orders
    WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT * FROM recent_orders
WHERE member_id = 42;
-- → 옵티마이저가 member_id = 42 조건을 orders 테이블 스캔에 직접 푸시

-- MATERIALIZED: CTE 결과를 임시 테이블로 먼저 실체화
WITH expensive_calc AS MATERIALIZED (
    SELECT member_id, SUM(amount) AS total
    FROM orders
    GROUP BY member_id
)
SELECT * FROM expensive_calc WHERE total > 100000
UNION ALL
SELECT * FROM expensive_calc WHERE total < 1000;
-- → CTE가 두 번 참조되므로 한 번만 계산하고 재사용

옵션	동작	사용 시점
NOT MATERIALIZED	인라인 최적화 허용	CTE 1회 참조, 필터 푸시다운 필요
MATERIALIZED	결과를 임시 저장	CTE 다중 참조, 비용 큰 집계

Window 함수와 CTE 조합

-- 연속 매출 성장 월 찾기
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY DATE_TRUNC('month', created_at)
),
with_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        revenue - LAG(revenue) OVER (ORDER BY month) AS growth,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))
            / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
        ) AS growth_pct
    FROM monthly
),
growth_streak AS (
    SELECT *,
        SUM(CASE WHEN growth > 0 THEN 0 ELSE 1 END) 
            OVER (ORDER BY month) AS streak_group
    FROM with_growth
)
SELECT month, revenue, growth_pct,
    COUNT(*) OVER (PARTITION BY streak_group ORDER BY month) AS consecutive_months
FROM growth_streak
WHERE growth > 0
ORDER BY month;

CTE를 단계별로 쌓아 원본 → 집계 → 윈도우 분석 → 스트릭 계산으로 복잡한 비즈니스 로직을 SQL만으로 구현할 수 있다. Window 함수에 대한 자세한 내용은 MySQL Window Functions 글도 참고하자.

성능 최적화

-- 1. 재귀 CTE에 LIMIT 사용
WITH RECURSIVE ... 
SELECT * FROM tree LIMIT 100;  -- 결과 제한

-- 2. 재귀 depth 제한 (필수!)
WHERE depth < 20  -- 무한 재귀 방지

-- 3. EXPLAIN ANALYZE로 실행 계획 확인
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH monthly AS (...)
SELECT * FROM monthly WHERE total > 1000000;
-- CTE Scan on monthly 확인 → Materialized 여부 판단

-- 4. 인덱스 활용: 재귀 CTE의 JOIN 컬럼
CREATE INDEX idx_employee_manager ON employee(manager_id);
CREATE INDEX idx_category_parent ON category(parent_id);

-- 5. 대용량 재귀 시 work_mem 조정
SET work_mem = '256MB';  -- 세션 단위 임시 메모리 증가

마무리

CTE 유형	용도	핵심 키워드
기본 CTE	쿼리 분리, 재사용	WITH ... AS
재귀 CTE	트리, 그래프 탐색	WITH RECURSIVE
Writeable CTE	다중 DML 원자적 실행	INSERT/DELETE RETURNING
Materialized CTE	비용 큰 결과 캐싱	MATERIALIZED

PostgreSQL의 CTE는 단순한 가독성 도구를 넘어, 계층 탐색·그래프 순회·원자적 DML·쿼리 최적화 제어까지 가능한 핵심 기능이다. 재귀 CTE로 트리 구조를 한 방 쿼리로 풀고, Writeable CTE로 아카이빙을 원자적으로 처리하며, MATERIALIZED 힌트로 옵티마이저를 제어하는 것이 PostgreSQL 고급 쿼리의 정석이다.