SQLite Internals Deep Dive — B-tree, WAL, VFS, Virtual Table, Query Planner 완전 정복 (2025)

TL;DR

• SQLite는 전 세계에서 가장 많이 배포된 소프트웨어. 매일 사용되는 인스턴스가 1조 개 이상. 아이폰, 안드로이드, Chrome, Firefox, Mac, Windows에 내장.
• 설계: D. Richard Hipp의 2000년 프로젝트. "서버가 필요 없는 작은 SQL 엔진" → 임베디드 DB의 표준.
• Architecture: 단일 C 파일 amalgamation (~250,000줄). 5개 레이어 — Interface / Tokenizer / Parser / Code Generator / Virtual Machine / B-tree / Pager / OS Interface (VFS).
• Storage: B+tree 기반, 4KB 페이지 기본. 하나의 DB 파일 안에 여러 B-tree (테이블 + 인덱스).
• Transaction: Rollback Journal(전통) 또는 WAL(2010+). WAL이 리더-라이터 동시성을 극적으로 개선.
• VFS: OS 파일시스템 추상. 메모리, 암호화, HTTP 등 커스텀 VFS로 확장 가능.
• Virtual Table: "이 외부 데이터 소스를 SQL로 질의 가능"하게 하는 확장 — CSV, FTS5, R-tree, JSON1.
• Query Planner: 비용 기반. 인덱스 선택, join 순서 결정. Bytecode VM(VDBE)에 컴파일.
• 신뢰성: 150K 줄 코드에 92M 줄의 테스트. FAA, Boeing, 의료 기기에서도 사용.
• 최신 기능: JSONB (3.45+), math 함수, RETURNING, STRICT 테이블.

1. SQLite의 기원

1.1 2000년 — 선박 위의 프로그래머

D. Richard Hipp은 1998년 General Dynamics에서 US Navy 미사일 구축함용 소프트웨어를 개발 중이었다. Oracle과 PostgreSQL이 필요했지만 선박 내 데이터베이스 서버 관리는 비현실적. 서버 크래시 → 하루 종일 수리 → 훈련 중단.

Hipp의 아이디어: "서버 없는 DB". 라이브러리로 앱에 링크되고 파일 하나에 저장. 설치/관리 불필요.

기존 솔루션:

• Berkeley DB: KV 저장소, SQL 없음.
• GDBM: 더 단순, SQL 없음.
• 순수 SQL + 임베디드 = 없음.

2000년 5월, Hipp가 SQLite 1.0을 공개. 단순한 C 코드, 퍼블릭 도메인 (저작권 포기). "Because smart engineers will just need a simple database that works".

1.2 초기 성장

초기 수년 동안 사용자가 느리게 늘었다. 전환점:

• 2002 MacOS X: Apple이 Mail.app, Safari에 SQLite 내장.
• 2005 Firefox 1.5: 북마크, 방문 기록을 SQLite로.
• 2007 iPhone: 전 모바일 앱이 SQLite 사용.
• 2008 Android: 안드로이드 framework API가 SQLite 기반.

폭발적 성장. 2010년경 "모든 모바일 앱이 SQLite에 의존"하는 상황이 됐다.

1.3 2025년 현황

• 매일 사용: 추정 1조 개 이상의 SQLite DB. 세상에서 가장 많이 배포.
• 설치처: iPhone, Android, Windows, macOS, Chrome, Firefox, Edge, Skype, Dropbox, Adobe, PHP, Python, Ruby, ...
• 코드 크기: ~150,000 줄 (amalgamation 기준).
• 테스트: 92M+ 줄 (정식 regression + soak + fuzz).
• 개발팀: D. Richard Hipp + 소수 핵심. "Hwaci" (Hipp, Wyrick & Company).
• 라이선스: Public Domain. 완전 자유.

1.4 왜 SQLite가 승리했는가

단순한 이유:

1. 파일 하나: 복사/백업/이동이 쉽다.
2. 서버 불필요: 설치 제로.
3. 작다: 바이너리 ~500KB.
4. 안정적: 비정상 종료에 강하다.
5. 빠르다: 파일시스템 I/O에 거의 근접.
6. SQL 완전: 대부분 SQL-92 지원.

Hipp의 고집: "SQLite is small, fast, reliable. Choose any three." 세 가지 모두 포기하지 않는다.

2. 아키텍처 개요

2.1 5+ 레이어

SQLite는 잘 정의된 레이어로 구성:

