우석이지 Blog

2025년 11월 30일
SQL Server

SQL SERVER 파티션 테이블 실무 사용법

by 전우석

대용량 테이블을 운영하다 보면 언젠가 “파티션” 이야기가 나옵니다.
다만 파티션은 흔히 오해하듯 성능 만능키가 아니라, 대부분의 현장에서는 관리(보관/삭제/유지보수) 도구로 더 큰 가치를 냅니다.

1. 파티션 테이블, 진짜 성능에 도움이 될까?

1.1 성능이 좋아지는 케이스(딱 핵심 1가지)

파티션이 성능에 도움이 되는 핵심 조건은 아래 하나로 요약됩니다.

  • WHERE 조건에 파티션 키가 포함되고
  • 옵티마이저가 파티션 제거(Partition Elimination) 를 할 수 있을 때

예: OrderDate 기준으로 월별 파티셔닝된 주문 로그 테이블

SELECT *
FROM dbo.OrderLog
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2025-02-01';

이 경우 SQL Server는 해당 월에 해당하는 파티션만 읽고,
나머지 파티션은 아예 건드리지 않기 때문에 전체 스캔보다 빠를 수 있습니다.

실무 팁: 날짜 필터는 >= / < 형태의 Half-open interval(반개구간)로 쓰는 것이 안전합니다.
BETWEEN이나 날짜 함수로 감싸는 표현은 의도치 않게 파티션 제거가 깨질 수 있습니다.

A visual representation comparing effective and ineffective SQL queries in partition elimination, showcasing a good query that targets a specific month (March) and a bad query that scans all partitions.

1.2 성능이 안 좋아지는 케이스(오히려 미세하게 손해일 수 있음)

반대로 파티션 키가 WHERE에 없다면?

SELECT *
FROM dbo.OrderLog
WHERE UserId = 12345;   -- 파티션 키가 아님

옵티마이저 입장에서는 “파티션이 여러 개 붙어 있는 큰 테이블 1개”일 뿐이고,
실제로는 모든 파티션을 대상으로 스캔/탐색을 하게 됩니다.
이때 파티션 경계 확인/메타데이터 처리 같은 오버헤드까지 더해져서

  • 비파티션 테이블과 비슷하거나
  • 경우에 따라 약간 더 느릴 수도 있습니다.

요약:

  • 파티션 키가 WHERE에 거의 안 들어가면 → 성능 이득은 거의 없음

1.3 “성능 튜닝 도구”라기보다 “관리 도구”

실무에서 파티션 도입 판단은 보통 이렇게 내려집니다.

  • “순수 성능 때문에?”
    → 먼저 인덱스/쿼리/통계/설계 개선을 최대한 한다.
  • “그래도 삭제(PURGE) / 보관 / 인덱스 유지보수 / 백업이 감당이 안 된다.”
    → 그때 파티션을 고려한다.

즉, 파티션은 대용량 테이블을 정리·보관·삭제하기 위한 관리 도구에 가깝습니다.

2. 실무에서 파티션을 도입하는 ‘진짜 이유’

현장에서 파티션 이야기가 나오는 가장 흔한 상황은 이런 형태입니다.

“접속 로그 테이블이 5억 건이 넘었는데,
매일 1,000만 건씩 DELETE 해서 3일째 끝이 안 나요…”

2.1 일반 DELETE 기반 PURGE의 문제점

보통 오래된 데이터를 지우기 위해 이렇게 합니다.

DELETE TOP (100000)
FROM dbo.AccessLog
WHERE LogDate < DATEADD(DAY, -90, GETDATE());

데이터가 많아질수록 아래 문제가 커집니다.

  • 삭제가 몇 시간~며칠씩 걸림
  • 대량 삭제로 트랜잭션 로그 폭증
  • 락 경합, I/O 부하, 인덱스 조각화 증가
  • 유지보수 윈도우(야간 배치 시간) 내에 못 끝나는 경우 발생

2.2 파티션 기반 PURGE의 장점(슬라이딩 윈도우)

LogDate 기준으로 파티셔닝해 두면, “90일 이전” 파티션을 통째로 비울 수 있습니다.

-- 예: 오래된 데이터가 들어 있는 특정 파티션을 통째로 삭제
TRUNCATE TABLE dbo.AccessLog
WITH (PARTITIONS (1));

