정규화와 반정규화

게임 리더보드에서 정규화와 반정규화 중 하나가 언제나 옳지는 않다. 점수의 원본은 정규화하고, 읽기 모델은 측정 결과에 따라 반정규화한다는 출발점이 안전하다. 이 글은 시즌 점수 원장을 기준으로 실시간 순위를 제공하는 문제를 PostgreSQL로 비교한다.

먼저 서비스 수준 목표를 숫자로 쓴다

‘빠른 리더보드’만으로는 설계를 고를 수 없다. 예제에서는 상위 100명과 내 주변 순위를 제공하며, 목표를 읽기 p95 100ms 이하, 점수 반영 지연 5초 이하, 점수 유실 0건으로 둔다. 이 숫자는 예시이므로 실제 게임의 동시 접속자와 이벤트 빈도로 다시 정해야 한다.

질문	측정값	설계에 주는 압력
얼마나 자주 읽는가?	초당 조회 수, p50/p95/p99	인덱스·캐시·읽기 모델
점수가 얼마나 자주 바뀌는가?	초당 이벤트 수, WAL bytes/s	쓰기 증폭·락 경합
얼마나 최신이어야 하는가?	원본과 읽기 모델의 지연 초	강한 일관성 또는 최종 일관성

정규화 모델 — 원본 사실을 한 번만 저장한다

플레이어 정보, 시즌, 점수 이벤트를 분리하면 이름 변경과 점수 변경이 한 장소에서 일어난다. leaderboard_event는 획득·차감의 불변 원장이며 요청 ID를 UNIQUE로 만들어 재시도 중복을 막는다.

BEGIN;

CREATE TABLE player (
    player_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    display_name text NOT NULL
);

CREATE TABLE season (
    season_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    season_name text NOT NULL UNIQUE,
    starts_at   timestamptz NOT NULL,
    ends_at     timestamptz NOT NULL,
    CHECK (starts_at < ends_at)
);

