Sqlite 개발자가 알아야할 필수지식

SQLite 전문가를 위한 필수 지식

SQLite 전문가는 경량 데이터베이스 시스템인 SQLite의 독특한 아키텍처와 제약 사항을 깊이 이해하고, 이를 바탕으로 효율적이고 안정적인 애플리케이션을 설계 및 개발할 수 있어야 합니다.

SQLite 핵심 개념 및 아키텍처

요점 정리

SQLite는 서버 없이 파일 기반으로 작동하는 임베디드 데이터베이스입니다.
애플리케이션 내부에 직접 통합되어, 별도의 데이터베이스 서버 프로세스나 네트워크 통신 없이 로컬 파일에 데이터를 저장하고 관리합니다.

주요 사항

서버리스(Serverless): 별도의 데몬 프로세스가 필요 없고, 애플리케이션 프로세스 내에서 직접 데이터베이스 엔진이 동작합니다.
제로 컨피규레이션(Zero-Configuration): 설치 및 설정이 거의 필요 없으며, 데이터베이스는 단일 파일로 존재합니다.
트랜잭션(Transactional): ACID(원자성, 일관성, 격리성, 지속성) 속성을 완벽하게 지원합니다.
내장형(Embedded): 모바일 앱, 데스크톱 앱, 임베디드 시스템 등 다양한 애플리케이션에 내장되어 사용됩니다.

데이터베이스 파일: 모든 데이터베이스 객체(테이블, 인덱스, 뷰 등)가 하나의 단일 파일(`.db`, `.sqlite`, `.sqlite3` 등)에 저장됩니다.

예제:

-- SQLite 데이터베이스 파일 생성 및 연결 (Python 예시)
import sqlite3
conn = sqlite3.connect('my_application.db')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)")
conn.close()

관련 지식: 파일 시스템 동작 방식, ACID 트랜잭션 속성.

데이터 타입 및 저장 방식

요점 정리

SQLite는 동적 타입 시스템을 사용하며, 이는 다른 관계형 데이터베이스와 차별화되는 중요한 특징입니다.
선언된 데이터 타입과 무관하게 모든 값을 내부적으로 BLOB, INTEGER, REAL, TEXT, NULL의 5가지 스토리지 클래스 중 하나로 저장합니다.

주요 사항

유연한 타입 시스템:
1. 타입 어피니티(Type Affinity): 컬럼 선언 시 지정된 타입(예: `INTEGER`, `TEXT`, `REAL`, `BLOB`, `NUMERIC`)은 '선호하는' 스토리지 클래스를 나타낼 뿐, 다른 타입의 값을 저장하는 것을 막지 않습니다.
2. `INTEGER`: 정수.
3. `REAL`: 부동 소수점 숫자.
4. `TEXT`: 문자열 (UTF-8, UTF-16).
5. `BLOB`: 원시 바이너리 데이터.
6. `NULL`: NULL 값.
PRIMARY KEY 동작 방식:
1. `INTEGER PRIMARY KEY`: 선언 시 해당 컬럼은 rowid의 별칭이 됩니다. 이는 내부적으로 인덱스 탐색 없이 매우 빠르게 레코드를 찾을 수 있게 합니다.
2. `AUTOINCREMENT`: `INTEGER PRIMARY KEY`와 함께 사용 시, 삭제된 rowid를 재사용하지 않고 항상 새로운 고유한 rowid를 할당합니다.

예제:

CREATE TABLE products (
    id INTEGER PRIMARY KEY, -- id는 INTEGER 값을 저장하고 rowid의 별칭이 됨
    name TEXT,              -- 문자열 선호
    price REAL,             -- 실수 선호
    created_at TIMESTAMP    -- TIMESTAMP로 선언해도 내부적으로 TEXT 또는 INTEGER로 저장될 수 있음
);

INSERT INTO products (name, price, created_at) VALUES ('Laptop', 1200.50, '2025-07-01 10:00:00');
INSERT INTO products (name, price, created_at) VALUES ('Mouse', 25, 1678886400); -- 정수 형태의 타임스탬프도 저장 가능

관련 지식: 관계형 데이터베이스의 정적 타입 시스템과의 비교, 데이터 직렬화/역직렬화.

동시성 및 잠금(Concurrency & Locking)

요점 정리: SQLite는 단일 파일에 데이터를 저장하므로, 동시성 제어 방식이 다른 RDBMS와 다릅니다. 기본적으로 데이터베이스 전체에 대한 파일 레벨 잠금(File-level Locking)을 사용합니다.
주요 사항:
- 읽기/쓰기 잠금:
  - 읽기(Shared) 잠금: 여러 프로세스가 동시에 읽을 수 있습니다.
  - 쓰기(Exclusive) 잠금: 쓰기 작업이 진행되는 동안에는 다른 모든 읽기/쓰기 작업이 대기해야 합니다.
