SQLite PRAGMA 설정: 운영 환경 튜닝 가이드

PRAGMA로 SQLite 엔진과 대화하기

PRAGMA는 SQLite 엔진의 동작 방식을 읽거나 바꿀 때 쓰는 SQLite 전용 명령어입니다. 사용법은 일반 SQL 문과 똑같지만, 데이터를 건드리는 대신 데이터베이스의 설정값을 다룬다는 점이 다릅니다.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

PRAGMA는 쿼리처럼 실행하면 현재 설정값을 돌려주고, 대입문처럼 실행하면 그 값을 바꿉니다:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

핵심 개념부터 잡고 가자. 대부분의 PRAGMA는 _커넥션 단위_로 적용된다. 새 커넥션을 열면 다시 기본값으로 돌아간다는 뜻이다. 그래서 운영 코드에서는 보통 커넥션을 연 직후에 곧바로 실행되는 PRAGMA 문 몇 줄을 한 블록으로 묶어 두는 패턴을 쓴다.

운영환경 기본 PRAGMA 설정

PRAGMA를 딱 다섯 개만 기억해야 한다면, 다음 다섯 개를 기억하자:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT '구성 완료' AS status;

거의 모든 애플리케이션에서 SQLite를 메인 저장소로 쓸 때 무난하게 시작할 수 있는 기본값들이다. 각 항목을 하나하나 이해해 둘 가치가 있는데, 이 글의 나머지 부분에서 차근차근 짚어 본다.

journal_mode = WAL

journal mode는 SQLite가 쓰기 작업을 어떻게 안전하게 디스크에 반영할지를 결정하는 설정이다. 기본값인 DELETE는 롤백 저널 방식을 사용하는데, 쓰기가 읽기를 막고 읽기가 쓰기를 막는다. CLI 도구라면 큰 문제가 안 되지만, 웹 앱에서는 꽤 거슬린다.

이를 뒤집어 주는 게 바로 WAL(Write-Ahead Logging) 모드다. 읽기와 쓰기가 서로를 막지 않기 때문에, 쓰기 작업이 커밋 중이어도 읽기 쪽에서는 일관된 스냅샷을 그대로 볼 수 있다. 동시에 쓸 수 있는 작성자는 여전히 한 명뿐이지만, 부하가 걸려도 읽기 성능은 떨어지지 않는다.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

몇 가지 알아둘 점:

• journal_mode은 _영구 설정_입니다. 한 번 설정해두면 데이터베이스 파일에 그대로 유지되죠. 매 연결마다 다시 설정할 필요는 없지만, 그렇다고 해서 문제가 되는 것도 아닙니다.
• WAL 모드를 켜면 .db 파일 옆에 -wal과 -shm 두 개의 추가 파일이 생깁니다. 데이터베이스가 열려 있는 동안에는 이 파일들을 절대 지우지 마세요.
• WAL은 네트워크 파일시스템(NFS, SMB)에서는 제대로 동작하지 않습니다. 데이터베이스는 로컬 디스크에 두세요.

WAL 모드와 동시성에 관해서는 좀 더 깊이 다루는 별도 문서가 있습니다. 일단 지금은 켜두면 됩니다.

synchronous = NORMAL 설정하기

synchronous는 SQLite가 디스크에 얼마나 적극적으로 플러시할지를 결정합니다. 핵심은 내구성(durability)과 속도 사이의 트레이드오프예요.

• FULL (기본값) — 커밋할 때마다 플러시합니다. 내구성은 최고지만 속도는 느립니다.
• NORMAL — 안전한 체크포인트 시점에 플러시합니다. WAL과 함께 쓰면 안전하면서도 더 빠릅니다.
• OFF — OS에 맡깁니다. 빠르긴 하지만 정전 같은 상황에서 데이터가 손상될 위험이 있습니다.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

