Quick Reference
WITH recent_posts AS (
SELECT id, user_id, title
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) AS post_count
FROM recent_posts
GROUP BY user_id;CTE는 이름 붙인 중간 query가 읽기와 검증을 쉽게 만들 때 쓰고, 짧게 한 번 쓰는 중첩은 서브쿼리와 비교합니다. PostgreSQL 12+에서는 side-effect-free CTE를 한 번 참조하면 보통 본문으로 접히고, 여러 번 참조하면 기본적으로 materialize됩니다. 비용·최적화 경계를 바꿀 때만 AS MATERIALIZED 또는 AS NOT MATERIALIZED를 명시하고 EXPLAIN으로 확인합니다.
문법
CTE 는 복잡한 쿼리를 단계별로 분리해 의도를 명확히 한다
WITH name AS (...) 은 뒤따르는 본문 쿼리에서 그 이름을 테이블처럼 참조할 수 있게 해 줍니다. 깊이 중첩된 서브쿼리와 달리 각 단계에 의미 있는 이름을 붙일 수 있어 쿼리의 흐름이 글처럼 읽힙니다. 여러 CTE 를 순서대로 나열하면 "활성 사용자를 먼저 추린 뒤, 그 중 최근 7일 글을 쓴 사람을 다시 필터링한다"처럼 분석 쿼리의 단계를 자연스럽게 표현할 수 있습니다.
WITH active_users AS (
SELECT id, name
FROM users
WHERE active = true
),
recent_writers AS (
SELECT DISTINCT user_id
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT u.id, u.name
FROM active_users AS u
JOIN recent_writers AS rw ON rw.user_id = u.id;CTE와 서브쿼리는 둘 다 중간 결과를 만들지만 읽는 흐름이 다르다
CTE는 쿼리를 위에서 아래로 단계별로 읽게 만들고, 서브쿼리는 필요한 위치에 바로 끼워 넣는 방식입니다. 여러 단계를 이름 붙여 설명해야 하면 CTE가 더 읽기 쉽고, 한 번만 쓰는 짧은 중첩이라면 서브쿼리가 더 짧을 수 있습니다.
-- CTE: 단계에 이름 붙이기
WITH recent_posts AS (
SELECT *
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT COUNT(*) FROM recent_posts;
-- 서브쿼리: 짧게 한 번만 감싸기
SELECT COUNT(*)
FROM (
SELECT *
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
) AS recent_posts;CTE materialization은 참조 횟수와 힌트에 따라 달라진다
PostgreSQL 12 이전에는 CTE가 항상 별도 임시 결과로 materialize됐습니다. PostgreSQL 12부터 side-effect-free CTE가 한 번만 참조되면 보통 본문 query로 fold되어 필터와 join 최적화를 함께 검토할 수 있습니다. 여러 번 참조되는 CTE는 기본적으로 materialize되며, AS MATERIALIZED는 그 경계를 강제하고 AS NOT MATERIALIZED는 중복 계산 가능성을 감수하고 fold를 요청합니다.
-- PostgreSQL 12+: 옵티마이저가 자동으로 인라인 처리 가능
WITH filtered AS (
SELECT id, title FROM posts WHERE user_id = 42
)
SELECT * FROM filtered WHERE id > 100;
-- 강제 실체화: 중간 결과를 캐시하고 싶을 때
WITH expensive_result AS MATERIALIZED (
SELECT ... FROM large_table WHERE ...
)
SELECT * FROM expensive_result
UNION ALL
SELECT * FROM expensive_result WHERE ...; -- 두 번 참조, 한 번만 실행
-- 강제 인라인: 실체화 비용을 피하고 싶을 때
WITH simple AS NOT MATERIALIZED (
SELECT id FROM users WHERE active = true
)
SELECT * FROM simple WHERE id > 1000;CTE 는 윈도 함수 결과를 필터링하거나 다단계 집계를 표현할 때 가장 유용하다
윈도 함수는 WHERE 절에서 직접 참조할 수 없습니다. CTE 로 한 단계 감싸면 윈도 함수 결과를 다음 단계의 필터 조건으로 쓸 수 있습니다. 다단계 집계(전체 집계 → 다시 집계하는 패턴)에서도 CTE 를 단계별로 나누면 중간 결과를 검증하기 쉽습니다.
-- 윈도 함수 결과 필터링 패턴
WITH ranked_posts AS (
SELECT
id, user_id, title, created_at,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM posts
)
SELECT id, user_id, title
FROM ranked_posts
WHERE rn = 1;CTE와 JOIN은 대체 관계가 아니라 역할이 다르다
CTE는 단계를 나누는 도구이고, JOIN은 관계를 결합하는 도구입니다. "여러 테이블을 붙이는 작업" 자체는 여전히 JOIN이 맡고, 그 앞뒤를 읽기 좋게 분리할 때 CTE가 붙습니다. CTE만으로 관계 표현을 대신하려고 하면 오히려 같은 테이블을 여러 번 훑는 구조가 되기 쉽습니다.
WITH recent_posts AS (
SELECT id, user_id
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT u.id, u.name
FROM users AS u
JOIN recent_posts AS rp ON rp.user_id = u.id;data-modifying CTE는 RETURNING으로만 다음 단계에 결과를 넘긴다
CTE 안에 INSERT, UPDATE, DELETE가 있으면 data-modifying CTE가 됩니다. 각 문장은 한 번 실행되어 완료되지만, 형제 CTE와 본문 query는 같은 snapshot을 공유하고 실제 실행 순서를 예측할 수 없습니다. target table을 다시 읽어 순서를 전달하려 하지 말고, RETURNING 출력으로만 다음 단계에 값을 넘깁니다. 같은 행을 두 data-modifying CTE에서 바꾸는 설계는 피합니다.
-- 쓰기 + RETURNING 파이프라인
WITH inserted_user AS (
INSERT INTO users (email, name)
VALUES ('new@example.com', 'New User')
RETURNING id
)
INSERT INTO user_settings (user_id, theme)
SELECT id, 'default'
FROM inserted_user;선택 기준
| 상황 | 적합한 선택 |
|---|---|
| 중첩 서브쿼리를 읽기 좋게 나눌 때 | WITH name AS (...) CTE |
| 윈도 함수 결과로 행 필터링이 필요할 때 | CTE 로 감싸고 바깥에서 WHERE |
| CTE 를 여러 번 참조하고 계산 비용이 클 때 | name AS MATERIALIZED (...) 검토 |
| CTE 를 여러 번 참조하지만 본문 최적화가 더 중요할 때 | name AS NOT MATERIALIZED (...) + 중복 비용 확인 |
| 쓰기 결과를 다음 쓰기 입력으로 연결할 때 | CTE + RETURNING 파이프라인 |
주의할 점
PostgreSQL 12 이전에서는 CTE가 항상 materialize되어 내부 필터가 바깥으로 전파되지 않을 수 있습니다.
PostgreSQL 12 이상에서는 side-effect-free CTE를 여러 번 참조하면 기본적으로 materialize됩니다.
NOT MATERIALIZED는 본문 최적화를 넓히는 대신 계산을 반복할 수 있으므로, 힌트는 추측으로 넣지 말고 EXPLAIN으로 확인하세요.
WITH filtered AS (
SELECT * FROM posts
)
SELECT * FROM filtered WHERE user_id = 42;단순한 한 단계 쿼리를 습관적으로 CTE로 감싸면 읽기만 길어질 수 있습니다. 단계 이름이 실제 의미를 더해 주지 못하면 오히려 바로 쓴 쿼리가 더 낫습니다.
CTE 이름을 실제 테이블 이름과 똑같이 짓거나 너무 포괄적으로 짓기 시작하면, 본문 쿼리를 읽을 때 어느 posts를 말하는지 바로 헷갈립니다. WITH posts AS (SELECT ... FROM posts)처럼 쓰면 CTE 이름이 원본 테이블 이름을 가려 재귀 query 오류까지 만들 수 있습니다. recent_posts, ranked_posts처럼 단계의 의미가 드러나는 이름을 사용하세요.
참고 링크
1 sources