(또는 더 실무적인 패턴은 SWITCH OUT → TRUNCATE/DROP 입니다. 아래 6장에서 정리합니다.)

파티션 기반 PURGE의 장점은 명확합니다.

  • 동작 자체가 “파티션 단위로 잘라내는” 형태가 되어 매우 빠름
  • 로그 사용량이 DELETE 대비 크게 줄어듦
  • 짧은 시간에 끝나서 유지보수 윈도우 내 처리 가능
  • 이 패턴으로 슬라이딩 윈도우(최근 N개 파티션만 유지) 구현 가능
A comparison graphic illustrating DELETE batch operations versus partition purging methods (TRUNCATE/SWITCH) over time, highlighting differences in time and log usage.

3. 파티션 도입을 결정하는 기준(현실 체크리스트)

아래 항목 중 3개 이상이 YES라면 파티션 도입을 진지하게 검토할 만합니다.

3.1 데이터 볼륨 & 성장 속도

  • ✅ 이미 수천만~수억 건 이상인 테이블이다.
  • ✅ 매일/매달 데이터가 크게 늘어나는 로그·이벤트·IoT 데이터다.

3.2 명확한 데이터 수명 주기(Lifecycle)

  • ✅ “최근 3개월/1년 데이터만 자주 조회하고, 나머지는 거의 안 본다.”
  • ✅ “3년 지난 데이터는 삭제하거나 별도 DB/스토리지로 아카이브한다.”

→ 규칙이 명확하면 파티션 단위 PURGE/아카이브 효과가 커집니다.

3.3 유지보수 작업 시간이 부담

  • ✅ 인덱스 재구성/재빌드가 몇 시간씩 걸린다.
  • ✅ 통계 업데이트, 백업, CHECKDB가 운영 시간에 영향을 줄 정도다.

→ 파티션 단위로 “핫 데이터 위주” 유지보수 전략이 가능해집니다.

3.4 인력/운영 역량

  • ✅ 팀에서 파티션 함수/스키마, SPLIT/MERGE/SWITCH/TRUNCATE PARTITION을 이해하고 운영할 수 있다.

파티션은 도입 후에도 경계값 관리(미리 SPLIT), 아카이브/삭제 루틴 등 운영 작업이 계속 필요합니다.
운영 역량이 정말 중요합니다.

A graphic illustrating when to use partitioning in database management, highlighting factors such as data volume and growth, lifecycle rules, operational expertise, and the role of partitioning as a management tool rather than solely a performance enhancement.

4. 파티션 기본 개념(꼭 알아야 하는 4가지)

(SQL Server 2022 기준이지만 개념 자체는 버전에 크게 의존하지 않습니다.)

4.1 파티션 테이블이란?

겉으로 보면 테이블은 1개지만, 내부적으로 여러 조각(파티션)으로 나뉘어 있습니다.

예:

  • P1: 2023년 이전
  • P2: 2023년
  • P3: 2024년
  • P4: 2025년 이후 …

4.2 파티션 키(Partition Key)

“어떤 컬럼을 기준으로 나눌지”를 결정하는 컬럼입니다.

실무에서 가장 흔한 키:

  • 날짜/시간: OrderDate, LogDate, CreatedAt 등

좋은 파티션 키의 조건:

  • WHERE에 자주 쓰이고
  • 데이터 수명주기(최근 N개월 유지 등)와 잘 맞는 컬럼

4.3 파티션 함수(Partition Function)

“어떤 값이 어느 파티션으로 들어갈지”를 정의하는 객체입니다.

CREATE PARTITION FUNCTION PF_LogDate (DATE)
AS RANGE RIGHT FOR VALUES
(
    ('2023-01-01'),
    ('2024-01-01'),
    ('2025-01-01')
);

RANGE RIGHT 기준으로 파티션 범위는 다음처럼 해석됩니다.

  • P1: 2023-01-01 미만
  • P2: 2023-01-01 이상 ~ 2024-01-01 미만
  • P3: 2024-01-01 이상 ~ 2025-01-01 미만
  • P4: 2025-01-01 이상

4.4 파티션 스키마(Partition Scheme)

“각 파티션을 어느 파일그룹에 저장할지”를 정의합니다.

CREATE PARTITION SCHEME PS_LogDate
AS PARTITION PF_LogDate
TO
(
    FG_OLD, -- P1
    FG_HOT, -- P2
    FG_HOT, -- P3
    FG_HOT  -- P4
);

