SQLite

SQLite 는 프로그램 안에 들어가서 함께 도는 데이터베이스입니다. 따로 띄워 둘 서버 프로세스가 없습니다. 데이터베이스 하나가 디스크 파일 하나입니다. 그 파일을 복사하면 데이터베이스를 통째로 옮긴 것이 됩니다.

상세

공식 문서는 SQLite 를 자기완결적이고, 서버가 없고, 설정이 필요 없고, 트랜잭션을 지원하는 SQL(Structured Query Language, 구조화 질의 언어) 데이터베이스 엔진을 구현한 인프로세스(in-process, 프로그램의 프로세스 안에 들어가 함께 도는) 라이브러리라고 적습니다. 같은 문서가 곧이어 SQLite 를 임베디드 SQL 데이터베이스 엔진이라고도 적어, 인프로세스와 임베디드를 같은 뜻으로 씁니다. 이 사전도 뒤에서 그 둘을 같은 뜻으로 씁니다. 같은 문서가 붙인 한 줄이 이 물건의 자리를 잘 보여줍니다. SQLite 를 Oracle 의 대체물이 아니라 fopen() 의 대체물로 생각하라는 것입니다.

서버 없는 구조

대부분의 SQL 데이터베이스 엔진은 별도의 서버 프로세스로 구현됩니다. 데이터베이스에 접근하려는 프로그램은 프로세스 사이 통신으로 서버에 요청을 보내고 결과를 돌려받습니다. 공식 문서는 그 통신이 보통 TCP/IP(Transmission Control Protocol/Internet Protocol, 전송 제어 프로토콜/인터넷 프로토콜)라고 적습니다. SQLite 는 그렇게 동작하지 않습니다. 데이터베이스에 접근하려는 프로세스가 디스크의 데이터베이스 파일을 직접 읽고 씁니다. 중간에 서버 프로세스가 없습니다.

flowchart TD
    subgraph SRV["서버를 두는 엔진"]
        C1[프로그램] -->|프로세스 사이 통신| S[서버 프로세스]
        S --> F1[(데이터 파일)]
    end
    subgraph LIB["SQLite"]
        C2[프로그램 + SQLite 라이브러리] --> F2[(데이터베이스 파일)]
    end

그래서 설치하고 설정하고 초기화하고 관리하고 문제를 살필 서버 프로세스가 따로 없습니다. 공식 문서는 이것을 SQLite 가 설정이 필요 없는 엔진인 이유 하나로 듭니다. 디스크에 접근할 수 있는 프로그램이면 어느 것이든 SQLite 데이터베이스를 쓸 수 있다고 적습니다.

파일 하나에 담기는 데이터베이스

여러 테이블과 인덱스와 트리거와 뷰를 갖춘 완전한 SQL 데이터베이스가 디스크 파일 하나에 담깁니다. 그 파일은 디렉토리 계층 어디에나 둘 수 있는 평범한 파일입니다. SQLite 가 그 파일을 읽을 수 있으면 데이터베이스 안의 무엇이든 읽을 수 있고, 파일과 그 디렉토리에 쓸 수 있으면 무엇이든 바꿀 수 있습니다. 문서는 그 파일을 USB(Universal Serial Bus, 범용 직렬 버스) 메모리에 복사하거나 메일로 보내 나눌 수 있다고 적습니다.

트랜잭션이 도는 동안에는 SQLite 가 원자적 커밋과 롤백을 구현하려고 둘째 파일에 추가 정보를 저장합니다. 그 둘째 파일이 롤백 저널이거나, WAL(Write-Ahead Log, 미리 쓰기 로그) 모드라면 WAL 파일입니다.

파일 안쪽은 일정 크기로 자른 조각인 페이지로 나뉘고 그 페이지들이 B-tree 를 이룹니다. B-tree 페이지는 안쪽 페이지(interior page)이거나 잎 페이지(leaf page)입니다. 안쪽 페이지는 키와 함께 자식 페이지를 가리키는 포인터를 들고, 잎 페이지가 실제 데이터를 담습니다. 파일 포맷 문서는 두 갈래를 적습니다. 테이블 B-tree 는 64비트 부호 있는 정수를 키로 쓰고 데이터를 전부 잎 페이지에 저장합니다. 인덱스 B-tree 는 임의의 키를 쓰고 데이터를 전혀 저장하지 않습니다. 즉 행 데이터를 담는 구조와 그 행을 가리키는 키만 담는 구조를 나눠 쓰는 것입니다.

