Re: [sqlite] VACUUM & journal size

> First, I'm sure it is nearly impossible to do this as a > guaranteed, atomic operation on most OSes and filesystems. ... > > Second, if this is meant to look like a cleanup operation on the > original file, the original file (including any filesystem meta-data) > should be kept in-tact.

Re: [sqlite] VACUUM & journal size

On Tue, Mar 16, 2010 at 06:18:13PM +0300, Max Vlasov scratched on the wall: > When I read the comments it was obvious that the algorithm uses very simple > approach: > Attach blank database, copy all data, detach, rename. Sure I might be > wrong in details, but generally it looks like this.

Re: [sqlite] VACUUM & journal size

> So the question is what is so special about sqlite3RunVacuum that it needs > more space than a simple emulation of its actions? I believe renaming of the file cannot be atomic. So in case of OS crash you can be in situation without database at all - no old and no new. Also deleting of old file

Re: [sqlite] VACUUM & journal size

> This means that to VACUUM a SQLite database of size X, you need at > least 2X of _additional_ free disk space available. That seems rather > wasteful, just looking at it as a SQLite user. Although > programmatically there may be reasons for it that I'm not aware of. > > Hmm, did some

Re: [sqlite] VACUUM & journal size

On Mon, Mar 15, 2010 at 11:18 AM, Scott Hess wrote: > AFAICT, the operation to copy the pages back _is_ journaled, and the > journal will get any pages which are overwritten in the front of the > main database.  If the initial database has half of the pages used, it > seems like

Re: [sqlite] VACUUM & journal size

On Mon, Mar 15, 2010 at 11:18:32AM -0800, Scott Hess scratched on the wall: > On Sun, Mar 14, 2010 at 5:18 PM, Jay A. Kreibich wrote: > > ?While I have not tested this, I was under the impression that the > > ?journal file is very very small, as no modifications are made to the >

Re: [sqlite] VACUUM & journal size

On Sun, Mar 14, 2010 at 5:18 PM, Jay A. Kreibich wrote: > On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the > wall: >> I have a SQLite database with one large table, and I'd like to shrink >> the size of that table to free up space in the filesystem.  

Re: [sqlite] VACUUM & journal size

On Mar 14, 2010, at 7:19 PM, Matthew L. Creech wrote: > Hi, > > I have a SQLite database with one large table, and I'd like to shrink > the size of that table to free up space in the filesystem. > > I'm finding that it needs a full 100 MB for the journal, even > though once the VACUUM succeeds

Re: [sqlite] VACUUM & journal size

On Mon, Mar 15, 2010 at 2:11 AM, Matthew L. Creech wrote: > > I'll give this a try tomorrow on a real device with journaling off, > and see how much space it uses in /tmp with journaling turned off. > I ran some tests on a real device with a real database, and got the

Re: [sqlite] VACUUM & journal size

On Sun, Mar 14, 2010 at 9:18 PM, Jay A. Kreibich wrote: > >  Are you sure it is the journal file that is growing too large? > ... > >  Now, if I'm following you correctly, the numbers you gave seem to >  indicate that this should work... If the old database is 100MB and >  the new

Re: [sqlite] VACUUM & journal size

On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the wall: > Hi, > > I have a SQLite database with one large table, and I'd like to shrink > the size of that table to free up space in the filesystem. My problem > is that the database is (for example) 100 MB, and I have

[sqlite] VACUUM & journal size

Hi, I have a SQLite database with one large table, and I'd like to shrink the size of that table to free up space in the filesystem. My problem is that the database is (for example) 100 MB, and I have 80 MB of free filesystem space. I figured that I could DELETE, say, half of the records from