📚 지식 노트

같은 SQL, 다른 몸통

PostgreSQL, MySQL, SQLite는 모두 표와 SQL, 트랜잭션을 준다. 겉에서 보면 비슷하지만 안쪽은 꽤 다르다. 행을 어디에 어떤 순서로 두는지, 고친 행의 옛 판을 어디에 남기는지, 커밋을 무엇으로 지키는지, 연결 하나를 무엇으로 받는지가 엔진마다 갈린다. 이 노트는 그 차이를 따라간다. B+트리, WAL, MVCC 같은 공통 원리는 저장 엔진 노트와 동시성 노트에 있고, 여기서는 각 엔진이 그 원리를 어떻게 골라 조립했는지에 집중한다.

이 차이는 운영에서 바로 드러난다. PostgreSQL은 연결이 들어올 때마다 백엔드 프로세스를 하나 띄우므로 연결 하나가 무겁다. 그래서 수천 개 연결을 직접 받기보다 PgBouncer 같은 연결 풀러를 앞에 두는 것이 보통이다. MySQL은 스레드라 연결이 가볍지만, 모든 연결이 한 주소 공간을 나눠 쓴다. SQLite는 네트워크도 서버도 없이 앱과 같은 프로세스에서 돌아, 연결이라는 말 자체가 "파일을 연다"에 가깝다.

PostgreSQL: 힙에 쌓이는 튜플 판과 VACUUM

PostgreSQL의 테이블은 힙(heap)이다. 8KB 페이지에 행(튜플)을 들어온 순서대로 쌓을 뿐 정렬하지 않는다. 인덱스는 모두 따로 있는 보조 구조이고, 잎에 힙의 위치(ctid, 페이지 번호와 칸 번호)를 적어 둔다.

동시성은 MVCC로 푼다. 독특한 점은 옛 판을 별도 공간이 아니라 힙 안에 그대로 둔다는 것이다. 튜플마다 자신을 만든 트랜잭션 번호(xmin)와 지운 트랜잭션 번호(xmax)가 붙어 있고, UPDATE는 "옛 튜플에 xmax를 적고 새 튜플을 하나 더 넣는" DELETE + INSERT다. 읽는 트랜잭션은 시작할 때 찍은 스냅샷과 xmin·xmax를 비교해 자기에게 보이는 판을 고른다. 그래서 읽기는 쓰기를 기다리지 않고, 쓰기도 읽기를 기다리지 않는다.

대가는 쓰레기다. 아무 스냅샷도 볼 수 없게 된 옛 판(죽은 튜플)이 힙과 인덱스에 남아 자리를 차지한다. 이것을 치우는 일이 VACUUM이고, 평소에는 autovacuum 데몬이 바뀐 행이 많은 테이블을 골라 알아서 돌린다. VACUUM이 하는 일은 셋이다.

• 자리 회수: 죽은 튜플과 그것을 가리키는 인덱스 항목을 지우고, 빈자리를 여유 공간 지도(FSM)에 적어 다음 INSERT가 쓰게 한다. 파일 크기는 보통 줄지 않는다(VACUUM FULL은 테이블을 새로 써서 줄이지만 그동안 테이블을 잠근다).
• 가시성 지도 갱신: 페이지의 모든 튜플이 모든 트랜잭션에 보이면 가시성 지도(visibility map)에 표시한다. 인덱스 전용 스캔은 이 표시가 있는 페이지의 힙을 건너뛰고, 다음 VACUUM도 그 페이지를 건너뛴다.
• 얼리기(freeze): 트랜잭션 번호는 32비트라 약 40억에서 한 바퀴 돈다. 충분히 오래된 튜플을 "모두에게 보이는 과거"로 표시해 두지 않으면 번호가 돌아왔을 때 과거 행이 미래 행처럼 보인다. 이 작업을 오래 미루면 결국 데이터베이스가 쓰기를 멈추고 VACUUM을 요구한다.