SQLite 데이터베이스의 전체 상태는 보통 디스크의 파일 하나, 메인 데이터베이스 파일에 담깁니다. WAL 파일이 언제 정리되는지는 뒤의 「운영」 절이 다룹니다.

기계를 안 가리는 파일 포맷

파일 포맷은 플랫폼을 가리지 않습니다. 한 기계에서 쓴 데이터베이스 파일을 아키텍처가 다른 기계로 복사해 그대로 쓸 수 있습니다. 빅엔디언이든 리틀엔디언이든, 32비트든 64비트든 상관없다고 적습니다. 개발자들은 파일 포맷을 안정적이고 하위 호환되게 유지하겠다고 약속했습니다. 그래서 새 판의 SQLite 가 옛 데이터베이스 파일을 읽고 씁니다. 다른 SQL 데이터베이스 엔진은 대개 플랫폼을 옮길 때, 그리고 흔히 소프트웨어를 새 판으로 올릴 때도 덤프하고 복원해야 합니다.

이 성질 때문에 SQLite 가 애플리케이션 파일 포맷으로 자주 선택된다고 문서는 적습니다. 미국 의회도서관이 권장하는 저장 포맷이기도 합니다.

트랜잭션

트랜잭션은 ACID(Atomicity·Consistency·Isolation·Durability, 원자성·일관성·격리성·지속성)입니다. 시스템 충돌이나 정전으로 중단되더라도 그렇다고 문서는 적습니다.

퍼블릭 도메인

코드와 문서 전부가 저자들에 의해 퍼블릭 도메인에 헌정되었습니다. 누구나 원본 SQLite 코드를 복사하고 수정하고 게시하고 사용하고 컴파일하고 판매하고 배포할 수 있습니다. 소스 형태로든 컴파일된 바이너리로든, 어떤 목적으로든, 상업적이든 비상업적이든 그렇습니다.

포기한 것

서버 프로세스

서버를 두지 않기로 한 결정이 나머지를 전부 정합니다. 얻은 것은 설치하고 설정하고 초기화하고 관리하고 문제를 살필 별도 프로세스가 없다는 것입니다. 내준 것도 공식 문서가 스스로 적어 둡니다. 서버를 쓰는 엔진은 클라이언트 애플리케이션의 버그로부터 데이터베이스를 더 잘 보호할 수 있습니다. 클라이언트의 엉뚱한 포인터가 서버 쪽 메모리를 망가뜨릴 수 없기 때문입니다. 그리고 서버는 하나의 영속 프로세스이므로 데이터베이스 접근을 더 정밀하게 제어할 수 있고, 더 세밀한 잠금과 더 나은 동시성을 가능하게 합니다.

네트워크 너머로 쓰는 자리도 여기서 잘립니다. 문서는 여러 클라이언트 프로그램이 네트워크로 같은 데이터베이스에 SQL 을 보낸다면 SQLite 말고 클라이언트/서버 엔진을 쓰라고 적습니다. SQLite 가 네트워크 파일시스템 위에서 동작은 하지만, 대부분의 네트워크 파일시스템에 딸린 지연 때문에 성능이 좋지 않을 것이라고 합니다. 게다가 파일 잠금 로직이 유닉스와 윈도우 양쪽에서 많은 네트워크 파일시스템 구현에 버그가 있습니다. 파일 잠금이 제대로 동작하지 않으면 둘 이상의 클라이언트가 같은 데이터베이스의 같은 부분을 동시에 고치려 들 수 있고, 그 결과가 손상입니다. 밑바닥 파일시스템 구현의 버그에서 나오는 문제라 SQLite 가 막을 방법이 없다고 못 박습니다. 문서가 내놓는 경험칙은 하나입니다. 애플리케이션 서버를 사이에 두지 않고 네트워크 너머 여러 컴퓨터에서 같은 데이터베이스에 동시에 직접 접근하는 상황은 피하라는 것입니다.

