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 <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!
>
> 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
>


-- 
Ben
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to