SQLite ANALYZE와 VACUUM: 통계 수집과 공간 회수

서로 다른 두 가지 유지보수 작업

ANALYZE와 VACUUM은 늘 같이 언급되지만, 실제로 해결하는 문제는 다릅니다.

• ANALYZE는 데이터에 대한 통계 정보를 수집해서 쿼리 플래너가 더 똑똑한 판단을 내리게 해줍니다. sqlite_stat1이라는 테이블에 결과를 쓸 뿐, 실제 데이터 행은 건드리지 않습니다.
• VACUUM은 파일 자체를 다시 만들어서 사용하지 않는 페이지를 회수하고 저장 공간 단편화를 정리합니다. 쿼리 실행 계획을 직접 바꾸지는 않습니다.

쿼리가 엉뚱한 인덱스를 고르고 있다면 필요한 건 ANALYZE입니다. 대량 삭제 후 파일 크기가 비정상적으로 크다면 VACUUM을 써야 하고요. 이 둘을 헷갈리면 유지보수에 들이는 시간을 그대로 날리게 됩니다.

ANALYZE는 정확히 무슨 일을 하나

쿼리 플래너는 결국 추측을 해야 합니다. WHERE status = 'active' 조건을 만나면, 매칭되는 행이 한 개일지 백만 개일지 가늠해서 인덱스를 쓸지 풀 스캔을 할지 결정해야 하니까요. 통계가 없으면 단순한 휴리스틱에 의존할 수밖에 없습니다.

ANALYZE는 각 인덱스를 훑으면서 값이 어떻게 분포되어 있는지 요약 정보를 기록합니다:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

sqlite_stat1 테이블에 저장된 한 줄은 인덱스가 대략 몇 개의 행을 가지고 있고, 키 하나당 중복이 평균 몇 개나 되는지를 쿼리 플래너에게 알려줍니다. 다음에 WHERE status = 'pending' 쿼리를 실행하면 플래너는 pending 값이 드물다는 걸 알고 인덱스를 활용하지만, WHERE status = 'shipped'라면 그냥 스캔하는 편이 더 빠르다고 판단할 수도 있죠.

데이터베이스 전체가 아니라 특정 테이블이나 인덱스만 골라서 분석할 수도 있습니다:

ANALYZE orders;
ANALYZE idx_orders_status;

대량 적재 작업이 끝났거나, 큰 스키마 변경이 있었거나, 데이터 분포가 바뀐 테이블에서 플래너가 엉뚱한 실행 계획을 고를 때 ANALYZE를 실행해 주세요.

PRAGMA optimize: 요즘 권장되는 기본값

연결을 닫을 때마다 무작정 ANALYZE를 돌리는 건 낭비입니다. 대부분의 경우 통계를 다시 뽑을 만큼 데이터가 바뀌지 않았기 때문이죠. SQLite는 이를 똑똑하게 감싸 주는 기능을 기본으로 제공합니다:

PRAGMA optimize;

PRAGMA optimize는 마지막 분석 이후 데이터베이스가 어떻게 변했는지 살펴보고, 필요한 테이블에 한해서만 ANALYZE를 실행합니다. 공식 권장 사항은 오래 살아있는 커넥션의 경우 종료 직전에 호출하고, 몇 시간씩 열려 있는 커넥션이라면 주기적으로 호출하라는 것입니다.

바뀐 게 없으면 부담이 거의 없고, 바뀐 게 있으면 제값을 합니다. 먼저 optimize를 쓰고, 강제로 통계를 갱신해야 할 때만 순수 ANALYZE를 꺼내 쓰세요.

VACUUM이 실제로 하는 일

행을 삭제하거나 테이블을 DROP하면 SQLite는 해당 페이지를 빈 공간으로 표시할 뿐, 파일 크기 자체는 줄이지 않습니다. 이 빈 페이지들은 이후의 INSERT에서 재사용되기 때문에 평소에는 별 문제가 없습니다. 다만 데이터가 오랫동안 들고 나기를 반복하다 보면 두 가지가 쌓입니다.

