SQLite 테이블별 용량 확인, 큰 테이블과 인덱스 찾는 법

파일 크기 대신 테이블과 인덱스를 나눠 본다

SQLite 파일이 예상보다 커졌다면 sqlite3 demo.db .diskused로 공간 사용 보고서를 열 수 있다. demo.db는 분석할 실제 DB 경로로 바꾼다. 파일 전체 크기만 보는 데서 나아가 테이블과 인덱스가 각각 공간을 얼마나 차지하는지 확인하는 명령이다.

10월 9일 공개된 SQLite 3.54.0은 이 기능을 CLI에 넣고 별도 도구였던 sqlite3_analyzer를 더 이상 권장하지 않는 도구로 전환했다. 새 셸을 준비한다면 10월 16일 나온 3.54.1의 후속 수정도 포함된 버전을 고를 수 있다.

sqlite3 --version
sqlite3 demo.db .diskused

공식 CLI 문서의 공간 분석 설명에 따르면 보고서는 사람이 읽는 설명과 분석 데이터를 담은 SQL로 구성된다. 화면에 SQL이 함께 나오는 것은 오류가 아니다. 오래된 셸에서 명령을 찾지 못한다면 실행한 sqlite3의 버전부터 확인한다.

셸의 새 명령이 앱에도 자동으로 생기지는 않는다

.diskused는 셸 명령이며, 내부에서는 diskused('main') 함수를 호출한다. 그러나 이 함수는 기본 배포 소스인 sqlite3.c에 포함되지 않는다. 앱에서 쓰려면 확장을 별도로 빌드하고 불러와야 한다. SQLite 라이브러리 버전만 올렸는데 함수가 없다고 나오는 상황을 구별해야 한다.

숫자를 직접 가져오려면 DBSTAT 가상 테이블을 사용할 수 있다. 해당 SQLite가 SQLITE_ENABLE_DBSTAT_VTAB 옵션으로 빌드돼 있어야 한다. 다음 예시는 객체별 할당 바이트가 큰 순서로 정렬한다.

SELECT name, pgsize, payload, unused
FROM dbstat
WHERE aggregate = TRUE
ORDER BY pgsize DESC;

pgsize는 데이터 본문만의 크기가 아니다

위 쿼리는 테이블이나 인덱스 하나당 한 행을 돌려준다. pgsize는 해당 객체가 쓰는 전체 페이지 공간, payload는 그 안에 저장한 내용의 바이트, unused는 그 페이지들 안의 남는 공간이다. 값은 모두 바이트 단위다.

DBSTAT는 완전히 비어 있는 freelist 페이지 등 일부 영역을 제외한다. 따라서 결과의 pgsize를 모두 더한 값을 DB 파일 전체 크기라고 해석해서는 안 된다. 우선 큰 객체를 찾고, 그 객체 안에서 내용과 여유 공간이 각각 어느 정도인지 읽는 데 적합하다.

분석 결과와 파일 축소는 별도 작업이다

행을 삭제해도 파일 크기가 그대로일 수 있다. SQLite는 삭제한 공간을 재사용할 수 있도록 남겨 두며, auto_vacuum=FULL 같은 설정에 따라 회수 동작이 달라진다. .diskused를 실행한다고 데이터가 재배치되거나 파일이 작아지지는 않는다.

완전히 빈 페이지 수는 PRAGMA freelist_count;로 확인할 수 있다. 이는 사용 중인 페이지 안의 여유를 나타내는 unused와 다르다. 어느 한 값만으로 축소 가능한 용량을 단정하면 안 된다.

실제 재구성이 필요할 때 쓰는 VACUUM은 추가 디스크 공간을 요구하고 쓰기 잠금의 영향도 받는다. 먼저 객체별 사용량을 확인한 뒤, 데이터 자체가 많은 것인지 재사용할 공간이 남은 것인지에 따라 정리 계획을 세우는 순서가 맞다.