> On Jun 9, 2018, at 10:16 AM, Ben Asher <benashe...@gmail.com> wrote: > > To further clarify on VACUUM, we actually want to actively avoid this > because it's expensive, and we'd be running these on small devices like > iPhones and iPads with large-ish DBs.
If a full VACUUM is not feasible, you can simply copy the table after the column is removed. This may or may not be feasible, depending on if you have a small number of large tables, or a large number of medium/small tables. For smaller tables you can clear the column, then create a copy of the table with a new name, use something like INSERT INTO…SELECT to copy the data, then drop the old table, and finally rename the new one back to the old name. You’ll want to do all that in a transaction which will lock the database, but the individual tables are not huge that might be much faster than a VACUUM. Of course, it requires two copies of the table to exist in the database at the same time, but when you’re done, the new table will be smaller and all the pages used by the old table will go into the free list. I’d also suggest you get a copy of sqlite_analyze running, as it will tell you stats about page usage and such. That will give you some insight into if this process is worth the effort. -j > We'd also expect lots more writes in > the future (after obsoleting the column), so it seems preferable to focus > on making sure that the space that was used by the column is just returned > to be used by SQLite for those future writes. > > Ben > > On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin <slav...@bigfraud.org> wrote: > >> On 9 Jun 2018, at 3:52pm, Ben Asher <benashe...@gmail.com> wrote: >> >>> we would like to make sure that the space occupied by the columns >> contents is zeroed and returned to SQLite's free list ( >> https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the >> column's contents to "" (for a TEXT column >>> specifically) sufficient to do that? >> >> I recommend you use NULL instead: >> >> UPDATE myTable SET deadColumn = NULL WHERE [whatever]; >> VACUUM; >> >> I have three reasons for preferring NULL: >> >> 1) NULL takes the minimum amount of space to store >> 2) handing NULLs is much faster than handling strings >> 3) NULL in SQL literally means "value missing" or "value unknown". >> >> [later seeing a followup] >> >> If you can't use NULL, use the integer 0. Even if the column has TEXT >> affiliation. The values NULL, 0 and 1 each take no storage (they are >> special value types) and all three values are handled very efficiently. >> >> VACUUM will do the actual returning of space and shrink the file size. >> Doing it with VACUUM does it all at once at a time of your controlling >> rather than slowing down subsequent operations. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Ben > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
