SQL CTE·View·Materialized View 선택 기준

CTE, View, Materialized View는 모두 복잡한 SELECT에 이름을 붙이지만 수명과 최신성이 다르다. CTE는 한 문장 동안만 존재하고, View는 조회 때 원본을 읽으며, Materialized View는 마지막 REFRESH 결과를 저장한다. 퀘스트 선행 관계와 시즌 리더보드로 이 차이를 실행해 본다.

선택표부터 보기

도구	수명·저장	적합한 게임 문제
일반 CTE	한 SQL 문장, 결과 비영속	활성 플레이어→점수 집계→상위권 단계화
재귀 CTE	기준 항 + 반복 항, 결과 비영속	퀘스트 선행 트리·길드 계층 탐색
View	정의만 저장, 조회 때 원본 계산	항상 최신인 공개용 플레이어 열 제한
Materialized View	결과 저장, 명시적 REFRESH	갱신 지연이 허용되는 대형 리더보드

실행 데이터 만들기

CREATE TABLE player (
  player_id bigint PRIMARY KEY,
  display_name text NOT NULL,
  email text NOT NULL UNIQUE,
  is_banned boolean NOT NULL DEFAULT false
);

CREATE TABLE quest (
  quest_id bigint PRIMARY KEY,
  quest_name text NOT NULL,
  prerequisite_id bigint REFERENCES quest(quest_id),
  reward_gold integer NOT NULL CHECK (reward_gold >= 0)
);

CREATE TABLE score_event (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  season_id bigint NOT NULL,
  player_id bigint NOT NULL REFERENCES player(player_id),
  score_delta integer NOT NULL,
  occurred_at timestamptz NOT NULL
);

INSERT INTO player VALUES
(1,'달빛검사','alice@example.com',false),
(2,'숲의궁수','bob@example.com',false),
(3,'금지된마법사','mallory@example.com',true);

INSERT INTO quest VALUES
(1,'초보자의 숲',NULL,100),
(2,'고블린 소굴',1,250),
(3,'오크 요새',2,500),
(4,'용의 둥지',3,2000),
(5,'낚시 입문',NULL,50);

INSERT INTO score_event(season_id,player_id,score_delta,occurred_at) VALUES
(1,1,120,'2026-08-10 09:00+09'),
(1,1,80,'2026-08-11 10:00+09'),
(1,2,150,'2026-08-11 11:00+09'),
(1,3,999,'2026-08-11 12:00+09');

일반 CTE로 계산 단계를 이름 붙이기

일반 CTE는 복잡한 쿼리를 위에서 아래로 읽히게 한다. 다음 쿼리는 먼저 시즌 점수를 집계하고, 밴 사용자를 제외한 뒤 100점 이상인 플레이어만 반환한다.

WITH season_score AS (
  SELECT player_id, SUM(score_delta)::bigint AS score
  FROM score_event
  WHERE season_id = 1
  GROUP BY player_id
),
eligible AS (
  SELECT s.player_id, p.display_name, s.score
  FROM season_score AS s
  JOIN player AS p USING (player_id)
  WHERE NOT p.is_banned
)
SELECT player_id, display_name, score
FROM eligible
WHERE score >= 100
ORDER BY score DESC, player_id;

player_id | display_name | score
----------+--------------+------
1         | 달빛검사     | 200
2         | 숲의궁수     | 150

CTE는 임시 테이블이 아니다. PostgreSQL은 부작용 없는 SELECT CTE가 한 번만 참조되면 보통 부모 쿼리에 접어 넣어 함께 최적화할 수 있다. 여러 번 참조되면 기본적으로 한 번 계산해 재사용하는 쪽을 택한다.

MATERIALIZED는 만능 가속 옵션이 아니다

CTE의 MATERIALIZED는 결과를 문장 안에서 따로 계산하도록 강제하는 최적화 경계다. 비싼 계산을 여러 번 참조하거나 의도적으로 부모 조건의 영향을 차단할 때 유용할 수 있지만, 필터가 원본 스캔으로 내려가지 못해 더 많은 행을 임시 저장할 수도 있다. 반대로 NOT MATERIALIZED는 중복 계산 가능성을 감수하고 부모 쿼리와 공동 최적화를 허용한다.

-- 같은 집계를 두 번 쓰므로 한 번 계산해 재사용한다.
WITH season_score AS MATERIALIZED (
  SELECT player_id, SUM(score_delta)::bigint AS score
  FROM score_event
  WHERE season_id = 1
  GROUP BY player_id
)
SELECT a.player_id, a.score, b.score AS compared_score
FROM season_score AS a
JOIN season_score AS b ON b.player_id = 2
WHERE a.player_id = 1;

