DB Index & Query Optimization

분류: Layer 8 - 데이터베이스 심화 | 작성일: 2026-04-02

1. 한 줄 정의

인덱스는 “DB가 데이터를 찾을 때 전체를 뒤지지 않고, 미리 정렬된 별도 자료구조를 통해 빠르게 위치를 찾아가는 길잡이”이고, 쿼리 최적화는 이 인덱스를 제대로 활용하도록 SQL과 ORM 코드를 조정하는 작업이다.

2. 왜 중요한가

수십만 건 이상의 테이블에서 인덱스 없이 조회하면 PostgreSQL은 모든 행을 순서대로 읽는다(Seq Scan). 이 경우 데이터가 늘수록 응답 시간이 선형으로 증가한다.
TypeORM find() 를 관계 없이 반복 호출하면 N+1 쿼리가 발생해 DB에 수백 개의 쿼리를 날린다. N=100이면 101번, N=1,000이면 1,001번의 쿼리가 나가고, Scout APM은 이 패턴을 Rails·Django·Elixir 같은 ORM 공통 문제로 설명한다.
“왜 갑자기 API가 느려졌나요?” 를 스스로 진단하고 해결하려면 EXPLAIN ANALYZE를 읽을 줄 알아야 한다.
BackOps 환경에서 대량 배치 처리·정산·통계 조회는 인덱스 설계가 곧 서비스 안정성과 직결된다.

출처: Scout APM — Understanding N+1 Database Queries

2-1. 순차 스캔의 한계 — 왜 B+Tree 인덱스가 등장했나

아무 준비가 없으면 DB는 WHERE id = 123 같은 조건도 테이블을 행 단위로 끝까지 훑어야 한다. PostgreSQL 공식 문서는 이 경우 반환 행이 0~1개뿐이어도 많은 행이 있는 테이블에서는 비효율적이며, 인덱스를 유지하면 검색 트리의 몇 단계만 내려가서 후보 행을 찾을 수 있다고 설명한다. 이 문서의 frontmatter가 말하는 “전체 테이블 순차 스캔 병목 → 정렬된 B+Tree 인덱스로 O(log N) 탐색 해결”은 바로 이 한계를 가리킨다.

단, B+Tree는 공짜가 아니다. PostgreSQL은 인덱스 생성 뒤 테이블 변경과 인덱스를 동기화해야 하므로 DML 오버헤드가 생기고, 큰 테이블에서 일반 CREATE INDEX는 쓰기 작업을 막는다. 따라서 인덱스의 등장 메커니즘은 “읽기 탐색 공간을 정렬된 트리로 줄인다”이고, 실무 결정 메커니즘은 “읽기 절감이 쓰기·저장공간·운영 락 비용을 초과할 때만 유지한다”이다. 이 원리는 MySQL InnoDB의 Clustered Index나 Lucene 역색인에도 전이된다. 모두 후보 집합을 미리 정렬·분할해 전체 탐색을 피하지만, PostgreSQL B+Tree는 MVCC 힙 포인터와 VACUUM 비용까지 함께 봐야 한다는 점이 다르다.

출처: PostgreSQL 공식 문서 — Indexes Introduction, PostgreSQL 공식 문서 — CREATE INDEX

3. 핵심 개념

3-1. B-Tree 인덱스 동작 원리

비유: 도서관 십진분류 카탈로그

책이 100만 권인 도서관에서 “컴퓨터 공학” 책을 찾는다고 생각해보자. 모든 책을 하나씩 펼쳐보는 게 Seq Scan, 십진분류 카탈로그(대분류 → 중분류 → 소분류)로 찾아가는 게 B-Tree Index Scan이다.

왜 이렇게 설계되었는가 — B-Tree 선택의 이유

PostgreSQL이 기본 인덱스로 B+Tree를 선택한 이유는 세 가지다. 첫째, O(log N) 탐색으로 데이터 1억 건도 3~4단계면 찾는다. 둘째, 리프 노드가 연결 리스트로 연결돼 범위 검색이 효율적이다(BETWEEN, >, <). 셋째, 균형 트리 구조로 어떤 값을 찾아도 일정한 성능이 보장된다. 실무에서 슬로우 쿼리 대부분은 B-Tree 인덱스 누락이나 인덱스를 타지 못하는 쿼리 패턴에서 발생한다.

B-Tree 최적화 기능 (PostgreSQL 공식 문서 기준)

PostgreSQL의 B-Tree는 multi-level tree이며 각 레벨은 페이지의 doubly-linked list로 동작한다. 공식 문서 기준으로 leaf page가 보통 전체 페이지의 99% 이상이고, leaf page가 꽉 차면 page split이 상위 레벨로 전파될 수 있다. 그래서 PostgreSQL은 쓰기 부하에서 split과 bloat를 줄이기 위해 HOT Updates, bottom-up deletion, deduplication 같은 최적화를 함께 사용한다.

핵심 최적화 3가지:

1. HOT Updates (Heap Only Tuple)
   - 인덱스 컬럼이 바뀌지 않는 UPDATE는 인덱스를 재작성하지 않음
   - 예: name은 인덱스인데 email만 바꾸는 경우 → 인덱스 갱신 비용 없음
   - 효과: 인덱스 비대화(bloat) 속도 크게 감소

2. Index Deduplication (PostgreSQL 13+)
   - 중복값이 많은 인덱스에서 같은 값을 하나의 포스팅 리스트로 압축
   - 예: status='PENDING' 값이 반복되면 key는 한 번 저장하고 TID 배열로 묶어 leaf page 공간 절약
   - 기본값: B-Tree `deduplicate_items = ON`

3. Bottom-up Deletion
   - 인덱스 페이지가 꽉 찰 때 페이지 분할 전에 dead tuple 정리 먼저 시도
   - UPDATE가 잦고 HOT 최적화가 적용되지 않는 테이블에서 version churn tuple을 줄임

출처: PostgreSQL 공식 문서 — B-Tree Indexes, PostgreSQL 공식 문서 — CREATE INDEX storage parameters

구조

루트(Root) 페이지
  └─ 브랜치(Branch/Internal) 페이지 [100 ~ 500]
       ├─ 리프(Leaf) 페이지 [100 ~ 200]
       │    ├─ (101, → heap row ptr)
       │    ├─ (150, → heap row ptr)
       │    └─ (199, → heap row ptr)
       └─ 리프(Leaf) 페이지 [201 ~ 300]
            ├─ (201, → heap row ptr)
            └─ ...

리프 페이지들은 연결 리스트(Doubly Linked List)로 연결됨
→ 범위 검색 시 순서대로 탐색 가능

핵심 특성

특성	설명
균형 트리	모든 리프가 동일한 깊이 → 어느 값을 찾아도 탐색 시간이 일정
O(log N)	데이터 1억 건도 3~4단계면 도달 (루트 1페이지 → 브랜치 → 리프)
정렬 유지	값이 항상 오름차순으로 정렬된 상태 유지
리프 연결 리스트	범위 검색(BETWEEN, >, <) 시 연결된 리프를 순서대로 탐색

규모 스케일링 예시

2레벨 트리: 약 18만 건 (600 브랜치 포인터 × 300 항목/페이지)
3레벨 트리: 약 1억 건 이상
→ 전형적인 서비스 DB는 3레벨이면 충분

