프로덕션에서 SQLite: WAL 모드, 동시성 및 VFS 레이어 최적화

프로덕션에서 SQLite: 임베디드 신화를 넘어

SQLite는 애플리케이션과 동일한 서버에 배포될 경우 네트워크 지연을 없애고 메모리 매핑 파일 작업을 통해 서브밀리초 수준의 쿼리 실행을 가능하게 하여 프로덕션 웹 애플리케이션에 적합합니다.

동시성을 위한 Write-Ahead Logging (WAL) 모드 최적화

WAL 모드를 활성화하면 트랜잭션을 별도의 .sqlite-wal 파일에 저장하고 메인 데이터베이스를 직접 수정하지 않음으로써 동시 읽기와 쓰기를 허용합니다.

PRAGMA journal_mode = WAL;

WAL 모드에서는 읽기 작업이 메인 데이터베이스 파일에 접근하고 쓰기 작업은 WAL 파일에 추가되므로 상호 차단을 방지합니다. 하지만 WAL 파일은 시간이 지나면서 커지고, 변경 사항을 메인 데이터베이스에 병합하기 위해 체크포인트를 수행해야 합니다.

SQLite는 네 가지 체크포인트 모드를 제공합니다:

  • PASSIVE: 가능한 많은 페이지를 병합하지만 읽기나 쓰기를 차단하지 않습니다.
  • FULL: 새로운 쓰기를 차단하고 기존 읽기가 완료될 때까지 기다린 후 전체 WAL을 병합합니다.
  • RESTART: FULL과 동일하지만 WAL 파일을 0 크기로 재설정합니다.
  • TRUNCATE: RESTART와 동일하지만 디스크상의 WAL 파일을 0 바이트로 잘라냅니다.

지속적인 읽기 활동으로 인한 무한한 WAL 성장 방지를 위해, PASSIVE 또는 RESTART 체크포인트를 일정 간격으로 백그라운드 스레드에서 명시적으로 관리합니다:

PRAGMA wal_checkpoint(PASSIVE);

WAL 모드와 PRAGMA synchronous = NORMAL을 함께 사용하면 디스크 동기화 오버헤드를 줄일 수 있으며, 매 트랜잭션 커밋마다가 아니라 체크포인트와 같은 중요한 순간에만 동기화합니다.

"NORMAL 모드에서는 데이터베이스 엔진이 디스크에 동기화하는 시점이 체크포인트와 같은 중요한 순간에만 제한됩니다(예: 체크포인트 중). WAL 모드에서는 이는 데이터베이스 손상으로부터 완전히 안전합니다; 서버가 충돌하더라도 WAL에 있는 커밋되지 않은 트랜잭션만 손실되고 데이터베이스 무결성은 유지됩니다." - Original post

"아니요, 이것은 안전하지 않습니다. 이 pragma를 사용하면 최신 커밋된 트랜잭션을 잃을 수 있습니다." - @tnodir

동시성 및 SQLITE_BUSY 오류 처리

WAL 모드가 동시 읽기와 쓰기를 허용하지만, SQLite는 단일 작성자 모델을 강제합니다. 동시에 쓰기를 시도하면 SQLITE_BUSY 오류가 발생합니다.

이를 우아하게 처리하려면:

  1. 예외를 발생시키기 전에 내부적으로 잠금 획득을 재시도하도록 busy timeout을 설정합니다:
PRAGMA busy_timeout = 5000; -- 5 seconds

이 기간 동안 SQLite는 지수 백오프를 사용해 대기하고 재시도함으로써 피크 부하 시 애플리케이션 수준 오류를 감소시킵니다.

  1. 쓰기 작업이 포함된 트랜잭션은 BEGIN IMMEDIATE TRANSACTION;으로 시작해 즉시 예약 잠금을 획득함으로써 교착 상태를 방지합니다:
BEGIN IMMEDIATE;
-- Write operations here
COMMIT;

"개인적으로 sqlite_busy를 해결하는 더 좋은 방법은 애플리케이션 수준에서 단일 작성자를 관리하는 것이라고 생각합니다. 이는 단일 프로세스 환경에서 sqlite_busy를 효과적으로 제거합니다." - @andersmurphy

