Hi, I tried the same database I had and used a 2KB page size. It works much faster and also the pages are reused immediattly to the extent of 95%.
If only the number of pages could be increased somehow. Does anyone think its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned Integer. I do understand that VACUUM is not a good option for me. On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 9 Feb 2014, at 10:45am, RSmith <rsm...@rsweb.co.za> wrote: > > > On 2014/02/09 12:06, Raheel Gupta wrote: > >> Sir, I have only one auto increment primary key. > >> Since the new rows will always have a higher number will the pages > >> which have some free space by deleting rows with lower numbered keys > never > >> be reused ? e.g. If row with ROWID "1" was deleted and freed, will it > not > >> be used to store the NEW row which will be assigned ROWID 10001 ? > > > > Yes. That is the point of AutoIncrement, every new Key will always be > higher than any previous key ever used, and always exactly one higher than > the highest ever previously used key. As such, it cannot be re-used within > pages that are half filled from deletion (except maybe the last page), and > I believe pages that go completely empty may be re-used without the need to > vacuum etc. (need someone to confirm this). > > You are correct, depending on this PRAGMA: > > <http://www.sqlite.org/pragma.html#pragma_auto_vacuum> > > auto_vacuum = NONE > > A page which has all its data deleted is added to the 'free pages' list > and eventually reused. > > auto_vacuum = FULL > > A page which has all its data deleted is replaced by the last page of the > file. The file is then truncated to release the space of the last page for > use in other files. > > auto_vacuum = INCREMENTAL > > A page which has all its data deleted is replaced by the last used page of > the file. When you issue "PRAGMA incremental_vacuum(N)" the file is > truncated to release unused pages at the end for use in other files. > > As in previous discussion, all this is about reclaiming space at the page > level: releasing entire pages of space. It has nothing to do with > reclaiming space within a page. And also as in previous discussion, the > fastest of these is "auto_vacuum = NONE". Copying one page to another, > releasing filespace and claiming it back again are slow and require much > reading and writing. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