같은 페이지 안에 새 판을 넣을 자리가 있고 인덱스 걸린 열이 바뀌지 않았다면, 인덱스를 고치지 않고 페이지 안에서 옛 판이 새 판을 가리키게만 하는 HOT 업데이트가 일어난다. UPDATE가 잦은 테이블에 fillfactor로 페이지 여유를 남겨 두는 이유다.

PostgreSQL: WAL, 스트리밍 복제, 확장

커밋의 내구성은 WAL(write-ahead log)이 맡는다. 바뀐 페이지를 디스크에 바로 쓰지 않고, 변경 기록만 순서대로 WAL에 붙여 커밋 때 그 WAL을 fsync한다. 페이지 자체는 shared buffers에 더티 상태로 남았다가 checkpointer가 모아서 쓴다. 꺼졌다 켜지면 마지막 체크포인트부터 WAL을 다시 재생해 커밋된 상태로 돌아온다.

이 WAL을 네트워크로 흘려보내면 그대로 복제가 된다. 스트리밍 복제는 standby가 주 서버의 WAL을 받아 재생하는 물리 복제이고, standby는 재생하는 동안에도 읽기 전용 쿼리를 받는다(hot standby). 기본은 비동기라 주 서버가 갑자기 죽으면 아직 보내지 못한 마지막 커밋이 사라질 수 있고, synchronous_standby_names를 설정하면 standby가 받았다고 답할 때까지 커밋을 기다린다. 테이블 단위로 골라 다른 버전·다른 스키마로 보내는 논리 복제도 있다. 주 서버가 죽었을 때 standby를 올리는 일은 PostgreSQL 자체가 아니라 Patroni 같은 바깥 도구가 맡는다.

PostgreSQL을 많이 고르는 이유 하나는 확장(extension)이다. 새 자료형, 연산자, 인덱스 방식, 백그라운드 작업까지 C로 짜 넣고 CREATE EXTENSION 한 줄로 켠다. 지리 정보의 PostGIS, 벡터 검색의 pgvector, 시계열의 TimescaleDB가 모두 확장이다. 데이터베이스를 하나 더 늘리는 대신 PostgreSQL 안에서 해결하는 길이 열린다.

MySQL InnoDB: 테이블이 곧 기본 키 B+트리

MySQL은 저장 엔진을 갈아 끼우는 구조이고, 지금 기본이자 사실상 유일한 선택은 InnoDB다. InnoDB의 테이블은 힙이 아니라 클러스터드 인덱스다. 기본 키 B+트리의 잎 페이지에 행 전체가 기본 키 순서로 들어 있다. 기본 키를 정하지 않으면 NOT NULL인 첫 UNIQUE 인덱스를, 그것도 없으면 숨은 행 번호를 기본 키로 쓴다.

이 구조에서 실무 규칙 몇 가지가 나온다. 기본 키 범위 조회는 잎을 옆으로 훑기만 하면 되어 아주 빠르다. 보조 인덱스는 잎마다 기본 키를 품으므로 기본 키가 길면 모든 인덱스가 같이 커진다. 무작위 UUID(v4)를 기본 키로 쓰면 새 행이 트리 아무 곳에나 끼어들어 페이지 분할이 잦아지므로, 늘어나는 정수나 시간 순서를 담은 UUIDv7이 낫다. 필요한 열이 모두 보조 인덱스 안에 있으면(커버링 인덱스) 두 번째 탐색을 건너뛴다.

MySQL InnoDB: undo·redo·binlog와 복제

InnoDB도 MVCC를 쓰지만 옛 판을 두는 곳이 PostgreSQL과 반대다. 행은 제자리에서 고치고, 고치기 전 값을 undo 로그에 남긴다. 오래된 스냅샷으로 읽는 트랜잭션은 행에서 undo 사슬을 거꾸로 따라가 옛 판을 다시 만든다. 테이블에 쓰레기가 쌓이지 않는 대신, 아주 긴 트랜잭션이 열려 있으면 undo를 지우지 못해(purge 지연) undo 공간이 불어난다.

