5편 · 약 14분

스키마 설계 패턴과 Materialized View

스키마 설계 원칙

ClickHouse의 스키마 설계는 관계형 DB의 정규화 원칙과 정반대 방향으로 출발한다.

비정규화 우선

ClickHouse는 조인이 느리다. 분산 조인은 더 느리다. 설계 목표는 쿼리 시점 조인을 삽입 시점 비정규화로 치환하는 것이다.

-- 나쁨: 쿼리마다 users 조인
SELECT u.country, count() FROM events e JOIN users u ON e.user_id = u.id GROUP BY u.country;

-- 좋음: 삽입 시 country를 이미 events에 복제
SELECT country, count() FROM events GROUP BY country;

같은 값이 여러 행에 반복되어도 열 지향 압축이 효과적으로 중복을 제거하므로 스토리지 비용 증가는 생각보다 작다.

ORDER BY 컬럼 순서

ORDER BY는 스파스 인덱스의 정렬 키이자 MergeTree 병합 기준이다. 순서 설계 규칙:

필터에 자주 쓰는 저카디널리티 컬럼을 앞에 (예: region, service_name)
고카디널리티 컬럼은 뒤에 (예: user_id, request_id)
타임스탬프는 마지막 — 최신 데이터 집중 접근 시 앞에 두는 경우도 있지만, 범위 쿼리가 타임스탬프 + 다른 필터 조합인 경우 저카디널리티 필터를 앞에 두는 것이 전체 그래뉼 제거 효율이 높다.

-- 이벤트 로그 테이블 예시
ORDER BY (region, service, event_date, user_id)
--        ^저카디          ^중간      ^날짜   ^고카디

LowCardinality 타입

문자열 컬럼의 고유값이 10,000개 미만이면 LowCardinality(String)을 쓴다. 내부적으로 딕셔너리 인코딩이 적용되어 디스크 사용량과 쿼리 속도가 동시에 개선된다.

status         LowCardinality(String),   -- 'active', 'inactive' 등
country_code   LowCardinality(String),   -- ISO 국가 코드
event_type     LowCardinality(String),

Nullable은 성능을 저해한다. NULL이 필요 없으면 기본값(0, 빈 문자열)을 쓰고 Nullable을 피한다.

파티셔닝과 TTL

파티셔닝은 인덱스가 아니다. 파티션 프루닝으로 읽지 않아도 될 파티션 전체를 건너뛸 때 유효하다.

대부분의 시계열 테이블: PARTITION BY toYYYYMM(event_date) (월별)
매우 고용량 테이블: toYYYYMMDD(event_date) (일별)
너무 잘게 쪼개면 파티션 수가 폭증해 오히려 느려진다.

TTL로 오래된 파티션을 자동 삭제한다.

TTL event_date + INTERVAL 90 DAY

Materialized View 동작 원리

증분 업데이트 메커니즘

ClickHouse의 Materialized View(MV)는 RDBMS의 전체 재계산 뷰와 다르다. 삽입 시점에 증분으로 트리거된다.

INSERT 발생
    ↓
새 파트 생성
    ↓
MV 쿼리가 해당 새 파트 블록에만 적용됨
    ↓
결과가 타깃 테이블에 INSERT됨

전체 소스 테이블을 재스캔하지 않는다. 소스 테이블에 새 데이터가 들어올 때만 그 블록에 대해 MV의 SELECT 쿼리가 실행된다. 따라서 삽입이 빠르고 SELECT는 미리 계산된 결과만 읽는다.

소스 → MV → 타깃 테이블 패턴

MV는 뷰처럼 보이지만 실제로는 데이터를 다른 테이블에 쓴다. 권장 구조는 항상 세 개의 객체다.

소스 테이블 (MergeTree) 원시 이벤트 저장

→

Materialized View INSERT 트리거 + 변환 쿼리

→

타깃(백킹) 테이블 AggregatingMergeTree 등

↓ 쿼리는 타깃 테이블만 SELECT

Materialized View 삼각 구조

-- 1. 소스 테이블 (원시 이벤트)
CREATE TABLE events (
    event_date  Date,
    user_id     UInt64,
    event_type  LowCardinality(String),
    value       Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, event_date, user_id);

-- 2. 타깃(백킹) 테이블 — MV보다 먼저 만들어야 함
CREATE TABLE events_hourly_agg (
    event_date  Date,
    hour        UInt8,
    event_type  LowCardinality(String),
    cnt         AggregateFunction(count),
    uv          AggregateFunction(uniq, UInt64),
    total_value AggregateFunction(sum,  Float64)
) ENGINE = AggregatingMergeTree()
ORDER BY (event_type, event_date, hour);

-- 3. Materialized View (소스 → 타깃 연결)
CREATE MATERIALIZED VIEW events_hourly_mv
TO events_hourly_agg
AS
SELECT
    event_date,
    toHour(now())     AS hour,
    event_type,
    countState()      AS cnt,
    uniqState(user_id) AS uv,
    sumState(value)   AS total_value
FROM events
GROUP BY event_date, hour, event_type;

