데이터 웨어하우스 운영

src/content/documents/data-analysis/game-data-warehouse-and-database-operations.json

라이브 게임의 데이터 시스템은 결제 한 건을 안전하게 확정하는 일과 ‘어제 국가별 구매 전환율은 얼마인가’를 빠르게 집계하는 일을 동시에 요구한다. 하나의 운영 DB에 둘을 몰아넣으면 대형 분석 쿼리가 게임 트랜잭션과 자원을 다툰다. 이 런북은 이벤트 수집부터 웨어하우스 KPI, 권한, 복구와 관측까지 하나의 운영 흐름으로 연결한다.

운영 목표와 데이터 흐름

게임 서버는 OLTP에 원자적 이벤트를 기록하고, 적재 작업은 증분 데이터를 웨어하우스로 옮겨 차원 키를 해석한 뒤 fact에 저장한다. BI와 알림은 검증된 fact·dimension만 읽는다.

  1. 게임 이벤트 발생: 로그인, 매치 종료, 구매를 고유 event_id와 발생 시각으로 기록한다.

  2. 증분 추출: 재처리 가능한 범위와 watermark를 사용하고 원본 이벤트를 보존한다.

  3. 변환·적재: 차원 surrogate key를 찾고 명시한 grain의 fact를 멱등하게 적재한다.

  4. 품질 게이트: 건수·중복·NULL·금액 합계와 freshness를 확인한 뒤 KPI를 공개한다.

  5. 운영 보호: 최소권한과 RLS로 테넌트를 격리하고 백업·복제·모니터링으로 장애에 대비한다.

OLTP와 OLAP의 책임을 분리하기

구분

OLTP 운영 DB

OLAP 웨어하우스

주요 작업

짧은 INSERT·UPDATE, 계정·재화 무결성

대량 스캔·집계, 기간·국가·게임별 비교

모델

중복을 줄인 정규화 모델, 현재 상태 중심

fact와 넓은 dimension의 스타 스키마, 이력 중심

성공 기준

낮은 쓰기 지연, 정확한 트랜잭션, 높은 가용성

빠르고 재현 가능한 지표, freshness와 계보

분리는 제품마다 별도 PostgreSQL 클러스터, 관리형 웨어하우스, 읽기 복제본 등으로 구현할 수 있다. 같은 서버의 스키마 두 개는 학습용 논리 분리일 뿐, CPU·I/O·장애 도메인을 격리하지 않는다.

스타 스키마의 첫 결정은 grain

Kimball 방식에서는 먼저 비즈니스 프로세스와 fact의 grain을 문장으로 선언한다. 여기서는 ‘fact_game_event 한 행은 특정 테넌트에서 한 플레이어가 한 시각에 발생시킨 고유 게임 이벤트 하나’다. fact에는 측정값과 차원 FK를, dimension에는 국가·플랫폼·게임 같은 설명 문맥을 둔다.

DROP SCHEMA IF EXISTS oltp CASCADE;
DROP SCHEMA IF EXISTS dw CASCADE;
CREATE SCHEMA oltp;
CREATE SCHEMA dw;

CREATE TABLE oltp.game_events (
    event_id bigint PRIMARY KEY,
    tenant_id integer NOT NULL,
    player_id bigint NOT NULL,
    event_type text NOT NULL CHECK (event_type IN ('login', 'match_end', 'purchase')),
    occurred_at timestamptz NOT NULL,
    country_code text NOT NULL,
    platform text NOT NULL,
    amount numeric(12,2),
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    ingested_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK ((event_type = 'purchase' AND amount > 0) OR
           (event_type <> 'purchase' AND amount IS NULL))
);

CREATE TABLE dw.dim_date (
    date_key integer PRIMARY KEY,
    calendar_date date NOT NULL UNIQUE,
    year integer NOT NULL,
    month integer NOT NULL,
    day integer NOT NULL
);

CREATE TABLE dw.dim_player (
    player_key bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id integer NOT NULL,
    player_id bigint NOT NULL,
    country_code text NOT NULL,
    platform text NOT NULL,
    UNIQUE (tenant_id, player_id)
);