결과로 나온 정수 1은 NORMAL을 의미합니다. WAL 모드에서는 NORMAL이 권장 설정인데요, 크래시가 나도 커밋된 트랜잭션은 잃지 않고 정전 같은 상황에서만 가장 최근 트랜잭션 일부가 날아갈 수 있는 정도입니다. 대부분의 애플리케이션에서는 이 정도가 가장 적절한 절충점입니다.

OFF는 처음부터 다시 만들 수 있는 일회용 데이터베이스를 채우는 경우가 아니라면 쓰지 마세요.

foreign_keys = ON

이 옵션에서 많이들 헷갈려 합니다. SQLite는 외래 키를 지원하지만 기본값으로는 외래 키 제약이 꺼져 있고, 게다가 커넥션 단위로 적용되는 설정입니다:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Ghosts', 999);

foreign_keys = ON을 켜두면 마지막 insert는 실패합니다 — id 999인 author가 존재하지 않으니까요. 반대로 이 PRAGMA 없이 돌리면 SQLite는 고아 레코드를 아무렇지 않게 써버리고, 몇 달 뒤에야 문제가 터진 걸 발견하게 되죠.

새 커넥션을 열 때마다 PRAGMA foreign_keys = ON;을 가장 첫 번째 문장으로 실행하세요. 대부분의 ORM은 알아서 해주지만, 로우 드라이버를 쓰고 있다면 직접 챙겨야 합니다.

busy_timeout = 5000

SQLite는 한 번에 한 명의 writer만 허용합니다. 첫 번째 커넥션이 트랜잭션 중인데 두 번째 커넥션이 쓰기를 시도하면, 기본 동작은 SQLITE_BUSY를 뱉고 곧바로 포기해버리는 겁니다.

sqlite busy_timeout 설정을 주면 SQLite가 바로 포기하지 않고 잠시 기다렸다가 재시도하도록 만들 수 있습니다:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

값의 단위는 밀리초입니다. 5000이라면 "락이 풀릴 때까지 최대 5초간 기다렸다가 포기한다"는 뜻이죠. WAL 모드와 함께 쓰면 동시 접근이 많은 애플리케이션에서 흔히 보던 database is locked 오류 대부분이 사라집니다.

만약 이 값을 30초 이상으로 올리고 싶어진다면, 진짜 해결책은 타임아웃을 늘리는 게 아니라 트랜잭션을 더 짧게 쪼개는 겁니다.

cache_size

cache_size는 SQLite가 메모리에 유지할 데이터베이스 페이지 수를 지정합니다. 캐시가 클수록 디스크에서 읽는 횟수가 줄어들고, 자주 쓰는 데이터에 대한 쿼리 속도도 빨라지죠.

값을 지정하는 방식은 두 가지입니다.

• 양수 — 페이지 수. 기본 페이지 크기인 4 KB 기준으로 2000은 8 MB입니다.
• 음수 — 키비바이트(KiB) 단위. -20000은 페이지 크기와 무관하게 20 MB입니다.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

음수로 지정하는 방식이 직관적입니다. 페이지 크기를 곱하고 나누는 계산 없이 그냥 "캐시 20MB 줘"라고 말하는 셈이니까요. 작은 앱이라면 20~50MB 정도면 충분하고, 읽기 위주의 큰 데이터베이스라면 더 키워도 좋습니다. synchronous와 마찬가지로 cache_size도 커넥션마다 따로 설정됩니다.

mmap_size

메모리 맵 I/O를 켜면 SQLite가 OS 페이지 캐시에서 데이터베이스 파일을 직접 읽어올 수 있어 복사 과정을 한 단계 건너뛰게 됩니다. 큰 데이터베이스에서 읽기 성능을 끌어올릴 수 있는 옵션이죠:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

256MB라는 뜻입니다. 공간만 충분하다면 SQLite가 데이터베이스를 이 크기만큼 메모리에 매핑합니다. 페이징은 OS가 알아서 처리하기 때문에, 실제로 256MB를 미리 할당하는 건 아닙니다. 그저 그만큼까지 매핑할 수 있게 허용하는 것뿐이죠.

