And for TEXT records, it sounds like this isn't always the case and is dependent on original record size?
Ben On Sat, Jun 9, 2018 at 10:04 AM Ben Asher <[email protected]> wrote: > 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 <[email protected]> wrote: > >> >> > On Jun 9, 2018, at 9:52 AM, Ben Asher <[email protected]> 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 >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > Ben > -- Ben _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
