On 8 Feb 2014, at 11:24am, Raheel Gupta <raheel...@gmail.com> wrote:
> 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. Just to explain that this would be extremely inefficient because a new row that you write to a database will not take up the same space as a row you have deleted. While a database is in use it might use perhaps 100 pages for a particular table. Almost every one of those pages will have a little space free: anything from 1 byte to most of the page, depending on how much space each row takes up. When writing a new row to a table, SQLite intelligently figures out which existing page it can write the row to (or does it ? someone who has read the source code can tell me I'm wrong and if it searches for the 'best' page). What it won't do is rearrange existing pages so that they are used as much as possible. That could be done whenever a row is deleted (including when a row is replaced using UPDATE). But it would require a lot of checking, processing, reading and writing, and this would slow SQLite down a great deal for every DELETE and UPDATE operation. As an the top of my head guess, individual operations could take unpredictable amounts of time since most efficient packing could require any number of pages to be rewritten. I don't know of any database system that works like this. So that's one thing that might make you want to use VACUUM. Even VACUUM does not reclaim the maximum amount of space possible. Instead it prefers to keep the data for a row together and rows in primary index order together, to increase speeds There are also the auto_vacuum and incremental-vacuum PRAGMAs. However they operate only on the level of pages: they will reap entire unused pages, but not interfere with the packing of data within a page. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