B-Tree vs B+Tree

PostgreSQL은 엄밀히 B+Tree를 사용한다.

구분	B-Tree	B+Tree (PostgreSQL)
데이터 위치	모든 노드에	리프 노드에만
범위 검색	루트로 다시 올라가야 함	리프 연결 리스트로 바로 탐색
메모리 효율	낮음	높음 (내부 노드가 가벼움)

→ 범위 검색이 많은 실무에서 B+Tree가 유리한 이유: WHERE created_at BETWEEN '2026-01-01' AND '2026-04-01' 같은 쿼리는 시작 리프를 찾은 뒤 연결 리스트를 따라가면 되기 때문이다.

📖 더 보기: Understanding the Mechanics of PostgreSQL B-Tree Indexes — B-Tree 페이지 구조를 pageinspect 확장으로 직접 확인하는 방법 포함

B+Tree 원리의 다른 시스템 전이

B+Tree와 “정렬된 자료구조로 탐색 범위를 줄인다”는 원리는 PostgreSQL에만 국한되지 않는다.

시스템	내부 구조	PostgreSQL B+Tree와의 차이
MySQL InnoDB	B+Tree — 단, PK 자체가 Clustered Index (데이터 행이 리프 노드에 직접 저장). 보조 인덱스 리프에는 실제 행 위치가 아닌 PK 값이 저장되어, 보조 인덱스 조회 시 PK → Clustered Index 추가 탐색 발생	PostgreSQL Non-Clustered는 힙 주소를 저장; MySQL은 PK를 저장
Elasticsearch/Lucene	역색인(Inverted Index) — 텍스트 토큰 → 문서 ID 목록. B+Tree처럼 “조건에 해당하는 대상만 찾는다”는 목적은 동일하지만, 구조가 불변(Immutable) 세그먼트로 관리되어 업데이트 시 새 세그먼트 생성 후 병합	B+Tree는 제자리 갱신; Lucene 역색인은 병합 기반 갱신
파일시스템(ext4/APFS)	디렉터리 엔트리에 B-Tree 변형 사용. inode 번호 탐색이 DB PK 탐색과 구조적으로 동일	파일 데이터가 “힙”, 디렉터리 트리가 “인덱스”에 대응

핵심 전이 통찰: “탐색 공간을 미리 정렬해 두면 O(N) → O(log N)으로 줄 수 있다”는 원리는 동일하다. 시스템마다 갱신 전략(제자리 갱신 vs. 불변 세그먼트)과 데이터 위치(힙 포인터 vs. PK vs. 문서 ID)가 다를 뿐이다.

📖 출처: MySQL 공식 문서 — InnoDB Clustered and Secondary Indexes, Elastic Blog — Elasticsearch from the Bottom Up

Hash Index

-- Hash Index 생성 (PostgreSQL 10+ 에서 WAL 지원)
CREATE INDEX idx_user_email_hash ON users USING HASH (email);

구분	B-Tree	Hash Index
동등 검색 (`=`)	O(log N)	O(1)
범위 검색 (`>`, `<`, `BETWEEN`)	가능	불가능
정렬 (`ORDER BY`)	가능	불가능
사용 권장	대부분의 경우	오직 동등 검색만 할 때

실무에서는 B-Tree를 기본으로 사용하고, Hash는 거의 쓰지 않는다.

3-2. 인덱스 종류

Clustered Index (클러스터드 인덱스)

테이블 데이터 자체가 인덱스 순서로 물리적으로 정렬된다. PostgreSQL에서는 PK가 이에 해당하며, 정확히는 CLUSTER 명령으로 수동 생성하거나, InnoDB(MySQL)처럼 자동으로 적용되지는 않는다.

-- PostgreSQL: 인덱스 순서로 테이블을 물리적으로 재정렬 (1회성)
CLUSTER users USING idx_users_pkey;

특징: 범위 검색, 정렬 쿼리에서 매우 빠름 (데이터가 이미 순서대로 있으므로)
단점: 테이블당 1개만 가능, 재정렬 비용 발생

Non-Clustered Index (논클러스터드 인덱스)

별도의 인덱스 구조에 키 값 + 실제 행을 가리키는 포인터(힙 주소)를 저장한다. PostgreSQL의 일반 CREATE INDEX가 여기 해당된다.

인덱스 리프: (email 값, → 실제 row 위치)
                              ↓
                        heap 페이지에서 실제 행 읽기 (Random I/O)

특징: 여러 개 생성 가능
단점: 인덱스 조회 후 힙 페이지를 한 번 더 읽어야 함 (= Table Lookup)

Composite Index (복합 인덱스) + Leftmost Prefix Rule

-- (status, created_at) 복합 인덱스
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Leftmost Prefix Rule: 복합 인덱스는 왼쪽 컬럼부터 순서대로 사용해야 효과가 있다.

-- ✅ 인덱스 사용 (status가 leftmost)
SELECT * FROM orders WHERE status = 'PENDING';
SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2026-01-01';

-- ❌ 인덱스 미사용 (첫 번째 컬럼 status 없음)
SELECT * FROM orders WHERE created_at > '2026-01-01';

컬럼 순서 설계 기준: 카디널리티(선택도)가 높은 컬럼을 앞에 배치한다. (user_id 같은 고유값 > status 같은 분류값)

Covering Index (커버링 인덱스)

쿼리에 필요한 모든 컬럼이 인덱스 안에 포함되어, 힙 페이지를 읽지 않아도 되는 인덱스.

-- user_id로 검색해서 email, name만 SELECT하는 경우
CREATE INDEX idx_users_covering ON users(user_id) INCLUDE (email, name);
-- PostgreSQL 11+ INCLUDE 문법 사용

-- EXPLAIN 결과에 "Index Only Scan" 으로 표시됨 (힙 접근 없음)

PostgreSQL에서 TypeORM @Index로는 INCLUDE를 직접 지원하지 않아 마이그레이션에서 직접 생성해야 한다.

3-3. EXPLAIN ANALYZE 읽는 법

기본 사용법

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'PENDING'
ORDER BY created_at DESC;

출력 예시 (Seq Scan - 인덱스 없을 때)

Seq Scan on orders  (cost=0.00..4821.00 rows=12 width=128)
                     (actual time=0.042..38.721 rows=12 loops=1)
  Filter: ((user_id = 123) AND (status = 'PENDING'::text))
  Rows Removed by Filter: 99988
Planning Time: 0.215 ms
Execution Time: 38.749 ms

출력 예시 (Index Scan - 인덱스 있을 때)

Index Scan using idx_orders_user_status on orders
                     (cost=0.43..16.82 rows=12 width=128)
                     (actual time=0.025..0.187 rows=12 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'PENDING'::text))
Planning Time: 0.318 ms
Execution Time: 0.221 ms

핵심 항목 해석

항목	의미	주목 포인트
`cost=0.43..16.82`	예상 비용 (시작..전체). 단위는 임의 숫자 (8KB 페이지 읽기 = 1)	큰 숫자 = 무거운 작업
`rows=12`	플래너가 예측한 결과 행 수	실제 rows와 크게 다르면 통계 갱신 필요
`actual time=0.025..0.187`	실제 실행 시간(ms). 시작..완료	loops > 1 이면 합산해서 봐야 함
`loops=1`	이 노드가 실행된 횟수	Nested Loop에서 N이 크면 주의
`Rows Removed by Filter`	인덱스 없이 필터로 걸러낸 행 수	이 수가 크면 인덱스 후보

