WITHOUT ROWID 최적화

WITHOUT ROWID 최적화

기본적으로 SQLite의 모든 행에는 "rowid"라는 특별한 컬럼이 있고, 이 컬럼이 테이블 안에서 그 행을 고유하게 식별해요. 그런데 CREATE TABLE 문장 끝에 "WITHOUT ROWID"를 붙이면 이 특별한 rowid 컬럼이 생략돼요. 언뜻 뭘 위한 건지 헷갈릴 수 있는데, rowid를 없애면 어떤 상황에서 공간과 성능 이점이 생겨요. 이 글은 WITHOUT ROWID 테이블이 뭔지, 언제 유용한지, 일반 테이블과 뭐가 다른지 설명할게요.

출처: https://www.sqlite.org/withoutrowid.html

WITHOUT ROWID란 무엇인가

WITHOUT ROWID 테이블은 기본 키(PRIMARY KEY)를 클러스터 인덱스(Clustered Index) 로 사용하는 테이블이에요.

CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID;

WITHOUT ROWID 테이블은 반드시 PRIMARY KEY가 있어야 해요. PRIMARY KEY가 없으면 오류가 나요. 이 기능은 SQLite 버전 3.8.2(2013-12-06) 이상에서 사용할 수 있고, 더 낮은 버전으로 데이터베이스를 열면 "malformed database schema" 오류가 나요.

일반 rowid 테이블과의 차이

WITHOUT ROWID 문법은 최적화일 뿐, 새로운 기능을 제공하진 않아요. WITHON ROWID로 할 수 있는 건 일반 rowid 테이블로도 똑같이 할 수 있어요. 장점은 특정 상황에서 더 적은 디스크 공간을 쓰거나 조금 더 빠를 수 있다는 점뿐이에요.

다만 일반 테이블에 없는 제한이 몇 개 있어요.

  1. PRIMARY KEY 필수 — PRIMARY KEY 없이는 만들 수 없어요.
  2. INTEGER PRIMARY KEY의 특별 동작 없음 — 일반 테이블에서 "INTEGER PRIMARY KEY"는 rowid의 별칭이지만, WITHOUT ROWID에는 rowid가 없으므로 그 의미가 사라져요. 정수 affinity를 가진 PRIMARY KEY처럼 동작해요.
  3. AUTOINCREMENT 동작 안 함 — AUTOINCREMENT는 rowid가 있어야 동작하는데 WITHOUT ROWID에는 없으니 오류가 나요.
  4. PRIMARY KEY 컬럼에 NOT NULL 강제 — SQL 표준대로, WITHOUT ROWID 테이블은 PRIMARY KEY의 각 컬럼에 NULL을 넣으려 하면 오류를 내요.
  5. sqlite3_last_insert_rowid() 동작 안 함 — INSERT가 이 함수의 반환값을 바꾸지 않아요.
  6. 증분 BLOB I/O 동작 안 함 — rowid가 없어 sqlite3_blob 객체를 만들 수 없어요.
  7. sqlite3_update_hook() 콜백 없음 — 변경된 행의 rowid를 알려줄 수 없으니 UPDATE 훅이 호출되지 않아요.

왜 공간·성능 이점이 생기나

핵심을 비교해 볼게요. 일반 rowid 테이블에서 PRIMARY KEY는 사실 UNIQUE 인덱스에 불과해요. 디스크에서 레코드를 찾는 키는 rowid죠.

CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER );

이 일반 테이블은 두 개의 B-트리로 구현돼요. 메인 테이블은 숨겨진 rowid를 키로 쓰고 "word"와 "cnt"를 데이터로 저장해요. 그리고 "word"의 UNIQUE 인덱스가 별도 B-트리로 생기는데, 이 인덱스는 키로 "word"와 rowid를 쓰고 데이터는 저장하지 않아요. 그래서 모든 "word" 텍스트가 메인 테이블에 한 번, 인덱스에 한 번, 두 번 저장돼요.

SELECT cnt FROM wordcount WHERE word='xsync';

이 쿼리는 먼저 인덱스 B-트리에서 "word"를 찾고, 거기서 rowid를 뽑아 메인 테이블을 다시 탐색한 뒤 "cnt"를 읽어요. 즉 이진 탐색이 두 번 필요한 거죠.

WITHOUT ROWID 테이블은 다른 설계를 써요.

CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID;

이 테이블은 B-트리가 하나뿐이에요. "word" 컬럼이 키이고 "cnt"가 데이터예요. "word" 텍스트가 한 번만 저장되고, 특정 "word"의 "cnt"를 찾는 것도 메인 B-트리 탐색 한 번으로 끝나요.

그래서 어떤 경우에는 WITHOUT ROWID 테이블이 디스크 공간을 절반으로 줄이고 거의 두 배 가까이 빠르게 동작할 수 있어요.

언제 쓰는 게 좋나

WITHOUT ROWID 최적화는 정수가 아니거나 복합(다중 컬럼) PRIMARY KEY를 갖고, 큰 문자열이나 BLOB을 저장하지 않는 테이블에 유용해요.

  • 단일 INTEGER PRIMARY KEY 테이블에는 일반 rowid 테이블이 더 빨리 동작하므로 WITHOUT ROWID를 피하는 게 좋아요.
  • 행이 크면 안 돼요. 좋은 경험칙은 행 평균 크기가 데이터베이스 페이지 크기의 약 1/20 미만이어야 한다는 거예요(1KiB 페이지면 행당 약 50바이트, 4KiB 페이지면 약 200바이트).
  • 큰 행에서도 동작하긴 하지만, 일반 rowid 테이블이 보통 더 빠르기 때문이에요.

실전 전략은 개발 후반에 WITHOUT ROWID를 걱정하지 않고, 나중에 정수가 아닌 PRIMARY KEY 테이블에 적용했을 때 성능이 나아지는지 테스트해서 도움이 되는 곳에만 유지하는 거예요. 두 테이블이 같은 SQL 문장에 같은 답을 내기 때문에 실험하기 쉬워요.

기존 테이블이 WITHOUT ROWID인지 확인

PRAGMA index_info가 WITHOUT ROWID 테이블에서는 PRIMARY KEY 정보를 반환해요. 일반 테이블은 항상 행을 반환하지 않으므로, 이 pragma로 두 테이블을 구분할 수 있어요.

더 알아보기