성능 튜닝과 쿼리 최적화

느린 쿼리는 데이터베이스 탓이 아니다

PostgreSQL 성능 문제의 대부분은 인덱스 누락, 통계 부재, 과도한 메모리 설정 오류 중 하나에서 시작된다. 서버 스펙을 높이기 전에 먼저 쿼리 실행 계획을 읽어야 한다. 실행 계획은 플래너가 어떻게 데이터를 읽을지 결정한 경로를 보여준다. 그 경로가 잘못됐다면 인덱스나 파라미터를 바꿔 올바른 경로로 유도할 수 있다.

EXPLAIN ANALYZE 읽는 법

기본 구조

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

출력에서 각 노드는 다음 형식으로 표현된다:

-> Seq Scan on orders  (cost=0.00..1240.00 rows=50000 width=64)
                       (actual time=0.024..8.312 rows=49823 loops=1)
   Buffers: shared hit=740

핵심 진단 포인트: rows(추정)와 actual rows(실제) 차이가 크면 통계가 낡았거나 인덱스 선택이 잘못된 것이다.

주요 노드 유형

실행 계획 읽기 실습

-- 1) 플랜 확인
EXPLAIN (ANALYZE, BUFFERS)
  SELECT user_id, SUM(amount)
  FROM orders
  WHERE created_at >= '2024-01-01'
  GROUP BY user_id;

-- 2) 결과에서 확인할 사항
--    - "Seq Scan" + 큰 rows → 인덱스 누락 의심
--    - "Sort Method: external merge Disk" → work_mem 부족
--    - "shared read" 비율 높음 → OS 페이지 캐시 미스, effective_cache_size 조정 검토
--    - "actual rows" << "rows" (또는 반대) → ANALYZE로 통계 갱신 필요

슬로우 쿼리 찾기: pg_stat_statements

pg_stat_statements는 쿼리별 누적 통계를 관리하는 확장이다. postgresql.conf에 로드해두면 애플리케이션 전체에서 어느 쿼리가 가장 많은 시간을 소비하는지 한눈에 볼 수 있다.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 확장 설치 (최초 1회)
CREATE EXTENSION pg_stat_statements;

-- 총 소요 시간 상위 10개 쿼리
SELECT
    LEFT(query, 80)        AS q,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric,  1) AS avg_ms,
    round(stddev_exec_time::numeric,1) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

total_exec_time이 큰 쿼리부터 EXPLAIN ANALYZE로 분석한다. stddev_ms가 높으면 특정 파라미터 값에서만 느린 경우다(파라미터 스니핑 문제).

log_min_duration_statement를 설정하면 지정 시간을 초과하는 쿼리를 로그에 기록한다:

# 500 ms 이상 쿼리를 로그에 기록
log_min_duration_statement = 500

인덱스 전략

인덱스 유형 선택

부분 인덱스(Partial Index)

자주 조회되지만 특정 조건에만 해당하는 데이터에 효과적이다.

-- 완료되지 않은 주문만 인덱스화
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

전체 데이터의 5%에 해당하는 pending 레코드만 인덱싱하므로 크기와 갱신 비용이 작다.

커버링 인덱스(Index Only Scan)

쿼리에 필요한 컬럼을 인덱스에 포함시켜 힙 접근을 없앤다.

-- user_id로 필터 + email·name 반환: 힙 불필요
CREATE INDEX idx_users_covering
  ON users (user_id) INCLUDE (email, name);

인덱스 활용도 확인

-- 사용 빈도 낮은(낭비 중인) 인덱스 조회
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan < 50
  AND schemaname NOT IN ('pg_catalog','pg_toast')
ORDER BY idx_scan;

idx_scan이 매우 낮은 인덱스는 쓰기 비용만 높이고 읽기 성능에 기여하지 않는다.

메모리 파라미터 튜닝

파라미터 설정 예시 (64 GB 서버)

