Thanks for that helpful tip on 0/1! We'll definitely use those then. 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. 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