"제가 권고와 약간 다른 점은 busy_timeout + BEGIN IMMEDIATE 제안입니다. 임베디드 시스템에서는 이것만으로는 충분하지 않습니다... 결국 우리는 한 번에 하나의 작성자 트랜잭션만 진행하도록 하는 애플리케이션 수준의 잠금을 추가해야 했습니다." - @firesteelrain

성능을 위한 메모리 및 캐시 최적화

SQLite의 기본 캐시 크기(보통 2MB)는 프로덕션 워크로드에 충분하지 않습니다. 작업 집합을 메모리에 유지하도록 늘려야 합니다.

PRAGMA cache_size = -64000; -- Allocates approximately 64MB of RAM for cache

메모리 매핑 I/O를 활성화하면 데이터베이스 파일을 애플리케이션의 가상 주소 공간에 직접 매핑하여 OS가 페이지 캐시를 관리하게 하고, 데이터베이스가 mmap 크기보다 작을 경우 디스크 읽기를 포인터 연산으로 전환합니다.

PRAGMA mmap_size = 2147483648; -- Map up to 2GB of the database file into memory

"데이터베이스 크기가 mmap_size보다 작으면 전체 데이터베이스가 메모리에 매핑되어 디스크 읽기가 단순한 포인터 연산으로 전환됩니다." - Original post

"하하, 그래서 그런 거였구나?" - @kev009

내구성과 분산을 위한 맞춤형 VFS 레이어 활용

SQLite의 가상 파일 시스템(VFS) 추상화는 파일 작업을 VFS 모듈에 위임하여 클라우드 환경에 맞춤형 레이어를 구현할 수 있게 합니다.

이를 통해 현대 복제 엔진을 지원합니다:

  • Litestream: 증분 WAL 프레임을 객체 스토리지(예: AWS S3)로 스트리밍하여 시점 복구를 수행하는 별도 프로세스.
  • LiteFS: FUSE 기반 VFS로, SQLite 데이터베이스를 애플리케이션 노드에 분산하고 트랜잭션을 실시간으로 읽기 복제본에 복제합니다.

에페머럴 로컬 스토리지를 사용하는 클라우드 환경(AWS ECS, Kubernetes, Fly.io)에서는 내구성과 고가용성을 위해 VFS 기반 복제 도구를 실행해야 합니다.

"클라우드 환경에서 로컬 디스크 영속성이 일시적(AWS ECS, Kubernetes, Fly.io 등)인 경우, 내구성과 고가용성을 보장하기 위해 VFS 기반 복제 도구를 실행하는 것이 필수입니다." - Original post

"저는 SQLite를 사랑하고 실제로 프로덕션에서 사용하고 싶지만, 클라이언트는 서버가 다운될 때 최소한의 데이터 손실과 다운타임을 기대합니다. 이를 위해 LiteFS 또는 Litestream 위에서 실행하는 것이 설정을 훨씬 복잡하고 검증되지 않은 것으로 만들며, 이는 단순히 Postgres를 사용하는 것에 비해 장점을 어느 정도 무효화합니다." - @mikeocool

"프로덕션에서 SQLite를 사용하고 싶어도, 제가 계속 부딪히는 문제는 실행 중인 데이터베이스와 상호작용할 수 있는 좋은 GUI를 어떻게 제공하느냐입니다. 현재 프로덕션 데이터베이스에서는 dbeaver 같은 툴로 연결해 데이터를 탐색하고 쿼리하거나 가끔 수정을 할 수 있는데, 데이터베이스가 애플리케이션과 같은 VPS에 파일 하나로 존재한다면 이것이 훨씬 더 복잡해질 것 같습니다." - @graboid

"누군가 시도해 본 적 있나요?" - @wg0 (per tenant 데이터베이스를 언급)

프로덕션 구성 청사진 및 고려 사항

데이터베이스 연결을 초기화할 때, 각 연결을 연 직후 다음 pragma 순서를 실행합니다:

-- Enable Write-Ahead Logging
PRAGMA journal_mode = WAL;

-- Reduce synchronization overhead without risking corruption
PRAGMA synchronous = NORMAL;

-- Prevent deadlocks by waiting for locks gracefully
PRAGMA busy_timeout = 5000;

-- Scale cache size to fit active working set (64MB)
PRAGMA cache_size = -64000;

-- Enable memory-mapped I/O for faster reads (1GB)
PRAGMA mmap_size = 1073741824;

-- Enforce foreign key constraints
PRAGMA foreign_keys = ON;

