I was working with a large (3.22GB) SQLite3 database containing a lot of binary data. I updated a number of rows in the largest table in the database, replacing a binary column in each row with a compressed version. In tests, the average compression ratio is between 85% to 90%.

When I was done, I noticed that the file size had shrunk dramatically - all the way down to 286.3MB.

I never ran VACUUM, never received any error messages and don't see any data corruption or missing data. The new size tracks with the expected compression ratio, but I didn't expect the file size to change at all, given years of past experience with SQLite and this note in the SQLite3 FAQ.

What could have happened here? Is this new behavior in SQLite? I'm a bit worried about it because VACUUM in the past has broken references between row - this particular database unfortunately uses implicit rowids, so any potential automatic vacuuming makes me nervous. I don't see evidence of that, but who knows? I confirmed that auto_vacuum is 0 for this database.

I ran this test iOS 17 running SQLite3 3.43, the stock binary provided by Apple.

It does if auto_vacuum is turned on.

I don't know what is doing it.

SQLite will remember freed space in the file and reuse it. But SQLite does not zero out freed space (unless you use PRAGMA secure_delete). So I don't understand how sparse-files could have kicked in.

I run PRAGMA secure_delete on iOS 17.6.1, it shows:

I believe it means it's in FAST mode, and the FAST mode may deleted content with zeros as the document said.

I searched from the web and the APFS's sparse files behavior may probably lead to this behavior. From a link that I read in https://eclecticlight.co/2021/03/29/sparse-files-are-common-in-apfs/

macOS¹ now defaults to creating all files in sparse format when certain conditions are met. This means that an SQLite database could easily be a sparse file, and so could some of your documents.

1. ^{^} iOS will probably have similar behavior

If you're actually using Apple's APFS, in other words you're using a recent version of any of Apple's operating systems, then this is a likely explanation.

1. SQLite overwrites some pages (a structure of SQLite databases) with zeros.
2. Sometimes all the pages in a block (a structure of an APFS volume) are filled with zeros.
3. APFS recognises this situation and, instead of providing a block number for where to find it in the file, just notes that the block as filled with zeros. So no part of the file is allocated to that block.

So it's an unplanned interaction between two pieces of software which use zeros for different purposes. SQLite uses them to prevent reading of deleted data. APFS uses them for sparse file management.

If the behaviour is important to you, make sure that the pagesize of your SQLite database divides neatly into the the blocksize of your storage medium. This should be the default behaviour, but to find the pagesize of your database, use

PRAGMA page_size

. To find the blocksize of your storage medium, make a short text file (a few characters) on it, then do Get Info (command-I) on it and look how much space it uses on disk. Or issue

stat -f "%k, %z, %b" [filepath][filename]

on it to see the block size, the size of the contents, and the number of blocks (including non-existant ones filled with zeros) used to store it. (For technical reasons the number of blocks used to store a short file will be more than you'd expect.)

I suppose a good test for this is to repeat your operation using some other volume format, e.g. on a Windows computer or an Android phone. Unless those OS now default to using sparse file systems, and nobody told me.

Thanks for the answers! I think this explanation makes sense with the behavior I saw.

The following query should tell you how big SQLite thinks the file is.

select page_count * page_size from pragma_page_size, pragma_page_count;

If the filesystem says it is smaller, then that's the filesystem's doing.