postgresql.co.kr Logo
postgresql.co.kr
PostgreSQL
강의

#2 - 실무적인 입장에서 대용량 처리 테이블 생성

중년개발자
중년개발자

@loxo

27일 전

38

이 문서는 postgresql.co.kr 프로젝트의 데이터베이스 스키마 설계 철학, 주요 개념, 그리고 성능 최적화 전략을 설명하는 교육 자료입니다. 이 스키마는 대규모 트래픽과 데이터를 효율적으로 처리하기 위해 설계되었습니다. 비록 시작은 작은 사이트이지만, 실제 처리는 대형 사이트보다 실무적인 테이블 설계를 하였습니다. 그렇다고 딱히 대단한 것도 아닌 것이, 어떻게 보면 다 똑같은 게시판입니다. 그러나 게시판도 어떻게 설계 방향을 가져가느냐에 따라, 대형 사이트 블로그처럼 큰 데이터를 다룰 수 있다고 생각합니다. 작은 설계는 결국 개인에게 도움되지 않습니다. 테이블 설계에 조금이나마 도움이 되었으면 합니다.
여기에서 나오는 용어를 알기만 해도 요즘은 AI가 해주는 시대이니 용어 개념이라도 얻어 가셨으면 합니다.

1. 핵심 설계 개념 (Key Concepts)

1.1 UUIDv7 (Primary Key 전략)

전통적인 정수형 ID(Auto Increment) 대신 UUIDv7을 기본 키로 사용합니다.

  • 개념: UUIDv7은 기존의 완전 무작위 UUID(v4)와 달리, 타임스탬프(시간) 정보가 포함되어 있습니다.
  • 장점:
    1. 시간순 정렬: 생성된 순서대로 정렬이 가능하여, ID만으로도 대략적인 생성 시간을 알 수 있고 DB 인덱싱 성능이 UUIDv4보다 월등히 뛰어납니다. (B-Tree 인덱스 분열 감소)
    2. 보안: 연속된 숫자가 아니므로 외부에서 다음 ID를 예측하여 데이터를 크롤링하거나 공격하기 어렵습니다.
    3. 분산 환경: 여러 서버에서 동시에 데이터를 생성해도 충돌이 거의 없어 샤딩(Sharding)이나 마이크로서비스 환경 확장이 용이합니다.

1.2 파티셔닝 (Partitioning)

대용량 데이터를 대비하여 PostgreSQL의 Native Partitioning 기능을 적극 도입했습니다.

  • Hash Partitioning (posts, comments, likes 등):
    • 데이터를 ID(해시값) 기준으로 여러 테이블(p0~p6)에 고르게 분산 저장합니다.
    • 장점: 특정 테이블이 너무 커지는 것을 방지하여 조회 및 쓰기 성능을 균등하게 유지합니다. Lock 경합을 줄여 동시성 처리 능력을 높입니다.
  • List Partitioning (attachments):
    • 데이터의 성격(예: post, comment, profile)에 따라 테이블을 물리적으로 분리합니다.
    • 장점: 관리 목적에 따라 데이터를 분류하기 쉽고, 특정 카테고리의 데이터만 따로 관리(백업/삭제)하기 용이합니다.

1.3 정규화와 역정규화의 조화

기본적으로 정규화(Normalization)를 통해 데이터 무결성을 지키지만, 성능을 위해 과감한 역정규화(Denormalization)를 적용했습니다.

  • user_stats 테이블: posts_count, total_views 등의 통계 정보를 실시간 COUNT(*) 쿼리 대신 별도 테이블에 저장하여 관리합니다.
  • 성능 이점: 수백만 건의 데이터가 쌓여 있을 때 카운트 쿼리는 매우 느립니다. 이를 미리 계산된 필드로 조회하여 대시보드나 프로필 페이지 로딩 속도를 획기적으로 개선합니다.

2. 테이블별 설계 의도 및 장점

2.1 IAM (Identity & Access Management) - users 관련

  • users: 인증에 필요한 최소한의 정보(이메일, 비번, 권한)만 담아 가볍게 유지합니다.
  • user_profiles: 자주 바뀌고 용량이 큰 정보(소개, 링크 등)는 별도로 분리하여, 로그인 시 불필요한 데이터 로딩을 줄입니다.
  • refresh_tokens: 보안 강화를 위해 리프레시 토큰을 DB에 저장하여, 필요 시 특정 기기의 로그인을 강제로 만료(Revoke)시킬 수 있게 설계했습니다.