규모도 파일 하나에 묶입니다. 데이터베이스 파일의 최대 크기는 4294967294 페이지이고, 페이지 크기 상한인 65536바이트를 쓰면 약 281테라바이트가 됩니다. 문서는 이 상한이 시험되지 않았다고 밝힙니다. 개발자들이 이 한도에 닿을 만한 하드웨어를 갖고 있지 않기 때문입니다. 그리고 전체 데이터베이스를 디스크 파일 하나에 담기 때문에 많은 파일시스템의 파일 크기 상한이 이보다 작습니다. 이 규모의 데이터베이스를 생각한다면 내용을 여러 디스크 파일에, 어쩌면 여러 볼륨에 흩는 클라이언트/서버 엔진을 고려하는 편이 낫다고 적습니다.

동시에 쓰는 여럿

SQLite 는 동시 독자(읽는 프로그램) 수에 제한을 두지 않지만, 어느 순간에도 기록자(쓰는 프로그램)는 하나만 허용합니다. 잠금 문서는 프로세스 하나가 쓰기로 나아가며 쥐는 잠금 단계 넷을 적습니다. SHARED 잠금은 이름 그대로 Shared Lock(공유 잠금)이라 여러 프로세스가 동시에 쥘 수 있어서 독자가 여럿일 수 있습니다. 다만 SHARED 잠금이 하나라도 살아 있는 동안에는 다른 어떤 스레드나 프로세스도 데이터베이스 파일에 쓸 수 없습니다. RESERVED 는 지금은 읽기만 하지만 앞으로 쓸 계획이라는 표시이고 한 번에 하나만 살아 있을 수 있습니다. PENDING 은 가능한 한 빨리 쓰고 싶어서 남은 SHARED 잠금이 풀리기를 기다리는 상태입니다. PENDING 이 살아 있으면 새 SHARED 잠금은 허용되지 않고, 이미 있던 SHARED 잠금만 계속됩니다. 파일에 쓰려면 EXCLUSIVE 잠금이 필요합니다. EXCLUSIVE 는 파일당 하나만 허용됩니다. 어떤 종류의 다른 잠금과도 공존할 수 없습니다. 문서는 SQLite 가 EXCLUSIVE 잠금을 쥐고 있는 시간을 최소화하려 애쓴다고 적습니다. 동시성을 최대로 하려는 것입니다.

stateDiagram-v2
    [*] --> SHARED: 읽기 시작
    note right of SHARED
        여러 프로세스가
        동시에 쥘 수 있다
    end note
    SHARED --> RESERVED: 곧 쓰겠다고 표시
    note right of RESERVED
        한 번에 하나만
    end note
    RESERVED --> PENDING: 남은 SHARED 를 기다린다
    note right of PENDING
        다른 프로세스가 쥔
        SHARED 가 아직 남아 있다
    end note
    PENDING --> EXCLUSIVE: 마지막 SHARED 가 풀림
    note right of EXCLUSIVE
        파일당 하나만,
        다른 잠금과 공존 못 함
    end note
    EXCLUSIVE --> [*]: 쓰기 끝

문서는 이 포기가 많은 상황에서는 문제가 아니라고 적습니다. 기록자들은 줄을 섭니다. 각 애플리케이션이 데이터베이스 작업을 재빨리 끝내고 지나가며, 어떤 잠금도 수십 밀리초를 넘게 유지되지 않습니다. 다만 더 많은 동시성을 요구하는 애플리케이션도 있고, 그런 애플리케이션은 다른 해법을 찾아야 할 수 있다고 덧붙입니다. 쓰기가 몰리거나 서버를 여러 대 둬야 할 만큼 바쁜 웹사이트라면 기업급 클라이언트/서버 엔진을 고려하라는 것도 같은 자리에 적혀 있습니다.

접근 권한과 스키마 변경 명령

SQLite 는 평범한 디스크 파일을 읽고 쓰기 때문에, 적용할 수 있는 접근 권한은 밑바닥 운영체제의 일반 파일 접근 권한뿐입니다. 클라이언트/서버 데이터베이스에 흔히 있는 GRANT 와 REVOKE 는, 앞서 인프로세스라고 적었던 것과 같은 뜻인 임베디드 데이터베이스 엔진에서는 의미가 없어서 구현되지 않았다고 문서는 적습니다. 권한 모델을 데이터베이스 안에 두지 않고 파일시스템에 넘긴 것입니다.

