Hello all, I have a process that runs 24/7 and permanently inserts data into an SQLite database:
1) Create new database at midnight, keep it open for writing for the next 24 hours 2) There are no deletions and not even reads during the database creation 3) I use only bulk inserts wrapped in a transaction (for each minute of data) that may contain a few thousands of rows in every transaction. 4) My journal mode is MEMORY. 5) Once the insertion process is done with I build several indices and close the database 6) After that the database file is moved over the network to a storage device The database will only be used again for reading and will remain unmodified forever. Each database is fairly large (3-5 GB) and considering it will never be modified again I would like to take all possible measures to ensure that the file size / fragmentation / data access times are all as low as possible. So my question is about how to ensure most efficient data allocation for such scenario. I thought of several options: 1) VACUUM before creating indices, then create indices, then move database off to storage 2) Create indices, then VACUUM, then move off to storage 3) Create indices, move to storage, VACUUM when already on storage (using SQLite process running locally on storage device) ... or any other sequence of those 3 steps (vacuum, indexing, moving to storage) Another question would be, do I even need to VACUUM considering the way my database is being produced? I would appreciate a reply from somebody who is aware of SQLite internals rather than an "educated guess" :-) Thank you for you time, Andrei _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
