>> No matter what size you make the pages, a delete function is never going to re-pack the db
I dont want to repack the DB sir. When a page becomes free I want to make sure that page is used up first and then new pages are created. VACUUM is not what I want to do. I think that free pages are used up for new data in SQLIte as well and I have no doubt of that. The issue is when the page is 64 KB and it has lets say 16KB free then atleast the 16KB should be used before an entirely new page is created. @Clemens told that the page is used again if it has 2/3 free space. My question is that can this 2/3 ratio be changed by me for my purpose to 1/3 or any other ratio. On Sat, Feb 8, 2014 at 3:47 PM, RSmith <rsm...@rsweb.co.za> wrote: > Hi Raheel, > > It does make sense what you would like to do, but your concern does not > make sense. You say you are "trying to optimize the utilization of the free > space available" but give no indication why, it certainly does not seem > that space is a problem. > > I do understand the urge to optimize very much, but inside a Database > engine you can optimize either for speed or for size, not for both. SQLIte > as it stands is quite good at not wasting space unnecessarily, BUT, it is > first and foremost optimized for speed (Thank goodness for that), which > means the space-saving you are looking for is not going to happen. In my > previous post I made a passing comment / suggestion re using your own data > files in stead of sqlite, and if it is a case of not needing the sql > ability - which I seriously doubt since you are basically saving blocks of > information from a blocked device and doing so as byte streams (or BLOB > fields in SQL terms) - then I seriously suggest creating your own files and > custom index mechanism and saving the byte streams in there. It will be a > lot faster and with zero space wastage and the size limits can be whatever > you like them to be. > > Trying to use SQLite (or any other DB engine) for this purpose is akin to > using a full-function bakery with ovens, humidifiers, provers, rising > agents and bake timers when you just want to warm up your pizza (not to > mention being restricted by the limitations that come with it). > > No matter what size you make the pages, a delete function is never going > to re-pack the db, though you might get better results at re-using the > space - but this is a compromise and one that does not sit well with you > (if I read you right). > > Best of luck! > Ryan > > > > On 2014/02/08 07:57, Raheel Gupta wrote: > >> Hi, >> Sir, the 32 TB size is not always going to be reached. >> The Database is going to be used to store blocks of a Block Device like >> /dev/sda1 >> The size can reach 3-4 TB easily and would start from atleast 20-100 GB. >> 32 TB of data though impractical as of today will be possible in 2-3 years. >> The issue happens when I delete the rows and new rows are inserted at the >> end of the database the size of the database exceeds that of the actual >> block device size even though many pages are having free space. >> Hence I am simply trying to optimize the utilization of the free space >> available. >> I would have loved to use the page size of 2KB which would give me a >> practical size of 4TB. But that would have this hard limit of 4TB. >> So I have two possible options which I am trying to help me solve this >> issue : >> 1) Either make the page size to 2KB and increase the maximum page count >> to 2^64 which will be more than sufficient. >> 2) Improve the free space utilization of each page when the page size is >> 64KB. >> I hope this makes sense. >> >> > _______________________________________________ > 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
