Purge documents on iOS not reducing disk size

#1

Hi,

We have a simple application built with Couchbase. Our problem is that when purging all documents the sqlite database remains more or less the same size, why is this? We are using Couchbase Lite 2.1.3 (Swift)

Here is what we do:

We have a Couchbase server with 40k documents (no attachments).

  1. We launch the application with a user that subscribes on all channels so everything is synchronised. The database size (not the WAL or shm but the other one named only sqlite3) is 115Kb before the synchronisation and around 80mb after.

  2. We stop the replicator, purge all documents and compact, change the users channels to subscribe on NO channels and then we start the replicator again. This all seems to work fine. When looking at the database through a sqlite software, all documents are gone but the sqlite file is still 80 mb.

Why is this and what can we do? We really do not want to delete the database.

#2

The SQLite database is incrementally vacuumed when closed and when compact is called, which should remove all free blocks and shorten the file.

Try this:

  • Open the db.sqlite3 file with the sqlite3 command-line tool.
  • Enter PRAGMA page_count;
  • Enter PRAGMA freelist_count; — the result should be less than 1/4 of the first number.
  • Enter select count(*) from kv_default; — result should be the same as the CBL database’s count.

If these all seem in order, but the file is still too big, would you consider sending me a copy of the database, via Dropbox or something similar?

#3

Thanks for the quick response!

I will try this on monday and get back to you.

#4

Now I have tried some more.

The freelist_count is not less than 1/4 of the result of page_count, they are almost the same.
The select count(*) matches though, they are both 0.

What we do in the code is the following:

  1. Stopreplicator
  2. purge all documents
  3. compact

We have tried to use db.close as well after the compact but it makes no difference. We do not want to use the close method but we did try it. Anyways, after this we just close the application in the simulator and Xcode but and the file size remains…

We did try to open the sqlite3.db file with a software called DB Browser for SQLite. In this software there is a compact functionality. After using that and closing the software the file size shrinked. So I am guessing we are doing something wrong in Xcode or that the method don’t work properly… Any ideas?

#5

That’s weird; after the compact or close, LiteCore should have run incremental_vacuum, which should have removed the free pages and shrunk the file. (You can see the source code here, if you’re curious.)

The SQLite docs say that PRAGMA incremental_vacuum “causes up to N pages to be removed from the freelist. The database file is truncated by the same amount. … if the “( N )” argument is omitted, then the entire freelist is cleared.” So afterwards the database file should be very small.

You could try entering the same pragma into the DB Browser app and see what happens…