┌────────────────────────────────┐
│      Interface (C API)         │
├────────────────────────────────┤
│   SQL Command Processor        │
│   ┌──────────────────────┐    │
│   │ Tokenizer            │    │
│   ├──────────────────────┤    │
│   │ Parser               │    │
│   ├──────────────────────┤    │
│   │ Code Generator       │    │
│   └──────────────────────┘    │
├────────────────────────────────┤
│   Virtual Machine (VDBE)       │
├────────────────────────────────┤
│   B-tree                       │
├────────────────────────────────┤
│   Pager                        │
├────────────────────────────────┤
│   OS Interface (VFS)           │
└────────────────────────────────┘

각 레이어는 위에만 의존. 하위 레이어 교체 가능 (특히 VFS).

2.2 실행 흐름

SQL 문이 실행되면:

1. Tokenizer: SELECT name FROM users WHERE id = 1 → 토큰 [SELECT, name, FROM, users, WHERE, id, =, 1].
2. Parser: 토큰 → AST.
3. Code Generator: AST → VDBE bytecode.
4. VDBE: bytecode 실행 → B-tree 레이어 호출.
5. B-tree: 페이지 조회 → Pager 호출.
6. Pager: 캐시 확인 → 없으면 VFS로 디스크 I/O.
7. VFS: OS 파일시스템 API (read/write/fsync).

이 분리 덕분에 각 레이어를 독립적으로 테스트, 교체 가능.

2.3 Amalgamation

SQLite 소스 코드는 100+ .c 파일로 개발되지만, 배포 시 하나의 거대 파일 sqlite3.c로 합쳐진다. amalgamation.

# 빌드
gcc sqlite3.c -o libsqlite3.a -c

단일 파일의 장점:

• 컴파일러 최적화: 전체를 한 번에 보므로 인라이닝 극대화.
• 배포 단순: 파일 하나 복사.
• 포팅: 어떤 시스템이든 컴파일.

단점: 디버깅 시 줄 번호 거대. 하지만 SQLite 자체 개발자에게만 문제.

3. B-tree 저장

3.1 파일 레이아웃

SQLite DB 파일은 고정 크기 페이지로 나뉜다. 기본 4KB (configurable 512B - 65KB).

File header (100 bytes) | Page 1 | Page 2 | Page 3 | ... | Page N

• Page 1: 특별. 파일 헤더 포함 + schema B-tree의 루트.
• 이후 페이지: 데이터 또는 인덱스.

3.2 파일 헤더

첫 100 바이트:

Offset  Size  Description
0       16    "SQLite format 3\000"
16      2     Page size (2의 거듭제곱, 512-65536)
18      1     File format write version (1=legacy, 2=WAL)
19      1     File format read version
20      1     Reserved space per page
21      1     Max embedded payload fraction
24      4     File change counter
28      4     Total number of pages
32      4     First freelist page
36      4     Number of freelist pages
40      16    Schema cookie, ...
...

파일 하나가 전체 DB. 헤더로 자기 설명.

3.3 테이블 = B+tree

각 테이블은 B+tree 하나.

• Internal node: 자식 페이지 포인터 + 키.
• Leaf node: 실제 행 데이터.

B+tree의 특성:

• 균형 잡힌: 트리 높이가 로그 복잡도.
• 정렬된: 키 기준 순차 접근.
• Page-sized: 각 노드가 한 페이지 = 디스크 I/O 1회.

3.4 Row ID

SQLite의 핵심 특징: **각 행이 64-bit integer rowid**를 가진다.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INTEGER PRIMARY KEY는 특별히 rowid의 alias. 다른 column을 PK로 쓰면 rowid는 별도.

테이블 B+tree의 키는 rowid. Leaf node는 rowid → row_data.

3.5 WITHOUT ROWID

Rowid가 없는 테이블:

CREATE TABLE users (email TEXT PRIMARY KEY, name TEXT) WITHOUT ROWID;

• 키 = email 자체.
• Non-integer PK에 효율적.
• 저장 공간 약간 절약.

3.6 Index = 또 다른 B-tree

인덱스도 B+tree. 키 = indexed column + rowid.

CREATE INDEX idx_name ON users(name);

이 인덱스의 B-tree:

Key: (name, rowid) → rowid

쿼리 WHERE name = 'Alice':

1. Index B-tree 조회: ('Alice', *) → rowid 리스트.
2. Main table B-tree에서 각 rowid 조회 → 실제 행.

2단계 조회 (index scan + table lookup).

3.7 Covering Index

인덱스만으로 쿼리 답이 충족되면 table lookup skip:

CREATE INDEX idx_name_age ON users(name, age);

SELECT name, age FROM users WHERE name = 'Alice';
-- age까지 인덱스에 있음 → main table 조회 불필요

Covering index. 빠름.

3.8 Varint

SQLite는 정수를 저장할 때 varint(variable-length integer)를 쓴다. 작은 수는 1 바이트, 큰 수는 더.

