Zettelkasten

파티셔닝은 논리적 한 테이블을 독립된 물리 세그먼트로 쪼개 안 읽을 조각을 통째로 건너뛴다

·수정 3회
databasepartitioningindexpaginationmysqlpostgresql

요약

  • 파티셔닝의 물리적 본질은 "논리적으로 하나인 테이블을 디스크 상 여러 개의 독립 저장 단위(파일/세그먼트 + 자기만의 인덱스)로 쪼개놓은 것"이다.
  • 빠른 이유는 옵티마이저가 안 읽을 조각을 read 자체를 하지 않기 때문(partition pruning)이며, DROP PARTITION이 즉시인 이유도 파일 unlink + 카탈로그 엔트리 제거로 끝나기 때문이다.
  • 시간 파티셔닝 + 최근순 cursor 페이징 + 복합 인덱스는 "어느 조각 / 조각 안에서 빨리 / 중복 없이" 역할로 한 세트처럼 움직인다.

본문

물리적 구현 — DB마다 다르지만 본질은 같다

PostgreSQL: 파티션은 사실 별개의 테이블 부모 테이블은 데이터가 0바이트인 껍데기고, 실제 데이터는 자식 테이블에 들어간다.

CREATE TABLE orders (id int, created_at date) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_06 PARTITION OF orders
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
  • orders_2026_06는 파일시스템에 자기만의 데이터 파일(base/<db_oid>/<relfilenode>)을 가진다. 부모 orders는 데이터 파일이 없다.
  • 각 자식은 자기만의 인덱스, 통계, visibility map을 가진 완전한 테이블이다.
  • INSERT 시 created_at을 보고 맞는 자식 파일로 tuple routing, SELECT 시 옵티마이저가 WHERE로 불필요한 자식을 플랜에서 제거한다. 본질적으로 자식들의 UNION ALL과 같다.

