SQLite - pre allocating database size

Question

Is there a way to pre allocate my SQLite database to a certain size? Currently I'm adding and deleting a number of records and would like to avoid this over head at create time.

Answer 1

The fastest way to do this is with the zero_blob function:

Example:

Y:> sqlite3 large.sqlite
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table large (a);
sqlite> insert into large values (zeroblob(1024*1024));
sqlite> drop table large;
sqlite> .q

Y:> dir large.sqlite
Volume in drive Y is Personal
Volume Serial Number is 365D-6110

Directory of Y:\

01/27/2011 12:10 PM 1,054,720 large.sqlite

---

Note: As Kyle properly indicates in his comment:

There is a limit to how big each blob can be, so you may need to insert multiple blobs if you expect your database to be larger than ~1GB.

Answer 2

There is a hack - Insert a bunch of data into the database till the database size is what you want and then delete the data. This works because:

"When an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary."

Naturally, this isn't the most reliable method. (Also, you will need to make sure that auto_vacuum is disabled for this to work). You can learn more here - http://www.sqlite.org/lang_vacuum.html