DB 엔지니어링 — 내부 구조·Storage Engine·WAL

이 글은 데이터베이스 엔지니어링 마스터 노트 시리즈의 일곱 번째 편입니다. 1~6편이 사용자 관점이었다면, 이번엔 DB 안에서 무슨 일이 일어나는가 — 내부 구조.

WAL·Buffer Pool·Page를 알면 왜 빠르고 왜 ACID가 보장되는지 이해된다. 성능 튜닝의 깊이가 달라진다. 면접 단골 주제이기도.

DB 동작 큰 그림

Client (SQL)
   ↓
Parser (SQL → 트리)
   ↓
Optimizer (실행 계획)
   ↓
Executor
   ↓
Storage Engine (디스크 I/O)
   ↓
Disk (Pages)

각 층이 역할 분담. Storage Engine은 교체 가능 (MySQL = InnoDB·MyISAM 선택).

Page — 디스크 I/O 단위

DB는 고정 크기 블록(Page) 단위로 디스크 읽고 씀.

PostgreSQL: 8 KB (기본)
MySQL InnoDB: 16 KB
Oracle: 2 KB / 4 KB / 8 KB / 16 KB / 32 KB
SQL Server: 8 KB

여기서 시험 함정이 하나 있어요. DB는 1바이트 읽으려고 해도 페이지 1개(8KB)를 읽음. 메모리 단위와 비슷한 원리. 인덱스가 빠른 이유 = 적은 페이지로 답에 도달.

Page 구조 (PostgreSQL)

[Page Header (24 bytes)]
[Item Pointers (각 4 bytes)]    -- 행 위치 포인터
[... free space ...]
[Row 3 data]
[Row 2 data]
[Row 1 data]

행은 페이지 끝부터 채우고, 포인터는 앞부터 채움. 가운데 free space.

Heap, Tablespace, Database 계층

Database (e.g., myapp)
  └── Schema (public)
       └── Table (users)
            └── Heap File (8KB pages 모음)
                 └── Pages
                      └── Tuples (rows)

Tablespace = 물리적 위치 (디스크).

PostgreSQL:

-- 물리 위치 확인
SELECT pg_relation_filepath('users');
-- → base/16384/16385

-- pg_data 디렉토리 안 8KB 페이지들

Heap 저장 vs Index Organized Table

Heap 저장 (PostgreSQL 기본)

데이터가 삽입 순서로 heap에 저장. 인덱스는 별도 구조.

Table heap: 무작위 순서
Index: B+Tree → heap의 ctid 포인터

IOT (Index Organized Table)

테이블 자체가 클러스터드 인덱스 = PK 순서로 정렬.

• MySQL InnoDB = IOT (PK = clustered index)
• Oracle = 옵션
• SQL Server = 기본 (clustered index)
• PostgreSQL = 기본 X (CLUSTER 명령으로 일시 재정렬만)

여기서 정말 중요한 시험 함정 — MySQL InnoDB 모든 secondary index는 PK 통해 데이터 접근. Secondary index lookup = 1) secondary index → PK → 2) clustered index → 데이터. 두 단계. PostgreSQL은 직접 ctid 사용.

Buffer Pool — 메모리 캐시

디스크 → 메모리 (Buffer Pool). 자주 쓰는 페이지를 메모리에.

PostgreSQL: shared_buffers (기본 128MB, 권장 RAM 25%)
MySQL InnoDB: innodb_buffer_pool_size (권장 RAM 50~70%)

LRU (Least Recently Used)

오래 안 쓴 페이지 = 메모리에서 추출.

PostgreSQL = Clock-Sweep (LRU 변형, 효율 ↑). MySQL InnoDB = Midpoint LRU (스캔 공격 방어).

Dirty Page

1. 페이지 메모리 로드
2. UPDATE → 메모리만 수정 (디스크 X)
3. 페이지 = "dirty" 표시
4. Background process가 주기적으로 디스크에 flush

여기서 시험 함정이 하나 있어요. UPDATE는 즉시 디스크에 안 쓴다. 메모리만 수정 후 background flush. 그럼 장애 시 어떻게? → WAL.

WAL — Write-Ahead Logging

ACID의 D(Durability) 핵심.

동작

