500===Dev Database/Architecture

확장 가능한 테이블 설계: 대규모 데이터를 위한 심층 아키텍처 가이드 🚀

블로글러 2025. 3. 27. 18:25

안녕하세요! 이전에 확장 가능한 테이블 설계의 기본 개념을 살펴봤는데요, 오늘은 더 깊이 파고들어 실제 구현 방법과 최적화 전략에 대해 자세히 알아보겠습니다. 대규모 데이터를 다루는 아키텍트와 개발자분들께 실질적인 도움이 될 내용으로 준비했습니다. 🧐

정규화와 비정규화의 깊은 이해

정규화의 단계별 접근 🔍

정규화는 단순히 데이터 중복을 제거하는 것 이상의 의미가 있습니다. 각 정규화 단계가 해결하는 문제를 이해하면 더 효과적인 설계가 가능합니다.

1NF (제1정규형)

  • 모든 속성값이 원자값(atomic value)이어야 함
  • 반복 그룹이 없어야 함
-- 1NF를 위반하는 테이블 (반복 그룹 포함)
CREATE TABLE orders_violation (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  product_list VARCHAR(255) -- "product1, product2, product3"처럼 저장
);

-- 1NF를 준수하는 설계
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100)
);

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

2NF (제2정규형)

  • 1NF를 만족하면서 부분 함수 종속성 제거
  • 복합 키의 일부에만 종속된 속성을 별도 테이블로 분리
-- 2NF를 위반하는 테이블
CREATE TABLE order_items_violation (
  order_id INT,
  product_id INT,
  quantity INT,
  product_name VARCHAR(100), -- product_id에만 종속됨
  PRIMARY KEY (order_id, product_id)
);

-- 2NF를 준수하는 설계
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100)
);

3NF (제3정규형)

  • 2NF를 만족하면서 이행적 종속성 제거
  • 기본 키에 직접적으로 종속되지 않는 속성 분리
-- 3NF를 위반하는 테이블
CREATE TABLE orders_violation (
  order_id INT PRIMARY KEY,
  customer_id INT,
  customer_address VARCHAR(255) -- customer_id에 종속됨
);

-- 3NF를 준수하는 설계
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_address VARCHAR(255)
);

BCNF (보이스-코드 정규형)

  • 3NF를 만족하면서 모든 결정자가 후보 키여야 함
  • 보통 실무에서는 3NF까지만 적용하지만, 특정 상황에서는 BCNF가 필요
-- BCNF를 위반하는 테이블
CREATE TABLE course_teachers (
  student_id INT,
  course_id INT,
  teacher_id INT,
  PRIMARY KEY (student_id, course_id),
  UNIQUE (course_id, teacher_id) -- 하나의 과목은 한 명의 교사만 담당
);
-- 문제: teacher_id는 course_id에 의해 결정되지만, course_id는 후보 키가 아님

-- BCNF를 준수하는 설계
CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  teacher_id INT
);

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

비정규화의 고급 전략 📊

비정규화는 단순히 테이블을 합치는 것이 아닌, 성능과 데이터 무결성 사이의 균형을 찾는 의도적인 전략입니다.

계산된 컬럼 추가

-- 주문 테이블에 총 금액 컬럼 추가
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATETIME,
  total_amount DECIMAL(10,2), -- 계산된 값 저장
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 트리거로 자동 계산 구현
DELIMITER //
CREATE TRIGGER update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE orders 
  SET total_amount = (
    SELECT SUM(price * quantity) 
    FROM order_items oi 
    JOIN products p ON oi.product_id = p.product_id 
    WHERE oi.order_id = NEW.order_id
  )
  WHERE order_id = NEW.order_id;
END; //
DELIMITER ;

롤업 테이블(요약 테이블) 생성

-- 일별 판매 요약 테이블
CREATE TABLE daily_sales_summary (
  date DATE PRIMARY KEY,
  total_orders INT,
  total_revenue DECIMAL(12,2),
  avg_order_value DECIMAL(10,2)
);

-- 월별 배치 작업으로 데이터 갱신
INSERT INTO daily_sales_summary (date, total_orders, total_revenue, avg_order_value)
SELECT 
  DATE(order_date) as date,
  COUNT(*) as total_orders,
  SUM(total_amount) as total_revenue,
  AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(order_date)
ON DUPLICATE KEY UPDATE
  total_orders = VALUES(total_orders),
  total_revenue = VALUES(total_revenue),
  avg_order_value = VALUES(avg_order_value);

히스토리 패턴

