PostgreSQL 성능 튜닝 완벽 가이드 - 쿼리 최적화부터 파티셔닝까지

들어가며

서비스가 성장하면 어느 순간 DB가 병목이 됩니다. API 응답이 느려지고, 커넥션 풀이 고갈되고, 슬로우 쿼리 알림이 쏟아집니다. PostgreSQL은 뛰어난 쿼리 옵티마이저를 가지고 있지만, 올바른 튜닝 없이는 그 성능을 제대로 끌어낼 수 없습니다.

이 글에서는 PostgreSQL 성능 튜닝의 핵심인 EXPLAIN ANALYZE 읽는 법부터 인덱스 전략, 테이블 파티셔닝, 커넥션 풀링(PgBouncer), postgresql.conf 주요 설정까지 실무에서 바로 적용할 수 있는 내용을 다룹니다.

EXPLAIN ANALYZE 완전 정복

기본 사용법

PostgreSQL의 실행계획을 확인하는 가장 강력한 도구는 EXPLAIN ANALYZE입니다. EXPLAIN은 예상 계획만 보여주지만, ANALYZE를 추가하면 실제로 쿼리를 실행하고 측정값을 함께 보여줍니다.

-- 기본 실행계획 확인 (실제 실행하지 않음)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- 실제 실행 + 측정값 포함 (데이터 변경 쿼리는 트랜잭션으로 감싸기)
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'COMPLETED'
ORDER BY o.created_at DESC
LIMIT 100;
ROLLBACK;

실행계획 읽기 실전 예제

