I see. Thanks for the detailed explanation! Given how expensive a full DB VACUUM can be (also seems impossible to determine how expensive without running VACUUM), could a table-level VACUUM make sense for this scenario? One could even make it a command as specific as CLEAR COLUMN, which has this side effect.
But actually, I think that what you're saying is that when setting the column to 0, if each row fits into a page, there's nothing to do. If there were spill pages being used by that row, then those will be recovered? If I understand correctly then, the only benefit is recovering those spill pages if possible, and that will already happen by 0'ing the column if possible. If that's the case, I think I can be happy that some space will be recovered if possible, short of doing the full VACUUM. Ben On Sat, Jun 9, 2018 at 10:17 AM Jay Kreibich <j...@kreibi.ch> wrote: > > > On Jun 9, 2018, at 10:04 AM, Ben Asher <benashe...@gmail.com> 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! > > Without digging through some detailed docs, I’m pretty sure empty string > and NULL require the same amount of storage space. If not, the difference > is maybe one byte. > > As for freeing the pages, the issue is that row values are stored in leaf > pages, kind of like a big array or list, packed together— not only are the > values in a row packed, the rows themselves are them packed into pages. If > you just set existing column value to NULL (or empty string) it is going to > re-pack and re-write the values in that page, but it isn’t going to > globally recover the space used by that column because it is intermixed > with all the other columns. The only case when a single column change > would trigger page recovery is if the value in that column is so big the > row overflows a single page and requires spill pages. Since a column clear > generally won’t free whole pages, there is nothing to put on the free list. > > The point of the VACUUM is not to release free pages back to the OS, but > to force the database to re-write (and therefore re-pack) all the rows, so > that the whole table will require fewer pages, and whatever is left over > can be freed (or in the case of a VACUUM, never re-written). > > It is different when you delete rows, since the the whole row record is > deleted and it tends to free up bigger chunks. But clearing the data out > of a column only clears values in the middle of row records, so it is > unlikely to free up pages by itself. > > -j > > > > > 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 > > _______________________________________________ > 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