변경 이력을 추적하면서도 현재 데이터에 빠르게 접근하는 전략입니다.

-- 현재 상태 테이블
CREATE TABLE products_current (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  stock INT,
  last_updated DATETIME
);

-- 히스토리 테이블
CREATE TABLE products_history (
  history_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  stock INT,
  changed_at DATETIME,
  FOREIGN KEY (product_id) REFERENCES products_current(product_id)
);

-- 변경 시 히스토리 저장 트리거
DELIMITER //
CREATE TRIGGER product_history_trigger
BEFORE UPDATE ON products_current
FOR EACH ROW
BEGIN
  INSERT INTO products_history
  (product_id, name, price, stock, changed_at)
  VALUES
  (OLD.product_id, OLD.name, OLD.price, OLD.stock, NOW());
END; //
DELIMITER ;

정규화/비정규화 결정 프레임워크 🧠

확장 가능한 테이블 설계에서 정규화와 비정규화 사이의 균형을 결정하는 체계적인 접근법이 필요합니다.

요소 정규화 선호 비정규화 선호
읽기/쓰기 비율 쓰기 위주 (< 1:1) 읽기 위주 (> 10:1)
데이터 일관성 매우 중요 어느 정도 유연함 허용
쿼리 복잡성 단순한 쿼리 복잡한 조인, 집계 쿼리
데이터 변경 빈도 높음 낮음
저장 공간 제약 심각함 덜 중요함
트랜잭션 격리 수준 높음 (Serializable) 낮음 (Read Committed)

아마존의 웨르너 보겔스(Werner Vogels) CTO가 말했듯이, "모든 것에 정규화를 적용하려는 유혹을 피하고, 실제 쿼리 패턴에 맞게 설계하라"는 원칙을 기억해야 합니다.

인덱싱 전략의 고급 기법

B-트리 vs. 해시 인덱스 vs. 비트맵 인덱스 🌲

인덱스 유형별 특성과 적합한 상황을 이해하는 것이 중요합니다.

B-트리 인덱스

대부분의 관계형 데이터베이스에서 기본적으로 사용하는 인덱스 구조입니다.

  • 장점: 범위 검색, 정렬, 부분 일치에 효율적
  • 단점: 삽입/삭제 시 재구성 필요, 공간 오버헤드
  • 적합한 상황: 대부분의 일반적인 쿼리, 특히 범위 검색
-- MySQL에서 B-트리 인덱스 생성
CREATE INDEX idx_customers_lastname ON customers(last_name);

-- 범위 검색에 활용
SELECT * FROM customers WHERE last_name BETWEEN 'A' AND 'C';

해시 인덱스

키-값 조회에 최적화된 인덱스 구조입니다.

  • 장점: 정확한 일치 검색에 매우 빠름(O(1))
  • 단점: 범위 검색, 정렬에 사용 불가
  • 적합한 상황: 정확한 일치 검색이 주로 필요한 경우
-- PostgreSQL에서 해시 인덱스 생성
CREATE INDEX idx_sessions_token USING HASH ON sessions(token);

-- 정확한 일치 검색에 활용
SELECT * FROM sessions WHERE token = '123abc456def';

비트맵 인덱스

낮은 카디널리티(고유값이 적은) 컬럼에 적합한 인덱스입니다.

  • 장점: 필터링 조건이 여러 개일 때 효율적, 저장 공간 효율
  • 단점: 높은 카디널리티에 비효율적, 쓰기 작업이 많을 때 성능 저하
  • 적합한 상황: 성별, 상태 코드 등 값의 종류가 적은 컬럼
-- Oracle에서 비트맵 인덱스 생성
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- 여러 조건 조합에 효율적
SELECT * FROM orders WHERE status = 'completed' AND region_id = 5;

커버링 인덱스 기법 🛡️

쿼리에 필요한 모든 데이터를 인덱스 자체에 포함시켜 테이블 접근을 최소화하는 고급 기법입니다.

-- 커버링 인덱스 생성
CREATE INDEX idx_orders_customer_date_status ON orders(customer_id, order_date, status);

-- 커버링 인덱스를 활용한 쿼리 (테이블 접근 없이 인덱스만으로 처리 가능)
SELECT customer_id, order_date, status 
FROM orders 
WHERE customer_id = 12345 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

커버링 인덱스 설계 시 고려사항:

  • 자주 사용되는 쿼리의 WHERE 절과 SELECT 절 분석
  • 인덱스 크기와 유지보수 비용 고려
  • 인덱스에 포함할 컬럼 수 제한 (너무 많으면 효율 감소)

