PostgreSQL 쿼리 설계의 다섯 가지 축

PostgreSQL의 고급 기능들 — 윈도우 함수, CTE, 파티셔닝, LATERAL JOIN, Upsert, 다차원 집계 — 은 겉보기에 서로 무관해 보인다. 하지만 이 모든 기능을 관통하는 하나의 질문이 있다. “같은 데이터를 몇 번 읽어야 하는가?” 그리고 그 답은 거의 항상 “한 번”이다.

행을 유지하면서 집계하는 법

GROUP BY는 N개 행을 K개로 줄인다. 유용하지만, “각 주문과 함께 사용자별 누적 합계를 보여라” 같은 요구에는 무력하다. 윈도우 함수는 이 문제를 정면으로 해결한다.

SELECT
    user_id, order_id, amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE status = 'DONE';

OVER 절의 세 요소는 각자 역할이 있다. PARTITION BY는 데이터를 독립된 구역으로 나누고, ORDER BY는 구역 내 순서를 정하며, ROWS BETWEEN은 현재 행 기준으로 집계 범위를 지정한다. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 파티션 시작부터 현재 행까지 — 즉 누적 합계다.

ROWS와 RANGE의 차이는 동점 처리에서 드러난다. ROWS는 물리적 행 수를 세고, RANGE는 ORDER BY 값이 같은 행을 하나의 그룹으로 취급한다. 날짜별 누적 합계에서 같은 날짜 행이 여럿이면 RANGE는 그 모두를 같은 프레임으로 묶어 동일한 누적값을 부여한다. 직관적이지 않으므로 이동 평균이나 누적 합계에는 ROWS가 더 명확하다.

순위 함수도 여기 속한다. ROW_NUMBER()는 동점을 무시하고 항상 고유 번호를, RANK()는 동점자에게 같은 순위를 주되 다음 순위를 건너뛰고, DENSE_RANK()는 건너뜀 없이 연속 번호를 유지한다. 각 카테고리 최상위 N개를 가져오는 고전적 패턴은 ROW_NUMBER()로 번호를 매기고 서브쿼리로 감싼 뒤 WHERE rn <= N으로 필터한다.

CTE — Optimization Fence의 두 얼굴

WITH 절은 쿼리 가독성을 높이지만, PostgreSQL 버전에 따라 동작이 다르다.

PostgreSQL 11 이하에서 CTE는 항상 MATERIALIZED — 먼저 전체를 실행하고 결과를 임시 저장한 뒤 외부 조건을 적용한다. WITH active AS (SELECT * FROM orders WHERE status='ACTIVE') SELECT * FROM active WHERE user_id = 42를 실행하면, 옵티마이저는 user_id 인덱스를 활용하지 못하고 status='ACTIVE' 전체를 임시 테이블에 쏟아낸 다음 user_id = 42를 필터한다.

PostgreSQL 12부터는 기본이 NOT MATERIALIZED — CTE를 인라인으로 처리해 외부 조건을 CTE 내부로 밀어 넣는다. 위 쿼리는 (user_id, status) 복합 인덱스를 활용한 Index Scan으로 변환된다.

재귀 CTE(WITH RECURSIVE)는 계층 데이터 순회의 유일한 SQL 도구다. 앵커(비재귀 파트)로 시작점을 정하고, 재귀 파트에서 이전 결과를 참조하며 반복한다. 내부적으로 BFS 방식으로 WorkTable을 교체하며 빈 결과가 나올 때까지 반복한다. 순환 그래프가 있다면 depth < N 조건 또는 PostgreSQL 14+의 CYCLE 절로 무한 루프를 방지한다.

DML을 포함한 CTE는 항상 MATERIALIZED이며, RETURNING으로 변경된 행을 다음 단계에 전달할 수 있다. 작업 큐에서 항목을 가져오는 동시에 상태를 업데이트하고 로그를 기록하는 패턴을 단일 원자적 트랜잭션으로 표현하는 게 가능해진다.

파티셔닝 — DROP이 DELETE보다 빠른 이유

단일 테이블에 수억 건이 쌓이면 VACUUM, 인덱스 스캔, 통계 수집 모두 느려진다. 파티셔닝은 데이터를 논리적으로는 하나의 테이블로, 물리적으로는 여러 파티션 파일로 분산 저장한다.

핵심은 Partition Pruning이다. WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'이면 플래너는 2024년 1월 파티션만 스캔한다. 나머지는 실행 계획에 포함되지 않는다. 단, date_trunc('month', created_at) = '2024-01-01' 같은 함수 변환은 Pruning을 막는다 — 파티션 키를 직접 비교해야 한다.

