PostgreSQL 17 파티셔닝 전략과 병렬 쿼리 최적화 완벽 가이드

-- Range 파티셔닝: 월별 주문 테이블
CREATE TABLE orders (
    order_id    BIGSERIAL,
    customer_id BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      VARCHAR(20) DEFAULT 'pending'
) 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 TABLE orders_default PARTITION OF orders DEFAULT;

-- 각 파티션에 로컬 인덱스 자동 생성
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

-- List 파티셔닝: 지역별 사용자 테이블
CREATE TABLE users (
    user_id     BIGSERIAL,
    username    VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    region      VARCHAR(10) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE users_kr PARTITION OF users
    FOR VALUES IN ('KR');

CREATE TABLE users_us PARTITION OF users
    FOR VALUES IN ('US');

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

CREATE TABLE users_apac PARTITION OF users
    FOR VALUES IN ('JP', 'SG', 'AU', 'IN');

CREATE TABLE users_others PARTITION OF users DEFAULT;

-- Hash 파티셔닝: 세션 테이블을 4개 파티션으로 균등 분배
CREATE TABLE sessions (
    session_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     BIGINT NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ DEFAULT now(),
    expires_at  TIMESTAMPTZ
) PARTITION BY HASH (session_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 복합 파티셔닝: 연도별 Range -> 지역별 List
CREATE TABLE events (
    event_id    BIGSERIAL,
    event_type  VARCHAR(50),
    region      VARCHAR(10),
    event_date  DATE NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2026 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    PARTITION BY LIST (region);

CREATE TABLE events_2026_kr PARTITION OF events_2026
    FOR VALUES IN ('KR');

CREATE TABLE events_2026_us PARTITION OF events_2026
    FOR VALUES IN ('US');

CREATE TABLE events_2026_eu PARTITION OF events_2026
    FOR VALUES IN ('DE', 'FR', 'GB');

-- 컴파일 타임 프루닝 확인
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';

/*
결과:
  Append
    -> Seq Scan on orders_2026_03
          Filter: ((order_date >= '2026-03-01') AND (order_date < '2026-04-01'))
-- orders_2026_01, orders_2026_02는 프루닝되어 스캔하지 않음
*/

-- 런타임 프루닝: Prepared Statement에서 동작
PREPARE get_orders(date, date) AS
SELECT * FROM orders WHERE order_date >= $1 AND order_date < $2;

EXPLAIN ANALYZE EXECUTE get_orders('2026-02-01', '2026-03-01');
-- 실행 시점에 orders_2026_02만 스캔

-- 프루닝 동작 확인: enable_partition_pruning 설정
SHOW enable_partition_pruning;  -- 반드시 'on' 확인

-- 안티패턴: 파티션 키에 함수 적용 (프루닝 불가)
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
  AND EXTRACT(MONTH FROM order_date) = 3;
-- 모든 파티션을 스캔하게 됨

-- 올바른 패턴: 범위 조건 사용 (프루닝 동작)
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';
-- orders_2026_03만 스캔

-- 병렬 쿼리 파라미터 설정 (세션 레벨)
SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';

-- 대규모 파티셔닝 테이블 병렬 스캔
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-04-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

/*
실행 계획 예시:
  Limit  (cost=... rows=100)
    -> Sort  (cost=... rows=...)
          Sort Key: (sum(total_amount)) DESC
          -> Finalize GroupAggregate  (cost=... rows=...)
                Group Key: customer_id
                -> Gather Merge  (cost=... rows=...)
                      Workers Planned: 4
                      Workers Launched: 4
                      -> Partial GroupAggregate  (cost=... rows=...)
                            Group Key: customer_id
                            -> Parallel Append  (cost=... rows=...)
                                  -> Parallel Seq Scan on orders_2026_01
                                        Filter: (...)
                                  -> Parallel Seq Scan on orders_2026_02
                                        Filter: (...)
                                  -> Parallel Seq Scan on orders_2026_03
                                        Filter: (...)
  Planning Time: 2.1 ms
  Execution Time: 1,245 ms
*/

-- 병렬 Hash Join: 대규모 조인 최적화
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.order_date, c.username, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-01' AND o.order_date < '2026-04-01'
  AND o.total_amount > 10000;

/*
  Gather  (cost=... rows=...)
    Workers Planned: 4
    Workers Launched: 4
    -> Parallel Hash Join  (cost=... rows=...)
          Hash Cond: (o.customer_id = c.customer_id)
          -> Parallel Seq Scan on orders_2026_03 o
                Filter: (total_amount > 10000)
          -> Parallel Hash  (cost=... rows=...)
                Buckets: 65536  Batches: 1  Memory Usage: 12MB
                -> Seq Scan on customers c
*/

-- PostgreSQL 17: 월별 파티션을 분기별로 병합
ALTER TABLE orders
    MERGE PARTITIONS (orders_2026_01, orders_2026_02, orders_2026_03)
    INTO orders_2026_q1;

-- 병합 후 새 파티션 확인
SELECT
    parent.relname AS parent_table,
    child.relname  AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
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;

-- PostgreSQL 17: 분기 파티션을 다시 월별로 분할
ALTER TABLE orders
    SPLIT PARTITION orders_2026_q1 INTO (
        PARTITION orders_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'),
        PARTITION orders_2026_02 FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'),
        PARTITION orders_2026_03 FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
    );

-- 1단계: 확장 설치
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 2단계: 새로운 파티셔닝 테이블 생성
CREATE TABLE orders_partitioned (LIKE orders INCLUDING ALL)
    PARTITION BY RANGE (order_date);

-- 3단계: pg_partman으로 파티션 자동 생성
SELECT partman.create_parent(
    p_parent_table := 'public.orders_partitioned',
    p_control := 'order_date',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

-- 4단계: 데이터 마이그레이션 (배치 처리)
-- 한 번에 전체를 INSERT하면 WAL이 폭증하므로 배치로 나눈다
DO $$
DECLARE
    batch_start DATE := '2020-01-01';
    batch_end   DATE;
BEGIN
    WHILE batch_start < '2026-04-01' LOOP
        batch_end := batch_start + INTERVAL '1 month';
        INSERT INTO orders_partitioned
        SELECT * FROM orders
        WHERE order_date >= batch_start AND order_date < batch_end;
        RAISE NOTICE 'Migrated: % to %', batch_start, batch_end;
        batch_start := batch_end;
        PERFORM pg_sleep(0.5);  -- WAL 부하 분산
    END LOOP;
END $$;

-- 5단계: 테이블 교체 (짧은 잠금)
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_partitioned RENAME TO orders;
COMMIT;

-- 6단계: 검증 후 이전 테이블 제거
-- SELECT count(*) FROM orders;
-- SELECT count(*) FROM orders_old;
-- DROP TABLE orders_old;

-- 파티셔닝 테이블의 고유 제약조건: 파티션 키 포함 필수
-- 아래는 오류 발생 (파티션 키 미포함)
-- ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

-- 올바른 방법: 파티션 키 포함
ALTER TABLE orders ADD CONSTRAINT pk_orders
    PRIMARY KEY (order_id, order_date);

-- 특정 파티션만 vacuum
VACUUM (VERBOSE, ANALYZE) orders_2026_03;

-- 전체 파티셔닝 테이블 vacuum (각 파티션 순차)
VACUUM (VERBOSE, ANALYZE) orders;

-- autovacuum 튜닝: 파티션별 설정
ALTER TABLE orders_2026_03 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2
);

-- 현재 설정 확인
SHOW max_worker_processes;          -- 기본 8
SHOW max_parallel_workers;          -- 기본 8
SHOW max_parallel_workers_per_gather;  -- 기본 2

-- 동시 실행 중인 워커 수 확인
SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'parallel worker';

-- 해결: max_worker_processes 증가 (재시작 필요)
-- postgresql.conf
-- max_worker_processes = 16
-- max_parallel_workers = 12
-- max_parallel_workers_per_gather = 4

-- 문제 쿼리: 타입 불일치
-- 파티션 키가 DATE인데 TIMESTAMP로 비교
SELECT * FROM orders
WHERE order_date = '2026-03-15 00:00:00'::timestamp;

-- 해결: 정확한 타입으로 비교
SELECT * FROM orders
WHERE order_date = '2026-03-15'::date;

-- 프루닝 동작 검증
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE order_date = '2026-03-15'::date;
-- Append -> Seq Scan on orders_2026_03 (프루닝 정상)

-- lock_timeout 설정으로 빠른 실패
SET lock_timeout = '5s';

-- 비활성 트랜잭션 확인
SELECT pid, state, query_start, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
   OR state = 'idle in transaction';

-- 유지보수 윈도우에서 실행하거나, CONCURRENTLY 옵션 활용
-- (파티션 추가 자체는 CONCURRENTLY 미지원이므로 인덱스만)
CREATE INDEX CONCURRENTLY idx_new_part ON orders_2026_04 (customer_id);

-- 안전한 work_mem 계산
-- 총 메모리: 64GB, 최대 동시 연결: 200, 병렬 워커 최대: 4
-- work_mem = 64GB * 0.25 / 200 / 5 = 약 16MB
SET work_mem = '16MB';

-- 특정 쿼리에서만 임시로 높이기
SET LOCAL work_mem = '256MB';
SELECT ... ;  -- 대규모 집계 쿼리
RESET work_mem;