On 28 Sep 2011, at 3:01pm, Tim Streater wrote: > No, I've done no tests. I'm not concerned that each database be down to its > smallest possible size, merely that the app have a mechanism that, from time > to time, compresses certain databases through which most of the apps traffic > flows (so, plenty of rows being added and deleted).
VACUUM does not do compression. What it does is recover space that is unused because you have deleted data from the database. However, so does inserting new data. So ... CREATE TABLE INSERT 1000 rows ... VACUUM here would not save any space DELETE 50 rows INSERT 50 rows ... VACUUM here would not save any space DELETE 10 rows ... VACUUM here would recover some space INSERT 10 rows ... but after this operation it is impossible to tell whether you did or didn't VACUUM. (Slightly inaccurate because some rows take up more space than other rows, but that's the idea.) So if you are constantly inserting and deleting rows, but you are inserting as much or more data than you are deleting, VACUUM does nothing to save filespace. The only time VACUUM will save space is if you've done a bunch of deleting and haven't put as much new data in since then. VACUUM does do something else: defragmentation. But since almost no operations of SQLite depend on reading many pages in sequence, defragmentation doesn't help much. I did some tests and found it difficult to see significant change except in specially designed situations which would never occur in real life. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
