[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] VACUUM and large indices: best practice?
From:       Simon Slavin <slavins () bigfraud ! org>
Date:       2013-08-30 20:52:08
Message-ID: F3D75F0A-87BE-4192-BD29-9E3E9CCF8150 () bigfraud ! org
[Download RAW message or body]


On 30 Aug 2013, at 9:32pm, uncle.f <uncle.f@sboxx.org> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]