✍️ 필사 모드: 데이터베이스 엔진 내부 완전 정복 — B-Tree부터 WAL, MVCC, 쿼리 플래너, 격리 수준까지 (2025)

0. 시작하기 전에 — "DB는 블랙박스"라는 착각

우리는 매일 SELECT * FROM users WHERE id = 42 같은 쿼리를 쓴다. 그런데 DB가 어떻게 단 1ms 만에 10억 행 중에서 1행을 꺼내는지 설명하라고 하면 — 대부분 "인덱스 덕분" 이라고 답하고 멈춘다.

인덱스가 B-Tree라는 건 들어봤지만 왜 B-Tree인지, 왜 해시 테이블이 아닌지는 애매하다. 트랜잭션이 ACID를 보장한다는 건 알지만 커밋 순간에 어떻게 "크래시해도 데이터가 살아남는지" 설명하라고 하면 막막하다. READ COMMITTED와 REPEATABLE READ 차이는 외웠지만 실제로 어떤 이상(anomaly)을 막는지는 실험해본 적이 없다.

이 글은 40년간 진화해온 RDBMS의 내부 구조 를 SQL 한 줄의 여정을 따라가며 풀어본다. 1970년 Edgar Codd의 논문에서 시작해서 Oracle, PostgreSQL, MySQL, SQLite가 각자 택한 트레이드오프까지. 그리고 왜 2020년대의 NewSQL과 분산 DB들이 이 고전적 아이디어 위에 계속 서 있는지까지.

1. 큰 그림 — DB 엔진의 6계층 아키텍처

데이터베이스 서버는 마치 러시아 인형처럼 여섯 층으로 포개져 있다.

┌─────────────────────────────────────────────────────────┐
│  1. Client / Protocol Layer (libpq, JDBC, wire protocol)│
├─────────────────────────────────────────────────────────┤
│  2. Parser (SQL → AST)                                   │
├─────────────────────────────────────────────────────────┤
│  3. Query Planner / Optimizer (AST → Plan tree)          │
├─────────────────────────────────────────────────────────┤
│  4. Executor (Plan → Tuple stream)                       │
├─────────────────────────────────────────────────────────┤
│  5. Access Method (B-Tree, Hash, GiST, Heap scan)        │
├─────────────────────────────────────────────────────────┤
│  6. Buffer Manager / WAL / Storage (page cache, fsync)   │
└─────────────────────────────────────────────────────────┘

한 줄의 SQL은 이 여섯 층을 세로로 관통 하며 실행된다. 각 층은 독립적으로 진화해왔고, 어떤 DB는 특정 층을 아주 다르게 구현한다 — 예를 들어 CockroachDB는 6층이 RocksDB (LSM-Tree) 이고, MySQL InnoDB는 B+Tree, SQLite는 모든 걸 단일 프로세스에 임베드한다.

2. SELECT 한 줄의 여정 — 전체 시나리오

SELECT name FROM users WHERE id = 42 를 예시로 전체 흐름을 따라가 보자.

2.1 Parser — 문자열이 트리가 되는 순간

"SELECT name FROM users WHERE id = 42"
           ↓  (렉서: 토큰화)
[SELECT] [IDENT:name] [FROM] [IDENT:users] [WHERE] [IDENT:id] [=] [NUM:42]
           ↓  (파서: LR/recursive descent)
SelectStmt
├── targetList: [ColumnRef(name)]
├── fromClause: [RangeVar(users)]
└── whereClause: OpExpr(=, ColumnRef(id), Const(42))

Postgres의 파서는 Bison (yacc 계열) 으로 짜여 있고, MySQL은 자체 파서를 쓴다. SQLite는 자체 파서 생성기인 Lemon을 사용한다. 파싱 단계에서 구문 오류가 잡히고, 테이블/컬럼 이름은 아직 검증되지 않는다.

2.2 Analyzer — 이름 해석과 타입 체크

WHERE id = 42
      ↓
id → users.id (BIGINT) [카탈로그 조회]
42 → CONST 42 (INT4) → BIGINT (암묵적 캐스트)

