22강. 최종 프로젝트: 게임 랭킹 서비스 DB 설계·성능·운영 점검

?1. 이번 강의에서 해결할 문제

테이블과 쿼리가 동작하는 것만으로 운영 준비가 끝나지 않습니다. 요구사항에서 시작해 ERD와 제약 근거를 만들고, 핵심 조회의 인덱스와 실행 계획, 동시 경기 저장 트랜잭션, FastAPI 연결, 마이그레이션, 백업·보안·모니터링까지 하나의 검토 가능한 결과물로 묶습니다.

✓2. 학습 목표

✓이 강의를 마치면

• 5개 엔터티의 ERD와 테이블 설계 근거를 설명합니다.
• 무결성 제약, 인덱스, 트랜잭션을 실제 유스케이스에 연결합니다.
• FastAPI·SQLAlchemy·Alembic·환경 변수 연결 방식을 구성합니다.
• 백업, 보안, 성능 점검 체크리스트로 배포 준비도를 평가합니다.

◇3. 핵심 개념

최종 프로젝트의 중심은 “어떤 SQL을 썼는가”가 아니라 “어떤 요구를 어떤 데이터 규칙으로 보장하는가”입니다. 원천 사건인 경기 결과와 현재 상태인 플레이어 평점, 시점 이력인 랭킹 스냅샷을 분리합니다. PK·FK·UNIQUE·CHECK는 잘못된 상태를 막고, 인덱스는 실제 API 조회를 지원하며, 트랜잭션은 한 경기의 결과와 평점 갱신을 하나의 업무 단위로 묶습니다.

ERD와 설계 근거