1. OS는 알 수 없는 빈 공간. 실제 데이터는 800MB뿐인데 .db 파일은 여전히 2GB를 차지합니다.
2. 단편화(fragmentation). 같은 테이블의 행들이 서로 떨어진 페이지에 흩어지면서 스캔 성능이 떨어집니다.

VACUUM은 데이터베이스 전체를 새 파일에 빈틈없이 다시 채워 복사한 뒤 원본과 교체하는 방식으로 두 문제를 한 번에 해결합니다. 이게 바로 sqlite 용량 줄이기의 핵심입니다.

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT '항목 ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

VACUUM을 실행하고 나면, 살아남은 100개 행만 처음부터 새로 INSERT한 것과 똑같은 크기의 파일이 됩니다. 부수 효과로 rowid는 모두 그대로 유지되고, 디스크 상의 레이아웃은 다시 연속적으로 정돈됩니다.

실행하기 전에 알아둬야 할 점이 몇 가지 있습니다:

• 작업이 끝날 때까지 데이터베이스에 배타적 잠금(exclusive lock) 을 겁니다. 다른 커넥션은 쓰기를 할 수 없습니다.
• 데이터베이스 크기의 약 2배에 해당하는 여유 디스크 공간이 필요합니다. 기존 파일 옆에 새 파일을 만들어가며 작업하기 때문입니다.
• 트랜잭션 안에서는 실행할 수 없고, 활성 트랜잭션이 하나라도 열려 있으면 에러가 납니다.
• 수 GB짜리 데이터베이스라면 꽤 오래 걸릴 수 있으니, 미리 계획하고 돌리세요.

sqlite vacuum, 언제 실제로 돌려야 할까

대부분의 애플리케이션에서는 특별한 변화가 없는 한 굳이 돌릴 필요가 없습니다.

VACUUM을 돌릴 만한 합당한 이유:

• 큰 테이블을 방금 DROP했거나 대량의 행을 한꺼번에 삭제해서 디스크 공간을 회수하고 싶을 때.
• 수년간 데이터가 들락날락한 데이터베이스라서 테이블 스캔 쿼리가 예전보다 느리게 느껴질 때.
• 릴리스에 데이터베이스 파일을 함께 배포하는데, 파일 크기를 최대한 줄이고 싶을 때.

별로 좋지 않은 이유:

• "그냥 혹시 모르니까." VACUUM은 매번 파일 전체를 다시 씁니다. 운영 중인 시스템에서 이 작업을 한다는 건 절대 안전하지 않습니다.
• 삭제 배치가 끝날 때마다 매번. 비워진 페이지는 어차피 재사용됩니다.

auto_vacuum과 incremental vacuum

SQLite가 빈 페이지를 자동으로 관리해 주길 원한다면 auto_vacuum을 데이터베이스 생성 시점에 설정해야 합니다. 한 번 만들고 나면 전체 vacuum 없이는 변경할 수 없습니다:

PRAGMA auto_vacuum = INCREMENTAL;

세 가지 모드가 있습니다.

• NONE (기본값): 비어 있는 페이지가 파일에 그대로 남아 있다가, 이후 INSERT 작업에서 재사용됩니다.
• FULL: 페이지가 해제되는 커밋이 발생할 때마다 파일을 잘라냅니다(truncate). 편하긴 하지만, 트랜잭션마다 그 비용을 부담해야 합니다.
• INCREMENTAL: SQLite가 비어 있는 페이지를 추적해 두기만 하고, 실제 반환은 여러분이 명시적으로 요청할 때만 수행합니다.

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N)은 비어 있는 페이지를 최대 N개까지 OS에 돌려줍니다. 동작이 빠르고, 배타 락(exclusive lock)을 오래 잡지 않아서 스케줄러에 걸어두고 주기적으로 실행하기 좋습니다. 전체 VACUUM을 돌리기엔 부담스럽지만 DB 용량은 꾸준히 줄여야 하는 쓰기 위주 워크로드에 딱 맞는 선택지죠.

VACUUM INTO: 압축된 복사본 내보내기

