EXPLAIN과 EXPLAIN ANALYZE 중 무엇을 먼저 보나요?

먼저 EXPLAIN으로 계획과 추정 비용을 보고, 스테이징·레플리카에서 EXPLAIN ANALYZE로 실제 시간과 행 수를 확인하는 순서가 일반적입니다. 프로덕션 직접 실행은 부하·락을 고려해 제한합니다.

인덱스를 많이 만들수록 빨라지나요?

아닙니다. 인덱스는 INSERT·UPDATE·DELETE와 Autovacuum 비용을 늘리고 저장공간을 씁니다. pg_stat_user_indexes 등으로 사용 여부를 보고 불필요한 인덱스를 정리하는 것이 중요합니다.

PgBouncer transaction 모드에서 주의할 점은?

세션 단위 기능(일부 SET, 임시 테이블, LISTEN/NOTIFY, 세션 수준 advisory lock 등)이 기대와 다르게 동작할 수 있습니다. 앱·ORM 설정과 함께 검증해야 합니다.

PostgreSQL 고급 최적화 가이드 — 쿼리 성능 튜닝·인덱스·파티셔닝·복제

2026년 4월 16일 · 60분 읽기 · 수정 2026년 4월 16일 고급

이 글의 핵심

EXPLAIN ANALYZE로 실행 계획을 읽고, B-tree·GIN·GiST·BRIN 인덱스를 워크로드에 맞게 선택합니다. 파티셔닝·샤딩, PgBouncer, Vacuum·Autovacuum, 스트리밍·논리 복제와 HA까지 실무 튜닝 관점에서 정리했습니다.

이 글의 핵심

PostgreSQL은 강력한 옵티마이저와 풍부한 인덱스·파티셔닝·복제 기능을 제공하지만, 운영 환경에서의 성능은 스키마·쿼리·설정·하드웨어가 맞물려 결정됩니다. 본 문서는 쿼리 계획 해석, 인덱스 선택, 파티셔닝과 샤딩, 연결 풀링, Vacuum, 복제·고가용성을 아우르는 고급 튜닝 관점의 참고서입니다.

대상 독자: PostgreSQL을 운영하거나 성능 이슈를 진단·개선해야 하는 백엔드·DBA·SRE. 기본 SQL·트랜잭션 개념은 알고 있다고 가정합니다.

1. 들어가며: 성능 문제의 범위

성능 이슈는 대개 다음 중 하나 이상에서 발생합니다.

잘못된 또는 부족한 인덱스로 인한 순차 스캔·불필요한 조인 비용
통계 부재·오래된 통계로 인한 비효율적 실행 계획
대량 갱신 후 쌓인 dead tuple과 bloat로 인한 I/O·Vacuum 부담
연결 수 폭증으로 인한 CPU·메모리·락 경합
단일 노드 한계를 스키마 설계만으로는 넘기 어려운 쓰기·읽기 확장 요구

이 글에서는 위 요소를 측정 가능한 지표(EXPLAIN, pg_stat_*, 모니터링)와 연결해 설명합니다.

2. EXPLAIN과 EXPLAIN ANALYZE

2.1 EXPLAIN만 쓸 때와 ANALYZE를 붙일 때

EXPLAIN: 옵티마이저가 선택한 실행 계획과 추정 비용을 보여 줍니다. 쿼리는 실행하지 않습니다(일부 경우 PREPARE 경로 등 예외가 있을 수 있으나 일반적으로 읽기 위주).
EXPLAIN ANALYZE: 계획을 실제로 실행하고, 실제 소요 시간·행 수를 수집합니다. INSERT/UPDATE/DELETE에 사용하면 데이터가 바뀌므로 트랜잭션·롤백 전략이 필요합니다.

프로덕션에서 EXPLAIN ANALYZE는 부하가 있으므로, 트래픽이 적은 레플리카·스테이징에서 먼저 수행하는 것이 안전합니다.

2.2 읽는 순서와 주요 필드