스캔 타입 비교

스캔 타입	동작	언제
Seq Scan	전체 테이블 순차 읽기	인덱스 없거나, 결과가 전체의 ~10% 이상
Index Scan	인덱스 → 힙 페이지 Random I/O	소수 행 정확히 찾을 때
Index Only Scan	인덱스만 읽기 (힙 불필요)	Covering Index 사용 시
Bitmap Index Scan	인덱스로 비트맵 생성 후 힙 읽기	중간 정도의 행 수

📖 더 보기: PostgreSQL 공식 문서 - Using EXPLAIN — 위 스캔 타입별 동작과 EXPLAIN 출력의 모든 항목 해석법 공식 레퍼런스

BUFFERS 옵션으로 I/O 병목 진단 — 실무 필수

EXPLAIN (ANALYZE, BUFFERS)를 사용하면 각 노드가 얼마나 많은 디스크/메모리 I/O를 수행했는지 확인할 수 있다. 단순한 실행 시간만이 아닌 I/O 근본 원인을 파악할 수 있는 실무 핵심 옵션이다.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'PENDING';

예상 출력 (캐시 히트가 높은 경우)

Index Scan using idx_orders_user_status on orders
  (cost=0.43..16.82 rows=12 width=128)
  (actual time=0.025..0.187 rows=12 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'PENDING'::text))
  Buffers: shared hit=8 read=0
Planning Time: 0.318 ms
Execution Time: 0.221 ms

예상 출력 (디스크 읽기가 많은 경우 — 슬로우 쿼리 원인)

Seq Scan on orders  (cost=0.00..4821.00 rows=12 width=128)
                     (actual time=0.042..185.231 rows=12 loops=1)
  Filter: ((user_id = 123) AND (status = 'PENDING'::text))
  Rows Removed by Filter: 99988
  Buffers: shared hit=12 read=621   ← read=621이 문제! 디스크에서 읽음
Planning Time: 0.215 ms
Execution Time: 185.249 ms

Buffers 항목 해석:

항목	의미	주목 포인트
`shared hit=N`	공유 버퍼(메모리)에서 읽은 페이지 수	높을수록 좋음 (캐시 히트)
`shared read=N`	디스크에서 읽은 페이지 수	높으면 cold cache 또는 인덱스 누락
`shared dirtied=N`	버퍼에서 변경(더티)된 페이지	UPDATE/DELETE 작업량 확인
`shared written=N`	디스크에 쓰여진 더티 페이지	큰 수 = checkpoint 비용

실무 체크포인트: read 값이 hit 값보다 크면 I/O 병목으로 인덱스 추가 또는 shared_buffers 설정 검토 필요.

TypeORM에서 EXPLAIN ANALYZE 확인하기

// TypeORM QueryBuilder에서 직접 실행
const result = await dataSource.query(`
  EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT o.id, o.status, u.email
  FROM orders o
  INNER JOIN users u ON o.user_id = u.id
  WHERE o.status = 'PENDING'
  ORDER BY o.created_at DESC
  LIMIT 20
`);
console.log(result[0]["QUERY PLAN"]);

// TypeORM 쿼리 로그 활성화 (ormconfig / DataSource 옵션)
const dataSource = new DataSource({
  type: "postgres",
  logging: ["query", "error", "slow"], // 'query'로 모든 쿼리 로깅
  maxQueryExecutionTime: 1000, // 1초 이상 걸리면 slow 로그
});

3-3.5. Join 전략 + Planner Cost 모델 (시니어 EXPLAIN ANALYZE)

§3-3에서 단일 테이블 Seq Scan vs Index Scan을 봤다면, 시니어 수준 의 EXPLAIN ANALYZE는 Join 노드가 왜 그 알고리즘으로 선택됐는지 를 읽는 능력이다. PostgreSQL은 cost-based optimizer라 3가지 join 알고리즘 중 예상 cost가 가장 낮은 것을 고르며, 통계·work_mem·random_page_cost 같은 knob 이 잘못되어 있으면 시스템이 잘못된 plan을 선택 한다.

3가지 Join 알고리즘

알고리즘	동작 요약	비용 구조	강한 시나리오	깨지는 시나리오
Nested Loop	outer 한 row마다 inner index lookup	O(outer × inner_lookup)	outer 작고(<수백 행) inner index 잘 탐	outer >1000행 + inner도 큰 경우 → 폭발
Hash Join	smaller side로 hash table build → larger side로 probe	O(build + probe). hash table이 work_mem fit 가정	inputs 정렬 안 됨, equality join, work_mem 충분	hash table > work_mem → 디스크 spill (`Batches: N>1`)
Merge Join	양쪽이 정렬된 상태에서 zipper merge	O(left + right). 사전 정렬 필요	양쪽 index sort order로 정렬돼 있을 때	정렬 비용이 추가되면 hash가 더 빠름

Planner의 결정 로직

planner는 각 알고리즘의 예상 cost 를 계산해 가장 낮은 것을 선택한다:

nested_loop_cost = outer_rows × inner_lookup_cost
hash_join_cost   = build_phase + probe_phase + (work_mem 초과 시 disk spill 페널티)
merge_join_cost  = sort_cost(if needed) + merge_cost

cost 산정의 핵심 knob (postgresql.conf):

seq_page_cost (기본 1.0) — 순차 페이지 read 비용
random_page_cost (기본 4.0) — 랜덤 페이지 read 비용. SSD 환경에선 1.1~1.5로 조정 필수 (안 하면 index scan이 과소평가됨)
cpu_tuple_cost (기본 0.01) — row 처리 CPU 비용
cpu_index_tuple_cost (기본 0.005) — index entry 처리 비용
work_mem (기본 4MB) — sort/hash 작업 메모리. 세션·노드당

knob이 환경과 맞지 않으면 planner가 구조적으로 잘못된 선택을 한다.

EXPLAIN ANALYZE에서 join 알고리즘 진단

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.email, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
  AND o.created_at >= '2026-05-01';

출력 예 (간소화):

Nested Loop  (cost=0.71..456.32 rows=120 width=44)
              (actual time=0.045..2.318 rows=87 loops=1)
  ->  Index Scan using users_country_idx on users u
        (cost=0.43..32.12 rows=24 width=12)
        (actual time=0.024..0.341 rows=18 loops=1)
        Index Cond: (country = 'KR')
        Buffers: shared hit=4
  ->  Index Scan using orders_user_created_idx on orders o
        (cost=0.42..17.65 rows=5 width=36)
        (actual time=0.012..0.107 rows=4 loops=18)
        Index Cond: ((user_id = u.id) AND (created_at >= '2026-05-01'))
        Buffers: shared hit=72 read=3

Planning Time: 0.521 ms
Execution Time: 2.418 ms

시니어가 읽는 포인트 4가지:

cost=0.71..456.32 — startup..total cost. planner의 예상 비용 단위 (단위는 임의, 비교용).
rows=120 (예상) vs rows=87 (실제) — 2배 이상 차이나면 통계 부정확 신호 → ANALYZE 재실행 필요.
loops=18 — outer 18행마다 inner lookup. Nested Loop의 outer count 가 작아 OK. 만약 loops=10000+이면 outer가 너무 커서 Hash/Merge로 가야 했음.
Buffers: shared hit=72 read=3 — 캐시 hit 대 디스크 read. read 비율이 높으면 cold cache 또는 shared_buffers 부족.

결정 트리거 — Nested Loop이 잘못 선택됐다고 의심해야 할 때

다음 신호가 1개라도 보이면 planner의 join 알고리즘 선택을 검증해야 한다:

신호	가능한 원인	검증·조치
outer side `rows > 1000` + inner `rows > 100`	통계 부정확 또는 join 선택성 오판	`ANALYZE <table>` 후 재실행. 그래도 같으면 세션에서 `SET enable_nestloop = off`
`Plan rows vs Actual rows` 차이 > 10배	`pg_statistic` stale	`ANALYZE` 즉시. autovacuum 미작동 또는 임계 미달 의심
Hash Join에 `Batches: N (N > 1)`	hash table이 work_mem 초과 → disk spill	`SET work_mem = '256MB'` 세션 단위 증가 후 재시도. 권장은 batch 쿼리 단위 SET
Merge Join 직전에 `Sort` + `Sort Method: external merge Disk`	sort가 work_mem 초과 → 디스크 sort	work_mem 증가 또는 인덱스 추가로 정렬 회피
`random_page_cost` 4.0 + SSD 환경	planner가 index scan 과소평가	`ALTER SYSTEM SET random_page_cost = 1.1`, `SELECT pg_reload_conf()`
`Planning Time` > `Execution Time`	plan 계산 자체가 병목	prepared statement / plan cache 활용. ORM의 plan cache 동작 확인

Planner 통계 — `ANALYZE`의 역할

planner는 pg_statistic 통계를 기반으로 cost를 추정한다. 통계가 stale하면 모든 결정이 어긋난다.

-- 통계 상태 확인
SELECT
  schemaname, relname,
  last_analyze, last_autoanalyze,
  n_mod_since_analyze AS mods_since_analyze,
  n_live_tup AS live_rows
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 특정 칼럼의 분포 확인
SELECT attname, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'user_id';

-- 강제 ANALYZE (vacuum 없이 통계만)
ANALYZE VERBOSE orders;

-- vacuum + analyze (bloat 정리 포함)
VACUUM (ANALYZE, VERBOSE) orders;

autovacuum_analyze_scale_factor 기본값 0.1 — 테이블 10% 변경 시 자동 ANALYZE. 대용량 테이블(>1억 행)에서는 너무 늦으므로 테이블별로 0.02 등으로 낮춰 잡는다:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

`work_mem` 튜닝 — sort·hash 메모리 한계

work_mem 기본값 4MB. Hash Join build, Sort, CTE materialize 등 세션당, 노드당 사용. 너무 크면 동시 세션이 OOM, 너무 작으면 디스크 spill로 느림.

계산 가이드:
- 동시 active connection N + 평균 노드 사용 노드 M
- 안전 max_work_mem ≈ (RAM × 25%) / (N × M)
- 예: 32GB RAM, 100 connections, 평균 2 node/query → 8GB / 200 = 40MB

세션 단위 일시 증가 (큰 batch 쿼리만 — 이게 안전한 패턴):
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ... 큰 join ...;
COMMIT;
-- SET LOCAL은 트랜잭션 종료 시 자동 RESET

전역 work_mem을 무작정 늘리면 peak 동시 세션 수 만큼 RAM이 곱해진다. peak 200 connection × work_mem 256MB × node 2 = 100GB OOM 위험.

종합 체크리스트 — EXPLAIN ANALYZE 시니어 읽기 절차

이 체크리스트는 슬로우 쿼리 1개를 받았을 때 30초 안에 적용하는 순서 다:

1단계: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 로 실행 (BUFFERS·VERBOSE 필수)
2단계: Plan rows vs Actual rows 차이 10배 이상인 노드 찾기 → 즉시 ANALYZE <table>
3단계: Nested Loop loops > 10,000 또는 outer rows > 1000이면 Hash/Merge 검토
4단계: Hash Join Batches > 1 또는 Sort Method: external merge Disk면 work_mem 증가
5단계: Buffers: shared read >> hit면 OS page cache 또는 shared_buffers 부족 점검
6단계: SSD인데 random_page_cost = 4.0 기본값이면 1.1~1.5로 조정
7단계: Planning Time > Execution Time이면 prepared statement·plan cache 활용
8단계: 그래도 안 되면 enable_nestloop=off / enable_hashjoin=off 로 다른 plan 강제 후 비교 (production은 세션 단위로만)

실전 예 — “동일 쿼리가 어제는 100ms, 오늘은 30s”

가장 흔한 패턴. EXPLAIN ANALYZE 한 번 돌려보면:

어제: Nested Loop (rows=1200 actual=1180)
오늘: Nested Loop (rows=1200 actual=180,000)  ← 통계 stale
                                ↑ 150배 차이!
Planning Time: 0.3 ms
Execution Time: 31,420 ms

진단: 어제 데이터로 추정된 rows=1200은 오늘 통계 갱신 전이라 그대로 사용됨 → planner가 작은 outer 라 Nested Loop 선택 → 실제는 18만 row × inner lookup으로 폭발.

복구 (1분 안):

ANALYZE orders;        -- 통계 갱신
-- 그래도 같은 plan이면 work_mem 증가 + plan cache invalidate
DISCARD PLANS;          -- 세션의 prepared statement 캐시 폐기

근본 대응: autovacuum_analyze_scale_factor 낮추기 + pg_stat_statements로 plan 변동 모니터링.

발생 원리

// ❌ N+1 문제 발생 코드
// 1번 쿼리: 모든 주문 조회
const orders = await orderRepository.find();

// N번 쿼리: 각 주문마다 user 조회 (orders.length만큼 반복!)
for (const order of orders) {
  const user = await userRepository.findOne({ where: { id: order.userId } });
  console.log(user.email);
}

실제 발생하는 쿼리 로그:

-- 1번 쿼리
SELECT * FROM orders;

-- 이후 orders 수만큼 반복 (N=100이면 100번!)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ... 총 N+1 = 101번

해결책 비교

방법 1: find({ relations }) - 간단한 경우

// ✅ relations 옵션으로 JOIN 수행
const orders = await orderRepository.find({
  relations: ["user"], // LEFT JOIN users
  where: { status: "PENDING" },
});

// 발생 쿼리 (단 1번):
// SELECT orders.*, users.* FROM orders
// LEFT JOIN users ON orders.user_id = users.id
// WHERE orders.status = 'PENDING'

방법 2: QueryBuilder leftJoinAndSelect - 복잡한 조건

// ✅ QueryBuilder로 세밀한 JOIN 제어
const orders = await orderRepository
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user") // user 컬럼 SELECT 포함
  .leftJoinAndSelect("order.items", "item") // items도 JOIN
  .where("order.status = :status", { status: "PENDING" })
  .andWhere("order.createdAt > :date", { date: startDate })
  .orderBy("order.createdAt", "DESC")
  .take(20)
  .getMany();