표의 구조를 고치는 스키마 변경 명령도 일부만 들어 있습니다. ALTER TABLE 이 그 명령이고, 지원되는 갈래는 RENAME TABLE·ADD COLUMN·RENAME COLUMN·DROP COLUMN 넷입니다. ALTER COLUMN· ADD CONSTRAINT 같은 다른 종류의 ALTER TABLE 연산은 빠져 있습니다.

타입 강제

열에 적은 데이터 타입이 강제가 아니라 권고입니다. INTEGER 열에 문자열 '1234' 를 넣으면 다른 엔진처럼 정수 1234 로 바꿔 저장합니다. 그런데 'wxyz' 처럼 숫자가 아닌 문자열을 넣으면 다른 SQL 데이터베이스와 달리 오류를 내지 않고 그 문자열을 그대로 저장합니다. VARCHAR(50) 열에 2000자 문자열을 넣어도 잘라내거나 오류를 내지 않고 손실 없이 전부 저장합니다.

문서는 이것을 버그가 아니라 기능이라고 못 박습니다. SQLite 가 "weakly typed" 이고 다른 데이터베이스가 "strongly typed" 라는 표현은 부정확하고 심지어 폄하적이라고 봅니다. 대신 SQLite 를 "flexibly typed", 다른 엔진을 "rigidly typed" 라고 부르기를 선호한다고 적습니다. 유연한 타이핑은 자유에 관한 것이라는 게 문서의 말입니다. 대신 엄격한 타입 규칙에 익숙한 개발자에게 이 성질이 때때로 혼란을 준다는 것도 인정합니다. 그 자리를 위해 3.37.0(2021-11-27)에서 STRICT 테이블 선택지가 들어왔습니다. STRICT 테이블은 다른 엔진에 있는 강제 타입 제약을 걸거나, 명시적인 ANY 타입으로 유연한 타이핑을 남겨 둡니다.

예시

sqlite3 셸 세션 한 벌

$ sqlite3 ex1.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table tbl1(one text, two int);
sqlite> insert into tbl1 values('hello!',10),('goodbye',20);
sqlite> select * from tbl1;
┌───────────┬─────┐
│   one     │ two │
├───────────┼─────┤
│ 'hello!'  │ 10  │
│ 'goodbye' │ 20  │
└───────────┴─────┘
sqlite>

sqlite3 뒤에 데이터베이스를 담은 파일 이름을 붙입니다. 그 이름의 파일이 없으면 새 데이터베이스 파일이 자동으로 만들어집니다. 명령줄에 파일을 아예 안 적으면 메모리 안의 임시 데이터베이스를 쓰고, 그 데이터베이스는 프로그램이 끝날 때 지워집니다. 명령마다 세미콜론을 꼭 붙여야 합니다. sqlite3 프로그램은 세미콜론을 보고 SQL 명령이 끝났음을 압니다. 끝낼 때는 시스템의 파일 끝 문자를 치는데 보통 Control-D 입니다. 배너에 찍힌 판 번호는 공식 문서가 이 세션을 예제로 실을 때의 값입니다.

Python 표준 라이브러리에서 부르는 호출

Python 배포판에는 2.5 이후로 전부 SQLite 가 들어 있습니다. 그래서 따로 설치할 것 없이 표준 라이브러리의 sqlite3 모듈을 부르는 것으로 끝납니다.

import sqlite3
con = sqlite3.connect("tutorial.db")
cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")

sqlite3.connect() 가 현재 작업 디렉토리의 tutorial.db 에 연결을 만들고, 그 파일이 없으면 암묵적으로 만듭니다. 돌아온 Connection 객체가 디스크 위 데이터베이스에 대한 연결을 나타냅니다. SQL 문을 실행하고 결과를 가져오려면 커서가 필요해서 con.cursor() 를 부릅니다. 테이블 선언에 열 이름만 적은 것도 그대로 동작합니다. Python 문서는 SQLite 의 유연한 타이핑 덕분에 데이터 타입을 적는 것이 선택 사항이라고 적습니다.

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")
con.commit()

INSERT 문이 암묵적으로 트랜잭션을 엽니다. 변경이 데이터베이스에 저장되려면 그 트랜잭션을 커밋해야 해서 연결 객체의 con.commit() 을 부릅니다.

>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]

res.fetchall() 이 결과 행 전부를 돌려줍니다.

