[SQLite3][C++] SQLite3 에서 record 를 지워도 DB 용량이 줄어들지 않는 이유는? - page, vacuum, rbu vacuum.

 SQLite3 에서 아래와 같이 DELETE query 를 실행하면 record 를 지울 수 있다

 DELETE FROM [table_name] WHERE [column_value]=?

 WHERE 문을 제거하면 전체 테이블이 모두 지워지는데, DELETE Query 를 이용해서 record 는 모두 삭제가 되어도 DB 용량은 그대로인 것을 볼 수가 있을 것이다.

 이는 SQLite3 가 reocrd 를 삭제해도 디스크로부터 할당받은 데이터 공간을 바로 버려버리는 것이 아니라, 이후에 새로운 record 가 Insert 되었을 때 재활용하기 위해 page 단위로 빈 공간이라는 정보를 유지하고 있기 때문이다.

 

Vacuum

그러면 DB Size 를 줄이기 위해서는 어떻게 해야 하는가? 간단하게도 VACUUM 이라는 query 를 수행하면 바로 빈 공간을 정리하여 현재 유지하고 있는 record 의 용량에 맞는 디스크 사이즈를 가지게 된다.

 

그런데 주의할 점은 이 작업은 존재하는 record 를 새로운 DB 를 만들어서 insert 하는 것과 마찬가지인 작업을 한다는 데에 문제가 있다. record 가 거의 지워져서 남아있지 않다면 문제는 없지만, record 를 10% 정도 지웠는데 용량이 너무 커서 vacuum 을 해야한다거나 하는 상황에서 위 단점이 문제점이 될 수 있다.

 많은 데이터를 Insert 하는 query 를 수행하는 것과 마찬가지로 이 작업은 은근 시간이 많이 걸린다. 단지 DB size 의 10% 만 용량 확보하고 싶은데, 90% 의 insert 작업을 수행해 버리기 때문에 비효율적이고, 재시작도 안되기에 어플리케이션이나 프로그램이 도중에 종료되는 user case 까지 있다면 꽤나 골치가 아프다. 또 vacuum 작업은 EXLCUSIVE LOCK 을 필요로 하기에 read 가 이루어지고 있는 도중에는 수행도 불가능하고 Vacuum 이 이루어지고 있으면 Read 또한 불가능하다는 점이 있다.

 

SQLite RBU Extension

 다행히 SQLite3 에서도 이런 문제점을 알고는 있는지 rbu vacuum 이라고 하는 일시 중단, 재시작이 가능한 vacuum 기능을 제공하고 있다. 또 auto vacuum 이라는 PRAGMA 또한 있어서 이를 이용해서 자신의 프로그램에 맞는 적절한 대응이 가능하다. 

 RBU Vacuum 을 이용하고 싶으면 아래 페이지를 참고 바란다.

https://www.sqlite.org/rbu.html

 

page_size, freelist_count

SQLite3 의 데이터들은 Page 라는 단위로 구성되어 있다고 위에서 언급했다. Page 는 512 에서 65536 byte 사이의 2의 제곱 값을 가지게 되는데, DB 마다 이 page size 를 정할 수가 있다. 그것이 page_size PRAGMA 다.

 

PRAGMA page_size : DB 에 설정된 page size 를 리턴

PRAGMA page_size=? : DB 의 page size 를 ? 값으로 설정

 

또 위에서 DELETE 를 이용해 record 를 삭제하여도 빈 공간을 page 단위로 남겨두고 있다는 말도 했었는데, 예약되었지만 사용되고 있지 않은 page 의 갯수를 얻는 PRAGMA 또한 존재한다. 아래 statement 를 page_size 와 함께 사용하면 DB 에서 사용되고 있지않은 빈 공간이 얼마정도 되는지 대략적으로 알 수 있다.

 

PRAGMA freelist_count : 사용되고 있지 않은 page 개수를 리턴

 

하지만 이것도 정확한 값은 아니다. 완전히 비어있는 page 개수를 리턴하는 것이라, 한 page 내에서 사용되고 있지 않은 공간까지는 알수가 없다.

 

가능한 SQLite3 에 대용량 데이터를 관리하는 일은 안하는게 좋겠지만, 굳이 관리해야 하고 삭제와 추가가 빈번하게 일어난다면 위의 PRAGMA statement 와 vacuum / rbu vacuum 을 이용해서 용량 조절과 용량 조절이 필요해지는 타이밍 등을 알수 있지 않을까 싶다.