PostgreSQL 파티셔닝 완벽 가이드: Range, List, Hash 전략과 성능 최적화

-- pg_stat_statements가 켜져 있다면 실제 쿼리 텍스트에서 세는 것이 가장 정확하다.
-- 컬럼 이름은 버전에 따라 다르므로 사용 중인 버전의 문서를 확인하세요.
SELECT calls, total_exec_time, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ILIKE '%from events%'
ORDER BY calls DESC
LIMIT 20;

SELECT region,
       count(*) AS rows,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM orders
GROUP BY region
ORDER BY rows DESC;

예시 출력

 region |   rows    |  pct
--------+-----------+-------
 KR     | 412000000 |  82.4
 US     |  51000000 |  10.2
 JP     |  28000000 |   5.6
 DE     |   9000000 |   1.8

-- 부모 테이블 생성
CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 기본 파티션 (범위에 맞는 파티션이 없을 때)
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- 각 파티션에 자동으로 생성되는 글로벌 인덱스
CREATE INDEX idx_events_type ON events (event_type);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 파티션별 로컬 인덱스
CREATE INDEX idx_events_2026_03_type
    ON events_2026_03 (event_type, created_at DESC);

-- 파티션 프루닝이 작동하는지 EXPLAIN으로 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-15'
  AND event_type = 'purchase';

-- 결과: events_2026_03 파티션만 스캔
-- Append
--   -> Index Scan using events_2026_03_type on events_2026_03
--        Index Cond: (event_type = 'purchase')
--        Filter: (created_at >= '2026-03-01' AND created_at < '2026-03-15')

-- 지역별 파티셔닝
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    region      VARCHAR(10) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_kr PARTITION OF orders
    FOR VALUES IN ('KR');

CREATE TABLE orders_jp PARTITION OF orders
    FOR VALUES IN ('JP');

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'GB', 'IT', 'ES');

CREATE TABLE orders_other PARTITION OF orders DEFAULT;

-- 사용자 ID 기반 해시 파티셔닝 (4개 파티션)
CREATE TABLE user_activities (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    activity    VARCHAR(100) NOT NULL,
    metadata    JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_activities_1 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_activities_2 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_activities_3 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 1차: 날짜(Range), 2차: 지역(List)
CREATE TABLE sales (
    id          BIGSERIAL,
    product_id  BIGINT NOT NULL,
    region      VARCHAR(10) NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    sold_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, sold_at, region)
) PARTITION BY RANGE (sold_at);

-- 월별 서브 파티션
CREATE TABLE sales_2026_03 PARTITION OF sales
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2026_03_kr PARTITION OF sales_2026_03
    FOR VALUES IN ('KR');

CREATE TABLE sales_2026_03_jp PARTITION OF sales_2026_03
    FOR VALUES IN ('JP');

CREATE TABLE sales_2026_03_other PARTITION OF sales_2026_03 DEFAULT;

-- pg_partman 설치
CREATE EXTENSION pg_partman;

-- 자동 파티션 관리 설정
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3,           -- 3개월 미리 생성
    p_start_partition := '2026-01-01'
);

-- 자동 유지보수 (cron으로 실행)
-- 새 파티션 생성 + 오래된 파티션 관리
SELECT partman.run_maintenance();

#!/bin/bash
# create_monthly_partitions.sh

PGHOST="localhost"
PGDB="mydb"
PGUSER="admin"

# 향후 3개월 파티션 생성
for i in 0 1 2 3; do
    MONTH=$(date -d "+${i} months" +%Y-%m-01)
    NEXT_MONTH=$(date -d "+$((i+1)) months" +%Y-%m-01)
    TABLE_NAME="events_$(date -d "+${i} months" +%Y_%m)"

    psql -h $PGHOST -d $PGDB -U $PGUSER -c "
        CREATE TABLE IF NOT EXISTS ${TABLE_NAME}
        PARTITION OF events
        FOR VALUES FROM ('${MONTH}') TO ('${NEXT_MONTH}');
    " 2>/dev/null

    echo "Created partition: ${TABLE_NAME}"