-- 실행계획 출력 예시
Limit  (cost=1523.45..1523.70 rows=100 width=52) (actual time=12.345..12.401 rows=100 loops=1)
  Buffers: shared hit=892 read=34
  ->  Sort  (cost=1523.45..1535.67 rows=4889 width=52) (actual time=12.342..12.365 rows=100 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 41kB
        Buffers: shared hit=892 read=34
        ->  Hash Join  (cost=234.50..1389.23 rows=4889 width=52) (actual time=2.105..10.234 rows=4889 loops=1)
              Hash Cond: (o.user_id = u.id)
              Buffers: shared hit=892 read=34
              ->  Bitmap Heap Scan on orders o  (cost=189.50..1298.73 rows=4889 width=36) (actual time=1.823..8.456 rows=4889 loops=1)
                    Recheck Cond: (created_at >= '2024-01-01'::date)
                    Filter: (status = 'COMPLETED')
                    Rows Removed by Filter: 1203
                    Heap Blocks: exact=567
                    Buffers: shared hit=580 read=34
                    ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..188.28 rows=6092 width=0) (actual time=1.234..1.234 rows=6092 loops=1)
                          Index Cond: (created_at >= '2024-01-01'::date)
                          Buffers: shared hit=47
              ->  Hash  (cost=30.00..30.00 rows=1200 width=20) (actual time=0.256..0.257 rows=1200 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 73kB
                    Buffers: shared hit=312
                    ->  Seq Scan on users u  (cost=0.00..30.00 rows=1200 width=20) (actual time=0.008..0.123 rows=1200 loops=1)
                          Buffers: shared hit=312
Planning Time: 0.456 ms
Execution Time: 12.567 ms

핵심 지표 해석

지표	의미	확인 포인트
cost	옵티마이저 예상 비용 (시작..전체)	상대적 비교용, 절대값은 무의미
rows	예상 행 수 vs 실제 행 수	차이가 크면 통계 정보 갱신 필요
actual time	실제 소요 시간 (ms)	첫번째 행..마지막 행 반환 시간
Buffers: shared hit	버퍼 캐시에서 읽은 페이지 수	높을수록 좋음 (캐시 히트)
Buffers: shared read	디스크에서 읽은 페이지 수	높으면 메모리 부족 의심
Rows Removed by Filter	필터로 제거된 행 수	높으면 인덱스 추가 고려

Sequential Scan vs Index Scan

스캔 방식 비교

스캔 방식	동작	적합한 상황
Sequential Scan	테이블 전체를 순차 읽기	대부분의 행을 읽어야 할 때 (선택도 > 10~20%)
Index Scan	인덱스 트리 탐색 + 테이블 접근	소수의 행을 선택할 때
Index Only Scan	인덱스만으로 결과 반환	SELECT 컬럼이 모두 인덱스에 포함될 때
Bitmap Index Scan	인덱스로 비트맵 생성 후 테이블 접근	중간 선택도, 여러 인덱스 결합 시

인덱스 설계 전략

-- 1. 복합 인덱스: WHERE 절의 등치 조건을 앞에, 범위 조건을 뒤에
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

-- 2. 부분 인덱스: 특정 조건에만 적용되는 인덱스 (크기 절약)
CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'PENDING';

-- 3. 커버링 인덱스: Index Only Scan 유도
CREATE INDEX idx_orders_covering
ON orders (user_id, status)
INCLUDE (total_amount, created_at);

-- 4. 표현식 인덱스: 함수 호출이 포함된 조건에 유용
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- 5. GIN 인덱스: 배열, JSONB, 전문검색에 사용
CREATE INDEX idx_products_tags
ON products USING GIN (tags);

-- 6. BRIN 인덱스: 시계열 데이터처럼 물리적 순서와 논리적 순서가 일치할 때
CREATE INDEX idx_logs_created_brin
ON access_logs USING BRIN (created_at)
WITH (pages_per_range = 32);

인덱스 사용 여부 확인

-- 사용되지 않는 인덱스 찾기
SELECT
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
  AND indexrelname NOT LIKE '%unique%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 테이블별 인덱스 히트율
SELECT
    relname,
    CASE WHEN idx_scan + seq_scan = 0 THEN 0
         ELSE round(100.0 * idx_scan / (idx_scan + seq_scan), 2)
    END AS index_hit_ratio,
    seq_scan,
    idx_scan,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY index_hit_ratio ASC;

파티셔닝 (Range / List / Hash)

파티셔닝이 필요한 시점

• 테이블 크기가 수천만 건 이상이고 특정 범위만 주로 조회할 때
• 오래된 데이터를 주기적으로 삭제(아카이빙)해야 할 때
• VACUUM이 오래 걸려 성능에 영향을 줄 때

Range 파티셔닝 - 주문 테이블 예제

-- 월별 Range 파티셔닝 테이블 생성
CREATE TABLE orders (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    product_id  BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- ... 반복

-- 파티션별 인덱스 (자동으로 각 파티션에 생성됨)
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status, created_at DESC);

-- 오래된 파티션 삭제 (DELETE보다 훨씬 빠름)
DROP TABLE orders_2023_01;  -- 해당 월 데이터 즉시 제거

-- 파티션 분리 (데이터 유지, 파티셔닝에서만 제외)
ALTER TABLE orders DETACH PARTITION orders_2023_02;

파티션 자동 생성 함수

-- 월별 파티션 자동 생성 함수
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    partition_date DATE;
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    -- 앞으로 3개월치 파티션 미리 생성
    FOR i IN 0..2 LOOP
        partition_date := DATE_TRUNC('month', NOW()) + (i || ' months')::INTERVAL;
        partition_name := 'orders_' || TO_CHAR(partition_date, 'YYYY_MM');
        start_date := partition_date;
        end_date := partition_date + '1 month'::INTERVAL;

        -- 파티션이 없으면 생성
        IF NOT EXISTS (
            SELECT 1 FROM pg_tables
            WHERE tablename = partition_name
        ) THEN
            EXECUTE FORMAT(
                'CREATE TABLE %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
                partition_name, start_date, end_date
            );
            RAISE NOTICE 'Created partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- pg_cron으로 매월 1일 자동 실행
SELECT cron.schedule('create-order-partitions', '0 0 1 * *', 'SELECT create_monthly_partition()');

List / Hash 파티셔닝

-- List 파티셔닝: 지역별 분리
CREATE TABLE customers (
    id BIGSERIAL,
    name TEXT NOT NULL,
    region VARCHAR(20) NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE customers_kr PARTITION OF customers FOR VALUES IN ('KR');
CREATE TABLE customers_us PARTITION OF customers FOR VALUES IN ('US');
CREATE TABLE customers_jp PARTITION OF customers FOR VALUES IN ('JP');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

-- Hash 파티셔닝: 균등 분배 (핫스팟 방지)
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    event_type TEXT,
    payload JSONB,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

커넥션 풀링 - PgBouncer

왜 PgBouncer가 필요한가?

PostgreSQL은 클라이언트 연결마다 별도의 프로세스를 생성합니다. 연결이 100개면 100개의 프로세스가 fork됩니다. MSA 환경에서 수십 개의 서비스가 각각 커넥션 풀을 가지면 DB 커넥션이 쉽게 수백 개를 넘기게 됩니다.

PgBouncer 설정

# pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop
analytics = host=127.0.0.1 port=5432 dbname=analytics

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 풀링 모드
# - session: 세션 종료까지 연결 점유 (prepared statement 사용 시)
# - transaction: 트랜잭션 종료 시 연결 반환 (권장)
# - statement: 문장 단위 (autocommit만 가능)
pool_mode = transaction

# 풀 크기 설정
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_client_conn = 1000
max_db_connections = 100

# 타임아웃 설정
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30
client_login_timeout = 15

# 로깅
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Spring Boot에서 PgBouncer 연결

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://pgbouncer-host:6432/shop
    username: app_user
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 20       # PgBouncer의 default_pool_size 이하로
      minimum-idle: 5
      connection-timeout: 5000
      idle-timeout: 300000
      max-lifetime: 600000
      # PgBouncer transaction 모드에서는 prepared statement 비활성화
      data-source-properties:
        prepareThreshold: 0

postgresql.conf 주요 설정

메모리 관련 설정

# 서버 RAM: 64GB 기준 설정 예시

# 공유 버퍼: 전체 RAM의 25%
shared_buffers = 16GB

# OS 캐시 추정값: 전체 RAM의 75%
effective_cache_size = 48GB

# 쿼리별 작업 메모리 (정렬, 해시 조인 등)
# 주의: 커넥션 * 쿼리당 사용 → 너무 크면 OOM 위험
work_mem = 64MB

# VACUUM, CREATE INDEX 등 유지보수 작업용
maintenance_work_mem = 2GB

# WAL 버퍼
wal_buffers = 64MB

쿼리 플래너 설정

# 병렬 쿼리 실행
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 100
min_parallel_table_scan_size = 8MB

# JIT 컴파일 (복잡한 쿼리 성능 향상)
jit = on
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000

# 랜덤 I/O 비용 (SSD 사용 시 낮추기)
random_page_cost = 1.1      # HDD: 4.0, SSD: 1.1~1.3
effective_io_concurrency = 200  # SSD 권장값

VACUUM 및 통계 설정

# Auto VACUUM 공격적으로 설정 (대용량 테이블)
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05    # 기본 0.2 → 0.05로 줄임
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02   # 기본 0.1 → 0.02로 줄임
autovacuum_vacuum_cost_limit = 1000      # 기본 200 → 더 공격적으로

# 통계 수집 정밀도
default_statistics_target = 200  # 기본 100 → 복잡한 쿼리가 많으면 200~500

# 수동 통계 갱신
ANALYZE orders;  -- 특정 테이블
ANALYZE;         -- 전체 데이터베이스

느린 쿼리 찾기

pg_stat_statements 활용

-- pg_stat_statements 확장 활성화
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf에 추가
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

-- 총 실행 시간 기준 상위 슬로우 쿼리
SELECT
    LEFT(query, 100) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(max_exec_time::numeric, 2) AS max_time_ms,
    rows,
    round((shared_blks_hit * 100.0 /
        NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS cache_hit_ratio
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

-- 캐시 히트율이 낮은 쿼리 (디스크 I/O 많은 쿼리)
SELECT
    LEFT(query, 100) AS short_query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    round((shared_blks_hit * 100.0 /
        NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY hit_ratio ASC
LIMIT 20;

auto_explain으로 자동 실행계획 수집

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

auto_explain.log_min_duration = 1000   # 1초 이상 걸린 쿼리
auto_explain.log_analyze = on          # ANALYZE 포함
auto_explain.log_buffers = on          # 버퍼 정보 포함
auto_explain.log_timing = on
auto_explain.log_nested_statements = on
auto_explain.log_format = json         # JSON 형식 (파싱 편리)

실전 튜닝 체크리스트

• Step 1: pg_stat_statements로 슬로우 쿼리 TOP 20 추출
• Step 2: EXPLAIN (ANALYZE, BUFFERS)로 실행계획 분석
• Step 3: Seq Scan이 불필요한 곳에 적절한 인덱스 추가
• Step 4: 예상 rows와 실제 rows 차이가 큰 경우 ANALYZE 실행
• Step 5: 대용량 테이블은 파티셔닝 검토
• Step 6: 커넥션 수가 많으면 PgBouncer 도입
• Step 7: postgresql.conf 메모리 설정 최적화
• Step 8: 캐시 히트율 99% 이상 유지 확인

마치며

PostgreSQL 성능 튜닝은 한 번에 끝나는 것이 아니라, 데이터가 쌓이고 트래픽 패턴이 변하면서 지속적으로 점검해야 하는 작업입니다. 이 글에서 다룬 핵심 내용을 정리하면 다음과 같습니다.

• EXPLAIN ANALYZE는 성능 문제 진단의 출발점입니다. actual time, Buffers, Rows Removed by Filter를 반드시 확인하세요
• 인덱스 설계는 등치 조건 → 범위 조건 순서를 지키고, 커버링 인덱스와 부분 인덱스를 적극 활용하세요
• 파티셔닝은 수천만 건 이상의 테이블에서 조회 성능과 데이터 관리 효율을 크게 개선합니다
• PgBouncer의 transaction 모드는 커넥션 효율을 5~10배 이상 높여줍니다
• pg_stat_statements로 주기적으로 슬로우 쿼리를 모니터링하고, 통계 정보를 최신으로 유지하세요

성능 튜닝에서 가장 중요한 것은 측정입니다. 감이 아닌 데이터에 기반해서 병목을 찾고, 변경 전후를 비교하는 습관을 들이시길 바랍니다.