실무 팁:

  • 오래된 데이터(P1)를 별도 파일그룹(FG_OLD)로 분리해 두면
    파일그룹 백업/복구, 스토리지 티어링 전략을 세우기 좋습니다.
  • 모든 파티션을 각각 다른 파일그룹으로 나눌 필요는 없습니다.
SQL Server 파티션 구축 블록 다이어그램: 파티션 함수, 파티션 스키마 및 파일 그룹을 보여주는 인포그래픽.

5. 샘플 시나리오: 주문 로그 테이블 월별 파티셔닝

초보도 따라 할 수 있는 예제로 핵심만 정리합니다.

5.1 현재 테이블 구조(비파티션)

CREATE TABLE dbo.OrderLog
(
    OrderLogId   BIGINT        IDENTITY(1,1) NOT NULL,
    OrderDate    DATE          NOT NULL,
    UserId       INT           NOT NULL,
    Amount       DECIMAL(18,2) NOT NULL,
    CreatedAt    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_OrderLog PRIMARY KEY CLUSTERED (OrderLogId)
);

현재는 일반 테이블(파티션 X)이고, 목표는 OrderDate 기준 월별 파티셔닝입니다.

5.2 파일그룹 준비(선택)

필수는 아니지만 핫/콜드 분리를 원한다면 파일그룹을 나눕니다.

ALTER DATABASE SalesDB ADD FILEGROUP FG_OLD;
ALTER DATABASE SalesDB ADD FILEGROUP FG_HOT;

ALTER DATABASE SalesDB
ADD FILE
(
    NAME = N'SalesDB_OLD',
    FILENAME = N'D:\MSSQL\DATA\SalesDB_OLD.ndf'
) TO FILEGROUP FG_OLD;

ALTER DATABASE SalesDB
ADD FILE
(
    NAME = N'SalesDB_HOT',
    FILENAME = N'D:\MSSQL\DATA\SalesDB_HOT.ndf'
) TO FILEGROUP FG_HOT;

5.3 파티션 함수 & 스키마 생성(월별 예시)

2024년 월별 경계값 예시:

CREATE PARTITION FUNCTION PF_OrderDate_Month (DATE)
AS RANGE RIGHT FOR VALUES
(
    ('2024-01-01'),
    ('2024-02-01'),
    ('2024-03-01'),
    ('2024-04-01'),
    ('2024-05-01'),
    ('2024-06-01')
);

파일그룹 매핑:

CREATE PARTITION SCHEME PS_OrderDate_Month
AS PARTITION PF_OrderDate_Month
TO
(
    FG_OLD,  -- P1: 2024-01-01 미만
    FG_HOT,  -- P2: 2024-01
    FG_HOT,  -- P3: 2024-02
    FG_HOT,  -- P4: 2024-03
    FG_HOT,  -- P5: 2024-04
    FG_HOT,  -- P6: 2024-05
    FG_HOT   -- P7: 2024-06-01 이상
);
월별 파티션 타임라인, 각 달의 시작일에 경계 표시, 차가운 데이터(FG_OLD)와 뜨거운 데이터(FG_HOT)로 구분된 파티셔닝 구조

5.4 파티션 테이블 생성(중요: Aligned Index)

핵심 포인트는 파티션 키(OrderDate)를 클러스터드 인덱스 키에 포함시키는 것입니다.
(파티션된 인덱스/제약을 “Aligned”하게 만들기 위한 기본 패턴)

CREATE TABLE dbo.OrderLog_Part
(
    OrderDate    DATE          NOT NULL,
    OrderLogId   BIGINT        IDENTITY(1,1) NOT NULL,
    UserId       INT           NOT NULL,
    Amount       DECIMAL(18,2) NOT NULL,
    CreatedAt    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_OrderLog_Part
        PRIMARY KEY CLUSTERED (OrderDate, OrderLogId)
) ON PS_OrderDate_Month (OrderDate);

🔍 왜 PK(클러스터 키) 순서를 바꿨을까?

  • 파티션 키가 클러스터 키에 포함되어야 인덱스가 파티션 스키마와 정렬(aligned)될 수 있고
  • 또한 파티션 테이블에서 UNIQUE 인덱스/PK를 만들 때 파티션 키 포함이 요구되는 경우가 많습니다(유일성 보장을 위해).