1. 트랜잭션 시작
2. 변경 사항을 WAL에 먼저 write (디스크)
3. WAL fsync (디스크 확정)
4. 메모리 페이지 수정 (Buffer Pool)
5. COMMIT 응답
6. 나중에 페이지를 디스크에 flush

장애 시:
- COMMIT 됐으면 WAL에 있음 → 재시동 시 WAL replay → 복구
- COMMIT 안 됐으면 WAL에 없음 → ROLLBACK

여기서 정말 중요한 시험 함정 — WAL이 먼저 디스크에 쓰여야 COMMIT 가능. 페이지 자체는 늦게 써도 됨. WAL이 작아 (변경분만) 빠른 sequential write 가능.

PostgreSQL — pg_wal

# WAL 디렉토리
ls /var/lib/postgresql/data/pg_wal/
# → 000000010000000000000001
# → 000000010000000000000002
# → ...

각 WAL 파일 = 16 MB (기본).

MySQL InnoDB — Redo Log

# ib_logfile0, ib_logfile1
ls /var/lib/mysql/

기본 2개 파일 순환.

여기서 정말 중요한 시험 함정 — WAL은 시스템 장애 복구의 근간. 사라지면 데이터 손실. 같은 디스크에 WAL과 데이터 저장 X — 별도 디스크가 안전.

Checkpoint — 메모리 → 디스크 동기화

Checkpoint:
  - 모든 dirty page를 디스크에 flush
  - WAL 사용 종료점 표시
  - 다음 재시동 시 이 지점부터 WAL replay

PostgreSQL checkpoint_timeout (기본 5분) 또는 WAL 양 임계값.

쿼리 실행 5단계

1. Parsing

SELECT name FROM users WHERE age > 30

→ AST(추상 구문 트리)

2. Rewriting

뷰 확장, 매크로 치환.

3. Planning (Optimization)

여러 실행 계획 생성, 비용 추정, 최적 선택.

계획 후보:
  A: Seq Scan + Filter
  B: Index Scan on age_idx + Bitmap Heap Scan
  C: ...

비용:
  A: 1000
  B: 50  ← 선택

4. Execution

선택한 계획대로 실행. Operator tree.

5. Returning

결과 클라이언트에 전송.

EXPLAIN ANALYZE SELECT name FROM users WHERE age > 30;
-- 단계별 시간 확인

여기서 시험 함정이 하나 있어요. Optimizer는 통계 기반. 통계 오래되면 잘못된 계획. ANALYZE 정기 실행 필요.

Row Store vs Column Store

Row Store (기본 — PostgreSQL·MySQL·Oracle)

디스크:
  [row1: id, name, age, ...]
  [row2: id, name, age, ...]

장점 — INSERT·UPDATE·전체 행 SELECT 빠름.

단점 — SELECT AVG(price) FROM orders 같은 컬럼 한 개만 분석 = 모든 컬럼 디스크 I/O 낭비.

Column Store (Redshift·BigQuery·ClickHouse·Cassandra)

디스크:
  [컬럼 id: 1,2,3,4,5,...]
  [컬럼 name: A,B,C,D,...]
  [컬럼 age: 25,30,35,...]

장점:

• 분석 쿼리 빠름 (필요 컬럼만 I/O)
• 압축 효율 ↑ (같은 타입 연속)

단점:

• INSERT 느림 (모든 컬럼 파일 수정)
• 단일 행 조회 비효율

여기서 정말 중요한 시험 함정 — OLTP = Row Store / OLAP = Column Store. 사용자 트랜잭션 = OLTP (PostgreSQL·MySQL). 데이터 분석 = OLAP (Redshift·BigQuery·Snowflake).

Indexing 내부 (2편 보충)

B+Tree 구조:
  Root Page (8KB)
    ├── 키 범위 0~1000 → Internal Page
    ├── 키 범위 1000~2000 → Internal Page
    ...
  Internal Pages
    ├── ...
  Leaf Pages
    ├── 실제 키 + 데이터 포인터 (ctid 또는 PK)
    └── 다음 leaf로 포인터 (range scan)

각 페이지 = 8KB. 트리 깊이 3~4 = 수억 행 처리.

동시성 제어 내부 (6편 보충)

PostgreSQL MVCC:

각 row의 시스템 컬럼:
  xmin (생성 트랜잭션)
  xmax (삭제 트랜잭션)
  ctid (물리 위치)

