PostgreSQL Partitioning 실전 가이드

-- 부모 테이블 생성
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (order_date);

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

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

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

-- 파티션별 인덱스 (자동 상속됨)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

-- 기본 파티션 (범위에 맞지 않는 데이터 수용)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (event_type);

CREATE TABLE events_user PARTITION OF events
    FOR VALUES IN ('user_signup', 'user_login', 'user_logout');

CREATE TABLE events_order PARTITION OF events
    FOR VALUES IN ('order_created', 'order_paid', 'order_cancelled');

CREATE TABLE events_system PARTITION OF events
    FOR VALUES IN ('health_check', 'deploy', 'config_change');

CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE TABLE user_sessions (
    id         BIGSERIAL,
    user_id    INTEGER NOT NULL,
    session_id UUID NOT NULL,
    data       JSONB,
    expires_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- 4개 파티션으로 균등 분배
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Partition pruning 활성화 확인
SHOW enable_partition_pruning;  -- on

-- EXPLAIN으로 pruning 확인
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2026-03-01'
  AND order_date < '2026-04-01';

-- 결과 예시:
-- Append (actual rows=50000)
--   -> Seq Scan on orders_2026_03 (actual rows=50000)
--        Filter: (order_date >= '2026-03-01' AND order_date < '2026-04-01')
-- ✅ orders_2026_01, orders_2026_02는 스캔하지 않음!

-- pg_partman 설치
CREATE EXTENSION pg_partman;

-- 자동 파티션 관리 설정
SELECT partman.create_parent(
    p_parent_table   => 'public.orders',
    p_control        => 'order_date',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3       -- 3개월 미리 생성
);

-- 유지보수 함수 (cron으로 매일 실행)
SELECT partman.run_maintenance();

# pg_partman 유지보수 (매일 새벽 2시)
0 2 * * * psql -U postgres -d mydb \
  -c "SELECT partman.run_maintenance();" \
  >> /var/log/pg_partman.log 2>&1

-- pg_partman 없이 직접 자동화
CREATE OR REPLACE FUNCTION create_monthly_partition(
    p_table TEXT,
    p_year INTEGER,
    p_month INTEGER
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('%s_%s_%s',
        p_table,
        p_year,
        LPAD(p_month::TEXT, 2, '0')
    );
    start_date := make_date(p_year, p_month, 1);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, p_table, start_date, end_date
    );

    RAISE NOTICE 'Created partition: %', partition_name;
END;
$$ LANGUAGE plpgsql;

-- 사용 예
SELECT create_monthly_partition('orders', 2026, 4);
SELECT create_monthly_partition('orders', 2026, 5);

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

-- 분리된 파티션을 별도 테이블로 유지하거나 삭제
DROP TABLE orders_2025_01;  -- 즉시 삭제 (수억 건도 순간)

-- 대비: DELETE는 매우 느림
-- DELETE FROM orders WHERE order_date < '2025-02-01';  ← 이러지 마세요!

-- 파티션별 행 수 확인
SELECT
    schemaname || '.' || relname AS partition,
    n_live_tup AS row_count,
    pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

-- 파티션 목록 조회
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS bounds
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

1. PRIMARY KEY에 파티션 키 포함 필수
   CREATE TABLE orders (...) PARTITION BY RANGE (order_date);
   → PK는 (id, order_date) 형태여야 함

2. UNIQUE 제약조건도 파티션 키 포함 필요

3. 파티션 수가 너무 많으면 플래닝 오버헤드 증가
   → 1000개 이하 유지 권장

4. 크로스 파티션 UPDATE는 PostgreSQL 11+에서만 지원