[PostgreSQL] 계층형 쿼리 정복: WITH RECURSIVE로 복잡한 조직도 한 번에 불러오기

[PostgreSQL] 계층형 쿼리 정복: WITH RECURSIVE로 복잡한 조직도 한 번에 불러오기

---

"PostgreSQL에서 조직도나 카테고리 같은 트리 구조 데이터를 어떻게 한 번에 조회할까요? Oracle의 CONNECT BY를 대체하는 WITH RECURSIVE의 문법부터 실무 예제, 그리고 인덱스를 활용한 $Big-O$ 성능 최적화 팁까지 상세히 정리했습니다."

---

1. 왜 계층형 쿼리가 필요한가?

웹 서비스를 개발하다 보면 부모와 자식 관계가 얽힌 데이터를 다뤄야 할 때가 정말 많습니다.

• 센터 구조: 본사 > 지역 본부 > 지점 > 대리점
• 카테고리: 전자제품 > 컴퓨터 > 노트북 > 맥북

만약 이 데이터를 Java 코드로만 처리하려면, 자식의 자식을 찾기 위해 DB를 여러 번 조회하는 'N+1 문제'에 빠지거나 코드가 매우 복잡해집니다. 하지만 PostgreSQL의 WITH RECURSIVE를 사용하면 단 한 번의 쿼리로 모든 트리 구조를 가져올 수 있습니다.

---

2. 예시 데이터 세팅 (Table & Data)

우리가 관리하는 센터 테이블(tb_center)에서 상하 관계를 관리한다고 가정해 봅시다. tc_code가 자식이고, tc_parent_code가 부모를 가리키는 구조입니다.

SQL

 

-- 테이블 구조 예시 (핵심 컬럼)
-- tc_code: 센터코드, tc_name: 센터명, tc_parent_code: 상위센터코드

INSERT INTO tb_center (tc_code, tc_name, tc_parent_code, td_code) VALUES
('00001', '알스퀘어 본사', NULL, 'RSQUARE'),         -- 최상위(Root)
('00002', '경기북부 지사', '00001', 'RSQUARE'),       -- 본사의 자식
('00003', '서울강남 지사', '00001', 'RSQUARE'),       -- 본사의 자식
('00049', '강남역 대리점', '00003', 'RSQUARE'),       -- 서울강남 지사의 자식
('00050', '삼성동 대리점', '00003', 'RSQUARE');       -- 서울강남 지사의 자식

---

3. WITH RECURSIVE 기본 문법 및 작동 원리

SQL

WITH RECURSIVE center_hierarchy AS (
    -- 1. Non-Recursive Term: 시작점(Root) 설정
    SELECT 
        tc_code, 
        tc_name, 
        tc_parent_code, 
        1 AS level,                         -- 계층 깊이 (1단계부터 시작)
        tc_name::TEXT AS tc_full_path       -- 전체 경로 저장용 (문자열 타입 캐스팅)
    FROM tb_center
    WHERE tc_parent_code IS NULL            -- 최상위 노드(본사)부터 시작
      AND td_code = 'RSQUARE'

    UNION ALL

    -- 2. Recursive Term: 자식 노드를 반복해서 연결
    SELECT 
        c.tc_code, 
        c.tc_name, 
        c.tc_parent_code, 
        h.level + 1,
        h.tc_full_path || ' > ' || c.tc_name -- 부모 경로에 현재 센터명 결합
    FROM tb_center c
    INNER JOIN center_hierarchy h ON c.tc_parent_code = h.tc_code -- 부모의 code와 자식의 parent_code 연결
    WHERE c.td_code = 'RSQUARE'
)
-- 3. 최종 결과 조회
SELECT * FROM center_hierarchy ORDER BY tc_full_path;

⚙️ 작동 원리 (Step-by-Step)

1. 앵커(Anchor) 실행: tc_parent_code가 없는 '알스퀘어 본사'를 먼저 결과 세트에 담습니다.
2. 재귀(Recursive) 실행: 방금 찾은 '본사'의 tc_code를 부모(tc_parent_code)로 둔 지사들을 찾아옵니다.
3. 반복: 더 이상 연결될 자식 데이터가 없을 때까지 자동으로 루프를 돌며 계층을 쌓아 올립니다.

---

4. 실무 활용 팁: 성능과 Big-O

기술 블로그라면 성능에 대한 언급이 빠질 수 없겠죠?

• 시간 복잡도($Big-O$):
• 각 단계에서 부모를 찾는 과정은 인덱스가 있을 때 O(\log n)의 성능을 보여줍니다. 전체 데이터를 한 번씩 순회하며 트리 구조를 완성하므로 전체적인 탐색 복잡도는 O(n) 수준입니다. Java에서 재귀 함수를 돌리며 DB를 여러 번 찌르는 것보다 훨씬 효율적입니다.
• 인덱스(Index) 필수:
• 조인 조건인 tc_parent_code 컬럼에는 반드시 인덱스가 걸려 있어야 합니다. 인덱스가 없다면 매 단계마다 테이블 전체를 스캔하는 O(n^2)의 참사가 일어날 수 있으니 주의해야 합니다.

---

5. 마무리하며

PostgreSQL의 WITH RECURSIVE는 Oracle의 CONNECT BY보다 유연하며 표준 SQL에 가깝습니다. 조직도나 메뉴 구조처럼 계층이 깊어질 수 있는 데이터를 처리할 때, 이 쿼리 하나면 백엔드 로직을 아주 깔끔하게 유지할 수 있습니다.