# postgresql.conf
shared_buffers = 16GB                 # RAM 25%
effective_cache_size = 48GB           # RAM 75% (플래너 힌트)
work_mem = 32MB                       # sort/hash 연산당
maintenance_work_mem = 512MB          # VACUUM, CREATE INDEX
wal_buffers = 64MB                    # 기본 auto보다 명시 지정

# 병렬 쿼리
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# 통계 샘플링 정밀도 향상 (기본 100)
default_statistics_target = 200

work_mem을 너무 크게 설정하면 연결이 몰릴 때 OOM이 발생한다. pg_stat_statements에서 temp_blks_written이 높은 쿼리를 보고 그 쿼리에만 세션 수준으로 높이는 방식이 안전하다.

-- 특정 세션에서만 높임
SET work_mem = '256MB';
SELECT ... ORDER BY ...;
RESET work_mem;

통계 갱신과 플래너 힌트

플래너는 테이블 통계를 기반으로 실행 계획을 선택한다. 통계가 오래됐으면 행 수 추정이 틀려 잘못된 인덱스를 선택하거나 Hash Join 대신 Nested Loop를 사용할 수 있다.

-- 특정 테이블 통계 즉시 갱신
ANALYZE orders;

-- 샘플링 정밀도를 컬럼별로 조정 (기본 100, 최대 10000)
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- 플래너 통계 확인
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders';

correlation은 물리적 저장 순서와 논리적 순서의 유사도다. 1.0에 가까울수록 인덱스 스캔이 효과적이다.

연결 관리와 PgBouncer

PostgreSQL 프로세스 모델은 연결당 전용 프로세스를 생성한다. 수백 개의 연결이 동시에 활성화되면 컨텍스트 스위치 오버헤드가 급증한다. PgBouncer 같은 커넥션 풀러를 PostgreSQL 앞에 두어 실제 연결 수를 제한하는 것이 권장된다.

# PgBouncer 핵심 설정 (pgbouncer.ini)
[pgbouncer]
pool_mode = transaction          # 트랜잭션 단위 풀링 (가장 효율적)
max_client_conn = 500            # 클라이언트 수용
default_pool_size = 20           # 서버 연결 수
server_idle_timeout = 600

pool_mode = transaction은 트랜잭션이 끝나면 연결을 반환해 가장 적은 서버 연결로 높은 동시성을 처리한다. 단, SET 명령 · LISTEN · 준비된 문장(Prepared Statement) 사용에 제약이 있다.

실전 성능 튜닝 체크리스트

• pg_stat_statements에서 total_exec_time 상위 쿼리를 매주 검토한다.
• EXPLAIN (ANALYZE, BUFFERS) 결과에서 Seq Scan + 높은 rows는 인덱스 후보다.
• Sort Method: external merge Disk 등장 시 work_mem을 세션 수준에서 높여 재실행한다.
• actual rows와 rows 차이가 10배 이상이면 ANALYZE 후 재확인한다.
• 미사용 인덱스(idx_scan < 50)는 삭제를 검토한다.
• 연결 수가 150을 넘으면 PgBouncer 도입을 고려한다.

References

• https://www.postgresql.org/docs/current/using-explain.html
• https://www.postgresql.org/docs/current/sql-explain.html
• https://www.enterprisedb.com/blog/postgresql-query-optimization-performance-tuning-with-explain-analyze
• https://www.cybertec-postgresql.com/en/how-to-interpret-postgresql-explain-analyze-output/
• https://use-the-index-luke.com/sql/explain-plan/postgresql/getting-an-execution-plan
• https://www.percona.com/blog/tuning-postgresql-database-parameters-to-optimize-performance/
• https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• https://www.mydbops.com/blog/postgresql-parameter-tuning-best-practices
• https://oneuptime.com/blog/post/2026-01-25-postgresql-shared-buffers-work-mem-tuning/view
• https://www.crunchydata.com/blog/postgis-performance-postgres-tuning