이 단계에서 카탈로그 (pg_class, pg_attribute) 를 뒤져서 "users 테이블에 id 컬럼이 정말 있는지, 타입이 뭔지" 를 확인한다. 이 과정에서 내부 표현은 파스 트리에서 쿼리 트리로 변환된다.

2.3 Planner — 가능한 실행 계획들을 비교한다

여기가 진짜 마법이 일어나는 곳이다. 같은 결과를 내는 계획이 수십 가지 있을 수 있다.

-- Plan A: Seq Scan
Seq Scan on users (cost=0.00..25000.00 rows=1)
  Filter: id = 42

-- Plan B: Index Scan
Index Scan using users_pkey on users (cost=0.29..8.31 rows=1)
  Index Cond: id = 42

-- Plan C: Bitmap Heap Scan
Bitmap Heap Scan on users (cost=4.30..12.31 rows=1)
  Recheck Cond: id = 42
  -> Bitmap Index Scan on users_pkey (cost=0.00..4.30)

옵티마이저는 통계 (ANALYZE 결과) 를 보고 "이 쿼리는 1행만 뽑힐 거다" 라고 예측 → Index Scan 을 고른다. 10만 행이 뽑힐 거면 Seq Scan 이 더 빠를 수도 있다.

2.4 Executor — 실제로 데이터를 가져온다

Index Scan: users_pkey
  ↓ (B-Tree에서 id=42의 TID를 찾는다)
TID = (page 17, offset 3)
  ↓ (Heap 테이블에서 해당 페이지를 읽는다)
Tuple: (id=42, name="Alice", email="...")
  ↓ (Projection: name만 추출)
"Alice"

이 모든 과정이 보통 1ms 이하 에 끝난다. 이게 가능한 이유가 다음 장부터 나올 B-Tree + Buffer Cache + WAL 트리오 덕분이다.

3. B-Tree — 왜 모든 DB가 같은 자료구조를 쓰는가

3.1 해시 테이블이 아닌 이유

"O(1) 조회" 라면 해시 테이블이 B-Tree (O(log n)) 보다 빠를 것 같다. 그런데 왜 DB는 B-Tree 를 선호할까?

• 범위 쿼리: WHERE age BETWEEN 20 AND 30 — 해시는 범위를 모른다. B-Tree 는 리프 노드가 정렬 연결 리스트이므로 범위 스캔이 자연스럽다.
• 정렬: ORDER BY id — 이미 정렬되어 있으므로 추가 정렬 비용이 없다.
• prefix 매칭: WHERE name LIKE 'A%' — 해시는 prefix 개념이 없다.
• 디스크 지역성: 해시는 무작위 충돌 → 무작위 I/O. B-Tree 는 지역성이 높다.
• 동시성: B-Tree 는 노드 단위 lock-coupling이 가능하다. 해시는 리사이즈 때 전체 락.

결론: 해시 인덱스는 정확히 등호 매칭만 필요한 특수 상황 (예: Postgres의 USING HASH) 에서만 쓰이고, 기본값은 B-Tree.

3.2 B-Tree 대신 B+Tree 인 이유

교과서에서 배우는 "B-Tree" 는 내부 노드에도 값을 저장한다. 하지만 실제 DB 는 B+Tree 를 쓴다.

팬아웃이 크다는 게 핵심이다. 한 페이지 (8KB) 에 더 많은 키를 넣을 수 있으면 트리 높이가 낮아진다. 10억 행 테이블도 B+Tree 높이는 보통 3~4 단계. 즉 3~4번의 페이지 읽기로 원하는 행에 도달한다.

3.3 페이지 구조 — 8KB 안에 무엇이 들어있나

Postgres 의 한 페이지 (8KB) 내부:

┌──────────────────────────────┐
│ PageHeader (24 bytes)        │ ← LSN, checksum, flags
├──────────────────────────────┤
│ ItemId[] (line pointers)     │ ← offset, length to each tuple
├──────────────────────────────┤
│   ...free space...           │
├──────────────────────────────┤
│ Tuple N                      │
│ Tuple N-1                    │
│ ...                          │
│ Tuple 0                      │
├──────────────────────────────┤
│ Special Space (B-Tree: 좌/우)│
└──────────────────────────────┘