done

-- 파티션 분리 (데이터 보존, 쿼리에서 제외)
ALTER TABLE events DETACH PARTITION events_2025_01;

-- 분리된 파티션을 압축 테이블스페이스로 이동
ALTER TABLE events_2025_01 SET TABLESPACE archive_tablespace;

-- 또는 완전 삭제 (DROP이 DELETE보다 훨씬 빠름!)
DROP TABLE events_2025_01;
-- vs.
-- DELETE FROM events WHERE created_at < '2025-02-01';
-- ↑ 이 방식은 수백만 행 삭제 시 수십 분 소요

-- 1억 행 테이블 생성 (파티셔닝 없음)
CREATE TABLE events_no_part (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 동일 데이터로 파티셔닝 테이블 생성 (월별)
-- ... (위의 events 테이블 사용)

-- 1개월 데이터 조회
-- 파티셔닝 없음: 15.2초 (Full Table Scan)
-- 파티셔닝 있음: 0.8초 (Partition Pruning → 단일 파티션 스캔)

-- 인덱스 크기
-- 파티셔닝 없음: 2.1 GB (단일 인덱스)
-- 파티셔닝 있음: 175 MB/파티션 × 12 = 2.1 GB (총합은 같지만 개별 인덱스 효율적)

-- 데이터 삭제 (1개월분)
-- 파티셔닝 없음: DELETE → 45분 + VACUUM 30분
-- 파티셔닝 있음: DROP TABLE → 0.01초

-- 오류! 파티션 키(created_at)가 PK에 없음
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,  -- ERROR
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 올바른 방법: 복합 PK
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- UNIQUE 제약도 파티션 키를 포함해야 함
CREATE UNIQUE INDEX idx_events_unique
    ON events (event_type, created_at);  -- OK

-- 파티션 키 없는 UNIQUE는 불가
-- CREATE UNIQUE INDEX ON events (event_type);  -- ERROR

-- 파티션 키로 필터링하지 않으면 모든 파티션을 스캔
-- 반드시 WHERE 절에 파티션 키 포함!
SELECT * FROM events
WHERE created_at >= '2026-03-01'  -- 파티션 프루닝 작동
  AND event_type = 'purchase';

-- enable_partition_pruning 설정 확인
SHOW enable_partition_pruning;  -- 'on' 이어야 함

-- 파티션별 크기 확인
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size
FROM pg_tables
WHERE tablename LIKE 'events_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 파티션별 행 수 확인
SELECT
    relname as partition_name,
    n_live_tup as row_count
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY relname;

-- 파티션 프루닝 효과 확인
EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

예시 출력 — 프루닝이 걸린 경우

 Aggregate  (actual time=88.412..88.413 rows=1 loops=1)
   ->  Seq Scan on events_2026_03 events  (actual time=0.019..61.203 rows=1204411 loops=1)
         Filter: ((created_at >= '2026-03-01') AND (created_at < '2026-04-01'))
 Planning Time: 0.412 ms
 Execution Time: 88.501 ms

예시 출력 — 프루닝이 안 걸린 경우

 Aggregate  (actual time=2140.882..2140.883 rows=1 loops=1)
   ->  Append  (actual time=0.021..2004.114 rows=1204411 loops=1)
         ->  Seq Scan on events_2026_01 events_1  (actual rows=0 loops=1)
         ->  Seq Scan on events_2026_02 events_2  (actual rows=0 loops=1)
         ->  Seq Scan on events_2026_03 events_3  (actual rows=1204411 loops=1)
         ->  Seq Scan on events_default events_4  (actual rows=0 loops=1)
 Planning Time: 1.882 ms
 Execution Time: 2140.994 ms

SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY relname;