계획은 중첩된 트리입니다. 일반적으로 가장 안쪽(자식)이 먼저 실행되고, 부모 노드가 그 결과를 소비합니다(노드 유형에 따라 예외 있음).

자주 보는 항목은 다음과 같습니다.

항목	의미
`cost=시작..끝`	옵티마이저 추정 비용(임의 단위; 절대값보다 상대 비교에 유용)
`rows`	예상 출력 행 수
`width`	평균 행 폭(바이트 추정)
`actual time`	실제 첫 행~전체 완료 시간(`EXPLAIN ANALYZE` 전용)
`loops`	루프 횟수(중첩 루프 조인·서브플랜 등)

rows 추정이 실제 actual rows와 크게 다르면 통계(ANALYZE) 부족, 상관 서브쿼리, 분포 가정 위반 등을 의심합니다.

2.3 대표적인 노드 유형

Seq Scan: 테이블 전체 스캔. 인덱스가 없거나, 조건이 인덱스를 타기 어렵거나, 대부분의 행을 읽는 경우 선택됩니다.
Index Scan / Index Only Scan: 인덱스를 통한 접근. Index Only Scan은 visibility map 덕에 힙 접근을 생략할 수 있을 때 나타납니다.
Bitmap Index Scan + Bitmap Heap Scan: 여러 조건 결합·OR 등에서 비트맵으로 후보를 모은 뒤 힙을 읽습니다.
Nested Loop: 바깥 행마다 안쪽을 반복. 한쪽이 작고 인덱스로 안쪽을 빠르게 찾을 때 유리합니다.
Hash Join: 한쪽을 해시 테이블로 구축. 메모리(work_mem) 부족 시 디스크 스필이 발생할 수 있습니다.
Merge Join: 정렬된 입력이 있을 때 유리. 대량 정렬 비용이 전제됩니다.

2.4 실무에서 자주 쓰는 패턴

-- 비용·행 추정만 보기
EXPLAIN (FORMAT TEXT)
SELECT ...
;

-- 버퍼 히트까지 보고 싶을 때(공유 버퍼 관점 디버깅)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...
;

BUFFERS는 캐시 히트/미스를 구분해 디스크 병목 여부를 가늠하는 데 도움이 됩니다. FORMAT JSON은 도구·시각화 파이프라인에 넣기 좋습니다.

2.5 함정과 주의점

짧은 쿼리: 준비·계획 캐시·JIT 등으로 첫 실행과 이후 실행의 편차가 큽니다. 여러 번 반복 측정합니다.
파라미터 스니핑: 준비된 구문에서 특정 상수에 맞춰 계획이 고정되어 다른 입력에서 느려질 수 있습니다. 필요 시 PLAN 힌트 확장(별도 모듈) 또는 통계·쿼리 구조 조정을 검토합니다.
병렬 쿼리: Gather, Parallel Seq Scan 등이 보이면 workers 설정과 실제 이득을 함께 봅니다. 작은 테이블에서는 병렬 오버헤드만 커질 수 있습니다.

2.6 출력 예시로 읽는 법

아래는 형식을 익히기 위한 개념적 예시입니다(실제 수치는 환경마다 다릅니다).

Hash Join  (cost=... rows=1000) (actual time=0.5..120.0 rows=950 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0..5000 rows=50000) (actual time=0.1..40.0 rows=50000 loops=1)
        Filter: (created_at >= '2026-01-01')
  ->  Hash  (cost=... rows=...)
        ->  Index Scan using users_pkey on users u ...

해석 포인트: 바깥쪽 Seq Scan on orders가 실제 시간과 행 수에서 병목인지 확인합니다. users는 인덱스 스캔으로 작게 잡혔다면, orders에 (created_at) 또는 조인 키·조건에 맞는 복합 인덱스가 필요한지 검토합니다. Hash Join의 총 시간이 크다면 work_mem 부족으로 외부 정렬·해시 스필이 발생했는지 EXPLAIN의 Sort/Hash 노드와 로그를 함께 봅니다.

2.7 통계(ANALYZE)와 확장 통계