• 라인 포인터 가 별도로 있는 이유: 튜플 위치가 바뀌어도 (예: UPDATE 후) 포인터만 고치면 된다. 인덱스는 TID (page, offset) 를 가리키는데, offset 이 line pointer 인덱스다.
• 튜플은 뒤에서부터 앞으로 채워진다. 라인 포인터는 앞에서부터 뒤로. 둘이 만날 때까지 페이지를 채운다.

3.4 B-Tree 가 클러스터드일 때와 아닐 때

MySQL InnoDB (Clustered):

• 주 인덱스 (PK) 의 리프 노드가 곧 데이터 행.
• 보조 인덱스의 리프 노드는 PK 값 을 저장.
• 보조 인덱스로 조회하면 "보조 인덱스 → PK → 주 인덱스 → 데이터" 의 2번의 B-Tree 탐색이 필요.

Postgres (Heap + Index 분리):

• 모든 인덱스가 TID 를 가리킨다.
• 행은 Heap 파일에 저장되고, 인덱스는 별도.
• 기본 인덱스도 "인덱스 → TID → Heap" 의 경로를 탄다.

트레이드오프: InnoDB 는 PK 조회가 빠르지만 PK 가 랜덤 UUID 면 페이지 분할이 심하다. Postgres 는 PK 변경이 자유롭다 (인덱스만 갱신) 하지만 HOT (heap-only tuple) 같은 최적화가 필요했다.

4. 버퍼 매니저 — 메모리와 디스크의 간극

4.1 왜 DB 는 OS 페이지 캐시를 신뢰하지 않는가

OS 는 이미 파일 시스템 레벨에서 페이지 캐시를 한다. 그런데 DB 는 자체 버퍼 풀 (shared_buffers, InnoDB buffer pool) 을 또 유지한다. 왜?

1. 교체 정책 제어: OS 는 LRU 를 쓰지만, DB 는 자주 접근되는 인덱스 루트를 영구적으로 메모리에 두고 싶다 → Clock-Sweep, LRU-K 같은 특화 알고리즘.
2. Dirty page 추적: OS 는 어느 페이지가 커밋돼야 하는지 모른다. DB 는 WAL 과 동기화된 "checkpoint" 시점에 정확히 flush 해야 한다.
3. Direct I/O: InnoDB 는 O_DIRECT 로 OS 캐시를 우회한다 (이중 캐싱 방지). Postgres 는 OS 캐시에 의존한다 (철학 차이).
4. 동시 접근: 버퍼 풀 엔트리에 pin 을 걸어 "이 페이지는 누가 읽는 중이니 교체하지 마라" 를 표현한다.

4.2 Postgres 의 Clock-Sweep 알고리즘

LRU 는 매 접근마다 리스트를 재정렬해야 한다 → 동시성 병목.

Clock-Sweep: 버퍼들을 원형 배열로 놓고, 포인터가 돌아다니며 "사용 카운트" 를 감소시킨다. 카운트가 0 이 된 버퍼가 교체 대상이 된다. 락 경합이 훨씬 적다.

[A:2] [B:1] [C:3] [D:0*] [E:1]
                    ↑ 교체!
한 바퀴 돌때마다 카운트 -1
접근될 때마다 +1 (최대 5)

4.3 읽기 흐름

1. SELECT 요청
2. 버퍼 풀에 해당 페이지가 있는지 해시 테이블 조회
3. Hit: 바로 반환, 카운트 증가
   Miss: Clock-Sweep 으로 victim 선택
         → victim 이 dirty면 WAL 보장 후 디스크 쓰기
         → 디스크에서 새 페이지 읽기
         → 해시 테이블에 등록

"Cache hit ratio 99%" 의 중요성이 여기서 나온다. 디스크 I/O 는 메모리보다 100,000 배 느리다. 99% hit 이면 평균 접근 시간이 거의 메모리 수준.

5. WAL — 쓰기 성능의 숨은 주역

5.1 문제 상황: 커밋할 때마다 fsync 하면 어떻게 되는가

트랜잭션을 커밋할 때마다 해당 페이지를 디스크에 쓰면 :

• 페이지 = 8KB. 하지만 보통 1행만 변경됨.
• fsync 비용 = 수 ms. 초당 1000 트랜잭션이 한계.
• 랜덤 I/O 가 폭주.

