Records can span multiple pages and the record itself is unrelated to the block size.
For smaller tables indexes an increase in page size can increase the DB size - consider a DB with 1024 byte pages and one table that occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take up two pages i.e. 8K. Without further investigation I suspect that savings would come in when you have larger tables with relatively large record payloads, I would think that the free space in a page would be half the average record length and so by multiplying the page size by 4 would decrease the free (wasted) space (due to not enough space for another complete record) also by a factor of four. This is grossly over simplified though and takes no account of presumably less overflow pages due the larger page size, pointer maps, and free space due to deleted records. In short (no testing done) I am not sure that for most implementations that increasing page size would make any significant space savings and may likely as mentioned above increase the DB size.. The savings as mentioned earlier are IO related due to matching the page size to the underlying hardwares block size and for larger payloads ensuring less IO due to no or lesss overflow pages. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5 March 2016 at 18:43, Jim Callahan <jim.callahan.orlando at gmail.com> wrote: > > >> From: Domingo Alvarez Duarte >> I have a question, is it enough to vacuum a database to update to the new >> page size ? >> > > Apparently all you need to is "pragma page_size=4096; vacuum;" using the >> appropriate page size. >> This makes very easy to convert any(all) database(s) with a single >> t >> command from the command-line, like so (Win7 example): >> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" > > > I am confused. > The "page" is an in-memory structure; it is how large a chunk the program > reads from the file (analogous to how large a scoop or shovel), So, unless > one is using an index, how would the on disk structure be impacted? How > does SQLite handle the last block (does it expect the file to be an even > multiple of the block size, or does it accept that the last read might be > less than a full block?). > > For example, if one encountered an old file, would it be enough to rebuild > the indices? > > Or is it simply a matter of closing the file? (close the file with the old > version and open the file with the new?). > > I haven't read the source code so I don't know what assertions, checks or > assumptions SQLite > uses. > > Jim > > > > On Sat, Mar 5, 2016 at 11:04 AM, <tonyp at acm.org> wrote: > >> From: Domingo Alvarez Duarte >>> I have a question, is it enough to vacuum a database to update to the new >>> page size ? >>> >> >> Apparently all you need to is "pragma page_size=4096; vacuum;" using the >> appropriate page size. >> This makes very easy to convert any(all) database(s) with a single command >> from the command-line, like so (Win7 example): >> >> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