// 발생 쿼리 (단 1번):
// SELECT order.*, user.*, item.*
// FROM orders order
// LEFT JOIN users user ON order.user_id = user.id
// LEFT JOIN order_items item ON item.order_id = order.id
// WHERE order.status = 'PENDING'
//   AND order.created_at > $1
// ORDER BY order.created_at DESC
// LIMIT 20

방법 3: Eager Loading - 항상 같이 쓰이는 관계

// Entity 정의
@Entity()
export class Order {
  @ManyToOne(() => User, { eager: true }) // Order 조회 시 항상 User JOIN
  user: User;
}

주의: eager: true는 모든 find() 에 적용되어 불필요한 JOIN이 생길 수 있다. 신중히 사용.

비교 정리

방법	쿼리 수	적합한 경우
`find()` 반복	N+1	절대 사용 금지
`find({ relations })`	1	단순 관계, 빠른 개발
QueryBuilder JOIN	1	복잡한 조건, 세밀한 제어
Eager Loading	1	항상 함께 쓰이는 필수 관계
DataLoader	배치	GraphQL, 동적 관계

3-5. TypeORM `@Index()` 데코레이터

import { Entity, Column, Index, PrimaryGeneratedColumn } from "typeorm";

// 단일 컬럼 인덱스
@Entity()
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Index() // 단일 인덱스: idx_order_user_id
  @Column()
  userId: number;

  @Index()
  @Column()
  status: string;

  @Column()
  createdAt: Date;
}

// 복합 인덱스 (Entity 레벨에서 선언)
@Entity()
@Index(["userId", "status"]) // (userId, status) 복합 인덱스
@Index(["status", "createdAt"]) // (status, createdAt) 복합 인덱스
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  status: string;

  @Column()
  createdAt: Date;
}

// 유니크 인덱스
@Index(["email"], { unique: true })
@Entity()
export class User {
  @Column()
  email: string;
}

Covering Index (INCLUDE) - 마이그레이션으로 직접 생성

// TypeORM 마이그레이션 파일
export class AddCoveringIndex1234567890 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE INDEX idx_orders_covering
      ON orders(user_id, status)
      INCLUDE (created_at, total_amount)
    `);
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX idx_orders_covering`);
  }
}

4. 실무에서 어떻게 쓰이나

인덱스 설계 기준 (체크리스트)

WHERE 절에 자주 등장하는 컬럼 → 인덱스 후보 1순위
JOIN ON 조건 컬럼 → 반드시 인덱스 (FK 컬럼은 PostgreSQL이 자동 생성하지 않음!)
ORDER BY, GROUP BY 컬럼 → 인덱스로 정렬 비용 제거 가능
카디널리티 확인 → 값의 종류가 매우 적은 컬럼(예: is_deleted만 있는 컬럼, true/false)은 인덱스 효과 낮음
복합 인덱스 순서 → 자주 쓰는 단독 조건 컬럼 먼저 (Leftmost Prefix Rule)

인덱스 추가 정량 판단 기준

인덱스는 읽기 성능을 높이는 대신 쓰기(INSERT/UPDATE/DELETE) 시 인덱스 갱신 비용이 추가된다. 아래 기준을 참고해 추가 여부를 판단한다.

판단 기준	인덱스 추가 권장	인덱스 효과 낮음
카디널리티(선택도)	결과가 전체 행의 ~10% 미만을 반환할 때	결과가 10% 이상 — Planner가 Seq Scan을 선택
컬럼 고유값 수	`user_id`처럼 고유값 수가 많을수록 유리	`status`(3~5가지), `is_deleted`(2가지)는 단독 인덱스 효과 낮음
쓰기:읽기 비율	읽기가 압도적으로 많을 때 (예: 조회 API)	초당 수만 건 INSERT 테이블 — 인덱스당 쓰기 오버헤드 누적
인덱스 수	테이블당 인덱스 5개 이하를 권장 기준으로 검토	인덱스가 많을수록 `VACUUM`, `ANALYZE` 비용도 증가

📖 출처: PostgreSQL 공식 문서 — Indexes Chapter, pganalyze — How Postgres Chooses Which Index To Use

실용 판단 순서

1. pg_stat_statements에서 mean_exec_time > 10ms인 쿼리 추출
2. EXPLAIN ANALYZE → Seq Scan이고 "Rows Removed by Filter"가 크면 인덱스 후보
3. 해당 컬럼의 고유값 수 확인: SELECT COUNT(DISTINCT col) / COUNT(*) FROM table;
   → 비율이 0.1(10%) 이상이면 인덱스 효과 기대 가능
4. 쓰기가 많은 테이블은 인덱스 추가 전 EXPLAIN 비용과 초당 DML 빈도를 함께 검토

이 판단은 PostgreSQL의 planner cost와도 연결된다. random_page_cost 기본값은 4.0, seq_page_cost 기본값은 1.0이며, PostgreSQL 문서는 random_page_cost를 낮추면 index scan 선호가 커지고 올리면 index scan 비용이 더 비싸게 평가된다고 설명한다. 즉 SSD/RAM 캐시가 충분한 운영 DB에서는 같은 인덱스라도 planner가 더 적극적으로 사용할 수 있지만, PostgreSQL 공식 문서가 경고하듯 몇 번의 실험만으로 cost 상수를 바꾸는 것은 위험하다. 먼저 쿼리별 EXPLAIN (ANALYZE, BUFFERS)와 ANALYZE로 통계 문제를 제거한 뒤 조정한다.

출처: PostgreSQL 공식 문서 — Query Planning

실제 쿼리 흐름 예시: 인덱스를 추가해야 하는 경우

BackOps 정산 API: "이번 달 COMPLETED 상태의 주문을 user_id별로 합산"

인덱스 없을 때: Seq Scan → 주문 100만 건 전체 스캔 → 5초
인덱스 추가 후: Index Scan on (status, created_at) → 0.05초

이 경우는 status 단독 인덱스보다 (status, created_at, user_id) 또는 (created_at, status) 후보를 실제 쿼리의 WHERE, GROUP BY, ORDER BY 순서로 비교한다. 예를 들어 최근 30일 데이터가 전체의 3%이고 status='COMPLETED'가 전체의 70%라면, status만 앞에 둔 인덱스는 선택도가 낮아 Seq Scan이 유지될 수 있다. 반대로 created_at >= now() - interval '30 days'가 먼저 후보를 좁히면 Bitmap/Index Scan으로 바뀔 가능성이 높다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, sum(total_amount)
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= now() - interval '30 days'
GROUP BY user_id;

기대 출력은 Seq Scan이 Index Scan 또는 Bitmap Index Scan으로 바뀌고 Rows Removed by Filter와 Buffers: shared read가 줄어드는 것이다. 출력이 그대로라면 인덱스가 틀린 것이 아니라 통계가 오래됐거나, 결과 행 비율이 너무 커서 planner가 Seq Scan을 더 싸게 본 것일 수 있다. 이때 첫 조치는 새 인덱스 추가가 아니라 ANALYZE orders; 후 같은 plan을 다시 보는 것이다. PostgreSQL의 autovacuum_analyze_scale_factor 기본값은 0.1이라 1억 행 테이블에서는 약 1,000만 행 변경 전까지 자동 ANALYZE가 늦을 수 있다.

