PostgreSQL 쿼리 최적화와 성능 튜닝 실전 가이드: EXPLAIN부터 파티셔닝까지

-- 기본 EXPLAIN ANALYZE 사용
EXPLAIN ANALYZE
SELECT u.id, u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.total_amount DESC
LIMIT 100;

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=45.123..45.156 rows=100 loops=1)
  ->  Sort  (cost=15234.56..15347.89 rows=45332 width=52) (actual time=45.121..45.142 rows=100 loops=1)
        Sort Key: o.total_amount DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Hash Join  (cost=3456.78..13890.12 rows=45332 width=52) (actual time=12.345..38.901 rows=45332 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..9876.54 rows=45332 width=20) (actual time=0.015..18.234 rows=45332 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 154668
              ->  Hash  (cost=2345.00..2345.00 rows=100000 width=36) (actual time=12.123..12.123 rows=100000 loops=1)
                    Buckets: 131072  Batches: 1  Memory Usage: 6234kB
                    ->  Seq Scan on users u  (cost=0.00..2345.00 rows=100000 width=36) (actual time=0.008..5.678 rows=100000 loops=1)
Planning Time: 0.456 ms
Execution Time: 45.234 ms

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders
WHERE user_id = 12345
  AND created_at BETWEEN '2026-01-01' AND '2026-03-14';

Index Scan using idx_orders_user_created on orders  (cost=0.43..8.45 rows=1 width=64) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: ((user_id = 12345) AND (created_at >= '2026-01-01') AND (created_at <= '2026-03-14'))
  Buffers: shared hit=4
Planning Time: 0.123 ms
Execution Time: 0.045 ms

-- 단일 컬럼 B-tree 인덱스
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 복합 인덱스: 선행 컬럼 순서가 중요하다
-- WHERE user_id = ? AND created_at >= ? 형태의 쿼리에 최적
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

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

-- 부분 인덱스: 특정 조건의 행만 인덱싱하여 크기를 줄인다
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
  WHERE status = 'active';

-- JSONB 필드에 대한 GIN 인덱스
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- jsonb_path_ops: 특정 연산자에 더 효율적이고 인덱스 크기가 작다
CREATE INDEX idx_products_metadata_path ON products USING GIN (metadata jsonb_path_ops);

-- 전문 검색용 GIN 인덱스
CREATE INDEX idx_articles_search ON articles USING GIN (
  to_tsvector('korean', title || ' ' || content)
);

-- 배열 컬럼에 대한 GIN
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- PostGIS 공간 인덱스
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- 범위 타입 인덱스
CREATE INDEX idx_reservations_period ON reservations USING GiST (
  tstzrange(start_time, end_time)
);

-- 범위 겹침 검색
SELECT * FROM reservations
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-14 09:00', '2026-03-14 18:00');

-- 시계열 로그 테이블에 BRIN 인덱스
CREATE INDEX idx_logs_created_brin ON access_logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- BRIN이 효과적인지 확인: 상관계수가 1에 가까워야 한다
SELECT correlation
FROM pg_stats
WHERE tablename = 'access_logs' AND attname = 'created_at';

-- Bad: 애플리케이션에서 N+1 패턴으로 실행되는 쿼리
-- 1) SELECT * FROM users WHERE department = 'engineering';  -- 50명 반환
-- 2) SELECT * FROM orders WHERE user_id = 1;  -- 50번 반복 실행
-- 3) SELECT * FROM orders WHERE user_id = 2;
-- ...

-- Good: JOIN으로 한 번에 가져오기
SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.department = 'engineering';

-- Good: LATERAL JOIN으로 사용자당 최근 주문 3건만 가져오기
SELECT u.id, u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.total_amount, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent_orders
WHERE u.department = 'engineering';

-- Bad: 상관 서브쿼리 - 외부 쿼리의 각 행마다 서브쿼리가 실행될 수 있다
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- Good: GROUP BY와 JOIN 활용
SELECT u.id, u.name, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