0-127:         1 byte
128-16K:       2 bytes
...
Max 8 bytes for 64-bit int

Rowid, 페이지 포인터, record header 등에 활용. 공간 절약.

3.9 Record Format

한 행의 인코딩:

Header size (varint)
Serial types (varint 리스트)
Body (serial types 순서대로)

Serial type:

• 0 = NULL
• 1 = 1-byte integer
• 2 = 2-byte integer
• ...
• 5 = 6-byte integer
• 6 = 8-byte integer
• 7 = IEEE 754 float
• 8, 9 = 상수 0, 1
• 10, 11 = reserved
• N≥12 and even = BLOB, (N-12)/2 bytes
• N≥13 and odd = TEXT, (N-13)/2 bytes

Type affinity: SQLite의 독특한 특징 — 컬럼 타입이 힌트일 뿐 강제 아님. 실제 저장은 serial type 기반.

4. Pager와 Page Cache

4.1 Pager의 역할

B-tree 레이어는 "페이지 번호"로 요청. Pager는:

1. 페이지 캐시 확인 (메모리).
2. 없으면 디스크에서 읽기 (VFS 통해).
3. 캐시에 저장.
4. 페이지 반환.

Cache Manager 역할 + transaction 관리.

4.2 Page Cache

기본 2000 페이지 캐시 (약 8MB). 설정 가능:

PRAGMA cache_size = -20000;  -- 20 MB

음수는 KB, 양수는 페이지 수.

LRU처럼 동작하지만 실제로는 단순 해시 테이블 + 사용 카운터.

4.3 Dirty Pages

수정된 페이지는 dirty 표시. 디스크에 아직 기록 안 됨.

커밋 시:

1. 모든 dirty 페이지를 디스크에 쓰기.
2. fsync 호출 (디스크에 강제 flush).
3. 커밋 완료.

fsync는 비싸다 (수 ms). 대량 insert는 트랜잭션으로 묶어서 한 번의 fsync로.

BEGIN;
INSERT INTO ...
INSERT INTO ...
...
COMMIT;  -- 이때 fsync 한 번

트랜잭션 없이 각 INSERT가 autocommit → 각자 fsync → 수백 배 느림.

5. Rollback Journal — 전통적 트랜잭션

5.1 ACID 보장

SQLite는 처음부터 ACID를 보장. 비정상 종료에도 일관성 유지.

5.2 Rollback Journal

2010 이전 기본 모드.

흐름:

1. 페이지 변경 전: 원본 페이지를 journal 파일(.db-journal)에 저장.
2. 메모리에서 수정.
3. 커밋 시: 수정된 페이지를 메인 파일에 쓰기 → fsync → journal 파일 삭제.
4. 롤백 시: journal의 원본으로 메인 파일 복원.

5.3 복구

비정상 종료 후 재시작 시 SQLite가:

1. .db-journal 파일 존재 확인.
2. 있으면 "트랜잭션 중 죽음" 판단.
3. Journal의 원본을 메인 파일로 복원.
4. Journal 삭제.

"crash-safe" 보장.

5.4 단점

Writer가 readers를 블록한다:

• 쓰기가 journal을 만들고, 메인 파일을 수정하기 시작하면 readers가 볼 수 없는 상태.
• 그래서 SQLite는 읽기 중엔 shared lock, 쓰기는 exclusive lock을 사용.
• 한 번에 하나의 writer 또는 여러 readers (writer XOR readers).

2010년 이전엔 모바일 앱이 UI + 백그라운드 sync → 서로 락 경쟁.

6. WAL Mode — 2010년의 혁명

6.1 왜 WAL

Write-Ahead Logging은 잘 알려진 기법 (PostgreSQL, Oracle도 사용). SQLite 3.7 (2010)에 도입.

핵심: 변경을 메인 파일에 바로 쓰지 않고 별도 WAL 파일에 추가. 나중에 배치로 메인 파일에 반영.

6.2 동작

DB 파일: users.db
         users.db-wal    ← 변경 기록
         users.db-shm    ← shared memory (인덱스)

쓰기:

1. 변경된 페이지를 users.db-wal에 append.
2. fsync (WAL 파일만).
3. 메인 파일은 건드리지 않음.
4. Commit 완료.

읽기:

1. 원하는 페이지가 wal에 있는지 확인.
2. 있으면 WAL에서 읽기 (가장 최신 버전).
3. 없으면 메인 파일에서.

Checkpoint:

• 주기적으로 WAL을 메인 파일에 반영.
• 기본 1000 페이지 쌓이면 자동.

6.3 동시성 이득

Writer가 WAL에만 쓰고 메인 파일을 건드리지 않으므로:

• Readers는 메인 파일을 계속 사용 (또는 WAL의 특정 시점 snapshot).
• Writer와 readers가 동시 작동.

실질적 MVCC (Multi-Version Concurrency Control). 각 reader는 자기 읽기 시점의 snapshot을 본다.

6.4 활성화

PRAGMA journal_mode = WAL;

파일에 기록되므로 한 번만 하면 됨.

6.5 Checkpoint 전략

Passive (기본): 다음 writer가 체크포인트 수행. Writer가 살짝 느려짐.

Full: 모든 WAL 내용을 flush.

Restart: WAL을 재사용 가능하게 정리.

Truncate: WAL을 0 바이트로 축소.

6.6 WAL의 한계

• shm 파일 필요: 프로세스 간 공유 메모리. 일부 환경에서 문제 (예: nfs).
• 장기 트랜잭션이 WAL 크기 폭발: 오래된 reader가 있으면 체크포인트 못 함.
• 복제 어려움: WAL 파일만 백업하면 불완전.

대부분 경우 WAL이 rollback journal보다 훨씬 낫다.

7. VFS — Virtual Filesystem

7.1 추상화

SQLite는 OS에 직접 의존하지 않는다. VFS 레이어가 파일 I/O를 추상화.

typedef struct sqlite3_vfs sqlite3_vfs;
struct sqlite3_vfs {
    int (*xOpen)(sqlite3_vfs*, const char *zName, sqlite3_file*, int flags, int *pOutFlags);
    int (*xDelete)(sqlite3_vfs*, const char *zName, int syncDir);
    int (*xAccess)(sqlite3_vfs*, const char *zName, int flags, int *pResOut);
    int (*xFullPathname)(sqlite3_vfs*, const char *zName, int nOut, char *zOut);
    // ... 수십 개 함수
};

기본 VFS:

• unix: Linux/Mac.
• win32: Windows.

7.2 커스텀 VFS 사용 사례

In-memory DB:

:memory:

VFS가 RAM만 사용. 디스크 I/O 없음.

암호화 VFS (SEE, SQLCipher): 파일 I/O 시 모든 바이트 암호화/해독. 투명하게 동작.

HTTP VFS: HTTP range request로 원격 DB 읽기. 클라이언트가 일부만 다운로드.

Cloud VFS: S3, GCS 저장소. 페이지 단위 HTTP GET.

Android contentProvider: 안드로이드가 자체 VFS로 DB 접근 제어.

7.3 WASM VFS

브라우저에서 SQLite 실행 (2020+):

• sqlite3-wasm: 공식. IndexedDB 또는 OPFS(Origin Private File System)를 VFS로.
• absurd-sql: IndexedDB 기반 VFS.
• sql.js: 순수 JS, 파일시스템 없음 (in-memory).

브라우저 DB가 가능해지면서 클라이언트 사이드 앱이 SQLite 기반 local-first로 진화.

8. VDBE — Virtual Machine

SQLite는 SQL을 직접 실행하지 않는다. 먼저 bytecode로 컴파일 → VDBE(Virtual Database Engine)가 실행.

8.1 Bytecode 예제

SELECT name FROM users WHERE id = 1;

addr  opcode          p1  p2  p3  p4          p5  comment
----  -------------   --  --  --  --          --  -------
0     Init            0   7   0               0   Start at 7
1     OpenRead        0   2   0   2           0   root=2 iDb=0; users
2     RowId           0   1   0               0   r[1]=rowid
3     IdxGE           1   6   2   r[1]        1   key=r[1]
4     Column          0   1   3               0   r[3]=users.name
5     ResultRow       3   1   0               0   output=r[3..3]
6     Halt            0   0   0               0
7     Transaction     0   0   1   0           1
8     Goto            0   1   0               0

VDBE는 이 프로그램을 해석하며 실행.

8.2 왜 Bytecode

• SQL 파싱을 한 번만: 재사용 (prepared statement).
• 최적화 분리: Query planner가 bytecode 생성, VDBE는 단순 실행.
• 디버깅 쉬움: EXPLAIN으로 bytecode 검사.

8.3 EXPLAIN

EXPLAIN SELECT name FROM users WHERE id = 1;

바이트코드 리스트 반환. "왜 이 쿼리가 이렇게 실행되는지" 파악 가능.

EXPLAIN QUERY PLAN SELECT name FROM users WHERE id = 1;

더 높은 수준의 플랜 (테이블 스캔 vs 인덱스 사용 등).

8.4 Register-Based

VDBE는 레지스터 기반 VM. Python의 스택 기반과 다름. 각 명령이 특정 레지스터를 읽고 쓴다.

p1, p2, p3 같은 operand가 레지스터/페이지/문자열 등을 가리킴.