옵티마이저는 pg_stats에 기반해 선택도를 추정합니다. 대량 적재·갱신 직후에는 통계가 달라져 잘못된 Nested Loop 같은 계획이 나올 수 있습니다.

ANALYZE: 테이블·컬럼 통계 갱신. 배치 ETL 끝에 자동으로 돌도록 하거나, 작업 후 수동 실행합니다.
CREATE STATISTICS ... (dependencies): 컬럼 간 상관관계가 강할 때 추정 오류를 줄이는 데 도움이 됩니다(버전에 따라 문법·기능 확인).

default_statistics_target을 올리면 더 세밀한 히스토그램을 갖지만 ANALYZE 시간·메타데이터 크기가 늘 수 있어, 문제 테이블에만 컬럼 타깃을 높이는 편이 안전합니다.

3. 인덱스 전략: B-tree, GiST, GIN, BRIN

3.1 B-tree(기본)

대부분의 등호·범위·정렬에 적합합니다. UNIQUE, PRIMARY KEY도 B-tree가 기본입니다.

복합 인덱스 (a, b, c): a로 시작하는 조건에 가장 잘 맞습니다. 선두 컬럼이 쿼리 WHERE·ORDER BY와 맞는지가 중요합니다.
선택도: 카디널리티가 낮은 컬럼만 단독 인덱스로 두면 인덱스 스캔보다 순차 스캔이 나은 경우가 많습니다.

부분 인덱스로 범위를 줄이는 예입니다.

CREATE INDEX idx_orders_open ON orders (created_at)
WHERE status IN ('pending', 'paid');

3.2 GiST(Generalized Search Tree)

공간 데이터, 범위 겹침, 일부 풀텍스트·거리 계열 확장에서 쓰입니다. 예: PostGIS 기하형, tsvector의 일부 연산 경로 등(버전·연산자에 따라 GIN과 경쟁).

언제 고려하는가: R-tree류 탐색이 자연스러운 “겹침·근접·범위” 질의입니다.

3.3 GIN(Generalized Inverted Index)

배열, jsonb의 키/값 존재, tsvector 전문 검색 등 요소 기반 검색에 강합니다.

-- jsonb: containment
CREATE INDEX idx_data_gin ON docs USING GIN (data jsonb_path_ops);

-- 전문 검색
CREATE INDEX idx_body_fts ON articles USING GIN (to_tsvector('korean', body));

특징: 인덱스 크기와 갱신 비용이 클 수 있습니다. 쓰기 많은 테이블에서는 비동기 갱신(fastupdate 등, 연산자 클래스에 따라)과 VACUUM 전략을 함께 봅니다.

3.4 BRIN(Block Range Index)

물리적으로 근접한 행이 같은 값 범위를 가질 때(시계열의 created_at 등) 아주 작은 인덱스로 대용량 테이블 순차 스캔을 줄일 수 있습니다.

CREATE INDEX idx_events_brin ON events USING BRIN (created_at);

주의: 삽입 순서가 키와 무관하면 BRIN 효과가 떨어질 수 있습니다. 클러스터링(물리적 군집)과 함께 설계하는 경우가 많습니다.

3.5 인덱스 설계 실무 원칙

실제 쿼리·리포트 워크로드를 기준으로 설계한다(가상의 “나중에 필요” 인덱스는 쓰기·Vacuum 비용만 늘린다).
pg_stat_user_indexes로 사용되지 않는 인덱스를 주기적으로 확인한다.
중복 인덱스(선두 컬럼이 동일한 다중 인덱스)를 정리한다.
필요 시 INCLUDE 컬럼으로 커버링 인덱스를 고려한다(PostgreSQL 11+).

3.6 SP-GiST와 표현식·부분 인덱스

SP-GiST: 비균형 트리에 적합한 데이터(일부 텍스트·공간 구조 등)에 사용됩니다. PostGIS 등 확장 문서에서 연산자 클래스를 확인합니다.
표현식 인덱스: LOWER(email)처럼 함수 결과에 인덱스를 두어 쿼리와 정확히 맞출 수 있습니다. 쿼리에서도 동일한 표현식을 써야 타합니다.
부분 인덱스: 자주 조회되는 부분 집합만 인덱싱해 크기와 갱신 비용을 줄입니다(앞선 WHERE status IN (...) 예제 참고).