5.2 WAL 의 혁명적 아이디어 (1992, ARIES 논문)

"변경된 페이지 대신, 변경 사항 (로그 레코드) 만 디스크에 순차적으로 쓰자. 페이지는 나중에 천천히 flush 하자."

• 로그는 순차 쓰기 (sequential write) → SSD 든 HDD 든 훨씬 빠름.
• 여러 트랜잭션의 로그를 그룹 커밋 (Group Commit) 으로 묶어서 fsync 1번에 처리.
• 크래시 시: 최근 checkpoint 부터 WAL 을 재생 (REDO) 하면 복구 완료.

5.3 WAL 레코드 예시

LSN 0/1A2B3C10: XLOG_HEAP_UPDATE
  rel: users, tid: (17, 3)
  old_xmax: txn 1000
  new_tid: (17, 4)
  new_tuple: { id:42, name:"Alice2", email:"..." }

이 레코드 하나가 디스크에 안전하게 기록되면, 실제 users 테이블 페이지는 아직 메모리에 더티 상태여도 된다. 커밋 보장은 WAL 기록이 fsync 된 시점 에 이루어진다.

5.4 체크포인트 — 메모리와 디스크 재동기화

WAL 만 있으면 영원히 누적되므로 때때로 "지금 시점의 메모리 더티 페이지를 모두 flush" 하는 체크포인트를 찍는다. 체크포인트 이후의 WAL 만 복구에 필요하다.

체크포인트 튜닝의 흔한 함정:

• 너무 자주: I/O 폭주, 성능 저하.
• 너무 드물게: 크래시 복구 시간이 수십 분.

Postgres 의 checkpoint_timeout=15min, max_wal_size=10GB 같은 기본값은 이 트레이드오프의 결과다.

5.5 Full Page Writes — torn page 문제

8KB 페이지를 디스크에 쓰는 중에 크래시 → 앞 4KB 만 쓰이고 뒤 4KB 는 안 쓰인 torn page 가 발생할 수 있다. OS/디스크는 원자성 단위가 보통 512 bytes 또는 4KB 이므로 8KB 페이지는 보장 안 됨.

해결: 체크포인트 이후 첫 번째 해당 페이지 변경 시 WAL 에 페이지 전체 사본 (FPW) 을 쓴다. 크래시 시 FPW 부터 재생하면 torn page 복구 가능.

MySQL InnoDB 는 doublewrite buffer 로 같은 문제를 해결한다 — 페이지를 먼저 별도 영역에 쓰고 나서 실제 위치에 쓴다. 쓰기 양이 2배가 되는 대신 WAL 이 작다.

6. MVCC — 동시성의 은빛 탄환

6.1 락 기반 모델의 한계

"READ 는 읽기 락, WRITE 는 쓰기 락" 의 전통적 모델 (DB2 의 기본값) 은 간단하지만:

• 읽기가 쓰기를 블록하고 쓰기가 읽기를 블록한다.
• 장기 트랜잭션이 OLTP 시스템을 마비시킨다.

6.2 MVCC 의 아이디어 — "버전을 여러 개 저장하자"

"UPDATE 할 때 원본을 지우지 말고, 새 버전을 추가한다. 각 트랜잭션은 자기가 시작할 때 보이던 버전만 본다."

Postgres 의 구현:

• 각 튜플에 xmin (생성 트랜잭션) 과 xmax (삭제 트랜잭션) 가 있다.
• xmin <= my_txid < xmax 인 튜플만 나에게 보인다.
• UPDATE 는 기존 튜플의 xmax 를 내 txid 로 설정하고, 새 튜플을 INSERT.

결과:

• 읽기는 쓰기를 블록하지 않는다.
• 쓰기는 읽기를 블록하지 않는다.
• 쓰기 간 충돌만 락으로 해결.

6.3 VACUUM — MVCC 의 숨은 비용

죽은 튜플 (dead tuple: 모든 트랜잭션에 안 보이게 된 버전) 을 정리하지 않으면 테이블이 무한히 커진다. Postgres 는 VACUUM 으로 주기적으로 정리.