- 트랜잭션 모델:
  - 즉시(Deferred) 트랜잭션: `BEGIN` 또는 `BEGIN TRANSACTION` 명령 시 잠금을 얻지 않고, 첫 번째 쓰기 작업 시 쓰기 잠금을 시도합니다.
  - 즉시(Immediate) 트랜잭션: `BEGIN IMMEDIATE TRANSACTION` 명령 시 즉시 쓰기 잠금을 시도합니다.
  - 배타적(Exclusive) 트랜잭션: `BEGIN EXCLUSIVE TRANSACTION` 명령 시 즉시 배타적 쓰기 잠금을 시도하며, 다른 모든 읽기/쓰기 작업을 막습니다.
- WAL 모드 (Write-Ahead Logging):
  - SQLite 3.7.0부터 지원되는 WAL 모드는 동시성을 크게 향상시킵니다.
  - 쓰기 작업은 WAL 파일에 기록되고, 읽기 작업은 데이터베이스 파일과 WAL 파일을 동시에 읽습니다.
  - 이로 인해 쓰기 작업 중에도 읽기 작업이 가능하며, 쓰기 작업끼리는 여전히 배타적 잠금을 사용합니다.
예제:

-- WAL 모드 활성화 (권장)
PRAGMA journal_mode = WAL;

-- 트랜잭션 시작 (기본 Deferred)
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

관련 지식: 운영체제의 파일 잠금 메커니즘, 트랜잭션 격리 수준, 동시성 제어.

성능 최적화 및 튜닝

요점 정리: SQLite는 기본적으로 빠르지만, 특정 시나리오에서는 명시적인 튜닝이 필요합니다. 인덱스 활용, 적절한 PRAGMA 설정, 쿼리 최적화가 중요합니다.
주요 사항:
- 인덱스 활용:
  - `CREATE INDEX`: `WHERE` 절, `JOIN` 조건, `ORDER BY`, `GROUP BY`에 사용되는 컬럼에 인덱스 생성.
  - `ANALYZE`: 쿼리 옵티마이저가 최적의 실행 계획을 선택하도록 테이블 및 인덱스 통계 정보 업데이트.
- PRAGMA 명령:
  - `PRAGMA journal_mode = WAL;`: 동시성 및 복구 성능 향상 (가장 중요).
  - `PRAGMA synchronous = NORMAL;`: 디스크 쓰기 동기화 레벨 조정. `FULL`은 안전하지만 느리고, `OFF`는 빠르지만 위험. `NORMAL`이 대부분의 경우 적합.
  - `PRAGMA cache_size = N;`: 페이지 캐시 크기 설정 (페이지 단위).
  - `PRAGMA temp_store = MEMORY;`: 임시 테이블 및 인덱스를 메모리에 저장하여 성능 향상 (RAM이 충분할 경우).
- 쿼리 최적화:
  - `EXPLAIN QUERY PLAN`: 쿼리 실행 계획을 분석하여 비효율적인 부분 식별.
  - 서브쿼리, 조인, 집계 함수 등 SQL 문법의 올바른 활용.
- VACUUM 및 VACUUM INTO:
  - `VACUUM`: 삭제된 데이터로 인해 생긴 빈 공간을 회수하여 데이터베이스 파일 크기를 줄이고 성능을 개선.
  - `VACUUM INTO 'new_db.sqlite';`: 데이터베이스를 새로운 파일로 압축하여 더 효율적인 저장 공간을 확보. (PostgreSQL의 VACUUM FULL과 유사)
예제:

-- 인덱스 생성
CREATE INDEX idx_products_name ON products (name);

-- 통계 정보 업데이트
ANALYZE;

-- 쿼리 실행 계획 확인
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice';

-- 캐시 크기 설정
PRAGMA cache_size = 20000; -- 20000 페이지 (각 페이지는 보통 4KB)

관련 지식: SQL 쿼리 최적화 기법, 인덱스 작동 방식, 파일 시스템 캐싱.

제약 사항 및 한계점

요점 정리: SQLite는 경량 데이터베이스로서 많은 장점이 있지만, 대규모 동시성 처리, 고성능 쓰기 트래픽, 네트워크 기반 접근 등 엔터프라이즈급 RDBMS가 필요한 시나리오에서는 명확한 한계를 가집니다.
주요 사항:
- 네트워크 접근 불가: SQLite는 파일 기반이므로, 네트워크를 통해 여러 클라이언트가 동시에 접근하여 데이터를 공유하는 데 적합하지 않습니다. (파일 공유 프로토콜을 사용해야 하지만, 잠금 문제 발생 가능)
- 제한된 동시성:
  - WAL 모드를 사용해도 쓰기 작업은 여전히 직렬화됩니다. 고빈도 쓰기 작업이 동시에 발생하는 환경에는 부적합합니다.
  - 수천 개 이상의 동시 읽기/쓰기 요청에는 적합하지 않습니다.
