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.