PostgreSQL 성능 최적화: 인덱스부터 쿼리 튜닝까지
데이터베이스는 애플리케이션의 심장이다. 아무리 잘 짜인 코드도 DB가 느리면 사용자 경험은 바닥을 친다. PostgreSQL은 강력한 오픈소스 RDBMS지만, 기본 설정만으로는 트래픽이 몰릴 때 한계를 드러낸다. 이 글에서는 인덱스 전략부터 쿼리 실행 계획 분석, 시스템 파라미터 튜닝까지 실전에서 바로 쓸 수 있는 최적화 기법을 단계별로 정리한다.
1. EXPLAIN ANALYZE로 병목 지점 찾기
최적화는 추측이 아닌 데이터에서 시작한다. EXPLAIN ANALYZE는 쿼리가 실제로 어떻게 실행되는지 보여주는 핵심 도구다.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;출력에서 주목해야 할 항목:
- actual time: 각 노드의 실제 실행 시간
- rows vs actual rows: 예측값과 실제값의 괴리 (10배 이상 차이나면 통계 갱신 필요)
- Seq Scan: 전체 테이블 스캔 - 인덱스 부재의 신호
- Buffers: shared hit/read: 캐시 히트율 확인
Seq Scan에 Filter가 붙어 있다면, 해당 컬럼에 인덱스가 없다는 뜻이다.
2. 인덱스 종류와 선택 기준
PostgreSQL은 다양한 인덱스 타입을 제공한다. 워크로드에 맞는 인덱스를 고르는 것이 성능의 첫 번째 열쇠다.
| 인덱스 타입 | 적합한 경우 | 주의사항 |
|---|---|---|
| B-tree | 등가, 범위, 정렬, NULL 검색 | 기본값, 대부분의 상황에 적합 |
| Hash | 단순 등가 비교(=)만 사용 | 범위 쿼리 불가 |
| GIN | JSONB, 배열, 전문 검색(tsvector) | 업데이트 비용 높음 |
| GiST | 지리공간 데이터, 범위 타입 | 특수 목적용 |
| BRIN | 타임스탬프, 순차 증가 컬럼이 있는 대용량 테이블 | 데이터 순서가 물리적으로 유지되어야 효과적 |
B-tree 인덱스 기본
-- 단일 컬럼 인덱스
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 복합 인덱스 (컬럼 순서가 중요)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 운영 중 테이블 잠금 없이 생성
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);GIN 인덱스 (JSONB 검색)
-- JSONB 컬럼 전체 인덱싱
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);
-- 특정 경로만 인덱싱 (더 효율적)
CREATE INDEX idx_products_tags ON products USING GIN((metadata -> 'tags'));BRIN 인덱스 (시계열 데이터)
-- 로그 테이블처럼 시간 순으로 insert되는 데이터에 효과적
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
-- B-tree 대비 저장 공간 1/100 수준3. 인덱스 고급 전략
부분 인덱스 (Partial Index)
전체 행이 아닌 특정 조건을 만족하는 행만 인덱싱한다. 인덱스 크기를 줄이고 성능을 높인다.
-- 활성 사용자만 인덱싱 (전체의 10%라면 인덱스 크기 90% 절감)
CREATE INDEX idx_users_active_email ON users(email)
WHERE status = 'active';
-- 미처리 주문만 인덱싱
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';커버링 인덱스 (Covering Index)
INCLUDE 절로 non-key 컬럼을 추가해 Index Only Scan을 유도한다. 테이블 접근 없이 인덱스만으로 결과를 반환한다.
-- user_id로 검색하면서 name, email도 자주 조회한다면
CREATE INDEX idx_users_covering ON users(user_id)
INCLUDE (name, email);표현식 인덱스 (Expression Index)
함수나 연산 결과에 인덱스를 건다.
-- 대소문자 구분 없는 이메일 검색
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 이제 이 쿼리가 인덱스를 활용한다
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';4. 쿼리 최적화 실전 기법
서브쿼리 대신 JOIN 사용
-- 느린 방식: 상관 서브쿼리
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;
-- 빠른 방식: JOIN + GROUP BY
SELECT u.name, COUNT(o.id) AS cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;CTE 활용과 주의점
PostgreSQL 12 이전에는 CTE가 최적화 장벽(optimization fence)으로 작동했다. 12 이후에는 기본적으로 인라인화되지만, 명시적으로 제어할 수 있다.
-- 인라인 CTE (최적화 허용, PG 12+)
WITH active_users AS NOT MATERIALIZED (
SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);
-- 명시적 구체화 (중간 결과를 캐시하고 싶을 때)
WITH expensive_query AS MATERIALIZED (
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT u.name, eq.total
FROM users u JOIN expensive_query eq ON u.id = eq.user_id;SELECT * 금지
-- 피해야 할 방식
SELECT * FROM users WHERE id = 1;
-- 올바른 방식: 필요한 컬럼만 지정
SELECT id, name, email FROM users WHERE id = 1;불필요한 데이터 전송을 줄이고, 커버링 인덱스 효과를 극대화한다.
5. 인덱스 사용 현황 모니터링
-- 인덱스 사용 통계 조회
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scan_count,
idx_tup_read AS tuples_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;idx_scan이 0에 가깝다면 사용되지 않는 인덱스다. 과감히 삭제하자.
-- 사용되지 않는 인덱스 목록
SELECT indexrelid::regclass AS index_name,
relid::regclass AS table_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS wasted_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint WHERE contype IN ('p', 'u')
);6. 느린 쿼리 자동 추적: pg_stat_statements
-- 확장 기능 활성화
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 가장 오래 걸리는 쿼리 TOP 10
SELECT
LEFT(query, 100) AS short_query,
calls,
total_exec_time / 1000 AS total_sec,
mean_exec_time / 1000 AS avg_sec,
rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;7. 메모리 및 시스템 파라미터 튜닝
쿼리와 인덱스를 최적화했다면 이제 시스템 레벨 튜닝 차례다. postgresql.conf에서 조정한다.
# 공유 버퍼: 총 RAM의 25~40%
shared_buffers = 4GB
# 정렬, 해시 조인용 메모리 (세션당 할당)
work_mem = 64MB
# autovacuum, 인덱스 생성용
maintenance_work_mem = 512MB
# 병렬 워커 수 (CPU 코어 수 기준)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
# 체크포인트 설정 (쓰기 I/O 분산)
checkpoint_completion_target = 0.9
wal_buffers = 64MB주의: work_mem은 세션당 할당이므로, 동시 접속이 많다면 과도하게 설정하지 않아야 한다.
8. VACUUM과 ANALYZE 관리
PostgreSQL은 MVCC(다중 버전 동시성 제어) 방식으로 동작한다. 업데이트나 삭제된 행은 즉시 제거되지 않고 dead tuple로 남는다. 이것이 쌓이면 테이블 블로팅이 발생하고 성능이 급격히 저하된다.
-- 테이블 블로팅 확인
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::text)) AS total_size,
n_dead_tup AS dead_tuples,
n_live_tup AS live_tuples,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- 수동 VACUUM
VACUUM ANALYZE orders;9. 파티셔닝으로 대용량 테이블 관리
수억 건이 넘는 테이블은 파티셔닝으로 관리한다. 쿼리가 특정 파티션만 스캔(Partition Pruning)하도록 유도하면 된다.
-- 레인지 파티셔닝 (월별)
CREATE TABLE orders (
id BIGSERIAL,
user_id INT,
amount DECIMAL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- 각 파티션에 로컬 인덱스
CREATE INDEX ON orders_2025_01(user_id);
CREATE INDEX ON orders_2025_02(user_id);마무리: 최적화는 반복이다
PostgreSQL 성능 최적화는 한 번에 끝나지 않는다. 데이터가 쌓이고 트래픽 패턴이 변하면 최적의 인덱스와 쿼리 전략도 달라진다. 핵심은 EXPLAIN ANALYZE로 현재 상태를 직시하고, 인덱스와 쿼리를 조금씩 개선하며, pg_stat_statements로 지속적으로 모니터링하는 루틴을 만드는 것이다.
코드벤터는 모두의회생(modoohs.com)을 비롯한 다양한 프로덕트를 직접 개발하고 운영하며 얻은 실전 경험을 기반으로, 개발자가 실제 현장에서 마주치는 문제들을 함께 해결해 나가고 있다. 앞으로도 코드픽(codepick.kr)을 통해 현장에서 바로 쓸 수 있는 기술 가이드를 꾸준히 공유할 예정이다.