AggregatingMergeTree와 -State / -Merge 패턴

단순 합산이면 SummingMergeTree로 충분하지만, 고유값 수(uniq), 평균(avg), 퍼센타일(quantile) 같은 복잡한 집계는 중간 상태를 저장해야 한다. 여기서 AggregatingMergeTree와 *State / *Merge 함수 쌍을 쓴다.

단계	함수	역할
삽입	`countState()`, `uniqState(col)`, `avgState(col)`	집계 중간 상태를 이진 형태로 저장
조회	`countMerge(cnt)`, `uniqMerge(uv)`, `avgMerge(avg_v)`	저장된 상태를 병합해 최종 결과 반환

-- 조회 시 항상 *Merge 함수 + GROUP BY
SELECT
    event_date,
    hour,
    event_type,
    countMerge(cnt)       AS requests,
    uniqMerge(uv)         AS unique_users,
    sumMerge(total_value) AS revenue
FROM events_hourly_agg
GROUP BY event_date, hour, event_type
ORDER BY event_date, hour;

uniq는 카디널리티를 근사 계산(HyperLogLog)하므로 정확한 카운트가 필요하면 uniqExact를 쓴다. 단, 메모리와 속도 트레이드오프가 있다.

실용 MV 패턴

패턴 1: 이벤트 필터링 / 라우팅

MV를 필터로 쓰면 조건에 맞는 이벤트만 별도 테이블에 분리할 수 있다.

-- error 이벤트만 별도 보관
CREATE MATERIALIZED VIEW errors_mv TO errors_table AS
SELECT * FROM events WHERE event_type = 'error';

패턴 2: 실시간 집계 대시보드

분당 API 요청 수 실시간 집계.

CREATE TABLE api_stats_1m (
    ts         DateTime,
    endpoint   LowCardinality(String),
    cnt        AggregateFunction(count),
    p95_ms     AggregateFunction(quantile(0.95), Float64)
) ENGINE = AggregatingMergeTree()
ORDER BY (endpoint, ts);

CREATE MATERIALIZED VIEW api_stats_mv TO api_stats_1m AS
SELECT
    toStartOfMinute(event_time) AS ts,
    endpoint,
    countState()                AS cnt,
    quantileState(0.95)(latency_ms) AS p95_ms
FROM api_requests
GROUP BY ts, endpoint;

조회:

SELECT ts, endpoint,
       countMerge(cnt)          AS requests,
       quantileMerge(0.95)(p95_ms) AS p95
FROM api_stats_1m
WHERE ts >= now() - INTERVAL 1 HOUR
GROUP BY ts, endpoint
ORDER BY ts;

패턴 3: 비정규화(Lookup 테이블 병합)

삽입 시점에 사용자 정보를 이벤트에 조인해 단일 평평한 테이블을 만든다.

CREATE MATERIALIZED VIEW enriched_events_mv TO enriched_events AS
SELECT
    e.event_date,
    e.user_id,
    u.country,
    u.plan,
    e.event_type,
    e.value
FROM events e
JOIN users u ON e.user_id = u.user_id;

주의: MV 내 JOIN은 소스 테이블의 INSERT 블록이 작을수록 효율이 낮다. 배치 삽입을 크게 유지해야 한다.

주요 주의사항

타깃 테이블을 MV보다 먼저 생성 — 순서가 바뀌면 CREATE MATERIALIZED VIEW가 실패한다.
POPULATE 키워드 주의 — CREATE MATERIALIZED VIEW ... POPULATE는 기존 데이터를 채워 넣지만, 채우는 도중 새로 들어오는 INSERT는 MV에 반영되지 않아 누락이 생길 수 있다. 대규모 테이블에서는 수동 INSERT SELECT를 선호한다.
MV 체이닝 주의 — MV → MV → MV 체인은 디버깅이 어렵고 삽입 지연을 유발한다. 체이닝 대신 소스 테이블에서 직접 각 MV를 만드는 것이 권장된다.
GROUP BY 키가 ORDER BY와 일치해야 — AggregatingMergeTree에서 ORDER BY 외의 컬럼으로 GROUP BY 하면 배경 병합이 기대대로 상태를 통합하지 못한다.

한 줄 정리

ClickHouse 스키마 설계는 비정규화 + 저카디널리티 우선 ORDER BY로 기초를 잡고, Materialized View + AggregatingMergeTree의 -State/-Merge 패턴으로 삽입 시점 집계를 구현해 SELECT 비용을 거의 0에 가깝게 만드는 것이 핵심이다.

References

https://clickhouse.com/docs/data-modeling/schema-design
https://clickhouse.com/docs/materialized-view/incremental-materialized-view
https://github.com/ClickHouse/ClickHouse/blob/master/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree.md
https://clickhouse.com/blog/10-best-practice-tips
https://www.glassflow.dev/blog/clickhouse-materialized-views
https://medium.com/@lureilly1/clickhouse-schema-design-best-practices-for-high-performance-analytics-5c26998c7e56
https://kb.altinity.com/altinity-kb-schema-design/materialized-views/