DevOpsMar 28, 2026

Deep EVM #27: PostgreSQL 대규모 성능 — 인덱스, VACUUM, 쿼리 최적화

Engineering Team

PostgreSQL 성능의 기초

대규모 PostgreSQL 데이터베이스를 운영할 때 가장 중요한 세 가지 영역은 인덱스 전략, VACUUM 관리, 쿼리 최적화입니다. 이 세 가지를 올바르게 조합하면 수백만 행의 테이블에서도 밀리초 단위의 쿼리 응답을 유지할 수 있습니다.

인덱스 전략

B-Tree: 기본이자 최강

B-Tree 인덱스는 PostgreSQL의 기본 인덱스 유형이며, 대부분의 워크로드에 최적입니다:

-- 기본 B-Tree 인덱스
CREATE INDEX idx_transactions_block ON transactions (block_number);

-- 복합 인덱스: 왼쪽에서 오른쪽으로 선택도 높은 순서
CREATE INDEX idx_transactions_addr_block
    ON transactions (from_addr, block_number DESC);

복합 인덱스 설계의 핵심 규칙:

등호 조건 열을 먼저, 범위 조건 열을 나중에
선택도가 높은 열을 먼저
정렬 순서를 쿼리의 ORDER BY와 일치시키기

부분 인덱스: 필요한 것만 인덱싱

전체 테이블의 작은 부분만 자주 쿼리된다면, 부분 인덱스가 극적인 공간과 성능 개선을 제공합니다:

-- 최근 미확인 트랜잭션만 인덱싱
CREATE INDEX idx_pending_tx
    ON transactions (created_at)
    WHERE status = 'pending';

-- 고가치 전송만 인덱싱
CREATE INDEX idx_high_value_transfers
    ON transactions (block_number, from_addr)
    WHERE value > 1000000000000000000;  -- > 1 ETH

부분 인덱스는 행의 5% 미만이 조건을 충족할 때 특히 효과적입니다. 인덱스 크기가 95% 줄어들고, 삽입/업데이트 시 인덱스 유지 비용도 감소합니다.

BRIN: 시계열 데이터의 비밀 무기

Block Range INdex(BRIN)는 물리적으로 정렬된 데이터에 적합합니다:

CREATE INDEX idx_transactions_block_brin
    ON transactions USING brin (block_number)
    WITH (pages_per_range = 128);

BRIN vs B-Tree 비교:

특성	B-Tree	BRIN
인덱스 크기 (1억 행)	2.1 GB	5 MB
INSERT 오버헤드	높음	매우 낮음
포인트 쿼리	O(log N)	O(범위 스캔)
범위 쿼리	우수	우수 (정렬된 데이터)

BRIN은 시계열 데이터에서 B-Tree 대비 400배 작은 인덱스를 제공하면서, 범위 쿼리에서는 비슷한 성능을 유지합니다.

GIN: JSONB와 배열 검색

GIN 인덱스는 JSONB, 배열, 전문 검색에 사용됩니다:

-- JSONB 키 검색
CREATE INDEX idx_events_data ON events USING gin (data);

-- 특정 경로만 인덱싱 (더 효율적)
CREATE INDEX idx_events_type
    ON events USING gin ((data -> 'type'));

VACUUM 관리

PostgreSQL의 MVCC 모델은 업데이트/삭제된 행을 즉시 제거하지 않습니다. 대신 “데드 튜플“로 남겨두고, VACUUM이 정리합니다.

autovacuum 튜닝

기본 autovacuum 설정은 대규모 테이블에 불충분합니다:

-- 테이블별 autovacuum 설정
ALTER TABLE transactions SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- 기본 0.2 -> 0.01
    autovacuum_vacuum_threshold = 10000,        -- 기본 50
    autovacuum_analyze_scale_factor = 0.005,    -- 기본 0.1
    autovacuum_vacuum_cost_delay = 2            -- 기본 2ms -> 더 공격적
);

1억 행 테이블에서:

기본: 2천만 행이 변경되어야 VACUUM 트리거
튜닝 후: 100만 행 변경 시 트리거

VACUUM 모니터링

-- 데드 튜플 모니터링
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

데드 튜플 비율이 10%를 초과하면 VACUUM이 뒤처지고 있으며, autovacuum 설정을 더 공격적으로 조정해야 합니다.

EXPLAIN ANALYZE 읽기

EXPLAIN ANALYZE는 쿼리 최적화의 가장 강력한 도구입니다:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE from_addr = '\x1234...'
  AND block_number > 19000000
ORDER BY block_number DESC
LIMIT 50;

출력에서 주의할 핵심 지표:

actual time: 실제 실행 시간 (첫 행..마지막 행)
rows: 예상 행 vs 실제 행 (큰 차이 = 오래된 통계)
Buffers: shared hit (캐시) vs shared read (디스크)
Sort Method: quicksort(메모리) vs external merge(디스크)

일반적인 안티 패턴

-- 안티 패턴 1: SELECT * (필요 없는 열까지 로드)
-- 나쁨:
SELECT * FROM transactions WHERE block_number = 19000000;
-- 좋음:
SELECT tx_hash, from_addr, value FROM transactions WHERE block_number = 19000000;

-- 안티 패턴 2: 함수 래핑으로 인덱스 무효화
-- 나쁨 (인덱스 사용 불가):
SELECT * FROM events WHERE LOWER(event_type) = 'transfer';
-- 좋음 (표현식 인덱스 사용):
CREATE INDEX idx_events_type_lower ON events (LOWER(event_type));

-- 안티 패턴 3: OFFSET 페이지네이션
-- 나쁨 (OFFSET 1000000은 백만 행을 스캔 후 폐기):
SELECT * FROM transactions ORDER BY id OFFSET 1000000 LIMIT 50;
-- 좋음 (커서 기반 페이지네이션):
SELECT * FROM transactions WHERE id > 1000000 ORDER BY id LIMIT 50;

커넥션 풀링

PostgreSQL은 커넥션당 프로세스를 포크합니다. 커넥션이 100개를 넘으면 메모리와 컨텍스트 스위칭 오버헤드가 심각해집니다:

# PgBouncer 구성
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 300

PgBouncer를 사용하면 1000개의 애플리케이션 커넥션을 20개의 실제 PostgreSQL 커넥션으로 다중화할 수 있습니다.

쿼리 통계 모니터링

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

-- 가장 느린 쿼리 상위 10개
SELECT
    LEFT(query, 100) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_sec,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

결론

PostgreSQL 대규모 성능은 세 개의 기둥 위에 서 있습니다: 올바른 인덱스 전략(B-Tree, 부분 인덱스, BRIN, GIN), 공격적인 VACUUM 관리(autovacuum 튜닝, 데드 튜플 모니터링), 지능적인 쿼리 설계(EXPLAIN ANALYZE, 안티 패턴 회피). 이 세 가지를 체계적으로 접근하면, 수억 행의 테이블에서도 밀리초 단위의 응답 시간을 유지할 수 있습니다.

태그

DevOps Performance PostgreSQL