A database that is geared for 32TB size and you are concerned about rather insignificant space wasted by the page size that is
needed to reach the 32TB max size... does not make any sense unless you are simply paranoid about space. Removing the gaps in the
table space when deleting a row (or rows) will render a delete query several magnitudes slower.
If it IS that big of a concern, then maybe use standard files rather than SQLite to save data in? If the SQL functionality is a
must, you can use vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum takes some processing to re-pack a DB,
especially a near 32TB one... in the order of minutes on a computer I would guess, and much much more on anything else. 2 - a 32TB
DB will need up to 64TB total free disk space to be sure to vacuum correctly - so having issues with it taking up maybe 40TB for
32TB of data is in itself an irrelevant concern. Even large queries, temporary tables etc will all need additional interim space for
the sorts of queries that might be requested of a 32TB data-set.
The real point being: if you do not have at least 64TB free on whatever that 32TB DB will sit, you are doing it wrong, and if you do
have that much free, you can ignore the 25% wasted deletion space problem.
If the problem is simply your own pedanticism (at least I can sympathise with that!) then it's simply a case of "Welcome to
efficient databasing", but if it is a real space deficit, then I'm afraid you will have to re-plan or reconsider either the max
allowable DB, or the physical layer's space availability - sorry.
On 2014/02/07 20:35, Raheel Gupta wrote:
Hi,
I use a page size of 64 KB. But my row consists of 2 columns that is :
i - Auto Increment Integer,
b - 4096 Bytes of BLOB data
Now for the sake of calculation, lets say 16 rows fit in a page and my
table has 10000 rows when I start.
Now, lets say I delete some data which is not in sequence i.e. it can be
deleted as per data which is not in use. To create such a hypothetical
situation for explaining this to you, here is a simple query :
DELETE from TABLE where i%4 = 0;
As you may see that there is now 25% data deleted in each page.
Now even if I do insert another 2500 rows (25% of original size) my
database size reaches 125% of the original size when I inserted the 10000
rows initially.
Hence there is significant space wastage. Anyway i can improve that ?
It would be nice if the database size would be close to the original size
after deleting 25% and adding some new 25% data.
I know you would recommend to use smaller page sizes. Ideally 2KP page size
is good but then, the number of pages is restricted to a max of 2^32 which
will restrict the total database size to 4TB only. I need the max size to
be capable of atleast 32TB.
On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs <dfgri...@gmail.com> wrote:
Can you write more about how this is causing you a problem? Most users
don't experience this as a problem
On Feb 7, 2014 10:30 AM, "Raheel Gupta" <raheel...@gmail.com> wrote:
SQLite's tables are B-trees, sorted by the rowid. Your new data will
probably get an autoincremented rowid, which will be appended at the
end
of the table.
A page gets reorganized only when about 2/3 is free space.
Anyway to make this ratio to lets say 1/3 ?
_______________________________________________
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
_______________________________________________
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