3.7 모니터링 쿼리 예시

-- 인덱스 사용 횟수 (스키마별로 필터 가능)
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

idx_scan이 0에 가깝고 오래 유지된다면 후보 제거를 검토합니다(배치·월간 리포트만 쓰는 인덱스는 예외일 수 있음).

4. 파티셔닝과 샤딩

4.1 파티셔닝(단일 클러스터 내)

PostgreSQL의 선언적 파티셔닝은 RANGE, LIST, HASH를 지원합니다. 대표적 이유는 다음과 같습니다.

프루닝: 조건에 맞는 파티션만 스캔
운영 작업: 오래된 파티션 드롭/디태치, 인덱스·VACUUM 단위 축소
대량 적재: 파티션별 병렬·스케줄링

CREATE TABLE events (
  id          bigserial,
  created_at  timestamptz not null,
  payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_q1 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

주의: 파티션 키가 쿼리와 맞지 않으면 모든 파티션 스캔으로 악화됩니다. 파티션 키는 접근 패턴과 일치시키는 것이 핵심입니다.

HASH 파티션은 키를 균등 분산할 때 유용하지만, 범위 조회(날짜 구간 리포트)에는 RANGE가 더 잘 맞는 경우가 많습니다. LIST는 지역·테넌트 코드처럼 명시적 구획이 있을 때 쓰입니다.

파티션 프루닝이 잘 되려면 WHERE에 파티션 키가 노출되어야 합니다. 동적 SQL이나 함수로 키가 가려지면 옵티마이저가 모든 파티션을 열 수 있으므로, 쿼리 형태를 통일하거나 체크 제약이 프루닝에 도움이 되도록 구조를 점검합니다.

4.2 샤딩(수평 분할)

PostgreSQL 자체는 자동 샤딩을 내장하지 않습니다. 일반적인 접근은 다음과 같습니다.

애플리케이션 샤딩: 샤드 키로 연결 문자열·스키마를 분기(Citus 등과 결합 가능)
Citus(확장): 분산 테이블·분산 쿼리를 지원, 멀티 테넌트·시계열에 자주 사용
Foreign Data Wrapper: 원격 DB 조합(운영 복잡도·지연 고려)

샤딩은 조인·트랜잭션·일관성 비용이 커지므로, 파티셔닝·읽기 스케일아웃·캐시로 해결 가능한지 먼저 검증하는 것이 좋습니다.

5. Connection Pooling과 PgBouncer

5.1 왜 풀이 필요한가

PostgreSQL은 프로세스당 연결 모델입니다. 연결이 많아지면 메모리·컨텍스트 스위칭·락 비용이 증가합니다. 애플리케이션 서버가 N대이면 DB 연결 수는 쉽게 폭증합니다.

5.2 PgBouncer 모드

모드	동작	적합한 경우
Session	클라이언트 세션과 DB 세션 1:1	세션 변수·임시 테이블·LISTEN 등 세션 상태 필요
Transaction	트랜잭션 종료 시 연결 반환	대부분의 무상태 웹 API
Statement	문장 단위(제약 많음)	특수 케이스

Transaction pooling을 쓰면 SET, PREPARE, advisory lock 등 세션 단위 기능이 깨질 수 있으므로 ORM·드라이버 설정과 함께 검증해야 합니다.

5.3 PgBouncer 설정 예시(개념)

pgbouncer.ini에서 풀 크기와 모드를 지정합니다. 실제 값은 CPU 코어 수, max_connections, 앱 서버 대수로 산정합니다.

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

default_pool_size는 데이터베이스·유저별로 DB에 연결되는 실제 백엔드 수에 가깝게 유지합니다. 앱이 서버당 수백 연결을 열어도 PgBouncer가 소수의 백엔드로 multiplexing합니다.

5.4 운영 팁

풀 크기는 max_connections와 애플리케이션 동시성을 함께 맞춥니다. 과도한 풀은 DB CPU 경합만 유발합니다.
애플리케이션 측 풀(HikariCP, pg 풀 등)과 이중 풀링이 중복되지 않게 조정합니다(예: 앱 풀은 짧게, PgBouncer에서 집중).
PgBouncer 앞단에 TLS 종료·네트워크 ACL을 두는 패턴이 흔합니다.

6. VACUUM과 Autovacuum 최적화

6.1 VACUUM의 역할

Dead tuple 정리: UPDATE/DELETE로 남은 이전 행 버전
동시성: VACUUM FULL과 달리 일반 VACUUM은 대부분 온라인으로 동작(LOCK 범위는 버전에 따라 다름)
Freeze: 트랜잭션 ID 고갈 방지
인덱스 청소: dead 항목 정리

6.2 Autovacuum 튜닝 포인트

자주 조정하는 파라미터는 다음과 같습니다. 프로덕션 변경 전에는 문서의 기본값과 팀의 기준을 확인하십시오.

파라미터	역할
`autovacuum_vacuum_scale_factor`	테이블 크기 대비 dead tuple 비율 임계값
`autovacuum_analyze_scale_factor`	ANALYZE 트리거 비율
`autovacuum_vacuum_threshold`	고정 행 수 임계값(작은 테이블에 영향)
`autovacuum_vacuum_cost_delay` / `..._limit`	한 번에 너무 많은 I/O를 하지 않도록 throttle
`autovacuum_max_workers`	동시 autovacuum 워커 수

쓰기가 매우 많은 테이블은 ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 500)처럼 테이블 단위로 더 자주 돌게 설정하는 경우가 있습니다. 반대로 거의 읽기 전용에 가깝다면 과도한 autovacuum은 불필요한 I/O를 유발할 수 있습니다.

