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)을 사용합니다.

주요 사항

읽기/쓰기 잠금:
1. 읽기(Shared) 잠금: 여러 프로세스가 동시에 읽을 수 있습니다.
2. 쓰기(Exclusive) 잠금: 쓰기 작업이 진행되는 동안에는 다른 모든 읽기/쓰기 작업이 대기해야 합니다.
트랜잭션 모델:
1. 즉시(Deferred) 트랜잭션: `BEGIN` 또는 `BEGIN TRANSACTION` 명령 시 잠금을 얻지 않고, 첫 번째 쓰기 작업 시 쓰기 잠금을 시도합니다.
2. 즉시(Immediate) 트랜잭션: `BEGIN IMMEDIATE TRANSACTION` 명령 시 즉시 쓰기 잠금을 시도합니다.
3. 배타적(Exclusive) 트랜잭션: `BEGIN EXCLUSIVE TRANSACTION` 명령 시 즉시 배타적 쓰기 잠금을 시도하며, 다른 모든 읽기/쓰기 작업을 막습니다.
WAL 모드 (Write-Ahead Logging):
1. SQLite 3.7.0부터 지원되는 WAL 모드는 동시성을 크게 향상시킵니다.
2. 쓰기 작업은 WAL 파일에 기록되고, 읽기 작업은 데이터베이스 파일과 WAL 파일을 동시에 읽습니다.
3. 이로 인해 쓰기 작업 중에도 읽기 작업이 가능하며, 쓰기 작업끼리는 여전히 배타적 잠금을 사용합니다.

예제:

-- 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 설정, 쿼리 최적화가 중요합니다.

주요 사항

인덱스 활용:
1. `CREATE INDEX`: `WHERE` 절, `JOIN` 조건, `ORDER BY`, `GROUP BY`에 사용되는 컬럼에 인덱스 생성.
2. `ANALYZE`: 쿼리 옵티마이저가 최적의 실행 계획을 선택하도록 테이블 및 인덱스 통계 정보 업데이트.
PRAGMA 명령:
1. `PRAGMA journal_mode = WAL;`: 동시성 및 복구 성능 향상 (가장 중요).
2. `PRAGMA synchronous = NORMAL;`: 디스크 쓰기 동기화 레벨 조정. `FULL`은 안전하지만 느리고, `OFF`는 빠르지만 위험. `NORMAL`이 대부분의 경우 적합.
3. `PRAGMA cache_size = N;`: 페이지 캐시 크기 설정 (페이지 단위).
4. `PRAGMA temp_store = MEMORY;`: 임시 테이블 및 인덱스를 메모리에 저장하여 성능 향상 (RAM이 충분할 경우).
쿼리 최적화:
1. `EXPLAIN QUERY PLAN`: 쿼리 실행 계획을 분석하여 비효율적인 부분 식별.
2. 서브쿼리, 조인, 집계 함수 등 SQL 문법의 올바른 활용.
VACUUM 및 VACUUM INTO:
1. `VACUUM`: 삭제된 데이터로 인해 생긴 빈 공간을 회수하여 데이터베이스 파일 크기를 줄이고 성능을 개선.
2. `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는 파일 기반이므로, 네트워크를 통해 여러 클라이언트가 동시에 접근하여 데이터를 공유하는 데 적합하지 않습니다. (파일 공유 프로토콜을 사용해야 하지만, 잠금 문제 발생 가능)
제한된 동시성:
1. WAL 모드를 사용해도 쓰기 작업은 여전히 직렬화됩니다. 고빈도 쓰기 작업이 동시에 발생하는 환경에는 부적합합니다.
2. 수천 개 이상의 동시 읽기/쓰기 요청에는 적합하지 않습니다.
스케일 아웃(Scale-out) 불가: 단일 파일 시스템에 의존하므로, 여러 서버에 걸쳐 데이터를 분산하고 확장하는 것이 불가능합니다.
대규모 데이터 처리의 한계: 수 테라바이트급 데이터를 처리하기에는 성능 및 관리 측면에서 한계가 있습니다.
보안 기능 부족: 사용자 관리, 역할 기반 권한 제어 등 엔터프라이즈급 데이터베이스가 제공하는 강력한 보안 기능이 부족합니다.
제한된 SQL 기능: 저장 프로시저, 트리거의 복잡한 구현, 특정 고급 SQL 함수 등이 부족할 수 있습니다. (그러나 대부분의 표준 SQL은 지원)
예제:
1. 웹 애플리케이션의 세션 데이터 저장 (`/tmp` 디렉토리에 SQLite 사용) → 적합.
2. 수천 명의 동시 사용자가 초당 수백 번씩 트랜잭션을 발생시키는 대규모 웹 서비스의 메인 데이터베이스 → 부적합. PostgreSQL, MySQL 등 서버 기반 RDBMS가 적합.
3. 모바일 앱의 로컬 데이터 저장 → 매우 적합.
4. 관련 지식: 분산 데이터베이스, 클라이언트-서버 아키텍처, 데이터베이스 스케일링 전략.

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

요점 정리

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 연동.