PostgreSQL 쿼리 성능 튜닝: 더 이상 참을 수 없는 개발자를 위한 EXPLAIN ANALYZE 완전 가이드

API 엔드포인트가 3초 걸려요. 대시보드 로딩에서 타임아웃이 터져요. "1분이면 끝날" 배치 잡이 45분째 돌고 있어요. pgAdmin을 열어서 쿼리를 쳐다보면 이런 생각이 들죠. "이게 왜 느리지? 그냥 SELECT인데."

PostgreSQL 성능 디버깅은 모든 백엔드 개발자가 언젠가 부딪히는 벽이에요. 대부분은 감으로 인덱스를 추가하거나, 쿼리를 이리저리 고쳐보거나, ORM 탓을 하고 넘어가죠.

이 글에서는 체계적인 접근법을 알려드릴게요. EXPLAIN ANALYZE 출력을 자유자재로 읽는 법, 진짜 효과 있는 인덱스 전략(오히려 나빠지는 것도), N+1 쿼리를 찾아서 처치하는 법, 커넥션 풀링 실수, 그리고 프로덕션에서 실제로 쓰는 디버깅 워크플로우까지 전부 다뤄요.

쿼리가 느리다. 어디서부터?

뭔가를 건드리기 전에 데이터가 필요해요. 감이 아니라 데이터요.

첫 번째 단계는 항상 같아요. EXPLAIN ANALYZE를 돌리세요.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;

ANALYZE 플래그는 쿼리를 실제로 실행해요(파괴적 쿼리는 트랜잭션으로 감싸세요). BUFFERS는 I/O 활동을 보여주는데, 느린 원인 찾을 때 제일 중요한 정보거든요.

돌려보면 이런 결과가 나와요:

Limit  (cost=1247.83..1247.96 rows=50 width=86) (actual time=342.118..342.127 rows=50 loops=1)
  Buffers: shared hit=128 read=15234
  ->  Sort  (cost=1247.83..1302.45 rows=21847 width=86) (actual time=342.115..342.120 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        Buffers: shared hit=128 read=15234
        ->  Hash Join  (cost=412.50..876.32 rows=21847 width=86) (actual time=45.821..298.456 rows=21847 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=128 read=15234
              ->  Seq Scan on orders o  (cost=0.00..398.75 rows=21847 width=24) (actual time=0.021..187.345 rows=21847 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 478153
                    Buffers: shared hit=0 read=14891
              ->  Hash  (cost=212.50..212.50 rows=10000 width=62) (actual time=45.678..45.678 rows=10000 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 890kB
                    Buffers: shared hit=128 read=343
                    ->  Seq Scan on customers c  (cost=0.00..212.50 rows=10000 width=62) (actual time=0.012..22.345 rows=10000 loops=1)
                          Buffers: shared hit=128 read=343

복잡해 보이죠? 하나씩 까볼게요.

EXPLAIN ANALYZE 읽기: 진짜 중요한 5가지

1. Actual Time (Cost 말고)

cost는 무시하세요. 플래너의 추정치일 뿐이에요. 진짜 봐야 할 건 actual time:

actual time=0.021..187.345 rows=21847

첫 번째 숫자(0.021)가 시작 시간, 두 번째(187.345)가 총 시간(밀리초)이에요. 이 Seq Scan이 187ms로 전체 쿼리의 55%를 잡아먹고 있죠.

2. Rows Removed by Filter

인덱스가 빠진 걸 알려주는 결정적 단서죠:

Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
Rows Removed by Filter: 478153

약 50만 행을 뒤져서 2만 2천 행을 찾았어요. 95.6% 낭비. Rows Removed by Filter가 반환 행수의 10배 이상이면 인덱스가 거의 확실히 필요해요.

3. Buffers: shared hit vs read

Buffers: shared hit=0 read=14891

shared hit은 PostgreSQL 버퍼 캐시(RAM)에서 찾은 페이지, read는 디스크에서 읽어온 페이지예요. read가 hit보다 훨씬 크면 작업 데이터가 메모리에 안 올라와 있다는 신호거든요.

4. 큰 테이블의 Seq Scan

->  Seq Scan on orders o  (cost=0.00..398.75 rows=21847 ...)

50만 행 테이블에 Sequential Scan은 거의 항상 문제죠. PostgreSQL 성능 킬러 1위.

5. Estimated vs Actual Rows 차이

(cost=... rows=21847 ...) (actual time=... rows=21847 ...)

추정 행수와 실제 행수가 10배 이상 차이 나면 플래너가 잘못된 전략을 골랐다는 뜻이죠. 보통 통계가 오래된 거거든요:

ANALYZE orders;
-- 아니면 전체 테이블:
ANALYZE;

진짜 효과 있는 인덱스 전략

대부분의 개발자 인덱스 전략이 "느리면 WHERE 컬럼에 인덱스 박기"인데, 이건 터진 파이프에 밴드 붙이는 격이에요.

규칙 1: 복합 인덱스가 단일 컬럼 인덱스를 이겨요

-- 그저 그런: 단일 컬럼
CREATE INDEX idx_orders_status ON orders(status);
-- 'completed'가 너무 많아서 PostgreSQL이 여전히 seq scan할 수 있어요

-- 좋음: 복합 인덱스
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- 두 조건 모두 Index Scan으로 처리 가능

복합 인덱스의 컬럼 순서가 중요해요. 등호 조건(status = 'completed')을 먼저, 범위 조건(created_at >= '2026-01-01')을 나중에 넣으세요.

규칙 2: 커버링 인덱스로 테이블 룩업 제거

인덱스 스캔 후에도 PostgreSQL은 실제 컬럼 값을 가져오려고 테이블(힙)에 다시 접근해요. 이 "힙 페치"가 비싸요.

커버링 인덱스는 필요한 컬럼을 전부 포함해요:

CREATE INDEX idx_orders_covering ON orders(status, created_at DESC)
INCLUDE (id, total, customer_id);

이러면 PostgreSQL이 인덱스만으로 쿼리를 해결해요. 힙 접근 필요 없음.

결과를 확인해볼게요:

->  Index Only Scan using idx_orders_covering on orders o
      Index Cond: ((status = 'completed') AND (created_at >= '2026-01-01'))
      Heap Fetches: 0
      Buffers: shared hit=4

342ms → 0.23ms. 1,487배 빨라졌어요. Heap Fetches: 0이 핵심이죠.

규칙 3: 편향된 데이터에는 부분 인덱스

주문의 90%가 completed인데 자주 쿼리하는 건 최근 pending 건이라면:

CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

인덱스 크기가 10분의 1로 줄고, 캐시에 쏙 들어가요.

규칙 4: 인덱스를 걸지 말아야 할 때

인덱스는 공짜가 아니에요. 인덱스마다 INSERT, UPDATE, DELETE가 느려지고, 디스크랑 메모리를 먹고, VACUUM 부하도 늘어요.

사용되지 않는 인덱스 찾는 법:

SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

실제 프로덕션 DB에서 안 쓰는 인덱스 40개가 30GB 잡아먹고 쓰기 성능 20% 깎아먹는 거 본 적 있어요.

N+1 문제: 천 개의 작은 쿼리가 서버를 죽여요

N+1은 PostgreSQL 문제가 아니라 애플리케이션 레이어 문제죠. 근데 웹 앱에서 제일 흔한 성능 이슈이기도 하고요.

이렇게 생겼어요

// 전형적인 N+1: 주문 1번 + 고객 N번
const orders = await db.query('SELECT * FROM orders LIMIT 100');

for (const order of orders) {
  // 이게 100개의 개별 쿼리를 날려요
  const customer = await db.query(
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer;
}
// 총 101개 쿼리, 네트워크 왕복만 340ms

각 쿼리는 1-3ms밖에 안 걸리는데, 네트워크 왕복 오버헤드가 쌓여요. 100번 왕복 × 3ms = 300ms 순수 낭비.

해결 1: JOIN

SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- 쿼리 1개, ~2ms

해결 2: 배치 로딩

// 1단계: 주문 가져오기
const orders = await db.query('SELECT * FROM orders LIMIT 100');

// 2단계: 고객을 한 번에 로드
const customerIds = [...new Set(orders.map(o => o.customer_id))];
const customers = await db.query(
  'SELECT * FROM customers WHERE id = ANY($1)',
  [customerIds]
);

// 3단계: 메모리에서 매핑
const customerMap = new Map(customers.map(c => [c.id, c]));
orders.forEach(o => o.customer = customerMap.get(o.customer_id));
// 쿼리 2개, ~4ms

해결 3: DataLoader 패턴

깊은 중첩 관계(주문 → 상품 → 카테고리)가 있을 때 DataLoader가 자동으로 배칭하고 중복을 제거해요:

import DataLoader from 'dataloader';

const customerLoader = new DataLoader(async (ids: number[]) => {
  const customers = await db.query(
    'SELECT * FROM customers WHERE id = ANY($1)',
    [ids]
  );
  const map = new Map(customers.map(c => [c.id, c]));
  return ids.map(id => map.get(id) ?? null);
});

// 이 세 개가 하나의 쿼리로 배칭돼요:
const [cust1, cust2, cust3] = await Promise.all([
  customerLoader.load(1),
  customerLoader.load(2),
  customerLoader.load(3),
]);
// 쿼리 1개: SELECT * FROM customers WHERE id = ANY([1, 2, 3])

프로덕션에서 N+1 탐지하기

ORM 사용자라면 N+1 탐지 도구가 있어요:

// Prisma: 쿼리 이벤트로 N+1 탐지
const prisma = new PrismaClient({
  log: [{ emit: 'event', level: 'query' }],
});

let queryCount = 0;
prisma.$on('query', () => { queryCount++; });

// 요청 후:
if (queryCount > 10) {
  console.warn(`⚠️ N+1 감지: 한 요청에서 ${queryCount}개 쿼리 실행`);
}

커넥션 풀링: 아무도 안 알려주는 처리량 킬러

쿼리 최적화하고, 인덱스 달고, N+1 잡았어요. 개발 환경에서 응답 시간 완벽. 근데 배포하고 부하 받으면 갑자기 느려져요.

범인은 거의 항상 커넥션 풀링이죠.

왜 커넥션이 비싼가

PostgreSQL 커넥션 하나당 프로세스 하나(~10MB RAM)를 생성해요. 그리고 핵심은 이거예요: 커넥션 50개를 넘기면 락 경합 때문에 성능이 떨어져요.

┌─────────────────────────────────────────┐
│         커넥션 수 vs 성능               │
│                                         │
│  커넥션     처리량      레이텐시        │
│  ───────   ────────    ────────         │
│      10    100%        기준             │
│      50    95%         1.1배            │
│     100    70%         2.5배            │
│     200    40%         8배              │
│     500    15%         25배             │
│                                         │
│  ⚠️  커넥션 많다고 처리량이 늘지 않아요  │
└─────────────────────────────────────────┘

해결: PgBouncer

PgBouncer가 앱과 PostgreSQL 사이에서 커넥션을 중계해요. 수천 개의 앱 커넥션을 소수의 실제 DB 커넥션으로 멀티플렉싱해요.

; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction          ; 트랜잭션마다 커넥션 반납
default_pool_size = 20           ; DB당 실제 커넥션 20개
max_client_conn = 1000           ; 앱 커넥션 최대 1000개 수용
reserve_pool_size = 5            ; 트래픽 폭주 대비 여유분

핵심 설정은 pool_mode = transaction이죠. 트랜잭션 동안만 실제 DB 커넥션을 할당하고 끝나면 풀에 반납해요.

가장 흔한 실수

앱 레벨 풀링에서 max를 너무 높게 잡는 거예요:

import { Pool } from 'pg';

const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  max: 20,                    // 인스턴스당 최대 커넥션
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

앱 인스턴스 10개 × max: 50 = 500개 실제 커넥션. PostgreSQL이 뻗어요. max_connections / 인스턴스 수로 계산하세요.

고급 디버깅 도구

pg_stat_statements: 프로덕션 성능 대시보드

모든 쿼리 패턴의 실행 통계를 추적하는 확장이에요:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 총 시간 기준 top 10 느린 쿼리
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

건당 2.81ms밖에 안 걸려도 15,000번 호출되면 총 시간의 34.2%를 잡아먹죠. 이런 걸 찾는 게 최적화의 핵심이거든요.

pg_stat_user_tables: 테이블 건강 체크

SELECT
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

dead_pct가 20%를 넘으면 autovacuum이 못 따라가고 있는 거예요. 데드 행이 테이블을 불려서 seq scan이 더 많은 페이지를 읽게 돼요.

-- 쓰기 많은 테이블은 autovacuum을 공격적으로
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

락 모니터링: 블로킹 쿼리 찾기

SELECT
  blocked.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking ON blocking.locktype = blocked.locktype
  AND blocking.database IS NOT DISTINCT FROM blocked.database
  AND blocking.relation IS NOT DISTINCT FROM blocked.relation
  AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

쿼리가 수 초 이상 블로킹되고 있으면 두 가지 원인이 있어요. 오래 실행되는 트랜잭션이 락을 잡고 있거나(해결: 트랜잭션을 짧게), 스키마 마이그레이션이 CONCURRENTLY 없이 돌고 있거나(해결: CREATE INDEX CONCURRENTLY 사용).

전체 디버깅 워크플로우

쿼리가 느릴 때 이 순서를 따르세요:

┌────────────────────────────────────┐
│  1. EXPLAIN (ANALYZE, BUFFERS)     │
│     의심 쿼리 실행                 │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  2. Seq Scan 확인                  │
│     1만 행 넘는 테이블이면         │
│     → 적절한 인덱스 추가           │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  3. Rows Removed 확인              │
│     반환 행수의 10배 이상이면      │
│     → 복합 / 커버링 인덱스         │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  4. 예상 행수 vs 실제 행수          │
│     10배 이상 차이나면             │
│     → ANALYZE 실행                 │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  5. Buffers hit vs read            │
│     read가 반복적으로 크면         │
│     → shared_buffers 증가          │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  6. pg_stat_statements             │
│     총 시간 기준 top 쿼리 확인     │
│     → 우선순위대로 최적화          │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  7. 커넥션 수 확인                  │
│     활성 50개 넘으면               │
│     → PgBouncer 도입               │
└────────────────────────────────────┘

흔한 PostgreSQL 설정 실수

설정	기본값	추천값	이유
`shared_buffers`	128MB	RAM의 25%	PostgreSQL 메인 캐시. 너무 작으면 디스크 읽기 폭주
`effective_cache_size`	4GB	RAM의 75%	OS 캐시 크기 힌트. 인덱스 사용 결정에 영향
`work_mem`	4MB	64-256MB	정렬/해시 조인용 메모리. 너무 작으면 디스크 정렬
`random_page_cost`	4.0	1.1 (SSD)	SSD면 낮추세요. 플래너가 인덱스 스캔을 선호하게 됨
`maintenance_work_mem`	64MB	1-2GB	VACUUM, CREATE INDEX용. 높을수록 빨라요

가장 효과 큰 변경: SSD 쓰면 random_page_cost = 1.1로 바꾸세요. 기본값 4.0은 HDD 시절 설정인데, 이 때문에 PostgreSQL이 인덱스 스캔 대신 seq scan을 골라요.

실전 사례: 12초 → 15밀리초

분석 대시보드 쿼리가 12초 걸리는 상황이에요:

SELECT
  date_trunc('day', o.created_at) AS day,
  COUNT(*) AS order_count,
  SUM(o.total) AS revenue,
  COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-12-31'
  AND o.store_id = 42
GROUP BY date_trunc('day', o.created_at)
ORDER BY day;

문제 진단:

Seq Scan on orders  (actual time=0.034..11234.567 rows=187234 loops=1)
  Filter: ((created_at BETWEEN ...) AND (store_id = 42))
  Rows Removed by Filter: 4812766
Sort Method: external merge  Disk: 48672kB

세 가지 문제: Seq Scan(500만 행), 디스크 정렬(work_mem 부족), 디스크 I/O 폭주.

해결:

-- 1. 타겟 복합 인덱스
CREATE INDEX idx_orders_store_date ON orders(store_id, created_at)
INCLUDE (total, customer_id);

-- 2. 세션 work_mem 올리기
SET work_mem = '128MB';

결과:

Index Only Scan using idx_orders_store_date  (actual time=0.045..8.234 rows=187234)
  Heap Fetches: 0
  Buffers: shared hit=892

12,847ms → 15ms. 856배 빨라졌어요. 복합 인덱스 + 커버링 인덱스 + 적절한 work_mem, 이 세 가지가 전부예요.

정리

PostgreSQL 성능 디버깅은 마법이 아니죠. 체계적인 프로세스예요:

먼저 측정하세요. 느린 쿼리에 EXPLAIN (ANALYZE, BUFFERS) 돌리세요. 감으로 하지 마세요.
신호를 읽으세요. Seq Scan, Rows Removed by Filter, Buffers hit vs read가 정확히 뭐가 문제인지 알려줘요.
전략적으로 인덱스를 거세요. 등호 먼저 순서의 복합 인덱스. 자주 쓰는 쿼리에 커버링 인덱스. 편향 데이터에 부분 인덱스.
N+1 쿼리를 잡으세요. JOIN, 배치 로딩, DataLoader 패턴을 쓰세요.
커넥션을 관리하세요. PgBouncer나 앱 레벨 풀링. 실제 커넥션은 50개 이하.
설정을 제대로 하세요. SSD면 random_page_cost = 1.1, shared_buffers는 RAM의 25%.
계속 모니터링하세요. pg_stat_statements로 쿼리, pg_stat_user_tables로 테이블 건강.

EXPLAIN ANALYZE부터 시작하세요. 워크플로우를 따르세요. 전부 측정하세요. 쿼리가 달라질 거예요.