CREATE TABLE dw.fact_game_event (
    source_event_id bigint PRIMARY KEY,
    tenant_id integer NOT NULL,
    date_key integer NOT NULL REFERENCES dw.dim_date(date_key),
    player_key bigint NOT NULL REFERENCES dw.dim_player(player_key),
    event_type text NOT NULL,
    event_count integer NOT NULL DEFAULT 1 CHECK (event_count = 1),
    revenue numeric(12,2) NOT NULL DEFAULT 0 CHECK (revenue >= 0),
    occurred_at timestamptz NOT NULL,
    loaded_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX fact_event_date_tenant_idx
ON dw.fact_game_event (date_key, tenant_id, event_type);

INSERT INTO oltp.game_events
(event_id, tenant_id, player_id, event_type, occurred_at, country_code, platform, amount) VALUES
(1, 10, 101, 'login',     '2026-08-10 09:00+09', 'KR', 'ios',     NULL),
(2, 10, 102, 'login',     '2026-08-10 09:05+09', 'KR', 'android', NULL),
(3, 10, 101, 'match_end', '2026-08-10 09:30+09', 'KR', 'ios',     NULL),
(4, 10, 101, 'purchase',  '2026-08-10 10:00+09', 'KR', 'ios',     1200),
(5, 10, 103, 'login',     '2026-08-10 10:10+09', 'JP', 'pc',      NULL),
(6, 10, 103, 'purchase',  '2026-08-10 10:20+09', 'JP', 'pc',      600);

ETL과 ELT를 운영 관점에서 고르기

방식

운영 특성

ETL

외부 처리 계층에서 검증·변환한 뒤 웨어하우스에 적재한다. 민감정보를 목적지 전에 제거해야 하거나 목적지 계산 자원이 제한될 때 유리하다.

ELT

원본을 staging/raw에 먼저 보존하고 목적지 SQL로 변환한다. 재처리와 계보 추적이 쉽지만 raw 접근 통제와 저장 비용이 필요하다.

실무에서는 수집 전에 금지 필드를 제거하고, 허용된 원본을 raw에 적재한 뒤 SQL로 모델링하는 혼합형이 흔하다. 다음 ELT 예제는 source_event_id PK로 같은 배치를 재실행해도 fact가 중복되지 않게 한다.

BEGIN;

INSERT INTO dw.dim_date (date_key, calendar_date, year, month, day)
SELECT DISTINCT
       to_char(occurred_at AT TIME ZONE 'Asia/Seoul', 'YYYYMMDD')::integer,
       (occurred_at AT TIME ZONE 'Asia/Seoul')::date,
       EXTRACT(year FROM occurred_at AT TIME ZONE 'Asia/Seoul')::integer,
       EXTRACT(month FROM occurred_at AT TIME ZONE 'Asia/Seoul')::integer,
       EXTRACT(day FROM occurred_at AT TIME ZONE 'Asia/Seoul')::integer
FROM oltp.game_events
ON CONFLICT (date_key) DO NOTHING;

INSERT INTO dw.dim_player (tenant_id, player_id, country_code, platform)
SELECT DISTINCT tenant_id, player_id, country_code, platform
FROM oltp.game_events
ON CONFLICT (tenant_id, player_id) DO UPDATE
SET country_code = EXCLUDED.country_code,
    platform = EXCLUDED.platform;

INSERT INTO dw.fact_game_event
(source_event_id, tenant_id, date_key, player_key, event_type, event_count, revenue, occurred_at)
SELECT e.event_id, e.tenant_id,
       to_char(e.occurred_at AT TIME ZONE 'Asia/Seoul', 'YYYYMMDD')::integer,
       p.player_key, e.event_type, 1, COALESCE(e.amount, 0), e.occurred_at
FROM oltp.game_events AS e
JOIN dw.dim_player AS p
  ON p.tenant_id = e.tenant_id AND p.player_id = e.player_id
ON CONFLICT (source_event_id) DO NOTHING;

COMMIT;

품질 게이트: 적재 후 바로 확인할 것

SELECT
  (SELECT COUNT(*) FROM oltp.game_events) AS source_rows,
  (SELECT COUNT(*) FROM dw.fact_game_event) AS fact_rows,
  (SELECT COUNT(*) - COUNT(DISTINCT source_event_id) FROM dw.fact_game_event) AS duplicate_events,
  (SELECT COUNT(*) FROM dw.fact_game_event WHERE player_key IS NULL OR date_key IS NULL) AS orphan_keys,
  (SELECT COALESCE(SUM(amount), 0) FROM oltp.game_events) AS source_revenue,
  (SELECT COALESCE(SUM(revenue), 0) FROM dw.fact_game_event) AS fact_revenue;
 source_rows | fact_rows | duplicate_events | orphan_keys | source_revenue | fact_revenue
-------------+-----------+------------------+-------------+----------------+-------------
           6 |         6 |                0 |           0 |        1800.00 |     1800.00

KPI: DAU·구매 전환율·ARPDAU

이 런북은 login 이벤트가 있는 고유 플레이어를 DAU로 정의한다. 구매 전환율의 분모도 같은 활성 집단으로 고정하고, ARPDAU는 하루 매출을 DAU로 나눈다. 지표 정의는 쿼리와 함께 버전 관리해야 한다.

DAUd=∣{p:login(p,d)}∣DAU_d=|\{p:login(p,d)\}|
Conversiond=∣Purchasersd∩Actived∣∣Actived∣×100%Conversion_d=\frac{|Purchasers_d \cap Active_d|}{|Active_d|}\times100\%
ARPDAUd=RevenuedDAUdARPDAU_d=\frac{Revenue_d}{DAU_d}
WITH daily_player AS (
    SELECT f.tenant_id, f.date_key, f.player_key,
           bool_or(f.event_type = 'login') AS active,
           bool_or(f.event_type = 'purchase') AS purchased,
           SUM(f.revenue) AS revenue
    FROM dw.fact_game_event AS f
    GROUP BY f.tenant_id, f.date_key, f.player_key
)
SELECT d.calendar_date, p.tenant_id,
       COUNT(*) FILTER (WHERE p.active) AS dau,
       ROUND(100.0 * COUNT(*) FILTER (WHERE p.active AND p.purchased)
             / NULLIF(COUNT(*) FILTER (WHERE p.active), 0), 2) AS conversion_pct,
       ROUND(SUM(p.revenue) / NULLIF(COUNT(*) FILTER (WHERE p.active), 0), 2) AS arpdau
FROM daily_player AS p
JOIN dw.dim_date AS d USING (date_key)
GROUP BY d.calendar_date, p.tenant_id
ORDER BY d.calendar_date, p.tenant_id;
 calendar_date | tenant_id | dau | conversion_pct | arpdau
---------------+-----------+-----+----------------+--------
 2026-08-10    |        10 |   3 |          66.67 | 600.00

최소권한과 RLS 런북

수집기는 OLTP INSERT만, 변환기는 원본 SELECT와 DW 쓰기만, BI는 DW SELECT만 가져야 한다. 운영자·테이블 소유자와 BYPASSRLS 역할은 정책을 우회할 수 있으므로 BI 연결에 사용하지 않는다. 다음 명령은 역할 생성 권한이 있는 관리자가 새 실습 DB에서 실행한다.

CREATE ROLE game_ingest NOLOGIN;
CREATE ROLE warehouse_loader NOLOGIN;
CREATE ROLE bi_reader NOLOGIN;

REVOKE ALL ON SCHEMA oltp, dw FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA oltp, dw FROM PUBLIC;

GRANT USAGE ON SCHEMA oltp TO game_ingest;
GRANT INSERT ON oltp.game_events TO game_ingest;

GRANT USAGE ON SCHEMA oltp, dw TO warehouse_loader;
GRANT SELECT ON oltp.game_events TO warehouse_loader;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA dw TO warehouse_loader;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA dw TO warehouse_loader;

GRANT USAGE ON SCHEMA dw TO bi_reader;
GRANT SELECT ON dw.dim_date, dw.dim_player, dw.fact_game_event TO bi_reader;

ALTER TABLE dw.dim_player ENABLE ROW LEVEL SECURITY;
ALTER TABLE dw.fact_game_event ENABLE ROW LEVEL SECURITY;

CREATE POLICY dim_player_tenant_read ON dw.dim_player
FOR SELECT TO bi_reader
USING (tenant_id = current_setting('app.tenant_id', true)::integer);

CREATE POLICY fact_event_tenant_read ON dw.fact_game_event
FOR SELECT TO bi_reader
USING (tenant_id = current_setting('app.tenant_id', true)::integer);

BEGIN;
SET LOCAL ROLE bi_reader;
SET LOCAL app.tenant_id = '10';
SELECT COUNT(*) AS visible_events FROM dw.fact_game_event;
ROLLBACK;
 visible_events
----------------
              6

app.tenant_id는 신뢰할 수 있는 인증 계층이 트랜잭션마다 SET LOCAL로 지정해야 한다. 임의 SQL을 실행할 수 있는 사용자가 값을 직접 바꿀 수 있다면 이것만으로 테넌트 인증이 되지 않는다. 연결 풀 반환 전에 트랜잭션을 끝내고 정책 테스트에 테이블 소유자가 아닌 실제 BI 역할을 사용한다.

백업·PITR과 RPO·RTO

논리 백업 pg_dump는 선택적 복원과 이관에 유용하지만 지속적인 시점 복구를 대신하지 않는다. PITR은 base backup과 그 이후의 연속 WAL archive를 함께 사용해 목표 시각까지 재생한다. 복제본은 운영 실수와 논리적 삭제도 전파하므로 백업의 대체물이 아니다.

RPO=max⁡(acceptable  data  loss  time)RPO=\max(acceptable\;data\;loss\;time)
RTO=max⁡(acceptable  service  recovery  time)RTO=\max(acceptable\;service\;recovery\;time)

예를 들어 RPO 5분·RTO 30분이면 장애 직전 최대 5분의 변경 손실을 허용하고 30분 안에 서비스를 복구해야 한다. 설정값이 아니라 복구 훈련 결과가 목표 충족을 증명한다.

# 논리 백업과 검증 가능한 목록 확인
pg_dump --format=custom --file=game-20260811.dump game
pg_restore --list game-20260811.dump | head

# PostgreSQL 18 물리 base backup 예시
pg_basebackup --pgdata=basebackup-20260811 --format=plain --wal-method=stream --checkpoint=fast
pg_verifybackup basebackup-20260811

실제 PITR에는 wal_level=replica 이상, archive_mode와 실패 시 비정상 종료하는 archive_command, 별도 보존소, 복구 서버의 restore_command와 recovery_target_time 설정이 필요하다. 자격증명과 저장소 경로는 환경마다 달라 예제에 하드코딩하지 않는다.

  1. 격리된 복구 환경에 가장 최근 base backup을 복원하고 WAL archive를 목표 시각까지 재생한다.

  2. 타임라인과 recovery_target_action을 확인하고 읽기 전용으로 핵심 테이블 건수·최신 거래·KPI를 검증한다.

  3. 복구 소요시간과 복원된 최신 시각을 기록해 실제 RTO·RPO를 계산한다.

  4. DNS·애플리케이션 연결 전환과 쓰기 재개 승인자를 명시하고 사후에 새 base backup을 만든다.

HA: 장애 조치와 데이터 손실의 선택

PostgreSQL streaming replication은 primary의 WAL을 standby로 전송한다. 비동기 복제는 primary 지연을 줄이지만 장애 시 아직 전달되지 않은 WAL을 잃을 수 있고, 동기 복제는 커밋이 standby 확인을 기다려 지연과 가용성의 trade-off가 생긴다. 자동 failover 도구는 PostgreSQL 코어 밖의 구성요소까지 포함해 split-brain 방지, fencing, 재가입 절차를 설계해야 한다.

  • 정상 시: primary와 standby가 streaming인지, replay lag와 WAL 보존 공간이 한계 안인지 확인한다.

  • 장애 선언: 네트워크 분할인지 실제 primary 장애인지 판단하고 이전 primary가 쓰기를 받지 못하도록 fencing한다.

  • 승격 후: 타임라인, 데이터 최신성, 쓰기 가능 여부와 애플리케이션 연결을 확인한다.

  • 복구 후: 옛 primary를 그대로 다시 연결하지 않고 rewind 또는 새 base backup으로 standby를 재구성한다.

모니터링: 지표보다 먼저 SLI를 정의하기

SLI는 사용자가 경험하는 품질을 측정하는 지표다. 데이터 플랫폼에서는 쿼리 성공률·지연, 이벤트 최신성, 적재 완전성, 복제·아카이브 상태를 함께 본다. 평균만 보면 긴 꼬리 지연을 숨기므로 p95·p99를 별도 수집한다.

Availability=successful  requestseligible  requests×100%Availability=\frac{successful\;requests}{eligible\;requests}\times100\%
FreshnessLag=now()−max⁡(occurred_atloaded)FreshnessLag=now()-\max(occurred\_at_{loaded})
-- 적재 freshness와 건수
SELECT CURRENT_TIMESTAMP - MAX(occurred_at) AS freshness_lag,
       CURRENT_TIMESTAMP - MAX(loaded_at) AS loader_lag,
       COUNT(*) AS fact_rows
FROM dw.fact_game_event;

-- DB 성공/롤백 비율의 원시 카운터(애플리케이션 SLI와 함께 사용)
SELECT datname, xact_commit, xact_rollback, deadlocks,
       ROUND(100.0 * xact_commit / NULLIF(xact_commit + xact_rollback, 0), 4) AS commit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- primary에서 standby 상태와 WAL 지연 바이트
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- WAL 아카이브 실패와 마지막 성공 시각
SELECT archived_count, failed_count, last_archived_time, last_failed_time
FROM pg_stat_archiver;

pg_stat_database의 commit 비율은 연결 성공률이 아니며 정상적인 명시 ROLLBACK도 실패처럼 포함한다. 애플리케이션 요청 지표와 데이터베이스 원시 신호를 혼동하지 않는다. 전체 세션 통계를 보는 모니터링 역할에는 필요한 범위에서 pg_read_all_stats를 부여하되 로그인 역할 자체를 슈퍼유저로 만들지 않는다.

장애 대응 15분 런북

  1. 0~2분: 영향 범위와 시작 시각을 선언하고 쓰기 오류, p99 지연, freshness, replica와 archive 상태를 캡처한다.

  2. 2~5분: 과부하·잠금·스토리지·primary 장애·잘못된 배포 중 분류하고 증거 없이 재시작하지 않는다.

  3. 5~10분: 안전한 완화책을 선택한다. 분석 쿼리 차단, 적재 일시정지, 읽기 축소, fencing 후 standby 승격 중 영향이 가장 작은 것을 적용한다.

  4. 10~15분: KPI 품질 게이트와 핵심 거래를 검증하고 이해관계자에게 실제 RPO·RTO 예상과 다음 업데이트 시각을 알린다.

  5. 복구 후: 누락 이벤트를 멱등 재적재하고 원인·탐지·대응·재발방지를 타임라인과 함께 기록한다.

출시 전 최종 점검

  • fact grain, 시간대, late event, 중복 제거 키와 KPI 분모를 문서화했다.

  • 적재 재실행이 멱등하고 source↔fact 건수·금액·NULL 품질 게이트가 자동화되었다.

  • 수집·적재·BI·모니터링 역할이 분리되고 RLS를 실제 비소유자 역할로 테스트했다.

  • 백업 생성 성공이 아니라 격리 환경 복원으로 RPO·RTO를 정기 검증한다.

  • HA 승격 권한, fencing, 연결 전환, 옛 primary 재가입과 담당자 호출 경로가 준비되었다.

참고 자료

댓글 0

댓글을 불러오는 중…