• Autovacuum: 기본적으로 자동 실행. 하지만 장기 트랜잭션이 있으면 진행이 멈춘다 ("vacuum 이 XID xxx 에서 막혔다").
• VACUUM FULL: 테이블을 재작성. 락이 필요하고 오래 걸림.
• transaction ID wraparound: XID 가 32-bit 이라서 약 20억 트랜잭션 후 회전. VACUUM 이 제때 안 되면 "데이터베이스가 종료됩니다" 경고가 나온다. Sentry 가 2015 년에 이 문제로 장애를 겪었다.

6.4 Oracle/MySQL 의 다른 MVCC

Oracle: Undo segment 에 이전 버전 저장. 메인 테이블은 항상 최신. VACUUM 불필요. 단점: undo 영역이 부족하면 "ORA-01555 snapshot too old".

MySQL InnoDB: Undo log + Rollback segment. Postgres 와 Oracle 의 중간 모델. 보조 인덱스에는 delta 만 저장되어 PK 조회가 필요하다.

6.5 스냅샷 격리의 이상한 점

MVCC 는 보통 "스냅샷 격리 (Snapshot Isolation, SI)" 를 제공한다. 대부분의 이상 (anomaly) 을 방지하지만 Write Skew 는 놓친다:

-- 테이블: employees(id, dept, on_call boolean)
-- 규칙: "응급실 근무자가 최소 1명이어야 한다"
-- 초기 상태: Alice, Bob 둘 다 on_call=true

Tx1: SELECT COUNT(*) FROM employees WHERE dept='ER' AND on_call=true; -- 2
     UPDATE employees SET on_call=false WHERE id=Alice;

Tx2: SELECT COUNT(*) FROM employees WHERE dept='ER' AND on_call=true; -- 2
     UPDATE employees SET on_call=false WHERE id=Bob;

-- 둘 다 커밋 → on_call 이 0명!

두 트랜잭션이 서로의 변경을 못 보고 각자 "2명 중 1명만 빼도 된다" 고 판단한다. SI 는 이걸 못 잡는다.

해결: Serializable Snapshot Isolation (SSI). Postgres 9.1+ 에서 SERIALIZABLE 격리 수준이 SSI 로 구현. 읽기 의존성 그래프를 추적해서 순환이 생기면 한 트랜잭션을 abort.

7. 격리 수준 — 4단계의 의미와 함정

SQL 표준 격리 수준 + 방지하는 이상:

하지만 DB 마다 같은 이름이 다른 것을 의미 한다:

• Postgres REPEATABLE READ: 사실상 SI. Phantom 도 방지됨. 하지만 Write Skew 는 발생.
• MySQL REPEATABLE READ (default): Gap Lock 덕분에 Phantom 방지. 하지만 "진짜 SI" 는 아니라서 다른 이상이 있음.
• Oracle SERIALIZABLE: 실제로는 SI. Write Skew 발생 가능.
• Postgres SERIALIZABLE: SSI 로 진짜 serializability 보장. 대신 abort 빈도 증가.

결론: "격리 수준을 설정했다" 만으로 안전을 보장하지 말고, 어떤 이상이 남아있는가 를 DB 공식 문서에서 확인해야 한다.

8. 쿼리 옵티마이저 — "최선의 계획" 은 어떻게 선택되는가

8.1 비용 기반 vs 규칙 기반

초기 DB (1980 년대) 는 규칙 기반 (RBO): "인덱스가 있으면 쓰라" 같은 하드코딩. 단점: 통계 무시 → 작은 테이블에서도 인덱스 쓰다가 느려짐.

1988 년 System R 의 Selinger 논문 이후 비용 기반 (CBO) 이 표준이 됨. 각 플랜의 예상 비용을 계산 → 최소 비용 플랜 선택.

8.2 비용 = CPU + I/O + 메모리

Seq Scan: cpu_tuple_cost * rows + seq_page_cost * pages
Index Scan: random_page_cost * pages (매칭되는) + cpu_index_tuple_cost * rows

Postgres 의 기본값 random_page_cost=4.0, seq_page_cost=1.0 은 HDD 시대의 값. SSD 환경에서는 random_page_cost=1.1 로 낮추는 게 관례가 됐다.

8.3 통계 — ANALYZE 가 중요한 이유