VACUUM INTO는 원본 파일은 그대로 두고, 깔끔하게 정리된 새 복사본을 별도 파일로 만들어 줍니다:

VACUUM INTO 'backup.db';

이런 상황에서 정말 쓸모가 많습니다:

• 백업 용도. 결과물은 완전히 정리된 일관성 있는 스냅샷입니다. 쓰다 만 페이지도 없고, 신경 쓸 .wal 파일도 없습니다. cp로 파일을 그대로 복사하는 것보다 훨씬 안전하죠.
• 쓰기 작업을 오래 막지 않고 용량 줄이기. 별도 파일로 vacuum을 돌린 뒤 원자적으로 교체하면 됩니다. vacuum이 진행되는 내내 쓰기 작업이 멈추는 일이 없습니다.
• 배포 용도. 개발용 데이터베이스를 작고 단편화 없는 복사본으로 깔끔하게 배포할 수 있습니다.

단, 대상 파일이 미리 존재하면 안 됩니다. 이미 있으면 에러가 납니다.

실전에서 쓰는 유지보수 레시피

일반적인 애플리케이션 데이터베이스라면 다음과 같이 관리합니다:

-- 長時間稼働している各接続で、クローズ前に:
PRAGMA optimize;

-- 大量の一括ロードまたはスキーマ変更の後に:
ANALYZE;

-- 大量のデータを削除してディスクを取り戻したい時に:
VACUUM;

-- バックアップ用:
VACUUM INTO '/backups/app-2026-04-23.db';

쓰기/삭제가 빈번하고 24시간 가동되는 데이터베이스라면, 생성 시점에 auto_vacuum = INCREMENTAL로 설정해 두고 PRAGMA incremental_vacuum(N)을 주기적으로 실행하세요. 트래픽이 한산한 시간대에 하루 한 번 정도 돌리면 적당합니다.

"왜 이렇게 파일이 커졌지?" 진단하기

다음 두 가지 PRAGMA로 현재 상태를 확인할 수 있습니다:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = 현재 파일 크기.
• freelist_count × page_size = 사용하지 않는 페이지로 낭비되는 바이트 수.

freelist_count가 page_count 대비 큰 비중을 차지한다면 VACUUM(또는 incremental_vacuum)으로 파일 용량을 눈에 띄게 줄일 수 있습니다. 반대로 비중이 작다면 이미 데이터가 빽빽하게 들어차 있다는 뜻이라 VACUUM을 돌려도 별 효과가 없습니다.

자주 하는 실수

• 트랜잭션 안에서 VACUUM 실행하기. 불가능합니다. 먼저 커밋부터 하세요.
• 디스크 여유 공간을 깜빡하는 경우. VACUUM은 작업용 공간이 필요합니다. 10GB짜리 DB라면 약 10GB의 여유 공간이 추가로 있어야 합니다.
• 데이터를 이미 넣은 뒤에 auto_vacuum을 켜는 경우. 이때는 다음번 전체 VACUUM을 돌리기 전까지 아무 효과도 없습니다. 자동으로 동작시키고 싶다면 데이터베이스를 처음 만들 때 설정해야 합니다.
• ANALYZE로 파일 크기가 줄어들 거라고 기대하는 경우. 그건 VACUUM의 역할입니다.
• VACUUM으로 쿼리 플랜이 좋아질 거라고 기대하는 경우. 그건 ANALYZE의 역할입니다.

두 명령은 서로 보완 관계이지, 어느 한쪽이 다른 쪽을 대체하지 않습니다.

다음 장: 트랜잭션

VACUUM 같은 유지 보수 명령을 살펴보다 보면 그동안 당연하게 여겨 왔던 것 하나가 드러납니다. 바로 SQLite의 트랜잭션 모델, 그리고 무엇을 언제 잠그는지에 대한 이야기죠. 다음 장에서는 여기서 출발합니다 — 트랜잭션이 어떻게 동작하는지, BEGIN / COMMIT / ROLLBACK이 실제로 어떤 것을 보장하는지, 그리고 여러 문장으로 이루어진 작업을 원자적으로 묶기 위해 이들을 어떻게 사용하는지를 다룹니다.