대략 150+ opcode. SQL의 모든 구문을 커버.

9. Query Planner

9.1 역할

SELECT * FROM users WHERE name = 'Alice' AND age > 30을 어떻게 실행?

• Option A: 전체 users 스캔, 각 행 체크.
• Option B: idx_name로 Alice 찾기, age 필터.
• Option C: idx_age로 age>30 찾기, name 필터.

Query planner가 가장 싼 것을 선택.

9.2 비용 모델

SQLite는 간단한 cost 모델:

• 각 operation에 weight.
• 인덱스 스캔 vs 테이블 스캔 비교.
• Selectivity 추정 (ANALYZE로 개선).

ANALYZE:

ANALYZE users;

통계 수집. Query planner가 더 나은 결정.

9.3 Join 순서

N개 테이블 join → N!개 순서 가능. 모두 시도하지 않고 heuristic.

• 가장 작은 테이블을 먼저.
• 인덱스 있는 조건 먼저.
• 카디널리티 추정 기반.

9.4 Index Selection

같은 쿼리에 여러 인덱스 가능:

CREATE INDEX idx1 ON users(name);
CREATE INDEX idx2 ON users(age);
CREATE INDEX idx3 ON users(name, age);

WHERE name = 'Alice' AND age > 30: idx3가 최적 (covering).

Query planner가 결정.

9.5 Partial Index

CREATE INDEX idx_active_users ON users(name) WHERE active = 1;

active = 1 행만 인덱싱 → 인덱스 작음, 빠름. 특정 WHERE 패턴에 최적.

9.6 Expression Index

CREATE INDEX idx_lower_name ON users(LOWER(name));

SELECT * FROM users WHERE LOWER(name) = 'alice';
-- ↑ 인덱스 사용 가능

표현식 자체를 인덱싱.

10. Virtual Table

"실제 파일에 저장되지 않는 가상 테이블". SQLite의 가장 강력한 확장 메커니즘.

10.1 사용 사례

Virtual table이 가능하게 하는 것:

• Full-text search (FTS5).
• R-tree 공간 인덱스.
• CSV 파일 직접 질의.
• 원격 DB 질의.
• 프로세스 정보 (/proc).
• 커스텀 데이터 소스 전부.

10.2 Module

Virtual table은 module로 구현:

typedef struct sqlite3_module sqlite3_module;
struct sqlite3_module {
    int iVersion;
    int (*xCreate)(sqlite3*, void *pAux, int argc, const char *const*argv,
                   sqlite3_vtab **ppVTab, char**);
    int (*xConnect)(sqlite3*, void *pAux, int argc, const char *const*argv,
                    sqlite3_vtab **ppVTab, char**);
    int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
    int (*xDisconnect)(sqlite3_vtab *pVTab);
    int (*xDestroy)(sqlite3_vtab *pVTab);
    int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
    int (*xClose)(sqlite3_vtab_cursor*);
    int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                   int argc, sqlite3_value **argv);
    int (*xNext)(sqlite3_vtab_cursor*);
    int (*xEof)(sqlite3_vtab_cursor*);
    int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
    int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
    ...
};

구현자는 "행을 어떻게 얻나"를 콜백으로 제공.

10.3 FTS5 — Full Text Search

Virtual table로 구현된 전문 검색 엔진.

CREATE VIRTUAL TABLE articles USING fts5(title, body);

INSERT INTO articles VALUES ('SQLite Tutorial', 'Learn SQLite...');
INSERT INTO articles VALUES ('PostgreSQL Guide', 'Master Postgres...');

SELECT * FROM articles WHERE articles MATCH 'sqlite OR postgres';

내부:

• Inverted index 구축 (Lucene 유사).
• Trigram 또는 full word 매칭.
• BM25 랭킹.
• Phrase query, NEAR 등 지원.

임베디드 검색 엔진이 DB 안에. 별도 Elasticsearch 불필요.

10.4 R-tree — 공간 인덱스

지리적 검색:

CREATE VIRTUAL TABLE restaurants_idx USING rtree(
    id,
    minLat, maxLat,
    minLng, maxLng
);

INSERT INTO restaurants_idx VALUES (1, 37.5, 37.5, 127.0, 127.0);

SELECT * FROM restaurants_idx
WHERE minLat >= 37.0 AND maxLat <= 38.0
  AND minLng >= 126.0 AND maxLng <= 128.0;

PostGIS의 축소판. 모바일 앱에서 지도 검색.

10.5 JSON1 / JSONB

SQLite 3.45+ (2024) JSONB 네이티브 지원.

CREATE TABLE events (id INTEGER, data JSONB);

INSERT INTO events VALUES (1, jsonb('{"name": "Alice", "age": 30}'));

