Skip to content

SQLite

SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 영어권에서는 '에스큐엘라이트(ˌɛs ˌkjuː ˈɛlaɪt)'또는 '시큐엘라이트(siːˈkwɛlaɪt)'라고 읽는다.

일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다.

구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

Categories

Projects

ETC

  • sqlite3 fiddle - 웹에서 테스트 해보는 SQLite
  • XLite - 엑셀 파일을 가상 테이블로 쿼리하는 SQLite 확장

How to compile

MacOSX에서는 autotools를 별도로 설치해야 한다. 

$ brew install automake autoconf libtool m4

Command-line tool

sqlite3명령어 유틸리티를 사용할 경우 아래와 같은 명령을 입력할 수 있다.

  • .help: 도움말 출력.
  • .tables: 테이블 목록 출력.
  • .schema [테이블명]: 테이블의 스키마를 출력.
  • .quit: 프로그램 종료.

Data Type

아래와 같은 자료형을 사용할 수 있다.

  • NULL: 널값.
  • INTEGER: 1,2,3,4,6,8bytes의 정수값. (자동증가 시킬 경우 INTEGER PRIMARY KEY AUTOINCREMENT 로 표기)
  • REAL: 8bytes의 부동소수점값.
  • TEXT: UTF-8, UTF-16BE, UTF-16LE 인코딩 문자열.
  • BLOB: 입력된 그대로 저장, 바이너리 파일 등.

C API Reference

Example