UPDATE = 새 버전 INSERT + 옛 xmax 표시
HOT (Heap-Only Tuples) = 같은 페이지 안 INSERT 시 인덱스 재생성 X (최적화)
VACUUM = xmax 표시된 옛 버전 회수

디스크 I/O 패턴

패턴	속도	사용처
Sequential Read	매우 빠름	Full Scan, Range Scan
Sequential Write	빠름	WAL
Random Read	느림 (HDD), 보통 (SSD)	Index Scan
Random Write	느림	UPDATE 분산

여기서 시험 함정이 하나 있어요. HDD에서 Random I/O는 100배 느림. 인덱스 스캔이 항상 빠른 건 아님 — 행 수 많으면 random I/O 비용 > seq scan. SSD는 차이 작음.

Storage Engine 종류

MySQL

Engine	특징
InnoDB (기본)	ACID, MVCC, row lock, FK
MyISAM	빠른 read, ACID X, table lock
Memory	인메모리

PostgreSQL

기본 1개 (heap-based). 확장 — TimescaleDB·Citus.

NewSQL

CockroachDB·TiDB·Spanner — 분산 + ACID.

시험 직전 한 번 더 — 자주 헷갈리는 함정 모음

여기까지가 7편의 핵심입니다. 시험 직전 또는 실무에서 헷갈릴 때 다시 펼쳐 볼 수 있게 압축 노트로 마무리할게요.

• DB 처리 흐름 — Parser → Optimizer → Executor → Storage
• Page = 디스크 I/O 단위 (PostgreSQL 8KB / InnoDB 16KB)
• 1바이트 읽어도 페이지 1개 읽음
• Page 구조 — 헤더·아이템 포인터·free·실제 데이터
• Heap = PostgreSQL 기본, 삽입 순서
• IOT (Clustered Index) = MySQL InnoDB 기본, PK 정렬
• InnoDB secondary index = PK 통해 2단계 lookup
• Buffer Pool = 메모리 캐시 (shared_buffers·innodb_buffer_pool_size)
• 권장 — PostgreSQL RAM 25% / InnoDB 50~70%
• LRU = 오래 안 쓴 페이지 추출
• PostgreSQL = Clock-Sweep / InnoDB = Midpoint LRU
• Dirty Page = 메모리에서 수정된, 디스크에 안 쓰인 페이지
• UPDATE는 즉시 디스크 안 씀
• WAL = Write-Ahead Logging, ACID Durability 핵심
• 변경 사항 WAL에 먼저 → fsync → 페이지 수정
• COMMIT = WAL fsync 완료 시점
• 장애 복구 = WAL replay
• WAL과 데이터 같은 디스크 X
• PostgreSQL pg_wal (16MB 파일) / InnoDB ib_logfile
• Checkpoint = dirty page 모두 디스크 flush
• 쿼리 5단계 — Parsing / Rewriting / Planning / Execution / Returning
• Optimizer = 통계 기반, ANALYZE 정기 실행 필수
• Row Store (OLTP) — PostgreSQL·MySQL
• Column Store (OLAP) — Redshift·BigQuery·ClickHouse·Cassandra
• Column = 분석 빠름·INSERT 느림·압축 ↑
• B+Tree 깊이 3~4 = 수억 행 처리
• HOT (Heap-Only Tuples) = 같은 페이지 UPDATE 최적화
• HDD Random I/O = Sequential 100배 느림
• SSD에서는 차이 작음
• MySQL InnoDB = ACID·MVCC·row lock·FK 표준
• NewSQL = CockroachDB·TiDB·Spanner (분산 + ACID)

시리즈 다른 편

• 1편 — ACID·트랜잭션
• 2편 — 인덱싱
• 3편 — 파티셔닝
• 4편 — 샤딩
• 5편 — 복제
• 6편 — 동시성 제어
• 7편 — 내부 구조 (현재 글)
• 8편 — 고급 주제

공식 문서: PostgreSQL Database File Layout 에서 더 깊이.

다음 글(8편, 마지막)에서는 고급 주제 — Connection Pooling, Distributed Transaction(2PC·Saga), Eventual Consistency, CAP·PACELC, Cache Invalidation, Read/Write 패턴까지 시리즈 마무리.