대량 적재 후에는 수동 ANALYZE로 통계를 갱신하고, 필요 시 윈도우를 정해 VACUUM (ANALYZE)를 돌리는 전략을 씁니다.

6.3 Bloat 모니터링

장기간 누적된 bloat는 디스크·캐시 효율을 떨어뜨립니다. 확장(pgstattuple 등)이나 쿼리로 대략적 추정을 하고, 테이블·인덱스 재구축은 점검 창구에 계획합니다.

7. 복제와 고가용성

7.1 물리(스트리밍) 복제

WAL 스트리밍으로 스탠바이에 반영합니다. 동기/비동기, 다중 스탠바이, 승격(failover) 절차는 RPO/RTO 요구에 따라 설계합니다.

동기 복제: 커밋 지연 증가 가능, 데이터 손실 최소화
비동기 복제: 지연은 작을 수 있으나 승격 시 손실 가능성

synchronous_standby_names: 동기로 기다릴 스탠바이를 지정합니다. 동기 후보가 없으면 동기 모드의 동작(트랜잭션 블록 등)은 설정에 따라 달라지므로 운영 문서를 반드시 따릅니다.

복제 슬롯(replication slot): 스탠바이·논리 복제가 밀린 WAL을 붙잡아 필요한 시점까지 보관합니다. 소비자가 끊기면 디스크 사용량 증가로 이어질 수 있으므로, 모니터링과 장기 미사용 슬롯 정리가 필요합니다.

7.2 논리적 복제

테이블 단위 구독, 버전 업그레이드, 일부 데이터만 다른 클러스터로 흐르게 할 때 유용합니다. 충돌·DDL 제약을 이해하고 운영해야 합니다.

7.3 고가용성 도구

Patroni, repmgr, 클라우드 매니지드 서비스의 자동 페일오버 등은 리더 선출·헬스체크·split-brain 방지를 담당합니다. 애플리케이션은 현재 라이터/리더 엔드포인트를 어떻게 바라볼지(프록시, DNS, 서비스 디스커버리)를 함께 설계합니다.

7.4 읽기 부하 분산

