파티션 운영

학습 목표

partition pruning이 실제 쿼리에서 동작하는지 EXPLAIN으로 검증할 수 있다.

DETACH·ATTACH·DROP으로 아카이브·복구 절차를 설계할 수 있다.

파티션별 크기·통계·vacuum 상태를 모니터링할 수 있다.

핫 파티션(latest range·skew) 증상과 완화 전략을 선택할 수 있다.

파티션 추가·DEFAULT·constraint exclusion 운영 함정을 피할 수 있다.

문제 상황

• orders RANGE 파티션 24개 — WHERE created_at > now()-7d 인데 전 파티션 scan
  • EXPLAIN Append child 24개
• 2023년 데이터 삭제 — DELETE 3시간 · WAL 폭주 · vacuum 밀림
• 아카이브 팀 — orders_2023_01 통째 S3 이동 후 DROP 원함
• 이번 달 orders_2025_06 — 디스크 40% · autovacuum 만 돌음 · 쓰기 지연
• 신규 월 시작 — 파티션 DDL 누락 · INSERT 실패
• MySQL PARTITION — p_future MAXVALUE 한 파티션에 쓰기 집중

앞서 파티셔닝 — range/hash 설계·pruning 개념; vacuum — 파티션 DROP이 freeze 부담 감소. 이번 편은 운영 루틴이다.

1. Pruning 검증

Partition pruning — planner가 불필요 child 제외 (실행 계획 8편).

조건	pruning
created_at >= '2025-06-01' AND created_at < '2025-07-01'	✅ 1 child
created_at BETWEEN ... literal	✅
created_at > $1 prepared	✅ (PG 11+ runtime)
date_trunc('month', created_at) = '2025-06-01'	❌ 함수 on key
파티션 키 없음	❌ 전 child

-- code/partition-pruning-check.sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-07-01';
-- Append -> Seq Scan on orders_2025_06 only

EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE date_trunc('month', created_at) = '2025-06-01';
-- Append -> all children (bad)

검증 루틴
배포 전	대표 쿼리 EXPLAIN — Append subplan 수
CI	EXPLAIN text assert child 이름
운영	pg_stat_statements — 높은 shared_blks_read + partitioned table

-- constraint exclusion (legacy) — declarative partition은 기본 pruning
SET constraint_exclusion = partition;

• JOIN — 파티션 키가 WHERE·ON에 있어야 prune
• Subpartition — parent prune 후 child prune 단계 적용

2. 파티션 생명주기: CREATE · DETACH · ATTACH · DROP

Declarative partition — DDL로 child 추가·분리·제거.

-- code/partition-lifecycle.sql
-- rolling: add next month before month starts
CREATE TABLE orders_2025_07 PARTITION OF orders
    FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

-- archive: detach → dump → drop (no mass DELETE)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- pg_dump orders_2024_01 → S3
DROP TABLE orders_2024_01;

-- reattach after validation (rare)
ALTER TABLE orders ATTACH PARTITION orders_2024_01_staging
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

단계	lock·영향
CREATE PARTITION	짧은 lock on parent
DETACH CONCURRENTLY (PG 14+)	덜 blocking — 대형 테이블
DETACH (default)	ACCESS EXCLUSIVE on parent
DROP detached	독립 테이블 — parent 무관

DELETE 10M rows  →  dead tuples · WAL · vacuum on all touched pages
DETACH + DROP    →  one partition file gone · parent metadata only

운영 패턴
월별 cron	다음 월 CREATE — 1일 전
보존 정책	13개월 유지 — 최고 오래 DETACH
DEFAULT	PARTITION ... DEFAULT — 누락 방지·모니터 필수

CREATE TABLE orders_default PARTITION OF orders DEFAULT;
-- alert if rows land here (missing monthly DDL)

• 앞서 vacuum — DROP partition = 해당 xid 부담 제거
• FK·global unique — detach 전 제약 확인

3. 파티션별 모니터링

한 logical table — child마다 다른 크기·bloat·통계.

-- code/partition-monitor.sql
SELECT c.relname AS partition,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       s.n_live_tup,
       s.n_dead_tup,
       s.last_autovacuum,
       s.last_autoanalyze