-- 대용량 데이터에서는 EXISTS가 유리한 경우가 많다
-- EXISTS는 첫 번째 매칭을 찾으면 즉시 중단한다
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- IN은 서브쿼리 결과가 작을 때 유리하다
SELECT u.id, u.name
FROM users u
WHERE u.department_id IN (
  SELECT id FROM departments WHERE region = 'APAC'
);

-- Bad: OFFSET이 커질수록 느려진다
SELECT * FROM orders
ORDER BY id
OFFSET 1000000 LIMIT 10;

-- Good: 키셋(Keyset) 페이지네이션 - 항상 일정한 성능
SELECT * FROM orders
WHERE id > 1000000  -- 이전 페이지의 마지막 id
ORDER BY id
LIMIT 10;

-- 복합 정렬 기준일 때의 키셋 페이지네이션
SELECT * FROM orders
WHERE (created_at, id) > ('2026-03-13 15:30:00', 98765)
ORDER BY created_at, id
LIMIT 10;

# postgresql.conf - Autovacuum 튜닝 권장 설정

# Autovacuum 활성화 (절대 끄지 마라)
autovacuum = on

# 동시에 실행 가능한 autovacuum worker 수
# CPU 코어 수와 테이블 수를 고려하여 설정
autovacuum_max_workers = 5

# VACUUM 트리거 임계값: dead tuple이 테이블의 10% + 50건 초과 시 실행
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

# 대용량 테이블에서는 scale_factor를 낮춰야 한다
# 1억 건 테이블에서 0.2(기본값)이면 2천만 건이 쌓여야 VACUUM이 실행된다

# ANALYZE 트리거: 변경이 테이블의 5% + 50건 초과 시 통계 갱신
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# 비용 기반 지연: 높을수록 VACUUM이 빠르지만 I/O 부하 증가
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

# XID Wraparound 방지를 위한 설정
autovacuum_freeze_max_age = 300000000

-- 대용량 주문 테이블: 더 빈번하게 VACUUM 실행
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,      -- 1%만 변경되어도 VACUUM
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0             -- 최대 속도로 VACUUM
);

-- VACUUM 상태 모니터링
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_ratio_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 월별 Range 파티셔닝 테이블 생성
CREATE TABLE events (
  id          BIGSERIAL,
  event_type  VARCHAR(50) NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 각 파티션에 인덱스 생성 (부모 테이블에 생성하면 자동 상속)
CREATE INDEX idx_events_type_created ON events (event_type, created_at);

-- 파티션 프루닝 확인: 2026년 3월 데이터만 조회
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND event_type = 'purchase';

-- pg_partman 설치 후 자동 관리 설정
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => '1 month',
  p_premake => 3  -- 3개월 분 미리 생성
);

-- 오래된 파티션 자동 삭제 (12개월 보관)
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

-- Bad: 대량 DELETE는 Dead Tuple을 생성하고 VACUUM 부하를 유발한다
DELETE FROM events WHERE created_at < '2025-01-01';

-- Good: 파티션을 통째로 분리하고 삭제 - 즉시 완료, VACUUM 불필요
ALTER TABLE events DETACH PARTITION events_2024_12;
DROP TABLE events_2024_12;

;; pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
;; 풀링 모드: transaction이 가장 효율적
pool_mode = transaction

;; 클라이언트 최대 연결 수
max_client_conn = 1000

;; 데이터베이스당 서버 연결 수 (실제 PostgreSQL 연결)
default_pool_size = 30
min_pool_size = 10
reserve_pool_size = 5

;; 유휴 연결 타임아웃
server_idle_timeout = 300

;; 클라이언트 연결 대기 큐 타임아웃
client_login_timeout = 60

;; 로깅
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

;; 모니터링용 포트
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# PgBouncer 상태 확인
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# 활성 연결 및 대기 상태 확인
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

