And for TEXT records, it sounds like this isn't always the case and is
dependent on original record size?
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?
> 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
>> > 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