MySQL(InnoDB): 하나의 테이블, 여러 테이블스페이스 사용자에게 자식 테이블은 안 보이지만 내부적으로 각 파티션이 별도의 .ibd 파일(orders#p#p202606.ibd)로 저장되고, 각자 독립된 B+tree(클러스터드 + 세컨더리)를 가진다. (구분자 #p#는 MySQL 8.0부터 플랫폼 무관 소문자로 통일됐고, 구버전 Linux 데이터파일에서는 대문자 #P#/서브파티션 #SP#로 보인다.) 핸들러 레이어가 N개의 B+tree를 하나의 테이블처럼 묶어 보여준다.

공통 메커니즘 3가지

  1. 라우팅 함수 — RANGE(구간 이분 탐색), HASH(hash(key) % N), LIST(값 매핑)로 row를 어느 파티션에 넣을지 결정
  2. 메타데이터(카탈로그) — 파티션 구성·경계값·파일 위치 매핑. 옵티마이저가 플래닝 시 이걸 보고 어떤 파일을 열지 결정
  3. 독립된 저장 세그먼트 — 각 파티션 = 독립 파일/세그먼트 + 독립 인덱스. 물리적 분리의 본질

"빠르다"를 물리 레벨로 번역하면

  • 파티션 프루닝 = 옵티마이저가 "이 파일은 안 열어도 됨" 판단 → 디스크에서 해당 세그먼트 read 자체를 안 함
  • 인덱스가 작아짐 = 10억 row 한 B-tree(depth 5)가 아니라 파티션당 1000만 row B-tree(depth 3) → 디스크 페이지를 5번이 아니라 3번만 읽음
  • DROP PARTITION이 즉시 = row를 하나씩 지우고 로그 쓰는 게 아니라 파일 unlink + 카탈로그 엔트리 제거

buffer pool 이득은 "cold 페이지가 애초에 안 올라온다"는 것

흔한 오해: 파티셔닝이 buffer pool을 파티션별로 쪼개준다 → 틀림. buffer pool은 인스턴스 전체가 공유하는 고정 크기 LRU 캐시고(InnoDB 16KB / PG 8KB 페이지 단위), 파티션 전용 풀은 없다. 이득은 "어떤 페이지가 buffer pool에 들어오느냐"의 문제다.

  • 핵심: 안 읽힌 페이지는 캐시 공간을 안 먹는다. 프루닝으로 cold 파티션 파일을 read 자체를 안 하므로, buffer pool은 거의 전부 hot 데이터로 채워지고 적중률이 오른다.
  • 진짜 차이는 cache pollution 방지에 있다. 단일 큰 테이블은 평소엔 최근 페이지만 hot하지만, WHERE에 시간 조건 없는 전체 집계/리포트 쿼리가 한 방 들어오면 옛날 페이지까지 buffer pool로 쏟아져 들어와 LRU가 hot 페이지를 밀어낸다(eviction). 시간 파티셔닝 + 프루닝이면 cold 페이지가 애초에 안 올라오므로 hot working set이 밀려나지 않고 resident로 유지된다.
  • 인덱스 페이지도 마찬가지: buffer pool은 B-tree 내부 노드 페이지도 캐싱한다. hot 파티션 인덱스는 작아서(depth 3) 통째로 resident되기 쉽고, cold 파티션 인덱스 페이지는 안 읽혀 공간을 안 먹는다.
  • 거꾸로 가는 경우: ① 프루닝 실패(파티션 키 미사용) 시 모든 파티션을 read → 단일 테이블보다 심한 pollution. ② 파티션 과다 시 파티션마다 생기는 인덱스 상위 노드 페이지의 합이 buffer pool을 갉아먹음. (참고로 MySQL의 buffer pool instance 여러 개는 페이지를 해시 샤딩하는 것이지 파티션과 무관)

시간 파티셔닝과 cursor 페이징은 천생연분

정렬 키 = 파티션 키일 때(created_at desc로 정렬 + created_at으로 파티셔닝), 정렬 순서와 파티션 순서가 일치한다. DB는 최신 파티션부터 순서대로만 읽으면 된다.

1페이지 요청 → [2026_06]만 읽음        ← HOT만 건드림
N페이지 요청 → [2026_05] → [2026_04]… ← 그제서야 옛 파티션

대부분 유저는 1~2페이지만 보므로 최신 파티션만 hot하게 유지된다 — 설계 의도와 정확히 맞물린다.

단, offset이 아니라 cursor(keyset) 방식이어야 한다. LIMIT 20 OFFSET 10000은 10000개를 세서 건너뛰려 여러 파티션을 다 훑어야 해 뒤 페이지로 갈수록 폭망한다. cursor 방식은 마지막으로 본 값 기준으로 다음을 가져온다.

SELECT * FROM saved_posts
WHERE user_id = ?
  AND (created_at, id) < (:last_created_at, :last_id)   -- 커서
ORDER BY created_at DESC, id DESC
LIMIT 20;
  • 커서의 created_at이 곧 "어느 파티션부터 읽을지"를 알려줘 partition pruning이 자동으로 걸린다. 건너뛰는 비용 0.
  • tie-breaker 필수: created_at만으로 커서를 잡으면 같은 시각 데이터가 여러 개일 때 중복/누락이 생긴다. 항상 (created_at, id)처럼 unique 보조 키를 묶는다.

세 요소가 한 세트로 움직인다: 파티셔닝(어느 조각) + 복합 인덱스 (user_id, created_at, id)(조각 안에서 빨리) + 커서(중복 없이).

물리 레벨에서 오는 함정

  • 로컬 인덱스의 한계: PG/MySQL 인덱스는 파티션별로 쪼개진 로컬 인덱스라, 파티션 키가 안 걸린 unique 조회는 모든 파티션의 인덱스를 다 뒤져야 한다. (Oracle은 글로벌 인덱스 지원하지만 DROP 시 리빌드 필요)
  • 정렬 키 ≠ 파티션 키면 비싸짐: 파티션은 created_at인데 인기순·이름순으로 정렬하려면 모든 파티션에서 긁어와 merge 정렬해야 한다. 다른 정렬이 메인이면 파티션 키를 재검토해야 한다.
  • 파티션 수가 과하면 메타데이터·파일 핸들·플래닝 비용이 선형으로 늘어 오히려 느려진다.

관련 노트

참고

이 문서를 참조하는 노트 (2)

함께 읽기 좋은 글