SELECT json_extract(data, '$.name') FROM events;
SELECT * FROM events WHERE json_extract(data, '$.age') > 25;

PostgreSQL JSONB 스타일. 빠른 경로 조회.

11. Transactions와 Concurrency

11.1 BEGIN / COMMIT / ROLLBACK

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

고전적 ACID 트랜잭션.

11.2 Transaction Types

DEFERRED (기본): BEGIN은 실제로 락을 걸지 않음. 첫 SELECT에서 SHARED, 첫 수정에서 RESERVED.

IMMEDIATE: BEGIN IMMEDIATE 즉시 RESERVED lock. 다른 writer 없음을 보장.

EXCLUSIVE: BEGIN EXCLUSIVE 즉시 EXCLUSIVE. 다른 프로세스 일체 접근 불가.

11.3 Lock 진화 (Rollback Journal 모드)

UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE

• UNLOCKED: 아무도 안 쓰고 있음.
• SHARED: 읽기 가능. 여러 SHARED 가능.
• RESERVED: "내가 쓸 것이지만 아직 쓰지 않았음". 여러 SHARED와 공존.
• PENDING: "나는 쓸 것이고 새 SHARED를 허용하지 않음". 기존 SHARED 마무리 대기.
• EXCLUSIVE: 실제 쓰기. 다른 모두 차단.

11.4 WAL 모드에서

위의 락 모델이 단순해진다:

• Readers: snapshot을 기준으로 lock-free.
• Writer: 자기들끼리는 mutex 필요 (한 번에 하나만).
• Reader ↔ writer: 거의 충돌 없음.

Much better concurrency.

11.5 Savepoint

Nested transactions:

BEGIN;
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO sp1;
UPDATE ...;
COMMIT;

ROLLBACK TO는 savepoint 이후만 되돌림.

12. 신뢰성 — 광적인 테스트

12.1 숫자

• SQLite 소스: ~150,000 lines.
• 테스트 코드: ~92,000,000 lines.
• 비율: 약 600x. 코드 1줄당 테스트 600줄.

이것이 SQLite가 Boeing 787, 의료 기기, 원자력 시설에서 쓰이는 이유.

12.2 테스트 종류

TCL Tests: 기본 단위/통합 테스트.

TH3 (Test Harness 3): 전문 상용 테스트 (Hwaci가 항공 인증을 위해 개발). 100% MC/DC 커버리지.

MC/DC (Modified Condition/Decision Coverage): DO-178B 항공 인증 표준. 모든 조건의 각 결과를 개별 검증. 비행기 소프트웨어 수준.

SQLite Logic Test: SQL 표준 준수 체크.

Fuzz Testing: 연중무휴 fuzz로 버그 찾기. Google의 OSS-Fuzz.

Soak Tests: 수일 동안 무작위 쿼리 실행.

Crash Tests: I/O 에러 주입 — "fsync 중에 전원 끊기면?" "디스크 풀?" "OS 버그?" — 수백만 시뮬레이션.

12.3 TH3와 인증

TH3는 상용 라이선스로만 제공. Hwaci의 수익원. 하지만 SQLite 자체는 public domain.

Boeing이 DO-178B 인증을 위해 TH3로 검증된 SQLite를 사용. 비행 중요 소프트웨어에서 DB 사용 증명.

12.4 Bug Report 정책

SQLite 팀의 원칙:

• 모든 bug는 재현 가능해야 한다.
• 모든 fix는 테스트와 함께.
• 신규 기능보다 안정성.

업계에서 가장 신중한 개발 문화.

12.5 Fossil

SQLite 프로젝트는 Fossil이라는 자체 버전 관리 시스템 사용 (Git이 아닌). Hipp이 직접 개발.

특징:

• 단일 SQLite DB 파일에 전체 이력 저장.
• Wiki, bug tracker, forum 포함.
• 분산 복제.

SQLite가 Fossil을 뒷받침하고 Fossil이 SQLite를 관리. 원형 관계.

13. 성능 튜닝

13.1 PRAGMA

SQLite의 설정 명령어.

필수적인 것:

PRAGMA journal_mode = WAL;    -- WAL 모드 (강력 권장)
PRAGMA synchronous = NORMAL;  -- NORMAL (WAL에서 안전)
PRAGMA cache_size = -20000;   -- 20 MB 캐시
PRAGMA temp_store = MEMORY;   -- 임시 테이블을 RAM에
PRAGMA mmap_size = 268435456; -- 256 MB memory-mapped I/O

13.2 synchronous 레벨

