On 30 Aug 2013, at 9:32pm, uncle.f <[email protected]> wrote: > 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)
Thank you very much for your detailed explanation of your procedure which saves lots of questions. > Another question would be, do I even need to VACUUM considering the way my > database is being produced? Great question. If all you are doing is writing (never any UPDATE or DELETE FROM or DROP) then VACUUM won't save any space in your database file. It would defragment your database and might thereby increase speed a little, but this is mostly for old-fashioned systems that get most of their speed from 'read-ahead buffering'. I would not expect much of a time saving (less than 1%) from any modern setup (test it !). If it's difficult, annoying or time-consuming to do the VACUUM I wouldn't bother. If you do decide to VACUUM then of your three options listed above I would do (2). Theoretically the indices would benefit from being vacuumed, and theoretically moving the VACUUMed file to the new storage medium should ensure it's defragmented. > I would appreciate a reply from somebody who is aware of SQLite internals > rather than an "educated guess" :-) Then you asked in the wrong place. You take what you can get by posting here. The idea is that if someone posts a wrong answer it'll be seen and corrected by another reader. It's open source information. If you want only replies from experts I assume you'll be happy to pay Hwaci's consultancy fee. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
