PostgreSQL 파티셔닝 전략

Introduction

대용량 데이터베이스에서 테이블이 수억 건 이상으로 성장하면 단일 테이블에서의 쿼리 성능이 급격히 저하됩니다. PostgreSQL의 파티셔닝은 테이블을 논리적으로 분할하여 관리 효율성과 쿼리 성능을 크게 향상시킬 수 있습니다. 이 글에서는 다양한 파티셔닝 전략과 실제 구현 방법을 다루겠습니다.

Environment

-- PostgreSQL 버전 확인
SELECT version();

-- 파티셔닝 지원 확인 (PostgreSQL 10+)
SHOW server_version_num;

-- pgAdmin 또는 psql 연결
psql -U postgres -d mydb

Problem

기존 단일 테이블의 성능 문제:

-- 10억 건 이상의 로그 테이블
CREATE TABLE event_logs (
    id BIGSERIAL,
    event_type VARCHAR(50),
    user_id INTEGER,
    payload JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 인덱스 크기 확인
SELECT 
    pg_size_pretty(pg_relation_size('event_logs')) as table_size,
    pg_size_pretty(pg_indexes_size('event_logs')) as index_size;

-- 결과
-- table_size  | index_size
-- 128 GB      | 45 GB

-- 단순 조회 쿼리 실행 시간
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_logs 
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- 실행 시간: 45,234ms
-- Buffers: shared hit=2456789 read=123456

Analysis

파티셔닝이 필요한 이유를 분석했습니다:

-- 월별 데이터 분포 확인
SELECT 
    date_trunc('month', created_at) as month,
    COUNT(*) as row_count,
    pg_size_pretty(pg_relation_size('event_logs')) as estimated_size
FROM event_logs
GROUP BY date_trunc('month', created_at)
ORDER BY month DESC
LIMIT 12;

-- 결과
-- month       | row_count  | estimated_size
-- 2025-02-01  | 125000000  | 18 GB
-- 2025-01-01  | 118000000  | 17 GB
-- 2024-12-01  | 95000000   | 14 GB
-- ...

-- 쿼리 패턴 분석
EXPLAIN SELECT * FROM event_logs WHERE created_at = '2025-02-15';
-- Seq Scan on event_logs  (cost=0.00..2894567.89 rows=450000)
--   Filter: (created_at = '2025-02-15')
--   Rows Removed by Filter: 999550000

Solution

1단계: 범위 기반 파티셔닝 구현

-- 기존 테이블 백업 후 새 파티션 테이블 생성
CREATE TABLE event_logs_partitioned (
    id BIGSERIAL,
    event_type VARCHAR(50),
    user_id INTEGER,
    payload JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE event_logs_2024_01 PARTITION OF event_logs_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE event_logs_2024_02 PARTITION OF event_logs_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE event_logs_2025_01 PARTITION OF event_logs_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE event_logs_2025_02 PARTITION OF event_logs_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 기본 파티션 (미래 데이터용)
CREATE TABLE event_logs_default PARTITION OF event_logs_partitioned DEFAULT;

2단계: 자동 파티션 생성 함수

-- 자동으로 월별 파티션을 생성하는 함수
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    next_month DATE;
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    next_month := date_trunc('month', CURRENT_DATE + INTERVAL '1 month');
    partition_name := 'event_logs_' || to_char(next_month, 'YYYY_MM');
    start_date := next_month;
    end_date := next_month + INTERVAL '1 month';
    
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF event_logs_partitioned
             FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
        
        RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 매월 1일에 실행되도록 스케줄러에 등록
SELECT cron.schedule('create-partition', '0 0 1 * *', 
    'SELECT create_monthly_partition()');

3단계: 파티션 prune 확인

-- 파티션 pruning 확인
EXPLAIN SELECT * FROM event_logs_partitioned
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

-- 결과
-- Append  (cost=0.00..123456.78 rows=12500000)
--   ->  Seq Scan on event_logs_2025_01  (cost=0.00..123456.78 rows=12500000)
--         Filter: ((created_at >= '2025-01-01') AND (created_at < '2025-02-01'))

-- 실행 시간 비교
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_logs_partitioned
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';
-- 실행 시간: 892ms (기존 대비 50배 향상)

Lessons Learned

파티션 키 선택: 가장 많이 조회되는 컬럼을 파티션 키로 선택해야 합니다
미래 파티션 준비: 향후 데이터를 위한 파티션을 미리 생성해두는 것이 좋습니다
파티션 테이블의 인덱스: 각 파티션에 개별 인덱스를 생성하면 성능이 향상됩니다
VACUUM 고려: 파티션 테이블의 VACUUM은 각 파티션마다 개별적으로 수행됩니다
파티션 테이블의 제약 조건: 기본키와 유니크 제약 조건에 파티션 키가 포함되어야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

PostgreSQL 파티셔닝 전략: 대용량 테이블 분할 및 성능 최적화