> 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