인덱스 성능 분석 및 최적화 📈

실제 워크로드에서 인덱스 사용 패턴을 분석하고 최적화하는 기법입니다.

인덱스 사용량 모니터링 (MySQL 예시)

-- 사용되지 않는 인덱스 확인
SELECT * FROM sys.schema_unused_indexes;

-- 중복된 인덱스 찾기
SELECT * FROM sys.schema_redundant_indexes;

-- 인덱스 사용 통계
SELECT * FROM information_schema.statistics 
WHERE table_schema = 'your_database';

쿼리 실행 계획 상세 분석

-- MySQL에서 쿼리 실행 계획 확인
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- PostgreSQL에서 더 상세한 실행 계획
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

인덱스 튜닝 사례

인덱스 컬럼 순서 최적화:

-- 비효율적인 컬럼 순서
CREATE INDEX idx_inefficient ON orders(status, customer_id);

-- 선택도가 높은 컬럼을 먼저 배치하여 최적화
CREATE INDEX idx_optimized ON orders(customer_id, status);

부분 인덱스로 크기 최적화:

-- PostgreSQL 부분 인덱스 (활성 주문만 인덱싱)
CREATE INDEX idx_active_orders ON orders(customer_id, order_date)
WHERE status = 'active';

함수 기반 인덱스:

-- 대소문자 구분 없는 검색을 위한 함수 기반 인덱스
CREATE INDEX idx_customer_email_lower ON customers(LOWER(email));

-- 활용 쿼리
SELECT * FROM customers WHERE LOWER(email) = 'user@example.com';

파티셔닝과 샤딩의 실전 구현

파티셔닝 상세 구현 전략 🧩

범위 파티셔닝 (Range Partitioning)

날짜, 숫자 범위 등을 기준으로 데이터를 분할합니다.

-- MySQL 8.0에서 날짜 기반 범위 파티셔닝
CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2),
  PRIMARY KEY (order_id, order_date)
) 
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION future VALUES LESS THAN MAXVALUE
);

범위 파티셔닝의 고급 활용:

  • 자동 파티션 추가 스크립트 구현
  • 오래된 파티션 아카이빙 자동화
  • 파티션별 다른 스토리지 엔진/압축 설정

목록 파티셔닝 (List Partitioning)

특정 값 목록을 기준으로 데이터를 분할합니다.

-- PostgreSQL에서 지역별 목록 파티셔닝
CREATE TABLE customers (
  customer_id SERIAL,
  name VARCHAR(100),
  email VARCHAR(100),
  region VARCHAR(2),
  PRIMARY KEY (customer_id, region)
) PARTITION BY LIST (region);

CREATE TABLE customers_asia PARTITION OF customers
  FOR VALUES IN ('KR', 'JP', 'CN', 'IN');

CREATE TABLE customers_europe PARTITION OF customers
  FOR VALUES IN ('DE', 'FR', 'IT', 'ES', 'UK');

CREATE TABLE customers_americas PARTITION OF customers
  FOR VALUES IN ('US', 'CA', 'BR', 'MX');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

해시 파티셔닝 (Hash Partitioning)

해시 함수를 사용하여 데이터를 균등하게 분산시킵니다.

-- MySQL에서 해시 파티셔닝
CREATE TABLE sessions (
  session_id VARCHAR(32),
  user_id INT,
  created_at DATETIME,
  data TEXT,
  PRIMARY KEY (session_id)
)
PARTITION BY HASH (CRC32(session_id))
PARTITIONS 8;

복합 파티셔닝 (Composite Partitioning)

여러 파티셔닝 방식을 조합하여 더 세분화된 분할을 구현합니다.

-- PostgreSQL에서 범위-해시 복합 파티셔닝
CREATE TABLE sales (
  sale_id BIGINT,
  store_id INT,
  sale_date DATE,
  amount DECIMAL(10,2),
  PRIMARY KEY (sale_id, sale_date, store_id)
) PARTITION BY RANGE (sale_date);

-- 월별 파티션 생성
CREATE TABLE sales_2023_01 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
  PARTITION BY HASH (store_id);

-- 각 월별 파티션을 store_id로 하위 파티션
CREATE TABLE sales_2023_01_p1 PARTITION OF sales_2023_01
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sales_2023_01_p2 PARTITION OF sales_2023_01
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sales_2023_01_p3 PARTITION OF sales_2023_01
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sales_2023_01_p4 PARTITION OF sales_2023_01
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