로그는 두 종류다. redo 로그는 InnoDB의 WAL로, 충돌 뒤 페이지를 되살리는 데 쓴다. binlog는 그 위 MySQL 서버 층의 기록으로, 복제와 특정 시점 복구에 쓴다. 둘이 어긋나면 "주 서버에는 있는데 복제본에는 없는" 행이 생기므로, 커밋 때 내부 2단계 커밋으로 순서를 맞춘다. 데이터 페이지는 16KB인데 디스크는 보통 4KB 단위로 쓰므로, 쓰는 도중 꺼지면 페이지가 반만 바뀔 수 있다. 그래서 페이지를 먼저 doublewrite 버퍼에 쓰고 나서 제자리에 쓴다.

복제는 복제본이 binlog를 받아(relay log) 재생하는 방식이다. 기본 binlog 형식은 바뀐 행 자체를 적는 ROW이고, GTID로 트랜잭션마다 전역 번호를 붙여 복제본을 바꿔 달기 쉽게 한다. 기본은 비동기이며, 반동기(semi-synchronous) 복제나 여러 서버가 합의로 커밋을 정하는 Group Replication을 고를 수 있다. 격리 수준 기본값도 다르다. InnoDB는 REPEATABLE READ이고 범위 조건에 갭 락을 걸어 팬텀을 막는 반면, PostgreSQL은 READ COMMITTED가 기본이다.

SQLite: 파일 하나에 든 데이터베이스

SQLite는 서버가 아니라 C 라이브러리다. 데이터베이스 전체가 파일 하나이고, 그 안에서 테이블과 인덱스가 각각 B-트리다. 파일은 고정 크기 페이지(기본 4096바이트)로 나뉘고, 테이블 B-트리는 행 번호(rowid)를 키로 잎에 행을 담는다. 파일을 복사하면 데이터베이스가 통째로 옮겨지고, 파일 형식은 하위 호환을 약속한다.

원자적 커밋은 두 방식 중 하나로 지킨다. 기본인 롤백 저널은 고칠 페이지의 원본을 -journal 파일에 먼저 떠 두고 DB 파일을 고친 뒤, 저널을 지우는 것으로 커밋한다. 도중에 꺼지면 다음에 열 때 저널의 원본으로 되돌린다. WAL 모드는 거꾸로 DB 파일을 두고 바뀐 페이지를 -wal 파일 끝에 붙이며, 읽는 쪽은 DB 파일과 -wal을 함께 본다. 그래서 쓰는 사람 하나와 읽는 사람 여럿이 서로 막지 않는다. 쓰기는 어느 모드든 한 번에 하나뿐이다.

SQLite는 가장 널리 배포된 데이터베이스 엔진이다. 스마트폰 운영체제, 웹 브라우저, 데스크톱 앱의 설정과 기록이 대부분 SQLite 파일이다. 공식 문서의 쓰기 알맞은 곳 설명대로 SQLite는 클라이언트·서버 데이터베이스와 겨루는 것이 아니라 fopen()과 겨룬다. 앱 안의 저장소, 파일 형식, 도구 사이의 데이터 교환, 쓰기가 많지 않은 웹사이트에 잘 맞고, 여러 기계가 네트워크로 동시에 쓰는 일에는 맞지 않는다.

고르는 법

새 서비스라면 대개 PostgreSQL에서 출발하고, 이미 MySQL 운영 경험과 도구가 갖춰진 조직이라면 MySQL도 충분히 좋은 선택이다. 데이터가 한 기계 안에만 있으면 되는 앱이라면 서버를 띄우기 전에 SQLite부터 따져 본다. 어느 쪽이든 한 대로 감당하지 못할 만큼 커지면 복제·샤딩 노트의 문제로 넘어간다.