옵티마이저는 테이블 통계를 보고 "이 쿼리가 몇 행 뽑힐지" 를 추정 한다.

pg_stats:
  n_distinct: -0.5  (고유값 비율)
  most_common_vals: {'active', 'inactive'}
  most_common_freqs: {0.8, 0.15}
  histogram_bounds: {1, 100, 500, 1000}

WHERE status = 'active' → most_common_freqs 에서 0.8 → 전체의 80% 가 매칭 → Seq Scan 선택. WHERE status = 'deleted' → most_common_vals 에 없음 → 1/n_distinct → 매우 적음 → Index Scan.

통계가 오래되면 "10만 행인데 10행이라고 잘못 추정" 같은 실수가 나와 플랜이 완전히 빗나간다. ANALYZE 가 그래서 중요하다. Autovacuum 이 자동으로 해주지만 배치 인서트 후 수동으로 돌리는 게 좋다.

8.4 조인 순서 — 지수적 탐색 공간

3개 테이블 조인 순서는 3! = 6 가지. 10개면 3,628,800 가지. 완전 탐색은 불가능.

동적 프로그래밍 (Selinger 알고리즘): 2-테이블 부분 플랜의 최적을 기억 → 3-테이블 확장 → 4-테이블 ... O(2^n) 이지만 n=12 까지는 가능.

Genetic Query Optimizer (GEQO): Postgres 가 12개 이상 테이블 조인 시 사용. 유전 알고리즘으로 근사 최적 탐색.

8.5 조인 알고리즘 3형제

• Nested Loop: 외부 루프 × 내부 루프. 외부가 작고 내부에 인덱스 있을 때 최고.
• Hash Join: 작은 쪽으로 해시 테이블 만들고 큰 쪽 스캔. 큰 테이블 × 큰 테이블 + 등호 조인일 때.
• Merge Join: 두 입력 정렬 후 병합. 이미 정렬된 입력 (예: 같은 인덱스) 일 때.

EXPLAIN ANALYZE 로 실제 선택된 알고리즘을 확인하는 습관이 중요하다.

9. 인덱스 — 은총이자 저주

9.1 복합 인덱스의 왼쪽 우선 규칙

CREATE INDEX idx ON users(country, city, age);

-- 쓸 수 있는 쿼리:
WHERE country='KR' AND city='Seoul' AND age=30 ✓
WHERE country='KR' AND city='Seoul' ✓
WHERE country='KR' ✓

-- 쓸 수 없는 (또는 비효율) 쿼리:
WHERE city='Seoul' ✗ (country 가 없음)
WHERE country='KR' AND age=30 △ (city 건너뛰기: index scan 가능하지만 비효율)

이유: B-Tree 는 (country, city, age) 의 lexicographic 순으로 정렬. 왼쪽이 고정 안 되면 범위가 의미 없다.

9.2 Covering Index — 힙 읽기 생략

CREATE INDEX idx ON users(email) INCLUDE (name);

SELECT name FROM users WHERE email='x@y.com';
-- 인덱스에 email, name 이 다 있어서 Heap 접근 불필요 → Index Only Scan

Postgres 는 INCLUDE 구문 (11+) 으로 지원. MySQL 은 그냥 복합 인덱스로 같은 효과 (INDEX(email, name)).

단: Visibility Map 이 최신 상태여야 한다 (MVCC 상 튜플이 보이는지 Heap 확인 없이 판단 가능한지 표시). VACUUM 이 이걸 유지한다.

9.3 부분 인덱스 — 공간 절약

CREATE INDEX idx ON orders(user_id) WHERE status='pending';

• 테이블의 5% 만 pending 이면 인덱스가 5% 크기.
• WHERE status='pending' AND user_id=42 쿼리가 엄청 빨라짐.
• 그 외 쿼리는 이 인덱스를 못 쓴다.

9.4 인덱스의 비용

인덱스는 공짜가 아니다:

• 쓰기 비용: INSERT/UPDATE 마다 모든 인덱스를 갱신.
• 공간: 큰 테이블은 인덱스만으로 수 GB.
• WAL 볼륨: 인덱스 변경도 WAL 에 기록 → 복제 지연.
• 플래너 혼란: 너무 많은 인덱스는 계획 탐색 시간을 늘린다.