저널 모드를 바꾸는 프라그마 한 줄

프라그마는 PRAGMA 이름 이면 조회하고 PRAGMA 이름 = 값 이면 바꾸는, 설정을 다루는 SQLite 전용 명령입니다. 데이터베이스 연결은 기본으로 journal_mode=DELETE 입니다. 이 기본값이 앞서 나온 롤백 저널을 쓰는 방식이고, WAL 은 그 대안입니다. 문서는 WAL 이 롤백 저널보다 빠르다고 적습니다 (자세한 설명은 뒤의 「운영」 절). 이 프라그마로 바꿉니다.

PRAGMA journal_mode=WAL;

journal_mode 프라그마는 새 저널 모드를 문자열로 돌려줍니다. 성공하면 "wal" 이 돌아옵니다. 전환이 끝나지 못하면 저널링 모드가 바뀌지 않고 이전 모드 문자열이 돌아옵니다. 예를 들어 "delete" 입니다. 문서가 드는 전환 실패 사례는 모든 프로세스가 나눠 쓰는 적은 양의 메모리인 공유 메모리 기능을 지원하지 않는 경우입니다.

사용처

이름이 오른 자리

공식 문서의 채택 사례 목록은 회사와 제품 이름을 직접 댑니다.

문서가 대는 이유

관리자가 붙을 수 없는 장치. SQLite 데이터베이스는 관리가 필요 없어서, 전문 인력의 지원 없이 동작해야 하는 장치에 잘 맞는다고 문서는 적습니다. 그 자리로 문서가 드는 목록이 휴대폰, 셋톱박스, 텔레비전, 게임 콘솔, 카메라, 시계, 주방기기, 온도조절기, 자동차, 공작기계, 비행기, 원격 센서, 드론, 의료기기, 로봇입니다. 에어버스의 비행 소프트웨어와 모바일 운영체제들이 이 칸에 앉습니다.

애플리케이션 파일 포맷. 버전 관리 시스템, 재무 분석 도구, 미디어 목록·편집 모음, 캐드 패키지, 기록 관리 프로그램 같은 데스크톱 애플리케이션이 디스크 위 파일 포맷으로 SQLite 를 자주 씁니다. 전통적인 File/Open 동작이 sqlite3_open() 을 불러 데이터베이스 파일에 붙습니다. 애플리케이션 내용이 고쳐질 때마다 갱신이 자동으로 일어납니다. 그래서 File/Save 메뉴가 필요 없어진다고 적습니다. 문서 하나가 파일 하나라 USB 로 복사하거나 메일로 보내는 단위가 그대로 문서입니다. 파일 포맷이 플랫폼을 가리지 않아 아키텍처가 다른 기계로 건네도 그대로 열립니다. Firefox 와 Thunderbird 의 메타데이터 저장, 의회도서관의 보존 포맷이 이 칸입니다.

데이터 분석. SQL 을 아는 사람이 sqlite3 명령줄 셸이나 제삼자 접근 프로그램으로 큰 데이터셋을 분석하는 자리입니다. CSV(Comma-Separated Values, 쉼표로 구분된 값) 파일에서 원본 데이터를 들여온 다음 잘라 붙여 요약 보고를 만듭니다. 더 복잡한 분석은 Tcl 이나 Python 으로 쓴 간단한 스크립트로 합니다. 둘 다 SQLite 를 내장하고 있기 때문이라고 문서는 적습니다. R 이나 다른 언어에서는 구할 수 있는 어댑터를 씁니다. 웹사이트 로그 분석, 스포츠 통계 분석, 프로그래밍 지표 집계, 실험 결과 분석이 문서가 드는 용도입니다.

운영

SQLITE_BUSY

SQLITE_BUSY 결과 코드는 다른 데이터베이스 연결의 동시 활동 때문에 데이터베이스 파일을 쓸 수 없었다는 뜻입니다. 어떤 경우에는 읽을 수 없었다는 뜻이기도 합니다. 그 다른 연결은 대개 별도 프로세스에 있는 연결입니다. 문서가 드는 예가 알기 쉽습니다. 프로세스 A 가 큰 쓰기 트랜잭션 도중인데 프로세스 B 가 새 쓰기 트랜잭션을 시작하려 하면, SQLite 가 한 번에 기록자 하나만 지원하므로 B 는 SQLITE_BUSY 를 돌려받습니다. B 는 A 의 트랜잭션이 끝나기를 기다렸다가 새 트랜잭션을 시작해야 합니다.