샤딩 아키텍처 상세 설계 🌐

샤딩 키 선택 전략

샤딩의 성공은 샤딩 키 선택에 달려있습니다. 이상적인 샤딩 키는:

  1. 데이터를 균등하게 분산 (핫스팟 방지)
  2. 자주 함께 접근되는 데이터를 같은 샤드에 유지
  3. 샤드 간 조인이나 트랜잭션을 최소화

샤딩 키 선택 예시:

  • 사용자 데이터: user_id (사용자별 데이터 지역화)
  • 이커머스: customer_id (고객 중심) 또는 product_category (제품 카테고리별 분리)
  • 콘텐츠 플랫폼: content_id 해시 또는 geo_region (지역별 콘텐츠 제공)

일관된 해싱 알고리즘

샤드 수가 변경되어도 데이터 재배치를 최소화하는 방법입니다.

// 일관된 해싱 구현 예시 (Java)
public class ConsistentHashing {
    private final TreeMap<Long, String> ring = new TreeMap<>();
    private final int numberOfReplicas;
    private final HashFunction hashFunction;

    public ConsistentHashing(List<String> nodes, int numberOfReplicas) {
        this.numberOfReplicas = numberOfReplicas;
        this.hashFunction = Hashing.murmur3_128();

        for (String node : nodes) {
            addNode(node);
        }
    }

    public void addNode(String node) {
        for (int i = 0; i < numberOfReplicas; i++) {
            long hash = hashFunction.hashString(node + i, StandardCharsets.UTF_8).asLong();
            ring.put(hash, node);
        }
    }

    public void removeNode(String node) {
        for (int i = 0; i < numberOfReplicas; i++) {
            long hash = hashFunction.hashString(node + i, StandardCharsets.UTF_8).asLong();
            ring.remove(hash);
        }
    }

    public String getNode(String key) {
        if (ring.isEmpty()) {
            return null;
        }

        long hash = hashFunction.hashString(key, StandardCharsets.UTF_8).asLong();
        if (!ring.containsKey(hash)) {
            SortedMap<Long, String> tailMap = ring.tailMap(hash);
            hash = tailMap.isEmpty() ? ring.firstKey() : tailMap.firstKey();
        }

        return ring.get(hash);
    }
}

프록시 계층 구현

샤딩 로직을 애플리케이션에서 분리하여 관리를 단순화하는 방법입니다.

# Python으로 구현한 샤딩 프록시 예시
class ShardingProxy:
    def __init__(self, shard_config):
        self.shard_map = {}
        self.connections = {}

        # 샤드 설정 로드
        for shard_id, config in shard_config.items():
            conn = self._create_connection(config)
            self.connections[shard_id] = conn

    def _create_connection(self, config):
        # 데이터베이스 연결 생성
        return DatabaseConnection(
            host=config['host'],
            port=config['port'],
            username=config['username'],
            password=config['password'],
            database=config['database']
        )

    def _get_shard_for_key(self, key):
        # 샤딩 키를 해시하여 샤드 결정
        hash_value = murmur3_hash(str(key))
        shard_id = hash_value % len(self.connections)
        return shard_id

    def execute_query(self, shard_key, query, params=None):
        shard_id = self._get_shard_for_key(shard_key)
        connection = self.connections[shard_id]
        return connection.execute(query, params)

    def execute_cross_shard_query(self, query, params=None):
        # 모든 샤드에 쿼리 실행하고 결과 병합
        results = []
        for shard_id, connection in self.connections.items():
            result = connection.execute(query, params)
            results.extend(result)
        return results

리밸런싱 전략

샤드 간 데이터를 재분배하는 과정의 안전한 관리 방법입니다.

  1. 이중 쓰기 (Dual Write)
    • 새 샤드에 쓰기 시작하면서 기존 샤드에도 계속 쓰기
    • 모든 데이터가 복사되면 읽기를 새 샤드로 전환
    • 검증 후 이전 샤드 제거
  2. 점진적 마이그레이션
    • 한 번에 일부 데이터만 이동하여 영향 최소화
    • 키 범위별로 순차적 마이그레이션
    • 백그라운드 마이그레이션 + 변경사항 추적

샤딩 고급 패턴

연합 샤드 패턴 (Federated Shard Pattern):
여러 샤드의 스키마를 동일하게 유지하면서 중앙 카탈로그로 라우팅하는 방식

-- PostgreSQL의 외부 테이블을 이용한 연합 샤드 구현
CREATE EXTENSION postgres_fdw;