FROM pg_inherits i
JOIN pg_class p ON p.oid = i.inhparent
JOIN pg_class c ON c.oid = i.inhrelid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE p.relname = 'orders'
ORDER BY c.relname;

-- rows per partition (quick skew check)
SELECT tableoid::regclass AS part, count(*)
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 2 DESC;

지표	알림
단일 child size >> 평균	핫 파티션
n_dead_tup 비율	vacuum 22편
last_autoanalyze NULL	플랜 오차
default partition rows > 0	DDL 누락
pg_stat_statements per query	pruning 실패 의심

ANALYZE orders;  -- propagates to children (PG)
-- or per-partition for huge parents:
ANALYZE orders_2025_06;

• Observability — partition label 메트릭 (relname)
• 대시보드 — top-N largest children 주간 리뷰

4. 핫 파티션 대응

Range time — latest 월·일에 쓰기·인덱스 집중 (파티셔닝 15편).

증상
한 child IOPS·lock 대부분	latest range
autovacuum 만 해당 child	write heavy
replication lag 피크 시 동시	bulk insert

완화
Subpartition	RANGE(created_at) → HASH(tenant_id) 4-way
일 단위	월 대신 일 — 더 작은 blast radius
BRIN on time	wide partition seq scan 완화
쓰기 분산	hash shard 키 — range 아카이브 와 composite
Connection pool	hot child 전용 read replica
Batch insert	COPY·multi-row — index pressure

-- subpartition example
CREATE TABLE orders_2025_06 PARTITION OF orders
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01')
    PARTITION BY HASH (tenant_id);

CREATE TABLE orders_2025_06_p0 PARTITION OF orders_2025_06
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... p1, p2, p3

MySQL
MAXVALUE catch-all	신규 데이터 한 파티션 — REORGANIZE 분할
innodb_flush	hot file 단일 tablespace 분리

• 핫 ≠ 샤딩 필수 — subpartition·일 분할 먼저
• 슬로우 쿼리 — hot child 인덱스·pruning 재확인

5. 운영 루틴

주기	작업
월	다음 파티션 CREATE · 오래된 DETACH 검토
주	child size·dead_pct TOP
배포	pruning EXPLAIN 회귀
분기	보존 정책·default partition 점검
이벤트	bulk load 전 대상 child ANALYZE

Month N-1 end:  CREATE orders_YYYY_MM+1
Month N+12:     DETACH orders_YYYY_MM-12 → archive → DROP
Daily:          alert default partition row count

실수
DELETE 대신 DROP 가능한데 안 함	시간·WAL
DETACH 피크 시간	lock — CONCURRENTLY·창
ATTACH without VALIDATE	constraint 위반 — staging 검증
파티션 키 타입 불일치	ATTACH 실패

6. MySQL 파티션 운영 개요

	PostgreSQL	MySQL
아카이브	DETACH + DROP	ALTER TABLE ... DROP PARTITION
추가	CREATE TABLE ... PARTITION OF	ADD PARTITION / REORGANIZE
pruning	Append plan	partitions column in EXPLAIN
관리	declarative 강함	RANGE/LIST/HASH — 제약 많음

-- MySQL: drop old monthly partition (metadata only)
-- ALTER TABLE orders DROP PARTITION p202401;

• MySQL FK·unique 전역 — 파티션 제한 — 설계 선행
• 운영 공통 — 키 없는 쿼리 = 전 파티션 스캔

7. 정리

• pruning — 설계 ≠ 보장 — EXPLAIN 검증 필수
• DETACH·DROP — DELETE 대비 아카이브 표준
• 모니터링 — child 단위 size·vacuum·analyze
• 핫 파티션 — subpartition·finer range·쓰기 패턴
• DDL 자동화 — 다음 월 CREATE·default 알림
• vacuum·슬로우 쿼리 — 파티션 단위 로컬 튜닝

다음에 다룰 것

• CDC 심화
• Debezium 토폴로지, schema registry, exactly-once

---

해당 내용은 Database System Concepts, 7/E (Avraham Silberschatz, Henry F. Korth, S. Sudarshan), PostgreSQL · MySQL 공식 문서 의 내용을 기반으로 합니다.