• OFF (0): fsync 안 함. 최대 속도, 전원 끊기면 DB 파손 가능.
• NORMAL (1): 중요 지점만 fsync. WAL에서 안전.
• FULL (2): 모든 COMMIT에 fsync. 가장 안전.
• EXTRA (3): FULL + directory fsync.

Rollback journal에선 FULL 권장. WAL에선 NORMAL 충분.

13.3 Batch Insert

# BAD
for row in data:
    cursor.execute("INSERT INTO t VALUES (?, ?)", row)
# 각자 자동 커밋 → 각자 fsync

# GOOD
conn.execute("BEGIN")
for row in data:
    cursor.execute("INSERT INTO t VALUES (?, ?)", row)
conn.execute("COMMIT")
# 한 번의 fsync

100배 이상 차이.

13.4 Prepared Statement

stmt = conn.prepare("INSERT INTO t VALUES (?, ?)")
for row in data:
    stmt.execute(row)

SQL 파싱을 한 번만. Bind parameter로 재사용.

13.5 Index 활용

인덱스 스캔을 확인:

EXPLAIN QUERY PLAN SELECT ...;

SCAN TABLE이면 전체 스캔. SEARCH TABLE USING INDEX가 정상.

13.6 VACUUM

VACUUM;

DB 파일을 재구성 → 삭제된 공간 회수, 페이지 재정렬. 오프라인 작업.

Auto vacuum:

PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- 100 페이지 회수

13.7 Memory-mapped I/O

PRAGMA mmap_size = 268435456;  -- 256 MB

DB 파일을 mmap → OS가 페이지 캐시 관리. 큰 DB에서 빠름. 단점: 일부 플랫폼 이슈.

14. 최신 기능 (2023-2025)

14.1 STRICT Tables

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
) STRICT;

INSERT INTO users VALUES (1, 123, 'Alice');
-- Error: NOT NULL constraint failed (name is INTEGER)

타입 체크 강제. 기본은 loose (SQLite의 type affinity). STRICT로 엄격 타입.

14.2 RETURNING

INSERT INTO users (name) VALUES ('Alice') RETURNING id, name;

PostgreSQL 스타일. Insert/Update/Delete 결과 반환.

14.3 JSONB (3.45)

위에서 본 것. 바이너리 JSON 포맷으로 파싱 오버헤드 감소.

14.4 Math Functions

SELECT SIN(3.14), LOG(100), POW(2, 10);

수학 함수 내장. 이전엔 extension 필요.

14.5 Window Functions

SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;

현대 SQL 표준. SQLite 3.25+ 지원.

14.6 CTE와 Recursive CTE

WITH RECURSIVE cnt(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM cnt WHERE x < 10
)
SELECT * FROM cnt;

재귀 쿼리. 트리 구조 순회에 유용.

15. SQLite 대체/경쟁자

15.1 DuckDB

"SQLite for analytics". OLAP용 임베디드. 컬럼나 저장.

차이:

• SQLite: OLTP, 로우 기반.
• DuckDB: OLAP, 컬럼나, 벡터화.

상호 보완. DuckDB가 CSV/Parquet/Arrow 읽기 우수.

15.2 LibSQL

SQLite의 오픈 fork. Turso가 개발. 특징:

• HTTP API: 원격 접근.
• Replication: WebSocket 기반.
• Edge computing: 전 세계 배포.

"SQLite + 분산". Cloudflare D1도 유사.

15.3 Cloudflare D1

Cloudflare Workers의 SQLite 기반 DB. Durable Objects 위에서 실행.

15.4 Turso

LibSQL 기반 관리형 서비스. SQLite를 엣지 컴퓨팅 플랫폼으로.

15.5 전통 경쟁자

• Berkeley DB: KV only, 오래됨.
• H2 (Java): 임베디드 Java DB.
• RocksDB: LSM, KV only.

아무도 SQLite의 조합(SQL + 임베디드 + 신뢰성 + 작음)을 이기지 못했다.

16. 실전 사용 패턴

16.1 iOS/Android 앱

Core Data (iOS), Room (Android)이 SQLite를 wrap.

// Swift
let manager = NSManagedObjectContext(...)
let users = manager.fetch(NSFetchRequest<User>())

내부적으로 SQLite 쿼리.

16.2 Chrome

• History: ~/Library/Application Support/Google/Chrome/Default/History
• Cookies: Cookies 파일
• LocalStorage: 일부 SQLite

모두 SQLite 파일. sqlite3 CLI로 열어서 탐색 가능.

16.3 Dropbox

초기 Dropbox는 SQLite에 파일 메타데이터 저장. 지금도 일부 사용.

16.4 데스크톱 앱

Skype, 1Password, Evernote, Notion (local cache) 등 셀 수 없이 많다.

16.5 서버 사이드?