-- 선택도가 높은 조건을 원본까지 밀어 넣고 싶다면 비교 측정한다.
WITH events AS NOT MATERIALIZED (
  SELECT * FROM score_event WHERE season_id = 1
)
SELECT SUM(score_delta) FROM events WHERE player_id = 1;

힌트를 넣기 전후에 EXPLAIN (ANALYZE, BUFFERS)로 실제 시간, 읽은 버퍼, 반복 횟수를 비교한다. 데이터 분포와 PostgreSQL 버전이 바뀌면 최적 계획도 바뀔 수 있다.

재귀 CTE로 퀘스트 선행 경로 탐색

재귀 CTE는 기준 항의 행에서 시작해 재귀 항을 반복한다. ‘용의 둥지’를 열기 위해 거쳐야 할 선행 퀘스트를 현재 퀘스트에서 부모 방향으로 따라간다. UNION ALL은 중복 제거 비용이 없으므로 경로 배열로 순환을 직접 차단한다.

WITH RECURSIVE quest_path AS (
  SELECT q.quest_id, q.quest_name, q.prerequisite_id,
         0 AS depth, ARRAY[q.quest_id] AS path
  FROM quest AS q
  WHERE q.quest_id = 4

  UNION ALL

  SELECT parent.quest_id, parent.quest_name, parent.prerequisite_id,
         child.depth + 1, child.path || parent.quest_id
  FROM quest_path AS child
  JOIN quest AS parent ON parent.quest_id = child.prerequisite_id
  WHERE NOT parent.quest_id = ANY(child.path)
)
SELECT depth, quest_id, quest_name
FROM quest_path
ORDER BY depth DESC;

depth | quest_id | quest_name
------+----------+-------------
3     | 1        | 초보자의 숲
2     | 2        | 고블린 소굴
1     | 3        | 오크 요새
0     | 4        | 용의 둥지

깊이를 무제한 신뢰하지 않는다. 잘못된 순환 데이터는 path 검사로 막고, 서비스 정책상 최대 선행 단계가 있다면 child.depth < 100 같은 방어 조건도 추가한다. 쓰기 시점에는 같은 퀘스트 자기 참조와 순환을 별도로 검증하는 편이 낫다.

View — 항상 최신이지만 계산 결과는 저장하지 않는다

View는 자주 쓰는 쿼리와 공개 가능한 열을 이름으로 감싼다. 아래 View는 이메일을 노출하지 않고 밴 사용자를 제외한다. 원본 player가 바뀌면 다음 조회에 즉시 반영된다.

CREATE VIEW public_player
WITH (security_barrier = true) AS
SELECT player_id, display_name
FROM player
WHERE NOT is_banned;

SELECT * FROM public_player ORDER BY player_id;

-- 운영 예시: 기본 생성자 권한 View에는 원본 권한 없이 View만 허용할 수 있다.
-- GRANT SELECT ON public_player TO game_reader;

-- 호출자 권한과 호출자의 RLS로 평가해야 할 때 선택한다.
-- ALTER VIEW public_player SET (security_invoker = true);
-- 이 경우 game_reader에도 기반 테이블 SELECT 권한이 필요하다.

player_id | display_name
----------+-------------
1         | 달빛검사
2         | 숲의궁수

security_barrier는 View 조건이 사용자 제공 함수보다 먼저 평가되어야 하는 보안 목적에 쓸 수 있다. security_invoker=true이면 기반 릴레이션 권한과 행 수준 보안 정책을 호출 사용자 기준으로 확인한다. 기본값은 생성자 권한 방식이다. 단순히 민감 열을 SELECT 목록에서 뺐다고 보안이 완성되지는 않으므로 소유자, GRANT, 함수의 leakproof 특성, RLS를 함께 검토한다.

Materialized View — 리더보드 결과를 저장한다

시즌 점수 이벤트가 수천만 건이면 매 요청마다 SUM과 정렬을 수행하기 어렵다. Materialized View는 마지막 갱신 시점의 집계 결과를 저장하고 자체 인덱스를 가질 수 있다.

CREATE MATERIALIZED VIEW leaderboard_mv AS
SELECT e.season_id, e.player_id, p.display_name,
       SUM(e.score_delta)::bigint AS score,
       statement_timestamp() AS refreshed_at
