โšก

Materialized View

์ตœ๋ฏผ์„ยท2026-05-31

Materialized View

ClickHouse

๐Ÿ” ClickHouse ์Šคํ„ฐ๋”” ์‹œ๋ฆฌ์ฆˆ

๊ฐœ์š”

4ํŽธ์—์„œ AggregatingMergeTree์— avgState๋กœ ์‚ฌ์ „ ์ง‘๊ณ„๋ฅผ ์†์œผ๋กœ ํ•œ ๋ฒˆ ์ ์žฌํ–ˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฒˆ ๊ธ€์˜ ์ฃผ์ œ๋Š” ๊ทธ ๋ณ€ํ™˜์„ INSERT ์‹œ์ ์— ์ž๋™ํ™”ํ•˜๋Š” Materialized View์ž…๋‹ˆ๋‹ค. ์ด๋ฆ„์˜ ๋œป๋ถ€ํ„ฐ INSERT ํŠธ๋ฆฌ๊ฑฐ ๋ฉ”์ปค๋‹ˆ์ฆ˜, ์ˆ˜๋™ ์ ์žฌ์™€์˜ ์ฐจ์ด, target ์ง์ ‘ ์“ฐ๊ธฐ๊นŒ์ง€ ๋กœ์ปฌ ์‹ค์ธก์œผ๋กœ ์ •๋ฆฌํ•ฉ๋‹ˆ๋‹ค.


Materialized View๋ž€ โ€” "ํŠธ๋ฆฌ๊ฑฐ + ๋ณ€ํ™˜ ๊ทœ์น™"

์ด๋ฆ„์ด ์˜คํ•ด๋ฅผ ๋ถ€๋ฅด๊ธฐ ์‰ฝ์Šต๋‹ˆ๋‹ค. ClickHouse์˜ MV๋Š” ํ…Œ์ด๋ธ”๋„, ์ผ๋ฐ˜ View๋„ ์•„๋‹™๋‹ˆ๋‹ค. ๊ฐ€์žฅ ์ •ํ™•ํ•œ ์ •์˜๋Š” ์ด๋ ‡์Šต๋‹ˆ๋‹ค.

MV = "source์— INSERT๋˜๋ฉด, ๊ทธ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณ€ํ™˜(SELECT)ํ•ด target์— ์ ์žฌํ•˜๋Š” ํŠธ๋ฆฌ๊ฑฐ"

MV ์“ฐ๊ธฐยท๋ณ€ํ™˜ ํ๋ฆ„

  • MV ์ž์ฒด๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ–์ง€ ์•Š์Šต๋‹ˆ๋‹ค. "๋ณ€ํ™˜ ๊ทœ์น™(SELECT) + ํŠธ๋ฆฌ๊ฑฐ"์ผ ๋ฟ์ด๊ณ , ์‹ค์ œ ๋ฐ์ดํ„ฐ๋Š” target ํ…Œ์ด๋ธ”์— ์ €์žฅ๋ฉ๋‹ˆ๋‹ค.
  • ๊ทธ๋ž˜์„œ ETL๋กœ ๋ณด๋ฉด MV๋Š” T(Transform), source๋Š” ๋ฐ์ดํ„ฐ๊ฐ€ ๋“ค์–ด์˜ค๋Š” ๊ณณ, target์€ ๋ณ€ํ™˜ ๊ฒฐ๊ณผ๊ฐ€ ์Œ“์ด๋Š” ๊ณณ์ž…๋‹ˆ๋‹ค.

์ด๋ฆ„์˜ ๋œป

  • View(๋ทฐ): "๋ฐ์ดํ„ฐ๋ฅผ ๋ฐ”๋ผ๋ณด๋Š” ๋ณ€ํ™˜๋œ ๊ด€์ "(raw๋ฅผ ๋‚ ์งœ๋ณ„ ์ง‘๊ณ„๋กœ ๋ณธ ๋ชจ์Šต). ์ผ๋ฐ˜ View๋Š” ์ด ์ฟผ๋ฆฌ๋งŒ ์ €์žฅํ•˜๊ณ , ์กฐํšŒํ•  ๋•Œ๋งˆ๋‹ค ์›๋ณธ์—์„œ ๋‹ค์‹œ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.
  • Materialized(๊ตฌ์ฒดํ™”๋œ): ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์‹ค์ œ๋กœ ๋ฌผ๋ฆฌ์ ์œผ๋กœ ์ €์žฅํ•œ๋‹ค๋Š” ๋œป์ž…๋‹ˆ๋‹ค.
