> 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
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.
> 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
>>> 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
>>> like to make sure that the space occupied by the columns contents is
>>> and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12)
>>> 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.
>> sqlite-users mailing list
> sqlite-users mailing list
sqlite-users mailing list