SELECT relname, last_autoanalyze, n_mod_since_analyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

예상 출력에서 n_mod_since_analyze / n_live_tup이 0.1에 가까운데 last_autoanalyze가 오래됐다면, 통계 지연으로 잘못된 plan이 선택됐을 가능성을 먼저 의심한다.

출처: PostgreSQL 공식 문서 — Vacuuming / autovacuum_analyze_scale_factor

5. 내 업무와 어떻게 연결되나

배치 정산 작업: WHERE status = 'PENDING' AND processed_at IS NULL 형태의 쿼리가 느리다면 복합 인덱스 (status, processed_at) 추가
TypeORM Repository 메서드 검토: find() 루프 → find({ relations }) 또는 QueryBuilder로 리팩토링
AWS RDS 모니터링: CloudWatch의 DatabaseConnections, ReadLatency 지표가 높을 때 슬로우 쿼리를 pg_stat_statements로 추적
배포 후 인덱스 추가: CREATE INDEX CONCURRENTLY 사용 → 테이블 락 없이 운영 중 인덱스 생성 가능

-- 운영 중 안전하게 인덱스 추가
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders(status, created_at);

6. 비교 / 대안

접근 방법	장점	단점	언제 선택
B-Tree 인덱스	범용적, 범위/정렬 지원	쓰기 시 인덱스 갱신 비용	대부분의 경우
Hash 인덱스	동등 검색 O(1)	범위 검색 불가	오직 `=` 검색만 할 때
Partial 인덱스	조건부 행만 인덱싱 → 크기 작음	조건 외 쿼리에선 무효	`WHERE deleted_at IS NULL` 같은 패턴
GIN 인덱스	전문 검색, JSONB, 배열	공간 많이 차지	`@>`, `?` 연산자, 전문 검색
BRIN 인덱스	매우 작은 크기	물리적 정렬된 데이터에서만 효과	타임스탬프처럼 순서대로 쌓이는 대용량
`find({ relations })`	간단한 코드	조건 제어 어려움	단순 관계 로딩
QueryBuilder JOIN	세밀한 제어	코드 복잡	복잡한 조건

Partial Index 예시

-- 삭제 안 된 활성 주문만 인덱싱 → 인덱스 크기 대폭 감소
CREATE INDEX idx_orders_active
ON orders(user_id, created_at)
WHERE deleted_at IS NULL;

6.5. 트러블슈팅

케이스 1: 인덱스를 만들었는데 안 타는 경우

증상: EXPLAIN ANALYZE 결과에 여전히 Seq Scan

원인 및 해결

-- ❌ 함수로 컬럼 감싸기 → 인덱스 무효
WHERE LOWER(email) = '[email protected]'
WHERE DATE(created_at) = '2026-04-01'
WHERE EXTRACT(YEAR FROM created_at) = 2026

-- ✅ 해결: 컬럼을 그대로 쓰거나, Expression Index 사용
WHERE email = LOWER('[email protected]')    -- 값을 변환

-- 또는 Expression Index 생성
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
WHERE LOWER(email) = '[email protected]'   -- 이제 인덱스 탐

-- ❌ LIKE '%keyword' → 앞부분 와일드카드는 B-Tree 사용 불가
WHERE title LIKE '%결제%'

-- ✅ 해결 옵션 1: 앞부분 고정 LIKE는 인덱스 가능
WHERE title LIKE '결제%'   -- 가능

-- ✅ 해결 옵션 2: 전문 검색이 필요하면 GIN + pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_orders_title_gin ON orders USING GIN(title gin_trgm_ops);
WHERE title LIKE '%결제%'   -- GIN으로 처리

-- ❌ 암묵적 타입 변환 → 인덱스 무효
-- status 컬럼이 VARCHAR인데 숫자로 비교
WHERE status = 1    -- 내부적으로 CAST 발생

-- ✅ 타입 맞춰서 비교
WHERE status = '1'

PostgreSQL 통계가 오래된 경우

-- 통계 수동 갱신 (자동 AUTOVACUUM이 있지만 대량 Insert 후엔 수동 실행)
ANALYZE orders;
-- 또는
VACUUM ANALYZE orders;

케이스 2: 슬로우 쿼리 찾기 (pg_stat_statements)

설정 방법

# postgresql.conf 수정
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- 재시작 후 extension 활성화
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

슬로우 쿼리 TOP 10 조회

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

결과 해석

query          | SELECT * FROM orders WHERE status = $1
calls          | 50000          ← 5만 번 호출됨
total_ms       | 125000.00      ← 총 125초 소요
mean_ms        | 2.50           ← 평균 2.5ms
rows           | 3              ← 평균 3건 반환

→ 자주 호출(50,000번)되고 평균 2.5ms면 개선 가치 높음. status 컬럼 인덱스 검토.

통계 초기화

-- 측정 시작점 리셋
SELECT pg_stat_statements_reset();

케이스 3: 인덱스 비대화 (Index Bloat)

증상: 테이블 대비 인덱스가 지나치게 크고, 쿼리 성능이 점점 저하됨

원인: 잦은 UPDATE/DELETE 시 인덱스 페이지에 빈 공간(dead tuple)이 누적됨

진단

-- 인덱스 크기 확인
SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- dead tuple 확인
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

해결

-- REINDEX로 인덱스 재빌드 (락 발생)
REINDEX INDEX idx_orders_status;