CREATE TABLE leaderboard_event (
    event_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    request_id  uuid NOT NULL UNIQUE,
    season_id   bigint NOT NULL REFERENCES season(season_id),
    player_id   bigint NOT NULL REFERENCES player(player_id),
    score_delta integer NOT NULL CHECK (score_delta <> 0),
    occurred_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ix_event_season_player
ON leaderboard_event (season_id, player_id);

COMMIT;

정규화 조회는 이벤트를 플레이어별로 집계하고 이름을 조인한다. 결과는 원장과 같은 트랜잭션 스냅샷에서 계산되므로 최신이지만, 이벤트가 누적될수록 읽는 행과 집계 비용이 커진다.

SELECT e.player_id, p.display_name, SUM(e.score_delta) AS score
FROM leaderboard_event AS e
JOIN player AS p USING (player_id)
WHERE e.season_id = $1
GROUP BY e.player_id, p.display_name
ORDER BY score DESC, e.player_id
LIMIT 100;

반정규화 모델 — 조회 결과를 미리 저장한다

반정규화 테이블에는 시즌별 플레이어 누적 점수와 화면에 필요한 이름 스냅샷을 둔다. 조회는 정렬된 인덱스에서 상위 100행만 읽지만, 점수 이벤트마다 원장과 스냅샷을 함께 갱신해야 한다.

CREATE TABLE leaderboard_current (
    season_id            bigint NOT NULL REFERENCES season(season_id),
    player_id            bigint NOT NULL REFERENCES player(player_id),
    display_name_snapshot text NOT NULL,
    score                bigint NOT NULL DEFAULT 0,
    updated_at           timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (season_id, player_id)
);

CREATE INDEX ix_leaderboard_current_rank
ON leaderboard_current (season_id, score DESC, player_id);

CREATE OR REPLACE FUNCTION add_score(
    p_request_id uuid, p_season_id bigint, p_player_id bigint, p_delta integer
) RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    v_name text;
    v_inserted integer;
BEGIN
    SELECT display_name INTO STRICT v_name
    FROM player WHERE player_id = p_player_id;

    INSERT INTO leaderboard_event(request_id, season_id, player_id, score_delta)
    VALUES (p_request_id, p_season_id, p_player_id, p_delta)
    ON CONFLICT (request_id) DO NOTHING;
    GET DIAGNOSTICS v_inserted = ROW_COUNT;

    IF v_inserted = 1 THEN
        INSERT INTO leaderboard_current(
            season_id, player_id, display_name_snapshot, score
        ) VALUES (p_season_id, p_player_id, v_name, p_delta)
        ON CONFLICT (season_id, player_id) DO UPDATE
        SET score = leaderboard_current.score + EXCLUDED.score,
            display_name_snapshot = EXCLUDED.display_name_snapshot,
            updated_at = now();
    END IF;
END;
$$;

SELECT player_id, display_name_snapshot, score
FROM leaderboard_current
WHERE season_id = $1
ORDER BY score DESC, player_id
LIMIT 100;

두 쓰기는 반드시 같은 트랜잭션에서 실행한다. 함수 호출 자체가 하나의 문장이므로 실패하면 원장과 현재 점수가 함께 롤백된다. 애플리케이션이 별도 큐로 스냅샷을 갱신한다면 최종 일관성을 명시하고 재처리·중복 제거·지연 감시를 설계해야 한다.

읽기 증폭과 쓰기 증폭을 함께 본다

읽기 증폭은 한 번의 논리 조회를 위해 읽은 물리 행·페이지의 양, 쓰기 증폭은 한 번의 논리 점수 변경이 만든 테이블·인덱스·WAL 쓰기의 양으로 생각할 수 있다.

RA = \frac{rows\ examined}{rows\ returned}, \qquad WA = \frac{physical\ writes}{logical\ score\ events}

예를 들어 이벤트 1천만 행을 집계해 상위 100명을 반환하면 행 기준 읽기 증폭은 최대 $10{,}000{,}000/100=100{,}000$ 에 가깝다. 실제 비용은 캐시 적중, 인덱스, 병렬 처리와 페이지 배치에 따라 달라지므로 이 비율은 직관이고 최종 판단은 실행 계획과 I/O로 한다.

C = Q_r L_r + Q_w L_w + \lambda S

여기서 $Q_r,Q_w$ 는 초당 읽기·쓰기 수, $L_r,L_w$ 는 각각의 평균 자원 비용, $S$ 는 허용 한도를 넘은 비일관성 시간, $\lambda$ 는 비일관성의 사업 비용 가중치다. 반정규화는 보통 $L_r$ 를 낮추지만 $L_w$ 와 불일치 위험을 높인다.

읽기 비율이 커질수록 어디서 유리해지는가

아래 그래프는 실측치가 아니라 선택 원리를 보여주는 예시 비용 함수다. 가로축 $r=Q_r/Q_w$ 는 쓰기 한 번당 읽기 횟수다. 정규화 비용은 매번 집계하므로 가파르게 증가하고, 반정규화는 기본 쓰기 비용이 높지만 읽기 증가 폭이 작다고 가정한다.

읽기/쓰기 비율에 따른 예시 상대 비용

비용 동일

손익분기 r=1

주석

8r+1=r+8 \Rightarrow r^*=1

예시에서는 $r>1$ 부터 반정규화가 저렴하지만 실제 계수는 반드시 부하 시험으로 구한다. 캐시 적중률이 높으면 정규화 경로의 유효 읽기 비용도 크게 낮아진다.

일관성은 최신성·정확성·원자성으로 나눈다

위험	사례	대응
원자성 실패	이벤트만 저장되고 현재 점수는 누락	동일 DB 트랜잭션 또는 재처리 가능한 outbox
중복 처리	네트워크 재시도로 점수가 두 번 증가	request_id UNIQUE와 조건부 UPSERT
스냅샷 지연	승리 직후 이전 순위 표시	updated_at 지연 SLO, 내 점수 원본 우선 읽기

lag = t_{observed}-t_{source}

리더보드가 최종 일관성을 허용한다면 평균 지연만 보지 말고 p95와 최대 지연, 허용 시간 초과 비율을 관찰한다. 시즌 보상 확정처럼 돈이나 아이템이 걸린 작업은 캐시나 오래된 스냅샷이 아니라 원본을 재집계하거나 확정 스냅샷을 트랜잭션으로 고정해야 한다.

직접 반정규화하기 전의 두 대안

짧은 TTL 캐시

상위 100명처럼 모든 사용자가 거의 같은 결과를 읽는다면 season_id를 키로 결과를 수 초 캐시할 수 있다. 원본 스키마는 유지되고 읽기 부하는 크게 줄지만, 무효화와 캐시 미스 폭주를 다뤄야 한다.

L_{effective}=hL_{cache}+(1-h)L_{db}

여기서 $h$ 는 캐시 적중률이다. TTL에 무작위 지터를 주고 한 요청만 원본을 재계산하게 하는 single-flight 방식으로 동시 만료를 완화할 수 있다.

Materialized View

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
FROM leaderboard_event AS e
JOIN player AS p USING (player_id)
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);

REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard_mv;

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

CONCURRENTLY는 읽기를 막지 않고 갱신하지만 즉시 갱신 기능은 아니며, 해당 Materialized View의 모든 행을 고유하게 식별하는 조건 없는 UNIQUE 인덱스가 필요하다. 첫 데이터 적재는 CONCURRENTLY 없이 실행해야 한다.

측정으로 선택하는 실험 계획

실서비스 분포를 축소해 플레이어 수, 시즌당 이벤트 수, 점수 편향, 읽기/쓰기 비율을 고정한다. 균등 난수만 쓰지 않는다.
정규화 쿼리, 현재 점수 테이블, Materialized View, 캐시 경로에 동일한 상위 100·내 주변 순위 시나리오를 적용한다.
콜드 캐시와 웜 캐시를 분리하고 최소 10분 이상 안정 구간에서 처리량, p50/p95/p99, 타임아웃을 기록한다.
EXPLAIN (ANALYZE, BUFFERS, WAL)로 실제·예상 행 수, shared hit/read, 정렬 방식, WAL 바이트를 비교한다. ANALYZE는 쿼리를 실제 실행하므로 운영 쓰기 문장에는 사용하지 않는다.
원장 SUM과 읽기 모델 score를 표본·전체로 대조하고 불일치 건수, 최대 절대 오차, 갱신 지연 p95를 측정한다.
장애를 주입한다. 함수 중간 실패, 동일 request_id 재시도, 캐시 장애, Materialized View 갱신 지연 후 복구 시간과 데이터 정확성을 확인한다.

EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT TEXT)
SELECT player_id, display_name_snapshot, score
FROM leaderboard_current
WHERE season_id = 1
ORDER BY score DESC, player_id
LIMIT 100;

WITH source_score AS (
  SELECT season_id, player_id, SUM(score_delta)::bigint AS score
  FROM leaderboard_event
  GROUP BY season_id, player_id
)
SELECT COUNT(*) AS mismatched_players,
       COALESCE(MAX(abs(s.score - c.score)), 0) AS max_abs_error
FROM source_score AS s
FULL JOIN leaderboard_current AS c
  USING (season_id, player_id)
WHERE s.score IS DISTINCT FROM c.score;

error\ rate=\frac{players\ with\ mismatched\ score}{players\ compared}

결정 규칙

규모가 작고 조회가 드물며 즉시 정확해야 하면 정규화 원장과 인덱스부터 시작한다.
같은 상위 목록을 반복 조회하고 몇 초 지연이 허용되면 짧은 TTL 캐시를 먼저 시험한다.
정기 갱신이면 충분하고 SQL로 관리하고 싶다면 Materialized View를 선택한다.
초당 쓰기가 많고 실시간 읽기도 많아 집계가 SLO를 넘을 때만 현재 점수 테이블을 운영하되, 원장을 진실의 원천으로 남기고 대조·재구축 절차를 갖춘다.

참고 자료

PostgreSQL 공식 문서 — Materialized Views (2026-08-11 확인): 결과 저장과 REFRESH의 기본 동작.
PostgreSQL 공식 문서 — REFRESH MATERIALIZED VIEW (2026-08-11 확인): CONCURRENTLY 조건과 잠금 특성.
PostgreSQL 공식 문서 — INSERT (2026-08-11 확인): ON CONFLICT와 UPSERT 문법.
PostgreSQL 공식 문서 — EXPLAIN (2026-08-11 확인): ANALYZE, BUFFERS, WAL 옵션과 실행 주의사항.
PostgreSQL 공식 문서 — Transaction Isolation (2026-08-11 확인): 동시 트랜잭션에서 보이는 데이터와 격리 수준.

댓글을 불러오는 중…