-- 원격 서버 정의
CREATE SERVER shard1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard1.example.com', port '5432', dbname 'users_db');

CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'shard2.example.com', port '5432', dbname 'users_db');

-- 사용자 매핑
CREATE USER MAPPING FOR local_user SERVER shard1
OPTIONS (user 'remote_user', password 'password');

CREATE USER MAPPING FOR local_user SERVER shard2
OPTIONS (user 'remote_user', password 'password');

-- 외부 테이블 생성
CREATE FOREIGN TABLE users_shard1 (
  user_id INT,
  username VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP
) SERVER shard1 OPTIONS (schema_name 'public', table_name 'users');

CREATE FOREIGN TABLE users_shard2 (
  user_id INT,
  username VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP
) SERVER shard2 OPTIONS (schema_name 'public', table_name 'users');

-- 뷰를 통한 통합 액세스
CREATE VIEW all_users AS
  SELECT * FROM users_shard1
  UNION ALL
  SELECT * FROM users_shard2;

데이터 타입과 저장 최적화

데이터 타입 성능 영향 분석 📋

각 데이터 타입의 선택이 저장 공간과 쿼리 성능에 미치는 영향을 이해하는 것이 중요합니다.

정수 타입 비교

데이터 타입 저장 크기 범위 권장 사용처
TINYINT 1바이트 -128~127 플래그, 작은 카운터
SMALLINT 2바이트 -32,768~32,767 중간 크기 ID, 카운터
INT 4바이트 -2^31~2^31-1 대부분의 ID 및 카운터
BIGINT 8바이트 -2^63~2^63-1 대규모 ID, 타임스탬프

실험 결과에 따르면, 동일한 1백만 개 레코드에 대해:

  • TINYINT: 0.98 MB 저장 공간, 조회 시간 0.12초
  • INT: 3.91 MB 저장 공간, 조회 시간 0.18초
  • BIGINT: 7.82 MB 저장 공간, 조회 시간 0.24초

문자열 타입 최적화

CHAR vs VARCHAR vs TEXT:

  • CHAR: 고정 길이, 짧은 문자열에 빠른 액세스
  • VARCHAR: 가변 길이, 저장 공간 효율적, 대부분의 문자열에 적합
  • TEXT: 대용량 텍스트, 별도 저장, 인덱싱 제한

VARCHAR 크기 최적화:

-- 비효율적
CREATE TABLE users (
  username VARCHAR(255), -- 실제로는 20자 이하만 사용
  country_code VARCHAR(255) -- 실제로는 2자리 코드만 사용
);

-- 최적화됨
CREATE TABLE users (
  username VARCHAR(30), -- 실제 사용 패턴 + 여유
  country_code CHAR(2)  -- ISO 국가 코드는 항상 2자
);

날짜/시간 타입 선택

정밀도와 성능 고려:

  • DATE: 날짜만 필요할 때 (3바이트)
  • DATETIME: 날짜+시간, 타임존 미포함 (8바이트)
  • TIMESTAMP: 날짜+시간, 타임존 변환 (4바이트)
-- 대량 로그 테이블의 최적화
CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_type TINYINT, -- ENUM 대신 숫자 코드
  created_at TIMESTAMP, -- DATETIME 대신 공간 절약
  user_id INT,
  data VARCHAR(500)
);

JSON vs. 구조화된 컬럼

현대 데이터베이스는 JSON 데이터 타입을 지원하지만, 사용 시 고려사항이 있습니다:

-- 유연성과 성능의 균형
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  -- 자주 검색되는 속성은 개별 컬럼으로
  category VARCHAR(50),
  brand VARCHAR(50),
  -- 가변적이고 덜 검색되는 속성은 JSON으로
  attributes JSON,
  -- JSON 필드 인덱싱 (PostgreSQL)
  INDEX idx_brand ((attributes->>'brand'))
);

PostgreSQL에서 JSONB 타입 사용 시 성능 이점:

  • 바이너리 저장으로 접근 속도 향상
  • GIN 인덱스로 내부 필드 검색 최적화
  • 중첩 구조에 대한 효율적인 쿼리

압축과 인코딩 전략 🗜️

대용량 데이터의 저장 효율성을 높이는 방법입니다.

테이블 압축 옵션

-- MySQL InnoDB 테이블 압축
CREATE TABLE logs (
  id BIGINT NOT NULL AUTO_INCREMENT,
  message TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- PostgreSQL 테이블 압축 (TOAST)
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  -- TOAST 압축 설정
  STORAGE EXTENDED
);

