SQLite Forum

Setting non-binary columns to null in a table with a binary column populated with data creates free space and a larger file
Login
Yes, and ... ?

So it appears that the BLOB field part was re-written in the overflow pages even though the boundary split of the data between the on-page cell and the overflow was unchanged.

```
sqlite> create table x(x,y,z);
sqlite> insert into x values (zeroblob(1024*1024),1,'MyKey');
sqlite> select * from dbstat;
┌───────────────┬─────────────┬────────┬──────────┬───────┬─────────┬────────┬────────────┬──────────┬────────┐
│     name      │    path     │ pageno │ pagetype │ ncell │ payload │ unused │ mx_payload │ pgoffset │ pgsize │
├───────────────┼─────────────┼────────┼──────────┼───────┼─────────┼────────┼────────────┼──────────┼────────┤
│ sqlite_schema │ /           │ 1      │ leaf     │ 1     │ 35      │ 3949   │ 35         │ 0        │ 4096   │
│ x             │ /           │ 2      │ leaf     │ 1     │ 1036    │ 3042   │ 1048588    │ 4096     │ 4096   │
│ x             │ /000+000000 │ 3      │ overflow │ 0     │ 4092    │ 0      │ 0          │ 4096     │ 4096   │
│ x             │ /000+000001 │ 4      │ overflow │ 0     │ 4092    │ 0      │ 0          │ 8192     │ 4096   │
... overflow pages 000+000002 to 000+0000fd each containing 4092 bytes elided
│ x             │ /000+0000fe │ 257    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 1044480  │ 4096   │
│ x             │ /000+0000ff │ 258    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 1048576  │ 4096   │
└───────────────┴─────────────┴────────┴──────────┴───────┴─────────┴────────┴────────────┴──────────┴────────┘
sqlite> select 1024*1024 - 256*4092;
┌──────────────────────┐
│ 1024*1024 - 256*4092 │
├──────────────────────┤
│ 1024                 │
└──────────────────────┘
sqlite>
```

So it appears that 1024 bytes of the `blob` are stored in the cell in the table.  

```
sqlite> update x set y=null, z=null where y=1;
sqlite> select * from dbstat;
┌───────────────┬─────────────┬────────┬──────────┬───────┬─────────┬────────┬────────────┬──────────┬────────┐
│     name      │    path     │ pageno │ pagetype │ ncell │ payload │ unused │ mx_payload │ pgoffset │ pgsize │
├───────────────┼─────────────┼────────┼──────────┼───────┼─────────┼────────┼────────────┼──────────┼────────┤
│ sqlite_schema │ /           │ 1      │ leaf     │ 1     │ 35      │ 3949   │ 35         │ 0        │ 4096   │
│ x             │ /           │ 2      │ leaf     │ 1     │ 1031    │ 3047   │ 1048583    │ 4096     │ 4096   │
│ x             │ /000+000000 │ 259    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 4096     │ 4096   │
│ x             │ /000+000001 │ 260    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 1056768  │ 4096   │
... overflow pages 000+000002 to 000+0000fd each containing 4092 bytes elided
│ x             │ /000+0000fe │ 513    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 2093056  │ 4096   │
│ x             │ /000+0000ff │ 514    │ overflow │ 0     │ 4092    │ 0      │ 0          │ 2097152  │ 4096   │
└───────────────┴─────────────┴────────┴──────────┴───────┴─────────┴────────┴────────────┴──────────┴────────┘
sqlite> select 1024*1024 - 256*4092;
┌──────────────────────┐
│ 1024*1024 - 256*4092 │
├──────────────────────┤
│ 1024                 │
└──────────────────────┘
sqlite>
```

Notice that after the update the cell size in the table has changed, however the overflow still the same number of bytes and the table cell still contains the same first 1024 bytes of the blob.

Why are the overflow pages being re-written if the boundary has not changed (or are they just being re-written because the whole cell is being replaced)?

Note that the same thing happens even if the blob is the last field in the record.