์ผ๋ฐ˜ View Materialized View
์ €์žฅ ์ฟผ๋ฆฌ(SELECT)๋งŒ ๊ฒฐ๊ณผ ๋ฐ์ดํ„ฐ (target์—)
์กฐํšŒ ์‹œ ๋งค๋ฒˆ ์›๋ณธ ์žฌ๊ณ„์‚ฐ ๋ฏธ๋ฆฌ ๊ณ„์‚ฐ๋œ ๊ฒฐ๊ณผ๋ฅผ ์ฝ์Œ
์†๋„ ์›๋ณธ ํฌ๊ธฐ์— ๋น„๋ก€ ์‚ฌ์ „ ์ง‘๊ณ„๋ผ ๋น ๋ฆ„

์ฆ‰ **"๋ณ€ํ™˜ํ•œ ๊ฒฐ๊ณผ(View)๋ฅผ ๋งค๋ฒˆ ๊ณ„์‚ฐํ•˜์ง€ ์•Š๊ณ  ๋ฏธ๋ฆฌ ์ €์žฅ(Materialized)ํ•ด๋‘๋Š” ๊ฒƒ"**์ž…๋‹ˆ๋‹ค. ClickHouse๋Š” ์—ฌ๊ธฐ์— "INSERT ํŠธ๋ฆฌ๊ฑฐ๋กœ ์ฆ๋ถ„ ๊ตฌ์ฒดํ™”"๋ฅผ ๊ฒฐํ•ฉํ–ˆ์Šต๋‹ˆ๋‹ค.


์‹ค์Šต: ์ž๋™ ์ง‘๊ณ„ ํŒŒ์ดํ”„๋ผ์ธ

source(raw) โ†’ MV(๋ณ€ํ™˜) โ†’ target(์ง‘๊ณ„) 3๋‹จ์„ ๋งŒ๋“ญ๋‹ˆ๋‹ค.

-- source: ์›๋ณธ ์ด๋ฒคํŠธ
CREATE TABLE events_raw
(
    ts DateTime, user_id UInt32,
    event_type LowCardinality(String),
    duration_ms UInt32
)
ENGINE = MergeTree ORDER BY (event_type, ts);