-- Prevent WAL file from growing indefinitely
PRAGMA journal_size_limit = 67108864; -- 64MB

-- Optimize index page allocation and query plans
PRAGMA auto_vacuum = INCREMENTAL;

"이러한 최적화를 기본 SQLite 설정과 비교하는 프로덕션 벤치마크를 포함하면 좋겠습니다." - @madhu_ghalame

"이것이 AI가 생성한 것이라고 꽤 확신하지만, 어쨌든 생각하게 됩니다. 이런 글을 볼 때마다 실제로 SQLite를 프로덕션에서 사용했는지 궁금해집니다. 왜냐하면 항상 WAL 사용 등 성능 최적화에 대한 내용은 나오지만, 그 전에 겪게 되는 불편함이나 문제에 대해서는 언급되지 않기 때문입니다." - @yladiz

"컬럼 정의는 생성 후 alter column과 같은 명령으로 변경할 수 없습니다. 컬럼 정의를 변경하려면 writable_schema pragma를 사용해 기본 스키마를 수동으로 업데이트해야 합니다. 이를 잘못하면 데이터베이스가 손상될 수 있습니다." - @yladiz

"컬럼 타입이 꽤 제한적입니다." - @yladiz

"스키마 마이그레이션을 처리할 옵션이 제한적입니다. 기본적으로 마이그레이션 파일을 서버에 복사해 직접 실행하거나(수동 또는 Ansible 등 사용), 애플리케이션 시작 시 마이그레이션을 실행해야 합니다." - @yladiz

"AI 에이전트 시대는 서버리스 SQL을 필요로 합니다. 비용 효율적인 기술이 인터넷 운영에 매우 중요합니다. 그래서 우리는 Postgres 호환 엔진을 위해 자체 WAL을 작성했으며, 놀라운 점은 로그 포맷보다 fsync 순서에 더 많은 작업이 들어간다는 것이었습니다. 이를 완화하면 충돌 시 마지막 몇 개의 트랜잭션을 잃는 선택이 됩니다. 일부 워크로드에는 괜찮지만, 이는 누군가 의도적으로 내린 결정이어야 합니다." - @kiranravi1995

프로덕션에서 SQLite를 선택해야 할 때

SQLite는 수백 기가바이트 이내의 데이터와 단일 서버에서 초저지연을 요구하는 읽기 중심 애플리케이션에 대해 성능이 뛰어나고 운영이 간단하며 비용 효율적인 선택입니다.

하지만 다음 경우에는 SQLite를 피해야 합니다:

  • 여러 지리적 지역에 걸친 복잡하고 분산된 쓰기 트랜잭션이 필요한 애플리케이션.
  • 수 테라바이트를 초과하는 데이터셋.
  • 스키마 유연성과 견고한 마이그레이션 도구가 중요한 환경.

"애플리케이션이 여러 지리적 지역에 걸친 복잡하고 분산된 쓰기 트랜잭션을 필요로 하거나, 데이터셋이 수 테라바이트를 초과한다면 전통적인 시스템인 PostgreSQL이 올바른 도구입니다. 하지만 시스템이 읽기 중심이고, 수백 기가바이트 이내이며, 초저지연을 요구한다면 애플리케이션 서버에 SQLite를 직접 실행하는 것이 성능이 뛰어나고 운영이 간단하며 비용 효율적인 아키텍처 선택이 됩니다." - Original post

"SQLite가 주목받는 것이 좋지만, 직접 사용해 본 결과 PostgreSQL과 같은 데이터베이스가 가진 많은 기능이 부족하고, 그 중 일부는 실제 프로덕션 환경에서 실제로 중요한 부분이라고 생각합니다." - @yladiz

"내구성을 희생할 준비가 된 경우에만 수행하십시오(즉, 트랜잭션 손실을 감당할 수 있는 경우)." - @andersmurphy

"일부 워크로드에는 괜찮지만, 이는 누군가 의도적으로 내린 결정이어야 합니다." - @kiranravi1995

Summary: SQLite는 WAL 모드를 활성화하고 동시성 설정을 조정하며 맞춤형 VFS 레이어를 활용하여 저지연 앱 서버에 최적화할 수 있지만, 운영상의 트레이드오프가 존재합니다.

Title: 프로덕션에서 SQLite: WAL 모드, 동시성 및 VFS 레이어 최적화

Sources