sequenceDiagram
    participant A as 프로세스 A
    participant F as 데이터베이스 파일
    participant B as 프로세스 B
    A->>F: 큰 쓰기 트랜잭션 시작
    activate F
    Note over F: A 가 잠금을 쥔 상태
    B->>F: 새 쓰기 트랜잭션을 시작하려 한다
    Note over B: 자신의 파일 접근이 실패한다 → SQLITE_BUSY<br/>(A 가 보낸 응답이 아니다)
    Note over B: A 의 트랜잭션이 끝나기를 기다린다
    A->>F: 트랜잭션을 끝낸다
    deactivate F
    B->>F: 새 트랜잭션을 시작한다

두 프로세스는 서로에게 말을 걸지 않습니다. 앞서 나왔듯 SQLite 에는 중간에 서는 서버 프로세스가 없어서, A 와 B 는 각자 디스크의 데이터베이스 파일을 직접 두드립니다. B 가 받는 SQLITE_BUSY 는 A 가 보낸 응답이 아니라, 이미 A 가 쥔 잠금 때문에 B 자신의 파일 접근이 실패한 결과입니다.

SQLITE_BUSY 는 트랜잭션의 어느 지점에서든 날 수 있습니다. 트랜잭션을 처음 시작할 때, 쓰기나 갱신 연산 도중, 그리고 커밋할 때입니다. 트랜잭션 한복판에서 이걸 만나지 않으려면 애플리케이션이 그냥 BEGIN 대신 BEGIN IMMEDIATE 로 트랜잭션을 시작할 수 있다고 문서는 적습니다.

같은 문서가 이웃 코드와 가르는 선도 그어 둡니다. SQLITE_BUSY 는 별도 데이터베이스 연결과의 충돌을 가리키고, 그 연결은 아마 별도 프로세스에 있습니다. SQLITE_LOCKED 는 같은 데이터베이스 연결 안의 충돌을 가리킵니다. 여러 연결이 캐시를 나눠 쓰도록 설정된 상태, 즉 공유 캐시를 쓰는 연결 사이의 충돌일 때도 있습니다.

busy_timeout

기다리는 시간을 정하는 손잡이가 busy_timeout 입니다.

PRAGMA busy_timeout;
PRAGMA busy_timeout = milliseconds;

이 프라그마는 C 언어 인터페이스 sqlite3_busy_timeout() 의 대체물입니다. sqlite3_busy_timeout() 에 직접 접근할 수 없는 언어 바인딩에서 쓰라고 프라그마로 내놓은 것입니다. busy 핸들러는 테이블이 잠겨 있을 때 정해진 시간만큼 기다리는 대기 루틴이고, 데이터베이스 연결 하나는 이것을 하나만 가질 수 있습니다. 이 프라그마는 프로세스의 busy 핸들러를 설정하며, 앞서 설정된 핸들러를 덮어쓸 수 있습니다.

int sqlite3_busy_timeout(sqlite3*, int ms);

이 루틴은 테이블이 잠겨 있을 때 지정한 시간만큼 자는 busy 핸들러를 설정합니다. 핸들러는 잠든 시간이 최소 ms 밀리초 쌓일 때까지 여러 번 자고, 그 뒤에 0 을 돌려주어 SQL 문을 한 단계씩 실행하는 함수인 sqlite3_step() 이 SQLITE_BUSY 를 반환하게 만듭니다. 인자를 0 이하로 주고 이 루틴을 부르면 모든 busy 핸들러가 꺼집니다. 즉 핸들러를 걸지 않았거나 0 이하로 걸었다면 기다리지 않고 즉시 SQLITE_BUSY 가 돌아옵니다.

WAL 모드

PRAGMA journal_mode=WAL; 로 켭니다. 기본은 journal_mode=DELETE 입니다. 롤백 저널이 SQLite 가 원자적 커밋과 롤백을 구현하는 기본 방식이고, WAL 은 3.7.0(2010-07-21)부터 들어온 선택지입니다. 문서는 WAL 이 롤백 저널에 비해 대부분의 상황에서 눈에 띄게 빠르다고 적습니다. 독자가 기록자를 막지 않고 기록자가 독자를 막지 않아 읽기와 쓰기가 동시에 진행될 수 있습니다. 디스크 입출력이 더 순차적인 경향이 있고 fsync() 호출을 훨씬 적게 씁니다.