파티션의 진짜 이점은 오래된 데이터 제거에서 나온다.

ALTER TABLE orders DETACH PARTITION orders_2022;
DROP TABLE orders_2022;  -- 즉각 완료

같은 데이터를 DELETE로 지우면 수 시간이 걸리고, Dead Tuple이 생기고, VACUUM이 뒤따라야 한다. DROP TABLE은 파일을 삭제하는 것이므로 즉각 완료된다.

LATERAL JOIN — 외부 행을 알고 있는 서브쿼리

일반 서브쿼리는 외부 FROM 절을 참조할 수 없다. LATERAL은 이 제약을 푼다.

SELECT u.id, u.name, recent.*
FROM users u
LEFT JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders
    WHERE user_id = u.id          -- 외부 행 참조
    ORDER BY created_at DESC
    LIMIT 3
) recent ON true;

LATERAL 서브쿼리는 users의 각 행마다 독립적으로 실행된다. 실행 계획은 Nested Loop이고, 내부에 (user_id, created_at DESC) 인덱스가 있다면 각 사용자마다 인덱스로 최신 3개를 빠르게 찾는다. 인덱스가 없으면 사용자 수만큼 Seq Scan이 반복되므로 LATERAL 사용 시 관련 인덱스는 필수다.

윈도우 함수도 같은 문제를 풀 수 있지만 차이가 있다. 윈도우 함수는 전체 테이블을 스캔한 뒤 순위를 부여하고 필터한다. LATERAL은 필요한 외부 행에 대해서만 서브쿼리를 실행한다. 특정 사용자 집합만 조회할 때는 LATERAL이, 전체 데이터에 순위를 매겨야 할 때는 윈도우 함수가 더 적합하다.

JSONB 배열 분해에서도 LATERAL이 필수다. jsonb_array_elements_text(e.tags)는 e를 참조하므로 암묵적 LATERAL이다.

트레이드오프

각 기능은 명확한 비용을 가진다.

윈도우 함수: 정렬 비용이 전부다. WindowAgg 앞에는 항상 Sort 노드가 있다. work_mem이 부족하면 외부 정렬(디스크)로 빠진다. 같은 PARTITION BY + ORDER BY를 공유하는 함수들은 단일 Sort를 공유하므로, WINDOW w AS (...) 별칭으로 재사용하는 것이 명시적이고 효율적이다.

CTE: PostgreSQL 12+에서 단순 CTE는 서브쿼리와 성능 차이가 없다. MATERIALIZED를 쓸 때는 “결과를 메모리에 저장한다”는 비용을 인식하고 선택한다.

파티셔닝: 파티션 키를 넘나드는 UPDATE는 내부적으로 DELETE + INSERT로 처리된다. 파티션 경계를 자주 넘는 UPDATE가 많으면 설계를 재검토해야 한다.

LATERAL: Nested Loop이 기반이므로 외부 집합이 클수록 비용이 선형으로 증가한다. 내부 서브쿼리에 적절한 인덱스가 없으면 쓰지 않는 것이 낫다.

Upsert와 SKIP LOCKED: INSERT ON CONFLICT DO UPDATE는 원자적이지만 같은 키에 집중적인 Upsert는 Lock 경합을 만든다. SKIP LOCKED 큐 패턴은 Worker가 죽으면 트랜잭션 ROLLBACK으로 자동 복원되지만, 오래된 PROCESSING 항목을 주기적으로 점검하는 타임아웃 처리가 필요하다.

정리

• 윈도우 함수는 행을 유지하면서 집계한다. PARTITION BY + ORDER BY + ROWS/RANGE BETWEEN이 세 축이다.
• CTE는 PostgreSQL 12+에서 기본 NOT MATERIALIZED다. 버전과 참조 횟수에 따라 명시적 선택이 달라진다.
• 파티셔닝의 핵심 이점은 Pruning과 파티션 DROP이다. 파티션 키 없는 쿼리는 Pruning이 없다.
• LATERAL은 외부 행을 참조해야 하거나 LIMIT을 파티션별로 다르게 적용해야 할 때 필수다.
• GROUPING SETS / ROLLUP / CUBE는 UNION ALL을 단일 스캔으로 대체한다. FILTER 절은 CASE WHEN을 읽기 쉽게 만든다.

다음 글에서는 이 쿼리들의 실행 계획 — WindowAgg, HashAgg, Nested Loop, Append 노드가 각각 무엇을 의미하는지, 그리고 EXPLAIN (ANALYZE, BUFFERS)로 병목을 어떻게 찾는지 추적한다.