실무 규칙: 매달 쓰이지 않는 인덱스는 삭제. Postgres 는 pg_stat_user_indexes.idx_scan=0 인 인덱스를 찾아낼 수 있다.

10. 실전 튜닝 시나리오 — EXPLAIN 의 언어

10.1 EXPLAIN ANALYZE 읽는 법

Hash Join  (cost=100.43..1234.56 rows=1000 width=48)
           (actual time=2.5..15.8 rows=987 loops=1)
  Hash Cond: (orders.user_id = users.id)
  Buffers: shared hit=234 read=12
  -> Seq Scan on orders (cost=0.00..500.00 rows=10000 width=32)
                        (actual time=0.1..5.2 rows=10000 loops=1)
  -> Hash (cost=50.00..50.00 rows=100 width=16)
         -> Seq Scan on users (cost=0.00..50.00 rows=100 width=16)
                               (actual time=0.05..0.5 rows=100 loops=1)
Planning Time: 0.3 ms
Execution Time: 16.1 ms

체크 포인트:

1. rows 추정 vs 실제 차이: 추정 1000, 실제 987 → 정확. 만약 추정 10 실제 10000 이면 통계가 틀렸다.
2. actual time 의 loops: Nested Loop 내부면 loops 가 매우 크다. 내부 비용을 loops 로 곱해야 실제 비용.
3. Buffers: shared hit = 메모리, read = 디스크. read 가 많으면 버퍼 풀을 키워야 할 수도.
4. Planning Time vs Execution Time: 계획 시간이 실행 시간보다 길면 prepared statement 를 고려.

10.2 느린 쿼리의 흔한 원인 5가지

1. 통계 오래됨 → ANALYZE 재실행.
2. 함수/형변환이 인덱스 무효화: WHERE lower(email) = 'x' → expression index 필요 (CREATE INDEX ON users(lower(email))).
3. 파라미터 스니핑: prepared statement 가 처음 호출의 파라미터로 계획 고정 → 다른 파라미터에서 망가짐.
4. N+1 쿼리: ORM 이 생성하는 반복 쿼리. JOIN 이나 배치 로딩으로 해결.
5. 인덱스 누락: EXPLAIN 에 Seq Scan 이 나오면서 rows 가 크면 인덱스 후보.

10.3 파티셔닝 — 초대형 테이블의 구원

10억 행 테이블은 인덱스만으로 안 된다. 파티셔닝 으로 테이블 자체를 쪼갠다:

• Range: 날짜별 (created_at 월 단위) — 시계열 데이터.
• List: 카테고리별 (country 별) — 지역 분리.
• Hash: 해시 값으로 균등 분할 — PK 로는 충분한데 분산이 필요할 때.

Postgres 10+ 의 declarative partitioning 으로 편해졌다. 옛 버전은 트리거로 구현해야 했다.

11. 분산 DB 와 NewSQL — 고전 아이디어의 확장

11.1 Replication — 동기 vs 비동기

• 동기 복제: 주 DB 가 commit 전에 복제본의 ACK 를 기다림. 지연 증가, 가용성 감소, 데이터 안전성 최상.
• 비동기 복제: commit 후 복제본에 전파. 빠르지만 failover 시 데이터 손실 가능.
• Semi-sync (MySQL): 적어도 1개 복제본이 로그 받은 후 commit. 타협책.

11.2 Sharding 의 고통

단일 DB 가 감당 안 될 때 sharding:

• Shard Key 선택의 함정: user_id 로 샤딩했는데 "모든 주문을 보여주는 페이지" 가 전체 샤드 쿼리 → 성능 붕괴.
• Rebalancing: 노드 추가 시 데이터 재분배. Consistent Hashing 으로 완화.
• Cross-shard Transaction: 2PC (Two-Phase Commit) 가 필요 — 성능과 가용성 양쪽에서 어렵다.

11.3 NewSQL — Spanner, CockroachDB, YugabyteDB

Google Spanner (2012) 가 "분산 SQL + Serializable + 전역 trans" 을 가능하게 만들면서 패러다임을 바꾸었다. 핵심:

• TrueTime (Atomic clock + GPS): 시간 불확실성 ε 를 노출. 트랜잭션 타임스탬프가 실제 시간과 일치하도록 "commit wait".
• Paxos/Raft 복제: 각 shard (Spanner 는 "tablet") 가 여러 노드에 복제.
• 글로벌 2PC: 여러 shard 걸친 트랜잭션도 serializable.

CockroachDB 는 같은 설계를 오픈 소스로 구현 (TrueTime 대신 HLC). PostgreSQL 호환 프로토콜이라 바로 쓸 수 있다.

11.4 OLTP vs OLAP — 같은 뿌리 다른 세계

같은 B-Tree 인덱스가 OLTP (트랜잭션) 와 OLAP (분석) 모두에 쓰일 것 같지만 실제로는 다르다:

• OLTP: 행 저장 (row-store). 한 행의 모든 컬럼을 같이 읽음. Postgres, MySQL, SQL Server.
• OLAP: 열 저장 (column-store). SELECT 에 쓰는 몇 개 컬럼만 읽음. 압축 잘 됨. BigQuery, ClickHouse, Snowflake, DuckDB.

HTAP (Hybrid): 두 저장소를 내부적으로 동기화 (TiDB, SingleStore).

12. 실무 체크리스트 — "DB 때문에 느리다" 를 말하기 전에

1. EXPLAIN ANALYZE 를 실제로 찍어봤는가?
2. 통계 가 최신인가? (ANALYZE table;)
3. 인덱스 가 WHERE/JOIN/ORDER BY 에 맞춰져 있는가?
4. Sequential scan 이 작은 테이블이 아닌 큰 테이블에서 발생하는가?
5. Loops × actual time 이 합리적인가?
6. Buffer cache hit ratio 가 99%+ 인가?
7. Long-running transaction 이 없는가? (pg_stat_activity 의 xact_start)
8. Autovacuum 이 도는가? (dead_tuple_count 확인)
9. 복제 지연 이 없는가? (pg_stat_replication.lag)
10. Lock 경합 이 없는가? (pg_locks + pg_stat_activity)
11. 쿼리 로그에서 비슷한 패턴 의 쿼리가 반복되지 않는가? (N+1)
12. Connection pool 이 설정돼 있는가? (pgBouncer, HikariCP)

13. 마치며 — SELECT 한 줄에 담긴 40년

SELECT name FROM users WHERE id = 42 라는 한 줄 SQL 뒤에는 :

• 1970 년 Codd 의 관계 모델.
• 1979 년 System R 의 SQL.
• 1988 년 Selinger 의 cost-based optimizer.
• 1992 년 ARIES 의 WAL 프로토콜.
• 1998 년 Postgres 의 MVCC 도입.
• 2012 년 Spanner 의 TrueTime.

...40년간 축적된 아이디어들이 한꺼번에 작동한다. 오늘 당신의 1ms 응답은 수십만 엔지니어-년의 합작이다.

다음 글에서는 DB 위에 올라가는 캐싱 계층 — Redis, Memcached 의 내부 자료 구조, LRU 의 확장, 분산 락, 그리고 "cache stampede" 와 같은 실전 함정들 — 을 파볼 예정이다. 스포일러: Redis 의 RDB/AOF 도 WAL 의 변형이고, Redis Cluster 의 gossip 프로토콜은 DB 의 replication 과 놀랍도록 닮았다.

참고 자료

• Hellerstein, Stonebraker, Hamilton — "Architecture of a Database System" (Foundations and Trends, 2007)
• Mohan et al — "ARIES: A Transaction Recovery Method" (ACM TODS, 1992)
• Selinger et al — "Access Path Selection in a Relational Database Management System" (SIGMOD, 1979)
• Cahill, Röhm, Fekete — "Serializable Isolation for Snapshot Databases" (SIGMOD, 2008)
• Corbett et al — "Spanner: Google's Globally-Distributed Database" (OSDI, 2012)
• PostgreSQL Documentation — Chapter 70: System Catalogs / Chapter 31: Reliability and the Write-Ahead Log
• MySQL Reference Manual — Chapter 14: The InnoDB Storage Engine
• Designing Data-Intensive Applications — Martin Kleppmann (O'Reilly, 2017)
• Database Internals — Alex Petrov (O'Reilly, 2019)