못 하는 것도 문서가 같은 자리에 적어 둡니다.

체크포인트도 봐야 하는 자리입니다. 체크포인트 연산은 WAL 파일의 내용을 원본 데이터베이스 파일로 옮겨 담습니다. 독자와 동시에 돌 수 있지만, 현재 독자가 쓰지 않는 공유 메모리가 떨어지면 체크포인트는 멈춰야 합니다. 기본값으로 SQLite 는 커밋이 일어나 WAL 파일이 1000 페이지 이상이 될 때, 또는 그 데이터베이스 파일의 마지막 연결이 닫힐 때 자동으로 체크포인트를 돕니다.

foreign_keys 와 synchronous

이 둘은 기본값을 그대로 둘지 애플리케이션이 판단해서 정해야 하는 프라그마입니다. 하나는 데이터 무결성을 강제할지를 정하고, 다른 하나는 커밋마다 디스크에 얼마나 세게 맞출지를 정합니다.

PRAGMA foreign_keys 는 외래키 제약의 강제를 조회하고 켜고 끕니다. 3.6.19 부터 외래키 강제의 기본 설정은 꺼짐입니다. 다만 문서는 그것이 앞으로의 판에서 바뀔 수도 있다고 덧붙입니다. 그래서 애플리케이션은 기본값에 기대지 말고 필요한 대로 외래키 강제 플래그를 직접 설정하라고 권합니다. 컴파일 시점에 SQLITE_DEFAULT_FOREIGN_KEYS 매크로로 기본값을 지정할 수도 있습니다. 이 프라그마는 트랜잭션 안에서는 아무 일도 하지 않습니다. 대기 중인 BEGIN 이나 SAVEPOINT 가 없을 때만 켜고 끌 수 있습니다.

PRAGMA synchronous 는 디스크에 맞추는 세기를 정합니다. 값은 0 | OFF·1 | NORMAL·2 | FULL·3 | EXTRA 입니다. FULL 이면 SQLite 가 진행하기 전에 모든 내용이 디스크 표면에 안전하게 쓰였는지 확인합니다. 운영체제 충돌이나 정전이 데이터베이스를 손상시키지 않게 하려는 것입니다. 롤백 저널의 기본 동기화 모드가 FULL 입니다. OFF 면 데이터를 운영체제에 넘기자마자 동기화 없이 진행합니다. 애플리케이션이 죽는 경우에는 데이터가 안전하지만, 그 데이터가 비휘발성 저장 장치에 쓰이기 전에 운영체제가 죽거나 컴퓨터가 전원을 잃으면 데이터베이스가 손상될 수 있습니다.

VACUUM

지운 만큼 파일이 줄지 않습니다. 지울 때마다 빈 공간을 자동으로 되돌려주는 auto_vacuum=FULL 모드로 돌고 있지 않다면, 데이터베이스 파일에서 많은 양의 데이터를 지웠을 때 빈 공간이 남습니다. 문서는 이것을 자유 페이지라고 부릅니다. 그래서 데이터베이스 파일이 꼭 필요한 크기보다 클 수 있습니다. VACUUM 은 데이터베이스 파일을 다시 지어 최소한의 디스크 공간으로 다시 담습니다. 그 과정에서 이 공간을 회수하고 파일 크기를 줄입니다.

조각화도 같은 명령이 다루는 자리입니다. 삽입·갱신·삭제가 잦으면 데이터베이스 파일이 조각날 수 있습니다. 테이블 하나나 인덱스 하나의 데이터가 파일 여기저기에 흩어지는 상태입니다. VACUUM 을 돌리면 각 테이블과 인덱스가 파일 안에 대체로 연속해서 저장됩니다.

관련 항목

이것을 이루는 구성 요소

SQL · B-tree · WAL · 롤백 저널

견주어 설명되는 이름

Oracle · fopen · TCP/IP

실제로 구현·채택한 사례

에어버스 · 애플 · Android · Chrome · 모질라 · Firefox · Thunderbird · 마이크로소프트 · Windows · PHP · Python · 미국 의회도서관