리드 레플리카로 읽기를 분산할 수 있지만, 복제 지연(replication lag) 때문에 갓 쓴 데이터를 바로 읽는 패턴과 맞지 않을 수 있습니다. “read your writes” 요구가 있으면 라우팅 정책을 명확히 합니다.

8. 실전 튜닝 체크리스트

8.1 쿼리·스키마

느린 쿼리에 EXPLAIN (ANALYZE, BUFFERS) 확보
추정 rows와 실제 행 수 불일치 시 ANALYZE·통계 점검
불필요한 SELECT *, N+1, 과도한 조인·서브쿼리 개선
FK 인덱스 누락 여부 확인(조인·삭제·갱신 경로)

8.2 인덱스

워크로드에 맞는 인덱스 타입(B-tree/GIN/GiST/BRIN) 선택
미사용 인덱스 제거 후보 검토
쓰기·저장공간 비용 대비 이득 검증

8.3 서버·설정(개략)

shared_buffers, work_mem, maintenance_work_mem, effective_cache_size는 하드웨어·워크로드에 맞게(문서·벤치 기반)
max_connections와 PgBouncer·앱 풀 동시에 조정
디스크는 IOPS·지연, WAL·데이터 분리 여부 점검

8.4 Vacuum·유지보수

Autovacuum이 핫 테이블에서 충분히 도는지
대량 적재/삭제 후 수동 VACUUM/ANALYZE 계획
장기 트랜잭션이 없는지(pg_stat_activity, xid age)

8.5 복제·HA

복제 지연 모니터링
페일오버 절차·런북·애플리케이션 재연결
백업·PITR 정책과 복구 테스트

9. 트러블슈팅 시나리오

9.1 “갑자기 특정 쿼리만 느려졌다”

EXPLAIN (ANALYZE, BUFFERS)로 계획이 이전과 달라졌는지 확인합니다.
통계: 최근 대량 COPY/UPDATE 후 ANALYZE 누락 여부를 봅니다.
파라미터 스니핑(Prepared statement) 의심 시 동일 SQL을 리터럴로 한 번 실행해 비교합니다.
인덱스 사용 중단: 새로운 조건 때문에 인덱스를 타지 않게 바뀌었을 수 있습니다.

9.2 “CPU는 높은데 디스크 IOPS도 높다”

Checkpoint, Autovacuum, 대량 정렬/해시 스필을 pg_stat_activity·EXPLAIN·OS iostat와 함께 봅니다.
work_mem이 너무 작으면 디스크 스필이 늘어납니다(너무 크면 동시 쿼리에서 OOM 위험).

9.3 “연결은 많은데 처리량은 낮다”

max_connections 한계·컨텍스트 스위칭·락 대기를 의심합니다.
PgBouncer로 백엔드 수를 줄이고, 앱에서 불필요한 장기 트랜잭션을 제거합니다.

9.4 “디스크만 계속 찬다”

WAL 보관, 복제 슬롯, 로그, 임시 파일을 순서대로 확인합니다.
테이블 bloat는 VACUUM 전략과 점검 창구의 VACUUM FULL/재구축을 검토합니다(VACUUM FULL은 락·다운타임 이슈가 있으므로 신중히).

10. 정리

PostgreSQL 성능 최적화는 한 번의 설정 변경보다 실행 계획·통계·인덱스·Vacuum·연결·복제를 연결해 보는 반복 과정입니다. 본 가이드의 체크리스트를 기준으로 스테이징에서 측정·비교하고, 프로덕션에는 변경 관리·롤백 계획을 반드시 병행하기 바랍니다.

부록: 참고 SQL 스니펫

-- 테이블·인덱스 크기
SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class WHERE relname = 'your_table';

-- 현재 활동 세션 요약
SELECT pid, usename, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;

추가로 pg_stat_statements를 켜 두면 상위 쿼리별 총 시간·호출 수를 누적해 병목 우선순위를 정하는 데 매우 유용합니다(확장 설치 및 shared_preload_libraries 구성 필요).