-- 운영 중 안전하게 (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- 또는 VACUUM으로 dead tuple 정리
VACUUM orders;

케이스 4: N+1 쿼리 발견 및 수정

발견 방법

// TypeORM 로그에서 동일 패턴 쿼리가 반복되면 N+1 의심
// DataSource 설정
const dataSource = new DataSource({
  logging: ["query"],
  // 로그에서 SELECT ... FROM users WHERE id = $1 가 반복되면 N+1
});

수정 전 → 후 비교

// ❌ 수정 전: N+1 발생
const orders = await orderRepo.find();
for (const order of orders) {
  order.user = await userRepo.findOne({ where: { id: order.userId } });
}

// ✅ 수정 후: 단일 쿼리
const orders = await orderRepo
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user")
  .getMany();

케이스 5: 운영 중 인덱스 추가 시 테이블 잠금 발생

증상: CREATE INDEX를 실행하자 해당 테이블에 대한 모든 INSERT/UPDATE/DELETE가 수십 초~수 분 동안 멈춤

원인: PostgreSQL의 일반 CREATE INDEX는 테이블에 ShareLock을 걸어 쓰기 작업을 차단한다. 수백만 건 테이블에서는 인덱스 생성 완료까지 모든 쓰기 요청이 대기열에 쌓인다.

해결:

-- ❌ 운영 중 사용 금지: 테이블 잠금 발생
CREATE INDEX idx_orders_status ON orders(status);

-- ✅ 운영 중 안전한 방법: CONCURRENTLY 옵션
-- 테이블 잠금 없이 인덱스 빌드 (단, 기존 인덱스보다 2배 이상 시간 소요)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- 인덱스 생성 중 진행 상황 모니터링
SELECT phase, blocks_done, blocks_total,
  round(blocks_done::numeric / NULLIF(blocks_total, 0) * 100, 1) AS pct
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;

CONCURRENTLY 주의사항:

트랜잭션 내에서 실행 불가 (BEGIN 없이 단독 실행)
빌드 중 에러 발생 시 INVALID 상태 인덱스가 남음 → DROP INDEX CONCURRENTLY로 정리 후 재시도
TypeORM 마이그레이션에서 사용하려면 query() 직접 호출로 작성

실패 시나리오에서 가장 위험한 것은 “쿼리는 계속 느린데 배포 로그만 성공처럼 보이는” 경우다. PostgreSQL은 concurrent index build 실패 시 INVALID 인덱스가 남을 수 있고, 이 인덱스는 쿼리에는 무시되지만 업데이트 오버헤드는 계속 만든다. 인덱스 생성 직후 아래 쿼리로 indisvalid=false를 확인한다.

SELECT i.relname AS index_name, ix.indisvalid, ix.indisready
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE i.relname = 'idx_orders_status';

예상 출력이 idx_orders_status | f | t 또는 f를 포함하면 silent failure다. 복구는 같은 트랜잭션 안에서 재시도하지 말고, 단독 명령으로 정리 후 다시 생성한다.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

출처: PostgreSQL 공식 문서 — Building Indexes Concurrently

7. 체크리스트

8. 키워드

키워드	한 줄 설명
B+Tree	PostgreSQL 기본 인덱스 구조. 리프 연결 리스트로 범위 검색에 강함
Clustered Index	테이블 데이터 자체가 정렬된 인덱스 (PK 기반)
Non-Clustered Index	별도 구조로 키+포인터 저장. 대부분의 CREATE INDEX
Composite Index	여러 컬럼 복합 인덱스. Leftmost Prefix Rule 적용
Covering Index	필요한 모든 컬럼이 인덱스에 포함 → Index Only Scan
Seq Scan	전체 테이블 순차 스캔. 인덱스 없거나 결과 비율이 클 때
Index Scan	인덱스 → 힙 Random I/O. 소수 행 검색
Index Only Scan	힙 접근 없이 인덱스만으로 결과 반환. Covering Index
EXPLAIN ANALYZE	쿼리 실행 계획 + 실제 실행 결과 동시 출력
N+1 Problem	1번 조회 후 N번 추가 쿼리 발생. ORM 관계 로딩 실수
QueryBuilder	TypeORM의 세밀한 SQL 생성 API
pg_stat_statements	PostgreSQL 확장. 슬로우 쿼리 통계 수집
Leftmost Prefix Rule	복합 인덱스는 왼쪽 컬럼부터 순서대로 사용해야 함
Index Bloat	인덱스 내 dead tuple 누적으로 크기 비대화
REINDEX CONCURRENTLY	운영 중 락 없이 인덱스 재빌드 (PG 12+)
Partial Index	특정 조건의 행만 인덱싱하는 부분 인덱스

8.5. 추천 리소스

📖 PostgreSQL 공식 문서 - Using EXPLAIN — EXPLAIN ANALYZE의 모든 옵션과 각 노드 타입(Seq Scan, Index Scan 등) 공식 레퍼런스 (입문)
📖 Understanding the Mechanics of PostgreSQL B-Tree Indexes - PostgreSQL Fastware — B-Tree 구조를 페이지 단위까지 시각적으로 설명. pageinspect 확장으로 직접 확인하는 법 포함 (중급)
📖 Solving N+1 Problem in NestJS with TypeORM - Medium — NestJS + TypeORM 환경에서 N+1 발생 원인과 4가지 해결책 코드 예시 (입문)
📖 How to Use pg_stat_statements for Query Analysis - OneUptime — pg_stat_statements 설치부터 슬로우 쿼리 진단까지 실전 가이드, EXPLAIN ANALYZE 해석법 포함 (중급)
📖 TypeORM Indices 공식 문서 — @Index() 데코레이터 사용법, 복합 인덱스, 유니크 인덱스 등 TypeORM 인덱스 전체 레퍼런스 (입문)

출처

9. 직접 확인해보기

실습 1: 인덱스 유무에 따른 성능 차이 측정

-- 1. 테스트 테이블 생성 및 대량 데이터 삽입
CREATE TABLE test_orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO test_orders (user_id, status, created_at)
SELECT
  (random() * 10000)::INT,
  (ARRAY['PENDING', 'COMPLETED', 'CANCELLED'])[floor(random() * 3 + 1)],
  NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000);  -- 100만 건

-- 2. 인덱스 없이 실행 (Seq Scan 확인)
EXPLAIN ANALYZE
SELECT * FROM test_orders
WHERE status = 'PENDING' AND user_id = 1234;

-- 3. 인덱스 생성
CREATE INDEX idx_test_orders_user_status
ON test_orders(user_id, status);

-- 4. 동일 쿼리 재실행 (Index Scan 확인)
EXPLAIN ANALYZE
SELECT * FROM test_orders
WHERE status = 'PENDING' AND user_id = 1234;

-- 5. 정리
DROP TABLE test_orders;

실습 2: TypeORM N+1 재현 및 해결

import { DataSource } from "typeorm";

// DataSource에 logging: ['query'] 설정
// 1. N+1 발생 버전 실행 → 로그에서 반복 쿼리 확인
const orders = await orderRepo.find();
for (const order of orders) {
  const user = await userRepo.findOne({ where: { id: order.userId } });
}

// 2. 해결 버전 실행 → 쿼리 1번만 발생 확인
const orders = await orderRepo
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user")
  .getMany();

실습 3: pg_stat_statements로 슬로우 쿼리 찾기

-- 1. 설정 확인
SHOW shared_preload_libraries;  -- pg_stat_statements 포함 확인

-- 2. 통계 초기화 후 쿼리 실행
SELECT pg_stat_statements_reset();

-- 3. 실제 쿼리들 실행 (애플리케이션 사용 or 직접 실행)

-- 4. 슬로우 쿼리 확인
SELECT
  left(query, 100) AS query_preview,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

실습 4: 안티패턴 확인

-- 아래 쿼리들이 인덱스를 타는지 EXPLAIN으로 확인
-- (idx_test_orders_user_status 인덱스가 있다고 가정)

-- 안티패턴 1: 함수 감싸기
EXPLAIN SELECT * FROM test_orders WHERE LOWER(status) = 'pending';
-- → Seq Scan 발생

-- 안티패턴 2: LIKE 앞 와일드카드
EXPLAIN SELECT * FROM test_orders WHERE status LIKE '%END%';
-- → Seq Scan 발생

-- 정상 패턴
EXPLAIN SELECT * FROM test_orders WHERE status = 'PENDING';
-- → Index Scan 발생

실습 후 자가 점검 질문

실습을 마친 뒤 아래 질문에 스스로 답할 수 있으면 개념이 내재화된 것이다.

EXPLAIN ANALYZE 해석

cost=0.00..4821.00에서 앞의 0.00과 뒤의 4821.00은 각각 무엇을 의미하는가?
rows=12(예측)와 actual rows=1200(실제)의 차이가 크면 어떻게 대응하는가?
Rows Removed by Filter: 99988 이 표시됐을 때 가장 먼저 할 행동은?
Buffers: shared hit=5 read=620 결과를 보고 어떤 결론을 내리는가?