# PostgreSQL 쪽 실제 연결 수 확인
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

# postgresql.conf - 슬로우 쿼리 로깅
log_min_duration_statement = 500       # 500ms 이상 쿼리 로깅
log_statement = 'none'                  # DDL만 로깅하려면 'ddl'
log_lock_waits = on                     # 잠금 대기 로깅
deadlock_timeout = 1s                   # 데드락 감지 주기

# pg_stat_statements 확장 활성화 (필수)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'

-- pg_stat_statements 확장 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 평균 실행 시간이 가장 긴 쿼리 TOP 10
SELECT
  queryid,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct,
  rows,
  query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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

-- 중복 인덱스 찾기
SELECT
  pg_size_pretty(SUM(pg_relation_size(idx))::bigint) AS total_size,
  (array_agg(idx))[1] AS idx1,
  (array_agg(idx))[2] AS idx2,
  (array_agg(indkey))[1] AS columns1,
  (array_agg(indkey))[2] AS columns2
FROM (
  SELECT indexrelid::regclass AS idx,
         indrelid, indkey,
         COALESCE(indexprs::text, '') AS exprs,
         COALESCE(indpred::text, '') AS preds
  FROM pg_index
) sub
GROUP BY indrelid, exprs, preds
HAVING COUNT(*) > 1;

-- 현재 잠금 대기 상황 확인
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query,
  blocked_activity.wait_event_type
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

# postgresql.conf - 메모리 관련 핵심 파라미터

# shared_buffers: 전체 RAM의 25% 권장 (최대 40%)
# 64GB RAM 서버 기준
shared_buffers = 16GB

# work_mem: 정렬, 해시 조인 등에 사용하는 세션당 메모리
# 주의: 쿼리당 여러 노드에서 각각 할당되므로 너무 크게 잡지 않는다
# (동시 접속 수 * 노드 수)를 고려하여 설정
work_mem = 64MB

# maintenance_work_mem: VACUUM, CREATE INDEX 등에 사용
maintenance_work_mem = 2GB

# effective_cache_size: OS 캐시를 포함한 총 캐시 크기 (플래너 힌트용)
# 전체 RAM의 50-75%
effective_cache_size = 48GB

# WAL 관련 설정
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

-- 1. 문제 쿼리를 실행 중인 세션 확인
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- 2. 문제 쿼리 강제 종료 (graceful)
SELECT pg_cancel_backend(12345);

-- 3. 종료되지 않으면 강제 킬
SELECT pg_terminate_backend(12345);

-- CONCURRENTLY: 테이블 잠금 없이 인덱스 생성 (프로덕션 필수)
CREATE INDEX CONCURRENTLY idx_reports_user_date
  ON reports (user_id, report_date);

-- 인덱스 생성이 실패하면 INVALID 상태로 남는다. 반드시 확인!
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'reports';

# pg_repack: 무중단으로 테이블 재구성 (짧은 잠금만 필요)
pg_repack -d myapp_production -t orders --no-superuser-check

# 특정 인덱스만 재구성
pg_repack -d myapp_production --index idx_orders_user_created

-- XID 소진 상태 확인
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  ROUND(100 * age(datfrozenxid)::numeric / 2147483647, 2) AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- 장시간 실행 중인 트랜잭션 확인 (autovacuum 차단 원인)
SELECT
  pid,
  now() - xact_start AS xact_duration,
  now() - query_start AS query_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start ASC;

-- 문제 트랜잭션 종료 후 수동 VACUUM FREEZE 실행
VACUUM FREEZE orders;

;; pgbouncer.ini - 커넥션 고갈 방지
query_wait_timeout = 30        ;; 서버 연결 대기 최대 30초
server_idle_timeout = 60       ;; 60초 유휴 시 서버 연결 반납
server_lifetime = 3600         ;; 최대 1시간 후 서버 연결 재생성
client_idle_timeout = 300      ;; 5분 유휴 클라이언트 연결 해제