Good tip on sqlite_analyze thanks! In this particular case, it's one large table. The column we're obsoleting is relatively small (20-30 bytes per row), but for a given instance of this table, we could be freeing upwards of 20MB of data (across all rows in the table for this column). Therefore, copying this table to remove the small column isn't worth the trouble relative to the time and disk space required to do it, compared to just living with the empty column.
Alternative, the ability to rename, and therefore repurpose, the column would also be welcome. I understand there are constraints there too. Ben On Sat, Jun 9, 2018 at 10:29 AM Jay Kreibich <j...@kreibi.ch> wrote: > > > 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 > -- Ben _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
