I'm examining an SQLite database at the moment and finding some interesting information. I'm hoping to get some opinions off anyone else who has some expertise in this area.
Lets assume I have a table in the database that stores data (leaf type pages) and occupies a single page (no leaf index pages) and currently holds 3 live data records. The page size is 4096. The primary key for the table is an incrementing number field.
These 3 live data records are stored at the very end of the page (so bytes ~3600 - 4095) and have a primary key value of say 200 - 203. There are other deleted records within the page (some referenced from deleted cell pointers at the top of the page) that have earlier primary key values (<200)
To me this would indicate that, at some point, all the data from this table was deleted and then these 3 records were stored.
I'm looking for alternate theories, a way in which these records could end up at the bottom of the page but without someone clearing all the data from the database first.
As it's a single page database, I've already ruled out the pages being re balanced due to deleted records.
I'm hoping I've explained this clearly enough, but anything that isn't, I'll be happy to fill in as many details as I can.