Right we'll prefer NULL, but some columns are NON NULL. In this scenario, we're not worried about returning space to the OS, but good point out VACUUM. Thanks!
So for NON NULL columns, setting contents to empty should return the freed space to internal free list? Ben On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich <j...@kreibi.ch> wrote: > > > On Jun 9, 2018, at 9:52 AM, Ben Asher <benashe...@gmail.com> wrote: > > > > Hi! I've read a lot of discussion about the constraints related to why > > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP, > > etc.). Despite that, we still have situations where our data model > changes, > > and a column becomes obsolete. Given the constraints, we've decided to > > create a column "graveyard" at the application level: basically a list of > > columns that exist but are obsolete. While we cannot drop columns, 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? > > That or NULL. You also have to vacuum the database to re-pack the > database pages and actually recover the disk space. Some of the space may > be recovered if the TEXT records were really long (and required spill > pages) but if most of the column were a dozen bytes or so (more typical) > you’ll want to do a VACUUM. > > -j > > _______________________________________________ > 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
