Re: [sqlite] Return a column's contents to the free list

On 9 Jun 2018, at 4:49pm, Paul Sanderson wrote: > The serial types NULL, 0 and 1 each have a serial type 0, 8 & 9 > respectively and there is no data stored for the column - the content of > the column is inherent in the serial types array. > > Blobs and Strings with no content are serial types

Re: [sqlite] Return a column's contents to the free list

On 9 Jun 2018, at 4:38pm, Ben Asher wrote: > Oh interesting! Is that optimization documented anywhere (website, code, or > otherwise)? I think I got my information when the change was announced or discussed on this list. However I now find that there's documentation on the web. See the

Re: [sqlite] Return a column's contents to the free list

On 9 June 2018 at 16:17, Jay Kreibich wrote: > > 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. > > You are correct Jay The serial types NULL, 0 and 1 each have a serial

Re: [sqlite] Return a column's contents to the free list

Oh interesting! Is that optimization documented anywhere (website, code, or otherwise)? Ben On Sat, Jun 9, 2018 at 10:36 AM Simon Slavin wrote: > On 9 Jun 2018, at 4:28pm, Jay Kreibich wrote: > > > If a full VACUUM is not feasible, you can simply copy the table after > the column is removed.

Re: [sqlite] Return a column's contents to the free list

Good tip on sqlite_analyze thanks! In this particular case, it's one large table. The column we're obsoleting is relatively small (20-30 bytes per row), but for a given instance of this table, we could be freeing upwards of 20MB of data (across all rows in the table for this column). Therefore,

Re: [sqlite] Return a column's contents to the free list

On 9 Jun 2018, at 4:28pm, Jay Kreibich wrote: > If a full VACUUM is not feasible, you can simply copy the table after the > column is removed. Note that SQLite has special optimization for the command DELETE FROM oldTable with no "WHERE" clause. So the best way to do it would be

Re: [sqlite] Return a column's contents to the free list

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

Re: [sqlite] Return a column's contents to the free list

> On Jun 9, 2018, at 10:16 AM, Ben Asher wrote: > > To further clarify on VACUUM, we actually want to actively avoid this > because it's expensive, and we'd be running these on small devices like > iPhones and iPads with large-ish DBs. If a full VACUUM is not feasible, you can simply copy the

Re: [sqlite] Return a column's contents to the free list

On 9 Jun 2018, at 4:16pm, Ben Asher wrote: > Thanks for that helpful tip on 0/1! We'll definitely use those then. Good. See section 2.1 of . > To further clarify on VACUUM, we actually want to actively avoid this > because it's expensive, and we'd be

Re: [sqlite] Return a column's contents to the free list

That would be great! The 0/1 thing feels like a trick that, while I'm glad I learned it, could be abstracted away by SQLite. Ben On Sat, Jun 9, 2018 at 10:16 AM Simon Slavin wrote: > Reading the thread suggests to me that a future version of SQLite might > profit from a special value-type for

Re: [sqlite] Return a column's contents to the free list

> On Jun 9, 2018, at 10:04 AM, Ben Asher 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

Re: [sqlite] Return a column's contents to the free list

Thanks for that helpful tip on 0/1! We'll definitely use those then. To further clarify on VACUUM, we actually want to actively avoid this because it's expensive, and we'd be running these on small devices like iPhones and iPads with large-ish DBs. We'd also expect lots more writes in the future

Re: [sqlite] Return a column's contents to the free list

Reading the thread suggests to me that a future version of SQLite might profit from a special value-type for the empty string, like there are already special column types for 0 and 1. I wonder what proportion of string values stored in SQLite databases are "". Simon.

Re: [sqlite] Return a column's contents to the free list

On 9 Jun 2018, at 3:52pm, Ben Asher wrote: > 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 >

Re: [sqlite] Return a column's contents to the free list

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

Re: [sqlite] Return a column's contents to the free list

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

Re: [sqlite] Return a column's contents to the free list

> On Jun 9, 2018, at 9:52 AM, Ben Asher 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