SQLite는 일반적으로 고동시성 웹 서비스엔 부적합. 하지만:

• Read-heavy: 매우 적합. Replication 없이도 단일 파일 분산.
• Analytics / reporting: OLTP가 아니라 분석이면 OK.
• Embedded in app server: 설정 DB, 캐시, 세션 스토리지.

Litestream, LiteFS 같은 도구로 SQLite를 replication/backup → "서버 사이드 SQLite" 재유행.

17. 학습 리소스

공식:

• https://www.sqlite.org/docs.html — 매우 우수한 공식 문서.
• https://sqlite.org/src/ — Fossil 소스.

책:

• "The Definitive Guide to SQLite" — Mike Owens.
• "Using SQLite" — Jay Kreibich.

영상:

• Richard Hipp의 강연들 (YouTube).
• "SQLite Internals" 시리즈.

코드 탐험:

• sqlite3.c amalgamation 읽기.
• 각 모듈별 주요 함수.

Tools:

• sqlite3 CLI — 내장.
• DB Browser for SQLite — GUI.
• DBeaver — 다양한 DB 지원.

18. 요약 — 한 장 정리

┌─────────────────────────────────────────────────────┐
│              SQLite Cheat Sheet                      │
├─────────────────────────────────────────────────────┤
│ 설계:                                                  │
│   단일 C 파일 (~150K 라인, amalgamation)              │
│   Public Domain                                       │
│   서버 없음, 파일 하나                                  │
│                                                       │
│ 아키텍처 (위에서 아래로):                                │
│   C API                                              │
│   Tokenizer → Parser → Code Generator                │
│   VDBE (bytecode VM)                                  │
│   B-tree                                              │
│   Pager + Page Cache                                  │
│   VFS (OS interface)                                  │
│                                                       │
│ 저장:                                                  │
│   4KB 페이지 기본                                      │
│   B+tree (테이블, 인덱스 각각)                         │
│   Row ID (64-bit)                                    │
│   Varint + Serial Types                               │
│                                                       │
│ Transaction 모드:                                     │
│   Rollback Journal (전통)                            │
│     - 원본 페이지를 journal에 저장                    │
│     - Writer가 readers 차단                           │
│   WAL (2010+, 권장)                                  │
│     - 변경을 별도 WAL 파일에                          │
│     - Reader/writer 동시성                             │
│                                                       │
│ VFS:                                                  │
│   OS 파일시스템 추상                                   │
│   커스텀 구현 가능                                     │
│   - In-memory, 암호화                                │
│   - HTTP, S3                                          │
│   - WASM/IndexedDB                                    │
│                                                       │
│ Virtual Table:                                        │
│   FTS5 (full-text search)                            │
│   R-tree (공간 인덱스)                                │
│   CSV, JSON1/JSONB                                   │
│   커스텀 가능                                          │
│                                                       │
│ VDBE:                                                 │
│   Bytecode VM (register-based)                       │
│   150+ opcodes                                        │
│   EXPLAIN으로 확인                                    │
│                                                       │
│ Query Planner:                                        │
│   Cost-based                                          │
│   ANALYZE로 통계 수집                                  │
│   Index selection                                    │
│   Partial, expression index                          │
│                                                       │
│ 신뢰성:                                                │
│   92M 줄 테스트 (150K 코드의 600배)                  │
│   100% MC/DC 커버리지 (DO-178B)                      │
│   Boeing, 의료기기에서 인증                            │
│                                                       │
│ 튜닝:                                                  │
│   PRAGMA journal_mode = WAL                          │
│   PRAGMA synchronous = NORMAL                        │
│   PRAGMA cache_size = -20000                         │
│   배치 insert (트랜잭션)                              │
│   Prepared statement                                  │
│                                                       │
│ 최신 (2023-2025):                                     │
│   STRICT tables                                       │
│   RETURNING                                           │
│   JSONB (3.45)                                        │
│   Math functions                                      │
│   Window functions                                    │
│                                                       │
│ 사용처:                                                │
│   모든 iOS/Android 앱                                 │
│   Chrome, Firefox, Safari                            │
│   Mac, Windows, Linux                                 │
│   Boeing 787, 의료기기                                │
│   Dropbox, Skype, Slack (local)                      │
└─────────────────────────────────────────────────────┘

19. 퀴즈

이 글이 도움이 됐다면 다음 포스트도 확인해 보세요:

• "RocksDB & LSM-Tree Deep Dive" — 또 다른 임베디드 스토리지 엔진.
• "ClickHouse Internals Deep Dive" — 분석용 columnar 엔진과의 비교.
• "Database Index Mastery" — B-tree 인덱스 설계.
• "Git Internals" — 또 다른 content-addressable 저장소.