mmap_size는 읽기 위주의 작업에서 진가를 발휘합니다. 작은 데이터베이스에도 부담이 없고요. 기본값이 워낙 보수적으로 잡혀 있어서, 값을 올리는 쪽이 거의 항상 이득입니다.

PRAGMA optimize 사용법

쿼리 플래너는 통계 정보를 바탕으로 인덱스를 고릅니다. 통계가 오래되면 실행 계획도 엉망이 되죠. PRAGMA optimize는 이 통계를 가볍게 갱신해 줍니다:

PRAGMA optimize;

권장 패턴은 수명이 긴 커넥션을 닫기 직전에 실행하는 것입니다. 애플리케이션 종료 시점이나, 커넥션을 한참 쥐고 있던 요청 핸들러가 끝나는 시점이 적당합니다. 보통 수 밀리초 안에 끝나고, 실제로 갱신할 게 있을 때만 작업을 수행하기 때문에 부담도 거의 없습니다.

ANALYZE와는 다릅니다. ANALYZE는 통계를 처음부터 다시 쌓는 무거운 작업이고, optimize는 자주 돌려도 되는 가벼운 사촌쯤이라고 보면 됩니다.

현재 설정값 모두 확인하기

지금 커넥션에 어떤 값들이 설정돼 있는지 보고 싶다면, 값을 대입하지 않고 PRAGMA를 그대로 조회하면 됩니다:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

디버깅할 때 꽤 유용합니다. 다른 드라이버로 접속했을 때 동작이 달라지는 이유는 거의 대부분 PRAGMA 설정 차이 때문이거든요.

참고로 PRAGMA pragma_list;를 쓰면 현재 빌드에서 지원하는 모든 PRAGMA 목록을 한 번에 확인할 수 있습니다:

PRAGMA pragma_list;

외울 필요까지는 없지만, 필요할 때 꺼내 쓰면 좋은 내용입니다.

런타임이 아니라 CREATE 시점에 설정해야 하는 항목

다음 PRAGMA들은 데이터베이스 파일 자체를 구성하는 옵션이라, 테이블이 만들어지기 전에 설정해야만 적용됩니다.

• PRAGMA page_size = 8192; — 디스크에 저장되는 페이지 크기입니다. 기본값은 4096이며 대부분의 워크로드에는 충분합니다. 행(row) 크기가 큰 경우에는 페이지 크기를 키우는 편이 유리합니다.
• PRAGMA encoding = 'UTF-8'; — 텍스트 인코딩 설정입니다.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

기존 데이터베이스에서 page_size를 바꿨다면 VACUUM을 한 번 실행해야 변경이 적용됩니다. 이런 값들은 데이터베이스를 처음 만들 때 한 번 정해두고 더 이상 신경 쓰지 않는 게 정석입니다.

실전 커넥션 초기화 코드

애플리케이션 코드에서는 보통 커넥션을 여는 부분에 이 설정을 모아둡니다. 개념적으로는 이런 모양입니다:

-- 새 연결마다 한 번씩 실행:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- 주기적으로, 또는 연결을 닫기 전에 실행:
PRAGMA optimize;

temp_store = MEMORY로 설정하면 임시 테이블과 인덱스를 RAM에 두기 때문에, 인덱스 없이 정렬하거나 집계해야 하는 쿼리가 훨씬 빨라집니다.

운영환경 체크리스트는 이게 전부입니다. 단 몇 줄만 추가했을 뿐인데, SQLite가 "개발용으로는 괜찮은" 수준에서 "실제 워크로드를 감당할 수 있는" 수준으로 올라섭니다.

다음 주제: 자주 마주치는 에러

PRAGMA를 잘 잡아두더라도 SQLite를 쓰다 보면 단골 에러들을 만나게 됩니다 — database is locked, disk I/O error, constraint failed 같은 것들이죠. 다음 페이지에서는 각 에러가 실제로 무슨 의미이고 어떻게 해결해야 하는지 하나씩 짚어보겠습니다.