DB Index & Query Optimization

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

1. 한 줄 정의

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

2. 왜 중요한가

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

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 2025 기준)

PostgreSQL의 B-Tree는 Lehman & Yao 알고리즘 위에 추가 최적화가 적용돼 있다.

핵심 최적화 3가지:

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

2. Index Deduplication (PostgreSQL 13+)
   - 중복값이 많은 인덱스에서 같은 값을 하나의 포스팅 리스트로 압축
   - 예: status='PENDING' 값이 100만 건 → 중복 제거로 인덱스 크기 50% 감소 가능
   - 자동 적용: 별도 설정 불필요

3. Bottom-up Deletion
   - 인덱스 페이지가 꽉 찰 때 페이지 분할 전에 dead tuple 정리 먼저 시도
   - UPDATE/DELETE가 잦은 테이블에서 인덱스 비대화 방지

구조

루트(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 확장으로 직접 확인하는 방법 포함

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-4. N+1 Problem

발생 원리

// ❌ 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)

실제 쿼리 흐름 예시

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

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

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) = 'user@example.com'
WHERE DATE(created_at) = '2026-04-01'
WHERE EXTRACT(YEAR FROM created_at) = 2026

-- ✅ 해결: 컬럼을 그대로 쓰거나, Expression Index 사용
WHERE email = LOWER('USER@EXAMPLE.COM')    -- 값을 변환

-- 또는 Expression Index 생성
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
WHERE LOWER(email) = 'user@example.com'   -- 이제 인덱스 탐

-- ❌ 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() 직접 호출로 작성

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 발생

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 성능 튜닝 전체 체크리스트, 인덱스 설정부터 파라미터 조정까지 (중급)