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 - 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 and this note in the SQLite3 FAQ.
What could have happened here? Is there a potential problem, or is this new behavior in SQLite? I'm a bit worried about it because VACUUM in the past has broken references between rows (in fact, the documentation says it will do so), so any kind of automatic vacuuming makes me nervous. I don't see evidence of that, but who knows? I confirmed that auto_vacuum is 0.
I ran this test on a Darwin kernel (iOS 17) running SQLite3 3.43.
I decided to ask this question on the official SQLite forum. Based on answers I received there, including from the creator of SQLite, I think what happened here is a combination of two behaviors:
First, SQLite will zero out empty pages if the secure_delete flag is on. This flag is indeed on by default on iOS.
Additionally, since APFS has sparse files, the file system will discard any disk pages that have been completely zeroed out, leading to a smaller reported file size.
So when I replaced lots of binary data with smaller binary data, SQLite secure-deleted lots of pages, and APFS no longer recorded them as contributing to the file size.