CREATE TABLE IF NOT EXISTS synset(idx INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
synset이름의 테이블이 존재하지 않다면 테이블을 생성하고, idx이름의 Primary key(Integer타입의 자동증가)와 name 문자열 컬럼을 생성한다.
DROP TABLE IF EXISTS synset;
synset이름의 테이블이 존재한다면 테이블을 제거한다.

문자열 연결하기

"\r\n" 문자를 "/" 으로 변환하기는 \r = char(13), \n = char(10) 이므로: 

select replace(replace(name, char(13), ''), char(10), '/') as name
from scrap_grade;

"\r\n" 를 하나의 문자로 만들려면 char(13) || char(10) 으로 문자를 연결할 수 있다. 

select replace(name, char(13) || char(10), '/') as name
from scrap_grade;

SQLite에 1분 내로 10억 Row 추가하기

SQLite는 싱글 커넥션 DB가 아님

SQLite에 대해 사람들이 자주 오해하는 점 중 하나는 “SQLite는 단일 연결만 허용하는 데이터베이스” 라고 생각해서 사람들이 사용하지 않게 되는 것

이는 혼란스럽기도 하고(여기서 "연결"의 의미?), 여러 측면에서 틀린 주장임

  • 읽기 작업 (Read Operations)
    • SQLite는 여러 개의 동시 읽기 작업을 완벽하게 지원함
    • 여러 “연결”에서 동시에 데이터를 읽을 수 있으며, 이 과정에서 충돌이나 문제가 발생하지 않음
  • 쓰기 작업 (Write Operations)
    • SQLite는 쓰기 작업 시 DB 레벨의 쓰기 잠금 (Write Lock) 을 사용함
    • 동시에 여러 개의 쓰기 작업을 허용하지 않음, 따라서 한 번에 한 개의 “연결”만 쓰기 작업을 수행할 수 있음
    • 일반적으로 이것이 문제로 작용하지 않는 이유는 IMMEDIATE TRANSACTION을 시작할 수 있기 때문임
    • 이 경우 SQLite는 쓰기 잠금을 획득하기 위해 큐에서 재시도를 할 수 있음
      • (이 방식은 쓰기 잠금이 해제될 때 자동으로 쓰기 작업이 수행될 수 있게 함)

5.0 데이터베이스 파일에 쓰기

데이터베이스에 쓰려면 먼저 위에서 설명한 대로 프로세스가 SHARED 락을 획득해야 합니다. SHARED 락을 획득한 후에는 RESERVED 락을 획득해야 합니다. RESERVED 락은 프로세스가 미래의 어느 시점에 데이터베이스에 쓰겠다는 신호를 보냅니다. 한 번에 한 프로세스만 RESERVED 락을 보유할 수 있습니다. 그러나 다른 프로세스는 RESERVED 락이 유지되는 동안 데이터베이스를 계속 읽을 수 있습니다.

SQLite on the Server 에 대한 오해

많은 개발자들이 서버에서 SQLite를 사용하는 것은 소규모 애플리케이션에나 어울린다고 생각

그 이유는 다음과 같음:

  • 낮은 인프라 비용: 별도의 데이터베이스 서버가 필요하지 않음 (단일 파일로 운영)
  • 개발 및 테스트 용이: 동일한 DB 파일을 클라이언트와 서버에서 활용 가능
  • 관리 부담 최소화: 복잡한 설정이나 데몬 관리가 불필요
  • 높은 신뢰성: SQLite는 전 세계에서 가장 많이 배포된 DB이며, 강력한 내구성을 가짐

LiteFS, Litestream, rqlite, Dqlite, Bedrock 등의 도구가 SQLite에 복제(replication) 및 고가용성(HA)을 추가하여 소규모 배포에 적합하게 만들어줌. 하지만 이 글에서는 소규모가 아닌 초대형 애플리케이션(Hyper-Scale)에 적합한 SQLite의 가능성을 탐구함

기존의 대형 데이터베이스 확장 문제

  • 대형 애플리케이션은 보통 PostgreSQL, MySQL을 단일 DB로 유지하기 어려워 샤딩(Sharding)된 데이터베이스를 사용
  • 예: Cassandra, ScyllaDB, DynamoDB, Vitess(샤딩된 MySQL), Citus(샤딩된 Postgres)
  • 샤딩된 DB는 다음과 같은 장점을 가짐:
    • 데이터 파티션을 통해 배치 읽기(Batch Read) 최적화
    • 수평적 확장(Scalability) 가능
    • 고속 쓰기 성능 제공

하지만 현재의 파티셔닝 솔루션에는 다음과 같은 단점이 존재

  • 고정된 스키마(Rigid Schemas): MySQL이나 Postgres처럼 유연한 쿼리를 지원하지 않음
  • 스키마 변경이 어려움: 인덱스 추가나 관계 변경 시 운영 부담이 큼
  • 복잡한 크로스 파티션 연산: ACID 트랜잭션을 유지하기 어렵고, 두 단계 커밋(2PC) 같은 복잡한 기법 필요
  • 데이터 불일치 문제: 파티션 간 강력한 데이터 제약 조건을 적용하기 어렵고, 데이터 정합성이 깨질 가능성이 높음

SQLite 기반 하이퍼스케일 데이터베이스의 가능성

  • Cloudflare Durable Objects와 Turso는 SQLite를 기반으로 하이퍼스케일 애플리케이션을 설계하는 방식을 보여줌
  • 이들 시스템은 다음과 같은 강점을 제공함:
    • 동적 확장(Dynamic Scaling): 엔터티(Entity)별로 데이터베이스를 생성하여 인프라 복잡성을 감소
    • 무제한의 저비용 데이터베이스: 기존 샤딩처럼 데이터 파티션을 강제하지 않고, 필요할 때마다 새로운 SQLite 인스턴스를 생성 가능
    • 글로벌 분산(Global Distribution): 데이터베이스를 사용자 가까운 위치에 배치하여 성능 향상
    • 내장된 복제 및 내구성(Built-in Replication & Durability): 기존 SQLite와 달리 다중 지역에서 데이터를 복제하여 고가용성 유지
  • SQLite를 활용한 샤딩 대체 방식 (Cloudflare Durable Objects & Turso 활용)
    • 기존 샤딩 방식에서는 단일 데이터베이스 파티션에 여러 채팅 로그 저장
    • SQLite를 사용하면 각 채널별로 독립적인 SQLite 데이터베이스를 생성하여 보다 유연한 스키마 활용 가능
    • 예제 구조
      • 기존 샤딩: 채팅 로그 테이블 + 파티션 키
      • SQLite 기반: 채널별 개별 SQLite DB (채팅 로그, 참여자, 반응 정보 포함)
  • SQLite를 사용한 이 방식의 장점은 다음과 같음:
    • 로컬 ACID 트랜잭션 유지: 크로스 파티션 문제 없이 개별 DB 내에서 트랜잭션 수행 가능
    • 고성능 I/O: SQLite는 단일 파일 DB이므로, 읽기 및 쓰기 성능이 매우 뛰어남
    • SQL 확장 기능 활용 가능:
      • FTS5(Full-Text Search): 검색 성능 향상
      • JSON1: JSON 데이터 저장 및 쿼리 지원
      • R*Tree, SpatiaLite: 공간 데이터 활용 가능
    • SQL 마이그레이션 지원: Prisma, Drizzle 같은 기존 마이그레이션 도구와 호환 가능
    • 느린(Lazy) 스키마 마이그레이션 지원:
      • 마이그레이션 실행이 즉시 필요하지 않고, SQLite 인스턴스를 열 때 가벼운 마이그레이션을 수행하는 방식 사용 가능

서버에서 SQLite를 사용할 때의 한계점

  • 오픈소스, 자체 호스팅 가능한 솔루션 부족
  • 크로스 데이터베이스 쿼리 미지원 → 분석을 위해서는 별도의 데이터 레이크 필요
  • 제한된 데이터베이스 툴링 (SQL 브라우저, ETL 파이프라인, 모니터링, 백업)
    • StarbaseDB가 Cloudflare Durable Objects + SQLite 기반으로 이 문제 해결 중
  • 통합된 표준 프로토콜 부족
    • PostgreSQL, MySQL, Cassandra는 표준화된 프로토콜을 사용하지만, SQLite 서버는 아직 표준화된 네트워크 프로토콜이 부족
  • 하이퍼스케일에서 SQLite를 사용한 대규모 사례 부족
    • Cassandra, DynamoDB 같은 사례 연구가 부족하지만, 시간이 지나면서 변화할 가능성이 있음

결론: SQLite는 단순한 로컬 DB가 아닌, 초대형 애플리케이션에서도 강력한 옵션

  • SQLite는 단순한 소규모 프로젝트용 DB가 아니라, 초대형 애플리케이션에서도 기존 샤딩 방식을 대체할 수 있는 강력한 도구
  • Cloudflare Durable Objects & Turso를 활용하면, 데이터베이스를 엔터티 단위로 분할하여 SQL의 강력한 기능과 ACID 트랜잭션을 유지하면서 확장 가능
  • 전통적인 샤딩된 데이터베이스보다 더 유연하고 관리가 쉬운 대안으로 자리 잡을 가능성이 높음

2025년을 위한 7개의 데이터베이스 중 하나

  • Database#2025년을 위한 7개의 데이터베이스 (GeekNews) 참조
  • SQLite는 "로컬 우선" 데이터베이스로 독립적으로 실행 가능
    • 클라이언트-서버 모델을 벗어나 애플리케이션과 같은 환경에서 실행됨
    • 예시: WhatsApp과 Signal은 기기 내부에 SQLite를 사용하여 채팅 데이터를 저장함
  • SQLite의 발전된 활용 사례
    • 기본 ACID 준수 데이터베이스 이상의 창의적인 사용 가능
    • 새로운 도구와 확장 기능:
      • Litestream: SQLite의 스트리밍 백업 제공
      • LiteFS: 분산 액세스를 지원하여 더욱 유연한 토폴로지 구현
      • CR-SQLite: CRDT(Conflict-free Replicated Data Types)를 사용해 변경 세트를 병합할 때 충돌 해결 필요성을 제거
  • SQLite의 인기 재조명
    • Ruby on Rails 8.0 덕분에 다시 주목받고 있음
    • 37signals: SQLite를 기반으로 Rails 모듈(Solid Queue 등)을 개발
      • Rails의 다중 SQLite 데이터베이스 관리 지원 (database.yml)
    • Bluesky: 개인 데이터 서버(Personal Data Servers)로 사용자마다 개별 SQLite 데이터베이스 사용
  • SQLite 활용 학습 권장
    • SQLite를 이용한 로컬 중심 아키텍처 실험
    • 기존의 PostgreSQL 기반 클라이언트-서버 모델을 SQLite로 대체할 수 있는지 시도

Bloom filter로 10배 빨라진 SQLite

  • Bloom 필터로 10배 빨라진 SQLite | GeekNews
  • How bloom filters made SQLite 10x faster - blag
  • SQLite는 디스크에 B-트리 구조로 저장되는 행 기반 저장소로, VDBE라는 가상 머신을 사용하여 쿼리를 실행함. 플랫폼에 구애받지 않고 단일 스레드로 거의 모든 환경에서 실행 가능함
  • 일반적인 용도의 데이터베이스이지만 OLTP 작업에 뛰어남. 2015년 버팔로 대학 연구진은 대부분의 쿼리가 간단한 키-값 조회와 복잡한 OLAP 쿼리임을 발견함
  • 위스콘신-매디슨 대학 연구진은 분석 쿼리를 더 빠르게 만들기 위해 노력함. DuckDB와 Star Schema Benchmark(SSB)를 사용하여 성능을 비교함

원인

  • SQLite가 느린 이유를 파악하기 위해 VDBE_PROFILE 옵션을 사용하여 VDBE의 각 명령어가 소모하는 CPU 사이클을 측정함.
  • SeekRowID와 Column이라는 두 개의 opcode가 주요 원인으로 밝혀짐.

데이터베이스 조인

  • 데이터베이스가 조인을 구현하는 방법에는 중첩 루프 조인, 해시 조인, 정렬-병합 조인이 있음.
  • SQLite는 가장 간단한 "중첩 루프 조인"을 사용함. 이는 B-트리 탐색과 유사하여 비용이 많이 듦.

조인 최적화의 중요성

  • 조인 연산에서 테이블의 순서가 중요함. 순서를 바꾸면 연산 횟수를 크게 줄일 수 있음. 이는 NP-난해 문제임.
  • 중첩 루프 조인보다 더 나은 두 가지 조인 알고리듬이 있지만, 해시 조인은 메모리를 많이 소모하며 SQLite는 메모리 제약 환경에서 주로 실행됨.
  • 연구진은 Bloom 필터를 사용하여 공간 효율성을 높이고 CPU 캐시 라인에 맞도록 함. Filter와 FilterAdd라는 두 개의 opcode를 추가함.

결과

  • 최적화 후 CPU 사이클 분석에서 큰 블루 바가 거의 사라짐.
  • SQLite는 7배에서 10배 더 빨라짐. 이 연구 결과는 SQLite v3.38.0에 적용됨.
  • Bloom 필터는 최소한의 메모리 오버헤드로 SQLite의 간단한 구현과 잘 맞고 기존 쿼리 엔진 내에서 작동함

Troubleshooting

Integration bash script

sqlite3를 BASH 스크립트에 통합할 경우 대화형 프로그램이 실행되어 스크립트가 중간에 멈추는 현상이 발생된다. 이 현상을 방지하기 위해, 아래와 같이 적용하면 된다. 

$ cat > test.sql << EOF
CREATE TABLE log_entry ();
.separator "\t"
.import logfile.log log_entry
.quit
EOF
$ sqlite3 db.sqlite < test.sql

Libtool library used but LIBTOOL is undefined

Libtool#Libtool library used but LIBTOOL is undefined 항목 참조.

Local Download

C source code as an amalgamation, version 3.11.1.
Sqlite-amalgamation-3110100.zip (sha1: 868a00d2adcc8b3803c46d461d7d8d23b2bfccbc)
C source code as an amalgamation. Also includes a "configure" script and TEA makefiles for the TCL Interface.
Sqlite-autoconf-3110100.tar.gz (sha1: c4b4dcd735a4daf5a2e2bb90f374484c8d4dad29)

See also

Favorite site