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
