SQLite와 PostgreSQL, 언제 바꿀까

주머니 수첩과 자료실

SQLite는 앱에 연결하는 라이브러리고 데이터베이스 전체가 디스크의 파일 하나에 들어 있어. 데이터베이스를 연다는 말이 곧 파일을 연다는 뜻이야. PostgreSQL은 소켓을 통해 접속하는 별도 서버 프로세스야. 둘 다 제자리에서는 훌륭하니 “무엇이 더 좋은가?”보다 “지금 작업에는 무엇이 맞는가?”를 물어야 해.

SQLite가 알맞은 경우

모바일 앱, CLI, 개인용 데스크톱 앱처럼 한 번에 한 프로세스만 값을 써.
데이터가 앱과 같은 기기에 머물러.
운영 부담을 0으로 만들고 싶어. 별도 서버, 네트워크 보안, 서버 백업과 업그레이드가 필요 없어.
서버를 띄우지 않고도 실제 SQL 데이터베이스가 필요한 시험 환경이야.

PostgreSQL로 옮길 신호

여러 앱 서버가 동시에 값을 써야 해.
복제, 특정 시점 복구, 대기 서버가 필요해.
JSONB, 전문 검색, pgvector, PostGIS, 행 수준 보안이 필요해.
데이터가 몇 GB를 넘거나 쓰기 속도가 한 장비의 한계를 넘어.

대부분의 이전은 단순해

SQL 문법은 상당 부분 함께 쓸 수 있어. 다만 SQLite는 자료형을 동적으로 다루고 PostgreSQL은 엄격하게 검사한다는 차이를 기억해야 해. SQLite의 AUTOINCREMENT는 PostgreSQL의 GENERATED ALWAYS AS IDENTITY로 옮기고, 서로 다른 날짜 함수도 바꿔야 해. 이런 차이를 빼면 대개 일대일로 대응해.

Code

설정 없이 시작하는 SQLite·bash

sqlite3 myapp.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);"
sqlite3 myapp.db "INSERT INTO notes (body) VALUES ('Hello world');"
# DB 전체가 myapp.db 파일. 복사하고 메일 보내도 동작.

SQLite에는 없는 PostgreSQL 기능·sql

-- MVCC 로 동시 multi-user write (전역 writer lock 없음)
-- 엔진에 박힌 실시간 pub/sub
LISTEN order_events;
NOTIFY order_events, '{"order_id":42,"status":"paid"}';

-- Row-level security
ALTER TABLE patient_records ENABLE ROW LEVEL SECURITY;
CREATE POLICY doctor_sees_own ON patient_records
  USING (doctor_id = current_setting('app.current_doctor')::int);

-- 선택된 테이블의 logical replication
CREATE PUBLICATION orders_pub FOR TABLE orders, line_items;

Exercise

다음 경우마다 SQLite와 PostgreSQL 가운데 하나를 고르고 한 줄로 이유를 적어봐. (1) iPhone 메모 앱, (2) 200명 회사의 사내 Slack 봇, (3) 하루 5명이 보는 개인 블로그, (4) 하루 1만 건을 처리하는 전자상거래, (5) 한 번 쓰고 끝나는 Jupyter notebook 데이터 분석.