-- target: ์ง‘๊ณ„ ๊ฒฐ๊ณผ๊ฐ€ ์Œ“์ผ ๊ณณ (AggregatingMergeTree)
CREATE TABLE events_daily
(
    event_date   Date,
    event_type   LowCardinality(String),
    cnt          AggregateFunction(count),
    avg_duration AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree ORDER BY (event_date, event_type);

-- MV: events_raw์— INSERT๋˜๋ฉด ์ž๋™ ์ง‘๊ณ„ํ•ด events_daily๋กœ
CREATE MATERIALIZED VIEW events_mv TO events_daily AS
SELECT
    toDate(ts)            AS event_date,
    event_type,
    countState()          AS cnt,
    avgState(duration_ms) AS avg_duration
FROM events_raw
GROUP BY event_date, event_type;

์ด์ œ source์—๋งŒ INSERTํ•˜๋ฉด target์ด ์ž๋™์œผ๋กœ ์ฑ„์›Œ์ง‘๋‹ˆ๋‹ค.

INSERT INTO events_raw SELECT ts, user_id, event_type, duration_ms FROM events;
-- 5์ฒœ๋งŒ ํ–‰ ์ ์žฌ

SELECT count() FROM events_daily;
-- 1830  (366์ผ ร— 5 event_type) โ€” target์„ ์ง์ ‘ INSERTํ•œ ์  ์—†๋Š”๋ฐ ์ฑ„์›Œ์ง

SELECT event_date, event_type,
       countMerge(cnt)        AS events,
       avgMerge(avg_duration) AS avg_duration
FROM events_daily GROUP BY event_date, event_type
ORDER BY event_date, event_type LIMIT 1;
-- 2025-05-29 | click | 16195 | 2494.67

events_daily๋ฅผ ์ง์ ‘ ๊ฑด๋“œ๋ฆฐ ์ ์ด ์—†๋Š”๋ฐ ์ง‘๊ณ„๊ฐ€ ์ฑ„์›Œ์กŒ์Šต๋‹ˆ๋‹ค. MV๊ฐ€ INSERT๋ฅผ ๊ฐ€๋กœ์ฑ„ ์ž๋™ ๋ณ€ํ™˜ํ•œ ๊ฒƒ์ž…๋‹ˆ๋‹ค.


INSERT์—๋งŒ ๋ฐ˜์‘ํ•œ๋‹ค

MV์˜ ๊ฐ€์žฅ ์ค‘์š”ํ•œ ์„ฑ์งˆ์ด์ž ๊ฐ€์žฅ ๋งŽ์ด ์˜คํ•ดํ•˜๋Š” ์ง€์ ์ž…๋‹ˆ๋‹ค. MV๋Š” ์˜ค์ง INSERT์—๋งŒ ํŠธ๋ฆฌ๊ฑฐ๋ฉ๋‹ˆ๋‹ค.

graph TD INS["INSERT (์ƒˆ ๋ธ”๋ก)"] ==>|"ํŠธ๋ฆฌ๊ฑฐ O"| MV["MV ๋ณ€ํ™˜ โ†’ target"] UPD["ALTER UPDATE / DELETE"] -.->|"ํŠธ๋ฆฌ๊ฑฐ X"| MV MRG["๋ฐฑ๊ทธ๋ผ์šด๋“œ ๋จธ์ง€ / TTL"] -.->|"ํŠธ๋ฆฌ๊ฑฐ X"| MV
source์— ์ผ์–ด๋‚œ ์ผ MV ๋ฐ˜์˜?
INSERT โœ… ๋ฐ˜์˜
ALTER ... UPDATE / DELETE โŒ
TRUNCATE / DROP โŒ
๋จธ์ง€ / TTL ์‚ญ์ œ โŒ

์™œ INSERT๋งŒ์ธ๊ฐ€ (๋ฉ”์ปค๋‹ˆ์ฆ˜)

MV๋Š” source์˜ ์“ฐ๊ธฐ ๊ฒฝ๋กœ(write path)์— hook์œผ๋กœ ๋ถ™์Šต๋‹ˆ๋‹ค. source์— ์ƒˆ ๋ธ”๋ก์ด INSERT๋  ๋•Œ, ClickHouse๋Š” ๊ทธ ๋ธ”๋ก์„ MV SELECT์˜ ์ž…๋ ฅ(FROM)์œผ๋กœ ๋„˜๊ฒจ ๋ณ€ํ™˜ ํ›„ target์— ์”๋‹ˆ๋‹ค. ํ•ต์‹ฌ์€ MV๊ฐ€ ๋ณด๋Š” ๊ฒŒ **"source ์ „์ฒด"๊ฐ€ ์•„๋‹ˆ๋ผ "๋ฐฉ๊ธˆ INSERT๋œ ๊ทธ ๋ธ”๋ก"**์ด๋ผ๋Š” ์ ์ž…๋‹ˆ๋‹ค.

UPDATE/DELETE๋Š” INSERT ๊ฒฝ๋กœ๊ฐ€ ์•„๋‹ˆ๋ผ mutation(๊ธฐ์กด part๋ฅผ ์ฝ์–ด ์žฌ์ž‘์„ฑํ•˜๋Š” ๋ณ„๋„ ๊ฒฝ๋กœ)์ด๋ผ ์ด hook์„ ํƒ€์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ๋จธ์ง€ยทTTL ์‚ญ์ œ๋„ ์“ฐ๊ธฐ ๊ฒฝ๋กœ๊ฐ€ ์•„๋‹ˆ๋ผ ๋ฐฑ๊ทธ๋ผ์šด๋“œ part ์—ฐ์‚ฐ์ด๋ผ ํŠธ๋ฆฌ๊ฑฐ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

ํ•จ์˜ โ€” append-only ๊ฐ€์ •

๊ทธ๋ž˜์„œ ์ผ๋ฐ˜ MV๋Š” **"ํ•œ๋ฒˆ ๋“ค์–ด์˜จ ๋ฐ์ดํ„ฐ๋Š” ๋ฐ”๋€Œ์ง€ ์•Š๋Š”๋‹ค"**๋Š” ์ „์ œ ์œ„์—์„œ ๋™์ž‘ํ•ฉ๋‹ˆ๋‹ค. ์ด๋ฒคํŠธ ๋กœ๊ทธยท๋ฉ”ํŠธ๋ฆญ์ฒ˜๋Ÿผ append๋งŒ ๋˜๋Š” ๋ฐ์ดํ„ฐ์— ๋งž์Šต๋‹ˆ๋‹ค. source์—์„œ ํ–‰์„ ์ง€์›Œ๋„ target ์ง‘๊ณ„์—” ๊ทธ ํ–‰์ด ๋‚จ์•„ ๋ถˆ์ผ์น˜๊ฐ€ ์ƒ๊น๋‹ˆ๋‹ค. ์ˆ˜์ •ยท์‚ญ์ œ ๋ฐ˜์˜์ด ํ•„์š”ํ•˜๋ฉด Refreshable MV(์ฃผ๊ธฐ์ ์œผ๋กœ ์ „์ฒด๋ฅผ ๋‹ค์‹œ ๊ณ„์‚ฐํ•˜๋Š” ๋ฐฐ์น˜ํ˜• MV)๋‚˜ CollapsingMergeTree(์ทจ์†Œ ์ด๋ฒคํŠธ -1์„ appendํ•ด ์ƒ์‡„)๋กœ ํ’€๊ฑฐ๋‚˜, ์žฌ์ง‘๊ณ„ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

์ฐธ๊ณ ๋กœ ์ผ๋ฐ˜ MV๋Š” INSERT ์‹œ์ ์— ๋™๊ธฐ์ ยท์‹ค์‹œ๊ฐ„์œผ๋กœ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค(๋ฐฑ๊ทธ๋ผ์šด๋“œ ๋ฐฐ์น˜๊ฐ€ ์•„๋‹˜). INSERT๊ฐ€ ๋๋‚œ ์‹œ์ ์—” ์ด๋ฏธ target์ด ๊ฐฑ์‹ ๋ผ ์žˆ์Šต๋‹ˆ๋‹ค. ๋Œ€์‹  MV ๋ณ€ํ™˜ ๋น„์šฉ์ด INSERT ์ง€์—ฐ์— ํฌํ•จ๋ฉ๋‹ˆ๋‹ค.


์ˆ˜๋™ INSERT SELECT์™€ ๋ฌด์—‡์ด ๋‹ค๋ฅธ๊ฐ€

"4ํŽธ์—์„œ ์†์œผ๋กœ ํ•œ INSERT INTO ... SELECT ... GROUP BY๋„ ์ž๋™ ์•„๋‹ˆ์—ˆ๋‚˜?"๋ผ๋Š” ์˜๋ฌธ์ด ๋“ค ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๊ทธ๊ฑด ์ˆ˜๋™ 1ํšŒ ์‹คํ–‰์ด์—ˆ์Šต๋‹ˆ๋‹ค. ์ฐจ์ด๋Š” ๋‘ ๊ฐ€์ง€์ž…๋‹ˆ๋‹ค.

โ‘  ๋ˆ„๊ฐ€ ์‹คํ–‰ํ•˜๋‚˜ โ€” ๊ทธ INSERT SELECT ๋ฌธ์žฅ์€ ์Šค์Šค๋กœ ์‹คํ–‰๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ์‚ฌ๋žŒยท์•ฑยท์Šค์ผ€์ค„๋Ÿฌ๊ฐ€ ๋งค๋ฒˆ ํ˜ธ์ถœํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. MV๋Š” INSERT ์ž์ฒด๊ฐ€ ํŠธ๋ฆฌ๊ฑฐ๋ผ ๋ณ„๋„ ํ˜ธ์ถœ์ด ์—†์Šต๋‹ˆ๋‹ค.

โ‘ก ์ฆ๋ถ„์ธ๊ฐ€ โ€” FROM events GROUP BY๋Š” ๋งค๋ฒˆ ์ „์ฒด๋ฅผ ์žฌ์ง‘๊ณ„ํ•ฉ๋‹ˆ๋‹ค. ์‹ ๊ทœ 1,000๊ฑด ๋•Œ๋ฌธ์— 5์ฒœ๋งŒ์„ ๋‹ค์‹œ ์ง‘๊ณ„ํ•˜๊ณ , ๊ฒฐ๊ณผ๋ฅผ ํ†ต์งธ๋กœ ๋˜ INSERTํ•˜๋ฉด ์ค‘๋ณต์ด ์Œ“์ž…๋‹ˆ๋‹ค. ์ฆ๋ถ„์œผ๋กœ ํ•˜๋ ค๋ฉด WHERE ts >= '๋งˆ์ง€๋ง‰ ์ฒ˜๋ฆฌ ์‹œ์ '์„ ์ง์ ‘ ์ถ”์ ํ•ด์•ผ ํ•˜๋Š”๋ฐ(์›Œํ„ฐ๋งˆํฌ ๊ด€๋ฆฌ), ์–ด๊ธ‹๋‚˜๋ฉด ๋ˆ„๋ฝยท์ค‘๋ณต์ด ๋‚ฉ๋‹ˆ๋‹ค. MV๋Š” ๋“ค์–ด์˜จ ๋ธ”๋ก๋งŒ ์ฒ˜๋ฆฌํ•˜๋ฏ€๋กœ ์›Œํ„ฐ๋งˆํฌ ๊ด€๋ฆฌ๊ฐ€ ํ•„์š” ์—†์Šต๋‹ˆ๋‹ค.

์ˆ˜๋™ INSERT SELECT MV
์‹คํ–‰ ์ฃผ์ฒด ์‚ฌ๋žŒ/์•ฑ/์Šค์ผ€์ค„๋Ÿฌ INSERT๊ฐ€ ์ž๋™
์ง‘๊ณ„ ๋ฒ”์œ„ ์ „์ฒด ์žฌ์ง‘๊ณ„ ๋“ค์–ด์˜จ ๋ธ”๋ก๋งŒ (์ฆ๋ถ„)
์›Œํ„ฐ๋งˆํฌ ์ถ”์  ์‚ฌ๋žŒ์ด ๋ถˆํ•„์š”
์‹ ์„ ๋„ ๋งˆ์ง€๋ง‰ ์‹คํ–‰ ์‹œ์ ์— ๋ฉˆ์ถค ํ•ญ์ƒ ์ตœ์‹ 

target์€ ์ผ๋ฐ˜ ํ…Œ์ด๋ธ”์ด๋‹ค

MV๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ–์ง€ ์•Š์œผ๋ฏ€๋กœ, **"MV์— ์“ด๋‹ค"๊ฐ€ ์•„๋‹ˆ๋ผ "target์— ์“ด๋‹ค"**๊ฐ€ ๋งž์Šต๋‹ˆ๋‹ค. target(events_daily)์€ ํ‰๋ฒ”ํ•œ ํ…Œ์ด๋ธ”์ด๋ผ ์‚ฌ๋žŒ์ด ์ง์ ‘ INSERTํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. MV๋Š” ๊ทธ ํ…Œ์ด๋ธ”์— ์“ฐ๋Š” ๊ฒฝ๋กœ ์ค‘ ํ•˜๋‚˜์ผ ๋ฟ์ž…๋‹ˆ๋‹ค.

๊ฐ€์žฅ ํ”ํ•œ ์šฉ๋ก€๊ฐ€ **๋ฐฑํ•„(backfill)**์ž…๋‹ˆ๋‹ค. MV๋Š” "๋งŒ๋“  ์‹œ์  ์ดํ›„ INSERT"๋งŒ ์žก์œผ๋‹ˆ, ๊ณผ๊ฑฐ ๋ฐ์ดํ„ฐ๋Š” target์— ์ง์ ‘ ์ ์žฌํ•ฉ๋‹ˆ๋‹ค.

-- MV ์ƒ์„ฑ ํ›„, ๊ณผ๊ฑฐ ๋ฐ์ดํ„ฐ๋Š” target์— ์ง์ ‘ ๋ฐฑํ•„ (์Šคํ‚ค๋งˆ๋Š” -State๋กœ ๋งž์ถค)
INSERT INTO events_daily
SELECT toDate(ts), event_type, countState(), avgState(duration_ms)
FROM events_raw   -- ์ด๋ฏธ ์Œ“์—ฌ ์žˆ๋˜ ๊ณผ๊ฑฐ ๋ฐ์ดํ„ฐ
GROUP BY toDate(ts), event_type;

AggregatingMergeTree๋ผ ์ง์ ‘ ๋„ฃ์€ ์ƒํƒœ์™€ MV๊ฐ€ ๋„ฃ์€ ์ƒํƒœ๊ฐ€ -Merge๋กœ ์ž์—ฐ์Šค๋Ÿฝ๊ฒŒ ํ•ฉ์ณ์ง‘๋‹ˆ๋‹ค. (POPULATE ์˜ต์…˜์œผ๋กœ ์ƒ์„ฑ ์‹œ ๊ธฐ์กด ๋ฐ์ดํ„ฐ๋ฅผ ์ ์žฌํ•  ์ˆ˜๋„ ์žˆ์ง€๋งŒ, ์ ์žฌ ์ค‘ ๋“ค์–ด์˜จ INSERT๊ฐ€ ๋ˆ„๋ฝ๋  ์ˆ˜ ์žˆ์–ด "MV ๋จผ์ € + ์ง์ ‘ ๋ฐฑํ•„"์ด ๋” ์•ˆ์ „ํ•ฉ๋‹ˆ๋‹ค.)


์กฐํšŒ๋Š” ์™œ -Merge์ธ๊ฐ€, ๊ทธ๋ฆฌ๊ณ  ์ฆ๋ถ„ ๋™์ž‘

MV ์กฐํšŒ ํ๋ฆ„

target ์ปฌ๋Ÿผ์ด AggregateFunction ์ƒํƒœ๋ผ, ์กฐํšŒ ์‹œ -Merge๋กœ ํ™˜์‚ฐํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค(4ํŽธ). ์ด์œ ๊ฐ€ MV์˜ ๋™์ž‘๊ณผ ์ง๊ฒฐ๋ฉ๋‹ˆ๋‹ค.

MV๋Š” INSERT ๋ธ”๋ก๋งˆ๋‹ค target์— ๋ถ€๋ถ„ ์ƒํƒœ๋ฅผ ๋ณ„๋„ part๋กœ ์”๋‹ˆ๋‹ค. 5์ฒœ๋งŒ์„ ํ•œ ๋ฒˆ INSERTํ•ด๋„ ๋‚ด๋ถ€์ ์œผ๋กœ ์—ฌ๋Ÿฌ ๋ธ”๋ก โ†’ target์— ์—ฌ๋Ÿฌ part๊ฐ€ ์ƒ๊ธฐ๊ณ , ๊ฐ™์€ (event_date, event_type) ํ‚ค๊ฐ€ ์—ฌ๋Ÿฌ part์— ๋ถ€๋ถ„ ์ƒํƒœ๋กœ ํฉ์–ด์ง‘๋‹ˆ๋‹ค. ๊ทธ๊ฑธ ํ•ฉ์ณ์•ผ ์ •ํ™•ํ•œ ๊ฐ’์ด ๋‚˜์˜ค๋ฏ€๋กœ -Merge๊ฐ€ ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.

์ง์ ‘ ๋ณด๋ฉด ๋ช…ํ™•ํ•ฉ๋‹ˆ๋‹ค. ๊ธฐ์กด ํ‚ค์— ํ•œ ๊ฑด ๋” ๋„ฃ์œผ๋ฉด target์— ์ƒˆ part๊ฐ€ ์ƒ๊ธฐ๊ณ  ๊ทธ ํ‚ค๊ฐ€ ์ž ๊น ์—ฌ๋Ÿฌ ํ–‰์ด ๋ฉ๋‹ˆ๋‹ค.

INSERT INTO events_raw VALUES ('2025-05-29 10:00:00', 99999, 'click', 100);

-- ๊ทธ ํ‚ค๊ฐ€ 2ํ–‰์œผ๋กœ ๋ณด์ž„ (๊ธฐ์กด part + ๋ฐฉ๊ธˆ MV๊ฐ€ ๋งŒ๋“  ์ƒˆ part)
SELECT count() FROM events_daily WHERE event_date='2025-05-29' AND event_type='click';
-- 2

-- ๊ทธ๋ž˜๋„ -Merge๋Š” ํ•ฉ์ณ์„œ ์ •ํ™•: 16195 + 1 = 16196
SELECT countMerge(cnt) FROM events_daily
WHERE event_date='2025-05-29' AND event_type='click'
GROUP BY event_date, event_type;
-- 16196

events_raw์— INSERT๋งŒ ํ–ˆ๋Š”๋ฐ events_daily๊ฐ€ ์ฆ‰์‹œ ๋”ฐ๋ผ ๊ฐฑ์‹ ๋๊ณ (์ฆ๋ถ„), ๋ถ€๋ถ„ ์ƒํƒœ๊ฐ€ ์—ฌ๋Ÿฌ part๋กœ ๊ฐˆ๋ ธ์–ด๋„ -Merge๊ฐ€ ์ •ํ™•ํžˆ ํ•ฉ์ณ์ค๋‹ˆ๋‹ค. ์‹œ๊ฐ„์ด ์ง€๋‚˜๊ฑฐ๋‚˜ OPTIMIZE FINAL์„ ํ•˜๋ฉด ๋ฐฑ๊ทธ๋ผ์šด๋“œ ๋จธ์ง€๊ฐ€ ๋‹ค์‹œ 1ํ–‰์œผ๋กœ ํ†ตํ•ฉํ•ฉ๋‹ˆ๋‹ค.

๋งŒ์•ฝ sumยทmax ๊ฐ™์€ ๋‹จ์ˆœ ์ง‘๊ณ„๋งŒ ํ•„์š”ํ•˜๋ฉด SimpleAggregateFunction์œผ๋กœ ๋‘์–ด target์— ์ผ๋ฐ˜ ๊ฐ’์„ ์ €์žฅํ•˜๊ณ  -Merge ์—†์ด ์กฐํšŒํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค. avgยทuniq์ฒ˜๋Ÿผ ๋ถ€๋ถ„ ๊ฒฐ๊ณผ๋ฅผ ๋ชป ํ•ฉ์น˜๋Š” ์ง‘๊ณ„๋งŒ AggregateFunction + -Merge๊ฐ€ ํ•„์ˆ˜์ž…๋‹ˆ๋‹ค.


ํ•œ source, ์—ฌ๋Ÿฌ MV (fan-out)

source ํ•˜๋‚˜์— MV๋ฅผ ์—ฌ๋Ÿฌ ๊ฐœ ๋ถ™์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. INSERT ํ•œ ๋ฒˆ์— ์—ฌ๋Ÿฌ ์ง‘๊ณ„ ํ…Œ์ด๋ธ”์ด ๋™์‹œ์— ๊ฐฑ์‹ ๋ฉ๋‹ˆ๋‹ค โ€” ๊ด€์ธก์„ฑ ๋ฐฑ์—”๋“œ์˜ ํ•ต์‹ฌ ํŒจํ„ด์ž…๋‹ˆ๋‹ค.

ํ•œ source, ์—ฌ๋Ÿฌ MV (fan-out)

๋Œ€์‹œ๋ณด๋“œ๋งˆ๋‹ค ๋‹ค๋ฅธ ์‚ฌ์ „ ์ง‘๊ณ„๊ฐ€ ํ•„์š”ํ•  ๋•Œ, raw์— ํ•œ ๋ฒˆ ๋„ฃ์œผ๋ฉด ๊ฐ target์ด ์•Œ์•„์„œ ์ฑ„์›Œ์ง‘๋‹ˆ๋‹ค.


TL;DR

  1. MV๋Š” "INSERT ํŠธ๋ฆฌ๊ฑฐ + ๋ณ€ํ™˜ ๊ทœ์น™"์ด๋‹ค. ๋ฐ์ดํ„ฐ๋Š” target ํ…Œ์ด๋ธ”์ด ์ €์žฅํ•˜๊ณ , MV๋Š” source์˜ INSERT๋ฅผ ๋ฐ›์•„ ๋ณ€ํ™˜(ETL์˜ T)ํ•ด target์— ํ˜๋ ค๋ณด๋‚ธ๋‹ค.
  2. ์˜ค์ง INSERT๋œ ๋ธ”๋ก์—๋งŒ ๋ฐ˜์‘ํ•œ๋‹ค(UPDATE/DELETE/๋จธ์ง€/TTL ๋ฌด์‹œ). ๊ทธ๋ž˜์„œ ํ˜ธ์ถœ ์ฃผ์ฒด ์—†์ด ์ฆ๋ถ„์œผ๋กœ ๋™์ž‘ํ•˜์ง€๋งŒ, append-only ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ •ํ•œ๋‹ค.
  3. target์€ ์ผ๋ฐ˜ ํ…Œ์ด๋ธ”์ด๋ผ ์ง์ ‘ ๋ฐฑํ•„์ด ๊ฐ€๋Šฅํ•˜๊ณ , AggregateFunction ์ƒํƒœ๋ฅผ ์ €์žฅํ–ˆ๋‹ค๋ฉด ์กฐํšŒ ์‹œ -Merge๋กœ ํ•ฉ์ณ ์ฝ๋Š”๋‹ค.

๊ด€๋ จ ํฌ์ŠคํŠธ