인덱스 설계 판단

SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders; 결과가 0.003(0.3%)라면 status 단독 인덱스를 추가하겠는가? 이유는?
동일 테이블에 인덱스가 8개 있다면 쓰기 성능에 어떤 영향이 있는가?
MySQL InnoDB에서 보조 인덱스 조회가 PostgreSQL Non-Clustered 인덱스 조회보다 항상 느린가? 왜 그런가?

목표 수치 기준

슬로우 쿼리를 개선했을 때 “성공”을 판단하는 지표는 무엇인가? (예: mean_exec_time, Seq Scan 제거, actual time ms)
인덱스 추가 후 pg_stat_user_indexes의 어떤 컬럼을 2주 후 확인해야 하는가?

10. 요약

핵심 포인트	한 줄 요약
B+Tree 구조	루트→브랜치→리프(연결 리스트), O(log N), 범위 검색 강점
인덱스 종류	Clustered(정렬된 실제 데이터), Non-Clustered(별도 구조+포인터), Composite(Leftmost Prefix), Covering(Index Only Scan)
EXPLAIN ANALYZE	cost=예상비용, rows=예상행수, actual time=실제시간, Seq Scan→Index Scan이 목표
N+1 해결	`find({ relations })` 또는 `.leftJoinAndSelect()` 로 단일 쿼리로 통합
안티패턴	함수 감싸기(LOWER, DATE), LIKE ‘%keyword’, 타입 불일치 → 인덱스 무효화
슬로우 쿼리	pg_stat_statements로 total_exec_time 기준 TOP N 추출 후 EXPLAIN ANALYZE 분석
TypeORM 인덱스	`@Index()` 로 단순 인덱스, `@Index(['col1','col2'])` 로 복합 인덱스, INCLUDE는 마이그레이션 직접 작성

인덱스는 읽기를 빠르게 하지만 쓰기(INSERT/UPDATE/DELETE)는 느려진다. 무분별하게 추가하지 말고, 실제 슬로우 쿼리를 확인한 뒤 필요한 곳에 정확하게 추가하는 것이 원칙이다.

11. 실전 장애 대응 시나리오 (On-Call Runbook)

DB 쿼리 성능 문제 발생 시 on-call 대응 체크리스트

시나리오 A: “API 응답이 갑자기 느려졌다” (슬로우 쿼리 의심)

즉각 확인 (5분 이내):

1단계: RDS/CloudWatch에서 DB 지표 확인
  경로: CloudWatch → RDS → Per-Database Metrics
  확인: ReadLatency, WriteLatency, DatabaseConnections
  → ReadLatency가 평소보다 10배 이상이면 슬로우 쿼리 의심

2단계: pg_stat_activity에서 현재 실행 중인 쿼리 확인
  SELECT pid, state, query, now() - query_start AS duration
  FROM pg_stat_activity
  WHERE state = 'active' AND query_start < now() - interval '5 seconds'
  ORDER BY duration DESC;
  → 5초 이상 실행 중인 쿼리가 있으면 해당 쿼리가 원인 후보

3단계: EXPLAIN ANALYZE로 실행 계획 확인
  → Seq Scan이 보이면 인덱스 누락
  → Nested Loop + 높은 loops 값이면 N+1 의심
  → Rows Removed by Filter가 크면 인덱스 후보

4단계: 즉시 조치
  → 인덱스 누락: CREATE INDEX CONCURRENTLY로 운영 중 추가
  → N+1 문제: 코드 수정 후 핫픽스 배포
  → 장시간 실행 쿼리: pg_cancel_backend(pid)로 취소

시나리오 B: “배치 작업이 갑자기 오래 걸린다”

배치 정산/통계 작업 성능 저하 시:

1단계: 데이터 변경 확인
  → 최근 대량 INSERT/UPDATE 이후 통계가 갱신되지 않았는지 확인
  SELECT relname, last_autoanalyze, n_live_tup, n_dead_tup
  FROM pg_stat_user_tables
  WHERE relname = 'orders';
  → last_autoanalyze가 오래됐으면: ANALYZE orders;

2단계: 인덱스 Bloat 확인
  SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
  FROM pg_stat_user_indexes
  WHERE schemaname = 'public'
  ORDER BY pg_relation_size(indexrelid) DESC
  LIMIT 10;
  → 테이블 크기 대비 인덱스가 비정상적으로 크면 Bloat
  → 해결: REINDEX INDEX CONCURRENTLY idx_name;

3단계: Lock 대기 확인
  → 배치 작업이 다른 트랜잭션과 Lock 경합하는지 확인
  SELECT blocked.query AS blocked_query, blocking.query AS blocking_query
  FROM pg_stat_activity blocked
  JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

시나리오 C: “미사용 인덱스 정리로 쓰기 성능 개선”

정기 점검 항목 (월 1회 권장):

1. 미사용 인덱스 찾기
  SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0 AND schemaname = 'public'
  ORDER BY pg_relation_size(indexrelid) DESC;
  → idx_scan = 0인 인덱스는 한 번도 사용되지 않은 것

2. 삭제 전 반드시 확인
  → 해당 인덱스가 FK 제약조건에 사용되는지 확인
  → 최소 2주 이상 idx_scan 데이터 수집 후 판단 (배치 작업 주기 고려)

3. 안전하게 삭제
  DROP INDEX CONCURRENTLY idx_unused_index;
  → CONCURRENTLY로 삭제해야 테이블 Lock 방지

2025년 최신 동향

PostgreSQL 17 인덱스 성능 개선 (2024~2025)

PostgreSQL 17에서 B-Tree 인덱스의 성능이 개선됐다. 특히 대량 INSERT 시 인덱스 갱신 비용이 줄었고, BRIN 인덱스의 범위 계산이 최적화됐다. 또한 EXPLAIN 출력에 I/O 통계(BUFFERS 옵션)가 더 세분화되어 성능 분석이 정밀해졌다.

TypeORM vs Drizzle ORM 트렌드

2025년 NestJS 생태계에서 Drizzle ORM이 빠르게 성장하고 있다. TypeORM의 save() 메서드가 내부적으로 SELECT → UPDATE 순서로 처리하면서 Deadlock이 발생하는 알려진 이슈(#10586)가 여전히 해결되지 않은 상태다. 신규 프로젝트에서는 Drizzle ORM을 검토하되, 기존 TypeORM 프로젝트에서는 insert()/update() 직접 사용을 권장한다.

pg_stat_statements 대체: auto_explain

슬로우 쿼리를 사전에 감지하는 방법으로 auto_explain 확장이 주목받고 있다. 지정 시간 이상 걸리는 쿼리의 실행 계획을 자동으로 로그에 기록해, 운영 중 성능 문제를 사후 분석할 수 있다.

-- auto_explain 설정 (postgresql.conf 또는 RDS 파라미터 그룹)
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '1s'  -- 1초 이상 걸리는 쿼리의 실행 계획 자동 기록
-- auto_explain.log_analyze = true       -- 실제 실행 시간 포함

📖 더 보기: PostgreSQL Performance Tuning Checklist 2026 — 2026년 기준 PostgreSQL 성능 튜닝 전체 체크리스트, 인덱스 설정부터 파라미터 조정까지 (중급)