컬럼 인코딩 최적화

Amazon Redshift와 같은 컬럼형 데이터베이스의 인코딩 예시:

-- Redshift 컬럼 인코딩
CREATE TABLE sales (
  sale_id INTEGER ENCODE DELTA,           -- 연속 값에 효율적
  product_id INTEGER ENCODE LZO,          -- 일반 압축
  customer_id INTEGER ENCODE MOSTLY8,     -- 대부분 1바이트로 표현 가능한 값
  quantity SMALLINT ENCODE RUNLENGTH,     -- 반복값에 효율적
  sale_date DATE ENCODE BYTEDICT,         -- 제한된 값 집합
  region VARCHAR(30) ENCODE ZSTD          -- 고급 압축
);

시계열 데이터 압축

시계열 데이터베이스(예: TimescaleDB, InfluxDB)의 압축 전략:

-- TimescaleDB 압축 정책
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- 압축 설정
SELECT alter_table_set_options('metrics', compression_segmentby => 'device_id');

대용량 텍스트/BLOB 관리 📚

대용량 바이너리 데이터 처리를 위한 최적 방법입니다.

외부 저장소 활용

-- 메타데이터만 데이터베이스에 저장
CREATE TABLE documents (
  document_id INT PRIMARY KEY,
  filename VARCHAR(255),
  mime_type VARCHAR(100),
  file_size BIGINT,
  uploaded_at TIMESTAMP,
  storage_path VARCHAR(255), -- S3, GCS 등 외부 저장소 경로
  metadata JSON -- 검색 가능한 메타데이터
);

BLOB 청크 처리

대용량 파일을 청크로 나누어 관리하는 방법:

CREATE TABLE file_chunks (
  chunk_id INT AUTO_INCREMENT PRIMARY KEY,
  file_id INT,
  chunk_sequence INT,
  data BLOB,
  INDEX (file_id, chunk_sequence),
  FOREIGN KEY (file_id) REFERENCES files(file_id)
);

-- 청크 재구성 (애플리케이션 코드)
function getCompleteFile(fileId) {
  let chunks = db.query(
    "SELECT data FROM file_chunks WHERE file_id = ? ORDER BY chunk_sequence",
    [fileId]
  );

  return Buffer.concat(chunks.map(chunk => chunk.data));
}

실제 사례 연구

넷플릭스의 데이터베이스 확장 전략 🎬

넷플릭스는 전 세계 수억 명의 사용자에게 콘텐츠를 제공하기 위해 복잡한 데이터 아키텍처를 개발했습니다.

주요 전략

  1. 멀티 리전 데이터베이스: Cassandra를 사용하여 전 세계적으로 복제
  2. 데이터 지역화: 사용자에게 가까운 데이터베이스에 자주 접근하는 데이터 배치
  3. 이벤트 소싱: Kafka를 통한 이벤트 기반 아키텍처로 시스템 간 결합 감소
  4. 캐싱 계층: EVCache(트위터의 Memcached 확장)로 읽기 부하 감소

구현 상세

넷플릭스의 주요 데이터베이스 구성:

  • 사용자 프로필 및 시청 기록: Cassandra (키-값 저장소)
  • 콘텐츠 메타데이터: Document DB (문서 저장소)
  • 검색 기능: Elasticsearch (검색 엔진)
  • 추천 시스템: Spark + S3 (데이터 레이크)

금융 시스템의 트랜잭션 중심 설계 💰

높은 일관성과 무결성이 필요한 금융 시스템의 데이터베이스 설계 사례입니다.

주요 전략

  1. 샤딩 방지: 트랜잭션 무결성을 위해 수직적 확장 우선
  2. 읽기 전용 복제본: 보고 쿼리용 별도 인스턴스 구성
  3. 이력 테이블: 모든 변경사항 감사 추적
  4. 시간 기반 파티셔닝: 최근 데이터와 과거 데이터 분리

구현 예시

계좌 잔액 변경 시 트랜잭션 처리:

BEGIN TRANSACTION;

-- 계좌 잔액 조회
SELECT balance INTO @current_balance 
FROM accounts 
WHERE account_id = 12345 
FOR UPDATE; -- 행 잠금

-- 변경사항 이력 기록
INSERT INTO account_transactions (
  account_id, 
  transaction_type, 
  amount, 
  previous_balance,
  new_balance,
  transaction_date,
  reference_id
) VALUES (
  12345, 
  'DEBIT', 
  100.00, 
  @current_balance,
  @current_balance - 100.00,
  NOW(),
  'REF-123456'
);