erDiagram
  SEASONS ||--o{ GAMES : schedules
  GAMES ||--|{ MATCH_RESULTS : contains
  PLAYERS ||--o{ MATCH_RESULTS : participates
  CHARACTERS ||--o{ MATCH_RESULTS : selected_as
  SEASONS ||--o{ RANKING_SNAPSHOTS : groups
  PLAYERS ||--o{ RANKING_SNAPSHOTS : receives

엔터티	저장하는 사실	분리 근거
players	플레이어 정체성과 현재 평점	경기와 독립된 수명, 닉네임 유일성
seasons	시즌 기간과 상태	경기·랭킹을 같은 기간 정책으로 묶음
games	한 경기의 모드·상태·시각	참가 결과보다 먼저 생성되는 사건
characters	선택 가능한 캐릭터 기준 정보	이름 중복과 변경을 결과 행에서 제거
match_results	플레이어의 한 경기 참가 결과	Game과 Player의 N:M 관계 및 점수 속성
ranking_snapshots	시점별 순위와 평점 이력	현재 rating과 과거 화면을 분리

§4. 단계별 예시

4-1. 최종 PostgreSQL 스키마

파일 경로: C:\dev\game-ranking-service\database\sql\22-final-schema.sql

database/sql/22-final-schema.sql

CREATE SCHEMA IF NOT EXISTS ranking;

CREATE TABLE ranking.seasons (
    season_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code varchar(20) NOT NULL UNIQUE,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz,
    status varchar(12) NOT NULL DEFAULT 'planned',
    CONSTRAINT ck_seasons_period CHECK (ends_at IS NULL OR ends_at > starts_at),
    CONSTRAINT ck_seasons_status CHECK (status IN ('planned', 'active', 'closed'))
);

CREATE TABLE ranking.players (
    player_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nickname varchar(30) NOT NULL,
    rating integer NOT NULL DEFAULT 1000,
    version integer NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT ck_players_rating CHECK (rating >= 0),
    CONSTRAINT ck_players_version CHECK (version > 0)
);

CREATE UNIQUE INDEX uq_players_active_nickname
ON ranking.players (lower(nickname))
WHERE deleted_at IS NULL;

CREATE TABLE ranking.characters (
    character_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code varchar(30) NOT NULL UNIQUE,
    display_name varchar(60) NOT NULL,
    is_active boolean NOT NULL DEFAULT true
);

CREATE TABLE ranking.games (
    game_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    season_id bigint NOT NULL REFERENCES ranking.seasons(season_id) ON DELETE RESTRICT,
    mode varchar(20) NOT NULL,
    status varchar(12) NOT NULL DEFAULT 'waiting',
    started_at timestamptz NOT NULL,
    ended_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT ck_games_status CHECK (status IN ('waiting', 'playing', 'finished', 'cancelled')),
    CONSTRAINT ck_games_time CHECK (ended_at IS NULL OR ended_at >= started_at)
);

CREATE TABLE ranking.match_results (
    result_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    game_id bigint NOT NULL REFERENCES ranking.games(game_id) ON DELETE CASCADE,
    player_id bigint NOT NULL REFERENCES ranking.players(player_id) ON DELETE RESTRICT,
    character_id bigint NOT NULL REFERENCES ranking.characters(character_id) ON DELETE RESTRICT,
    score integer NOT NULL,
    outcome varchar(8) NOT NULL,
    rating_delta integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_results_game_player UNIQUE (game_id, player_id),
    CONSTRAINT ck_results_score CHECK (score >= 0),
    CONSTRAINT ck_results_outcome CHECK (outcome IN ('win', 'loss', 'draw'))
);

CREATE TABLE ranking.ranking_snapshots (
    snapshot_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    season_id bigint NOT NULL REFERENCES ranking.seasons(season_id) ON DELETE RESTRICT,
    player_id bigint NOT NULL REFERENCES ranking.players(player_id) ON DELETE RESTRICT,
    rank_position integer NOT NULL,
    rating integer NOT NULL,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_snapshot_season_player_time UNIQUE (season_id, player_id, recorded_at),
    CONSTRAINT ck_snapshot_rank CHECK (rank_position > 0),
    CONSTRAINT ck_snapshot_rating CHECK (rating >= 0)
);

CREATE INDEX ix_results_player_game_desc
ON ranking.match_results (player_id, game_id DESC);

CREATE INDEX ix_games_season_finished_started
ON ranking.games (season_id, started_at DESC)
WHERE status = 'finished';

CREATE INDEX ix_snapshots_season_rank_time
ON ranking.ranking_snapshots (season_id, recorded_at DESC, rank_position);

CREATE INDEX ix_results_character
ON ranking.match_results (character_id);

PowerShell · 새 개발 DB에 스키마 적용

createdb game_ranking_final_dev
psql -d game_ranking_final_dev -v ON_ERROR_STOP=1 --single-transaction -f .\database\sql\22-final-schema.sql
psql -d game_ranking_final_dev -c "\dt ranking.*"

4-2. 경기 결과와 평점의 원자적 저장

파일 경로: C:\dev\game-ranking-service\database\sql\22-record-game.sql

database/sql/22-record-game.sql

BEGIN;

SELECT player_id, rating, version
FROM ranking.players
WHERE player_id IN (42, 77)
ORDER BY player_id
FOR UPDATE;

INSERT INTO ranking.match_results
    (game_id, player_id, character_id, score, outcome, rating_delta)
VALUES
    (101, 42, 3, 1850, 'win', 25),
    (101, 77, 5, 1210, 'loss', -25);

UPDATE ranking.players AS p
SET rating = p.rating + delta.rating_delta,
    version = p.version + 1,
    updated_at = now()
FROM (VALUES (42::bigint, 25), (77::bigint, -25)) AS delta(player_id, rating_delta)
WHERE p.player_id = delta.player_id;

UPDATE ranking.games
SET status = 'finished', ended_at = now()
WHERE game_id = 101 AND status = 'playing';

COMMIT;

잠금 순서를 player_id 오름차순으로 고정해 교착 가능성을 줄입니다. uq_results_game_player는 재시도된 요청이 같은 결과를 중복 저장하지 못하게 하는 마지막 방어선입니다. API는 별도 idempotency key가 필요하다면 요청 테이블을 추가할 수 있습니다.

4-3. 핵심 조회와 실행 계획

파일 경로: C:\dev\game-ranking-service\database\sql\22-ranking-plan.sql

database/sql/22-ranking-plan.sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT rs.player_id, p.nickname, rs.rank_position, rs.rating
FROM ranking.ranking_snapshots AS rs
JOIN ranking.players AS p ON p.player_id = rs.player_id
WHERE rs.season_id = 7
  AND rs.recorded_at = (
      SELECT max(recorded_at)
      FROM ranking.ranking_snapshots
      WHERE season_id = 7
  )
  AND p.deleted_at IS NULL
ORDER BY rs.rank_position
LIMIT 100;

PowerShell · 계획 기록

psql -d game_ranking_final_dev -f .\database\sql\22-ranking-plan.sql | Tee-Object .\reports\22-ranking-plan.txt

인덱스 적용 전후의 스캔 노드, 예상·실제 행 수, shared buffer 수를 기록합니다. 데이터가 작은 개발 DB에서 순차 스캔이 나오는 것은 자연스러울 수 있으므로 운영과 비슷한 규모·분포의 테스트 데이터로 재측정합니다.

4-4. FastAPI·SQLAlchemy 모델과 환경 변수

파일 경로: C:\dev\game-ranking-service\app\models\player.py

app/models/player.py

from datetime import datetime

from sqlalchemy import BigInteger, DateTime, Integer, String, text
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base


class Player(Base):
    __tablename__ = "players"
    __table_args__ = {"schema": "ranking"}

    id: Mapped[int] = mapped_column("player_id", BigInteger, primary_key=True)
    nickname: Mapped[str] = mapped_column(String(30), nullable=False)
    rating: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1000")
    version: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1")
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=text("now()")
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=text("now()")
    )

파일 경로: C:\dev\game-ranking-service\.env.example

.env.example

DATABASE_URL=postgresql+psycopg://ranking_app:CHANGE_ME@localhost:5432/game_ranking_dev
DB_POOL_SIZE=5
DB_MAX_OVERFLOW=5

PowerShell · 마이그레이션과 API 확인

Copy-Item .env.example .env
# .env의 CHANGE_ME는 로컬 비밀로 교체하고 Git에 추가하지 않습니다.
alembic upgrade head
alembic current
python -m uvicorn app.main:app --reload

SQLAlchemy 모델은 애플리케이션 매핑이고 Alembic 리비전이 환경별 실제 스키마 변경 이력입니다. .env는 .gitignore에 두며 운영에서는 배포 플랫폼의 비밀 저장소로 DATABASE_URL을 주입합니다. 애플리케이션 역할과 마이그레이션 역할은 분리합니다.

4-5. 백업·보안·성능 운영 체크리스트

문서 경로: C:\dev\game-ranking-service\docs\database\22-operations-checklist.md

docs/database/22-operations-checklist.md

## 백업·복구
- [ ] pg_dump 성공 여부와 파일 크기·checksum을 기록한다.
- [ ] 격리된 복구 DB에서 pg_restore와 핵심 조회를 정기 검증한다.
- [ ] RPO, RTO, 보관 기간, 외부 저장 위치와 암호화 책임자를 정한다.

## 보안
- [ ] API, 마이그레이션, 읽기 전용 역할을 분리한다.
- [ ] API 역할에는 CREATE, DROP, SUPERUSER 권한이 없다.
- [ ] .env와 dump 파일을 Git에 넣지 않고 비밀 교체 절차를 연습한다.
- [ ] SQL은 바인드 파라미터를 사용하고 로그에서 민감값을 마스킹한다.

## 성능·운영
- [ ] 핵심 API별 p95 지연 시간과 실행 계획 기준본이 있다.
- [ ] 인덱스 근거와 쓰기 비용을 기록한다.
- [ ] 오래 열린 트랜잭션, 연결 사용량, dead tuple을 관찰한다.
- [ ] deadlock·직렬화 실패는 제한된 트랜잭션 재시도로 처리한다.
- [ ] Alembic 리비전을 빈 DB와 기존 데이터가 있는 테스트 DB에서 검증한다.

§5. 동작 원리

설계 근거가 ERD와 제약에 연결되면 잘못된 상태가 저장 경계에서 거부됩니다. 실제 조회와 맞춘 인덱스는 읽기 범위를 줄이고 EXPLAIN (ANALYZE, BUFFERS)가 효과를 확인합니다. 경기 결과와 평점 갱신은 같은 트랜잭션과 잠금 순서로 일관성을 유지합니다. 모델, 리비전, 환경 변수, 역할, 백업과 모니터링을 함께 관리해야 코드 변경이 운영 데이터에 안전하게 도달합니다.

!6. 자주 하는 실수와 해결법

!자주 하는 실수

• 최종 프로젝트를 CREATE TABLE 파일로만 끝냄: 요구사항·ERD·제약 근거·계획·복구 증거를 함께 제출합니다.
• 기존 DB에 최종 스키마를 바로 실행: 같은 이름 객체와 데이터가 손상될 수 있습니다. 새 개발 DB에서 검증하고 Alembic 점진 변경으로 이전합니다.
• EXPLAIN ANALYZE 결과만 캡처: 데이터 규모, 파라미터, 통계 시점, 인덱스 정의를 같이 기록합니다.
• 백업 파일을 저장소에 커밋: 개인정보와 비밀이 포함될 수 있습니다. 접근 제어·암호화된 전용 저장소를 사용합니다.
• FastAPI 검증만으로 무결성 보장: 다른 쓰기 경로도 있으므로 핵심 규칙은 DB 제약으로 중복 방어합니다.

✎7. 직접 실습

✎직접 실습

1. ERD 각 관계를 FK와 삭제 정책에 연결하고 설계 근거를 한 문장씩 씁니다.
2. 새 개발 DB에 최종 스키마를 적용하고 정상·위반 샘플을 삽입합니다.
3. 경기 저장 중간에 오류를 발생시켜 결과와 평점이 모두 롤백되는지 확인합니다.
4. 상위 랭킹 계획을 인덱스 전후로 비교하고 계획·버퍼·시간을 기록합니다.
5. Alembic으로 빈 DB와 기존 데이터 DB를 head까지 올립니다.
6. dump를 별도 복구 DB에 복원하고 운영 체크리스트를 완료합니다.

?8. 이해 점검 질문 3개

?이해 점검

1. 현재 평점과 랭킹 스냅샷을 분리한 설계 근거는 무엇인가요?
2. 경기 저장 트랜잭션에서 일관된 잠금 순서와 UNIQUE 제약이 각각 막는 문제는 무엇인가요?
3. 배포 준비를 판단할 때 스키마와 API 코드 외에 어떤 증거가 필요한가요?

■9. 핵심 요약

■핵심 요약

운영 가능한 데이터베이스는 ERD, 무결성, 성능, 동시성, 변경 이력과 복구 절차가 하나의 근거 사슬로 연결됩니다. PostgreSQL을 기준 시스템으로 두고 FastAPI·SQLAlchemy는 짧은 트랜잭션과 최소 권한으로 접근하며, Alembic·백업·모니터링으로 변화를 안전하게 운영합니다.