SQL - 파티션(Partition)

1️⃣ 파티션(Partition)

SQL에서 파티션으느 대용량 데이터를 효율적으로 관리하고 쿼리 성능을 최적화하기 위해 테이블이나 인덱스를 논리적/물리적으로 분할하는 기술이다. 데이터베이스에서 파티셔닝은 주로 성능 개선, 관리 용이성, 가용성 향상을 목표로 사용된다.

2️⃣ 정의 및 목적

정의

• 테이블이나 인덱스의 데이터를 특정 기준(예: 날짜,범위,해시 등)에 따라 여러 개의 작은 조각(파티션)으로 나누는 것

목적

• 성능 향상: 쿼리가 필요한 데이터만 스캔하도록 제한(파티션 프루닝)
• 관리 용이성: 데이터 삭제, 백업, 복구 등을 파티션 단위로 수행
• 확장성: 대용량 데이터베이스에서 부하 분산

3️⃣ 종류

파티셔닝 방식은 데이터 분할 기준에 따라 여러 유형(범위 파티션, 리스트 파티션, 해시 파티션, 컴포지트 파티션, 인터벌 파티션)으로 나뉜다.

1.범위 파티션

• 설명: 특정 열의 값 범위에 따라 데이터를 분할
• 사용 예시: 날짜, 숫자 등 연속적인 값에 적합
• 예시:

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

• 특징: sale_date가 2023년인 데이터는 p_2023 파티션에, 2024년은 p_2024에 저장

2.리스트 파티션

• 설명: 특정 열의 개별 값 목록에 따라 분할
• 사용 예시: 지역, 상태, 카테고리 등 이산적인 값에 적합
• 예시:

CREATE TABLE customers (
    customer_id NUMBER,
    region VARCHAR2(50),
    name VARCHAR2(100)
)
PARTITION BY LIST (region) (
    PARTITION p_asia VALUES ('Korea', 'Japan', 'China'),
    PARTITION p_europe VALUES ('Germany', 'France'),
    PARTITION p_others VALUES (DEFAULT)
);

• 특징: region이 'Korea'인 데이터는 p_asia 파티션에 저장

3.해시 파티션

• 설명: 해시 함수를 사용해 데이터를 고르게 분배
• 사용 예시: 데이터 분포를 균일하게 하고 싶을 때 (특정 기준 없음)
• 예시:

CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;

• 특징: customer_id의 해시 값에 따라 4개의 파티션에 자동 분배

4.컴포지트 파티션

• 설명: 두 가지 이상의 파티션 방식을 결합 (예: 범위 + 해시)
• 사용 예시: 복잡한 데이터 관리 필요 시
• 예시:

CREATE TABLE transactions (
    transaction_id NUMBER,
    trans_date DATE,
    customer_id NUMBER
)
PARTITION BY RANGE (trans_date)
SUBPARTITION BY HASH (customer_id) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
        (SUBPARTITION sp1, SUBPARTITION sp2),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
        (SUBPARTITION sp3, SUBPARTITION sp4)
);

• 특징: 연도별 범위 파티션 후, 각 파티션 내에서 customer_id로 해시 분할

5.인터벌 파티션

• 설명: 범위 파티션의 자동 확장 버전. 새로운 데이터가 범위를 벗어나면 파티션이 자동 생성
• 사용 예시: 날짜 기반 데이터에 적합
• 예시:

CREATE TABLE logs (
    log_id NUMBER,
    log_date DATE
)
PARTITION BY RANGE (log_date)
INTERVAL (NUMTODSINTERVAL(1, 'MONTH')) (
    PARTITION p_start VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

• 특징: 2023년 1월 이후 데이터가 추가될 때마다 매달 새로운 파티션 생성

4️⃣ 장/단점

장점

1. 성능 향상:

• 파티션 프루닝(Partition Pruning): 쿼리가 필요한 파티션만 스캔해 I/O 감소
• 예: WHERE sale_date = '2023-06-01' → p_2023만 조회

2. 관리 용이성:

• 오래된 데이터 삭제 시 전체 테이블이 아닌 파티션 단위로 DROP PARTITION 가능
• 백업/복구도 파티션별로 수행 가능

3. 병렬 처리:

• 파티션 단위로 쿼리나 인덱스 작업을 병렬화

4. 가용성:

• 특정 파티션이 손상되더라도 나머지 파티션은 사용 가능

단점

1. 복잡성 증가:

• 테이블 설계, 관리, 쿼리 작성이 더 복잡해짐

2. 오버헤드:

• 파티션 수가 많아지면 관리 비용 증가 (메타데이터 관리 등)

3. 제한된 쿼리 최적화:

• 파티션 키를 사용하지 않는 쿼리는 성능 개선 효과 없음

4. 스토리지 비용:

• 파티션별 인덱스 생성 시 디스크 사용량 증가 가능

5️⃣ 사용 시 교려사항

• 파티션 키 선정:
  • 자주 조회되는 열(예: 날짜, 지역)을 기준으로 선택
  • 데이터 분포가 균일해야 효과적
• 데이터 크기:
  • 작은 테이블에서는 파티셔닝의 이점이 미미할 수 있음 (보통 수백만~수십억 행 이상에서 유리)
• 쿼리 패턴:
  • 파티션 키를 활용하는 쿼리가 많아야 프루닝 효과 극대화
• DBMS 지원:
  • MySQL은 PARTITION BY 지원, PostgreSQL은 테이블 상속 기반, SQL Server는 파티션 테이블/인덱스 제공

6️⃣ 결론

파티션은 대용량 데이터를 효율적으로 관리하기 위한 강력한 도구로, 쿼리 성능과 관리 편의성을 크게 개선할 수 있다. 적용 시점은 데이터 크기가 크고, 특정 패턴으로 조회/삭제가 빈번할 때 유리하고, 주의할점은 설계 시 파티션 키와 쿼리 패턴을 신중히 고려해야 효과를 극대화 할 수 있다.