-- 잔액 업데이트
UPDATE accounts 
SET balance = balance - 100.00,
    last_updated = NOW(),
    update_count = update_count + 1
WHERE account_id = 12345;

COMMIT;

테이블 설계의 미래 트렌드

다중 모델 데이터베이스 (Multi-model Databases) 🔄

단일 데이터베이스 시스템 내에서 여러 데이터 모델(관계형, 문서, 그래프 등)을 지원하는 접근 방식입니다.

ArangoDB 예시

// 관계형 모델과 그래프 모델 혼합 사용
const db = require('arangojs')();

// 컬렉션 생성 (SQL의 테이블과 유사)
db.collection('users').create();
db.collection('products').create();
db.collection('purchases').create();

// 그래프 관계 정의
db.graph('purchaseGraph').create({
  edgeDefinitions: [{
    collection: 'purchases',
    from: ['users'],
    to: ['products']
  }]
});

// 하이브리드 쿼리
const result = db.query(`
  FOR u IN users
    FILTER u.age > 30
    LET purchased = (
      FOR p IN OUTBOUND u purchases
        RETURN p.name
    )
    RETURN { user: u.name, purchasedProducts: purchased }
`);

서버리스 데이터베이스 (Serverless Databases) ☁️

확장성을 자동화하여 개발자가 인프라 관리 없이 데이터 모델링에 집중할 수 있게 하는 방식입니다.

AWS Aurora Serverless 설계 예시

-- 서버리스 환경에 최적화된 테이블 설계
CREATE TABLE events (
  event_id UUID PRIMARY KEY,
  event_type VARCHAR(50),
  user_id UUID,
  payload JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  -- 효율적인 파티션 접근을 위한 인덱스
  INDEX idx_user_type_time (user_id, event_type, created_at)
);

-- 단기 사용 테이블 (TTL 활용)
CREATE TABLE sessions (
  session_id VARCHAR(64) PRIMARY KEY,
  user_id UUID,
  data JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP,
  -- 만료된 세션 정리를 위한 인덱스
  INDEX idx_expires (expires_at)
);

시계열 최적화 (Time-Series Optimization) ⏱️

IoT, 모니터링, 금융 데이터 등 시간 기반 데이터를 효율적으로 처리하는 전문화된 설계입니다.

TimescaleDB 활용 예시

-- 하이퍼테이블 생성
CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  sensor_id INTEGER,
  location TEXT,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION
);

-- 시계열 테이블로 변환
SELECT create_hypertable('sensor_data', 'time', 
                         chunk_time_interval => INTERVAL '1 day');

-- 자동 롤업 뷰 생성
CREATE MATERIALIZED VIEW sensor_daily_summary
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', time) AS day,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp,
  AVG(humidity) AS avg_humidity
FROM sensor_data
GROUP BY day, sensor_id;

-- 보관 정책 설정
SELECT add_retention_policy('sensor_data', INTERVAL '3 months');
SELECT add_retention_policy('sensor_daily_summary', INTERVAL '5 years');

주의사항 및 심화 팁 💡

확장을 고려한 쿼리 패턴 🔍

확장 가능한 시스템에서는 쿼리 자체도 확장성을 고려해야 합니다.

안티패턴과 해결책

안티패턴: 오픈 엔드 쿼리

-- 위험한 쿼리 패턴
SELECT * FROM orders WHERE status = 'pending';

해결책: 페이징 및 제한

-- 개선된 쿼리 패턴
SELECT * FROM orders 
WHERE status = 'pending' 
AND order_id > ?last_seen_id 
ORDER BY order_id 
LIMIT 100;

안티패턴: N+1 쿼리

// 비효율적인 코드 (PHP 예시)
$orders = getOrders(); // 1개 쿼리
foreach ($orders as $order) {
    $orderItems = getOrderItems($order->id); // N개 추가 쿼리
}

해결책: 배치 조회

// 효율적인 코드
$orders = getOrders(); // 1개 쿼리
$orderIds = array_map(function($order) { 
    return $order->id; 
}, $orders);
$allOrderItems = getOrderItemsByOrderIds($orderIds); // 1개 추가 쿼리

고급 쿼리 최적화 기법

윈도우 함수 활용:

-- 개별 쿼리 대신 윈도우 함수 사용
SELECT 
  o.order_id,
  o.customer_id,
  o.order_date,
  ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) as order_rank
FROM orders o
WHERE o.order_date > '2023-01-01';

Common Table Expressions(CTE) 활용:

