비디오 분석 대시보드를 위한 PostgreSQL JSONB

I’m happy to translate the text for you, but I need the actual content you’d like translated. Could you please paste the article (or the specific portion you want translated) here? I’ll keep the source line unchanged and preserve all formatting, markdown, and technical terms as requested.

분석을 위한 경직된 스키마의 문제

비디오 분석 데이터는 본질적으로 지저분합니다. 어느 날에는 지역별 조회수를 추적하고, 다음 날에는 디바이스별 분류가 필요하고, 그 다음에는 시청 시간 백분위수가 필요합니다. 새로운 메트릭마다 컬럼을 추가하면 수십 개의 nullable 컬럼이 있는 테이블과 지속적인 마이그레이션이 발생합니다. DailyWatch에서는 PostgreSQL의 JSONB 컬럼 타입으로 이를 해결했습니다.

스키마 설계

우리는 구조화된 데이터는 일반 컬럼에, 유연한 분석 데이터는 JSONB에 저장합니다:

CREATE TABLE video_analytics (
    id SERIAL PRIMARY KEY,
    video_id TEXT NOT NULL REFERENCES videos(video_id),
    captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    views INTEGER NOT NULL,
    likes INTEGER NOT NULL,
    metrics JSONB NOT NULL DEFAULT '{}'
);

metrics 컬럼은 시간이 지나면서 형태가 바뀔 수 있는 모든 정보를 저장합니다:

{
  "regions": {"US": 45000, "GB": 12000, "DE": 8500, "FR": 6200},
  "devices": {"mobile": 0.62, "desktop": 0.31, "tablet": 0.07},
  "watch_time": {"avg_seconds": 142, "p50": 98, "p95": 340},
  "engagement": {"like_rate": 0.034, "comment_rate": 0.008},
  "traffic_sources": {"search": 0.35, "suggested": 0.42, "external": 0.23}
}

빠른 쿼리를 위한 JSONB 인덱싱

JSONB 컬럼에 대한 GIN 인덱스는 PostgreSQL이 JSON 내부를 효율적으로 검색할 수 있게 해줍니다:

-- General-purpose GIN index
CREATE INDEX idx_analytics_metrics ON video_analytics USING GIN (metrics);

-- Targeted index for a specific path (faster, smaller)
CREATE INDEX idx_analytics_regions ON video_analytics
    USING GIN ((metrics -> 'regions'));

일반 GIN 인덱스는 포함(@>) 및 존재(?) 연산자를 지원합니다. 경로‑특정 인덱스는 가장 많이 조회하는 키를 알고 있을 때 더 작고 빠릅니다.

JSONB 데이터 조회

지역별 조회수 분해

SELECT video_id,
       metrics -> 'regions' ->> 'US' AS us_views,
       metrics -> 'regions' ->> 'DE' AS de_views,
       metrics -> 'watch_time' ->> 'avg_seconds' AS avg_watch
FROM video_analytics
WHERE captured_at > NOW() - INTERVAL '24 hours'
ORDER BY views DESC
LIMIT 20;

모바일 트래픽이 70 %를 초과하는 비디오 찾기

SELECT v.title,
       a.metrics -> 'devices' ->> 'mobile' AS mobile_share
FROM video_analytics a
JOIN videos v ON v.video_id = a.video_id
WHERE (a.metrics -> 'devices' ->> 'mobile')::float > 0.70
ORDER BY a.captured_at DESC;

모든 비디오에 대한 지역별 조회수 집계

SELECT
    key AS region,
    SUM(value::integer) AS total_views
FROM video_analytics,
     jsonb_each_text(metrics -> 'regions')
WHERE captured_at > NOW() - INTERVAL '7 days'
GROUP BY key
ORDER BY total_views DESC;

jsonb_each_text 함수는 JSONB 객체를 행으로 풀어내어 동적 키에 대한 집계에 매우 유용합니다.

JSONB에 전체 교체 없이 추가하기

PostgreSQL의 jsonb_set은 전체 객체를 교체하지 않고 특정 경로를 업데이트합니다:

-- Add a new traffic source
UPDATE video_analytics
SET metrics = jsonb_set(metrics, '{traffic_sources,direct}', '0.15')
WHERE video_id = 'abc123';

-- Merge new data into existing object
UPDATE video_analytics
SET metrics = metrics || '{"cdn_cost": 0.0023}'::jsonb
WHERE video_id = 'abc123';

|| 연산자는 객체를 병합합니다. 기존 키는 덮어쓰이고, 새로운 키는 추가됩니다. 이것이 새로운 데이터가 도착할 때 분석 레코드를 점진적으로 풍부하게 만드는 방법입니다.

시간 시계열 뷰 구축

대시보드 차트를 위해서는 시간에 따른 메트릭이 필요합니다:

SELECT
    date_trunc('hour', captured_at) AS hour,
    AVG(views) AS avg_views,
    AVG((metrics -> 'watch_time' ->> 'avg_seconds')::float) AS avg_watch_time,
    AVG((metrics -> 'devices' ->> 'mobile')::float) AS mobile_share
FROM video_analytics
WHERE video_id = 'abc123'
  AND captured_at > NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;

이 쿼리는 시계열 데이터 포인트를 시간별로 생성하여 조회 추세, 시청 시간 및 디바이스 비율을 차트화할 수 있게 합니다 — 모두 단일 JSONB 컬럼에서 가져옵니다.

JSONB를 사용하면 안 되는 경우

JSONB는 올바른 스키마 설계를 대체하는 것이 아닙니다. 항상 조회하는 데이터(예: video_id, views, likes, captured_at)는 일반 컬럼으로 두고, 선택적이거나 형태가 가변적이며 자주 필터링되지 않는 데이터는 JSONB에 저장하세요.

• 잘못된 사용: JOIN이 필요할 때 video_id나 제목을 JSON 안에 저장하는 경우.
• 올바른 사용: 소스별로 다르고 시간이 지나면서 변하는 분석 세부 정보를 저장하는 경우.

성능 메모

~50,000개의 분석 레코드와 GIN 인덱스를 사용한 데이터셋에서:

• 경로 추출 (->>) 쿼리: 1–3 ms
• 포함 (@>) 쿼리: 2–5 ms
• jsonb_each_text 집계: 10–20 ms
• 전체 테이블 JSONB 집계: 40–80 ms

JSONB의 유연성 덕분에 초기 테이블 생성 이후 한 번도 마이그레이션 없이 분석 대시보드를 반복적으로 개선할 수 있었습니다.