FROM score_event AS e
JOIN player AS p USING (player_id)
WHERE NOT p.is_banned
GROUP BY e.season_id, e.player_id, p.display_name
WITH NO DATA;

CREATE UNIQUE INDEX uq_leaderboard_mv
ON leaderboard_mv(season_id, player_id);

CREATE INDEX ix_leaderboard_mv_rank
ON leaderboard_mv(season_id, score DESC, player_id);

-- 최초 적재는 CONCURRENTLY 없이 실행한다.
REFRESH MATERIALIZED VIEW leaderboard_mv;

SELECT player_id, display_name, score
FROM leaderboard_mv
WHERE season_id = 1
ORDER BY score DESC, player_id
LIMIT 100;

player_id | display_name | score
----------+--------------+------
1         | 달빛검사     | 200
2         | 숲의궁수     | 150

이후 읽기를 계속 허용하며 갱신하려면 조건 없는 UNIQUE 인덱스를 만든 상태에서 다음을 실행한다.

REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard_mv;

SELECT MAX(
  EXTRACT(EPOCH FROM (statement_timestamp() - refreshed_at))
)::numeric(12,2) AS staleness_seconds
FROM leaderboard_mv;

갱신 비용과 오래됨을 함께 예산화하기

staleness(t)=t-t_{last\ refresh}

refresh\ duty\ ratio=\frac{T_{refresh}}{I_{refresh}}

여기서 $T_{refresh}$ 는 한 번 갱신하는 데 걸린 시간, $I_{refresh}$ 는 갱신 시작 간격이다. 예를 들어 갱신에 40초가 걸리는데 60초마다 실행하면 비율은 $40/60=0.67$ 이다. 여기에 원본 스캔 I/O, 임시 파일, WAL, CPU와 읽기 지연을 함께 측정한다. 갱신 시간이 간격에 가까워지면 작업 중첩을 막고 주기를 늘리거나 증분 읽기 모델을 검토한다.

refresh\ interval \le SLO_{staleness}-T_{refresh}

이는 스케줄 대기와 갱신 실행을 모두 포함해 오래됨 SLO 안에 넣는 단순 상한이다. 원본 이벤트가 REFRESH 도중에도 들어오므로 실제 경계는 격리 스냅샷과 스케줄러 지연까지 관찰해야 한다.

보안·최신성·성능을 한 표에서 비교

관점	View	Materialized View
최신성	문장 스냅샷 기준 원본 최신	마지막 완료 REFRESH 기준
성능	매 조회 원본 계산, 부모 조건 최적화 가능	읽기 빠름, 저장·REFRESH 비용 발생
보안	GRANT·security_invoker·security_barrier·RLS 검토	저장된 행 자체에 권한 부여, 민감 값 복제 주의

Materialized View는 일반 View처럼 보안 경계를 자동으로 대신하지 않는다. REFRESH 실행 역할이 원본을 읽을 권한, 결과를 조회하는 역할의 GRANT, 결과에 복제된 개인정보의 보존·삭제 정책을 따로 설계한다.

결정과 검증 체크리스트

한 문장만 읽기 쉽게 나누려면 일반 CTE, 계층을 반복 탐색하면 재귀 CTE를 사용한다.
MATERIALIZED 또는 NOT MATERIALIZED는 EXPLAIN 결과가 근거일 때만 지정한다.
항상 최신이어야 하고 원본 계산이 SLO 안이면 View를 사용하고 열·행·권한 경계를 테스트한다.
반복 집계가 비싸고 오래됨이 허용되면 Materialized View를 사용하며 REFRESH 시간, I/O, 잠금, staleness를 관찰한다.
리더보드 상위 100 조회는 원본·View·Materialized View의 결과를 대조하고 동점 정렬 키까지 같게 만든다.

참고 자료

PostgreSQL 공식 문서 — WITH Queries (2026-08-11 확인): 일반·재귀 CTE 평가와 MATERIALIZED/NOT MATERIALIZED 동작.
PostgreSQL 공식 문서 — CREATE VIEW (2026-08-11 확인): security_barrier, security_invoker와 View 정의.
PostgreSQL 공식 문서 — Materialized Views (2026-08-11 확인): 결과 저장과 일반 View의 차이.
PostgreSQL 공식 문서 — REFRESH MATERIALIZED VIEW (2026-08-11 확인): CONCURRENTLY의 UNIQUE 인덱스 조건과 동시 갱신 제한.
PostgreSQL 공식 문서 — Row Security Policies (2026-08-11 확인): 호출 역할과 행 수준 보안 정책의 평가.

댓글을 불러오는 중…