- 스케일 아웃(Scale-out) 불가: 단일 파일 시스템에 의존하므로, 여러 서버에 걸쳐 데이터를 분산하고 확장하는 것이 불가능합니다.
- 대규모 데이터 처리의 한계: 수 테라바이트급 데이터를 처리하기에는 성능 및 관리 측면에서 한계가 있습니다.
- 보안 기능 부족: 사용자 관리, 역할 기반 권한 제어 등 엔터프라이즈급 데이터베이스가 제공하는 강력한 보안 기능이 부족합니다.
- 제한된 SQL 기능: 저장 프로시저, 트리거의 복잡한 구현, 특정 고급 SQL 함수 등이 부족할 수 있습니다. (그러나 대부분의 표준 SQL은 지원)
예제:
웹 애플리케이션의 세션 데이터 저장 (`/tmp` 디렉토리에 SQLite 사용) → 적합.
수천 명의 동시 사용자가 초당 수백 번씩 트랜잭션을 발생시키는 대규모 웹 서비스의 메인 데이터베이스 → 부적합. PostgreSQL, MySQL 등 서버 기반 RDBMS가 적합.
모바일 앱의 로컬 데이터 저장 → 매우 적합.
관련 지식: 분산 데이터베이스, 클라이언트-서버 아키텍처, 데이터베이스 스케일링 전략.

활용 시나리오 및 최적 사용법

요점 정리: SQLite의 제약 사항을 이해하고, 그 장점을 최대한 활용할 수 있는 시나리오에 적용하는 것이 전문가의 역량입니다.
주요 사항:
- 모바일 및 데스크톱 애플리케이션: 데이터 저장 및 관리 (안드로이드, iOS, Electron 앱 등).
- 임베디드 시스템: 라우터, IoT 기기, 셋톱박스 등 제한된 자원에서 데이터 관리.
- 웹 브라우저의 로컬 스토리지: IndexedDB의 백엔드 등으로 활용될 수 있음.
- 단일 사용자 또는 소규모 다중 사용자 환경: 파일 공유 기반의 소규모 데이터 저장.
- 개발 및 테스트 환경: 백엔드 데이터베이스를 대체하여 빠른 개발 및 테스트 수행.
- 데이터 아카이빙 및 변환: CSV/JSON 데이터를 SQLite로 로딩하여 간단한 쿼리 및 변환 작업.
- CLI 도구 및 스크립팅: 간단한 데이터 처리 자동화.
예제:
안드로이드 앱에서 사용자 설정, 북마크, 로컬 콘텐츠 목록 저장.
크롬 확장 프로그램에서 사용자 데이터 저장.
Python 스크립트에서 CSV 파일의 내용을 SQLite로 가져와 분석.
관련 지식: 다양한 프로그래밍 언어에서의 SQLite 연동 방법 (Python `sqlite3`, Java `JDBC-SQLite`, C# `Microsoft.Data.Sqlite`).

확장 (Extensions) 및 가상 테이블 (Virtual Tables)

요점 정리: SQLite는 핵심 기능을 넘어선 추가적인 기능을 확장 모듈 형태로 제공하며, 가상 테이블을 통해 외부 데이터 소스를 마치 SQLite 테이블처럼 다룰 수 있습니다.
주요 사항:
- 내장 확장:
  - FTS (Full-Text Search): 전문 검색 기능 (FTS3, FTS4, FTS5).
  - R-Tree: 공간 인덱싱 (GIS 데이터).
  - JSON1: JSON 데이터를 직접 처리하는 함수 및 연산자.
- 사용자 정의 함수(UDF): C, Python 등 호스트 언어로 사용자 정의 함수를 작성하여 SQL 쿼리 내에서 활용.
- 가상 테이블(Virtual Tables):
  - 데이터베이스 파일에 실제 데이터가 저장되지 않고, 특정 모듈(예: CSV 파일, 외부 API)을 통해 데이터를 제공받아 테이블처럼 작동.
  - `sqlite-vss`: 벡터 검색을 위한 가상 테이블.
예제:

-- FTS5 전문 검색 테이블 생성 및 활용
CREATE VIRTUAL TABLE articles USING fts5(title, content);
INSERT INTO articles (title, content) VALUES ('SQLite Overview', 'SQLite is a lightweight, serverless database.');
SELECT * FROM articles WHERE articles MATCH 'database lightweight';

-- JSON1 확장 사용
SELECT json_extract('{"name": "Alice", "age": 30}', '$.name'); -- 결과: "Alice"

관련 지식: C/C++ 프로그래밍 (SQLite 코어 확장 시), 특정 데이터 포맷(JSON, CSV) 및 API 연동.