2.2 CMS (Content Management) - posts

  • 구조: posts 테이블은 board_id를 기준으로 파티셔닝되어 있습니다.
  • 장점: 게시판별로 데이터가 물리적으로 모일 가능성이 높아지거나(설정에 따라), 전체 데이터 조회 시에도 병렬 스캔을 통해 성능 이득을 볼 수 있습니다.
  • 기능적 컬럼:
    • hash_content: 본문의 변경 사항을 감지하기 위한 해시값을 저장하여, 불필요한 업데이트를 방지합니다.
    • ai_summary: AI 요약 기능을 위한 전용 컬럼을 두어 확장성을 고려했습니다.

2.3 Engagement - comments, likes

  • Polymorphic Association 대신 명시적 컬럼: target_type과 target_id를 사용하지만, 필요 시 파티셔닝을 통해 물리적으로 데이터를 분리하여 관리 효율을 높였습니다.
  • 고도의 인덱싱: 대댓글(parent_id), 인기 댓글(is_pinned) 등 다양한 조회 패턴에 최적화된 복합 인덱스를 적용했습니다.

2.4 Search Engine - post_search_index

  • pgroonga 확장: 한국어, 일본어 등 텍스트 검색에 강한 pgroonga 인덱스를 사용하기 위해 별도의 검색용 텍스트 테이블을 분리했습니다.
  • 이유: 원본 posts 테이블의 쓰기 성능에 영향을 주지 않으면서, 전문 검색(Full Text Search) 성능을 극대화하기 위함입니다.

3. 성능 및 최적화 가이드 (Performance Guide)

3.1 인덱스 전략 (Indexing Strategy)

인덱스는 읽기 성능을 높이지만 쓰기 성능을 저하시킵니다. 이 스키마는 필요한 곳에만 정확히 인덱스를 생성했습니다.

  • Partial Index (부분 인덱스):
    • WHERE is_solved = TRUE / WHERE is_pinned = TRUE
    • 효과: 전체 데이터의 10% 미만인 데이터만 인덱싱하여 인덱스 크기를 줄이고 검색 속도를 비약적으로 높입니다.
  • Composite Index (복합 인덱스):
    • idx_posts_board_pinned_id: board_id로 필터링하고 고정글 우선, 최신순(id DESC)으로 정렬하는 게시판 조회 패턴에 최적화되었습니다. 별도의 정렬 연산(Sort Heap) 없이 인덱스만으로 결과를 반환합니다.

3.2 SQL 작성 가이드 (Developer Tips)

이 스키마를 사용하는 백엔드 개발자는 다음 사항을 준수해야 성능을 보장받을 수 있습니다.

  1. 파티션 키 포함: posts, comments 등 파티셔닝된 테이블 조회 시 board_id나 target_id 같은 파티션 키를 WHERE 절에 포함하는 것이 좋습니다. (Partition Pruning 유도)
  2. UUIDv7 활용: 정렬 시 created_at 대신 id로 정렬해도 동일한 시간순 정렬 효과를 얻으면서 성능은 더 좋습니다. (ORDER BY id DESC)
  3. 역정규화 데이터 활용: 팔로워 수나 조회수 등을 가져올 때 조인을 걸어 COUNT() 하지 말고, user_stats 테이블을 조인하여 숫자를 바로 가져오세요.

3.3 유지보수 및 확장성

  • Extension: pgcrypto와 uuid-ossp는 암호화 및 ID 생성을 데이터베이스 레벨에서 처리하여 애플리케이션의 부하를 줄여줍니다.
  • R2 Integration: attachments 테이블은 R2(Object Storage)와 연동을 전제로 설계되어, DB에는 파일 경로만 저장하여 스토리지 비용과 DB 용량을 절감합니다.

이 문서는 DB 스키마를 이해하고 효율적인 쿼리를 작성하는 데 도움을 주기 위해 작성되었습니다.
질문은 댓글로 주시면 성실히 답변 드리겠습니다.
작성일: 2026.01.18

목차

#PostgreSQL#대용량 처리#테이블 설계#UUIDv7#파티셔닝

댓글 0

Ctrl + Enter를 눌러 등록할 수 있습니다
※ AI 다듬기는 내용을 정제하는 보조 기능이며, 최종 내용은 사용자가 확인해야 합니다.