-- 복잡한 하위 쿼리 대신 CTE 사용
WITH active_customers AS (
  SELECT customer_id
  FROM customers
  WHERE status = 'active'
  AND last_order_date > CURRENT_DATE - INTERVAL '90 days'
),
customer_orders AS (
  SELECT 
    o.customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
  FROM orders o
  JOIN active_customers ac ON o.customer_id = ac.customer_id
  GROUP BY o.customer_id
)
SELECT 
  c.customer_id,
  c.name,
  co.order_count,
  co.total_spent
FROM customers c
JOIN customer_orders co ON c.customer_id = co.customer_id
ORDER BY co.total_spent DESC
LIMIT 100;

트랜잭션 관리와 락 전략 🔒

확장 가능한 시스템에서는 트랜잭션과 락 관리가 특히 중요합니다.

낙관적 락킹 (Optimistic Locking)

충돌이 드물다고 가정하고 버전 관리를 통해 충돌을 감지하는 방식입니다.

-- 버전 컬럼이 있는 테이블
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  stock INT,
  version INT DEFAULT 1
);

-- 낙관적 락킹을 사용한 업데이트
BEGIN TRANSACTION;

-- 현재 버전 조회
SELECT version INTO @current_version FROM products WHERE product_id = 123;

-- 재고 업데이트 시 버전 확인 및 증가
UPDATE products 
SET stock = stock - 1, 
    version = version + 1
WHERE product_id = 123 
  AND version = @current_version;

-- 업데이트된 행 수 확인
SELECT ROW_COUNT() INTO @updated_rows;

-- 충돌 발생 시 처리
IF @updated_rows = 0 THEN
  ROLLBACK;
  -- 충돌 처리 로직
ELSE
  COMMIT;
END IF;

분산 트랜잭션 처리

샤딩된 환경에서 트랜잭션 일관성을 유지하는 방법입니다.

  1. 2단계 커밋 (Two-Phase Commit)
  2. # 코디네이터 (애플리케이션 레벨) 1. 모든 샤드에 준비 요청 2. 모든 샤드가 준비 완료 응답하면 커밋 요청 3. 하나라도 준비 실패하면 롤백 요청
  3. 사가 패턴 (Saga Pattern)
  4. // Node.js로 구현한 사가 패턴 예시 async function createOrderSaga(orderId, customerId, items) { try { // 1. 주문 생성 await createOrder(orderId, customerId, items); // 2. 재고 감소 await reduceInventory(items); // 3. 결제 처리 await processPayment(orderId, calculateTotal(items)); // 4. 배송 요청 await createShipment(orderId); return { success: true }; } catch (error) { // 보상 트랜잭션 실행 if (error.step >= 3) await refundPayment(orderId); if (error.step >= 2) await restoreInventory(items); if (error.step >= 1) await cancelOrder(orderId); return { success: false, error }; } }

마치며

지금까지 확장 가능한 테이블 설계에 대해 깊이 있게 살펴보았습니다. 정규화와 비정규화의 균형, 효과적인 인덱싱, 파티셔닝과 샤딩 전략, 그리고 데이터 타입 최적화는 대규모 시스템 설계의 핵심입니다.

중요한 것은 모든 데이터베이스 설계가 트레이드오프의 산물이라는 점입니다. 완벽한 설계보다는 여러분의 특정 워크로드와 요구사항에 가장 적합한 설계를 선택하는 것이 중요합니다. 구글의 제프 딘(Jeff Dean)이 말했듯이, "모든 시스템은 특정 규모에서 깨진다. 중요한 것은 그 시스템이 깨지는 방식을 이해하고 대비하는 것이다."

데이터베이스 설계는 초기에 신중하게 이루어져야 하지만, 동시에 미래의 변화에 적응할 수 있는 유연성도 갖추어야 합니다. 계속해서 성능을 모니터링하고, 병목 현상을 파악하며, 필요에 따라 설계를 조정하는 과정이 중요합니다.

스타트업부터 엔터프라이즈까지, 모든 규모의 시스템이 데이터 폭증 시대에 경쟁력을 유지하기 위해서는 확장 가능한 테이블 설계가 필수적입니다. 이 글이 여러분의 데이터베이스 아키텍처 여정에 도움이 되었기를 바랍니다! 🚀

참고 자료 🔖

#데이터베이스설계 #확장성 #성능최적화 #인덱싱 #파티셔닝 #샤딩 #데이터아키텍처 #고성능DB

728x90
저작자표시 비영리 변경금지