UPDATE 문 하나가 실행될 때 파일에서 실제로 일어나는 일은 순서가 정해져 있다.
커밋 시점에 데이터 파일은 아직 안 바뀌어 있다. 이것이 핵심이다.
Write-Ahead Logging 의 규칙은 하나다. 데이터 페이지를 디스크에 쓰기 전에, 그 변경을 설명하는 로그가 먼저 디스크에 있어야 한다.
이 규칙이 주는 것이 두 가지다.
내구성(Durability) — 커밋 직후 전원이 나가도 로그는 디스크에 있다. 재시작 시 로그를 되짚어 데이터 파일에 반영하면 커밋된 트랜잭션이 되살아난다. 이 과정을 redo 라 한다.
원자성(Atomicity) — 커밋하지 않은 트랜잭션의 변경이 체크포인트에 휩쓸려 데이터 파일에 이미 나가 있을 수 있다. 재시작 시 그 부분을 되돌린다. 이 과정이 undo 다.
성능 측면의 이득도 크다. 데이터 페이지 쓰기는 디스크 여기저기에 흩어진 임의 쓰기지만, 로그 쓰기는 파일 끝에 붙이는 순차 쓰기다. 커밋마다 순차 쓰기 한 번만 기다리면 되고, 흩어진 쓰기는 나중에 모아서 한다.
| 제품 | 로그 이름 |
|---|---|
| PostgreSQL | WAL (pg_wal/) |
| Oracle | Redo log + Undo tablespace |
| MySQL InnoDB | Redo log (ib_logfile*) + Undo log |
| SQL Server | Transaction log (.ldf) |
비정상 종료 후 기동하면 마지막 체크포인트 지점부터 로그를 읽는다.
체크포인트 간격이 길면 복구할 로그가 많아져 기동이 느려지고, 짧으면 평시 디스크 부하가 는다. 이 둘 사이의 타협이 체크포인트 설정이다.
갱신을 처리하는 방식이 제품마다 다르고, 이 차이가 운영 성격을 결정한다.
제자리 갱신 + 별도 undo (Oracle · MySQL InnoDB) — 페이지 안의 행을 직접 고치고, 이전 값은 undo 영역에 둔다. 오래된 스냅샷을 읽는 질의는 undo 를 따라가 과거 모습을 재구성한다. undo 영역을 오래 잡아 두는 긴 질의가 있으면 ORA-01555 snapshot too old 같은 문제가 난다.
새 버전 추가 (PostgreSQL) — 기존 행을 죽은 것으로 표시하고 새 행을 덧붙인다. undo 영역이 없는 대신 죽은 행이 쌓이고, 그것을 회수하는 VACUUM 이 필요하다. UPDATE 가 실질적으로 DELETE + INSERT 이므로 테이블이 부풀고, 인덱스도 함께 갱신된다.
DB 가 "그 행" 을 찾는 방법은 두 층으로 나뉜다.
저장 위치를 가리키는 내부 값이다.
| 제품 | 이름 | 내용 |
|---|---|---|
| Oracle | ROWID |
데이터 오브젝트 · 파일 · 블록 · 슬롯 번호 |
| PostgreSQL | ctid |
(페이지 번호, 페이지 안 순번) |
| MySQL InnoDB | 클러스터드 인덱스 키 | 기본 키. 없으면 내부 6바이트 row id |
물리 식별자는 안정적이지 않다. PostgreSQL 의 ctid 는 UPDATE 마다 바뀌고 VACUUM FULL 로도 바뀐다. Oracle 의 ROWID 도 행 이동이나 테이블 재구성으로 바뀐다. 애플리케이션이 보관해 두고 나중에 쓰는 값이 아니다.
쓸 자리는 있다. 중복 행을 지울 때처럼 논리 키로 구분할 수 없는 경우다.
-- PostgreSQL: 완전히 같은 행이 여러 개일 때 하나만 남긴다
DELETE FROM t
WHERE ctid NOT IN (SELECT min(ctid) FROM t GROUP BY col1, col2);
기본 키다. 애플리케이션이 다루는 것은 이것뿐이어야 한다. 값이 바뀌지 않고, 저장 구조가 바뀌어도 그대로다.
InnoDB 는 기본 키가 곧 물리 배치(클러스터드 인덱스)를 정하므로, 기본 키 선택이 성능에 직접 영향을 준다. 무작위 UUID 를 기본 키로 쓰면 삽입이 인덱스 전체에 흩어져 페이지 분할이 잦아진다. 순차 증가하는 값이나 시간 정렬 UUID(UUIDv7 등)를 쓴다.