An infographic illustrating aligned indexes in database partitioning, comparing two scenarios: one where the clustered index key includes the partition key, and another where it does not. The left side highlights proper alignment with corresponding partition numbers, while the right side shows misalignment with potential issues in maintenance and switching.

5.5 기존 데이터 이관(가장 단순한 방식)

운영 환경에서는 다운타임/데이터량에 따라 전략이 달라지지만,
여기서는 이해를 위한 가장 단순한 방법만 정리합니다.

SET IDENTITY_INSERT dbo.OrderLog_Part ON;

INSERT INTO dbo.OrderLog_Part (OrderDate, OrderLogId, UserId, Amount, CreatedAt)
SELECT OrderDate, OrderLogId, UserId, Amount, CreatedAt
FROM dbo.OrderLog WITH (TABLOCK);

SET IDENTITY_INSERT dbo.OrderLog_Part OFF;

데이터 검증 후 테이블 교체:

DROP TABLE dbo.OrderLog;
EXEC sp_rename 'dbo.OrderLog_Part', 'OrderLog';

⚠️ 실제 운영에서는 반드시:

  • 전체 백업
  • 비상 롤백 계획
  • 충분한 사전 테스트
    를 거친 뒤 수행해야 합니다.

5.6 파티션별 행 수/경계값 확인

파티션이 잘 나뉘었는지 확인하려면 아래처럼 파티션별 row count를 확인합니다.

SELECT
    p.partition_number,
    ps.name AS partition_scheme,
    pf.name AS partition_function,
    prv.value AS boundary_value,
    SUM(st.row_count) AS row_count
FROM sys.dm_db_partition_stats st
JOIN sys.partitions p
  ON st.object_id = p.object_id
 AND st.index_id  = p.index_id
 AND st.partition_id = p.partition_id
JOIN sys.indexes i
  ON st.object_id = i.object_id
 AND st.index_id  = i.index_id
LEFT JOIN sys.partition_schemes ps
  ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf
  ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv
  ON pf.function_id  = prv.function_id
 AND prv.boundary_id = p.partition_number - 1
WHERE st.object_id = OBJECT_ID('dbo.OrderLog')
  AND st.index_id  = 1  -- clustered index
GROUP BY
    p.partition_number, ps.name, pf.name, prv.value
ORDER BY p.partition_number;

초보 입장에서는 “파티션 번호별로 행 수가 이렇게 나뉘는구나” 정도만 확인해도 충분합니다.

(추가 팁) 특정 날짜가 어느 파티션으로 들어가는지 빠르게 확인하려면 $PARTITION을 활용합니다.

SELECT $PARTITION.PF_OrderDate_Month('2024-03-15') AS partition_number;
Partitioned table results displaying partition number, boundary value, and row count, showcasing data distribution by date ranges.

6. PURGE/보관 실전 명령어 모음(슬라이딩 윈도우)

실제 운영에서 가장 자주 쓰는 파티션 명령어만 모았습니다.

6.1 새로운 기간용 파티션 추가 – SPLIT RANGE

새로운 월/연도가 다가오기 전에 미리 파티션 경계를 추가합니다.

중요: 파일그룹을 나눠 쓰는 경우, SPLIT 전에 NEXT USED로 “새 파티션이 들어갈 파일그룹”을 지정하는 패턴이 일반적입니다.

-- 새 파티션이 들어갈 파일그룹을 지정(예: FG_HOT)
ALTER PARTITION SCHEME PS_OrderDate_Month NEXT USED FG_HOT;
GO

-- 새 경계값 추가(예: 2024-07-01)
ALTER PARTITION FUNCTION PF_OrderDate_Month()
SPLIT RANGE ('2024-07-01');
GO

6.2 오래된 파티션을 아카이브 테이블로 SWITCH OUT

가정:

  • Partition 1이 오래된 데이터(예: 2024-01-01 미만)이고 FG_OLD에 있음

아카이브 테이블(비파티션) 생성 예시:

CREATE TABLE dbo.OrderLog_Archive_OLD
(
    OrderDate    DATE          NOT NULL,
    OrderLogId   BIGINT        NOT NULL,
    UserId       INT           NOT NULL,
    Amount       DECIMAL(18,2) NOT NULL,
    CreatedAt    DATETIME2(3)  NOT NULL,
    CONSTRAINT PK_OrderLog_Archive_OLD
        PRIMARY KEY CLUSTERED (OrderDate, OrderLogId),
    -- SWITCH 대상(파티션 범위)과 일치하는 CHECK 제약이 필요할 수 있습니다.
    CONSTRAINT CK_OrderLog_Archive_OLD_Range
        CHECK (OrderDate < '2024-01-01')
) ON FG_OLD;

파티션 1 전체를 아카이브 테이블로 이동:

ALTER TABLE dbo.OrderLog
SWITCH PARTITION 1 TO dbo.OrderLog_Archive_OLD;

✅ SWITCH 실무 필수 조건(요약)

  • 소스/타겟 테이블 컬럼 구조 동일(순서, 타입, NULL 허용 포함)
  • 인덱스 정의 정합성(특히 클러스터드 키/정렬/압축 등)
  • 타겟 테이블은 비어 있어야 함
  • 타겟 테이블은 보통 CHECK 제약으로 해당 파티션 범위를 보장해야 함
  • 소스 파티션과 타겟 테이블이 같은 파일그룹이어야 하는 케이스가 많음(구성/패턴에 따라 중요 포인트)

6.3 아카이브 없이 바로 삭제 – TRUNCATE PARTITION

그냥 오래된 데이터를 지워버리기만 하면 될 때:

TRUNCATE TABLE dbo.OrderLog
WITH (PARTITIONS (1));

여러 파티션을 한 번에 삭제:

TRUNCATE TABLE dbo.OrderLog
WITH (PARTITIONS (1 TO 3));

TRUNCATE는 대량 DELETE에 비해 로그 사용과 시간이 크게 줄어드는 장점이 있습니다.

6.4 필요 없는 경계값 정리 – MERGE RANGE

더 이상 세분화할 필요가 없는 경계값을 합칠 수 있습니다.

ALTER PARTITION FUNCTION PF_OrderDate_Month()
MERGE RANGE ('2024-01-01');

주의: MERGE는 파일그룹/데이터 이동 비용이 발생할 수 있습니다.
실무에서는 “비워진 파티션 정리” 같은 명확한 목적과 영향도 검토 후 적용하는 것을 권장합니다.

슬라이딩 윈도우 파티션 유지보수 단계: 파일그룹 선택, 새로운 경계 추가, 오래된 파티션을 아카이브 테이블로 이동, 아카이브 삭제 및 경계 병합.

7. 초보가 헷갈려 하는 포인트(FAQ)

Q1. 파티션 키는 무조건 PK여야 하나요?

아니요.
다만 실무에서는 클러스터드 인덱스 키에 파티션 키를 포함시키는 패턴이 매우 일반적입니다.

  • 파티션 키가 인덱스에 포함되지 않으면 aligned 설계가 어려워지고
  • SWITCH 같은 운영 작업에서 제약이 커질 수 있습니다.

Q2. 파티션만 걸면 모든 쿼리가 빨라지나요?

절대 아닙니다.
WHERE 조건에 파티션 키가 포함된 쿼리만 파티션 제거로 이득을 봅니다.
그 외 쿼리는 거의 동일하거나, 약간 느려질 수도 있습니다.

Q3. 파티션은 언제부터 진지하게 고려하면 될까요?

현실적인 기준을 한 줄로 압축하면:

  • “DELETE 기반 PURGE/유지보수가 어렵고 운영에 부담이 될 때”

처음부터 파티션을 도입하면 설계·운영 복잡도가 올라가므로,
정말 필요해졌을 때 도입하는 편이 실용적입니다.

8. 정리: SQL Server 2022에서 파티션을 어떻게 바라볼까?

  • 파티션 = 성능 튜닝 버튼 X
    → 인덱스/쿼리/통계 튜닝으로 해결되지 않는 대용량 PURGE·보관·유지보수 문제를 해결하는 도구
  • 파티션 키가 WHERE에 없으면 성능 이득은 거의 없다
    → 오히려 파티션 관리 오버헤드로 약간 손해일 수도 있음
  • 실무 도입 기준
  • 데이터가 이미 수천만~수억 건
  • 라이프사이클 규칙(최근 N개월 유지)이 명확
  • PURGE/인덱스 유지보수/백업 시간이 운영에 부담
  • 파티션을 설계·운영할 역량이 있음

Tags: